MySQL 基础

发表于 分类于
梳理 MySQL 基础用法，涵盖建库建表、增删改查、外键与索引、分组聚合、多表联查与视图存储过程

这篇笔记把 MySQL 入门阶段需要掌握的内容串成一条线：先分清数据库的类型和 SQL 语句的分工，再用一个 school 示例库走完建库建表、增删改查、外键与索引、多表联查，最后收尾于视图、函数和存储过程。

Windows 下的安装过程可以参考之前的文章。

一、数据库分类

按数据组织方式，数据库大致分两类：

  1. 关系型数据库：MySQL、SQL Server、MariaDB、Oracle 等，数据以二维表的形式存放，表之间通过键关联。
  2. 非关系型数据库（NoSQL）：MongoDB、Redis 等，不强制固定表结构。

非关系型数据库内部还可以细分为四种常见形态：

  1. 文档型
  2. key-value 型
  3. 列式数据库
  4. 图形数据库

文档型
key-value型
列式数据库
图形数据库

本文之后的内容都围绕关系型数据库中的 MySQL 展开。

二、连接与使用 MySQL

客户端连接

1
2
3
4
5
# 连接到 MySQL
mysql -h localhost -P 3306 -u root -p

# 直接连接到指定的库
mysql -u root -p company

进入客户端后，语句结尾的符号决定结果的排版方式：

  • \g 等价于分号，结果水平显示；
  • \G 结果垂直显示，字段很多的时候比表格好读。

修改当前用户密码：

1
ALTER USER `root`@`localhost` IDENTIFIED BY 'password';

准备一份示例数据

练习时用真实数据集比手工插入几行更有意义，可以直接导入官方示例库 employees：

1
2
# 示例数据下载地址 https://codeload.github.com/datacharmer/test_db/zip/master
mysql -u root -p < employees.sql

SQL 语句的四类分工

理解语句的分类，比死记语法更重要：

  1. DDL（数据定义语言）：CREATE / ALTER / DROP / TRUNCATE，操作的是数据库对象（库和表）本身。
  2. DML（数据操作语言）：INSERT / UPDATE / DELETE / SELECT，操作的是表里的数据。前三个也称为写，SELECT 称为读。
  3. TCL（事务控制语句）：COMMIT / ROLLBACK，管理数据库中的事务。
  4. DCL（数据控制语句）：GRANT / REVOKE，控制数据的访问权限。

常用变量与路径

1
2
3
4
5
SHOW VARIABLES LIKE '%datadir%';         -- 数据目录（配置文件里的 datadir）
SHOW VARIABLES LIKE '%long_query_time%'; -- 慢查询阈值
SET long_query_time = 5;                 -- 临时把慢查询阈值改成 5 秒
SELECT DATABASE();                       -- 当前连接的是哪个库
SHOW DATABASES;                          -- 当前用户有权访问的所有库

数据文件所在的目录可以直接在系统层面确认：

1
sudo ls -lhtr /usr/local/mysql/data/

创建用户与授权

除了 localhost 上的管理任务，一般不推荐用 root 账号连接数据库执行业务语句。给应用单独建一个只拥有必要权限的账号：

1
2
3
4
5
6
CREATE USER 'app'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON `school`.* TO 'app'@'%';
FLUSH PRIVILEGES;

-- 回收权限
REVOKE DELETE ON `school`.* FROM 'app'@'%';

三、库与表的管理（DDL）

层级关系是一条主线，后面所有操作都在这条线上：

数据库服务器 → 数据库 → 表（由列定义） → 行

一个数据库是许多表的集合，一台数据库服务器又可以容纳许多这样的数据库。

创建与切换数据库

1
2
3
4
5
6
7
8
-- 新建数据库，库名和表名建议统一用反引号包裹
CREATE DATABASE `school`;

-- 当名字里含特殊字符（如点号）时，反引号是必须的
CREATE DATABASE `my.contacts`;

-- 使用数据库
USE `school`;

创建表


SQL 里 -- 之后的内容是注释，不会被执行。下面建一张学生表：

1
2
3
4
5
6
7
CREATE TABLE `students`(
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` VARCHAR(20) NOT NULL,
	`nickname` VARCHAR(20) NULL,
	`sex` CHAR(1) NULL,
	`in_time` DATETIME NULL
) DEFAULT CHARSET 'UTF8MB4';

关于主键：PRIMARY KEY 是用来唯一定位记录的特殊索引，建议不要使用任何业务相关的字段作为主键。主键也可以事后单独添加：

1
ALTER TABLE `students` ADD CONSTRAINT pk_id PRIMARY KEY(`id`);

联合主键语法上支持，但实践中最好不要使用：

1
2
3
4
5
6
CREATE TABLE mytable (
    aa INT,
    bb CHAR(8),
    cc DATE,
    PRIMARY KEY (aa, bb)
);

建表时显式指定存储引擎和字符集是个好习惯：

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `company`.`customers`(
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(20),
    `last_name` VARCHAR(20),
    `country` VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看与克隆表结构

1
2
3
4
SHOW TABLES;                      -- 查看当前库的所有表
SHOW CREATE TABLE `customers`\G   -- 查看完整建表语句
DESC `customers`;                 -- 查看列定义
CREATE TABLE `new_customers` LIKE `customers`;  -- 只克隆结构，不含数据

修改表结构

1
2
3
-- 在 students 表中，把 class id 加到 id 的后一列
ALTER TABLE `school`.`students`
ADD COLUMN `class id` INT NULL AFTER `id`;

这里的列名带空格，所以每次引用都必须用反引号包住。真实项目里更推荐 class_id 这样的写法，本文为了和后面的联查示例输出保持一致才沿用原名。

删除列用 DROP COLUMN：

1
ALTER TABLE `students` DROP COLUMN `class id`;

删除与清空

1
2
3
TRUNCATE TABLE `customers`;   -- 清空所有行最快的方式，属 DDL，无法通过日志恢复
DELETE FROM `customers`;      -- 逐行删除，慢，但可以回滚 / 恢复
DROP TABLE `customers`;       -- 连表结构一起删掉

四、数据的增删改查（DML）

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
-- 按列顺序整行插入，now() 取 MySQL 当前时间
INSERT INTO `students` VALUE(1,'weilai','imwl','男',now());

-- 指定列名，选择性插入（推荐写法，表结构变化时不易出错）
INSERT INTO `students`(`name`,`nickname`,`sex`,`in_time`) VALUES('weilai','imwl','男',now());

-- 一次插入多行
INSERT INTO `students`(`name`,`nickname`,`sex`,`in_time`) VALUES
('weilai2','imwl','男',now()),
('weilai','imwl','男',now()),
('weilai','imwl','男',now()),
('weilai','imwl','男',now());

查询数据

SELECT 各子句的书写顺序是固定的，不能颠倒：

得按照上面的先后顺序，不能颠倒

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- * 表示所有列，生产环境应尽量避免
SELECT * FROM `students`;

-- 只取需要的列
SELECT `name`,`nickname` FROM `students`;

-- 加过滤条件
SELECT `name`,`nickname` FROM `students` WHERE `sex`='男';

-- 按 id 倒序
SELECT `id`,`name`,`nickname` FROM `students` WHERE `sex`='男'
ORDER BY `id` DESC;

-- 分页：0,2 表示从第 1 条开始取 2 条
SELECT `id`,`name`,`nickname` FROM `students` WHERE `sex`='男'
ORDER BY `id` DESC LIMIT 0,2;

-- 1,2 表示从第 2 条开始取 2 条
SELECT `id`,`name`,`nickname` FROM `students` WHERE `sex`='男'
ORDER BY `id` DESC LIMIT 1,2;

字符串比较默认不区分大小写，加上 BINARY 可以强制区分：

1
SELECT * FROM `students` WHERE BINARY `name` = 'imwl';

修改数据

WHERE 在这里非常重要，漏写就是改动整张表的数据：

1
2
3
4
5
6
7
8
9
10
11
-- 危险：把所有人的性别都改成女
UPDATE `students` SET `sex`='女';

-- 只改 name 为 weilai 的记录
UPDATE `students` SET `sex`='男' WHERE `name` = 'weilai';

-- 一次改多列
UPDATE `students` SET `sex`='男',`nickname`='没有昵称' WHERE `name` = 'weilai';

-- 按范围条件修改
UPDATE `students` SET `sex`='女' WHERE `id` < 3;

删除数据

1
2
3
4
5
6
-- 删除 students 表中性别为女的数据
DELETE FROM `students` WHERE `sex` = '女';

-- 删除全部数据（两种方式的差别见上文“删除与清空”）
DELETE FROM `students`;
TRUNCATE TABLE `students`;

五、分组与聚合

WHERE 过滤行、GROUP BY 分组、HAVING 过滤分组结果、ORDER BY 排序、LIMIT 截断，顺序不能乱：

1
2
3
4
5
6
7
8
9
-- WHERE 先过滤行，GROUP BY 按性别分组，HAVING 过滤分组结果，最后排序、截断
SELECT `sex`, COUNT(*) num FROM `students` WHERE `id` >= 10
GROUP BY `sex` HAVING COUNT(*) > 1 ORDER BY num DESC LIMIT 0,3;

-- COUNT 计数，num 是结果列的别名。不写 GROUP BY 时整张表算一组，只返回一行
SELECT COUNT(*) num FROM `students` WHERE NOT `id` >= 10 AND `sex` != '女';

-- AVG 求平均，同样是整张表算一组
SELECT AVG(`id`) num FROM `students` WHERE NOT `id` >= 10 AND `sex` != '女';

GROUP BY

写这类语句时有两个容易踩的坑：

  1. 分组列要选得有意义。按主键 GROUP BY id 等于每组只有一行，分组就白做了；HAVING 后面也该写聚合条件（COUNT(*) > 1 这种），直接写 HAVING in_time 是把 DATETIME 当布尔用，实际效果只是把 in_time 为 NULL 和零值的行滤掉，并不是在“过滤分组结果”。
  2. 没有 GROUP BY 的聚合查询只有一行结果，再 ORDER BY id 已经没有意义，而且 5.7.5 起 only_full_group_by 默认开启，id 既不在分组里也不是聚合函数，会直接报 ERROR 1140；同理这时 LIMIT 0,8 也是多余的。

六、外键与表间关系

外键

在 students 表中，class id 这一列的值指向另一张表（class）的记录，这样的列称为外键。

先建出班级表：

1
2
3
4
CREATE TABLE `class`(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(20) NOT NULL
) DEFAULT CHARSET 'UTF8MB4';

再把外键约束加上：

1
2
3
4
5
6
7
8
9
10
ALTER TABLE `students`
ADD CONSTRAINT `qe`          -- 约束名称可以随意取，便于以后删除
FOREIGN KEY (`class id`)
REFERENCES `class` (`id`);

-- 也可以不命名，让 MySQL 自动生成约束名
-- ALTER TABLE `school`.`students` ADD FOREIGN KEY (`class id`) REFERENCES `school`.`class` (`id`);

-- 删除外键约束
ALTER TABLE `students` DROP FOREIGN KEY `qe`;

注意：删除外键约束并没有删除外键这一列，删掉列要用前面提到的 DROP COLUMN。

一对多、多对多与一对一

  • 一对多：一个班级对应多个学生，也就是上面 students 与 class 的关系。
  • 多对多：通过一张中间表把两侧的主键都记下来，就定义出了多对多关系。
  • 一对一：一个表的记录对应另一个表唯一的一条记录。

一对一有一个很实用的场景：把大表拆成两张一对一的表，让经常读取和不经常读取的字段分开。例如把用户表拆成用户基本信息表 user_info 和用户详细信息表 user_profiles，大部分请求只查 user_info，查询速度自然更快。

七、索引

想在查找记录时获得很快的速度，就需要索引。

创建与删除索引

1
2
3
4
5
6
7
8
-- 名称为 sex search，建立在 sex 列上
ALTER TABLE `school`.`students` ADD INDEX `sex search`(`sex`);

-- 也可以是多列（组合索引）
ALTER TABLE `school`.`students` ADD INDEX `search`(`sex`,`name`);

-- 删除索引
ALTER TABLE `school`.`students` DROP INDEX `sex search`;

索引的效率取决于索引列的值是否足够离散。像 sex 列，大约一半记录是男、一半是女，对它建索引基本没有意义。

唯一索引与唯一约束

如果 name 不会重复，可以建唯一索引，它既能加速查询，又能保证该列的值唯一：

1
ALTER TABLE `students` ADD UNIQUE INDEX `uk_name`(`name`);

索引名不能和已有的重名。上面创建组合索引时用掉了 search 这个名字，而删除时只删了 sex search，search 还在，所以这里换成 uk_name，否则会报 Duplicate key name 'search'。

如果只想要唯一性约束而不关心索引名，可以直接加约束：

1
ALTER TABLE `students` ADD CONSTRAINT uni_name UNIQUE (`name`);

约束要加在真正不该重复的列上。像 is_vaild 这种只有 0/1/NULL 的标志位就不能加唯一约束——那等于规定全表最多只有一行 is_vaild = 1。

索引小结

  • 通过对表创建索引，可以提高查询速度；
  • 通过创建唯一索引，可以保证某一列的值具有唯一性；
  • 索引对于用户和应用程序来说都是透明的，SQL 不需要为了用索引而改写。

八、多表联查

笛卡尔积与投影查询

多表联查
投影查询 简写
加 where

不加连接条件的多表查询会得到 M × N 行记录（M、N 为两个表各自的行数），结果集可能非常巨大，要小心使用。

内连接 INNER JOIN

1
2
3
SELECT s.id, s.name, `s`.`class id`, s.nickname, s.sex, c.name, s.in_time, s.is_vaild
FROM students s
INNER JOIN class c ON `s`.`class id` = c.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----+---------+----------+-----------+------+--------------+---------------------+----------+
| id | name    | class id | nickname  | sex  | name         | in_time             | is_vaild |
+----+---------+----------+-----------+------+--------------+---------------------+----------+
|  7 | weilai  |      202 | imwl      | 男   | 二年二班     | 2018-12-27 22:05:41 |        1 |
|  8 | weilai  |      202 | imwl      | 男   | 二年二班     | 2018-12-27 22:05:41 |        2 |
|  9 | weilai  |      202 | imwl      | 男   | 二年二班     | 2018-12-27 22:05:41 |     NULL |
| 10 | weilai2 |      201 | imwl      | 男   | 二年一班     | 2018-12-27 22:05:41 |     NULL |
| 12 | name1   |      201 | nickname1 | 女   | 二年一班     | NULL                |     NULL |
| 13 | name2   |      201 | nickname2 | 男   | 二年一班     | NULL                |     NULL |
| 19 | 2       |      301 | i         | 男   | 三年一班     | 2019-02-27 12:02:04 |     NULL |
| 20 | 3       |      301 | m         | 女   | 三年一班     | 2019-02-27 12:02:04 |     NULL |
| 21 | 4       |      302 | w         | 男   | 三年二班     | 2019-02-27 12:02:04 |     NULL |
| 22 | 5       |      302 | l         | 男   | 三年二班     | 2019-02-27 12:02:04 |     NULL |
+----+---------+----------+-----------+------+--------------+---------------------+----------+

INNER JOIN 的写法可以拆成四步：

  1. 先确定主表，仍然使用 FROM <表1> 的语法；
  2. 再确定需要连接的表，使用 INNER JOIN <表2>；
  3. 然后确定连接条件，使用 ON <条件...>，上例的条件是 s.class id = c.id，表示 students 表的 class id 列与 class 表的 id 列相同的行需要连接；
  4. 可选：再加上 WHERE 子句、ORDER BY 等子句。

外连接：LEFT / RIGHT / FULL

把查询写成这样：

1
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

把 tableA 看作左表、tableB 看作右表，??? 处填不同的关键字，结果集的范围就不同。

INNER JOIN 只选出两张表都存在的记录：

inner-join

LEFT OUTER JOIN 选出左表存在的记录：

left-outer-join

RIGHT OUTER JOIN 选出右表存在的记录：

right-outer-join

FULL OUTER JOIN 则是选出左右表中出现过的全部记录：

full-outer-join

不过 MySQL 到 8.0 / 8.4 都还不支持 FULL OUTER JOIN 这个语法，照着写会直接报语法错误——那是 SQL Server、Oracle、PostgreSQL 的写法。在 MySQL 里要用 LEFT JOIN 和 RIGHT JOIN 各查一遍再 UNION 起来模拟（UNION 会自动去重，正好去掉两边都命中的那部分）：

1
2
3
SELECT ... FROM tableA LEFT  JOIN tableB ON tableA.column1 = tableB.column2
UNION
SELECT ... FROM tableA RIGHT JOIN tableB ON tableA.column1 = tableB.column2;

联查小结

  • JOIN 查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；
  • INNER JOIN 是最常用的一种，语法是 SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>；
  • JOIN 查询仍然可以使用 WHERE 条件和 ORDER BY 排序。

九、视图、函数与存储过程

视图

视图是一条被起了名字的查询，用起来像表，但不真正存储数据。下面基于一张 player 表建立视图 player_above_avg_height，筛出身高高于平均值的球员：

1
2
3
4
CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) FROM player);

之后查询视图，效果等同于执行它背后的那条 SQL：

1
2
3
4
5
6
SELECT * FROM player_above_avg_height;

-- 相当于
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) FROM player);

视图的价值在于把复杂查询收敛成一个名字，让上层 SQL 更易读。

函数

MySQL 自带大量内置函数，字符串、数值、日期、聚合都有：

sql内置函数

内置函数不够用时可以自定义函数。函数必须有返回值，可以直接嵌在 SELECT 等表达式里调用：

1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION `add_num`(n INT) RETURNS INT
DETERMINISTIC
BEGIN
       DECLARE i INT DEFAULT 1;
       DECLARE s INT DEFAULT 0;
       WHILE i <= n DO
              SET s = s + i;
              SET i = i + 1;
       END WHILE;
       RETURN s;
END
1
SELECT add_num(100);   -- 5050

存储过程

存储过程同样把一段逻辑存在服务端，但它没有返回值，通过 IN / OUT 参数传递数据，并且要用 CALL 调用：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;

       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i + 1;
       END WHILE;
       SELECT sum;
END
1
CALL add_num(100);

一句话区分：要一个值就写函数，要一段流程就写存储过程。