# 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