数据库基本操作

1. 数据库连接方式

1.1 本地连接

使用mysql -u root -p可以连接数据库，但这只是本地连接数据库的方式。

1
2
3

# 本地连接数据库
[root@sql ~]# mysql -uroot -p
Enter password:

1.2 远程连接

在生产很多情况下都是连接网络中某一个主机上的数据库。

连接参数:

-P: 指定连接远程数据库端口
-h: 指定连接远程数据库地址
-u: 指定连接远程数据库账户
-p: 指定连接远程数据库密码

# 远程连接数据库
[root@sql ~]# mysql -h192.168.56.11 -P3306 -uroot -p
Enter password: 

# 或者在一行中指定密码（不推荐，密码会出现在命令历史中）
[root@sql ~]# mysql -h192.168.56.11 -P3306 -uroot -p'password'

1.3 连接选项

常用连接选项:

选项	说明	示例
`-h`	指定主机地址	`-h192.168.56.11`
`-P`	指定端口号	`-P3306`
`-u`	指定用户名	`-uroot`
`-p`	指定密码	`-p` 或 `-p'password'`
`-S`	指定Socket文件	`-S /tmp/mysql.sock`
`-e`	执行SQL语句后退出	`-e "SHOW DATABASES;"`
`-D`	指定默认数据库	`-D testdb`

连接示例:

# 连接并执行SQL
mysql -h192.168.56.11 -uroot -p -e "SHOW DATABASES;"

# 连接并指定默认数据库
mysql -h192.168.56.11 -uroot -p -D testdb

# 使用配置文件连接
mysql --defaults-file=/etc/my.cnf

1.4 配置文件连接

创建客户端配置文件:

1 2	# 创建客户端配置文件 vim ~/.my.cnf

[client]
host=192.168.56.11
port=3306
user=root
password=your_password

# 使用配置文件连接
mysql

# 或指定配置文件
mysql --defaults-file=~/.my.cnf

2. 数据库基本操作

2.1 查看数据库版本

-- 查看MySQL版本
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.01 sec)

-- 查看版本和当前用户
mysql> SELECT VERSION(), USER(), DATABASE();
+-----------+----------------+------------+
| VERSION() | USER()         | DATABASE() |
+-----------+----------------+------------+
| 5.7.22    | root@localhost  | NULL       |
+-----------+----------------+------------+
1 row in set (0.00 sec)

2.2 创建数据库（DDL）

语法: CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];

-- 创建数据库
mysql> CREATE DATABASE Bgx_edu;
Query OK, 1 row affected (0.00 sec)

-- 创建数据库并指定字符集
mysql> CREATE DATABASE Bgx_edu CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)

-- 如果不存在则创建
mysql> CREATE DATABASE IF NOT EXISTS Bgx_edu;
Query OK, 1 row affected (0.00 sec)

数据库命名规范:

数据库名称严格区分大小写（Linux系统）
数据库名称必须是唯一
数据库名称不允许使用数字开头
数据库名称不能使用关键字命名（如：create、select等）
建议使用小写字母、数字、下划线
建议使用有意义的名称

注意: 执行命令不区分大小写，但数据库名称区分大小写。

2.3 查看数据库

-- 查看所有数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Bgx_edu            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

-- 查看数据库创建语句
mysql> SHOW CREATE DATABASE Bgx_edu;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| Bgx_edu  | CREATE DATABASE `Bgx_edu` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

系统数据库说明:

数据库	说明
information_schema	虚拟库，存储用户表信息、列信息、权限信息、字符信息等
performance_schema	主要存储数据库服务器的性能参数
mysql	授权库，主要存储系统用户的权限信息
sys	优化库，主要存储数据库服务器的性能参数
bgx	业务库，主要存放业务所需要的库和表

2.4 删除数据库

-- 删除数据库
mysql> DROP DATABASE Bgx_edu;
Query OK, 0 rows affected (0.07 sec)

-- 如果存在则删除
mysql> DROP DATABASE IF EXISTS Bgx_edu;
Query OK, 0 rows affected (0.00 sec)

-- 查看删除后的数据库列表
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

删除库下的表:

1
2
3

-- 删除指定库下的表
mysql> DROP TABLE Bgx_edu.t1;
Query OK, 0 rows affected (0.01 sec)

警告: 删除数据库会永久删除所有数据，请谨慎操作！

2.5 选择数据库

-- 使用USE进入对应库
mysql> USE Bgx_edu;
Database changed

-- 查看当前使用的数据库
mysql> SELECT DATABASE();
+-----------+
| DATABASE() |
+-----------+
| Bgx_edu   |
+-----------+
1 row in set (0.00 sec)

2.6 查看表

-- 列出当前库下面的表
mysql> SHOW TABLES;
Empty set (0.00 sec)

-- 查看所有库的表
mysql> SHOW TABLES FROM mysql;

-- 查看表状态
mysql> SHOW TABLE STATUS FROM Bgx_edu;

2.7 查看表结构

-- 查询某个库下的表结构
mysql> DESC mysql.slow_log;
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| Field          | Type                | Null | Key | Default              | Extra                          |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
| user_host      | mediumtext          | NO   |     | NULL                 |                                |
| query_time     | time(6)             | NO   |     | NULL                 |                                |
| lock_time      | time(6)             | NO   |     | NULL                 |                                |
| rows_sent      | int(11)             | NO   |     | NULL                 |                                |
| rows_examined  | int(11)             | NO   |     | NULL                 |                                |
| db             | varchar(512)        | NO   |     | NULL                 |                                |
| last_insert_id | int(11)             | NO   |     | NULL                 |                                |
| insert_id      | int(11)             | NO   |     | NULL                 |                                |
| server_id      | int(10) unsigned    | NO   |     | NULL                 |                                |
| sql_text       | mediumblob          | NO   |     | NULL                 |                                |
| thread_id      | bigint(21) unsigned | NO   |     | NULL                 |                                |
+----------------+---------------------+------+-----+----------------------+--------------------------------+
12 rows in set (0.00 sec)

查看建表语句:

-- 查看某张表的建表语句
mysql> SHOW CREATE TABLE mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

3. 数据库增删查改（CRUD）

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括：

INSERT: 数据插入
UPDATE: 数据更新
DELETE: 数据删除
SELECT: 数据查询（将在第4节详细讲解）

3.1 准备操作环境数据表

-- 创建数据库
mysql> CREATE DATABASE bgx;
Query OK, 1 row affected (0.00 sec)

-- 使用数据库
mysql> USE bgx;
Database changed

-- 创建数据表
mysql> CREATE TABLE t1(
    id INT, 
    name VARCHAR(10),
    sex ENUM('man','gril'), 
    age INT
);
Query OK, 0 rows affected (0.01 sec)

-- 查看表字段
mysql> DESC t1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type               | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id    | int(11)            | YES  |     | NULL    |       |
| name  | varchar(10)        | YES  |     | NULL    |       |
| sex   | enum('man','gril') | YES  |     | NULL    |       |
| age   | int(11)            | YES  |     | NULL    |       |
+-------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.2 插入数据INSERT语句

方式1: 插入完整数据（顺序插入）

语法: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值n);

1
2
3

-- 指定字段插入
mysql> INSERT INTO t1(id,name,sex,age) VALUES (1,'bgx','man',18);
Query OK, 1 row affected (0.01 sec)

方式2: 插入完整数据（推荐方式）

语法: INSERT INTO 表名 VALUES (值1,值2,值n);

1
2
3

-- 按字段顺序插入（推荐方式）
mysql> INSERT INTO t1 VALUES (2,'bgx2','gril',10);
Query OK, 1 row affected (0.01 sec)

方式3: 指定字段插入

语法: INSERT INTO 表名(字段2,字段3…) VALUES (值2,值3…);

1
2
3

-- 只插入部分字段
mysql> INSERT INTO t1(name,sex,age) VALUES ('bgx','man',20);
Query OK, 1 row affected (0.00 sec)

方式4: 插入多条记录

语法: INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);

-- 批量插入多条记录
mysql> INSERT INTO t1 VALUES
    (3,'bgx3','man',18), 
    (4,'bgx4','man',18), 
    (5,'bgx5','man',18);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看插入结果

-- 查看所有数据
mysql> SELECT * FROM t1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | bgx  | man  |   18 |
|    2 | bgx2 | gril |   10 |
| NULL | bgx  | man  |   20 |
|    3 | bgx3 | man  |   18 |
|    4 | bgx4 | man  |   18 |
|    5 | bgx5 | man  |   18 |
+------+------+------+------+
6 rows in set (0.00 sec)

INSERT最佳实践:

使用批量插入提高性能
明确指定字段名，避免字段顺序错误
使用事务保证数据一致性
检查数据类型和约束

3.3 更新数据UPDATE语句

语法: UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE 条件;

更新步骤:

查看需要修改的表的字段 DESC
查询对应的字段 SELECT
更新对应的表字段 UPDATE
添加对应的WHERE条件，精准修改

示例1: 更新单个字段

-- 将t1表中，name字段等于bgx的改为update_bgx
mysql> UPDATE t1 SET name='update_bgx' WHERE name='bgx';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

-- 查看更新结果
mysql> SELECT * FROM t1;
+------+------------+------+------+
| id   | name       | sex  | age  |
+------+------------+------+------+
|    1 | update_bgx | man  |   18 |
|    2 | bgx2       | gril |   10 |
| NULL | update_bgx | man  |   20 |
|    3 | bgx3       | man  |   18 |
|    4 | bgx4       | man  |   18 |
|    5 | bgx5       | man  |   18 |
+------+------------+------+------+
6 rows in set (0.00 sec)

示例2: 更新多个字段

1
2
3

-- 同时更新多个字段
mysql> UPDATE t1 SET name='new_name', age=25 WHERE id=1;
Query OK, 1 row affected (0.00 sec)

示例3: 修改密码示例

-- 查看表字段内容
mysql> SELECT user,host,authentication_string FROM mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

-- 更新字段（MySQL 5.7.6+）
mysql> UPDATE mysql.user SET
    authentication_string=PASSWORD('Bgx123.com') 
    WHERE user='root' AND host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

-- 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

UPDATE注意事项:

必须使用WHERE条件，否则会更新所有记录
更新前先使用SELECT验证条件
使用事务保证数据一致性
注意PASSWORD()函数在MySQL 5.7.6+中已废弃

3.4 删除数据DELETE

语法: DELETE FROM 表名 WHERE 条件;

删除指定记录

-- 删除字段包含update_bgx的记录
mysql> DELETE FROM t1 WHERE name='update_bgx';
Query OK, 2 rows affected (0.01 sec)

-- 查看删除结果
mysql> SELECT * FROM t1;
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    2 | bgx2 | gril |   10 |
|    3 | bgx3 | man  |   18 |
|    4 | bgx4 | man  |   18 |
|    5 | bgx5 | man  |   18 |
+------+------+------+------+
4 rows in set (0.00 sec)

清空表数据

-- 使用TRUNCATE清空表数据（速度快，不可回滚）
mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.00 sec)

-- 查看结果
mysql> SELECT * FROM t1;
Empty set (0.00 sec)

DELETE vs TRUNCATE:

操作	DELETE	TRUNCATE
速度	慢	快
可回滚	是	否
可带WHERE	是	否
重置AUTO_INCREMENT	否	是
触发触发器	是	否

DELETE注意事项:

必须使用WHERE条件，否则会删除所有记录
DELETE是逐行删除，大数据量时较慢
TRUNCATE是DDL操作，速度快但不可回滚
删除前先备份重要数据

4. 数据库查询语句

4.1 单表查询

在学习查询前，需要定义好对应数据进行查询。

表结构定义:

编号 id INT
姓名 name VARCHAR(30)
性别 sex ENUM
日期 time DATE
职位 post VARCHAR(50)
描述 job VARCHAR(100)
薪水 salary DOUBLE(15,2)
部门编号 dep_id INT

创建表

-- 创建表
mysql> CREATE TABLE bgx.t2(
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(30) NOT NULL, 
    sex ENUM('man','gril') DEFAULT 'man' NOT NULL, 
    time DATE NOT NULL, 
    post VARCHAR(50) NOT NULL, 
    job VARCHAR(100), 
    salary DOUBLE(15,2) NOT NULL,
    office INT, 
    dep_id INT
);
Query OK, 0 rows affected (0.01 sec)

插入数据

-- 插入测试数据
mysql> INSERT INTO bgx.t2(name,sex,time,post,job,salary,office,dep_id) VALUES 
('jack','man','2018-02-02','instructor','teach',5000,501,100), 
('tom','man','2018-02-03','instructor','teach',5500,501,100),
('robin','man','2018-02-02','instructor','teach',8000,501,100),
('alice','gril','2018-02-02','instructor','teach',7200,501,100), 
('bgx','man','2018-02-02','hr','hrcc',600,502,101),
('harry','man','2018-02-02','hr', NULL,6000,502,101),
('trf','gril','2018-02-06','sale','salecc',20000,503,102),
('test','gril','2018-02-05','sale','salecc',2200,503,102),
('dog','man','2018-02-05','sale', NULL,2200,503,102),
('alex','man','2018-02-05','sale','',2200,503,102);
Query OK, 10 rows affected (0.00 sec)

1. 简单查询

查看表字段与表信息

-- 查看表结构
mysql> DESC t2;
+--------+--------------------+------+-----+---------+----------------+
| Field  | Type               | Null | Key | Default | Extra          |
+--------+--------------------+------+-----+---------+----------------+
| id     | int(11)            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)        | NO   |     | NULL    |                |
| sex    | enum('man','gril') | NO   |     | man     |                |
| time   | date               | NO   |     | NULL    |                |
| post   | varchar(50)        | NO   |     | NULL    |                |
| job    | varchar(100)       | YES  |     | NULL    |                |
| salary | double(15,2)       | NO   |     | NULL    |                |
| office | int(11)            | YES  |     | NULL    |                |
| dep_id | int(11)            | YES  |     | NULL    |                |
+--------+--------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

查询所有数据

-- 查询所有数据
mysql> SELECT * FROM t2;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)

指定字段查询

-- 查询指定字段
mysql> SELECT name,salary,dep_id FROM t2;
+-------+----------+--------+
| name  | salary   | dep_id |
+-------+----------+--------+
| jack  |  5000.00 |    100 |
| tom   |  5500.00 |    100 |
| robin |  8000.00 |    100 |
| alice |  7200.00 |    100 |
| bgx   |   600.00 |    101 |
| harry |  6000.00 |    101 |
| trf   | 20000.00 |    102 |
| test  |  2200.00 |    102 |
| dog   |  2200.00 |    102 |
| alex  |  2200.00 |    102 |
+-------+----------+--------+
10 rows in set (0.00 sec)

避免重复查询字段（DISTINCT）

-- 查询所有职位（有重复）
mysql> SELECT post FROM t2;
+------------+
| post       |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr         |
| hr         |
| sale       |
| sale       |
| sale       |
| sale       |
+------------+
10 rows in set (0.00 sec)

-- 使用DISTINCT去重
mysql> SELECT DISTINCT post FROM t2;
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.00 sec)

-- 多字段去重
mysql> SELECT DISTINCT post, dep_id FROM t2;

通过四则运算查询

-- 计算每个人的年薪
mysql> SELECT name,salary,salary*14 FROM t2;
+-------+----------+-----------+
| name  | salary   | salary*14 |
+-------+----------+-----------+
| jack  |  5000.00 |  70000.00 |
| tom   |  5500.00 |  77000.00 |
| robin |  8000.00 | 112000.00 |
| alice |  7200.00 | 100800.00 |
| bgx   |   600.00 |   8400.00 |
| harry |  6000.00 |  84000.00 |
| trf   | 20000.00 | 280000.00 |
| test  |  2200.00 |  30800.00 |
| dog   |  2200.00 |  30800.00 |
| alex  |  2200.00 |  30800.00 |
+-------+----------+-----------+
10 rows in set (0.00 sec)

-- 计算年薪并定义输出字段信息别名（AS可去掉）
mysql> SELECT name,salary,salary*14 AS Annual_salary FROM t2;
+-------+----------+---------------+
| name  | salary   | Annual_salary |
+-------+----------+---------------+
| jack  |  5000.00 |      70000.00 |
| tom   |  5500.00 |      77000.00 |
| robin |  8000.00 |     112000.00 |
| alice |  7200.00 |     100800.00 |
| bgx   |   600.00 |       8400.00 |
| harry |  6000.00 |      84000.00 |
| trf   | 20000.00 |     280000.00 |
| test  |  2200.00 |      30800.00 |
| dog   |  2200.00 |      30800.00 |
| alex  |  2200.00 |      30800.00 |
+-------+----------+---------------+
10 rows in set (0.01 sec)

定义显示格式（CONCAT函数）

-- CONCAT()函数用于连接字符串
mysql> SELECT CONCAT(name,' annual salary:',salary*14) FROM t2;
+-----------------------------------------+
| CONCAT(name,' annual salary:',salary*14) |
+-----------------------------------------+
| jack annual salary:70000.00            |
| tom annual salary:77000.00             |
| robin annual salary:112000.00          |
| alice annual salary:100800.00          |
| bgx annual salary:8400.00              |
| harry annual salary:84000.00           |
| trf annual salary:280000.00            |
| test annual salary:30800.00             |
| dog annual salary:30800.00             |
| alex annual salary:30800.00            |
+-----------------------------------------+
10 rows in set (0.00 sec)

-- 使用别名
mysql> SELECT CONCAT(name,' annual salary:',salary*14) AS employee_info FROM t2;

2. 单条件查询

单条件查询

-- 查询职位为hr的员工
mysql> SELECT name,post FROM t2 WHERE post='hr';
+-------+------+
| name  | post |
+-------+------+
| bgx   | hr   |
| harry | hr   |
+-------+------+
2 rows in set (0.00 sec)

多条件查询

-- 查询职位为hr且薪水大于5000的员工
mysql> SELECT name,post,salary FROM t2 WHERE post='hr' AND salary >5000;
+-------+------+---------+
| name  | post | salary  |
+-------+------+---------+
| harry | hr   | 6000.00 |
+-------+------+---------+
1 row in set (0.00 sec)

-- 使用OR条件
mysql> SELECT name,post FROM t2 WHERE post='hr' OR post='sale';

关键字BETWEEN AND（区间查询）

-- 查找薪资范围在8000-20000之间
mysql> SELECT name,salary FROM t2 WHERE salary BETWEEN 8000 AND 20000;
+-------+----------+
| name  | salary   |
+-------+----------+
| robin |  8000.00 |
| trf   | 20000.00 |
+-------+----------+
2 rows in set (0.00 sec)

-- 等价于
mysql> SELECT name,salary FROM t2 WHERE salary >= 8000 AND salary <= 20000;

关键字IS NULL（空值查询）

-- 查找部门为NULL，没有部门的员工
mysql> SELECT name,job FROM t2 WHERE job IS NULL;
+-------+------+
| name  | job  |
+-------+------+
| harry | NULL |
| dog   | NULL |
+-------+------+
2 rows in set (0.00 sec)

-- 查找有部门的员工
mysql> SELECT name,job FROM t2 WHERE job IS NOT NULL;
+-------+--------+
| name  | job    |
+-------+--------+
| jack  | teach  |
| tom   | teach  |
| robin | teach  |
| alice | teach  |
| bgx   | hrcc   |
| trf   | salecc |
| test  | salecc |
| alex  |        |
+-------+--------+
8 rows in set (0.00 sec)

-- 查看部门为空的员工（空字符串）
mysql> SELECT name,job FROM t2 WHERE job='';
+------+------+
| name | job  |
+------+------+
| alex |      |
+------+------+
1 row in set (0.00 sec)

NULL vs 空字符串:

NULL: 表示没有值
'': 表示空字符串
使用IS NULL检查NULL值
使用=''检查空字符串

关键字IN（集合查询）

-- 使用OR方式
mysql> SELECT name,salary FROM t2 WHERE salary=4000 OR salary=5000 OR salary=8000;

-- 使用IN方式（推荐）
mysql> SELECT name,salary FROM t2 WHERE salary IN(4000,5000,8000);
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| robin | 8000.00 |
+-------+---------+
2 rows in set (0.01 sec)

-- NOT IN
mysql> SELECT name,salary FROM t2 WHERE salary NOT IN(4000,5000,8000);

关键字LIKE（模糊查询）

-- 通配符%：匹配任意多个字符
mysql> SELECT * FROM t2 WHERE name LIKE 'al%';
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name  | sex  | time       | post       | job   | salary  | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
|  4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 |    501 |    100 |
| 10 | alex  | man  | 2018-02-05 | sale       |       | 2200.00 |    503 |    102 |
+----+-------+------+------------+------------+-------+---------+--------+--------+
2 rows in set (0.00 sec)

-- 通配符_：匹配单个字符
mysql> SELECT * FROM t2 WHERE name LIKE 'al__';
+----+------+-----+------------+------+------+---------+--------+--------+
| id | name | sex | time       | post | job  | salary  | office | dep_id |
+----+------+-----+------------+------+------+---------+--------+--------+
| 10 | alex | man | 2018-02-05 | sale |      | 2200.00 |    503 |    102 |
+----+------+-----+------------+------+------+---------+--------+--------+
1 row in set (0.00 sec)

-- 包含特定字符
mysql> SELECT * FROM t2 WHERE name LIKE '%x%';

LIKE通配符:

%: 匹配任意多个字符（0个或多个）
_: 匹配单个字符
\%: 转义%，匹配%字符
\_: 转义_，匹配_字符

性能提示: LIKE ‘%pattern%’ 无法使用索引，性能较差。

3. 查询排序

单列排序

-- 按薪水从低到高排序（默认ASC）
mysql> SELECT * FROM t2 ORDER BY salary ASC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)

-- 按薪水从高到低排序（DESC倒序）
mysql> SELECT * FROM t2 ORDER BY salary DESC;

多列排序

-- 先按入职时间，再按薪水排序
mysql> SELECT * FROM t2 ORDER BY time DESC, salary ASC;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  8 | test  | gril | 2018-02-05 | sale       | salecc |  2200.00 |    503 |    102 |
|  9 | dog   | man  | 2018-02-05 | sale       | NULL   |  2200.00 |    503 |    102 |
| 10 | alex  | man  | 2018-02-05 | sale       |        |  2200.00 |    503 |    102 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
|  5 | bgx   | man  | 2018-02-02 | hr         | hrcc   |   600.00 |    502 |    101 |
|  1 | jack  | man  | 2018-02-02 | instructor | teach  |  5000.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
10 rows in set (0.00 sec)

-- 先按职位，再按薪水排序
mysql> SELECT * FROM t2 ORDER BY post, salary DESC;

4. 限制查询的记录数

-- 查询薪资最高前5名同事（默认初始位置为0）
mysql> SELECT * FROM t2 ORDER BY salary DESC LIMIT 5;
+----+-------+------+------------+------------+--------+----------+--------+--------+
| id | name  | sex  | time       | post       | job    | salary   | office | dep_id |
+----+-------+------+------------+------------+--------+----------+--------+--------+
|  7 | trf   | gril | 2018-02-06 | sale       | salecc | 20000.00 |    503 |    102 |
|  3 | robin | man  | 2018-02-02 | instructor | teach  |  8000.00 |    501 |    100 |
|  4 | alice | gril | 2018-02-02 | instructor | teach  |  7200.00 |    501 |    100 |
|  6 | harry | man  | 2018-02-02 | hr         | NULL   |  6000.00 |    502 |    101 |
|  2 | tom   | man  | 2018-02-03 | instructor | teach  |  5500.00 |    501 |    100 |
+----+-------+------+------------+------------+--------+----------+--------+--------+
5 rows in set (0.00 sec)

-- 从第4条开始，并显示5条数据（LIMIT offset, count）
mysql> SELECT * FROM t2 ORDER BY salary DESC LIMIT 3,5;

LIMIT语法:

LIMIT n: 返回前n条记录
LIMIT offset, count: 从offset开始返回count条记录
LIMIT count OFFSET offset: 标准SQL语法

分页查询:

-- 每页10条，第1页
SELECT * FROM t2 LIMIT 0, 10;

-- 每页10条，第2页
SELECT * FROM t2 LIMIT 10, 10;

-- 每页10条，第3页
SELECT * FROM t2 LIMIT 20, 10;

5. 使用集合函数查询

COUNT（计数）

-- 统计当前表总共多少条数据
mysql> SELECT COUNT(*) FROM t2;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

-- 统计dep_id为101有多少条数据
mysql> SELECT COUNT(*) FROM t2 WHERE dep_id=101;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

-- 统计非NULL值
mysql> SELECT COUNT(job) FROM t2;

MAX/MIN（最大值/最小值）

-- 薪水最高
mysql> SELECT MAX(salary) FROM t2;
+-------------+
| MAX(salary) |
+-------------+
|    20000.00 |
+-------------+
1 row in set (0.00 sec)

-- 薪水最低
mysql> SELECT MIN(salary) FROM t2;
+-------------+
| min(salary) |
+-------------+
|      600.00 |
+-------------+
1 row in set (0.00 sec)

AVG（平均值）

-- 平均薪水
mysql> SELECT AVG(salary) FROM t2;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
1 row in set (0.00 sec)

SUM（求和）

-- 总共发放多少薪水
mysql> SELECT SUM(salary) FROM t2;
+-------------+
| sum(salary) |
+-------------+
|    58900.00 |
+-------------+
1 row in set (0.00 sec)

-- hr部门发放多少薪水
mysql> SELECT SUM(salary) FROM t2 WHERE post='hr';
+-------------+
| sum(salary) |
+-------------+
|     6600.00 |
+-------------+
1 row in set (0.00 sec)

子查询应用

-- 哪个部门哪个人薪水最高
mysql> SELECT * FROM t2 WHERE salary=(SELECT MAX(salary) FROM t2);
+----+------+------+------------+------+--------+----------+--------+--------+
| id | name | sex  | time       | post | job    | salary   | office | dep_id |
+----+------+------+------------+------+--------+----------+--------+--------+
|  7 | trf  | gril | 2018-02-06 | sale | salecc | 20000.00 |    503 |    102 |
+----+------+------+------------+------+--------+----------+--------+--------+
1 row in set (0.01 sec)

6. 分组查询

GROUP BY和GROUP_CONCAT()

-- GROUP BY 和 GROUP_CONCAT()函数一起使用
mysql> SELECT post,GROUP_CONCAT(name) FROM t2 GROUP BY post;
+------------+----------------------+
| post       | GROUP_CONCAT(name)   |
+------------+----------------------+
| hr         | bgx,harry            |
| instructor | jack,tom,robin,alice |
| sale       | trf,test,dog,alex    |
+------------+----------------------+
3 rows in set (0.00 sec)

-- 使用别名
mysql> SELECT post,GROUP_CONCAT(name) AS Group_Post FROM t2 GROUP BY post;
+------------+----------------------+
| post       | Group_Post           |
+------------+----------------------+
| hr         | bgx,harry            |
| instructor | jack,tom,robin,alice |
| sale       | trf,test,dog,alex    |
+------------+----------------------+
3 rows in set (0.00 sec)

GROUP BY和集合函数

-- GROUP BY 和集合函数一起使用
mysql> SELECT post,SUM(salary) FROM t2 GROUP BY post;
+------------+-------------+
| post       | sum(salary) |
+------------+-------------+
| hr         |     6600.00 |
| instructor |    25700.00 |
| sale       |    26600.00 |
+------------+-------------+
3 rows in set (0.00 sec)

-- 多字段分组
mysql> SELECT post, dep_id, COUNT(*) FROM t2 GROUP BY post, dep_id;

HAVING子句

-- 筛选分组后的结果
mysql> SELECT post, SUM(salary) AS total_salary 
FROM t2 
GROUP BY post 
HAVING total_salary > 10000;
+------------+-------------+
| post       | total_salary |
+------------+-------------+
| instructor |    25700.00 |
| sale       |    26600.00 |
+------------+-------------+
2 rows in set (0.00 sec)

WHERE vs HAVING:

WHERE: 在分组前过滤，不能使用聚合函数
HAVING: 在分组后过滤，可以使用聚合函数

7. 使用正则表达式查询

-- 使用正则表达式查询（以ali开头）
mysql> SELECT * FROM t2 WHERE name REGEXP '^ali';
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name  | sex  | time       | post       | job   | salary  | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
|  4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 |    501 |    100 |
+----+-------+------+------------+------------+-------+---------+--------+--------+
1 row in set (0.00 sec)

-- 以gx结尾
mysql> SELECT * FROM t2 WHERE name REGEXP 'gx$';
+----+------+-----+------------+------+------+--------+--------+--------+
| id | name | sex | time       | post | job  | salary | office | dep_id |
+----+------+-----+------------+------+------+--------+--------+--------+
|  5 | bgx  | man | 2018-02-02 | hr   | hrcc | 600.00 |    502 |    101 |
+----+------+-----+------------+------+------+--------+--------+--------+
1 row in set (0.00 sec)

-- 包含特定字符
mysql> SELECT * FROM t2 WHERE name REGEXP 'x';

正则表达式元字符:

^: 匹配字符串开始
$: 匹配字符串结束
.: 匹配任意字符
*: 匹配0个或多个
+: 匹配1个或多个
?: 匹配0个或1个
[]: 字符集
|: 或

字符串匹配方式对比:

WHERE name = 'trf': 精确匹配
WHERE name LIKE 'ha%': 模式匹配
WHERE name REGEXP 'gx$': 正则匹配

4.2 多表查询

准备数据表

-- 准备表1
mysql> CREATE TABLE bgx.t3( 
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(50),
    age INT,
    dep_id INT
);
Query OK, 0 rows affected (0.01 sec)

-- 为表1插入数据
mysql> INSERT INTO t3(name,age,dep_id) VALUES 
('bgx',18,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);
Query OK, 6 rows affected (0.00 sec)

-- 查看表1数据
mysql> SELECT * FROM t3;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | bgx     |   18 |    200 |
|  2 | tom     |   26 |    201 |
|  3 | jack    |   30 |    201 |
|  4 | alice   |   24 |    202 |
|  5 | robin   |   40 |    200 |
|  6 | natasha |   28 |    204 |
+----+---------+------+--------+
6 rows in set (0.00 sec)

-- 准备表2
mysql> CREATE TABLE t4(
    dep_id INT, 
    dept_name VARCHAR(100)
);
Query OK, 0 rows affected (0.01 sec)

-- 为表2插入数据
mysql> INSERT INTO t4 VALUES
(200,'hr'),
(201,'it'),
(202,'xs'),
(203,'cw');
Query OK, 4 rows affected (0.00 sec)

-- 查看表2数据
mysql> SELECT * FROM t4;
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    200 | hr        |
|    201 | it        |
|    202 | xs        |
|    203 | cw        |
+--------+-----------+
4 rows in set (0.00 sec)

1. 交叉连接（不使用任何匹配条件）

交叉连接会产生笛卡尔积，结果集为两个表行数的乘积。

-- 交叉连接（笛卡尔积）
mysql> SELECT t3.name,t3.age,t3.dep_id,t4.dept_name FROM t3,t4;
+---------+------+--------+-----------+
| name    | age  | dep_id | dept_name |
+---------+------+--------+-----------+
| bgx     |   18 |    200 | hr        |
| bgx     |   18 |    200 | it        |
| bgx     |   18 |    200 | xs        |
| bgx     |   18 |    200 | cw        |
| tom     |   26 |    201 | hr        |
| tom     |   26 |    201 | it        |
| tom     |   26 |    201 | xs        |
| tom     |   26 |    201 | cw        |
| jack    |   30 |    201 | hr        |
| jack    |   30 |    201 | it        |
| jack    |   30 |    201 | xs        |
| jack    |   30 |    201 | cw        |
| alice   |   24 |    202 | hr        |
| alice   |   24 |    202 | it        |
| alice   |   24 |    202 | xs        |
| alice   |   24 |    202 | cw        |
| robin   |   40 |    200 | hr        |
| robin   |   40 |    200 | it        |
| robin   |   40 |    200 | xs        |
| robin   |   40 |    200 | cw        |
| natasha |   28 |    204 | hr        |
| natasha |   28 |    204 | it        |
| natasha |   28 |    204 | xs        |
| natasha |   28 |    204 | cw        |
+---------+------+--------+-----------+
24 rows in set (0.00 sec)

结果: 6行 × 4行 = 24行

2. 内连接（只连接匹配的行）

内连接只返回两个表中匹配的行。

-- 只找出有部门的员工（部门表中没有natasha所在的部门）
mysql> SELECT t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name 
FROM t3,t4 
WHERE t3.dep_id=t4.dep_id;
+----+-------+------+--------+-----------+
| id | name  | age  | dep_id | dept_name |
+----+-------+------+--------+-----------+
|  1 | bgx   |   18 |    200 | hr        |
|  2 | tom   |   26 |    201 | it        |
|  3 | jack  |   30 |    201 | it        |
|  4 | alice |   24 |    202 | xs        |
|  5 | robin |   40 |    200 | hr        |
+----+-------+------+--------+-----------+
5 rows in set (0.00 sec)

-- 使用INNER JOIN语法（推荐）
mysql> SELECT t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name 
FROM t3 
INNER JOIN t4 ON t3.dep_id=t4.dep_id;

3. 外连接

左连接（LEFT JOIN）

左连接返回左表的所有记录，右表没有匹配的显示NULL。

-- 左连接
mysql> SELECT id,name,t4.dept_name 
FROM t3 
LEFT JOIN t4 ON t3.dep_id = t4.dep_id;
+----+---------+-----------+
| id | name    | dept_name |
+----+---------+-----------+
|  1 | bgx     | hr        |
|  5 | robin   | hr        |
|  2 | tom     | it        |
|  3 | jack    | it        |
|  4 | alice   | xs        |
|  6 | natasha | NULL      |
+----+---------+-----------+
6 rows in set (0.00 sec)

右连接（RIGHT JOIN）

右连接返回右表的所有记录，左表没有匹配的显示NULL。

-- 右连接
mysql> SELECT id,name,t4.dept_name 
FROM t3 
RIGHT JOIN t4 ON t3.dep_id = t4.dep_id;
+------+-------+-----------+
| id   | name  | dept_name |
+------+-------+-----------+
|    1 | bgx   | hr        |
|    2 | tom   | it        |
|    3 | jack  | it        |
|    4 | alice | xs        |
|    5 | robin | hr        |
| NULL | NULL  | cw        |
+------+-------+-----------+
6 rows in set (0.00 sec)

全连接（FULL OUTER JOIN）

MySQL不支持FULL OUTER JOIN，使用UNION实现。

-- 使用UNION实现全连接
mysql> SELECT id,name,t4.dept_name 
FROM t3 
LEFT JOIN t4 ON t3.dep_id = t4.dep_id
UNION
SELECT id,name,t4.dept_name 
FROM t3 
RIGHT JOIN t4 ON t3.dep_id = t4.dep_id;

4. 符合条件连接查询

-- 以内连接的方式查询t3和t4表，找出公司所有部门中年龄大于25岁的员工
mysql> SELECT t3.id,t3.name,t3.age,t4.dept_name 
FROM t3,t4 
WHERE t3.dep_id = t4.dep_id 
AND age >25;
+----+-------+------+-----------+
| id | name  | age  | dept_name |
+----+-------+------+-----------+
|  5 | robin |   40 | hr        |
|  2 | tom   |   26 | it        |
|  3 | jack  |   30 | it        |
+----+-------+------+-----------+
3 rows in set (0.01 sec)

-- 以内连接的方式查询t3和t4表，并且以age字段降序显示
mysql> SELECT t3.id,t3.name,t3.age,t4.dept_name 
FROM t3,t4 
WHERE t3.dep_id = t4.dep_id 
ORDER BY age DESC;
+----+-------+------+-----------+
| id | name  | age  | dept_name |
+----+-------+------+-----------+
|  5 | robin |   40 | hr        |
|  3 | jack  |   30 | it        |
|  2 | tom   |   26 | it        |
|  4 | alice |   24 | xs        |
|  1 | bgx   |   18 | hr        |
+----+-------+------+-----------+
5 rows in set (0.00 sec)

4.3 子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字，还可以包含比较运算符：=、!=、>、<等。

带IN关键字的子查询

-- 查询t3表，但dept_id必须在t4表中出现过
mysql> SELECT * FROM t3 WHERE dep_id IN (SELECT dep_id FROM t4);
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | bgx   |   18 |    200 |
|  2 | tom   |   26 |    201 |
|  3 | jack  |   30 |    201 |
|  4 | alice |   24 |    202 |
|  5 | robin |   40 |    200 |
+----+-------+------+--------+
5 rows in set (0.00 sec)

带比较运算符的子查询

-- 查询年龄大于等于25岁员工所在部门（查询老龄化的部门）
mysql> SELECT dep_id,dept_name FROM t4
WHERE dep_id IN
(SELECT DISTINCT dep_id FROM t3 WHERE age >=25);
+--------+-----------+
| dep_id | dept_name |
+--------+-----------+
|    201 | it        |
|    200 | hr        |
+--------+-----------+
2 rows in set (0.01 sec)

-- 使用比较运算符
mysql> SELECT * FROM t3 
WHERE age > (SELECT AVG(age) FROM t3);

带EXISTS关键字的子查询

EXISTS关键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

-- t4表中存在dep_id=203，返回True，所以查询t3表所有记录
mysql> SELECT * FROM t3
WHERE EXISTS (SELECT * FROM t4 WHERE dep_id=203);
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | bgx     |   18 |    200 |
|  2 | tom     |   26 |    201 |
|  3 | jack    |   30 |    201 |
|  4 | alice   |   24 |    202 |
|  5 | robin   |   40 |    200 |
|  6 | natasha |   28 |    204 |
+----+---------+------+--------+
6 rows in set (0.00 sec)

-- t4表中不存在dep_id=300，返回False，所以不查询
mysql> SELECT * FROM t3 WHERE EXISTS (SELECT * FROM t4 WHERE dep_id=300);
Empty set (0.00 sec)

子查询类型

标量子查询:

1 2	-- 返回单个值 SELECT * FROM t3 WHERE age > (SELECT AVG(age) FROM t3);

行子查询:

1 2	-- 返回一行 SELECT * FROM t3 WHERE (age, dep_id) = (SELECT age, dep_id FROM t3 WHERE id=1);

列子查询:

1 2	-- 返回一列 SELECT * FROM t3 WHERE dep_id IN (SELECT dep_id FROM t4);

表子查询:

1
2
3

-- 返回多行多列
SELECT * FROM t3 
WHERE (age, dep_id) IN (SELECT age, dep_id FROM t3 WHERE age > 25);

5. SQL优化最佳实践

5.1 查询优化

**避免SELECT ***:

-- 不推荐
SELECT * FROM t2;

-- 推荐
SELECT id, name, salary FROM t2;

使用索引列:

1 2	-- 确保WHERE子句中的列有索引 SELECT * FROM t2 WHERE id = 1; -- id是主键，有索引

避免在WHERE子句中使用函数:

-- 不推荐
SELECT * FROM t2 WHERE YEAR(time) = 2018;

-- 推荐
SELECT * FROM t2 WHERE time >= '2018-01-01' AND time < '2019-01-01';

5.2 索引优化

-- 为经常查询的列创建索引
CREATE INDEX idx_name ON t2(name);
CREATE INDEX idx_salary ON t2(salary);

-- 创建复合索引
CREATE INDEX idx_post_salary ON t2(post, salary);

5.3 分页优化

-- 大数据量分页优化
-- 不推荐（OFFSET大时性能差）
SELECT * FROM t2 LIMIT 10000, 10;

-- 推荐（使用索引）
SELECT * FROM t2 WHERE id > 10000 LIMIT 10;

6. 常见问题排查

6.1 查询性能问题

-- 使用EXPLAIN分析查询计划
EXPLAIN SELECT * FROM t2 WHERE name = 'bgx';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';

6.2 数据类型问题

-- 检查数据类型
DESC t2;

-- 转换数据类型
SELECT CAST(salary AS UNSIGNED) FROM t2;

实战优化