MySQL单表查询

MySQL 前端之家
前端之家收集整理的这篇文章主要介绍了MySQL单表查询，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

目录

一、单表查询的语法及关键字执行的优先级

1.1 单表查询语法

  1. SELECT DISTINCT 字段1,字段2... FROM 表名
  2.                               WHERE 条件
  3.                               GROUP BY field
  4.                               HAVING 筛选
  5.                               ORDER BY field
  6.                               LIMIT 限制条数

1.2 关键字执行的优先级

  1. from：找到表
  2. where：拿着where指定的约束条件，去文件/表中取出一条条记录
  3. group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
  4. select：执行select
  5. distinct：去重
  6. having：将分组的结果进行having过滤
  7. order by：将结果按条件排序：order by
  8. limit：限制结果的显示条数
@H_403_98@二、简单查询

2.1 建表和数据准备

  1. company.employee
  2.     员工id      id                  int             
  3.     姓名        emp_name            varchar
  4.     性别        sex                 enum
  5.     年龄        age                 int
  6.     入职日期     hire_date           date
  7.     岗位        post                varchar
  8.     职位描述     post_comment        varchar
  9.     薪水        salary              double
  10.     办公室       office              int
  11.     部门编号     depart_id           int
  15. # 创建表
  16. create table employee(
  17. id int not null unique auto_increment,emp_name varchar(20) not null,sex enum('male','female') not null default 'male',#  大部分是男的
  18. age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int,#  一个部门一个屋子
  19. depart_id int
  20. );
  23. # 查看表结构
  24. MysqL> desc employee;
  25. +--------------+-----------------------+------+-----+---------+----------------+
  26. | Field        | Type                  | Null | Key | Default | Extra          |
  27. +--------------+-----------------------+------+-----+---------+----------------+
  28. | id           | int(11)               | NO   | PRI | NULL    | auto_increment |
  29. | emp_name     | varchar(20)           | NO   |     | NULL    |                |
  30. | sex          | enum('male','female') | NO   |     | male    |                |
  31. | age          | int(3) unsigned       | NO   |     | 28      |                |
  32. | hire_date    | date                  | NO   |     | NULL    |                |
  33. | post         | varchar(50)           | YES  |     | NULL    |                |
  34. | post_comment | varchar(100)          | YES  |     | NULL    |                |
  35. | salary       | double(15,2)          | YES  |     | NULL    |                |
  36. | office       | int(11)               | YES  |     | NULL    |                |
  37. | depart_id    | int(11)               | YES  |     | NULL    |                |
  38. +--------------+-----------------------+------+-----+---------+----------------+
  40. # 插入记录
  41. # 三个部门：教学，销售，运营
  42. insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
  43. ('nick','male',18,'20170301','老男孩驻上海虹桥最帅',7300.33,401,1),#  以下是教学部
  44. ('jason',78,'20150302','teacher',1000000.31,('sean',81,'20130305',8300,('tank',73,'20140701',3500,('oscar',28,'20121101',2100,('mac','female','20110211',9000,('rocky','19000301',30000,('成龙',48,'20101111',10000,('歪歪','20150311','sale',3000.13,402,#  以下是销售部门
  45. ('丫丫',38,'20101101',2000.35,('丁丁','20110312',1000.37,('星星','20160513',3000.29,('格格','20170127',4000.33,('张野','20160311','operation',10000.13,403,3),#  以下是运营部门
  46. ('程咬金','19970312',20000,('程咬银','20130311',19000,('程咬铜','20150411',18000,('程咬铁','20140512',17000,3)
  47. ;
  49. #  ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
  1. # 简单查询
  2.     SELECT id,emp_name,post_comment,depart_id 
  3.     FROM employee;
  5.     SELECT * FROM employee;
  7.     SELECT emp_name,salary FROM employee;
  9. # 避免重复DISTINCT
  10.     SELECT DISTINCT post FROM employee;    
  12. # 通过四则运算查询
  13.     SELECT emp_name,salary*12 FROM employee;
  14.     SELECT emp_name,salary*12 AS Annual_salary FROM employee;
  15.     SELECT emp_name,salary*12 Annual_salary FROM employee;
  17. # 定义显示格式
  18.    CONCAT() 函数用于连接字符串
  19.    SELECT CONCAT('姓名: ','  年薪: ',salary*12)  AS Annual_salary 
  20.    FROM employee;
  21.    
  22.    CONCAT_WS() 第一个参数为分隔符
  23.    SELECT CONCAT_WS(':',salary*12)  AS Annual_salary 
  24.    FROM employee;
  26.    结合CASE语句：
  27.    SELECT
  28.        (
  29.            CASE
  30.            WHEN emp_name = 'mac' THEN
  31.                emp_name
  32.            WHEN emp_name = 'jason' THEN
  33.                CONCAT(emp_name,'_BIGSB')
  34.            ELSE
  35.                concat(emp_name,'SB')
  36.            END
  37.        ) as new_name
  38.    FROM
  39.        employee;

2.2 练习

  1. 查出所有员工的名字，薪资，格式为@H_404_114@<名字:nick> <薪资:3000>
  2. 查出所有的岗位（去掉重复）
  3. 查出所有员工名字，以及他们的年薪，年薪的字段名为annual_year
  1. select concat('<名字:','>    ','<薪资:','>') from employee;
  2. select distinct depart_id from employee;
  3. select emp_name,salary*12 annual_salary from employee;

三、约束条件（where）

where子句中可以使用：

  1. 比较运算符：> < >= <= <> !=
  2. between 80 and 100 值在80到100之间
  3. in(80,90,100) 值是80或90或100
  4. like 'n%'
    • 通配符可以是%或_，
      • %表示任意多字符
      • _表示一个字符
  5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
  1. 1. 单条件查询
  2.     SELECT emp_name FROM employee
  3.         WHERE post='sale';
  4.         
  5. 2. 多条件查询
  6.     SELECT emp_name,salary FROM employee
  7.         WHERE post='teacher' AND salary>10000;
  9. 3. 关键字BETWEEN AND
  10.     SELECT emp_name,salary FROM employee 
  11.         WHERE salary BETWEEN 10000 AND 20000;
  13.     SELECT emp_name,salary FROM employee 
  14.         WHERE salary NOT BETWEEN 10000 AND 20000;
  15.     
  16. 4. 关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
  17.     SELECT emp_name,post_comment FROM employee 
  18.         WHERE post_comment IS NULL;
  20.     SELECT emp_name,post_comment FROM employee 
  21.         WHERE post_comment IS NOT NULL;
  22.         
  23.     SELECT emp_name,post_comment FROM employee 
  24.         WHERE post_comment=''; 注意''是空字符串，不是null
  25.     ps：
  26.         执行
  27.         update employee set post_comment='' where id=2;
  28.         再用上条查看，就会有结果了
  30. 5. 关键字IN集合查询
  31.     SELECT emp_name,salary FROM employee 
  32.         WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
  33.     
  34.     SELECT emp_name,salary FROM employee 
  35.         WHERE salary IN (3000,4000,9000) ;
  37.     SELECT emp_name,salary FROM employee 
  38.         WHERE salary NOT IN (3000,9000) ;
  40. 6. 关键字LIKE模糊查询
  41.     通配符’%’
  42.     SELECT * FROM employee 
  43.             WHERE emp_name LIKE 'ni%';
  45.     通配符’_’
  46.     SELECT * FROM employee 
  47.             WHERE emp_name LIKE 'ja__';

3.1 练习

  1. 查看岗位是teacher的员工姓名、年龄
  2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
  3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
  4. 查看岗位描述不为NULL的员工信息
  5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
  6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
  7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
  1. select emp_name,age from employee where post = 'teacher';
  2. select emp_name,age from employee where post='teacher' and age > 30; 
  3. select emp_name,salary from employee where post='teacher' and salary between 9000 and 10000;
  4. select * from employee where post_comment is not null;
  5. select emp_name,salary from employee where post='teacher' and salary in (10000,30000);
  6. select emp_name,salary from employee where post='teacher' and salary not in (10000,salary*12 from employee where post='teacher' and emp_name like 'mac%';

四、分组（group by）

  1. 单独使用GROUP BY关键字分组
  2.     SELECT post FROM employee GROUP BY post;
  3.     注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
  5. GROUP BY关键字和GROUP_CONCAT()函数一起使用
  6.     SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;  # 按照岗位分组，并查看组内成员名
  7.     SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
  9. GROUP BY与聚合函数一起使用
  10.     select post,count(id) as count from employee group by post;  #  按照岗位分组，并查看每个组有多少人

注意：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义；多条记录之间的某个字段值相同，该字段通常用来作为分组的依据。

五、聚合函数

强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组。

示例：

  1.     SELECT COUNT(*) FROM employee;
  2.     SELECT COUNT(*) FROM employee WHERE depart_id=1;
  3.     SELECT MAX(salary) FROM employee;
  4.     SELECT MIN(salary) FROM employee;
  5.     SELECT AVG(salary) FROM employee;
  6.     SELECT SUM(salary) FROM employee;
  7.     SELECT SUM(salary) FROM employee WHERE depart_id=3;

5.1 练习

  1. 查询岗位名以及岗位包含的所有员工名字
  2. 查询岗位名以及各岗位内包含的员工个数
  3. 查询公司内男员工和女员工的个数
  4. 查询岗位名以及各岗位的平均薪资
  5. 查询岗位名以及各岗位的最高薪资
  6. 查询岗位名以及各岗位的最低薪资
  7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
  1. 题目1
  2. MysqL> select post,group_concat(emp_name) from employee group by post;
  3. +-----------------------------------------+---------------------------------------------------------+
  4. | post                                    | group_concat(emp_name)                                      |
  5. +-----------------------------------------+---------------------------------------------------------+
  6. | operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
  7. | sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
  8. | teacher                                 | jason,sean,tank,oscar,mac,rocky,成龙   |
  9. | 老男孩驻上海虹桥最帅              | nick                                                    |
  10. +-----------------------------------------+---------------------------------------------------------+
  13. 题目2
  14. MysqL> select post,count(id) from employee group by post;
  15. +-----------------------------------------+-----------+
  16. | post                                    | count(id) |
  17. +-----------------------------------------+-----------+
  18. | operation                               |         5 |
  19. | sale                                    |         5 |
  20. | teacher                                 |         7 |
  21. | 老男孩驻上海虹桥最帅              |         1 |
  22. +-----------------------------------------+-----------+
  25. 题目3
  26. MysqL> select sex,count(id) from employee group by sex;
  27. +--------+-----------+
  28. | sex    | count(id) |
  29. +--------+-----------+
  30. | male   |        10 |
  31. | female |         8 |
  32. +--------+-----------+
  34. 题目4
  35. MysqL> select post,avg(salary) from employee group by post;
  36. +-----------------------------------------+---------------+
  37. | post                                    | avg(salary)   |
  38. +-----------------------------------------+---------------+
  39. | operation                               |  16800.026000 |
  40. | sale                                    |   2600.294000 |
  41. | teacher                                 | 151842.901429 |
  42. | 老男孩驻上海虹桥最帅              |   7300.330000 |
  43. +-----------------------------------------+---------------+
  45. 题目5
  46. MysqL> select post,max(salary) from employee group by post;
  47. +-----------------------------------------+-------------+
  48. | post                                    | max(salary) |
  49. +-----------------------------------------+-------------+
  50. | operation                               |    20000.00 |
  51. | sale                                    |     4000.33 |
  52. | teacher                                 |  1000000.31 |
  53. | 老男孩驻上海虹桥最帅              |     7300.33 |
  54. +-----------------------------------------+-------------+
  56. 题目6
  57. MysqL> select post,min(salary) from employee group by post;
  58. +-----------------------------------------+-------------+
  59. | post                                    | min(salary) |
  60. +-----------------------------------------+-------------+
  61. | operation                               |    10000.13 |
  62. | sale                                    |     1000.37 |
  63. | teacher                                 |     2100.00 |
  64. | 老男孩驻上海虹桥最帅              |     7300.33 |
  65. +-----------------------------------------+-------------+
  67. 题目7
  68. MysqL> select sex,avg(salary) from employee group by sex;
  69. +--------+---------------+
  70. | sex    | avg(salary)   |
  71. +--------+---------------+
  72. | male   | 110920.077000 |
  73. | female |   7250.183750 |
  74. +--------+---------------+

六、过滤（having）

6.1 where和having的区别

执行优先级从高到低：where > group by > having

  1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
  2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

6.1.1 验证

  1. MysqL> select @@sql_mode;
  2. +--------------------+
  3. | @@sql_mode         |
  4. +--------------------+
  5. | ONLY_FULL_GROUP_BY |
  6. +--------------------+
  7. row in set (0.00 sec)
  9. MysqL> select * from emp where salary > 100000;
  10. +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
  11. | id | emp_name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
  12. +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
  13. |  2 | jason | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
  14. +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
  15. row in set (0.00 sec)
  17. MysqL> select post,group_concat(emp_name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段
  18. ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
  19. MysqL> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000;
  20. +-----------+-------------------------------------------------------+
  21. | post | group_concat(emp_name) |
  22. +-----------+-------------------------------------------------------+
  23. | operation | 程咬铁,张野 |
  24. | teacher | 成龙,jason |
  25. +-----------+-------------------------------------------------------+
  26. rows in set (0.00 sec)

6.2 练习

  1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
  2. 查询各岗位平均薪资大于10000的岗位名、平均工资
  3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
  1. 题目1
  2. MysqL> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2;
  3. +-----------------------------------------+--------------------+-----------+
  4. | post                                    | group_concat(emp_name) | count(id) |
  5. +-----------------------------------------+--------------------+-----------+
  6. | 老男孩驻上海虹桥最帅              | nick               |         1 |
  7. +-----------------------------------------+--------------------+-----------+
  9. 题目2
  10. MysqL> select post,avg(salary) from employee group by post having avg(salary) > 10000;
  11. +-----------+---------------+
  12. | post      | avg(salary)   |
  13. +-----------+---------------+
  14. | operation |  16800.026000 |
  15. | teacher   | 151842.901429 |
  16. +-----------+---------------+
  18. 题目3
  19. MysqL> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
  20. +-----------+--------------+
  21. | post      | avg(salary)  |
  22. +-----------+--------------+
  23. | operation | 16800.026000 |
  24. +-----------+--------------+

七、查询排序（order by）

  1. 按单列排序
  2.     SELECT * FROM employee ORDER BY salary;
  3.     SELECT * FROM employee ORDER BY salary ASC;
  4.     SELECT * FROM employee ORDER BY salary DESC;
  6. 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
  7.     SELECT * from employee
  8.         ORDER BY age,salary DESC;

7.1 练习

  1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
  2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
  3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
  1. 题目1
  2. MysqL> select * from employee ORDER BY age asc,hire_date desc;
  4. 题目2
  5. MysqL> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
  6. +-----------+---------------+
  7. | post      | avg(salary)   |
  8. +-----------+---------------+
  9. | operation |  16800.026000 |
  10. | teacher   | 151842.901429 |
  11. +-----------+---------------+
  13. 题目3
  14. MysqL> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
  15. +-----------+---------------+
  16. | post      | avg(salary)   |
  17. +-----------+---------------+
  18. | teacher   | 151842.901429 |
  19. | operation |  16800.026000 |
  20. +-----------+---------------+

八、限制查询的记录数（limit）

示例：

  1. SELECT * FROM employee ORDER BY salary DESC 
  2.     LIMIT 3;                    #默认初始位置为0 
  4. SELECT * FROM employee ORDER BY salary DESC
  5.     LIMIT 0,5; #从第0开始，即先出第一条，然后包含这一条在内往后查5条
  7. SELECT * FROM employee ORDER BY salary DESC
  8.     LIMIT 5,5; #从第5开始，即先出第6条，然后包含这一条在内往后查5条

8.1 练习

  1. 分页显示，每页5条
  1. MysqL> select * from  employee limit 0,5;
  2. +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
  3. | id | emp_name      | sex  | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
  4. +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
  5. |  1 | nick      | male |  18 | 2017-03-01 | 老男孩驻上海虹桥最帅              | NULL         |    7300.33 |    401 |         1 |
  6. |  2 | jason      | male |  78 | 2015-03-02 | teacher                                 |              | 1000000.31 |    401 |         1 |
  7. |  3 | sean   | male |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
  8. |  4 | tank   | male |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
  9. |  5 | oscar | male |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
  10. +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
  11. rows in set (0.00 sec)
  13. MysqL> select * from  employee limit 5,5;
  14. +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
  15. | id | emp_name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
  16. +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
  17. |  6 | mac | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
  18. |  7 | rocky     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
  19. |  8 | 成龙       | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
  20. |  9 | 歪歪       | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |
  21. | 10 | 丫丫       | female |  38 | 2010-11-01 | sale    | NULL         |  2000.35 |    402 |         2 |
  22. +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
  23. rows in set (0.00 sec)
  25. MysqL> select * from  employee limit 10,5;
  26. +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
  27. | id | emp_name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
  28. +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
  29. | 11 | 丁丁      | female |  18 | 2011-03-12 | sale      | NULL         |  1000.37 |    402 |         2 |
  30. | 12 | 星星      | female |  18 | 2016-05-13 | sale      | NULL         |  3000.29 |    402 |         2 |
  31. | 13 | 格格      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |
  32. | 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
  33. | 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
  34. +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
  35. rows in set (0.00 sec)

九、使用正则表达式查询

  1. SELECT * FROM employee WHERE emp_name REGEXP '^jas';
  3. SELECT * FROM employee WHERE emp_name REGEXP 'on$';
  5. SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
  8. 小结：对字符串匹配的方式
  9. WHERE emp_name = 'nick';
  10. WHERE emp_name LIKE 'sea%';
  11. WHERE emp_name REGEXP 'on$';

9.1 练习

  1. 查看所有员工中名字是mac开头，n或者g结果的员工信息
  1. select * from employee where emp_name regexp '^mac.*[gn]$';

猜你在找的MySQL相关文章

Mysql通过frm和ibd恢复数据库
昨天的考试过程中，有个考点的服务器蓝屏重启后发现Mysql启动不了(5.6.45 x32版本，使用in...
MySQL 数据库中的数据类型
整数类型 标准 SQL 中支持 INTEGER 和 SMALLINT 这两种类型，MySQL 数据库除了支持这两种类...
MySQL Select 语句执行顺序
一条 SQL 查询语句结构如下： SELECT DISTINCT &lt;select_list&gt; FROM &lt...
MySQL 数据备份与恢复
数据备份 1. 备份数据库 使用 mysqldump 命令可以将数据库中的数据备份成一个文本文件，表...
MySQL 大表拆分
概述 在实际工作中，在关系数据库（MySQL、PostgreSQL）的单表数据量上亿后，往往会出现查...
MySQL 索引详解
索引的含义和特点 索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种数据结...
MySQL 索引失效场景总结
查询条件有 or 假设在 customer_name 字段设置了普通索引，执行以下 sql： # type: ref, p...
MySQL 主从复制
概述 MySQL 的高可用主要通过主从复制来实现，同时在主从复制的基础上可以构建一个 MySQL ...
MySQL 日志管理
概述 日志文件记录 MySQL 数据库运行期间发生的变化，当数据库遭到意外的损害时，可以通过...
MySQL 事务和锁
事务概述 当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问