文章摘要： SQL关系型数据库查询语言的命令格式总结。

相关信息

外部相关文章

内部相关文章

提示

  • SQL关键字不区分大小写。
  • SQL语句可以以单行或多行书写，以;分号结束。
  • SQL中的注释不要使用#作为单行注释，这会导致数据迁移时导致错误。推荐使用-- 两个横杠加一个空格。

注释

  • 本文章命令中的[ ]括起来的表示可选的内容。
  • 在使用语法时需要时刻确定自己所在的“数据库”、再给出具体的“表”来查数据。
  • 查询的语法比较常用，且比较复杂，查询与“添加、修改、删除”的语法分开记忆。
  • 字符串、日期类型、数据中间有空格，都需要将数据包含在引号中。
  • COMMENT <注释>参数是可选项，用于为表名和字段名提供注释。
  • <约束>参数是可选项，允许同时支持多个约束，约束之间用空格``分割。（请参考本文约束标题的知识进行查询具体写法）
  • 支持同时创建多个字段，字段之间用英文逗号,分割。

退出命令

EXIT;

切换数据库

  • 在登录数据库后，以及在操作表时，都需要先使用以下命令切换到相应的数据库中才执行后续操作。
-- 切换数据库
USE <数据库名>
;

数据 - 定义语言（DDL）

提示

  • 主要针对数据库、表、字段进行增、删、改、查的操作命令。
  • 字段是表的每列数据的标识。
  • 这些不是实际真正存储的数据信息，此处不操作实际存储的数据。

创建-数据库

-- 创建数据库
CREATE
DATABASE <数据库名>
;

删除-数据库

DROP
DATABASE <库名>
;

修改-数据库

-- 修改数据库的字符集
ALTER
DATABASE <数据库名>
CHARACTER SET
    <字符集名称>
;

-- 修改数据库的校验规则
ALTER
DATABASE <数据库名>
COLLATE
    <校对规则名称>
;

查询-数据库

-- 列出当前用户下的所有数据库
SHOW DATABASES
;

-- 显示当前正在使用的数据库
SELECT DATABASE()
;

创建-表

-- 创建表。
CREATE
TABLE <表名> [COMMENT <注释>]
;

-- 创建表时并创建字段。
CREATE
TABLE <表名> (
	<字段名> <数据类型> [COMMENT <注释>],
	...
) [COMMENT <注释>]
;

-- 创建表时并创建字段，并且为字段添加约束。
CREATE
TABLE <表名> (
	<字段名> <数据类型> [<约束>...] [COMMENT <注释>],
	...
) [COMMENT <注释>]
;

-- 创建表时并创建字段，并且为字段添加外键约束，顺便关联外表。
-- 关联外表的约束条件是：需要确保另一个表的字段名的约束条件是主键约束`PRIMARY KEY`。
CREATE
TABLE <表名> (
	<字段名> <数据类型> [<约束>...] [COMMENT <注释>],
	...
	[CONSTRAINT <约束注释/别名>] FOREIGN KEY <字段> REFERENCES <表名>(<字段名>)
) [COMMENT <注释>]
;

删除-表

DROP
TABLE <表名>
;

修改-表

-- 修改表名
ALTER
TABLE <表名> 
RENAME TO 
    <新表名>
;

-- 将原数据库中的所有表移动到新数据库中，可同时修改表名。
RENAME
TABLE <数据库名.表名> 
TO 
    <新数据库名.新表名>
;

查询-表

-- 列出当前数据库下的所有表
SHOW TABLES
;

-- 查询指定数据库下的所有表
SHOW TABLES
FROM <表名>
;

添加-表字段

-- 添加字段
ALTER
TABLE <表名>
ADD 
    <字段名> <数据类型> [<约束>...] [COMMENT <注释>]
;

删除-表字段

-- 删除字段
ALTER
TABLE <表名>
DROP 
    <字段名>
;

修改-表字段

-- 修改字段的数据类型
ALTER
TABLE <表名>
MODIFY 
    <字段名> <新数据类型>
;

-- 修改字段的名称和数据类型
ALTER
TABLE <表名>
CHANGE 
    <旧字段名> <新字段名> <新数据类型> [<约束>...] [COMMENT 注释]
;

查询-表结构

-- 显示表中字段的相关参数
DESC <表名>
;

-- 显示指定表的建表语句（获取可重复创建该表的命令语句）
SHOW
CREATE
TABLE <表名>
;

案例

-- 创建数据库
CREATE DATABASE test;
-- 切换到该数据库
USE test;

-- 创建"用户"表
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',                       -- 自动增长
    account VARCHAR(20) NOT NULL COMMENT '账号',
    password VARCHAR(20) NOT NULL COMMENT '密码',
    name VARCHAR(10) NOT NULL COMMENT '昵称',
    phone CHAR(11) NOT NULL COMMENT '电话',
    balance DECIMAL(10,2) DEFAULT 0.00 COMMENT '余额',                   -- 默认0.00
    avatar_path VARCHAR(255) COMMENT '头像路径',                          -- 存储头像图片的路径或URL
    register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间', -- 存储注册时间
    state TINYINT DEFAULT 1 COMMENT '用户状态'                           -- 表示用户的状态，0（禁用）、1（可用）
) COMMENT '用户信息';

-- 创建“收货地址”表
CREATE TABLE address (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    user_id INT NOT NULL COMMENT '用户ID',              -- 外键
    address VARCHAR(255) NOT NULL COMMENT '收货地址',
    FOREIGN KEY(user_id) REFERENCES user(id)           -- 关联外表 此处进行关联另一张表
) COMMENT '收货地址';

数据 - 操作语言（DML）

提示

  • 用于对表的数据进行增加、删除、修改的操作操作命令。
  • 提示：此处没有查询命令。
  • 需要根据已经定义创建完的数据库、表、表中的字段后，再进行操作的。

添加数据

注释

  • 插入数据时，字段顺序需要一一对应。
  • 字符串、日期类型、数据中间有空格，都需要将数据包含在引号中。
  • 插入的数据大小，应该在字段的规定范围中。
-- `INTO`：指定某个表中的字段
-- `VALUES`：指定添加的新值

-- 批量添加数据，字段与新值的顺序需要一一对应
INSERT
INTO <表名>
    (<字段名>, ...) 
VALUES
	(<值>, ...),
	...
;

-- 给全部字段添加数据（在不指定某个表中需要修改的具体字段时，表示全部字段添加数据）
INSERT
INTO <表名>
VALUES
    (<值>, ...),
    ...
;

删除数据

-- `WHERE <条件>`：可选参数，若没有指定条件将删除该表中的所有数据
-- `DELETE`：不能删除表中的字段，仅负责删除数据
DELETE
FROM <表名>
WHERE
	<条件>
;

-- 清空表
TRUNCATE <表名>;

修改数据

-- `WHERE <条件>`：条件，可选参数
UPDATE <表名> 
SET 
	<字段>=<值>,
	...
WHERE 
	<条件>
;

数据 - 查询语言（DQL）

提示

  • 专注于查询表中的数据。
  • 查询数据库中的数据，与数学中的集合类似。
  • 提示：查询语法有严格的顺序要求。

查询语法完整编写顺序如下：

-- 第一、基本查询语法，必须
SELECT
	<字段名>, ...
FROM
	<表名>, ...

-- 第二、条件查询语法，可选
WHERE
	<条件>

-- 第三、分组查询语法，可选
GROUP BY
	<分组字段>
HAVING
	<分组后字段条件>

-- 第四、排序查询语法，可选
ORDER BY
	<字段名> <排序方法>,
	...

-- 第五、分页查询语法，可选
LIMIT
	<起始索引数>，<查询记录数>
;

注释

可以配合SQL聚合函数进行更多查询方式。

SQL聚合函数相关文章：Language-SQL-函数

基本查询

  • 关键字：SELECT、FROM
-- 查询所有的数据
SELECT
    *
FROM
	<表名>, ...
;

-- 查询表中的所有记录中指定字段数据
SELECT
	<字段名>, ...
FROM
	<表名>, ...
;

-- 为字段在显示时取别名显示，`AS`关键字可以省略不写
SELECT
	<字段名> AS "<别名>"，
	...
FROM <表名>
;

基本查询-去重

  • 在某个字段的前面添加DISTINCT关键字，执行去重操作。
-- 去除重复记录
SELECT
DISTINCT
	<字段名>, ...
FROM
	<表名>, ...
;

条件查询

  • 关键字：WHERE
  • WHERE的条件设定方法，可以查看运算符标题一栏。
-- 查询指定过滤条件后的记录
SELECT
	<字段名>, ...
FROM
	<表名>, ...
WHERE
	<条件>
;

分组查询

  • 关键字：GROUP BY、HAVING
SELECT
	<字段名>, ...
FROM
	<表名>, ...
GROUP BY
	<字段名> 
HAVING
	<分组后条件>
;

排序查询

  • 关键字：ORDER BY
-- `ORDER BY <排序方法>`支持参数：`ASC`升序（默认），`DESC`降序。
SELECT
	<字段名>, ...
FROM
	<表名>, ...
ORDER BY
	<字段名> <排序方法>,
	...
;

分页查询

  • 关键字：LIMIT
  • LIMIT的起始索引计算公式：起始索引数 = (查询页码 - 1) * 每页显示记录数
  • 提示：查询页码从1开始计算，起始索引数是从0开计算。
-- `LIMIT`：起始索引是从0开始
SELECT
	<字段名>, ...
FROM
	<表名>, ...
LIMIT
	<起始索引数>，<查询记录数>
;

多表查询-交叉查询

  • 笛卡尔积（Cartesian Product）：列出两个（或更多）表中所有可能的组合。
  • 如果你执行了一个没有连接条件的查询，就会得到一个笛卡尔积。
-- 没有筛选条件
SELECT
    <字段名>, ...
FROM
	<表1>,<表2>, ...
;

多表查询-内连接

  • 相当于查询A、B交集部分的数据。（会剔除不符合条件的数据）
-- 隐式内连接
SELECT
    <字段名>, ...
FROM
	<表1>,<表2>, ...
WHERE
	<条件>
;

-- 显示内连接
SELECT
    <字段名>, ...
FROM <表1> 
    [INNER] JOIN <表2> ON <连接条件>
    ...
;

案例

-- 隐式内连接
SELECT
    agents.name, agents.region
FROM
    agents, sales
WHERE
    agents.agent_id = sales.agent_id
;

-- 显示内连接
SELECT
    agents.name, agents.region
FROM agents
    INNER JOIN sales ON agents.agent_id = sales.agent_id
;

多表查询-外连接

（会将查询没有值的数据也算进去）

  • 左外连接：查询左表所有数据（包括两张表交集部分数据）
  • 右外连接：查询右表所有数据（包括两张表交集部分数据）
  • 满外连接：并集。
-- 左外连接
SELECT
    <字段名>, ...
FROM <表1>
    LEFT [OUTER] JOIN <表2> ON <连接条件>
    ...
;

-- 右外连接
SELECT
    <字段名>, ...
FROM <表1>
    RIGHT [OUTER] JOIN <表2> ON <连接条件>
    ...
;

-- 满外连接
SELECT
    <字段名>, ...
FROM <表1>
    FULL [OUTER] JOIN <表2> ON <连接条件>
    ...
;

多表查询-子查询（嵌套查询）

  • 子查询：在一个SQL语句的基础上查询另一个表格的SQL语句。
  • 将条件的判断使用另一个查询语句的结果。

注释

查询关键字

  • ALL：指定条件必须满足子查询返回的所有结果。
  • ANY：比较操作。只要有一个满足即可。
  • SOME：比较操作。只要有一个满足即可。
  • IN：指定一个字段值是否在子查询返回的结果集中。
  • EXISTS：检查子查询是否有返回结果，返回一个布尔值。
-- 子查询
SELECT
	<字段名>, ...
FROM
	<表>
WHERE
	[<字段名>] [<判断运算符>] [<查询关键字>] (<子查询语句>)
;

多表查询-自关联

  • 一张表中的 “字段的数据” 关联的是当前表中的 “另一个字段中的数据”。
-- 查询同一张表中的两个字段的数据是否相同
SELECT
    <字段名>, ...
FROM
    <表1> <别名1>
[INNER] JOIN
    <表2> <别名2>
ON
<别名1>.<字段名>=<别名2>.<字段名>
;

用户 - 控制语言（DCL）

提示

  • 用来管理数据库用户、控制数据库的访问权限。

用户管理-创建用户

-- 若将"用户名@主机名"的主机名改为`%`，则表示任意主机都可访问。
CREATE
USER 
	"用户名"@"主机名" 
IDENTIFIED BY 
	"密码"
;

用户管理-删除用户

DROP
USER
	"用户名"@"主机名"
;

用户管理-修改用户

-- 修改用户密码
-- `mysql_native_password`：是MySQL的加密方式
ALTER
USER
	"用户名"@"主机名"
IDENTIFIED WITH
	mysql_native_password
BY
	"新密码"
;

用户管理-查询用户表

-- 提示：MySQL的用户信息都存放在`root用户中的mysql数据库下的user表中`。
USE mysql
;

SELECT * FROM user
;

权限控制-授予权限

GRANT
	<权限>,
	...
ON
	<数据库名.表名>,
	...
TO
	"用户名"@"主机名"
;

权限控制-撤销权限

REVOKE
	<权限>,
	...
ON
	<数据库名.表名>
FROM
	"用户名"@"主机名"
;

权限控制-查询权限

SHOW
GRANTS FOR
	"用户名"@"主机名"
;

权限控制-刷新权限表

  • 用于刷新权限表，重新加载MySQL服务器上的权限数据。
  • 以确保MySQL服务器上的权限更改立即生效。
FLUSH PRIVILEGES;

事务 - 控制语言（TCT）

提示

  • 事务是一组操作的集合，是一个不可分割的工作单位。
  • 事务是一个抽象的对象在监听对数据库做的任何操作。
  • 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作流程图

开启事务

  • 开启一个事务（对象）,建立一个新存档。为操作的表上加锁，防止其他用户修改该表。
  • START TRANSACTION;

保存点

  • 用于保存一个中间存档。可以有多个中间存档。
  • SAVEPOINT <保存点名称>;

回滚保存点

  • 用于回退到上一个中间存档。
  • ROLLBACK TO <保存点名称>;

提交事务

  • 保存数据、删除中间存档/历史记录。释放锁、关闭事务。
  • COMMIT;

回滚事务

  • 撤回全部操作、回到原始存档。释放锁、关闭事务。
  • ROLLBACK;

隔离级别

隔离级别脏读不可重复读幻读加锁读
read uncommitted（未提交读）YYY不加锁
read committed（提交读）YY不加锁
repeatable read（重复读）不加锁
serialize（串行化）加锁

查询

-- 查看当前“会话”的隔离级别
SELECT 
	@@transaction_isolation
;

-- 查看当前“系统”的隔离级别
SELECT 
	@@global.transaction_isolation
;

修改

-- 设置当前“会话”的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL
	<隔离级别>
;

-- 设置当前“系统”的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL
	<隔离级别>
;

索引

提示

  • 索引（index）是一种数据结构。
  • 优点：用于帮助数据库提高查询数据的效率，降低数据库的IO成本。
  • 缺点：索引会占用磁盘空间，对数据进行增加、删除、修改时需要同时维护索引，因此会降低该操作的效率。

创建索引

-- 创建普通索引
CREATE INDEX
	<索引名>
ON
	<表名>(<字段名>)
;

-- 创建唯一索引，确保索引列中的值是唯一的，`UNIQUE`代表唯一的约束。
CREATE UNIQUE INDEX
	<索引名>
ON
	<表名>(<字段名>)
;

-- 创建复合索引，在多个列上创建一个索引。
CREATE UNIQUE INDEX
	<索引名>
ON
	<表名>(<字段名>, ...)
;

查看索引

-- 查看表上所有索引的详细信息。
SHOW INDEX FROM
	<表名>
;

删除索引

DROP INDEX
	<索引名>
ON
	<表名>
;
更新时间: