# MySQL 增删改查案例
# 准备阶段
-- 建表
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname CHAR(10),
class_id INT DEFAULT NULL,
age SMALLINT NOT NULL
);
-- 插入数据
INSERT
INTO student (sname, class_id, age)
VALUES
('小赵',1,31),
('小钱',1,22),
('小孙',1,33),
('小李',NULL,34),
('小周',2,21),
('小吴',2,22),
('小郑',2,33),
('小王',2,24);
SELECT * FROM student;
查询结果
id sname class_id age
------ ------ -------- --------
1 小赵 1 33
2 小钱 1 33
3 小孙 1 33
4 小李 (NULL) 33
5 小周 2 20
6 小吴 2 20
7 小郑 2 20
8 小王 2 20
# 模糊查询
SELECT * FROM student
WHERE class_id=2
OR sname LIKE '%李%';
id sname class_id age
------ ------ -------- --------
4 小李 (NULL) 33
5 小周 2 20
6 小吴 2 20
7 小郑 2 20
8 小王 2 20
# 范围查询
BETWEEN
-- AND
SELECT * FROM student WHERE age >= 25 AND age <= 40;
-- 等价
SELECT * FROM student WHERE age BETWEEN 25 AND 40;
-- NOT
SELECT * FROM student WHERE age NOT BETWEEN 25 AND 40;
查询结果
id sname class_id age
------ ------ -------- --------
1 小赵 1 33
2 小钱 1 33
3 小孙 1 33
4 小李 (NULL) 33
IN
SELECT * FROM student WHERE class_id IN (2,3);
id sname class_id age
------ ------ -------- --------
5 小周 2 20
6 小吴 2 20
7 小郑 2 20
8 小王 2 20
# 去重
SELECT DISTINCT class_id FROM stu;
# 分页
SELECT * FROM student LIMIT 5;
SELECT * FROM student LIMIT 5 OFFSET 5;
# NULL 处理
NULL 不能和任何值比较
-- 无效，查询无结果
SELECT * FROM student WHERE class_id = NULL;
SELECT * FROM student WHERE class_id IS NULL;
查询结果
id sname class_id age
------ ------ -------- --------
4 小李 (NULL) 33
SELECT sname, IF(class_id, class_id, '未分配') FROM student;
SELECT sname, IFNULL(class_id, '未分配') FROM student;
查询结果
sname IFNULL(class_id, '未分配')
------ -------------------------------
小赵 1
小钱 1
小孙 1
小李 未分配
小周 2
小吴 2
小郑 2
小王 2
# 排序
ORDER BY
SELECT * FROM student ORDER BY class_id, age ASC;
id sname class_id age
------ ------ -------- --------
2 小钱 1 22
1 小赵 1 31
3 小孙 1 33
5 小周 2 21
6 小吴 2 22
8 小王 2 24
7 小郑 2 33
# 组合使用
SELECT * FROM student
WHERE
class_id = 2
AND
age IS NOT NULL
ORDER BY
age ASC
LIMIT 1;
id sname class_id age
------ ------ -------- --------
5 小周 2 21
# 更新数据
UPDATE student SET class_id = 2 WHERE class_id IS NULL;
SELECT * FROM student;
id sname class_id age
------ ------ -------- --------
1 小赵 1 31
2 小钱 1 22
3 小孙 1 33
4 小李 2 34
5 小周 2 21
6 小吴 2 22
7 小郑 2 33
8 小王 2 24
-- 把年龄大于 30 的同学分配到 3 班
UPDATE student SET class_id = 3 WHERE age > 30;
SELECT * FROM student ORDER BY age;
id sname class_id age
------ ------ -------- --------
5 小周 2 21
2 小钱 1 22
6 小吴 2 22
8 小王 2 24
1 小赵 3 31
3 小孙 3 33
7 小郑 3 33
4 小李 3 34
-- 删除最后报名的两个同学
DELETE FROM student ORDER BY id DESC LIMIT 2;
SELECT * FROM student;
id sname class_id age
------ ------ -------- --------
1 小赵 3 31
2 小钱 1 22
3 小孙 3 33
4 小李 3 34
5 小周 2 21
6 小吴 2 22
# 添加数据
INSERT INTO student SET sname = '小盾', age = 33, class_id = 3;
INSERT INTO student
(sname, age, class_id)
VALUES
('张三', 22, 1),
('李四', 18, NULL);
SELECT * FROM student;
id sname class_id age
------ ------ -------- --------
1 小赵 3 31
2 小钱 1 22
3 小孙 3 33
4 小李 3 34
5 小周 2 21
6 小吴 2 22
9 小盾 3 33
10 张三 1 22
11 李四 (NULL) 18