MySQL 进阶查询

1. 创建测试数据库和表

CREATE DATABASE auth;
USE auth;

解释：创建一个名为 auth 的数据库，并切换到该数据库进行后续操作。

CREATE TABLE t1(id INT(10), name CHAR(20), level INT(10));

解释：在 auth 数据库中创建一个名为 t1 的表，包含三个字段：id（整数类型，长度10）、name（字符类型，长度20）和level（整数类型，长度10）。

INSERT INTO t1 VALUES (10, 'sagou', 42);
INSERT INTO t1 VALUES (8, 'senoku', 45);
INSERT INTO t1 VALUES (15, 'useless', 47);
INSERT INTO t1 VALUES (27, 'guess', 52);
INSERT INTO t1 VALUES (199, 'useless', 48);
INSERT INTO t1 VALUES (272, 'Theshy', 36);
INSERT INTO t1 VALUES (298, 'leslieF', 40);
INSERT INTO t1 VALUES (30, 'shirley', 58);
INSERT INTO t1 VALUES (190, 'zhangsan', 48);
INSERT INTO t1 VALUES (271, 'lisi', 52);
INSERT INTO t1 VALUES (299, 'wangwu', 52);
INSERT INTO t1 VALUES (31, 'zhaoliu', 58);

解释：向 t1 表中插入多条记录，每条记录包含 id、name 和 level 字段的值。

CREATE TABLE t2(id INT(10), name CHAR(20), level INT(10));
INSERT INTO t2 VALUES (10, 'sagou', 42);
INSERT INTO t2 VALUES (8, 'senoku', 45);
INSERT INTO t2 VALUES (15, 'useless', 47);
INSERT INTO t2 VALUES (27, 'guess', 52);
INSERT INTO t2 VALUES (199, 'useless', 48);
INSERT INTO t2 VALUES (272, 'Theshy', 36);
INSERT INTO t2 VALUES (298, 'leslieF', 40);
INSERT INTO t2 VALUES (30, 'shirley', 58);
INSERT INTO t2 VALUES (190, 'zhangsan', 48);
INSERT INTO t2 VALUES (271, 'lisi', 52);
INSERT INTO t2 VALUES (299, 'wangwu', 52);
INSERT INTO t2 VALUES (31, 'zhaoliu', 58);

解释：创建另一个名为 t2 的表，并插入多条记录，结构与 t1 表相同。

2. 常用查询介绍

2.1 按关键字排序

例1

SELECT id, name, level 
FROM t1 
WHERE level >= 45 
ORDER BY level DESC;

解释：从 t1 表中查询 id、name 和 level 字段，筛选 level 大于等于 45 的记录，并按 level 字段降序排列。

例2

SELECT id, name, level 
FROM t1 
WHERE level >= 45 
ORDER BY level DESC, id DESC;

解释：从 t1 表中查询 id、name 和 level 字段，筛选 level 大于等于 45 的记录，先按 level 降序排列，相同 level 再按 id 降序排列。

2.2 对结果进行分组

例1

SELECT COUNT(name), level 
FROM t1 
WHERE level >= 45 
GROUP BY level;

解释：从 t1 表中查询 name 字段的计数和 level 字段，筛选 level 大于等于 45 的记录，并按 level 字段分组。

例2

SELECT COUNT(name), level 
FROM t1 
WHERE level >= 45 
GROUP BY level 
ORDER BY COUNT(name) DESC;

解释：从 t1 表中查询 name 字段的计数和 level 字段，筛选 level 大于等于 45 的记录，按 level 字段分组，并按 name 的计数降序排列。

2.3 限制结果条目

例1

SELECT id, name, level 
FROM t1 
LIMIT 3;

解释：从 t1 表中查询 id、name 和 level 字段，仅返回前 3 条记录。

例2

SELECT id, name, level 
FROM t1 
ORDER BY level DESC 
LIMIT 3;

解释：从 t1 表中查询 id、name 和 level 字段，按 level 字段降序排列，并返回前 3 条记录。

例3

SELECT id, name, level 
FROM t1 
LIMIT 2, 3;

解释：从 t1 表中查询 id、name 和 level 字段，跳过前 2 条记录，返回接下来的 3 条记录。

2.4 设置别名

例1

SELECT COUNT(*) AS number 
FROM t1;

解释：从 t1 表中查询所有记录的计数，并将计数结果命名为 number。

例2

SELECT p.id, p.name 
FROM t1 AS p 
LIMIT 3;

解释：将 t1 表命名为 p，然后查询 p 表中的 id 和 name 字段，仅返回前 3 条记录。

例3

CREATE TABLE t3 AS 
SELECT * 
FROM t1;

解释：创建一个名为 t3 的表，并将 t1 表中的所有数据复制到 t3 表中。

2.5 通配符

例1

SELECT id, name, level 
FROM t1 
WHERE name LIKE 's%';

SELECT id, name, level 
FROM t1 
WHERE name LIKE '%s';

SELECT id, name, level 
FROM t1 
WHERE name LIKE '%es%';

解释：使用 % 通配符：

• s%：从 t1 表中查询 id、name 和 level 字段，筛选 name 以 s 开头的记录。
• %s：从 t1 表中查询 id、name 和 level 字段，筛选 name 以 s 结尾的记录。
• %es%：从 t1 表中查询 id、name 和 level 字段，筛选 name 包含 es 字符串的记录。

例2

SELECT id, name, level 
FROM t1 
WHERE name LIKE '_uess';

SELECT id, name, level 
FROM t1 
WHERE name LIKE 'use____';

SELECT id, name, level 
FROM t1 
WHERE name LIKE 'shi_ley';

解释：使用 _ 通配符：

• _uess：从 t1 表中查询 id、name 和 level 字段，筛选 name 第一个字符任意，接下来的字符是 uess 的记录。
• use____：从 t1 表中查询 id、name 和 level 字段，筛选 name 以 use 开头，后面跟随 4 个任意字符的记录。
• shi_ley：从 t1 表中查询 id、name 和 level 字段，筛选 name 以 shi 开头，中间有一个任意字符，后面是 ley 的记录。

例3

SELECT id, name, level 
FROM t1 
WHERE name LIKE '_es%';

解释：从 t1 表中查询 id、name 和 level 字段，筛选 name 第二个字符是 e，第三个字符是 s 的记录。

2.6 子查询

例1

SELECT name, level 
FROM t1 
WHERE id IN (
    SELECT id 
    FROM t1 
    WHERE level >= 45
);

解释：从 t1 表中查询 name 和 level 字段，筛选 id 在子查询结果中的记录。子查询筛选 level 大于等于 45 的 id。

例2

TRUNCATE TABLE t2;

INSERT INTO t2 
SELECT * 
FROM t1 
WHERE id IN (
    SELECT id 
    FROM t1
);

解释：

• TRUNCATE TABLE t2;：清空 t2 表中的所有记录。
• INSERT INTO t2 SELECT * FROM t1 WHERE id IN (SELECT id FROM t1);：将 t1 表中 id 在子查询结果中的所有记录插入到 t2 表中。子查询返回 t1 表中所有的 id。

3. NULL 值

例1

CREATE TABLE test (
    id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    level INT(10) NOT NULL,
    coin INT(32),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

解释：创建一个名为 test 的表，包含 id（整数类型，长度10，不能为空，自动递增）、name（字符串类型，长度32，不能为空）、level（整数类型，长度10，不能为空）和 coin（整数类型，可为空）字段。设置 id 为主键，并使用 InnoDB 存储引擎和 utf8 字符集。

INSERT INTO test(name, level) VALUES ('aa', 10);

解释：插入一条记录到 test 表中，name 为 aa，level 为 10，coin 字段为空。

INSERT INTO test(name, level, coin) VALUES ('ab', 20, 100);

解释：插入一条记录到 test 表中，name 为 ab，level 为 20，coin 为 100。

SELECT * FROM test WHERE coin IS NULL;

解释：从 test 表中查询 coin 字段为空的所有记录。

SELECT * FROM test WHERE coin IS NOT NULL;

解释：从 test 表中查询 coin 字段不为空的所有记录。

4. 正则表达式

例1

SELECT id, name, level 
FROM t1 
WHERE name REGEXP '^zhang';

解释：从 t1 表中查询 id、name 和 level 字段，筛选 name 以 zhang 开头的记录。

例2

SELECT id, name, level 
FROM t1 
WHERE name REGEXP 'ss$';

解释：从 t1 表中查询 id、name 和 level 字段，筛选 name 以 ss 结尾的记录。

例3

SELECT id, name, level 
FROM t1 
WHERE name REGEXP 'an';

解释：从 t1 表中查询 id、name 和 level 字段，筛选 name 包含 an 字符串的记录。

例4

SELECT id, name, level 
FROM t1 
WHERE name REGEXP 'shir.ey';

解释：从 t1 表中查询 id、name 和 level 字段，筛选 name 匹配正则表达式 shir.ey 的记录，其中 . 代表任意一个字符。

5. 运算符

5.1 算术运算符

解释：进行基本的算术运算，包括加法（1 + 2）、减法（2 - 1）、乘法（2 * 3）、除法（4 / 2）和取余（7 % 2），并分别将结果别名为 addition、subtraction、multiplication、division 和 remainder。

5.2 比较运算符

例1

SELECT 2 = 4, 2 = '2', 'e' = 'e', (2 + 2) = (3 + 1), 'r' = NULL;

解释：进行等于比较运算，包括数值和字符串的比较，并返回结果（0 表示 false，1 表示 true，NULL 表示未知或不可比较）。

例2

SELECT 'kgc' <> 'bdqn', 1 <> 2, 3 != 3, 2.5 != 2, NULL <> NULL;

解释：进行不等于比较运算，包括数值和字符串的比较，并返回结果（0 表示 false，1 表示 true，NULL 表示未知或不可比较）。

例3

SELECT 5 > 4, 'a' > 'b', 2 >= 3, (2 + 3) >= (1 + 2), 4.4 < 3, 1 < 2, 'x' <= 'y', 5 <= 5.5, 'u' >= NULL;

解释：进行大于、小于运算，包括数值和字符串的比较，并返回结果（0 表示 false，1 表示 true，NULL 表示未知或不可比较）。

例4

SELECT 2 IN (1, 2, 3, 4, 5), 'c' NOT IN ('a', 'b', 'c');

解释：进行 IN 和 NOT IN 运算，检查某个值是否在给定的集合中，并返回结果（0 表示 false，1 表示 true）。

5.3 逻辑运算符

例1

SELECT NOT 2, !3, NOT 0, !(4 - 4);

解释：进行逻辑非运算，将非零值视为 true，零值视为 false，并返回结果（0 表示 false，1 表示 true）。

例2

SELECT 2 AND 3, 4 && 0, 0 && NULL, 1 AND NULL;

解释：进行逻辑与运算，只有在所有值都为 true 时返回 true，否则返回 false。

例3

SELECT 2 OR 3, 4 || 0, 0 OR NULL, 1 || NULL;

解释：进行逻辑或运算，只要有一个值为 true 就返回 true，否则返回 false。

例4

SELECT 2 XOR 3, 0 XOR 0, 0 XOR 5, 1 XOR NULL, NULL XOR NULL;

解释：进行逻辑异或运算，当两个值不同时返回 true，否则返回 false。

5.4 位运算符

例1

SELECT 10 & 15, 10 | 15, 10 ^ 15, 5 & ~1;

解释：进行按位与（&），按位或（|），按位异或（^），和按位取反（~）运算，并返回结果。按位操作是对二进制位进行的操作。

例2

SELECT 2 << 3, 32 >> 2;

解释：进行左移（<<）和右移（>>）运算，并返回结果。左移将二进制位向左移动指定的位数（右边补零），右移将二进制位向右移动指定的位数（左边补零或符号扩展）。

6. 集合运算符

6.1 UNION 和 UNION ALL

例1

SELECT id, name, level 
FROM t1 
WHERE level > 45 
UNION 
SELECT id, name, level 
FROM t2 
WHERE level < 45;

解释：使用 UNION 运算符，合并两个 SELECT 语句的结果并去除重复的行。第一个 SELECT 从 t1 表中筛选 level 大于 45 的记录，第二个 SELECT 从 t2 表中筛选 level 小于 45 的记录。

例2

SELECT id, name, level 
FROM t1 
WHERE level > 45 
UNION ALL 
SELECT id, name, level 
FROM t2 
WHERE level < 45;

解释：使用 UNION ALL 运算符，合并两个 SELECT 语句的结果但不去除重复的行。第一个 SELECT 从 t1 表中筛选 level 大于 45 的记录，第二个 SELECT 从 t2 表中筛选 level 小于 45 的记录。

7. 连接操作

7.1 内连接（INNER JOIN）

SELECT t1.id, t1.name, t1.level, t2.level 
FROM t1 
INNER JOIN t2 
ON t1.id = t2.id;

解释：使用 INNER JOIN 运算符，连接 t1 表和 t2 表，并仅返回在 t1 和 t2 表中 id 字段匹配的行。

7.2 左连接（LEFT JOIN）

SELECT t1.id, t1.name, t1.level, t2.level 
FROM t1 
LEFT JOIN t2 
ON t1.id = t2.id;

解释：使用 LEFT JOIN 运算符，连接 t1 表和 t2 表，返回 t1 表中的所有行和 t2 表中 id 字段匹配的行。如果在 t2 表中没有匹配的行，结果中对应 t2 表的字段值为 NULL。

7.3 右连接（RIGHT JOIN）

SELECT t1.id, t1.name, t1.level, t2.level 
FROM t1 
RIGHT JOIN t2 
ON t1.id = t2.id;

解释：使用 RIGHT JOIN 运算符，连接 t1 表和 t2 表，返回 t2 表中的所有行和 t1 表中 id 字段匹配的行。如果在 t1 表中没有匹配的行，结果中对应 t1 表的字段值为 NULL。

8. 索引和性能优化

8.1 创建索引

CREATE INDEX idx_name_level 
ON t1 (name, level);

解释：在 t1 表的 name 和 level 字段上创建一个名为 idx_name_level 的索引，以加快基于这些字段的查询速度。

8.2 删除索引

DROP INDEX idx_name_level 
ON t1;

解释：删除 t1 表上的 idx_name_level 索引。

8.3 查看表的执行计划

EXPLAIN 
SELECT id, name, level 
FROM t1 
WHERE name = 'sagou';

解释：使用 EXPLAIN 语句查看查询的执行计划，帮助分析查询的性能瓶颈。

9. 事务处理

9.1 开始、提交和回滚事务

START TRANSACTION;

解释：开始一个事务。

INSERT INTO t1 VALUES (32, 'transaction_test', 60);

解释：在事务中执行插入操作

COMMIT;

解释：提交事务，将事务中的所有操作永久保存到数据库。

ROLLBACK;

解释：回滚事务，撤销事务中的所有操作。

10. 视图

10.1 创建视图

CREATE VIEW view_name 
AS 
SELECT id, name, level 
FROM t1 
WHERE level > 45;

解释：创建一个名为 view_name 的视图，视图的内容是从 t1 表中筛选 level 大于 45 的记录。

10.2 查询视图

SELECT * 
FROM view_name;

解释：查询视图 view_name 的内容。

10.3 删除视图

DROP VIEW view_name;

解释：删除视图 view_name。

11. 存储过程和函数

11.1 创建存储过程

DELIMITER //

CREATE PROCEDURE proc_name(IN param1 INT, OUT param2 INT)
BEGIN
    SELECT level INTO param2 
    FROM t1 
    WHERE id = param1;
END //

DELIMITER ;

解释：创建一个名为 proc_name 的存储过程，接受一个输入参数 param1 和一个输出参数 param2，在 t1 表中通过 id 查找 level 并赋值给 param2。

11.2 调用存储过程

CALL proc_name(10, @result);
SELECT @result;

解释：调用存储过程 proc_name，传入 10 作为 param1，并将结果存储在 @result 变量中，然后查询 @result 的值。

11.3 创建函数

DELIMITER //

CREATE FUNCTION func_name(param1 INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    SELECT level INTO result 
    FROM t1 
    WHERE id = param1;
    RETURN result;
END //

DELIMITER ;

解释：创建一个名为 func_name 的函数，接受一个整数参数 param1，在 t1 表中通过 id 查找 level，并返回该值。

11.4 调用函数

SELECT func_name(10);

解释：调用函数 func_name，传入 10 作为参数，并返回结果。