MySQL - CRUD增查改删基础与进阶超详细（附例子）

CRUD基础

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)

1.Create插入数据

格式

-- 单行插入 
insert into 表名 (字段1, ..., 字段N) values (value1, ..., value N);

-- 多行插入 
insert into 表名 (字段1, ..., 字段N) values (value1, ...), (value2, ...), (value3, ...);    

例：

-- 创建一张学生表 
drop table if exits student;
create table student (  
	 id INT,   
	sn INT comment '学号',  
	name VARCHAR(20) comment '姓名',  
	qq_mail VARCHAR(20) comment 'QQ邮箱'
 );

单行数据全插：

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

多行数据指定插：

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

---

2.Retrieve查询

2.1 全列查询

Select * from 表名

2.2 指定列查询

Select 字段名，字段名… from 表名

2.3 查询字段为表达式

-- 表达式不包含字段10 （结果：第三列全为 10）
SELECT id, name, 10 FROM exam_result; 

-- 表达式包含字段english （第三列数据+10）
SELECT id, name, english + 10 FROM exam_result; 

-- 表达式包含多个字段 （第三列为chinese + math + english）
SELECT id, name, chinese + math + english FROM exam_result; 

2.4 别名

-- 第三列结果为：chinese + math + english，表头名字为：总分
SELECT id, name, chinese + math + english 总分 FROM exam_result

2.5 某列去重查询：distinct

SELECT distinct 字段  from 表名;

2.6 排序：ORDER BY

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC 
SELECT ... FROM table_name [WHERE ...]
	ORDER BY 字段 [ASC|DESC], [...];

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

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

SELECT name, math, english, chinese FROM exam_result 
ORDER BY math DESC, english;
（对数学进行降序排序，如数学相等则按英语升序排）

order可以对表达式及别名排序

---

2.8 条件查询：WHERE

逻辑运算符：

注：

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

模糊查询：LIKE

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

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;

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

---

2.9 日期查询
例:
建借阅图书表：

drop table if exists record;
create table record(
	name varchar(20),
	-- 开始时间
	start_time timestamp default now(),
	-- 结束时间
	end_time timestamp default now()
);

插入数据：

insert into record values("白骨精", "2020-04-01 14:00:00", "2020-04-19 00:00:00");
insert into record values("狐狸精", "2020-04-10 14:00:00", "2020-04-23 00:00:00");
insert into record values("蜘蛛精", "2020-04-20 14:00:00", "2020-04-22 00:00:00");
insert into record values("鸡精", "2020-04-18 14:00:00", "2020-04-19 00:00:00");

要求：

查询一个[a, b]，a代表查询起始日期，b代表结束日期。要求是每行数据，如果和[a, b]产生交集才显示。
[a,b] = [2020-04-05 00:00:00 ，2020-04-17 00:00:00]

分析：
匹配开始条件：查询结束时间大于等于每列数据的开始时间。
匹配结束条件：查询开始的时间小于等于每列数据的结束时间。
语句：

 select * from record where start_time <= '2020-04-17 00:00:00' and end_time >= '2020-04-05 00:00:00';

---

分页查询
（下标从0开始）

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

例:

-- 查询exam_result表中，chinese降序排列，从下标1开始，展示1个数据。 即成绩第二
select * from exam_result order by chinese desc limit 1,1;

---

3.修改（Update）

格式：

update 表 set 字段1=value1, 字段2=value2... where 条件

例:

-- 将孙悟空同学的数学成绩变更为 80 分 
UPDATE exam_result SET math = 80 WHERE name = '孙悟空'; 

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

 -- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分 
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3; 

-- 将所有同学的语文成绩更新为原来的 2 倍 
UPDATE exam_result SET chinese = chinese * 2; 

---

4.DELETE 删除

格式：

delete from 表 where 条件

例：

-- 删除孙悟空同学的考试成绩 
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;

---

CRUD进阶

1.数据库约束类型

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值
• DEFAULT - 规定没有给列赋值时的默认值。
• PRIMARY KEY（主键） - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。 对于整数类型的主键，常配搭自增长auto_increment来使用。插入对应字段不给值时，使用最大值+1

 id INT  PRIMARY KEY auto_increment;

• FOREIGN KEY - 外键用于关联其他表的主键或唯一键
  语法： foreign key (字段名) references 主表(列)

例:

-- 创建班级表 id为主键： 
DROP TABLE IF EXISTS classes; 
CREATE TABLE classes ( 
	id INT PRIMARY KEY auto_increment, 
	name VARCHAR(20) DEFAULT 'unkown' 
 ); 

-- 创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键， classes_id为外键，关联班级表id
DROP TABLE IF EXISTS student; 
CREATE TABLE student (   
	id INT PRIMARY KEY auto_increment,    
	name VARCHAR(20) DEFAULT 'unkown', 
	classes_id int, 
	FOREIGN KEY (classes_id) REFERENCES classes(id) 
); 

• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句

2.新增

语法：insert into 表名(字段, 字段…..) select 字段, 字段 from 表2

例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
   id INT primary key auto_increment,
   name VARCHAR(20) comment '姓名',
   age INT comment '年龄',
   email VARCHAR(20) comment '邮箱',
   sex varchar(1) comment '性别',
   mobile varchar(20) comment '手机号'
);
-- 将student表中的name、qq_mail复制到test_user表
insert into test_user(name, email) select name, qq_mail from student;

3.查询

3.1 聚合查询

3.1.1 聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	求和
AVG([DISTINCT] expr)	平均值
MAX([DISTINCT] expr)	最大值
MIN([DISTINCT] expr)	最小值

例：SELECT COUNT(*) FROM student where id > 2;

3.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。
需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。 （合并行数只有一条的字段也可）

 语法:select column1, sum(column2), .. from 表名 group by 字段1,字段2;（先以第一字段分，再以上次结果继续以第二字段分组）

例：

-- 按角色分组，查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

3.1.3 HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

-- 显示平均工资低于1500的角色分组和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

-- 先按where 拿到 salary<10000的，再按having  显示平均工资低于1500的角色分组
select role,max(salary),min(salary),avg(salary) from emp where salary<10000 group by role having avg(salary)<1500;

3.2 联合查询

笛卡尔积：多张表关联，结果集的字段是多表的字段合集，结果集的数据是多张表数据的排列组合

3.2.1 内连接
取笛卡尔积经过连接条件过滤后的结果集多表联合查询，是以结果集作为再次连接的数据

语法:
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; 
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

例:
（1）查询“张三”同学的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='张三'; 
-- 或者 
select sco.score from student stu, score sco where stu.id=sco.student_id and stu.name='张三';

例2：

-- 学生表，课程表，成绩表关联查询
SELECT 
	stu.id,
	stu.sn, 
	stu.NAME, 
	sco.score,
	sco.couse_id,
	cou.name
FROM 
	student stu 
	JOIN score sco ON stu.id = sco.student_id 
	JOIN course cou ON sco.course_id = cou.id 
ORDER BY
	stu.ID;

3.2.2 外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
语法：

-- 左外连接，表1完全显示 
select 字段名  from 表名1 left join 表名2 on 连接条件; 
-- 右外连接，表2完全显示 
select 字段 from 表名1 right join 表名2 on 连接条件;

注：
From 表1 inner/left/right join 表2 on 连接条件1 and 连接条件2 where 其他条> 件1 and 其他条件2
内连接中，使用on的条件和where 的条件一样的
外连接中，左右表数据要完全显示，根据的是连接前的条件过滤（连接条件）where 后的其他条件是连接后过滤

例：
查询所有同学的成绩及同学的个人信息，如果该同学没有成绩，也需要显示

select * from student stu left join score sco on stu.id=sco.student_id; 
-- 对应的右外连接为： 
select * from score sco right join student stu on stu.id=sco.student_id;

多表联合查询，是以结果集作为再次连接的数据

3.2.3 自连接
自连接是指在同一张表连接自身进行查询。

3.2.4 子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询：返回一行记录的子查询

-- 查询与“李四” 同学的同班同学
select * from student where classes_id=(select classes_id from student where name='李四'); 

多行子查询：返回多行记录的子查询

-- 查询“语文”或“英文”课程的成绩信息
	-- 使用IN 
	select * from score where course_id in (select id from course where name='语文' or name='英文'); 

-- 使用 NOT IN 
select * from score where course_id not in (select id from course where name!= '语文' and name!='英文');

-- 使用 EXISTS 
select * from score sco where exists (select sco.id from course cou where (name='语文' or name='英文') and cou.id = sco.course_id); 

-- 使用 NOT EXISTS 
select * from score sco where not exists (select sco.id from course cou where (name!='语文' and name!='英文') and cou.id = sco.course_id); 

3.2.5 合并查询

union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
例：查询id大于3，或者名字为“英文”的课程：

select * from course where id > 3
Union
select * from course where name = “英文”;

union all 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行