MySQL——表的内容增删查改

表的增删改查

一、Create

语法：

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...

value_list: value, [, value] ...

• 建一张表：

1、单行全列插入

2、多行数据指定列插入

3、插入否则更新

这个操作很像unordered_map中的operator[ ]
由于主键或者唯一键对应的值已经存在而导致插入失败。
可以选择性的进行同步更新操作语法：

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

如果有，则更新：

2行被影响的意思：第一冲突了，就和这一行有影响，第二更新了，这就是第二次影响。(冲突了且修改了就是2行被影响)

如果没有，就新插入：

这里新插入就是1行受到影响。

可以通过MySQL函数获取受到影响的数据行数：

4、替换

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

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

二、Retrieve😊(重点)

语法：

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

建表：

预插入数据：

2.1 select 列

2.1.1 全列查询

通常情况下不建议使用 * 进行全列查询

1. 查询的列越多，意味着需要传输的数据量越大(现在查询的案例数据量不大，后面可能会查询上万行的数据，一旦刷屏就不好观察)。
2. 可能会影响到索引的使用。

2.1.2 指定列查询

根据的查询的字段顺序，来显示查询结果：

2.1.3 查询字段为表达式

表达式不包含字段：

表达式包含一个字段：

表达式包含多个字段：

2.1.4 为查询结果指定别名

语法：
SELECT column [AS] alias_name [...] FROM table_name;

2.1.5 结果去重

2.2 where查询

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为true(1)，结果为false(0)

不建议使用 = 、<=>查询NULL字段，而是使用 IS NULL、IS NOT NULL：

范围查找：

in条件查询：

like条件查询：
% 匹配任意多个（包括 0 个）任意字符，也可以是汉字；_ 匹配严格的一个任意字符。

where条件中比较运算符两侧都是字段：

where条件中使用表达式，别名不能用在where条件中：

两个字句是有先后执行顺序的：
1、按照条件把数据筛选出来。
2、按照要求，计算数据。
所以where字句中的内容要在你select表达式之前执行！也就是说，起别名是在筛选之后，所以不起作用了。

为什么order by可以使用别名？
你要排序，是不是你的数据已经根据条件已经将数据全部筛选完了！
——排序本质是计算吗? 是的
所以要有一个对于mysql各个子句的执行顺序，有一定的刻意练习！

AND 与 NOT 的使用：

综合性查询：
孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

NULL的查询：
不建议使用符号，而是直接使用 is null或者is not null

2.3 结果排序

ascend 升序——默认
descend降序
语法：

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

NULL 视为比任何值都小，升序出现在最上面；
NULL 视为比任何值都小，降序出现在最下面：

多字段排序，排序优先级随书写顺序
eg：查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

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;

三、Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

对于查询到的结果进行列值更新

• 更新值为具体值

• 一次更新多个列

• 更新值为原值基础上变更
  更新完之后，原来的倒数前三名，不一定是现在的倒数前三名了。
  
• 没有 WHERE 子句，则更新全表
  注意：更新全表的语句慎用！

四、Delete

1、delete删除数据

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

注意：删除整张表中的数据(不是drop table)要慎用！

表结构中天然维护了自增记录：

清空数据：

再次插入一条数据，在原来id上继续增长：

2、truncate截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用！

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

再次插入数据，查看，id从1开始了：

3、delete清空和truncate清空的区别

区别还有 日志 ：承担很大的功能要求

• bin log：几乎所有的sql操作，mysqld服务器都会给我们记录下来，该log用来进行多主机同步在增量备份。
• redo log: mysql数据持久化和crash-safe功能。
• undo log：在事务中承担回滚的日志，数据操作恢复功能。

delete清空会更新日志，而truncate清空是不更新日志的。(了解知道即可)

五、插入查询结果

把一个表里的数据查询出来，再插入到另一个表中！
语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

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

创建数据源表，并且插入一定的重复数据：

创建一个备份表，将不重复的数据插入即可：

通过重命名表，实现原子的去重操作：

六、聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

1、统计班级共有多少同学

可以对结果取别名，其中*做统计不受NULL影响。

2、统计班级收集的 qq 号有多少

NULL不会计入结果，空会计入结果。

3、统计本次考试的数学成绩分数个数

COUNT(math) 统计的是全部成绩

COUNT(DISTINCT math) 统计的是去重成绩数量

4、统计数学成绩总分

以前是横向统计，现在是纵向统计：

5、统计平均总分

这样也行：

6、返回最高分

7、返回 > 80 分以上的数学最低分

七、group by分组子句的使用😊

在select中使用group by 子句可以对指定列进行分组查询
语法：

select column1, column2, .. from table group by column;

案例：

• 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
  EMP员工表
  DEPT部门表
  SALGRADE工资等级表

先在MySQL中导入sql文件：

mysql> source ./MySQL/scott_data.sql;

查看各个表：

1、如何显示每个部门的平均工资和最高工资

2、显示每个部门的每种岗位的平均工资和最低工资

3、显示平均工资低于2000的部门和它的平均工资

• 统计每个部门的平均工资

• having和group by配合使用，对group by结果进行过滤

where与having区别

上述语句为什么要报错？

根本原因在于两者的执行次序是不同的！where是过滤表中数据的，having是过滤分组数据的，where要起效果只要有数据即可，但是having要起效果是要先分组的！可是分组又必须要先把数据拿全了才能分组。
这就意味着where和having是不冲突的！where是在数据分组前进行过滤，而having是在数据分组后进行过滤。

总结：他们两个唯一的差别就是：where过滤行，而having过滤分组。
目前所接触的where都是可以用having子句来代替的。