【MySQL】表的增删改查

前言

面试题：SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit

插入

1. 单行全列插入 INSERT INTO students VALUES (100, 10000, ‘唐三藏’, NULL);
2. 多行指定列插入 INSERT INTO students (id, sn, name) VALUES (102, 20001, ‘曹孟德’), (103, 20002, ‘孙仲谋’);
3. 插入否则更新 由于 主键 或者 唯一键 对应的值已经存在而导致插入失败

– 0 row affected: 有冲突数据，冲突数据的值和 update 的值相等 – 1 row affected: 没有冲突数据，数据被插入 – 2 row affected: 有冲突，且数据更新

1. 全部替换 – 主键 或者 唯一键 没有冲突，则直接插入； – 主键 或者 唯一键 如果冲突，则删除后再插入

select 列 （distinct ：去重）

1. 全列查询 select * from table_name; – 通常情况下不建议使用 * 进行全列查询 – 1. 查询的列越多，需要传输的数据量越大； – 2. 可能会影响到索引的使用。
2. 指定列查询 / 指定别名 select id, name, english + 10 from table_name; #查询出来的english + 10分；

select id, name, chinese + math + english as total from table-name; # 语数外的和并且列重命名为total， as可省略

1. 结果去重 select distinct math from table_name; # 对数学分数列去重

where 条件筛选

1. 比较运算符

1. 逻辑运算符（and / or / not） AND 多个条件必须都为 TRUE(1)，结果才是 TRUE(1) OR 任意一个条件为 TRUE(1), 结果为 TRUE(1) NOT 条件为 TRUE(1)，结果为 FALSE(0)

结果排序（order by column [asc / desc]）

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

1. 多字段排序 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示 SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;
2. ORDER BY 子句中可以使用列别名，因为执行顺序满足 查询同学及总分，由高到低 SELECT name, chinese + english + math 总分 FROM exam_result ORDER BY 总分 DESC

筛选分页结果 【LIMIT n (n条结果) OFFSET s (起始下标) 】

– 起始下标为 0

1. – 从 s 开始，筛选 n 条结果 SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n
2. – 从 0 开始，筛选 n 条结果 SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
3. – 从 s 开始，筛选 n 条结果，用法更明确，建议使用 SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;
4. 建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

Update

语法：UPDATE table_name SET column = expr [, column = expr …]

[WHERE …] [ORDER BY …] [LIMIT…]

1. 列值更新 将孙悟空同学的数学成绩变更为 80 分 UPDATE exam_result SET math = 80 WHERE name = ‘孙悟空’;#####
2. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分 （1）-- 查看原数据 – 别名可以在ORDER BY中使用 SELECT name, math, chinese + math + english 总分 FROM exam_result ORDER BY 总分 LIMIT 3; （2）-- 数据更新，不支持 math += 30 这种语法 UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;######## （3）-- 查看更新后数据 – 注意：这里不可以按总分升序排序取前 3 个，加30分更新后倒数不一定还是倒数 SELECT name, math, chinese + math + english 总分 FROM exam_result WHERE name IN (‘name1’, ‘name2’, ‘name3’);
3. 没有 WHERE / ORDER BY子句，则更新全表 UPDATE exam_result SET chinese = chinese * 2;######

如何理解更新对象：对于（update table_name set 更新条件）之后的整体，看作一个子表，该子表为更新对象

Delete

语法：DELETE FROM table_name [WHERE …] [ORDER BY …] [LIMIT …]

1. 删除孙悟空同学的考试成绩 DELETE FROM exam_result WHERE name = ‘孙悟空’;
2. 删除整张表数据 DELETE FROM for_delete_table;

注意：自增约束未清空 再插入一条数据，自增 id 在原值上增长 查看表结构：SHOW CREATE TABLE for_delete_table\G 会有 AUTO_INCREMENT=n 项

截断表 （TRUNCATE [TABLE] table_name）

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

插入查询结果

语法：INSERT INTO table_name [(column [, column …])] SELECT…

案例：删除表中的的重复复记录，重复的数据只能有一份

1. – 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样 CREATE TABLE no_duplicate_table LIKE duplicate_table;
2. – 将 duplicate_table 的去重数据插入到 no_duplicate_table INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
3. – 通过重命名表，实现原子的去重操作 RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table; #先要重命名duplicate_table表为 old_duplicate_table才可对no_duplicate_table 表重命名为duplicate_table

聚合函数

expression:表达式，这里主要是指列名

group by column

select column1, column2, … from table group by column [having…]; #having：条件筛选类似于where