MySQL基本查询 -- 表的增删改查CRUD

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

1.Create

a.insert

语法：

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

insert (into) 表名 (指定列) values 对应列内容

• into 可以省略
• 指定列如果省略，则为全列插入
• 对应列内容即 value_list 可以是多项，用 ’ , ’ 隔开

插入指定列：

全列插入：

一次插入多行数据：

b. on duplicate key update ~ 插入否则更新

主键 或者 唯一键 对应的值已经存在时会导致插入失败

ERROR 1062 (23000): Duplicate entry '  ' for key '  '

可以选择性的进行同步更新操作

语法：

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

正常写 insert ，最后面加上 on duplicate key update 值a = ' ' , 值b = ' ' ;

Query OK, ‘N’ rows affected (‘N’ sec)

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

通过 MySQL 函数获取受到影响的数据行数：
SELECT ROW_COUNT();

+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

c. replace 插入替换

语法：

replace + 正常写 into

示例：

（这里 sn 和 qq 是唯一键，发生冲突，同时 name 不一样）

---

2.Retrieve

a.select

语法：

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

查找指定列：

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

查询字段为表达式：

为查询结果指定别名：

语法：

列名后加 as '新名称'

as 也可以不写：

结果去重：

select 后加 distinct 即可

b. 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 个）任意字符；_ 表示任意一个字符

对于null安全：

（一般用 is null 和 is not null 即可。）

结果为 null 即 空 ，意为无法参与比较。

例子：

IN (option, …)

LIKE 模糊匹配 %

• % 匹配任意多个（包括 0 个）任意字符
• _ 匹配严格的一个任意字符

as 指定别名后，后面的 where 可以直接使用别名吗？

不可以。

SQL的执行顺序决定了别名只能在SELECT语句的输出结果中使用，而WHERE子句的执行是在别名定义之前

其实可能我不要这一列，但是我的where里面在这列上有一些限制。所以只能where过滤后，再进行展示。如果先把要的列拿出来，那么不要的列上面的限制就不方便找了。
比如老师要考试不及格的学生名单，但是不要具体分数。
或者老师要迟到同学名单，迟到为true这一列就不用打印出来了。

【 逻辑运算符 】

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

() 也是可以使用的

c. order by

ascending 上升的
descending 下降的

order by 默认升序排序 （asc可以直接省略）

语法：

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

NULL 视为比任何值都小，升序出现在最上面:

能用别名：

排序肯定是where过滤后，再进行的。

limit

可以实现分页显示：

三者顺序：

---

3. Update

语法：

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

一次改多列：

给倒数三名同学加30分：

不支持 +=

整表更新：

---

4. Delete

- 删除表内的数据

语法：

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

后面什么限制都不加，就直接删除了。

就算删除所有数据，表的一些内容还是不会变的。比如auto_increment ：

truncate

截断表

语法：

TRUNCATE [TABLE] table_name

慎用:

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

          即不会把本次行为存到 bin log 当中

• 3. 会重置 AUTO_INCREMENT 项

插入查询结果

语法：

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

案例 — 去重表数据

先创建个类型一样的表，然后将原表数据插入，进行去重后，rename 操作’‘偷天换日’’

---

5. 聚合统计

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

统计有多少人：

一般情况下，Select Count (*)和Select Count(1)两着返回结果是一样的，
假如表没有主键(Primary key), 那么count(1)比count(*)快，
如果有主键的话，那主键作为count的条件时候count(主键)最快，
如果你的表只有一个字段的话那count(*)就是最快的。

按列也是统计数目：

去重：

求和，平均值：

手动求：求和再除以数目:

sum:

avg

min

---

6. group by

语法：

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

如何显示每个部门的平均工资和最高工资：
（分组的目的是方便进行聚合统计）

having

having 是对聚合后的数据进行统计

having vs where

其实区别就是本文 2.b. 里面提及的，where可以选择没有select的列进行限制。

条件筛选的阶段是不同的

不要单纯的认为，只有磁盘上表结构导入到mysql，真实存在的表，才叫做表。
中间筛选出来的，包括最终结果，全部都是逻辑上的表！“MySQL一切皆表”。
未来只要我们能够处理好单表的CURD，所有sql场景，我们全部都能用统一的方式进行…

7.OJ推荐：

leetcode: duplicate-emails

leetcode: big-countries

leetcode: nth-highest-salary