MySQL——5、基本查询

CURD：Create(创建)，Update(更新)，Retrieve(读取)，Delete(删除)。

1、Create

这是用来向表中插入数据的，表明后面跟指定列，如果没有指定列默认就是全列插入，values后面跟要插入列的数据。
首先创建一张学生表：

1.1、单行数据+全列插入

单行指定列插入：

单行全列插入：

into可以省略：

1.2、多行数据+指定列插入

多行全列插入：

多行指定列插入：

1.3、插入否则更新

我们在插入的时候可能会发生以下两种情况：主键冲突、唯一键冲突。

如果插入的时候发生了冲突，我们想让它去修改值，而不是直接无脑的拦住我，可以使用下面的语法：

下面这种情况插入默认是直接拦截你的，因为主键发生了冲突，但是我们在后面添加on duplicate key update，如果发生了冲突可以对其他属性进行修改，这样不会直接拦截报错。

另外，还可以根据执行后的响应结果分辨是哪种情况：
由于上面是插入冲突了，进行数据更新，所以是两行受影响

当插入一行且不存在冲突时，就是一行受影响。
接着我们再插入肯定会发生冲突，而此时就是更新数据，所以是两行受影响。
第三次插入时也会发生冲突，但是更新的数据和原来的一样，所以零行受影响。

另外，我们可以使用row_count()获取受影响的行数：

1.4、替换

上述语法只不过是把insert into替换成了replace into。

第一次插入的时候由于没有存在冲突，所以直接插入，一行受影响。第二次插入的时候存在冲突，所以会把原来那一行删除掉然后再重新插入，因此两行受影响。同时可以看到新的id为20，第一次插入的时候为19，删除后再次插入就变成20了。

---

2、Retrieve

首先需要做一下准备工作，创建一张表并插入一些数据：

-- 创建表结构
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);

-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

2.1、select列

2.1.1、全列查询

*和Linux里的很像，表示一种通配。
通常情况下不建议使用*进行全列查询：
1、查询的列越多，意味着需要传输的数据量越大。
2、可能会影响到索引的使用。

2.1.2、指定列查询

2.1.3、查询字段为表达式

2.1.4、为查询结果指定别名

并且as可以省略：

2.1.5、结果去重
默认查询数学成绩这一列会出现重复的数据，如果想对数据进行去重可以加上distinct

---

2.2、where条件

=可以用来比较数字和字符串，但是不能用来判断是否为NULL，如果要判断是否为NULL，应该使用<=>或is null：

同样的，!=和<>也是NULL不安全的，判断一个值不为NULL应该使用is not NULL。

2.2.1、英语不及格的同学及英语成绩 ( < 60 )

2.2.2、语文成绩在 [80, 90] 分的同学及语文成绩

2.2.3、数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

2.2.4、姓孙的同学及孙某同学

2.2.5、语文成绩好于英语成绩的同学

2.2.6、总分在200分以下的同学

下面这种重命名为什么不行？因为执行该条语句的顺序是：1、先知道要从哪个表中获取数据。2、筛选的条件。3、筛选的内容。因此total是在重命名之前的，所以就不认识total了。

2.2.7、语文成绩>80并且不姓孙的同学

2.2.8、孙某同学，否则要求总成绩>200并且语文成绩<数学成绩并且英语成绩>80

2.2.9、NULL的查询
前置工作：

---

2.3、结果排序

注意：没有ORDER BY子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

2.3.1、同学及数学成绩，按数学成绩升序显示

2.3.2、查询test表，按名字升序排序

这里主要是要说明NULL比任何值都要小。

2.3.3、查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

另外我们也可以省略asc或desc，默认采用asc升序排序。

2.3.4、查询同学及总分，由高到低

我们发现这里又可以使用别名了？为什么在这里可以使用别名，在where子句不能使用别名？
执行顺序是：1、先知道从哪个表中查。2、根据where子句的条件去筛选。3、要筛选出哪些列信息。4、进行排序。
所以排序是在最后进行的，此时已经被重命名了，所以可以使用重命名。

2.3.5、查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

---

2.4、筛选分页结果

实现分页查找：

比如我们想根据三个科目总成绩来查找第一名，或者查找前n名：

---

3、Update

3.1、将孙悟空同学的数学成绩变更为80分

3.2、将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分

3.3、将总成绩倒数前三的3位同学的数学成绩加上30分

3.4、将所有同学的语文成绩更新为原来的2倍

---

4、Delete

4.1、删除数据

4.1.1、删除孙悟空同学的考试成绩

首先默认不加条件就会把整张表都删掉。

4.1.2、删除总分倒数第一同学的考试成绩

4.1.3、删除整张表数据，慎用！！
首先创建一张测试表：

-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

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

当我们删除后，查询不到表的数据，表已经为空。并且我们注意到删除前后的自增计数器是不变的。

---

4.2、截断表

创建一个表进行测试：

-- 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

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

truncate清空表数据后，我们发现自增计数器也不见了。

接着我们再插入数据发现自增主键又从1开始了，并且这时候自增计数器的值为2。
总结：delete不会重置自增计数器，会记录下操作的日志。truncate会重置自增计数器，但是不会记录下操作日志。所以truncate比delete快。

---

5、插入查询结果

案例：删除表中的的重复复记录，重复的数据只能有一份

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));

-- 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

我们想要去除重复的数据，但是没办法直接delete from删除，因为会把重复的所有数据都删掉。
所以我们的做法是创建一个和duplicate_table结构一样的表，然后查询duplicate_table去重后的数据，将查询的数据插入到新建的表中。然后把duplicate_table重命名，再把新建的表名字改为duplicate_table，这样就完成了数据去重。

然后对表进行重命名，修改表明有以下两种方式：

---

6、聚合函数

6.1、统计班级共有多少同学

6.2、统计本次考试的数学成绩分数个数

先对数学成绩做去重，然后再聚合统计。

6.3、统计数学成绩总分

再比如统计数学的平均分：

6.4、统计平均总分

6.5、返回英语最高分


另外我们发现在前面加上name是不行的。

6.6、返回>70分以上的数学最低分

---

7、group by子句的使用

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）：EMP员工表、DEPT部门表、SALGRADE工资等级表。可以上网查。
MySQL数据库测试文件
或者在我的gitee仓库下下载scott_data.sql文件，source还原出数据库即可。

6.7.1、如何显示每个部门的平均工资和最高工资

第一次查询，我们是把整个表当作一个组，查找这一个组里面薪资最高和平均薪资。第二次查询我们使用了group by通过部门号deptno进行分组，由于emp表中部门号有3种，因此根据部门号分成三个组然后再把这三个组分别进行聚合统计，计算出每个组的最高薪资和平均薪资。
分组的目的：分组之后方便进行聚合统计。

分组指定的是列名，实际上分组根据的是该列不同的数据来进行分组的。分组的条件为deptno，组内的deptno值一定都是相同的，所以可以被聚合统计。分组我们看成"分表"，把一张表按照条件在逻辑上拆成了多个子表，然后对各自的子表进行聚合统计。

6.7.2、显示每个部门的每种岗位的平均工资和最低工资

如果我还想看一下名字呢？

我们发现直接报错了。一般只有在group by后面出现的，或者是聚合函数才能在select后面出现。

6.7.3、显示平均工资低于2000的部门和它的平均工资
处理：1、统计出每一个部门的平均工资，先聚合统计出结果。2、再进行判断，对聚合统计的结果判断是否低于2000。

having是对聚合后的数据进行条件筛选。

6.7.4、having VS where的区别和理解？执行顺序？

我们发现把having换成where后就报错了，把where换成having是可以查出来的，虽然可以查出来，但是严重不推荐这么写。

需求：显示平均工资低于2000的部门和它的平均工资并且SMITH员工不参与统计。

where和having都是条件筛选，区别在于它们进行条件筛选的阶段是不同的。
另外不要单纯的认为，只有磁盘上的表结构导入到mysql，真实存在的表才叫做表。按条件筛选之后、分组之后等全都是表。中间过程包括最后结果，在我们看来都是逻辑上的表。MySQL一切皆表。未来只要我们能够处理好单表的CURD，所有的SQL场景，我们全部都能用统一的方式进行。

面试题：SQL查询中各个关键字的执行先后顺序 from > on > join > where > group by > with > having > select > distinct > order by > limit。

---

8、实战OJ

牛客：批量插入数据

牛客：找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示

牛客：查找最晚入职员工的所有信息

牛客：查找入职员工时间排名倒数第三的员工所有信息

牛客：查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

LeetCode：182. 查找重复的电子邮箱

LeetCode：595. 大的国家

LeetCode：177. 第N高的薪水