MySQL 表的增删改查(基础篇) · CRUD增删改查 · insert 增加 · select 查询 · update 修改 · delete 删除

---

一、CRUD (增删改查)

CRUD 即：
	增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

---

二、增加

案例：
添加一个学生表

create table student(
	id int,
	sn int commit '学号',
	name varchar(20),
	qq_mail varchar(20)
);

2.1 单行数据 + 全列插入

-- 插入两条数据，括号中的字段数量 必须和 表中列的数量以及顺序一致。
insert into student values(100, 1000, '唐三藏', null);
insert into student values(101, 1001, '孙悟空', '111@qq.com');

2.2 多行数据 + 指定列插入

-- 插入两条记录，数量必须和指定列数量以及顺序一致
-- 忽略了邮箱数据列。
insert into student(id, sn, name) values 
	(102, 20001, '曹孟德'),
	(103, 20002, '孙仲谋');

ps.
一条 SQL 语句插入多条数据好？还是多条 SQL 语句插入多条数据好？

答:
前者好，因为一个请求里面带多个数据，服务器只需要对一个 SQL 进行解析，并执行插入操作即可。
后者需要多次解析 SQL，相比之下效率低；网络传输的话，资源的开销就更大了。

2.3 插入中文遇到的编码问题

我们可以通过一个指令
来查看当前数据库的字符集~

show variables like '%character%';

把这两个修改成 utf8mb4，后重启 MySQL 就可以解决中文乱码问题。

之前创建过的数据库只能删除重新创建，才能使用新设置的编码格式。

---

三、查询

查询，是整个 SQL 中最复杂的环节。
使用的方法和技巧也是非常多种多样。

案例：
先插入一些数据，以供查询。

-- 创建考试成绩表
create table exam_result(
	id int,
	name varchar(20),
	chinese decimal(3,1),
	math decimal(3,1),
	english decimal(3,1)
);

-- 插入测试数据
insert into exam_result (id,name,chinese,math,english) values
	(1, '唐三藏', 67, 98, 56),
	(2, '孙悟空', 87.5, 78, 77),
	(3, '猪悟能', 88, 98.5, 90),
	(4, '曹孟德', 82, 84, 67),
	(5, '刘玄德', 55.5, 85, 45),
	(6, '孙权', 70, 73, 78.5),
	(7, '宋公明', 75, 65, 30);

3.1 全列查询 *

-- 虽然是最简单的查询语句，也可能带来麻烦
-- 1. 查询的列越多，意味着需要传输的数据量越大，效率低。
-- 2. 可能会影响到索引的使用。
-- 3. 实际开发环境中，使用此查询，可能把服务器带宽全部吃满，导致其它业务挂掉...

select * from exam_result；

查询结构类似一个 “表”，但这只是一个 “临时表”。

表只能在内存存储一下，执行完打印任务就没了，不会持久化存储。

3.2 指定列查询

-- 指定列的顺序不需要按照定义表的顺序来
select id, name, english from exam_result;

3.3 查询字段为表达式

-- 表达式不包含字段
select id, name, 10 from exam_result;

-- 表达式包含一个字段
select id, name, english + 10 from exam_result;

-- 表达式包含多个字段
select id, name, chinese + math + english from exam_result;

3.4 别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称。

语法：

select 字段名 [as] 别名 [...] from 表名;

示例：

select id, name, chinese+math+english as 总分 from exam_result;

3.5 去重 distinct

使用 distinct 关键字对某列数据进行去重。

-- 查询所有math成绩
select math from exam_result;

-- 去掉math成绩里面的重复数据
select distinct math from exam_result;

3.6 排序 order by

语法：

-- asc 为升序(从小到大)(默认的)
-- desc 为降序(从大到小)
select ... from 表名 [where ...] order by  column [asc | desc], [...];

说明：

1. 没有 order by 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面。

示例一

-- 查询姓名和chinese成绩，按照chinese排序显示
select name,chinese from exam_result order by chinese;
-- 排序改为降序显示
select name,chinese from exam_result order by chinese desc;

运行结果：

示例二

-- 查询同学及总分，由高到低
select name, chinese+math+english from exam_result order by chinese+math+english desc;

-- 查询同学及总分，由高到低，用上别名排序
select name,chinese+math+english as total from exam_result order by total desc;

运行结果：

示例三

-- 查询各门成绩，依次按照 数学降序，英语升序，语文升序的方式显示
select name, math, english, chinese from exam_result order by math desc, english, chinese;

运行结果：

3.7 条件查询 where

比较运算符：

逻辑运算符：

ps.
where 条件可以使用表达式，但不能使用别名。
and 的优先级高于 or，在同时使用时，需要使用小括号 ( ) 包裹优先执行的部分。

示例： where 基本查询

-- 查询英语不及格的同学以及英语成绩
select name,english from exam_result where english < 60;

-- 查询语文成绩好于英语成绩的同学
select name,chinese,english from exam_result where chinese > english;

-- 查询总分在 200 分以下的同学
select name,chinese+english+math as 总分 from exam_result where chinese+math+english < 200;

运算结果：

示例： AND 与 OR

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;

-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;

-- 观察AND 和 OR 的优先级：
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

运算结果：

SQL 中的条件，相当于 “过滤器”，而不是 “逻辑分支”。

SQL 中最核心的是 select，select 最核心的就是 where 条件。

3.8 范围查询 between_and_

1. between…and…

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

2. in

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
-- 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;

3.9 模糊查询 like

-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权

-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权

3.10 NULL 的查询 IS[NOT] NULL

-- 查询 qq_mail 已知的同学姓名
SELECT name, qq_maill FROM student WHERE qq_maill IS NOT NULL;

-- 查询 qq_mail 未知的同学姓名
SELECT name, qq_maill FROM student WHERE qq_maill IS NULL;

3.11 分页查询 LIMIT

当查询结果比较多的时候，把结果分成很多 “页”。

简单写法：

select * from exam_result limit 3;

搭配offset使用：

表示从第几条数据开始，往后进行查找~

select * from exam_result limit 3 offset 3;
select * from exam_result limit 3 offset 6;

---

四、修改

修改数据库中的数据。

update exam_result set math = 88 where name = '孙悟空';

-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
update exam_result set math = 60,chinese = 70 where name = '曹孟德';

-- 将英语成绩倒数前三的 3 位同学的英语成绩加上 10 分
update exam_result set english = english + 10 order by english limit 3;

-- 将所有同学的数学成绩更新为原来的0.5倍
update exam_result set math = math / 2;

---

五、删除

删除操作同样是非常危险的！！！ 可能会影响到其它数据

-- 删除表中孙悟空的数据
delete from exam_result where name = '孙悟空';

-- 删除整张表数据
-- 准备测试表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (
	id INT,
	name VARCHAR(20)
);

-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

-- 删除整表数据
DELETE FROM for_delete;

通过 delete from 的方式来删表，这种操作会一条一条的删除。
如果数据比较多，效率低，相比之下直接删表 drop 的速度会快很多。