MySQL 表的增删改查详解（上）

1.1 单行数据 + 全列插入

• 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致。
• 注意，这里在插入的时候，也可以不用指定 id（当然，那时候就需要明确插入数据到那些列了），那么 MySQL 会使用默认的值进行自增。
• insert 后面的 into 可以省略。

-- 指定列插入
INSERT INTO student (sn, name, qq) VALUES (100, '关羽', '123456');

-- 全列插入
INSERT INTO student VALUES (2, 101, '刘备', '123356');

SELECT * FROM student;

1.2 多行数据 + 指定列插入

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

-- 多行全列插入
INSERT INTO student VALUES 
(3, 188, '许攸', '111222'),
(4, 189, '许褚', '222333');

-- 多行指定列插入
INSERT INTO student (sn, name, qq) VALUES 
(799, '曹植', '777808'),
(801, '曹冲', '999000');

1.3 插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败，插入失败那就由原来的插入操作变成更新操作。

• 主键冲突

INSERT INTO student VALUES (128, 200, '项羽', '908909');
-- ERROR 1062 (23000): Duplicate entry '128' for key 'PRIMARY'

• 唯一键冲突

INSERT INTO student VALUES (130, 101, '张三', '123666');
-- ERROR 1062 (23000): Duplicate entry '101' for key 'sn'

基本语法：

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

实操理解：

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

可通过 MySQL 中 row_count() 函数获取受到影响的数据行数。

1.4 替换

• 主键 或者 唯一键 没有冲突，则直接插入；
• 主键 或者 唯一键 如果冲突，则删除后再插入。

理解 + 实操：

• 1 row affected: 表中没有冲突数据，数据被插入。
• 2 row affected: 表中有冲突数据，删除后重新插入。

二、Retrieve（查询）

语法

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

案例：创建测试表

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 列

【全列查询】

• 通常情况下不建议使用 * 进行全列查询。
• 1. 查询的列越多，意味着需要传输的数据量越大；
• 2. 可能会影响到索引的使用。

【指定列查询】

• 指定列的顺序不需要按定义表的顺序来。

【查询字段为表达式】

• 可以给指定列进行重命名，格式：原名 as 新名，as 可以省略。

【distinct 结果去重】

• 使用 DISTINCT 关键字去除重复行。

2.2 where 条件

比较运算符

• NULL 无法参与 = 的比较，但是可以参与 <=> 的比较。我们只需要记住，= 对于 NULL 是不安全的，后面判断 NULL 使用 IS NULL，IS NOT NULL 即可。

逻辑运算符

• AND, OR, NOT

案例：

• 【英语不及格的同学及英语成绩 (< 60)】

SELECT name, english FROM exam_result WHERE english < 60;

• 【语文成绩在 [80, 90] 分的同学及语文成绩】

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

• 【数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩】

SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

• 【姓孙的同学 及 孙某同学】

• % 匹配任意多个（包括 0 个）任意字符

• _ 匹配严格的一个任意字符

SELECT name FROM exam_result WHERE name LIKE '孙%'; -- 姓孙
SELECT name FROM exam_result WHERE name LIKE '孙_'; -- 孙某

• 【语文成绩好于英语成绩的同学】

SELECT name FROM exam_result WHERE chinese > english;

• 【总分在 200 分以下的同学】

• WHERE 条件中使用表达式

• 别名不能用在 WHERE 条件中

SELECT name, (chinese + math + english) AS total FROM exam_result WHERE (chinese + math + english) < 200;

• 【语文成绩 > 80 并且不姓孙的同学】

SELECT name FROM exam_result WHERE chinese > 80 AND name NOT LIKE '孙%';

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

SELECT name, chinese, math, english, chinese + math + english AS total 
FROM exam_result 
WHERE name LIKE '孙_' OR (chinese + math + english > 200 AND chinese < math AND english > 80);

• 【NULL 的查询】

SELECT * FROM table_name WHERE column IS NULL;
SELECT * FROM table_name WHERE column IS NOT NULL;

2.3 结果排序

语法：

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

• 注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
• NULL 视为比任何值都小，升序出现在最上面，降序出现在最下面。
• 多字段排序，排序优先级随书写顺序。
• ORDER BY 中可以使用表达式或列别名。

2.4 筛选分页结果

语法：

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

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

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

• 建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。
• LIMIT 的执行顺序更靠后。