【MySQL】增删改查

目录

一、新增（Create）

单行数据 + 全列插入

多行数据 + 指定列插入

插入时间

二、查询（Retrieve）

全列查询

指定列查询

查询字段为表达式

别名

去重：DISTINCT

排序：ORDER BY

条件查询：WHERE

基本查询

 AND与OR：

 范围查询

模糊查询：LIKE

NULL 的查询：IS [NOT] NULL

分页查询：LIMIT

三、修改

四、删除

---

一、新增（Create）

👁‍🗨案例

1. -- 创建一张学生表
2. DROP TABLE IF EXISTS student;
3. CREATE TABLE student (
4. id INT,
5. sn INT comment '学号',
6. name VARCHAR(20) comment '姓名',
7. qq_mail VARCHAR(20) comment 'QQ邮箱'
8. );

复制代码

单行数据 + 全列插入

1. -- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
2. INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
3. INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

复制代码

多行数据 + 指定列插入

1. -- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
2. INSERT INTO student (id, sn, name) VALUES
3. (102, 20001, '曹孟德'),
4. (103, 20002, '孙仲谋');

复制代码

🍃SQL中表示字符串，既可以使用单引号 ' ，也可以使用双引号 " 

🍂也可插入中文字符串，但需要确保，创建数据库的时候，指定了字符集是 utf8/gbk 等能支持中文的字符集。像MySQL5默认的字符集是拉丁文。因此，如果发现插入的中文数据出现报错，大概率是创建数据库的时候没有正确指定字符集

插入时间

🎸有的时候插入的时间日期，希望就是"当前时刻"，SQL作为一个编程语言，也支持一些库函数，now()能获取到当前的时间日期

二、查询（Retrieve）

👁‍🗨案例

1. -- 创建考试成绩表
2. DROP TABLE IF EXISTS exam_result;
3. CREATE TABLE exam_result (
4. id INT,
5. name VARCHAR(20),
6. chinese DECIMAL(3,1),
7. math DECIMAL(3,1),
8. english DECIMAL(3,1)
9. );
10. -- 插入测试数据
11. INSERT INTO exam_result (id,name, chinese, math, english) VALUES
12. (1,'唐三藏', 67, 98, 56),
13. (2,'孙悟空', 87.5, 78, 77),
14. (3,'猪悟能', 88, 98.5, 90),
15. (4,'曹孟德', 82, 84, 67),
16. (5,'刘玄德', 55.5, 85, 45),
17. (6,'孙权', 70, 73, 78.5),
18. (7,'宋公明', 75, 65, 30);

复制代码

全列查询

1. --通常情况下不建议使用 * 进行全列查询
2. -- 1. 查询的列越多，意味着需要传输的数据量越大；
3. -- 2. 可能会影响到索引的使用。（索引待后面课程讲解）
4. SELECT * FROM exam_result;

复制代码

🧾查询出这个表中的所有的行和所有的列，* 称为 "通配符"，可以指代所有的列

⚠️select* 是一个很危险的操作

如果表比较小，select* 都无所谓，一旦表非常大，像干万/亿级别的数据量，此时，进行select* 就会产生大量的硬盘IO和网络IO。而硬盘和网卡，读写速度，都是存在上限的，一旦触发大规模的 select* ，意味着很可能就把你的硬盘/网卡带宽给吃满了（堵车）。其他的客户端尝试访问数据库，访问操作就无法正常进行了。如果你针对公司的生产环境进行select*就很可能使其他的用户访问数据库的时候出现访问失败的情况。比如你们做的是一个 "支付宝" 支付系统，用户访问不了数据库会是什么后果呢？当前阶段，数据库中，没啥数据select* 就无所谓了。以后再工作中，尤其是"生产环境”，一定要慎重。

指定列查询

1. --指定列的顺序不需要按定义表的顺序来
2. SELECT id, name, english FROM exam_result;

复制代码

🚀实际开发中，一个表有十几列，甚至几十列都是很有可能的。比如当前一共有20列，只需要关注两列，.则使用指定列查询，得到的数据量就比全列查询要少很多。

查询字段为表达式

1. -- 表达式不包含字段
2. SELECT id, name, 10 FROM exam_result;

复制代码

1. -- 表达式包含一个字段
2. SELECT id, name, english + 10 FROM exam_result;

复制代码

1. -- 表达式包含多个字段
2. SELECT id, name, chinese + math + english FROM exam_result;

复制代码

🚗多个列之间可进行加减乘除运算

🚕执行 select 就会遍历每一行，取出需要的列，把列代入到表达式中。这样的结果，只是数据库查询过程中，生成的 "临时表" 数据库本体（数据库服务器硬盘上的数据）是没有任何改变的

🚙表达式查询，只能针对列和列之间进行运算，行和行之间的运算，后面会介绍 "聚合查询"

别名

如果表达式简单，一眼就能看明白；如果表达式比较复杂，就没法直观观察了。此处就可以给表达式取别名，此时别名就是查询结果的列名。

SELECT column [AS] alias_name [...] FROM table_name;

1. -- 结果集中，表头的列名=别名
2. SELECT name, chinese + math + english 总分 FROM exam_result;

复制代码

去重：DISTINCT

1. -- 98 分重复了
2. SELECT math FROM exam_result;
3. +--------+
4. | math |
5. +--------+
6. | 98 |
7. | 78 |
8. | 98 |
9. | 84 |
10. | 85 |
11. | 73 |
12. | 65 |
13. +--------+
14. 7 rows in set (0.00 sec)
15. --去重结果
16. SELECT DISTINCT math FROM exam_result;
17. +--------+
18. | math |
19. +--------+
20. | 98 |
21. | 78 |
22. | 84 |
23. | 85 |
24. | 73 |
25. | 65 |
26. +--------+
27. 6 rows in set (0.00 sec)

复制代码

🥎必须所有列都是重复才会触发去重，即多行数据如果出现相同的值，就会只保留一份

⚾️去重都是针对 "临时表"，硬盘上的数据没有任何影响

排序：ORDER BY

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

SELECT ... FROM table_name [WHERE ...]

ORDER BY column [ASC|DESC], [...];

NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

1. -- 查询同学姓名和 qq_mail，按 qq_mail 排序显示
2. SELECT name, qq_mail FROM student ORDER BY qq_mail;
3. SELECT name, qq_mail FROM student ORDER BY qq_mail DESC;

复制代码

使用表达式及别名排序

1. -- 查询同学及总分，由高到低
2. SELECT name, chinese + english + math FROM exam_result
3. ORDER BY chinese + english + math DESC;
5. SELECT name, chinese + english + math total FROM exam_result
6. ORDER BY total DESC;

复制代码

可以对多个字段进行排序，排序优先级随书写顺序

1. -- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
2. SELECT name, math, english, chinese FROM exam_result
3. ORDER BY math DESC, english, chinese;

复制代码

🍇没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。如果某一列有相同值，其他记录的顺序都是不可预期的

🍒排序，也是只是针对临时表结果进行排序，不会影响到硬盘上原始的数据

条件查询：WHERE

🌮SQL没有 == 这个运算符，SQL中使用 = 表示比较相等

🌯NULL参与运算，结果也会得到NULL

🍝WHERE条件可以使用表达式，但不能使用别名

🍱AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

🍛与NULL比较不能直接用 =，如 id<=>NULL 

基本查询

1. -- 查询英语不及格的同学及英语成绩 ( < 60 )
2. SELECT name, english FROM exam_result WHERE english < 60;
3. +-----------+---------+
4. | name | english |
5. +-----------+---------+
6. | 唐三藏 | 56.0 |
7. | 刘玄德 | 45.0 |
8. | 宋公明 | 30.0 |
9. +-----------+---------+
10. 3 rows in set (0.01 sec)
12. -- 查询语文成绩好于英语成绩的同学
13. SELECT name, chinese, english FROM exam_result WHERE chinese > english;
14. +-----------+---------+---------+
15. | name | chinese | english |
16. +-----------+---------+---------+
17. | 唐三藏 | 67.0 | 56.0 |
18. | 孙悟空 | 87.5 | 77.0 |
19. | 曹孟德 | 82.0 | 67.0 |
20. | 刘玄德 | 55.5 | 45.0 |
21. | 宋公明 | 75.0 | 30.0 |
22. +-----------+---------+---------+
23. 5 rows in set (0.00 sec)
25. -- 查询总分在 200 分以下的同学
26. SELECT name, chinese + math + english 总分 FROM exam_result
27. WHERE chinese + math + english < 200;
28. +-----------+--------+
29. | name | 总分 |
30. +-----------+--------+
31. | 刘玄德 | 185.5 |
32. | 宋公明 | 170.0 |
33. +-----------+--------+
34. 2 rows in set (0.00 sec)

复制代码

 AND与OR：

1. -- 查询语文成绩大于80分，且英语成绩大于80分的同学
2. SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
3. +------+-----------+---------+------+---------+
4. | id | name | chinese | math | english |
5. +------+-----------+---------+------+---------+
6. | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
7. +------+-----------+---------+------+---------+
8. 1 row in set (0.01 sec)
10. -- 查询语文成绩大于80分，或英语成绩大于80分的同学
11. SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
12. +-----------+--------+
13. | name | 总分 |
14. +-----------+--------+
15. | 刘玄德 | 185.5 |
16. | 宋公明 | 170.0 |
17. +-----------+--------+
18. 2 rows in set (0.00 sec)
20. -- 观察AND 和 OR 的优先级：
21. SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
22. +------+-----------+---------+------+---------+
23. | id | name | chinese | math | english |
24. +------+-----------+---------+------+---------+
25. | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
26. | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
27. | 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
28. | 6 | 孙权 | 70.0 | 73.0 | 78.5 |
29. +------+-----------+---------+------+---------+
30. 4 rows in set (0.00 sec)
32. SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;
33. +------+-----------+---------+------+---------+
34. | id | name | chinese | math | english |
35. +------+-----------+---------+------+---------+
36. | 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
37. | 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
38. | 6 | 孙权 | 70.0 | 73.0 | 78.5 |
39. +------+-----------+---------+------+---------+
40. 3 rows in set (0.00 sec)

复制代码

 范围查询

1. --查询语文成绩在 [80, 90] 分的同学及语文成绩
2. SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
3. +-----------+---------+
4. | name | chinese |
5. +-----------+---------+
6. | 孙悟空 | 87.5 |
7. | 猪悟能 | 88.0 |
8. | 曹孟德 | 82.0 |
9. +-----------+---------+
10. 3 rows in set (0.00 sec)
11. -- 使用 AND 也可以实现
12. SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;
14. -- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
15. SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
16. +-----------+------+
17. | name | math |
18. +-----------+------+
19. | 唐三藏 | 98.0 |
20. +-----------+------+
21. 1 row in set (0.01 sec)
22. -- 使用 OR 也可以实现
23. SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math
24. = 98 OR math = 99;

复制代码

模糊查询：LIKE

1. -- % 匹配任意多个（包括 0 个）字符
2. SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权
3. +-----------+
4. | name |
5. +-----------+
6. | 孙悟空 |
7. | 孙权 |
8. +-----------+
9. 2 rows in set (0.01 sec)
11. -- _ 匹配严格的一个任意字符
12. SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权
13. +--------+
14. | name |
15. +--------+
16. | 孙权 |
17. +--------+
18. 1 row in set (0.00 sec)

复制代码

NULL 的查询：IS [NOT] NULL

1. -- 查询 qq_mail 已知的同学姓名
2. SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;
3. -- 查询 qq_mail 未知的同学姓名
4. SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

复制代码

分页查询：LIMIT

select* 容易查询出太多的数据，使机器挂了，通过指定列查询，虽然你查到的结果是变少了很多，但是如果行数足够多的话，仍然是有可能会把机器搞出问题的。此时更稳妥的做法，就是 "分页查询"，限制一次查询，最多能查到多少个记录

1. -- 起始下标为 0
2. -- 从 0 开始，筛选 n 条结果
3. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
4. -- 从 s 开始，筛选 n 条结果
5. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
6. -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
7. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

复制代码

指定 limit 的时候，还可以搭配 offset 来使用，offset表示是从下标为几这样的记录开始算 limit，这样的操作过程相比于不加上limit，效率高很多，针对一个行数非常多的表，使用 limit 仍然是一个高效的操作

1. --第 1 页
2. SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
3. OFFSET 0;
4. -- 第 2 页
5. SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
6. OFFSET 3;
7. -- 第 3 页，如果结果不足 3 个，不会有影响
8. SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
9. OFFSET 6;

复制代码

三、修改

UPDATE table_name SET column = expr [, column = expr ...]

[WHERE ...] [ORDER BY ...] [LIMIT ...]

1. -- 将孙悟空同学的数学成绩变更为 80 分
2. UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
4. -- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
5. UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
7. -- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
8. UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT3;
10. -- 将所有同学的语文成绩更新为原来的 2 倍
11. UPDATE exam_result SET chinese = chinese * 2;

复制代码

这样的修改是 "特久有效"，真正在改硬盘了。一定要确保，update的修改是改对了，改出问题来就麻烦。指定update的时候，如果当前不指定任何条件，就会针对所有的行都能生效(把整个表都给改了)

四、删除

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

1. -- 删除孙悟空同学的考试成绩
2. DELETE FROM exam_result WHERE name = '孙悟空';
3. -- 删除整张表数据
4. -- 准备测试表
5. DROP TABLE IF EXISTS for_delete;
6. CREATE TABLE for_delete (
7. id INT,
8. name VARCHAR(20)
9. );
10. -- 插入测试数据
11. INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
12. -- 删除整表数据
13. DELETE FROM for_delete;

复制代码

⚠️⚠️⚠️delete也是一个危险操作，会把筛选出来的行删除掉，而且修改的是硬盘，一旦删掉了，数据就无了。这里的匹配，是匹配到几个就删除几个，条件如果不写，就是删除所有数据

delete from 是删除表里的数据，表还存在（表中内容为空）
drop table 是连数据带里面的表，都删除了