MySQL常用的增删改查操作_mysql的增删改查

---

前言

文章记录常用的SQL查询语句，包括数据库、表和数据的增删改查，逐渐扩展到其他比较复杂的查询语句。SQL语句有很多相似之处，对比起来记忆比较方便。以下做一些记录。数据库的一些操作可以关联到高级语言编程的一些相关操作，比如类、对象和函数。

---

SQL语句关键字尽量以大写规范写
非SQL关键字我用中文代替，代表可以用户自定义的东西

---

一、DDL数据定义语言

DDL、DML和DCL的中文翻译很好说明了其作用
DDL相当于直接对对象进行操作
关键字：CREATE、ALTER、DROP、RENAME、TRUNCATE
为何理解为对象？
CREATE创建数据库，相当于实例化一个数据库类的对象
CREATE创建表也是，相当于实例化一个表类的对象
实例化并没有对对象中的数据进行操作
ALTER、DROP、RENAME、TRUNCATE关键字也可以看出来是对这个实例化对象进行操作，而非更改对象的数据
因此这些关键字的语句作为数据定义语言出现

二、DML数据操纵语言

DML相当于直接对对象的数据进行操作
关键字：INSERT、DELETE、UPDATE、SELECT
对象的属性对于数据库中的一张张表来说就是数据
这样就可以理解为什么INSERT、DELETE、UPDATE、SELECT作为数据操纵语言出现

三、DCL数据控制语言

DCL相当于对操作流程的操作控制
关键字：COMMIT、ROLLBACK、SAVEPOINT、GRANT、REVOKE
COMMIT、ROLLBACK控制操作流程是否提交
SAVEPOINT控制操作流程中的保存点
GRANT、REVOKE控制用户的操作权限，限制用户的操纵流程

四、展示存在的数据库和表

SHOW DATABASES;#展示存在的数据库
SHOW TABLES;#展示存在的表

SHOW CREATE DATABASE `数据库名`;#显示具体数据库的一些相关信息
SHOW CREATE TABLE `表名`;#显示具体表的一些相关信息
1
2
3
4
5

五、创建数据库和表

CREATE DATABASE `数据库名`;
CREATE DATABASE `数据库名` CHARACTER SET '字符集如utf8mb4';
CREATE DATABASE IF NOT EXISTS `数据库名` CHARACTER SET 'utf8mb4';#推荐

CREATE TABLE `表名`(`字段名1` 类型1,`字段名1` 类型1);
CREATE TABLE IF NOT EXISTS `表名`(`字段名1` 类型1,`字段名1` 类型1)#默认使用数据库字符集

CREATE TABLE `新创建表名` AS SELECT `存在字段1`，`存在字段2` FROM `存在表`;#从存在的表中选择字段创建新表

CREATE TABLE `表名`(
`字段1` 类型1，
`字段2` 类型2，
`字段3` 类型3 GENERATED ALWAYS AS (字段1与字段2运算表达式)
);
#生成含有计算列的表，插入前两个字段，计算列自动计算，更新时也是如此

CREATE VIEW `视图名`(`字段1别名`,`字段2别名`) AS SELECT `字段1`,`字段2` FROM `表名`;
#基于某一张表创建视图
#VIEW的作用是限制观看字段，字段信息保存在视图中，直接查询字段进行观看，可以简化复杂的查询语句
#使用的是相同的数据，在视图中更改数据时，对应表的数据也会更改
#视图的增删改操作与表相同
#不存在表中的字段是不支持更改视图的。比如聚合函数或者运算结果字段存在的视图，是不能更改的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

六、更改数据库和表

ALTER DATABASE `数据库名` CHARACTER SET '字符集';

ALTER TABLE `表名` ADD(`新字段1` 类型1，`新字段1` 类型1) FIRST或AFTER `某字段`;#FIRST和AFTER省略时默认在最后添加新字段
ALTER TABLE `表名` MODIFY `某字段` 类型 DEFAULT 默认值;#更改某字段类型,类型更改到小储存空间类型是会报错的
ALTER TABLE `表名` CHANGE `旧字段名` `新字段名` 类型;#直接更改某一字段的名字和类型
ALTER TABLE `表名` DROP COLUMN `某字段`;#删除表中的某一个字段

RENAME TABLE `旧表名` TO `新表名`;#更改某一张表的表名
1
2
3
4
5
6
7
8

七、删除数据库、表或视图

DROP DATABASE IF EXISTS `数据库名`;

DROP TABLE IF EXISTS `表名`;

TRUNCATE TABLE `表名`;#清空表中的数据，DDL操作！无法回滚！
DELETE FROM `表名`;#删除表中的所有记录，DML操作，可以回滚。不加WHERE关键字就是删除所有的记录

DROW VIEW `视图名`;
#注意基于删除视图创建的视图查询会受到影响
1
2
3
4
5
6
7
8
9

八、表的增删改查语句

因为常用且重要因此放在一起说，方便查阅
符号``是为了防止字段名或者表名和关键字重复。

SELECT * FROM `表名`;
#查询表的所有字段，不推荐
SELECT `表名1`.`字段名1`,`表名1`.`字段名2` FROM `表名1` WHERE 条件;
#查询满足条件的表1的字段1和字段2
INSERT INTO `表名`(`字段1`,`字段2`) VALUES (字段1的值,字段2的值);
#往表的字段1和字段2中插入相应的值，如果其他字段可以为NULL就会为NULL
#其他字段不可以为NULL则不会允许插入
INSERT INTO `表名`(`字段1`，`字段3`) VALUES (记录1字段1值，记录1字段3值),(记录2字段1值，记录2字段3值);
#插入多条记录，较高效率
UPDATE `表名` SET `字段1`=值 WHERE 条件;
#更新表中某一条记录的字段1的值，WHERE后面跟着需要更新的记录需要满足的条件
DELETE FROM `表名` WHERE 条件;
#从表中删除满足WHERE条件的记录
1
2
3
4
5
6
7
8
9
10
11
12
13

以上是最为基础的增删改查语句，后面还有很多扩展

SELECT `表1的别名`.`字段名1` "字段1别名",`表1的别名`.`字段名2` FROM `表名1` `表1的别名`;
#字段名和表名存在别名的情况，使""可以避免别名出现空格时出错
SELECT DISTINCT `字段名1` FROM `表名`;
#DISTINCT关键字去除重复行
INSERT INTO `表名1`(`字段1`，`字段3`) SELECT `字段1`，`字段3` FROM `表名2`
#将表2中的记录插入到表1中，字段需要一一对应
1
2
3
4
5
6

1.关键字的处理顺序

这里很重要，一条SQL语句是由多个关键字组成的，自已可以选取任意关键字组合，但是需要注意顺序，还需要注意它们之间的配合使用。这样既可以知道如何保证语句的查询效率，又可以保证语法不出错
系统对于关键字的执行顺序为：注意，这不是关键字书写顺序！
FROM（#笛卡尔积连接） -> ON ->(LEFT\RIGHT JOIN) ->WHERE ->GROUP BY ->HAVING->SELECT（#相当于最后的查询显示）->DISTINCT ->ORDER BY ->LIMIT
先过滤条件再进行数据处理可以减少后续的开销
关键字书写顺序是：
SELECT(DISTINCT)->FROM->JOIN->ON->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT;

2.JOIN的应用（多表连接）

SELECT `表1`.`字段1`,`表2`.`字段1` FROM `表1`,`表2`;
#结果输出为对应字段笛卡尔积的形式，很少有用到的场景
#并不是全连接，全连接是左连接和右连接的并集
SELECT `表1`.`字段1`,`表2`.`字段1` FROM `表1` JOIN `表2` ON `表1`.`某字段`=`表2`.`某字段`;
#只会查询到满足连接条件的记录
SELECT `表1`.`字段1`,`表2`.`字段2` FROM `表1` LEFT JOIN `表2` ON 字段连接条件;
#左外连接的方式，会显示表1中不满足连接条件的记录
SELECT `表1`.`字段1`,`表2`.`字段2` FROM `表1` RIGHT JOIN `表2` ON 字段连接条件;
#左外连接的方式，会显示表2中不满足连接条件的记录
#一般情况下把需要多余展示记录的表放在左边写成左外连接的方式
1
2
3
4
5
6
7
8
9
10

更多表的连接只需要在后面不断加JOIN ON即可，只要写清楚字段连接条件
MySQL中不支持全连接的方式，需要利用UNION和UNION ALL关键字实现全连接
UNION ALL的效率是高于UNION的，因为UNION会剔除掉相同的记录，产生开销
因此全连接考虑利用UNION ALL来实现
注意到，全连接可以利用左连接和右连接中多余的部分UNION ALL实现
或者左连接中多余的部分和右连接UNION ALL实现
还注意到，左连接中右边表的字段为NULL
右连接中，左边表的字段为NULL
那思路清楚之后就能写出UNION ALL形式的全连接
首先，准备左连接

SELECT `表1`.`字段1`,`表2`.`字段2` FROM `表1` LEFT JOIN `表2` ON 字段连接条件;
1

其次，准备右连接多余的部分

SELECT `表1`.`字段1`,`表2`.`字段2` FROM `表1` RIGHT JOIN `表2` ON 字段连接条件 WHERE `表2`.`字段2`<=>NULL;
#注意NULL比较运算需要用<=>而不是=
1
2

最后，将两个结果UNION ALL起来就能得到全连接的结果

SELECT `表1`.`字段1`,`表2`.`字段2` FROM `表1` LEFT JOIN `表2` ON 字段连接条件
UNION ALL
SELECT `表1`.`字段1`,`表2`.`字段2` FROM `表1` RIGHT JOIN `表2` ON 字段连接条件 WHERE `表2`.`字段2`<=>NULL;
#注意NULL比较运算需要用<=>而不是=
1
2
3
4

SQL语句没有;就不代表结束。
此即全连接的实现方式，还有其他全连接的实现方式同理即可。

其他连接语法：

NATURAL JOIN;
#自动查询相同的字段进行连接
JOIN USING(`字段1`);
#等值连接同名字段1

#手动连接的方式比以上两种连接更加灵活
1
2
3
4
5
6

3.WHERE条件过滤

SELECT `字段1` FROM `表1` WHERE 条件;
#设置过滤记录的条件，比如字段应满足何种运算关系
1
2

4.GROUP BY分组

SELECT 聚合函数(`某字段`),`字段1`,`字段2` FROM `表名` ORDER BY `字段3`,`字段1`,`字段2`;
#如果按照字段3分组进行聚合函数，但是查询语句中字段1和字段2没有出现在聚合函数内
#则必须要求字段1和字段2出现在GROUP BY中
#否则假设你按照字段3分组了，同一个字段3对应的字段1出现重复的记录，这时该如何展示？会报错
1
2
3
4

5.HAVING分组过滤

SELECT 聚合函数(`某字段`) FROM `表名` GROUP BY `字段1` HAVING 聚合函数结果需要满足的条件;
#HAVING也是起到条件过滤的作用，因此也可以跟字段条件
#但是HAVING的执行顺序是后于WHERE的，这就是这两个关键字的区别
#因此要求聚合函数满足某一条件时，应放在HAVING后面
#要求某一字段满足条件时，放在WHERE后面
#这样做能够提升效率，因为如果优先经过WHERE的过滤
#剩下进行分组处理的记录数会大为减少
1
2
3
4
5
6
7

6.ORDER BY排序显示

SELECT `字段1`,`字段2` FROM `表名` WHERE 条件 ORDER BY `某字段` ASC或DESC;
#按照某字段升序（ASC）或降序（DESC）排列
SELECT `字段1`,`字段2` FROM `表名` ORDER BY `字段1`,`字段2`;
#按照两个字段进行排序，先排序字段1，在此基础上再排序字段2
1
2
3
4

7.LIMIT分页展示

SELECT `字段1`,`字段2` FROM `表名` WHERE 条件 ORDER BY `某字段` ASC或DESC LIMIT 偏移量,展示量;
#设置记录的展示量，作为分页操作，比如偏移量0，展示量20表示展示满足条件的前20条记录
1
2

8.CASE WHEN多分支查询

SELECT `表名`.`字段1`,CASE 表达式 WHEN 表达式的值1 THEN 自定义的值1 
							  WHEN 表达式的值2 THEN 自定义的值2
							  ELSE 自定义的值3
							  END
							  FROM `表名`;
#位于SELECT后面的CASE WHEN语句是必须要返回值的，所以THEN后面只能记录满足条件时返回的值
SELECT `表名`.`字段1`,CASE WHEN 条件表达式1 THEN 自定义的值1 
					 CASE WHEN 条件表达式2 THEN 自定义的值2
					 ELSE 自定义的值3
					 END
					 FROM `表名`;
#注意这和上面的CASE WHEN的用法是不同的
1
2
3
4
5
6
7
8
9
10
11
12

9.子查询

嵌套之外的查询为外查询（又称主查询），嵌套之内的查询为内查询（又称为子查询）。
SQL中除了GROUP BY和LIMIT之外，其他位置都可以嵌入子查询
先说一个相关子查询的概念，我的理解是主查询和子查询通过一个字段进行连接，连接条件写在子查询的WHERE或者HAVING中
比如子查询中的语句可以写为

SELECT `子查询表`.`字段1` FROM `子查询表` WHERE `子查询表`.`某字段`=`主查询表`.`某字段`;
#通过某字段连接的WHERE形式
SELECT `子查询表`.`字段1` FROM `子查询表` GROUP BY `子查询表`.`某字段` HAVING `子查询表`.`某字段`=`主查询表`.`某字段`;
#这两种方式很像通过某字段连接主查询表和子查询表，再选择相应的字段进行输出
1
2
3
4

如果查询结果返回一行一列大的数据，我称其查询结果为一个常数
SELECT中的子查询：子查询结果是一个常数，或者通过相关子查询的方式连接

SELECT `主查询字段1`,(结果为常数的子查询或者相关子查询) FROM `主查询表`;
1

FROM中的子查询：子查询结果作为一张独立的表出现，需要有别名

SELECT `子查询表别名`.`字段1`,`子查询表别名`.`字段2` FROM (子查询) "子查询表别名";
1

JOIN中的子查询：子查询结果作为一张独立的表和主查询表进行连接，需要有别名

SELECT `主查询表`.`主查询表字段`,`子查询表别名`.`子查询表字段` FROM `主查询表` JOIN (子查询) "子查询表别名" ON `主查询表`.`某字段`=`子查询表别名`.`某字段`
1

WHERE中的子查询：子查询结果用来做条件运算

SELECT `主查询表`.`主查询表字段` FROM `主查询表` WHERE `主查询表`.`主查询表字段`=(返回结果为常数的子查询);
#因为比较运算符=是单值比较，所以要求子查询结果返回为常数
SELECT `主查询表`.`主查询表字段` FROM `主查询表` WHERE `主查询表`.`主查询表字段` IN (返回结果可以为多行多列的子查询);
#根据比较运算符IN的性质，所以子查询结果可以为多行多列
#其他运算符类似推理即可
1
2
3
4
5

HAVING中的子查询：类似于WHERE的使用，这里就不多说了
ORDER BY中的子查询：多是配合相关子查询，可以理解是先连接之后再通过子查询的字段进行排序

SELECT `主查询表`.`主查询表字段` FROM `主查询表` ORDER BY (SELECT `主查询表和子查询表都要有的用于排序的字段` FROM `子查询表` WHERE `子查询表`.`某字段`=`主查询表`.`某字段`);
#我的理解是根据某字段连接之后，利用子查询SELECT的字段对于连接后的表进行排序
#当然最后显示的字段是主查询SELECT后选择的字段
1
2
3

总结一下：SELECT和ORDER BY关键字的子查询可能配合相关子查询的使用最佳，除了GROUP BY和LIMIT之后不能使用子查询之外，FROM、JOIN、WHERE、HAVING关键字后面的子查询结果多作为独立表使用，大家可以灵活选择。
优先使用多表连接的方式代替子查询，多表连接的效率比起子查询更高！

总结

本文记录常用的创建数据库和表，以及常用的表的增删改查语句，从简单到复杂。如有纰漏，还望指教！

参考