目录
CRUD: create（创建），retrieve（读取），Update（更新），delete（删除）
1. Create
1.1 语法
INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...插入数据的时候，value_list 中列的数量和顺序要和 前面定义表([(column [, column] ...)] )的列的一致。
这里先创建一张学生表，用于下列实验。
1.2 单行数据 + 全列插入
如果定义表中没有指定列，表示全列插入。也可以在定义表中指定全部列表示全列插入。
1.3 多行数据 + 指定列插入
1.4 插入数据否则更新数据
由于在插入的时候会发生主键或者唯一键冲突，可以选择性的进行同步更新操作：
INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...如下图中的语句，当插入的数据没有主键或者唯一键冲突的时候插入数据，如果有主键或唯一键冲突则将原数据更新为 update 之后的数据。
1.5 替换
REPLACE [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...如果插入的数据与表中的数据没冲突，则插入该数据；如果插入的数据与表中的数据有冲突，则将表中数据替换为该数据。
2. Retrieve
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
DISTINCT：表示去重。
{* | {column [, column] ...}：*表示通配符，column表示要查看的列。
WHERE ...：后面跟筛选条件。
ORDER BY column [ASC | DESC], ...：按照 column 升序或降序排序。
LIMIT ...：限制条数。下列是后续实验使用的表的结构以及其数据。
2.1 SELECT 列
2.1.1 全列查询
通常情况下不建议使用 * 进行全列查询，因为查询的列越多，意味着需要传输的数据量越大，并且会影响到索引的使用。
2.1.2 指定列查询
指定列的顺序不需要按定义表的顺序来写。可以指定想要查询的若干列进行查询。
2.1.3 查询字段为表达式
（1）表达式不包含字段
（2）表达式包含一个字段
（3）表达式包含多个字段
2.1.4 为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;as 可以省略。
2.1.5 结构去重
2.2 WHERE 条件
2.2.1 运算符介绍
比较运算符：
'=' 不能用于判断是否等于 NULL。
between 的区间是左闭右闭的。
like 前可以加 not 表示取反。
逻辑运算符：
2.2.2 案例
（1）查看英语低于60分的同学。
（2）查看语文成绩在 [80, 90] 分的同学。
（3）数学成绩是 98 或 99 的同学。
（4）筛选出姓孙的同学，孙某以及孙某某。
（5）筛选语文成绩好于英语成绩的同学。
（6）筛选总分在 200 分一下的同学。
从上图可以看到，语句 1 可以正常执行，而语句 2 则会失败。原因是因为 select 语句是有执行顺序的。首先执行的是 from 表示从那个表中找，其次是 where 子句，表示筛选的条件，最后在是列的显示。上述的语句 2 中，在执行 where 的时候还没有进行重命名，所以使用 total 充当筛选条件是不可行的，其次，重命名也不能在 where 子句中进行。
2.3 对筛选结果排序显示
order by 的执行顺序在显示列之后，所以可以使用重命名的列名进行排序。
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];注：没有 order by 子句查询出来的顺序是未定义的。 NULL 视为比任何值都小。
2.3.1 案例
（1）按数学成绩升序或降序显示同学及其数学成绩。
（2） 按数学降序，英语升序，语文升序的方式显示各科成绩。
上述语句表明的是，当数学相等的时候，按照英语成绩升序排列，当数学成绩相等并且英语成绩相等的时候，按照语文成绩相等来排。
2.4 分页显示结果
-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
对未知表进行查询的时候最好加上一条 limit 语句，避免因为表中数据过大，查询全表数据导致数据库卡死。
3. Update
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...];对查询到的结果进行列值更新，如果没有 where 子句会对全表进行修改。
3.1 案例
（1）将孙悟空的数学成绩修改为 80 分。
（2）将总分倒数前 3 的同学数学加 30 分。
4. Delete
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];对查询到的结果进行删除。
4.1 案例
（1）删除孙悟空的成绩。
（2）删除整张表的数据。
整张表的数据删除之后，并不会重置 auto_increment 项。
4.2 截断表
TRUNCATE [TABLE] table_name;truncate 也是清空整张表的数据，只能对整张表操作，不能想 delete 一样删除部分数据。会重置 auto_increment 项。truncate 不对数据操作，直接把表清空，所以比 delete 快，但是 truncate 在删除数据的时候，并不会记录到日志当中，也不会经过真正的事务，所以无法回滚。
5. 插入查询结果
INSERT INTO table_name [(column [, column ...])] SELECT ...;案例：删除表中的重复记录。
（1）创建原始数据表并插入测试数据。
（2）创建一张空表，结构和原表一样。
（3）将原表去重的数据插入到新表中。
（4）将原表重命名为其他，然后将新表重命名为原表。
6. 聚合函数
6.1 案例
（1）统计学生数学成绩的平均值。
（2）返回 > 70 分以上的最低数学成绩。
7. group by 子句的使用
分组的目的是为了进行分组之后，方便进行聚合统计。
select column1, column2, .. from table group by column;7.1 案例
创建一个雇员信息表（来自oracle 9i的经典测试表），EMP 员工表，DEPT 部门表，SALGRADE 工资等级表。
（1）显示每个部门的平均工资和最高工资。
（2）显示每个部门的每种岗位的平均工资和最低工资。
（3）显示平均工资低于 2000 的部门和它的平均工资。
having 对聚合统计之后的结果进行条件筛选。
知识点1：
SQL 查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit
2701
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
