[ MySQL ] — 基础增删查改的使用

表的增删查改

Create

单行数据全列插入

多行数据全列插入

多行数据指定列插入

不存在插入存在则更新

替换

Retrieve

SELECT 列

全列查询

指定列查询

查询字段为表达式

为查询结果指定别名

结果去重

WHERE 条件

结果排序

筛选结果分页

Update

Delete

删除数据

截断表

插入查询结果

聚合函数

group by子句的使用

表的增删查改

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

Create

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

value_list: value, [, value] ...
[ ]表示里面的内容可以省略

创建一张学生信息表

单行数据全列插入

全列插入时，values 的右边的数量必须和定义表时列的数量及顺序一致。

如果有自增属性可以不写，但是就需要明确把数据插入到哪些列中。这个就属于指定列插入了。

多行数据全列插入

多条记录用 “，” 分割即可，每条记录的数量必须和定义表时列数量及顺序一致。

多行数据指定列插入

要保证每条记录的数量必须和指定列数量及顺序一致。

不存在插入存在则更新

由于主键或者唯一键对应的值已经存在而导致插入失败。

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

语法：

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

也可以通过MySQL函数获取收到影响的数据行数

替换

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

仅需及将 insert 改为 replace

Retrieve

语法：

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

使用：

创建一个学生成绩表，插入一批数据。

SELECT 列

全列查询

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

指定列查询

查询字段为表达式

select比较特殊，他后面可以跟表达式。

为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;

结果去重

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)

使用：

查找显示数学成绩低于80分的同学以及他的成绩，看看是哪些同学在拖后腿。

查找显示英语成绩在 80~90分范围内的同学以及他的成绩。

查找显示英语成绩在 58或59 或98或99的同学以及他的成绩。注：中途插入了一些例子，只看演示不要在意细节。

查找姓A的同学以及A某同学。

查找语文成绩好于英语成绩的同学

查找总分低于200分的同学

查找语文成绩 > 80，但是不姓A的同学

查找孙某同学成绩或者总成绩超过250 并且语文<数学，英语>80的同学。

NULL的查询

NULL 和 NULL 的比较，= 和 <=> 的区别

结果排序

语法：

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

ASC 为升序（从小到大）DESC 为降序（从大到小）默认为 ASC

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

使用：

同学及数学成绩，按数学成绩升序显示

显示名字及年龄，对年龄进行排序

查询同学各门成绩，依次按数学降序，语文升序，英语升序的方式显示

查询同学及总分，由高到低降序排列

查询姓A的同学或者姓B的同学数学成绩，结果按数学成绩由高到低降序显示

筛选结果分页

语法：

-- 起始下标为 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;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

使用：

查找显示总分为第一名和前三名的同学及成绩

Update

语法：

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

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

更新数据时也要查询到某条记录再进行列值更新，否则更新的就是整表的列记录。

使用：

将F同学的数学成绩改为85分

将B同学的数学成绩变更为60分，语文成绩变更为70分

将总分倒数前三的 3 位同学的数学成绩都加上 30 分

将所有同学的语文成绩更新为原来的 2 倍。注意：更新全表的语句慎用！

Delete

删除数据

语法：

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

使用：

删除A同学的成绩

删除总分为倒数第一的同学的成绩

删除整张表数据。注意：删除整表操作要慎用！

这里删除整张表的数据并不会对表的结构有影响，比如id自增，删除所有数据后这个自增信息并不会归零，还是会根据之前插入的信息继续自增。

截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用！

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

使用：

插入查询结果

语法：

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

使用：

要删除表内的重复记录，保证任意一条记录只有一份。

聚合函数

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

使用：

统计有多少个同学

统计记录

统计英语成绩的个数

统计英语成绩总分

统计平均分，我们可以统计某一科的平均分

也可以统计总分的平均分

查找数学最高分

查找英语高于75分的最低分

group by子句的使用

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

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

我们要知道分组的目的是分组之后可以方便聚合统计，而group by column 是根据该列的不同的数据来进行分组的，那么也就意味着分组后，组内该列的条件是相同的，也就代表组内数据可以被聚合压缩。分组的含义是根据条件把一列分为多个组，然后进行组内的统计，而我们也可以把分组理解为“分表”，什么意思呢？就是把一张表按照条件在逻辑上划分成多个子表，然后各自对各自的子表进行聚合统计。

使用：

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