神光《Nest 通关秘籍》学习总结-前端快速入门MySql语法（真的很友好）

最近在学习神光大神的《Nest通关秘籍》，该小册主要包含下面这些内容：

想购买的可以点击《传送门》。

接下来的日子里，我将更新一系列的学习笔记。感兴趣的可以关注我的专栏《Nest 通关秘籍》学习总结。

特别申明：本系列文章已经经过作者本人的允许。 大家也不要想着白嫖，我的笔记只是个人边学习边记录的，不是很完整，大家想要深入学习还是要自己去购买原版小册。

本章我们来学习一下MySql的一些语法知识和常见的查询操作。

1. 数据定义语句(DDL)

1.1. 登录数据库

mysql -u root -p

1.2. 创建数据库

create database test;

1.3. 查看所有数据库

show databasese;

1.4. 选择数据库并使用

use test;

1.5. 查看所有数据表

show tables;

1.6. 删除数据库

drop database test;

2. 表操作

2.1. 创建表

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
    name VARCHAR(50) NOT NULL COMMENT '学生名',
    gender VARCHAR(10) NOT NULL COMMENT '性别',
    age INT NOT NULL COMMENT '年龄',
    class VARCHAR(50) NOT NULL COMMENT '班级名',
    score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4

2.2. 插入数据

INSERT INTO student (name, gender, age, class, score)
    VALUES 
        ('张三', '男',18, '一班',90),
        ('李四', '女',19, '二班',85),
        ('王五', '男',20, '三班',70),
        ('赵六', '女',18, '一班',95),
        ('钱七', '男',19, '二班',80),
        ('孙八', '女',20, '三班',75),
        ('周九', '男',18, '一班',85),
        ('吴十', '女',19, '二班',90),
        ('郑十一', '男',20, '三班',60),
        ('王十二', '女',18, '一班',95),
        ('赵十三', '男',19, '二班',75),
        ('钱十四', '女',20, '三班',80),
        ('孙十五', '男',18, '一班',90),
        ('周十六', '女',19, '二班',85),
        ('吴十七', '男',20, '三班',70),
        ('郑十八', '女',18, '一班',95),
        ('王十九', '男',19, '二班',80),
        ('赵二十', '女',20, '三班',75);

2.3. 删除表

drop table emp

2.4. 查询列

SELECT name, score FROM student;
# as命名列
SELECT name as "名字", score as "分数" FROM student;
# 带where条件
select name as "名字",class as "班级" from student where age >= 19;
# 条件可以是 and 连接的多个
select name as '名字',class as '班级' from student where gender='男' and score >= 90;
# 用 LIKE 做模糊查询
select * from student where name like '王%';
# 通过 in 来指定一个集合：
select * from student where class in ('一班', '二班');

# not in：
select * from student where class not in ('一班', '二班');

# 通过 between and 来指定一个区间
select * from student where age between 18 and 20;

# 通过limit实现分页返回
select * from student limit 0,5
# 简写为
select * from student limit 5;
#第二页的数据
select * from student limit 5,5;
# 通过 order by 来指定排序的列，asc表示升序，desc表示降序
select name,score,age from student order by score asc,age desc;

# 分组统计每个班级的平均成绩：
SELECT class as '班级', AVG(score) AS '平均成绩' FROM student GROUP BY class ORDER BY '平均成绩' DESC;

# 通过count统计班级人数
select class, count(*) as count from student group by class;

# 根据having统计
SELECT class,AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score > 90;

# distinct去重
SELECT distinct class FROM student;

3. 内置函数

3.1. 聚合函数

聚合函数：用于对数据的统计，比如 AVG、COUNT、SUM、MIN、MAX。

select avg(score) as '平均成绩',count(*) as '人数',sum(score) as '总成绩',min(score) as '最低分', max(score) as '最高分' from student 

3.2. 字符串函数

字符串函数：用于对字符串的处理，比如 CONCAT、SUBSTR、LENGTH、UPPER、LOWER。

SELECT CONCAT('xx', name, 'yy'), SUBSTR(name,2,3), LENGTH(name), UPPER('aa'), LOWER('TT') FROM student;

其中，substr 第二个参数表示开始的下标（mysql 下标从 1 开始），所以 substr('一二三',2,3) 的结果是 '二三'。

当然，也可以不写结束下标 substr('一二三',2)

3.3. 数值函数

数值函数：用于对数值的处理，比如 ROUND、CEIL、FLOOR、ABS、MOD。

SELECT ROUND(1.234567, 2), CEIL(1.234567), FLOOR(1.234567), ABS(-1.234567), MOD(5, 2);

分别是 ROUND 四舍五入、CEIL 向上取整、FLOOR 向下取整、ABS 绝对值、MOD 取模。

3.4. 日期函数

日期函数：对日期、时间进行处理，比如 DATE、TIME、YEAR、MONTH、DAY

SELECT YEAR('2023-06-01 22:06:03'), MONTH('2023-06-01 22:06:03'),DAY('2023-06-01 22:06:03'),DATE('2023-06-01 22:06:03'), TIME('2023-06-01 22:06:03');

3.5. 条件函数

条件函数：根据条件是否成立返回不同的值，比如 IF、CASE

select name, if(score >=60, '及格', '不及格') from student;

SELECT name, score, CASE WHEN score >=90 THEN '优秀' WHEN score >=60 THEN '良好'ELSE '差' END AS '档次' FROM student;

if 和 case 函数和 js 里的 if、swtch 语句很像，很容易理解。

if 函数适合单个条件，case 适合多个条件。

3.6. 系统函数

系统函数：用于获取系统信息，比如 VERSION、DATABASE、USER。

select VERSION(), DATABASE(), USER()

3.7. 其他函数

其他函数：NULLIF、COALESCE、GREATEST、LEAST。

NULLIF：如果相等返回 null，不相等返回第一个值。

select NULLIF(1,1), NULLIF(1,2);

COALESCE：返回第一个非 null 的值：

select COALESCE(null, 1), COALESCE(null, null, 2)

GREATEST、LEAST：返回几个值中最大最小的。

select GREATEST(1,2,3),LEAST(1,2,3,4);

3.8. 类型转换函数

类型转换函数：转换类型为另一种，比如 CAST、CONVERT、DATE_FORMAT、STR_TO_DATE。

select greatest(1, '123',3);

3 最大，因为它并没有把 '123' 当成数字.

用 convert 或者 cast 做类型转换：

select greatest(1, convert('123', signed),3);
select greatest(1, cast('123' as signed),3);

这里可以转换的类型有这些：

• signed：整型；
• unsigned：无符号整型
• decimal：浮点型；
• char：字符类型；
• date：日期类型；
• time：时间类型；
• datetime：日期时间类型；
• binary：二进制类型

SELECT DATE_FORMAT('2022-01-01', '%Y年%m月%d日');

SELECT STR_TO_DATE('2023-06-01', '%Y-%m-%d');

灵活掌握这些语法，就能写出各种复杂的查询语句。

4. JOIN ON 关联查询

4.1. 一对一查询

在MySQL中，联表查询是指在查询操作中使用多个表进行关联操作。通常情况下，我们将不同的数据拆分到多个表中，通过联表查询可以根据这些表之间的关系，一次性获取到需要的数据。

外键（Foreign Key）是一种用于建立表与表之间关联的约束。它定义了一个表中的一列，该列的值必须在另一个表的主键列中存在。外键用于确保数据的完整性和一致性，以及实现表之间的关联。

下面我用user和id_card两个表作为示例，来看看如何创建外健和联表查询。

新建user表：

CREATE TABLE `mysql2-test`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(45) NOT NULL COMMENT '名字',
  PRIMARY KEY (`id`)
);

新建id_card表：

CREATE TABLE `id_card` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `card_name` varchar(45) NOT NULL COMMENT '身份证号',
  `user_id` int DEFAULT NULL COMMENT '用户 id',
  PRIMARY KEY (`id`),
  INDEX `card_id_idx` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)  CHARSET=utf8mb4;

user 表的主键是 id、可以通过 id 来唯一标识一个 user。那如果id_card 想查找 user，也就是我想通过身份知道这个user叫什么，那是不是在id_card表中需要存在一个id跟user表中的一模一样啊，这个id就是外健，也就是表中的user_id。

当我们在id_card表查询某个用户身份证的时候，是不是也能知道对应的user_id,然后通过这个user_id再去user表中去查询id，如果相等是不是就找到了这个人的名字。

这个就是通过外健来做的联表查询了。

下面分别在两个表插入一些数据，以便我们操作。

user表：

INSERT INTO `user` (`name`)
	VALUES
		('张三'),
		('李四'),
		('王五'),
		('赵六'),
		('孙七'),
		('周八'),
		('吴九'),
		('郑十'),
		('钱十一'),
		('陈十二'); 

id_card表：

INSERT INTO id_card (card_name, user_id) 
    VALUES
        ('110101199001011234',1),
	('310101199002022345',2),
	('440101199003033456',3),
	('440301199004044567',4),
	('510101199005055678',5),
	('330101199006066789',6),
	('320101199007077890',7),
	('500101199008088901',8),
	('420101199009099012',9),
	('610101199010101023',10);

这样，一对一关系的数据就插入成功了。

那怎么关联查出来呢？

SELECT * FROM user JOIN id_card ON user.id = id_card.user_id;

我们优化一下上面列表的展示：

SELECT user.id, name, id_card.id as card_id, card_name 
    FROM user
    JOIN id_card ON user.id = id_card.user_id;

这就是多表关联查询，语法是 JOIN ON。

JOIN ON的语法有三种：

• INNER JOIN 是只返回两个表中能关联上的数据
• LEFT JOIN 是额外返回左表中没有关联上的数据。
• RIGHT JOIN 是额外返回右表中没有关联上的数据。

在 FROM 后的是左表，JOIN 后的表是右表。

当 user 删除的时候，关联的 id_card 要不要删除？

当 user 的 id 修改的时候，关联的 id_card 要不要改 user_id？

我们之前在设置user_id的时候，是这样的：

其实它还还有其他的类型：

• CASCADE： 主表主键更新，从表关联记录的外键跟着更新，主表记录删除，从表关联记录删除
• SET NULL：主表主键更新或者主表记录删除，从表关联记录的外键设置为 null
• RESTRICT：只有没有从表的关联记录时，才允许删除主表记录或者更新主表记录的主键 id
• NO ACTION： 同 RESTRICT，只是 sql 标准里分了 4 种，但 mysql 里 NO ACTION 等同于 RESTRICT。

级联方式为 CASCADE 的处理逻辑：主表删除，从表关联记录也级联删除，主表 id 更新，从表关联记录也跟着更新。

4.2. 一对多查询

一对多的关系，比如一个部门有多个员工。

我们会有一个部门表和一个员工表：

在员工表添加外键 department_id 来表明这种多对一关系：

我们添加这两个表。

department表：

CREATE TABLE `mysql2-test`.`department` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(45) NOT NULL COMMENT '员工名称',
  PRIMARY KEY (`id`)
);

employee表：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(45) NOT NULL COMMENT '部门名称',
  `department_id` int DEFAULT NULL COMMENT '部门id',
  PRIMARY KEY (`id`),
  INDEX `department_id_idx` (`department_id` ASC) VISIBLE,
  CONSTRAINT `department_id` 
  	FOREIGN KEY (`department_id`) 
  	REFERENCES `department` (`id`)
  	ON DELETE SET NULL 
  	ON UPDATE SET NULL
)  CHARSET=utf8mb4;

往部门表插入几条数据：

INSERT INTO `department` (`id`, `name`) 
    VALUES 
        (1, '人事部'),
        (2, '财务部'),
        (3, '市场部'),
        (4, '技术部'),
        (5, '销售部'),
        (6, '客服部'),
        (7, '采购部'),
        (8, '行政部'),
        (9, '品控部'),
        (10, '研发部');

往员工表里插入几条数据:

INSERT INTO `employee` (`id`, `name`, `department_id`)
    VALUES 
        (1, '张三', 1),
        (2, '李四', 2), 
        (3, '王五', 3),
        (4, '赵六', 4),
        (5, '钱七', 5),
        (6, '孙八', 5),
        (7, '周九', 5),
        (8, '吴十', 8),
        (9, '郑十一', 9),
        (10, '王十二', 10);

我们通过 JOIN ON 关联查询下 id 为 5 的部门的所有员工：

select * from department
    join employee on department.id = employee.department_id
    where department.id = 5

把 id 为 5 的部门删掉：

DELETE FROM department WHERE id = 5;

再查看下员工表，可以看到销售部下的 3 个员工的部门被设置为 null 了：

一对多是不是还挺简单的？

确实，它和一对一没啥本质的区别。

4.3. 多对多查询

多对多，比如文章和标签：

之前一对多关系是通过在多的一方添加外键来引用一的一方的 id。

但是现在是多对多了，每一方都是多的一方。这时候是不是双方都要添加外键呢？

一般我们是这样设计：

文章一个表、标签一个表，这两个表都不保存外键，然后添加一个中间表来保存双方的外键。

这样文章和标签的关联关系就都被保存到了这个中间表里。

下面我们先创建这两个表。

创建article表：

CREATE TABLE `article` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `title` VARCHAR(50) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`id`)
) CHARSET=utf8mb4;

这里的 TEXT 是长文本类型，可以存储 65535 长度的字符串。

插入数据：

INSERT INTO `article` (`title`, `content`)
    VALUES
            ('文章1', '这是文章1的内容。'),
            ('文章2', '这是文章2的内容。'),
            ('文章3', '这是文章3的内容。'),
            ('文章4', '这是文章4的内容。'),
            ('文章5', '这是文章5的内容。');

创建Tag表：

CREATE TABLE `tag` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
);

插入数据：

INSERT INTO `tag` (`name`)
    VALUES
            ('标签1'),
            ('标签2'),
            ('标签3'),
            ('标签4'),
            ('标签5');

创建中间表：

CREATE TABLE `article_tag` (
  `article_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`article_id`, `tag_id`),
  INDEX `tag_id_idx` (`tag_id` ASC) VISIBLE,
  CONSTRAINT `article_id` 
  	FOREIGN KEY (`article_id`) 
  	REFERENCES `article` (`id`)
  	ON DELETE CASCADE 
  	ON UPDATE CASCADE,
  CONSTRAINT `tag_id` 
  	FOREIGN KEY (`tag_id`) 
  	REFERENCES `tag` (`id`)
  	ON DELETE CASCADE 
  	ON UPDATE CASCADE
)  CHARSET=utf8mb4;

插入数据：

INSERT INTO `article_tag` (`article_id`, `tag_id`)
    VALUES
    (1,1), (1,2), (1,3),
    (2,2), (2,3), (2,4),
    (3,3), (3,4), (3,5),
    (4,4), (4,5), (4,1),
    (5,5), (5,1), (5,2);

插入的数据如下：

我们查询出 id 为 1 的 article 的所有标签：

SELECT * FROM article a 
    JOIN article_tag at ON a.id = at.article_id
    JOIN tag t ON t.id = at.tag_id
    WHERE a.id = 1

这样查询出的就是 id 为 1 的 article 的所有标签：

删除文章1:

delete from article where id = 1;

可以看到关系也被级联删除了，这就是 CASCADE 的作用。

当然，删除的只是关系，并不影响 id=1 的标签。

这就是多对多数据的表设计、关联查询和级联方式。

5. 子查询

sql 还支持更复杂的组合，sql 可以嵌套 sql，也就是子查询。

还记得之前我们创建过一个student表：

select * from student;

我们想查询学生表中成绩最高的学生的姓名和班级名称。

这是不是就要分成两个 sql 语句：

先查询最高分：

SELECT MAX(score) FROM student;

再查询这个分数为这个最高分的学生：

SELECT name, class FROM student WHERE score = 95;

这两个 sql 合并就是子查询：

SELECT name, class FROM student WHERE score = (SELECT MAX(score) FROM student);

会得到和上面一样的结果。

比如查询成绩高于全校平均成绩的学生记录：

SELECT * FROM student WHERE score > (SELECT AVG(score) FROM student);

此外，子查询还有个特有的语法 EXISTS、NOT EXISTS。

我们用部门表和员工表来试一下。

修改一下员工表的数据：

这样就有的部门 2 个员工，有的部门 3 个员工，有的部门没有员工了。

查询有员工的部门：

SELECT name FROM department
    WHERE EXISTS (
        SELECT * FROM employee WHERE department.id = employee.department_id
    );

EXISTS 的作用：子查询返回结果，条件成立，反之不成立。

用 NOT EXISTS 来查询所有没有员工的部门：

SELECT name FROM department
    WHERE NOT EXISTS (
            SELECT * FROM employee WHERE department.id = employee.department_id
    );

子查询不止 select 里可以用，insert、update、delete 语句同样可以。

我们建个产品表：

CREATE TABLE product (
     id INT PRIMARY KEY,
     name VARCHAR(50),
     price DECIMAL(10,2),
     category VARCHAR(50),
     stock INT
);

插入几条数据：

INSERT INTO product (id, name, price, category, stock)
	VALUES 
		(1, 'iPhone12',6999.00, '手机',100),
		(2, 'iPad Pro',7999.00, '平板电脑',50),
		(3, 'MacBook Pro',12999.00, '笔记本电脑',30),
		(4, 'AirPods Pro',1999.00, '耳机',200),
		(5, 'Apple Watch',3299.00, '智能手表',80);

查询价格最高的产品的信息：

SELECT name, price FROM product WHERE price = (SELECT MAX(price) FROM product);

除了 select 之外，增删改也是可以用子查询的。

比如我们把每个产品分类的分类名、平均价格查出来放入另一个 avg_price_by_category 表。

先创建这个表：

CREATE TABLE avg_price_by_category (
 id INT AUTO_INCREMENT,
 category VARCHAR(50) NOT NULL,
 avg_price DECIMAL(10,2) NOT NULL,
 PRIMARY KEY (id)
);

然后把 product 产品表里的分类和平均价格查出来插入这个表：

INSERT INTO avg_price_by_category (category, avg_price) 
    SELECT category, AVG(price) FROM product GROUP BY category;

这就是 insert + select 结合使用的场景。

update 同样也可以使用 select 子查询。

比如之前的 department 和 employee 表，我们想把技术部所有人的 name 前加上 “技术-”，就可以这么写：

UPDATE employee SET name = CONCAT('技术-', name) 
    WHERE department_id = (
      SELECT id FROM department WHERE name = '技术部'
    );

还未更新完。。。欢迎持续关注