【MySQL】基本查询（表的增删查改+聚合函数）

CURD : Create(创建)，Update(更新)，Retrieve(读取)，Delete（删除）

一、Create

INSERT [INTO] table_name
	[(column [, column] ...)]
	VALUES (value_list) [, (value_list)] ...
	
value_list: value, [, value] ...

说明：

• INSERT [INTO]：INTO 是可选的，但通常会被包含，以提高语句的可读性。
• table_name：这是你想要插入数据的表的名称。
• (column1,column2, …)：这是可选的列名列表。如果提供了这个列表，那么 VALUES 中的值将按照这里指定的顺序被插入到相应的列中。如果省略了这个列表，那么 VALUES 中的值将按照表中列的顺序被插入，这要求 VALUES 中的值的顺序必须与表中列的顺序完全一致。
• VALUES：这是你要插入的值，每个值之间用逗号分隔。如果一次插入多行数据，每行的值应该用括号括起来，并且各行的值列表之间用逗号分隔。

实例：

create table if not exists stu(
	id int unsigned primary key auto_increment,
	num int not null unique comment '学号',
	name varchar(10) not null comment '姓名',
	telephone varchar(11) unique comment '电话号码'
);

1.1 单行数据 + 全列插入

插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致。

1.2 多行数据 + 指定列插入

插入两条记录，value_list 数量必须和指定列数量及顺序一致

注意，这里在插入的时候，可以不用指定id，但是就需要明确插入数据到那些列了，那么mysql会使用默认的值进行自增。

1.3 插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败，可以选择性的进行同步更新操作。

INSERT ... ON DUPLICATE KEY UPDATE
	column = value [, column = value] ...

说明：

• INSERT … ：这部分是标准的插入语句
• ON DUPLICATE KEY UPDATE: 这是一个条件子句，它指定了当插入操作因为唯一键或主键冲突而失败时应该执行的操作。
• column = value ：这里列出了在发生冲突时需要更新的列和它们的新值

下面先插入一行数据，然后再插入一行数据（主键/唯一键冲突），如果主键/唯一键冲突就将num和telephone修改为后面的值，我们看到表中的num和telephone确实发生了改变，但是id却没有改变，很明显就是在原来数据的基础上进行修改的。

命令执行完后，会显示影响了多少行，不同的行数也代表着不同的含义。

• 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，并且数据已经被更新

我们可以通过MySQL函数来查看影响数据的行数。

SELECT ROW_COUNT();

由于我上一条命令是查询语句，并未影响表中数据，所以函数的返回值为-1。

1.4 替换

• 当用户插入数据时，主键或者唯一键没有冲突，则直接插入
• 当用户插入数据时，主键或者唯一键如果冲突，则删除后再插入

下面我插入了两次数据，第一次没有冲突就直接插入了，第二次唯一键冲突了，删除数据后，再插入，由于这里的id值发生了改变，所以这里可以确定数据确实是被删除后，再插入的。

二、Retrieve

SELECT
	[DISTINCT] {* | {column [, column] ...}
	[FROM table_name]
	[WHERE ...]
	[ORDER BY column [ASC | DESC], ...]
	LIMIT ...

说明：

• SELECT [DISTINCT]：

  • SELECT 关键字用于从数据库中选择数据。
  • DISTINCT 是一个可选的关键字，用于返回唯一不同的值，即去除重复的记录。

• { * | {column [, column] …}}：

  • 星号（*）表示选择所有列。
  • 如果不使用星号，可以指定一个或多个列名，用逗号分隔，以选择特定的列。

• [FROM table_name]：

  • FROM 关键字指定了查询将要从哪个表中检索数据。
  • table_name 是表的名称。

• [WHERE …]：

  • WHERE 子句是可选的，用于过滤记录。
  • 可以在这里指定条件，只有满足条件的记录才会被选中。

• [ORDER BY column [ASC | DESC], …]：

  • ORDER BY 子句是可选的，用于对结果集进行排序。
  • column 指定了排序依据的列。
  • ASC 表示升序（默认），DESC 表示降序。
  • 可以根据多个列进行排序，列之间用逗号分隔。

• LIMIT …：

  • LIMIT 子句用于限制返回的记录数。
  • 可以指定一个数字来限制结果集的大小。
  • 例如，LIMIT 10 会返回前10条记录。

例子：

-- 创建表结构
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 全列查询

通常情况下不建议使用 * 进行全列查询

1. 查询的列越多，意味着需要传输的数据量越大
2. 可能会影响到索引的使用。（后面详细讲解索引）

2.1.2 指定列查询

指定列查询并不需要按照表中列的顺序来，任意顺序都可以。

2.1.3 查询字段为表达式

表达式的样式有以下几种：

• 表达式不包含字段
• 表达式包含一个字段
• 表达式包含多个字段

2.1.4 为查询结果指定别名

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

说明：

• column：这里指的是你想要从table_name中检索的列的名称。
• [AS] alias_name：AS关键字可以省略，用于为column指定的列名创建一个别名
• […]：这里的省略号表示你可以继续添加更多的列名及其可选的别名

2.1.5 结果去重

2.2 WHERE 条件

2.2.1 比较运算符

2.2.2 逻辑运算符

2.2.3 案例实操

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

select name,english from exam_result where english < 60;

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

select name,chinese from exam_result where chinese >= 80 and chinese <= 90;

select name,chinese from exam_result where chinese between 80 and 90

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

select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

select name,math from exam_result where math in(58,59,98,99);

2.2.3.4 姓孙的同学 及 孙某同学

select name from exam_result where name like '孙%'

2.2.3.5 语文成绩好于英语成绩的同学

select name,chinese,english from exam_result where chinese > english;

2.2.3.6 总分在 200 分以下的同学

select name,chinese,math,english,chinese+math+english as total from exam_result where chinese+math+english<200;

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

select name,chinese from exam_result where chinese>80 and name not like '孙%';

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

select name,chinese,math,english,chinese+math+english as total from exam_result where (chinese+math+english>200 and chinese<math and englissh > 80) or name like '孙_';

2.2.3.9 NULL 的查询

下面我创建了一个表，并向表中插入了3条数据，一条name为正常名字，一条name为空字符串，一条name为NULL。通过下面的查找来看，空串并不等于NULL，NULL代表怎么都没有，空串代表有但字符串中没有内容。

2.3 结果排序

2.3.1 结果排序的语法

SELECT ... FROM table_name [WHERE ...]
	ORDER BY column [ASC|DESC], [...];

说明：

• ASC 为升序（从小到大）
• DESC 为降序（从大到小）
• 默认为 ASC

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

2.3.2 案例实操

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

select name,math from exam_result order by math asc;

2.3.2.2 按同学姓名排序显示

select * from test order by name asc; // 升序
select * from test order by name desc; // 降序

注意：在进行比较的时候，NULL比任何值都要小

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

select name,chinese,math,english from exam_result order by math desc,english asc,chinese asc;

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

select name,chinese,math,english,chinese+math+english as total from exam_result order by total desc;

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

select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

2.4 筛选分页结果

2.4.1 筛选分页结果的语法

-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n

-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

注意：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

2.4.2 案例实操

2.4.2.1 按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

// 方法1
select * from exam_result limit 0,3;
select * from exam_result limit 3,3
select * from exam_result limit 6,3

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

三、Update

3.1 UPDATE 的语法

UPDATE table_name SET column = expr [, column = expr ...]
	[WHERE ...] [ORDER BY ...] [LIMIT ...]

• table_name：要更新的表的名称。
• column = expr：要更新的列及其新值。可以指定多个列及其新值，每个列赋值之间用逗号分隔。
• [WHERE …]：可选条件，用于指定哪些行应该被更新。如果省略，则表中的所有行都会被更新，更新全表的语句一定要慎用！
• [ORDER BY …]：可选条件，ORDER BY子句可以用来指定更新操作的顺序
• [LIMIT …]：可选条件，LIMIT子句用于限制更新操作影响的行数。

3.2 案例实操

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

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

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

update exam_result set math=60,chinese=70 where name='曹孟德';

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

update exam_result set math=math+30 order by chinese+math+english asc limit 3;

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

update exam_result set chinese=chinese*2;

注意：更新全表的语句一定要慎用！

四、Delete

4.1 删除数据（DELETE语法）

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

说明：

• table_name：指定要从中删除记录的表的名称。
• [WHERE …]：可选条件，用于指定哪些行应该被删除。如果省略，则表中的所有行都会被删除，删除全表的语句一定要慎用！
• [ORDER BY …]：可选条件，ORDER BY子句用于指定删除记录的顺序。
• [LIMIT …]：可选条件，LIMIT子句用于限制删除操作影响的行数。

DELETE特点：

1. 不仅仅可以对整表操作，还可以针对部分数据操作；
2. 不会重置 AUTO_INCREMENT 项

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

delete from exam_result where name='孙悟空';

4.1.2 删除整张表数据

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

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

下面我创建了一个表，并向表中插入数据，然后查看AUTO_INCREMENT 值为4，当我删除表中所有元素后，又插入一个元素，再次查看AUTO_INCREMENT 的值为5，显然AUTO_INCREMENT 的值没有被重置。

// 删除整张表数据
delete from 表名;

4.2 截断表

4.2.1 截断表（TRUNCATE语法）

TRUNCATE [TABLE] table_name

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

4.2.2 实操

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

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

下面我创建了一个表，并向表中插入数据，然后查看AUTO_INCREMENT 值为4，当我删除表中所有元素后，查看表创建命令时，发现没有了AUTO_INCREMENT 选项，然后又插入一个元素，再次查看AUTO_INCREMENT 的值为2，显然AUTO_INCREMENT 的值被重置。

五、插入查询结果

5.1 插入查询结果语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

说明：

• table_name: 指定了要插入数据的表。
• [(column [, column …])]: 这是可选的列列表，用于指定目标表中你想要插入数据的列。如果提供了列列表，SELECT 语句返回的列必须按照相同的顺序与这些列匹配。如果没有提供列列表，那么SELECT 语句返回的列将按顺序对应于目标表中的列（前提是列的数量和类型兼容）。
• SELECT …: 这部分是一个SELECT语句，用于指定要从哪个表（或哪些表）中选择数据，以及选择哪些列的数据。SELECT语句可以包含任何有效的查询条件，例如WHERE子句来过滤数据。

5.2 实操

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

下面我们创建了一个表duplicate_table，然后再向表中插入一些重复数据。现在我想将表中的重复数据只留一份，其余的重复数据全部删除。需要注意的是，我们需要将原本进行修改，而不是显示去重后的结果。

-- 创建原数据表
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');

这里我就来解决这个问题，首先我创建一个与duplicate_table表结构相同的空表no_duplicate_table，然后将duplicate_table去重后的数据插入到no_duplicate_table中，然后对两个表的表名进行重命名，使表no_duplicate_table变为表no_duplicate_table。

六、聚合函数

6.1 常见聚合函数

6.2 案例 实操

6.2.1 统计班级共有多少同学

select count(*) from exam_result;

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

select count(math) from exam_result;

6.2.3 统计数学成绩总分

select sum(math) from exam_result;

6.2.4 统计平均总分

select sum(chinese+math+english)/count(chinese+math+english) from exam_result;

select avg(chinese+math+english) from exam_result;

6.2.5 返回英语最高分

select max(english) from exam_result;

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

select min(math) from exam_result where math > 70;

七、group by子句的使用

7.1 group by子句的语法

select column1, column2, .. from table group by column;

特点：

• 分组的目的就是为了进行分组后，方便的进行聚合统计。
• 指定列名，实际分组使用该列不同的行数据进行分组的。
• 分组条件，在组内一定是相同的，所以可以聚合压缩。

7.2 案例实操

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);

insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

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

select deptno avg(sal),max(sal) from emp group by deptno;

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

select deptno,job,avg(sal),max(sal) from emp group by deptno,job;

7.2.3 显示平均工资低于2000的部门和它的平均工资

select deptno,avg(sal) myavg from emp group by deptno having myavg < 2000;

7.3 having VS where

• WHERE：在聚合前过滤行（对原始数据生效）。
• HAVING：在聚合后过滤分组（对 GROUP BY 结果生效）。

八、OJ练习

8.1 SQL233 批量插入数据

insert into actor values 
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

8.2 SQL207 找出所有员工当前薪水salary情况

select distinct salary from salaries 
order by salary desc;

8.3 SQL200 查找最晚入职员工的所有信息

select * from employees 
where hire_date = (select max(hire_date) from employees);

8.4 SQL201 查找入职员工时间升序排名的情况下的倒数第三的员工所有信息

select * from employees 
where hire_date = 
(select distinct hire_date from employees 
order by hire_date desc limit 1 offset 2)

8.5 SQL206 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

select emp_no,count(emp_no) mycnt 
from salaries group by emp_no having mycnt > 15;

8.6 182. 查找重复的电子邮箱

select email from Person 
group by email having count(email) >= 2;

8.7 595. 大的国家

select name,population,area 
from World where area >= 3000000 or population >= 25000000;

8.8 177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;

  RETURN (
    select distinct salary from Employee order by salary desc limit 1 offset N
  );
END

8.9 面试题：SQL查询中各个关键字的执行先后顺序

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

结尾

如果有什么建议和疑问，或是有什么错误，大家可以在评论区中提出。
希望大家以后也能和我一起进步！！🌹🌹
如果这篇文章对你有用的话，希望大家给一个三连支持一下！！🌹🌹