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, …)
IN 用于检查某个值是否在一个给定的集合中。这个集合可以是一个列表,也可以是一个子查询的结果。
EXISTS
EXISTS 子查询可以引用主查询中的表。
这种用法使得 EXISTS 非常灵活,能够在子查询中使用主查询的列来进行条件判断。
在 EXISTS 子查询中,返回的列或值并不影响结果。你可以使用 SELECT 1、SELECT * 或 SELECT NULL,它们的效果是一样的,因为 EXISTS 只关心子查询是否有结果,而不关心具体返回的内容。
SELECT *
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.department_id = e.department_id AND d.location = 'New York'
);
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 :
delete 默认删除 from 后跟的一个表。若有一些限制条件可能要跟多个表,直接在 delete 和 from 之间加上要删的表即可。
truncate
截断表
语法:
TRUNCATE [TABLE] table_name
慎用:
-
- 只能对整表操作,不能像 DELETE 一样针对部分数据操作;
-
- 实际上 MySQL 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事物,所以无法回滚
即不会把本次行为存到 bin log 当中
-
- 会重置 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场景,我们全部都能用统一的方式进行…