SQL是结构化的查询语句
SQL的种类
- DDL:数据定义语句
- DCL:数据控制语言
- DML:数据操作语言
- DQL:数据查询语言

SQL 语句概览

这里介绍了 SQL 的概念和四大分类。这里理清一下：

SQL (Structured Query Language)：结构化查询语言，是我们和关系型数据库（如 MySQL）沟通的标准语言。
SQL 的四大种类（面试常考）：
- DDL (Data Definition Language) 数据定义语言：用来定义和管理数据库对象的（如库、表、列）。核心指令：CREATE（建）、DROP（删）、ALTER（改）。（主要讲的就是 DDL 对库的操作）。
- DCL (Data Control Language) 数据控制语言：用来管理权限的。核心指令：GRANT（授权）、REVOKE（收回权限）。
- DML (Data Manipulation Language) 数据操作语言：用来操作表中的数据的。核心指令：INSERT（增）、DELETE（删）、UPDATE（改）。
- DQL (Data Query Language) 数据查询语言：专门用来查询数据的。核心指令：SELECT。（虽然很多地方把它归为 DML，但单独拎出来更严谨）。

DDL数据定义语句

对库或者表进行操作的语句
创建数据库

create database db01;
# 创建数据库
create database DB01;
# 数据库名区分大小写(注意windows里面不区分)
# show variables like 'lower%'; 可以看到lower_case_table_names默认0是区分大小写通过/etc/my.cnf [mysqld]下可以永久修改
show databases;
# 查看数据库(DQL)
show create database db01;
# 查看创建数据库语句
help create database;
# 查看创建数据库语句帮助
create database db02 charset utf8;
# 创建数据库的时候添加属性

删除数据库

drop database db02;
# 删除数据库db02

修改定义库

alter database db01 charset utf8;
show create database db01;

DDL 数据定义语句 —— 针对“数据库”的操作

教你怎么对“库”进行增删改查。

1. 创建数据库 (CREATE)

#  创建数据库
create database db01;

最基础的建库语法，创建一个名为 db01 的数据库。

create  database  DB01;

# 数据库名区分大小写(注意windows里面不区分)
# show variables like 'lower%'; 可以看到lower_case_table_names默认0是区分大小写通过/etc/my.cnf [mysqld]

重点原理（避坑）：
- 在 Linux 系统下，MySQL 默认是区分大小写的。db01 和 DB01 是两个不同的数据库。这由系统变量 lower_case_table_names 控制，默认值为 0（0代表区分大小写，1代表不区分，全变小写）。
- 在 Windows 系统下，由于文件系统（NTFS）本身不区分大小写，MySQL 默认 lower_case_table_names=1，所以不区分。
- 这就解释了为什么很多人在 Windows 上写好的代码，放到 Linux 服务器上运行会报错“表不存在”——因为大小写不一致。

create database db02 charset utf8;
# 创建数据库的时候添加属性，添加一个utf-8编码，

建库时指定字符集。非常实用！如果不指定，==MySQL 默认可能是 latin1（不支持中文）。指定为 utf8 可以防止以后存入中文时出现乱码==。

2. 查看数据库 (SHOW)

show databases;
# 查看数据库(DQL)

列出当前 MySQL 服务器中所有的数据库名称。

show create database db01;
# 查看创建数据库语句，显示当初用什么字符集，什么排序规则创建

极其常用！ 当你接手一个别人的数据库，不知道它当初是用什么字符集、什么排序规则创建的时候，用这个命令，MySQL 会把当初创建它的 CREATE 语句原原本本地打印出来。

help create database;
# 查看创建数据库语句帮助

查官方文档。比如你想知道建库时还能加什么参数（如 COLLATE 排序规则），直接 help 一下非常方便。

3. 删除数据库 (DROP)

drop database db02;
# 删除数据库db02

极度危险命令！ 它会瞬间且不可逆地删除数据库里所有的表、数据、权限设置。在==生产环境中执行此命令前，务必确认已做备份==。

4. 修改数据库定义 (ALTER)

alter database db01 charset utf8;
show create database db01;

修改数据库属性。比如刚才建的 db01 字符集不对，可以用 ALTER 命令修改它的默认字符集为 utf8。
修改完后再用 show create database db01; 验证一下，确保修改成功。

5、创建表 (CREATE TABLE)

建表是 DDL 的基础，==核心在于定义列名、数据类型以及数据属性==。

1. 基础语法：

create table 表名(
    列名1 数据类型,
    列名2 数据类型,
    ...
);

笔记中示例定义了：int（整数）、varchar（可变长字符）、tinyint（微小整数）、enum（枚举）、datetime（时间）。

6 进阶语法（带属性与引擎设置）：

create table student(
    sid int not null primary key auto_increment comment '学号',
    sname varchar(20) not null comment '学生姓名',
    ...
) charset utf8 engine innodb;

数据属性（写在数据类型后面）：
- not null：非空约束，该列必须有值。
- primary key：主键约束（唯一且非空）。
- auto_increment：自增属性，通常配合主键使用（必须是整数类型）。
- default：设置默认值（如 default 'm'）。
- comment：添加列注释，方便后期维护。
表级属性（写在括号外面）：
- charset utf8：指定表的字符集（防止中文乱码）。
- engine innodb：指定存储引擎（InnoDB 支持事务，是目前的默认推荐引擎）。

7、查看表信息 (SHOW / DESC)

show tables; ：查看当前数据库下所有表的名称。
show create table 表名;：查看该表当初被创建时的完整 SQL 语句（包含字符集、引擎、注释等，非常实用）。
desc 表名;：查看==表的结构定义==（列出所有列名、数据类型、是否为空、主键、默认值等详细信息）。

8、删除表 (DROP)

drop table 表名;：极度危险命令。删除表及其所有数据，还原需要事先备份。

9、修改表结构 (ALTER TABLE)

ALTER TABLE 是后期维护表结构最常用的命令，用于在不删除表的情况下修改其定义。

1. 修改表名：

alter table 旧表名 rename 新表名;

2. 添加列 (ADD)：

alter table 表名 add 列名数据类型; （默认添加到末尾）
alter table 表名 add 列名数据类型 first; （添加到表的第一列）
alter table 表名 add 列名数据类型 after 指定列名; （添加到指定列的后面）
alter table 表名 add 列1 类型, add 列2 类型; （一次添加多列）

3. 修改列 (MODIFY / CHANGE)：

alter table 表名 modify 列名新数据类型;：只能修改列的数据类型和属性，不能修改列名。
alter table 表名 change 旧列名新列名新数据类型;：既能修改列名，也能修改数据类型（功能更强大，通常使用这个来改列名）。

4. 删除列 (DROP)：

alter table 表名 drop 列名;

学习与实操建议

1. 命名规范与语法避坑

统一命名格式：在 Linux 环境下，数据库名和表名默认严格区分大小写（由系统变量 lower_case_table_names 控制，默认为 0）。为了避免跨平台（Windows 迁移至 Linux）时出现“表不存在”的错误，建议统一采用小写字母加下划线的命名规范（如 wordpress_db）。
避开保留字：==严禁使用 MySQL 的保留字（如 order、user、database、select 等）作为库名或表名==。若必须使用，需在 SQL 语句中强制加上反引号（“`），这会增加后续维护的复杂度，应尽量避免。

2. 字符集（Charset）配置标准

显式指定字符集：==在执行 CREATE DATABASE 时，必须显式加上 charset 参数==。如果不指定，MySQL 可能会使用默认的 latin1，导致存储中文时出现乱码。
推荐使用 utf8mb4：相较于旧版的 utf8，utf8mb4 是更完整的字符集标准，支持 4 字节字符（如 Emoji 表情），能有效避免特殊字符写入时的报错。

3. 操作环境与权限隔离

使用专用测试库：严禁在系统自带的核心库（如 mysql、information_schema、performance_schema）中执行 DDL 操作。必须建立专用的测试数据库（如 test_db）进行命令练习。
高频命令演练：在测试库中反复执行 create -> show create -> alter -> drop 的完整生命周期操作，观察每一步的系统反馈和报错信息。

4. 结合现有备份知识的底层逻辑

之前掌握的 mysqldump 工具，理解逻辑备份的本质：备份文件（如 .db 或 .sql 文件）实质上是将数据库的结构和数据转换成了一系列的 DDL（定义结构）和 DML（插入数据）语句。
在执行==数据恢复==（source 或 < 导入）时，若备份文件中包含建库语句（如 CREATE DATABASE），则无需手动建库；若备份文件中仅包含建表语句，则==必须先手动创建一个同名数据库==，否则导入会报错。

备份流程图

graph TD
    %% 定义样式
    classDef terminal fill:#2d2d2d,stroke:#666,stroke-width:2px,color:#fff;
    classDef mysql fill:#00758f,stroke:#005a6e,stroke-width:2px,color:#fff;
    classDef action fill:#f9f9f9,stroke:#333,stroke-width:1px;
    classDef success fill:#d4edda,stroke:#28a745,stroke-width:2px,color:#155724;
    classDef danger fill:#f8d7da,stroke:#dc3545,stroke-width:2px,color:#721c24;

    %% 阶段一
    subgraph 第一阶段: 准备数据
        A1[终端登录 MySQL]:::terminal
        A2[创建库 restore_test 并切换]:::mysql
        A3[创建表 student]:::mysql
        A4[插入测试数据]:::mysql
        A5[验证数据存在]:::success
    end

    %% 阶段二
    subgraph 第二阶段: 执行备份
        B1[退出 MySQL 命令行]:::mysql
        B2[终端执行 mysqldump 备份]:::terminal
        B3[生成备份文件 restore_test.db]:::action
    end

    %% 阶段三
    subgraph 第三阶段: 模拟灾难
        C1[重新登录 MySQL]:::terminal
        C2[删除数据库 restore_test]:::danger
        C3[验证已删除]:::action
    end

    %% 阶段四
    subgraph 第四阶段: 恢复数据
        D1[手动创建空库]:::mysql
        D2[切换到该库]:::mysql
        D3[执行 source 导入]:::mysql
    end

    %% 阶段五
    subgraph 第五阶段: 最终验证
        E1[查看表结构]:::mysql
        E2[查询数据成功恢复]:::success
    end

    %% 连接线
    A1 --> A2 --> A3 --> A4 --> A5
    A5 --> B1
    B1 --> B2 --> B3
    B3 --> C1 --> C2 --> C3
    C3 --> D1 --> D2 --> D3
    D3 --> E1 --> E2

    %% 强调核心逻辑
    C2 -.->|关键前提: 必须先建库| D1

学习与实操建议

1. 建表原则：规范与前瞻性

严格定义属性：除了主键自增列，其他列尽量加上 not null 和 default 约束，这能有效减少业务代码中的空指针报错。
必带注释：所有列都必须写 comment 注释，尤其是 enum 或 tinyint 表示状态的列（如 1代表正常，0代表禁用），否则后期维护极其困难。
显式指定引擎和字符集：建表语句末尾强制带上 engine innodb 和 charset utf8mb4，避免依赖数据库的全局默认设置。

2. 操作 ALTER TABLE 的注意事项

性能影响：在==数据量极大的表==上执行 ALTER TABLE（特别是 MODIFY 修改数据类型、CHANGE 修改列名）==可能会导致锁表==。在生产环境中，建议在业务低峰期执行，或使用在线 DDL 工具（如 pt-online-schema-change）。
修改数据类型可能导致数据丢失：例如将一个包含长字符串的 varchar(100) 列修改为 char(10)，会导致超出 10 个字符的数据被截断。

3. 强化命令记忆

区分 MODIFY 与 CHANGE：记住 MODIFY 只改属性不改名字，CHANGE 名和属性都能改。
明确 DESC 的用途：在遇到线上故障排查时，第一时间使用 desc 表名; 确认表结构，比去看建表语句更快捷。

MySQL 列属性（Column Attributes）标准规范总结

在 MySQL 的 CREATE TABLE 或 ALTER TABLE 语句中，==列属性用于对列的数据进行约束和规范==。根据适用范围，列属性可分为通用属性和特殊属性两类。

一.通用属性（适用于绝大多数列）

这些属性可以根据业务需求，添加在任意数据类型的列上。

属性	作用	适用范围	说明
`NULL` / `NOT NULL`	定义该列是否允许为空值	所有数据类型	默认为 `NULL`（允许为空）。建议核心字段显式声明 `NOT NULL`。
`DEFAULT 值`	定义该列的默认值	所有数据类型	插入数据时若未指定该列，则自动填入默认值。
`COMMENT '注释'`	为该列添加说明注释	所有数据类型	强烈建议所有列都添加，便于后期维护。
`UNSIGNED`	无符号（非负数）	仅限数值类型	如 `INT UNSIGNED`，不能加在字符或时间类型上。

二. 特殊属性（具有严格使用限制）

这类属性不能随意添加，必须满足特定的前提条件。

1. `AUTO_INCREMENT`（自增）

作用：插入数据时，若未指定该列的值，数据库自动生成递增的整数。
三大硬性限制：
1. 数量限制：一张表最多只能有一个列使用 AUTO_INCREMENT。
2. 索引限制：使用 AUTO_INCREMENT 的列，必须被定义为 PRIMARY KEY（主键）或 UNIQUE KEY（唯一键）。
3. 类型限制：仅支持整数类型（如 INT、BIGINT、TINYINT），不可用于 VARCHAR、DATETIME 等类型。

2. `PRIMARY KEY`（主键）

作用：唯一标识表中的每一行记录。
限制：一张表只能有一个主键，主键列的值必须唯一且非空（隐含 NOT NULL 和 UNIQUE 约束）。

3. `UNIQUE KEY`（唯一键）

作用：保证该列的值唯一。
限制：与主键不同，唯一键允许为空值（NULL），且一张表可以有多个唯一键。

三. 标准建表示例

以下示例展示了通用属性与特殊属性的正确组合方式：

五、 核心原则
明确适用范围：添加属性前，先确认该属性是否适用于当前列的数据类型（如 UNSIGNED 仅限数值）。

遵守自增规则：AUTO_INCREMENT 仅能用于整数主键，且每表仅限一列。

规范定义约束：核心字段必须显式声明 NOT NULL，业务常用字段应设置 DEFAULT 默认值。CREATE TABLE student (
    -- 特殊属性：整数 + 主键 + 自增（正确）
    sid INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',

    -- 通用属性：非空 + 注释
    sname VARCHAR(20) NOT NULL COMMENT '学生姓名',

    -- 通用属性：默认值 + 注释
    sage TINYINT DEFAULT 18 COMMENT '学生年龄',

    -- 通用属性：非空 + 默认值 + 注释
    sgender ENUM('m','f') NOT NULL DEFAULT 'm' COMMENT '学生性别',

    -- 通用属性：非空 + 注释
    comtime DATETIME NOT NULL COMMENT '入学时间'
) CHARSET=utf8 ENGINE=InnoDB;

四.核心原则

明确适用范围：添加属性前，先确认该属性是否适用于当前列的数据类型（如 UNSIGNED 仅限数值）。
遵守自增规则：AUTO_INCREMENT 仅能用于整数主键，且每表仅限一列。
规范定义约束：核心字段必须显式声明 NOT NULL，业务常用字段应设置 DEFAULT 默认值。

MySQL DCL 数据控制语言

整体分为两大块： DCL（数据控制语言）：管权限（用户授权、回收权限）

DML（数据操作语言）：管表里的数据（增、改、删，还有伪删除方案）

补充：SQL 四大分类简单区分

DDL：库、表结构（create alter drop truncate）

DML：表内数据（insert update delete）

DCL：权限管理（grant revoke）

DQL：查询 select

一、DCL 数据控制语言

DCL 作用：控制数据库用户的访问权限，创建用户、分配权限、收回权限

1. grant 授权语句

grant all on *.* to root@'192.168.226.%' identified by '123456'

grant all：授予所有权限（增删改查、建表等全部权限）
on *.*：*.* 代表所有库。所有表；如果写成 testdb.* 就是 testdb 库下面所有表
```
to root@'192.168.226.%'
```
- root：用户名
- 192.168.226.%：允许登录的客户端 IP，%是通配符，代表 192.168.226 网段任意 IP都可以登录
identified by '123456'：设置这个用户的密码为 123456

注释：授予 root@’192.168.226.%’ 用户所有权限，不是超级管理员

grant all on *.* to root@'192.168.226.%' identified by '123456' with grant option;

多了 with grant option：超级链路权限，==这个用户拿到权限后，还可以把自己的权限再授权给别的用户==

用户资源限制参数（限制用户使用资源）

max_queries_per_hour：用户每小时最多能执行多少次查询
max_updates_per_hour：用户每小时最多执行多少次更新（update/insert）
max_connections_per_hour：用户每小时最多发起多少次连接
max_user_connections：该用户同一时间最多能保持多少个并发连接

用途：防止某个用户疯狂占用数据库资源，做限流。

2. revoke 回收权限

revoke select on *.* from root@'192.168.226.%';

含义：从用户root@192.168.226.% 回收 select（查询）权限
语法：revoke 权限 on 库.表 from 用户;

show grants for root@'192.168.226.%';

查看这个用户当前拥有哪些权限。

二、DML 数据操作语言

DML：操作表里面的行数据，不修改表结构；包含 insert新增、update修改、delete删除

1. insert 插入数据

写法 1：简写（不推荐，必须和表字段顺序完全一致）

insert into stu values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

特点：values 后面的值，顺序、数量、类型必须和建表时字段完全对应，少写 / 顺序错直接报错。

写法 2：规范写法（推荐，指定字段）

insert into stu(classid,birth,sname,sage,sgender,comtime,telnum,qq) 
values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

在表名后面 (字段1,字段2...) 指定要插入哪些列
values 里面的值和前面括号里字段一一对应；可以只插入部分字段，没写的字段会自动填充默认值（允许 null 的填 null）

写法 3：一次性插入多条记录

insert into stu(classid,birth,sname,sage,sgender,comtime,telnum,qq) 
values
('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),
('linux02',2,NOW(),'zhangs i',21,'f',NOW(),111,1234567);

多条记录用逗号隔开，一次性写入，性能优于多次单独 insert。

NOW()：mysql 内置函数，获取当前时间（年月日时分秒）

2. update 更新修改数据

⚠️重点：update不加 where 条件会修改全表，生产严禁！！

update student set sgender='f';
# 不规范！没有where，会把student表所有行sgender全部改成f
update student set sgender='f' where sid=1;
# ✅规范写法：只修改sid=1这一行，把性别sgender改成f
update student set sgender='f' where 1=1;
# where 1=1 永远成立，等价于没有where，修改整张表

修改 mysql 用户密码

update mysql.user set password=PASSWORD('123456') where user='root' and host='localhost';

mysql.user：mysql 内置系统表，存放所有数据库账号信息
PASSWORD()函数：把明文密码加密
修改系统用户后，必须执行 flush privileges; 刷新权限才能生效

3. delete 删除数据

⚠️delete 不加 where 会清空整张表，高危操作！

delete from student;
# 不规范：删除student表里全部数据，表结构保留，自增id继续累加
delete from student where sid=3;
# ✅规范删除：只删除sid=3的这一行

truncate 清空表

truncate table student;

重点区分 delete vs truncate

truncate 属于DDL 语言（不是 DML），不是删除行，是重建整张表
清空所有数据，自增主键重置，重新从 1 开始
不记录事务日志，速度远快于 delete；不能回滚
表结构保留

特性	delete	truncate
分类	DML	DDL
自增 id	继续增长	重置从 1 开始
事务	可以回滚	不可回滚
速度	慢（逐行删除记日志）	快，直接重建表

三.alter ..status 伪删除（逻辑删除）

业务场景：很多重要业务数据不能物理删除（删掉就找不回来了），比如订单、用户数据，需要保留记录，只是前端 “看不见”，用伪删除（逻辑删除）

思路：不使用 delete，新增一个状态字段标记是否删除

新增状态字段

alter table student add status enum('1','0') default 1;

alter table 修改表结构，给 student 表增加 status 字段
enum (‘1′,’0’)：只能存 ‘1’ 或者 ‘0’
default 1：默认值是 1
- status='1'：正常有效数据
- status='0'：标记为已删除

“删除” 操作，实际是 update 修改状态

update student set status='0' where sid=1;
# 把sid=1的数据标记为已删除，数据还在数据库里

查询数据的时候，只查正常数据

select * from student where status=1;
# 查询所有没有被标记删除的数据，被标记0的不会查出来

优点：数据永久保留，可以恢复；缺点：表数据会越来越多，需要定期归档。

`where` 就是用来指定筛选条件

where 作用：过滤行，只对满足条件的行执行 update /delete/select。

update student set sgender='f' where sid=1;

👉 where sid=1：只找到 sid 等于 1 那一行，只修改这一行。

delete from student where sid=3;

👉 where sid=3：只删除 sid=3 这一行。

如果没有 where，或者 where 条件永远成立

update student set sgender='f';
-- 没有where → 表里面【所有行】全部修改

update student set sgender='f' where 1=1;
-- 1=1永远是true，条件对全部行成立，等价于不带where，全表修改

⚠️生产环境大坑：update /delete 忘记写 where，直接全表改 / 全表删！

where 不只在 update、delete 用，查询 select 也用

select * from student where status=1;

where status=1：只查询状态为正常的数据，过滤掉标记删除的数据。

where 就是行过滤器：满足条件的行才会被操作，不满足的跳过。

重点总结

DCL：grant 授权，revoke 回收权限，控制账号访问
DML：insert 增，update 改，delete 删；update、delete 必须带 where
delete 只删行、truncate 重建表，自增主键行为不一样
生产业务优先逻辑伪删除，慎用物理 delete/truncate

Mysql的SQL语句

SQL 语句概览

DDL数据定义语句

DDL 数据定义语句 —— 针对“数据库”的操作

1. 创建数据库 (CREATE)

2. 查看数据库 (SHOW)

3. 删除数据库 (DROP)

4. 修改数据库定义 (ALTER)

5、 创建表 (CREATE TABLE)

6 进阶语法（带属性与引擎设置）：

7、 查看表信息 (SHOW / DESC)

8、 删除表 (DROP)

9、 修改表结构 (ALTER TABLE)

学习与实操建议

备份流程图

学习与实操建议

MySQL 列属性（Column Attributes）标准规范总结

一.通用属性（适用于绝大多数列）

二. 特殊属性（具有严格使用限制）

1. AUTO_INCREMENT（自增）

2. PRIMARY KEY（主键）

3. UNIQUE KEY（唯一键）

三. 标准建表示例

四.核心原则

MySQL DCL 数据控制语言

一、DCL 数据控制语言

1. grant 授权语句

用户资源限制参数（限制用户使用资源）

2. revoke 回收权限

二、DML 数据操作语言

1. insert 插入数据

写法 1：简写（不推荐，必须和表字段顺序完全一致）

写法 2：规范写法（推荐，指定字段）

写法 3：一次性插入多条记录

2. update 更新修改数据

修改 mysql 用户密码

3. delete 删除数据

truncate 清空表

三.alter ..status 伪删除（逻辑删除）

where 就是用来指定筛选条件

如果没有 where，或者 where 条件永远成立

where 不只在 update、delete 用，查询 select 也用

重点总结

插入数据实践

更多文章

Redis 数据库介绍和环境准备

Mysql 单表查询和字符集介绍

MySQL 索引介绍

MySQL 执行计划（explain)

5、创建表 (CREATE TABLE)

7、查看表信息 (SHOW / DESC)

8、删除表 (DROP)

9、修改表结构 (ALTER TABLE)

1. `AUTO_INCREMENT`（自增）

2. `PRIMARY KEY`（主键）

3. `UNIQUE KEY`（唯一键）

`where` 就是用来指定筛选条件