MySQL - 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查询

全列查询

Select * from 表名

指定列查询

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

查询字段为表达式

-- 表达式不包含字段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; 

别名

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

某列去重查询：distinct

SELECT distinct 字段  from 表名;

排序：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可以对表达式及别名排序

---

条件查询：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，在同时使用时，需要使用小括号()包裹优先执行的部分

---

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

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;