数据库管理的艺术（MySQL）：DDL、DML、DQL、DCL及TPL的实战应用（下：数据操作与查询）

MySQL基础知识：DDL、DML、DQL、DCL及TPL的使用

• DDL用于创建、修改和删除数据库及表；
• DML执行数据的增删改操作；
• DQL主要用于数据检索，包括基本查询、条件筛选、排序、分组、嵌套查询等功能；
• DCL负责权限管理，如授权和撤销访问权限；
• TPL处理事务，确保数据操作一致性。
• SQL关键词如INSERT, DELETE, UPDATE, SELECT, WHERE, ORDER BY, GROUP BY, UNION, IN, LIKE, LIMIT以及事务控制语句BEGIN, COMMIT, ROLLBACK等，构成核心操作集。
• 表约束如主键、唯一、非空和外键，保障数据完整性。常用SQL如SHOW DATABASES, USE, SHOW TABLES等，方便数据库和表的管理。

SQL分类

DML数据操作语言

操作的是表中的数据信息

1、新增记录

insert into 表名 (列名, ......, 列名) values (值, ......, 值);

注意：

1. 如果新增全部列，可以省略表名后的括号及内容，此时values后的值的个数、类型、顺序要与表中列保持一致。
2. 若想要一条insert语句新增多个记录，每个 (值, …, 值) 之间用逗号隔开。

2、删除记录

delete from 表名 [where ....] ;

没有where条件会删除表中所有数据

3、修改记录

update 表名 set 列=值, ... , 列=值 [where ...] ;

没有where条件会将对应的列的数据全部都修改

DQL数据查询语言

1、查询记录

select 列名, ... , 列名 from 表名 [where ...] ;

需要起别名使用as关键字 列名as 别名

去重使用关键字distinct

根据列去重复，如果有一样的列信息，将一样的信息合并，行数可能会减少。

distinct 列1,列2 两个列以上的，会当作一个整体，整体一样的才去重，否则全部显示。

2、条件筛选

用来筛选符合条件的记录行，并不是控制显示的列。

按照某个列或某些条件进行筛选

使用where关键字，其后可以跟：

1. 比较运算符：>、>=、 <、<=、!=、 =
2. 算术运算符：+、-、*、/
3. 逻辑运算符：and、or、not（如果and和or同时出现，and优先级更高）
4. [not] between ... and ...
5. [not] in (... , ...)
6. [not] like %xxx_（% 匹配0~n个字符，_ 仅匹配一个字符）

3、排序

order by 列名 asc/desc（连接在查询语句后，先筛选再排序）

asc 升序 （默认）

desc 降序

联合排序：

order by 列名1, 列名2

按书写顺序依次排序，不同的列可以有不同的排序规则

4、函数

1. MySQL的函数都有返回值，但不一定都有参数列表。

2. 函数直接放置在SQL语句中相当于调用，可以放置在：

   • 查询中用来显示的部分select 函数(列) from 表名;
   • 条件筛选where的后面
     

5、分组条件

group by 列

一旦搭配了分组条件，select能展示的信息就只有两种：

1. 分组条件（按什么分组就可显示什么）
2. 聚合函数（结果就剩一个值）

查询时分组了，查询的行数会减少。

比如按班级分组统计每个班的人数，那么原来可能有多个班级数据，一个班级可能又有多条学生数据，但现在查询出来就一个班级一条数据，统计着每个班级的人数。

先分组？还是先筛选条件？

1. 先筛选后分组：where + group by
2. 先分组再筛选：group by + having
3. 先筛选一部分后分组，分完组再筛选符合条件的：where + group by + having

优先级：where > group by > having > order by

6、嵌套

一个完整的SQL语句中嵌套了另一个完整的SQL语句（用括号括起来优先级更高）

1. 嵌套可以将一个查询的结果当作另一个查询的条件来使用（同一张表格）
2. 可以将查询的结果当作一张表格，在这个表格的基础上再次查询
   注意：在将结果当作表格时，需要给这个表格起别名才能用（表别名不需要as关键字，空格即可）
3. 嵌套将一个查询结果当作条件时，使用的表格可以不是同一张，将另外一张表格查询的信息，当作当前表格的查询条件。（即：与第1点不同在于是可以不同表格）

7、模糊查询

使用like关键字

% ：表示任意 0 个或多个字符。

_： 表示任意单个字符。

8、limit分页查询

语法：select * from xxx limit a,b;

a：想要显示的起始行索引，偏移量从0开始（包括此行）。
b：想要显示的行数（多少条记录）。

集合操作union

用union连接两条查询语句，将两个查询结果进行合并（是那种上下拼合的效果）

1. 要求前后两个查询子集的列数一致
2. 对应的类型没有要求
3. 拼接后显示的列名是前一个查询子集默认的列名

union和union all的区别：

1. union 合并后做去重复的处理，性能比较慢，如果有重复元素，记录的是第一次出现的那一行。
2. union all 将两个查询到的子集直接合并，不做任何处理，性能比较快。

关键字和运算符

in关键字

in() 括号内是待查询的子集，满足下面一个即可：

1. 常量固定值
2. 另一条SQL语句查询出来的结果

默认以=进行比较

以下三个关键字使用与in相似，查询是否满足子集中的条件，区别在于不允许写固定值，只允许写SQL语句，通过嵌套来获取子集。

any关键字

满足查询子集中的某一个即可

some关键字

与any一样

all关键字

满足查询子集中的全部才可以

联合查询

1、广义笛卡尔积

将两张表格或多张表格，进行无条件的拼接。(即使两张表格没有关系也可以进行拼接)

语法：select * from A , B

列的个数 ：A和B表之和，A数据在左边显示，B数据在右边显示（按上述语法）

行的个数 ：A和B表乘积

2、等值连接

语法：select * from A , B where 条件

在拼接后的一张大表格的基础上进行了where的筛选 -> 等值连接

在笛卡尔积的基础上进行了where条件筛选，只不过筛选条件都是按照值相等来进行的。

拼接后相当于在一张大的表格中挑选有用的记录，性能比较慢。

3、外连接

外连接分为左外连接和右外连接。

语法：select * from A left/right [outer] join B on 条件

可省略outer关键字

1. 两张表格A和B ，谁的数据在左边显示？

   A和B表格出现的顺序决定了 谁在左边 谁在右边

   A表格先出现，A左边显示

   B表格后出现，B右边显示

2. left和right来决定以哪一个表格的数据作为基准

   作为基准的表格数据必须全部显示出来

   非基准的表格按照on条件与之拼接，若找到则正常显示，若找不到满足条件的则显示 null。

因此，外连接查询结果有可能比等值连接的最终结果多一部分数据。

4、内连接（自连接）

语法：select * from A inner join B on 条件

特点：

1. 查询出的结果与等值连接的结果一致。
2. 内连接不分左右，不能省略inner关键字。
3. A和B是可以不同的两张表格。
4. A和B也可以是相同的一张表格，但必须给表格起别名，可能还需要当前的表格有一定的设计。

常用SQL

# 显示有哪些database
show databases; 

#使用哪个数据库
use database名字;

# 显示当前数据库有哪些table
show tables;

# 查询数据库的字符集
select schema_name, default_character_set_name from information_schema.schemata where schema_name = `查询的数据库名`;

# 查询表格信息
show table status from 数据库名 like `表名`;

#查看表有什么约束
desc 表名;
show keys from 表名;
show create table 表名;

总结

SQL分类

• DDL (Data Definition Language)：用于定义数据库对象，如创建、修改、删除数据库或表。
• DML (Data Manipulation Language)：用于操作表中的数据，包括插入、更新和删除。
• DQL (Data Query Language)：主要用于查询数据，即SELECT语句。
• DCL (Data Control Language)：用于控制用户权限，如GRANT和REVOKE命令。
• TPL (Transaction Processing Language)：用于处理事务，确保数据操作的完整性和一致性。

DDL - 数据定义语言

• 创建数据库：CREATE DATABASE
• 创建表：CREATE TABLE
• 修改表结构：ALTER TABLE
• 删除表或数据库：DROP TABLE, DROP DATABASE
• 数据类型：如INT, VARCHAR, DATE等。

DML - 数据操作语言

• 插入记录：INSERT INTO
• 删除记录：DELETE FROM
• 更新记录：UPDATE

DQL - 数据查询语言

• 查询记录：SELECT
• 条件筛选：WHERE
• 排序：ORDER BY
• 函数：如COUNT(), SUM()等。
• 分组：GROUP BY
• 嵌套查询：IN, ANY, ALL
• 模糊查询：LIKE
• 分页查询：LIMIT
• 集合操作：UNION

TPL - 事务处理语言

• 事务特性：原子性(Atomicity), 一致性(Consistency), 隔离性(Isolation), 持久性(Durability)
• 事务操作：BEGIN, COMMIT, ROLLBACK
• 隔离级别：SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED

列的约束

• 主键约束：PRIMARY KEY
• 唯一约束：UNIQUE KEY
• 非空约束：NOT NULL
• 检查约束：CHECK (MySQL中不常用)
• 外键约束：FOREIGN KEY

常用SQL语句

• 查看数据库：SHOW DATABASES
• 使用数据库：USE
• 查看表：SHOW TABLES
• 查看表信息：SHOW TABLE STATUS, DESCRIBE, SHOW KEYS, SHOW CREATE TABLE

DCL - 数据控制语言

• 用户管理：创建、修改、删除用户
• 权限管理：GRANT, REVOKE
• 查看权限：SHOW GRANTS
• 修改密码：UPDATE mysql.user
• 删除用户：DROP USER

这些语言和命令构成了MySQL的核心功能，涵盖了从数据建模到数据操作、数据查询和数据安全性管理的各个方面。掌握这些知识能够帮助你有效地管理和操作数据库。

相关文章：
《数据库管理的艺术（MySQL）：DDL、DML、DQL、DCL及TPL的实战应用（上：数据定义与控制）》