[ MySQL ] — 基础增删查改的使用-Toy模板网

这篇具有很好参考价值的文章主要介绍了[ MySQL ] — 基础增删查改的使用。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

表的增删查改

Create

单行数据全列插入

多行数据全列插入

多行数据指定列插入

不存在插入存在则更新

替换

Retrieve

SELECT 列

全列查询

指定列查询

查询字段为表达式

为查询结果指定别名

结果去重

WHERE 条件

结果排序

筛选结果分页

Update

Delete

删除数据

截断表

插入查询结果

聚合函数

group by子句的使用

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

表的增删查改

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

Create

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

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

创建一张学生信息表

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

单行数据全列插入

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

多行数据全列插入

多条记录用 “，” 分割即可，每条记录的数量必须和定义表时列数量及顺序一致。
[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

多行数据指定列插入

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

不存在插入存在则更新

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

语法：

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

替换

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

仅需及将 insert 改为 replace

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

Retrieve

语法：

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

使用：

创建一个学生成绩表，插入一批数据。 [ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

SELECT 列

全列查询

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

指定列查询

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

查询字段为表达式

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

为查询结果指定别名

语法：

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

结果去重

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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分的同学以及他的成绩，看看是哪些同学在拖后腿。

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

查找总分低于200分的同学

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

NULL的查询

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

结果排序

语法：

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

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

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

使用：

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

筛选结果分页

语法：

-- 起始下标为 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，避免因为表中数据过大，查询全表数据导致数据库卡死。

使用：

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

Update

语法：

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

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

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

使用：

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

Delete

删除数据

语法：

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

使用：

删除A同学的成绩

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用！

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

使用：

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

插入查询结果

语法：

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

使用：

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

聚合函数

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

使用：

统计有多少个同学

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

统计记录

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux 统计英语成绩的个数

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

统计英语成绩总分

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

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

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

也可以统计总分的平均分

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

查找数学最高分

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux 查找英语高于75分的最低分

[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux

group by子句的使用

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

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

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

使用：

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
EMP员工表
DEPT部门表
SALGRADE工资等级表
[ MySQL ] — 基础增删查改的使用,MySQL笔记,数据库,mysql,linux