SQL 语言
字数: 0 字 时长: 0 分钟
SQL （Structured Query Language）是管理关系型数据库的标准语言。MySQL 支持标准的 SQL 语法，同时也有自己的扩展。
DDL
DDL（Data Definition Language），数据库定义语言，用于定义和管理数据库对象（如数据库、表、索引）。
数据库操作
CREATE DATABASE [IF NOT EXISTS] db_name
[DEFAULT CHARACTER SET charset_name] -- 字符集（推荐 utf8mb4）
[COLLATE collation_name] -- 排序规则（如 utf8mb4_unicode_ci）
[ENCRYPTION {'Y' | 'N'}]; -- MySQL 8.0+ 支持透明加密ALTER DATABASE db_name
[CHARACTER SET charset_name]
[COLLATE collation_name];DROP DATABASE [IF EXISTS] db_name; -- 连级删除所有表（高危操作！）表级操作
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (
column1 datatype [column_constraints],
column2 datatype [column_constraints],
...
[table_constraints]
) [ENGINE=storage_engine] -- 存储引擎（InnoDB/MyISAM）
[DEFAULT CHARSET=utf8mb4] -- 默认字符集
[COMMENT '表注释']
[PARTITION BY ...]; -- 分区表（高级特性）
# 示例
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
email VARCHAR(100) NOT NULL UNIQUE CHECK (email LIKE '%@%'),
balance DECIMAL(12,2) DEFAULT 0.00 COMMENT '账户余额',
reg_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email) -- 非唯一索引
) ENGINE=InnoDB
CHARSET=utf8mb4
COMMENT '用户主表';ALTER TABLE table_name
ADD COLUMN col_name datatype [constraints] [FIRST|AFTER col] -- 新增列
DROP COLUMN col_name -- 删除列
MODIFY COLUMN col_name new_datatype [constraints] -- 修改列类型
CHANGE COLUMN old_name new_name new_datatype [constraints] -- 重命名列
RENAME TO new_table_name -- 表重命名
ADD INDEX index_name (col1, col2) -- 添加索引
ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES tbl(col) -- 添加外键
DROP PRIMARY KEY -- 删除主键
DROP FOREIGN KEY fk_symbol; -- 删除外键DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name2...]pt-online-schema-change 解决大表 DDL 操作锁表问题
如果线上表的数据量很大，进行 alter table 操作时耗时很长，可能导致锁表，锁表的原因简单来说有两点：
- 使用
alter操作修改表结构时，需要创建新表结构并复制原表数据，对大表而言，复制这个过程很耗时，而且会使用表级锁阻塞所有写操作，某些操作甚至会阻塞读取（如修改主键） - 即使是添加索引这类不需要重建表的操作，仍然会获取元数据锁这种表级锁来阻塞所有对该表的操作（包括
select）
对于这种情况，可以使用 pt-online-schema-change 工具来解决，它的原理是复制过程不会使用表锁来阻塞操作，而是给原表加触发器来捕获 insert/update/delete 操作， 保证新表与旧表的数据同步。
索引操作
# 创建主键约束/唯一约束等会自动隐式创建相关索引
create table book(
book_id int,
book_name varchar(100),
info varchar(100),
author varchar(20),
# 显式为 book_name 字段创建普通索引
index index_name(book_name),
# 显式为 info 字段创建唯一索引
unique index uk_idx(info),
# 创建联合索引
index mul_index_author_name(author,book_name)
);# 普通索引
alter table book add index idx_info(info);
# 唯一索引
alter table book add unique idx_id(book_id);
# 联合索引
alter table book add index mul_idx_author_info(author,info);# 在大量增删改操作之前可以先删除索引，可以增加执行效率，然后再重新创建索引
alter table book drop index idx_info;
# 或
drop index uk_idx on book;DML
DML （Data Manipulation Language），数据库操作语言，用于增删改查数据（select 也被认为是 DQL）。
插入数据
-- 基础插入（列顺序严格匹配）
INSERT INTO table_name VALUES (value1, value2, ...);
-- 指定列插入（推荐）
INSERT INTO employees (name, salary, hire_date)
VALUES ('李华', 28000.00, '2023-01-15');
-- 多行批量插入
INSERT INTO employees (name, dept_id)
VALUES
('张三', 101),
('李四', 102),
('王五', 101);数据冲突时如何插入？
当我们插入一条新数据时，如果遇到主键冲突那么使用普通 insert 会插入失败的， 这时候可以使用 ON DUPLICATE KEY UPDATE 或 REPLACE 来解决，不过它们两者有本质区别。
REPLACE：
replace into users (id,username, email)
values (1,'admin2', 'admin2@example.com');REPLACE 本质是：当 insert 数据遇到冲突时，则会先 delete 旧数据再 insert 新数据（两个操作，不过由 MySQL 保证原子性）
因此自增ID会重新生成，可能导致 ID 改变，整行替换，需要包含所有字段，否则报错
ON DUPLICATE KEY UPDATE
insert into users (id,username,email)
-- 如果没冲突则 username 改为 admin5，email 改为 admin5@example.com
values (1,'admin5', 'admin5@example.com')
-- 如果冲突则更新 username 为 admin7，其他字段不变
on duplicate key update username = 'admin7';本质是：尝试 insert 新数据，当主键或唯一索引冲突时，则执行 update 操作，仅修改指定列的值，自增ID保持不变，性能比 REPLACE 更高，死锁概率更低。
更新数据
-- 务必使用 WHERE 条件限定范围
UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = 101;
-- 多列更新
UPDATE employees
SET salary = salary * 1.1,
hire_date = '2023-01-15'
WHERE dept_id = 101;删除数据
-- 按条件删除
DELETE FROM user
WHERE name = '张三';
-- 清空表（逐行删除,会触发事务）
DELETE FROM temp_table;如果要清空表，使用 TRUNCATE TABLE 命令
- 如果要清空表，不要使用
delete删除，因为会触发事务，性能很低 - 直接使用
truncate table命令，直接清空物理文件，不触发事务（无法回滚）
DQL
DQL（Data Query Language），数据库查询语言，用于查询数据。DQL 是 SQL 最常用也是最复杂的部分。
基础查询语句结构如下：
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数select 、from、join、where、group by、having、order by、limit 的执行顺序是什么？
from：先确定数据源join：执行连接，结合多张表的数据（本质也是确定数据源）where：过滤不符合条件的行group by：将数据按指定的列分组having：过滤分组后的数据select：选择要返回的列order by：对查询结果进行排序limit：限制返回的行数
基础查询
-- 不带任何条件查询所有数据
select * from user;
-- 指定字段
select id, name, age from user;
-- 指定别名
select name as username from user;
-- 去除重复记录
select distinct username from user;条件查询
-- 比较运算符
select name,age from user where age = 18;
-- 逻辑运算符
select * from user where name != '张三' and age > 18;
-- 范围匹配
select * from user where age between 18 and 25;
select * from user where age in (18,21,25);
select * from user where age not in (18,20);
-- 模糊匹配
select * from user where name like '张%'
-- 空值匹配
select * from user where adress is null;
select * from user where adress is not null;聚合函数
MySQL 内置了聚合函数，用于对数据进行统计计算，常见的有 count()、sum()、avg()、max()、min()等。
-- 求最大年龄
select max(age) from user;
-- 求平均年龄
select avg(age) from user;
-- 求最小年龄
select min(age) from user;
-- 求和
select sum(age) from user;
-- 求一共有多少用户
select count(*) from user;count(*) 、count(1) 和 count(字段名) 的区别
count(*)会统计表中所有行的数量，包括null值count(1)和count(*)几乎没差别，也会统计null值行，性能也没有差别count(字段名)只会统计指定字段不为null的行数，性能比上面两者差一点
分组查询
使用 group by 对查询进行分组，where 过滤满足条件的数据参与分组；having 对分组之后的结果进行过滤。
分组之后，查询的字段一般为聚合函数或分组字段，查询其他字段没有意义。
-- 对姓名进行分组，统计每个姓名的数量
select count(1) as cnt,username from user group by username;排序查询
asc：升序（默认）desc：降序
-- 按年龄排序
select * from user order by age desc ;分页查询
select * from user limit 起始索引,查询记录数如果查询的是第一页数据，起始索引可以省略，比如：
-- 查询前 10 条数据
select * from user limit 10;子查询
where子查询
-- 查询年龄大于平均年龄的用户
select * from user
where age > (select avg(age) from user);from子查询
-- 查询结果包含 user 表和派生表的字段
select u.username,u.age,a.avg from user u
join (
select avg(age) as avg from user
) a ;DCL
DCL （Data Control Language），数据库控制语言，用于控制数据库的访问权限。
用户管理
MySQL 用户可以分为 普通用户 和 root用户。
- 创建用户
# 创建用户 zhang3 密码为 123456
create user 'zhang3'@'%' identified by '123456';
create user 'zhang3'@'localhost' identified by '123456';mysql 库中的 user 表的主键是由 host 和 user 组合的联合主键。% 标识任何主机都可以通过该用户登录，而 localhost 表示只能通过本机登录。
- 查询用户
select host,user from user;
-- 注意这样会把所有 zhang3 用户都修改为 zhang4
update user set User = 'zhang4' where User = 'zhang3';
-- 可以指定 host 修改 zhang3
update user set User = 'zhang4' where User = 'zhang3' and Host = 'localhost';
-- 修改用户后需要刷新权限使其生效
flush privileges;- 删除用户
drop user 'zhang3'@'localhost';- 修改密码
-- 当前登录用户修改自己密码
alter user user() identified by '111111';
-- 或
set password = '123456';
-- root 用户也可直接修改其他用户密码
set password for 'zhang3'@'%' = '111111';- 设置密码过期
-- root 用户可以直接让其他用户密码过期
ALTER USER 'zhang3'@'%' PASSWORD EXPIRE;
-- 全局设置所有用户密码过期时间180天
SET persist default_password_lifetime = 180;
-- 创建用户时设置密码过期策略
create user 'li4'@'%' identified by '123456' password expire interval 90 DAY;
-- 修改已经创建的用户密码过期策略
alter user 'li4'@'%' password expire interval 180 DAY;权限管理
权限列表
我们主要关注数据库或表权限，需指定 ON database.* 或 ON database.table：
| 权限名 | 描述 |
|---|---|
SELECT | 读取数据 |
INSERT | 插入数据 |
UPDATE | 更新数据 |
DELETE | 删除数据 |
CREATE | 创建新表/数据库 |
DROP | 删除表/数据库 |
ALTER | 修改表结构（如修改列） |
INDEX | 创建/删除索引 |
REFERENCES | 外键约束权限（通常需要 ALTER） |
CREATE TEMPORARY TABLES | 创建临时表 |
CREATE VIEW | 创建视图 |
SHOW VIEW | 查看视图定义 |
TRIGGER | 操作触发器 |
权限赋予
-- 赋予 mydb 下所有表的查询和更新权限给 zhang3
grant select,update on mydb.* to 'zhang3'@'%';
-- 赋予所有权限给 li4
grant all privileges on *.* to 'li4'@'%';
-- 赋予所有权限给 wang5,并允许 wang5 把自己权限赋予给其他用户
grant all privileges on *.* to 'wang5'@'%' with grant option;访问控制
我们给用户配置了权限，那么 MySQL 是如何来判断用户是否有权限访问数据库或表呢？
- 当用户试图连接到 Mysql 服务器时，Mysql 首先会根据
user表中的host、user、authentication_string3 个字段来匹配用户。- 匹配成功，则接受连接
- 匹配失败，则拒绝连接
- 一旦连接成功，对此连接上的每一个请求，服务器都会根据
user、db、table_priv、column_priv表来进行权限检查。- 先查找
user表的权限 - 如果没有匹配到，则查找
db表的权限 - 如果没有匹配到，则查找
table_priv表的权限 - 如果没有匹配到，则查找
column_priv表的权限 - 如果都没匹配到，则返回错误信息
- 先查找
角色管理
角色 是 MySQL 8.0 中引入的新功能，角色是权限的集合，直接赋予用户，更方便地管理用户权限。
- 创建角色
create role 'manager'@'%';
create role 'boss'@'%';- 给角色赋权
grant select,update on mydb.* to 'manager'@'%';
grant all privileges on *.* to 'boss'@'%';- 展示角色权限
show grants for 'manager'@'%';
show grants for 'boss'@'%';- 回收角色权限
revoke update on mydb.* from 'manager'@'%';- 删除角色
drop role 'manager'@'%';- 赋予用户角色
grant 'manager'@'%' to 'wang5'@'%';- 回收用户角色
revoke 'manager'@'%' from 'wang5'@'%';- 为用户激活角色
为用户赋予了角色之后，不会立即生效，还需要激活角色，然后重新登录
set default role 'manager'@'%' to 'wang5'@'%';数据库三大范式是什么？
- 第一范式：每个列的值都是原子值，表中的每个字段只能包含单一的数据项，不能出现重复的列和多值字段
- 第二范式：在满足第一范式基础上，非主键字段依赖于整个主键
- 第三范式：在满足第二范式基础上，所有非主键字段只能依赖于主键，不能相互依赖
数据库的三大范式是为了减少数据冗余，提高数据的完整性和一致性，使得表的设计更清晰。
不过实际业务的表设计通常都不会完全遵守三大范式，比如故意冗余部分字段，减少联表查询提升性能。
MySQL 深分页的问题
当我们同样查询 10 条数据，但 LIMIT 1000000,10 和 LIMIT 10 的执行速度完全不一样。因为 LIMIT 1000000,10 需要先读取并跳过 1000000 行数据，再读取 10 行数据，而 LIMIT 10 只需要读取 10 行数据。
LIMIT 10000000,10 这种情况的效率很差，也就是深分页问题，优化方式有三种：
- 子查询
select * from user where name = '张三' limit 99999999,10 这条查询语句可以优化为：
select * from user where name = '张三'
and id >= (select id from user where name = '张三' order by id limit 99999999,1)
order by id limit 10;name 有索引的情况下，这样的查询会直接扫描 name 的二级索引，二级索引数据量小，比直接扫描主键索引性能高，并且在子查询中能直接得到 id 不需要回表。
- 记录 id
每次分页都返回当前的最大 id，然后下次查询的时候带上这个 id 就可以利用 id > maxid 过滤，不过仅适合连续查询的情况。
- elasticsearch
利用搜索引擎来解决大数据量下的搜索性能问题
MySQL 中的 int(11) 的 11 表示什么？
最开始我以为 int(11) 表示该字段最多能存储 11 位的数据，但其实是错的。11 表示显示宽度，并不影响存储的大小或数值范围。
int 类型始终占 4 字节也就是 32 位, int(11) 表示如果数值的位数小于 11 位，比如 42 将显示为 0000000000042。
所以 int(11) 中的数字只是显示格式的定义，在 MySQL 8.0 中显示宽度被正式弃用。
VARCHAR(100) 和 VARCHAR(10) 有什么区别
和 int(n) 不同的是，varchar(100) 中的数字表示能存储的字符串长度上限。
varchar(10) 和 varchar(100) 在存储相同字符串时占用的空间是一样的，除了存储字符本身外，还需要额外的 1 或 2 个字节存储字符串的长度。
虽然两者存储空间一样，但在排序查询时，varchar(100) 占用的内存会更多
char 和 varchar 有什么区别？
char 和 varchar 都表示字符串类型，char 表示定长字段；varchar 表示可变长度的字段。
理论上来说 char 会比 varchar 快，因为 varchar 长度不固定，处理需要多一次运算，但实际上这种运算耗时微乎其微， 而固定大小在很多场景下浪费空间（即使存储字段长度小于固定长度也会填充），所以业务上推荐使用 varchar。
DATETIME 和 TIMESTAMP 有什么区别？
- DATETIME：以字符串形式存储，占用
8个字节，不受时区影响，范围为1000-01-01 00:00:00到9999-12-31 23:59:59 - TIMESTAMP：以 UNIX 时间戳形式存储，占用
4个字节，受时区影响，范围为1970-01-01 00:00:00到2038-01-19 03:14:07
TIMESTAMP 可以有默认的当前时间戳 CURRENT_TIMESTAMP，并且支持在行更新时自动更新为当前时间戳，更适合记录行的创建和修改时间。
inner join 和 left join 及 right join 的区别
- inner join：只返回两个表中匹配的行，如果没有匹配，则该行不会出现在结果集中
- left join：返回左表中的所有行，即使右表没有匹配，右表对应列会显示为
NULL - right join：返回右表中的所有行
什么是数据库的视图
数据库的视图是一个虚拟表，它并不存储实际的数据，而是通过查询其他表的数据来生成的。
视图可以将复杂的查询封装为简单的视图，使得用户在查询数据时更加方便。
为什么阿里手册禁用存储过程
- 可移植性差：存储过程是在数据库服务器上执行的，会导致应用程序的数据库依赖增加，难以迁移到其他数据库系统
- 调试困难：相对应用层代码，特别是复杂业务场景，难以调试定位问题
- 维护复杂：存储过程与应用代码分离，增加维护成本