# 一.基础入门
### 1.简介
MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由瑞典公司 MySQL AB 开发，现属于 Oracle 旗下产品。
核心特点：
**开源免费**
- 社区版（MySQL Community Edition）可免费使用，适合个人和小型企业。
- 企业版提供高级功能和技术支持（需付费）。
**跨平台支持**
- 支持 Windows、Linux、macOS 等多种操作系统。
**高性能**
- 优化了查询处理、索引和存储引擎（如 InnoDB、MyISAM），适合高并发场景。
- 支持事务处理（ACID 兼容），确保数据一致性。
**易用性**
- 使用标准的 SQL 语法，学习成本低。
- 提供丰富的图形化管理工具（如 MySQL Workbench）。
**可扩展性**
- 支持主从复制、集群（MySQL Cluster）、分片（Sharding）等方案，满足大规模数据需求。
**安全性**
- 提供数据加密、用户权限管理、SSL 连接等安全机制。
**与其他数据库对比**
1.MySQL vs PostgreSQL
|对比项| MySQL| PostgreSQL|
|-|-|-|
|类型 |关系型数据库| 关系型数据库（支持扩展类型）|
|事务支持 |支持（InnoDB引擎） |完全支持，ACID 兼容|
|性能 |读写速度快，适合高并发简单查询| 复杂查询优化更好，适合分析场景|
|扩展性| 有限（需通过分片/主从复制）| 更强（支持自定义函数、运算符）|
|JSON支持 |支持（5.7+版本） |更强大（支持JSONB、索引、查询）|
|适用场景 | Web应用、电商、CMS |复杂业务、GIS、数据分析|
|典型用户 |Facebook、Twitter、YouTube| Apple、Spotify、Reddit|
2.MySQL vs MongoDB（非关系型对比）
|对比项 |MySQL |MongoDB|
|-|-|-|
|数据模型 |表结构（行和列） |文档型（JSON格式，无固定Schema）|
|事务支持 |支持 |支持（4.0+版本多文档事务）|
|查询语言 |SQL |NoSQL（类JSON查询语法）|
|扩展性| 垂直扩展（硬件升级） |水平扩展（分片集群）|
|适用场景 |结构化数据、事务操作 |非结构化数据、快速迭代（如日志、IoT）|
|典型用户 |Airbnb、Uber |eBay、Adobe|
3.MySQL vs SQLite
|对比项| MySQL |SQLite|
|-|-|-|
|架构|客户端-服务器模式 |嵌入式（单文件数据库）|
|并发性 |支持多用户高并发| 仅支持单进程访问|
|配置复杂度 |需独立安装和配置|零配置，开箱即用|
|存储限制 |支持TB级数据| 适合小型数据（通常<1TB）|
|适用场景 |多用户Web应用 |移动端App、本地软件（如浏览器缓存）|
|典型用户|WordPress、Joomla| Android、iOS、Chrome|
4.MySQL vs MariaDB
|对比项 |MySQL |MariaDB|
|-|-|-|
|血缘关系 |Oracle维护 |MySQL原团队开发的分支|
|兼容性| 标准MySQL语法 |完全兼容MySQL，新增优化功能|
|性能 |稳定 |部分查询更快（如Aria引擎）|
|开源协议 |部分功能企业版收费 |完全开源|
|适用场景 |企业级稳定需求| 需要免费开源替代方案|
5.MySQL vs Oracle Database
|对比项| MySQL |Oracle Database|
|-|-|-|
|成本 |免费（社区版）| 商业收费，价格高昂|
|功能 |基础功能完善| 企业级功能（如分区表、高级安全）|
|扩展性| 适合中小规模 |支持超大规模集群（如银行系统）|
|适用场景 |中小型企业、初创公司 |大型企业、金融、电信|
6.MySQL vs SQL Server
|对比项 |MySQL |SQL Server|
|-|-|-|
|开发商 |Oracle（原Sun/MySQL AB）| Microsoft|
|许可证| 开源（GPL）/商业版| 商业收费（部分免费版）|
|跨平台支持| 是（Windows/Linux/macOS等） |主要Windows（Linux支持有限）|
|性能 |高并发读写优化 |企业级优化，复杂查询更强|
|扩展性 |垂直扩展为主 |支持分布式、分区表|
|典型用户| Facebook、YouTube、Airbnb |银行、大型企业|
## 2.安装与配置
当前安装教程所使用系统为ubuntu，其他linux类似。windows和mac os 略...
### 2.1 安装MySQL 8
更新软件包列表并安装MySQL服务器
```bash
sudo apt update
sudo apt install mysql-server -y
```
安装完成后，MySQL服务会自动启动。检查状态
```bash
sudo systemctl status mysql
```
### 2.2 运行安全初始化脚本
执行以下命令设置root密码和其他安全选项
```bash
sudo mysql_secure_installation
```
按提示操作：
1.设置root密码。
2.移除匿名用户（选Y）。
3.禁止root远程登录（选Y，后续可通过其他用户远程管理）。
4.移除测试数据库（选Y）。
5.重新加载权限表（选Y）。
### 2.3 配置MySQL允许远程连接
编辑MySQL配置文件
```bash
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
找到bind-address并修改为：
```bash
bind-address = 0.0.0.0 # 允许所有IP连接
# 或指定服务器IP，如 bind-address = 192.168.1.100
```
保存后重启MySQL：
sudo systemctl restart mysql
### 2.4 创建远程访问用户并授权
登录MySQL（使用root）
```bash
sudo mysql -u root -p
```
执行以下SQL命令（替换username和password）
密码要求：
•至少包含 1 位大小写
•至少包含 1 位数字
•包含 1 个特殊符号
•必须 8 位及以上
```sql
-- 创建用户（如果允许所有主机访问，用'%'代替'remote_host_ip'）
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
-- 授予所有权限（或根据需要限制为特定数据库）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
-- 刷新权限
FLUSH PRIVILEGES;
退出MySQL：
EXIT;
```
mysql默认使用3306端口，如果作为远程服务器使用，请配置防火墙打开3306端口。
## 3.数据库基本操作
### 3.1登录 MySQL
```bash
mysql -u 用户名 -p
```
### 3.2 查看所有数据库
```sql
SHOW DATABASES;
```
### 3.3 创建数据库
```sql
CREATE DATABASE 数据库名;
```
### 3.4 选择数据库
```sql
USE 数据库名;
```
### 3.5 删除数据库
```sql
DROP DATABASE 数据库名;
```
### 3.6 查看当前数据库的所有表
```sql
SHOW TABLES;
```
# 二.SQL 语言基础
## 0.数据类型、约束、函数
### 0.1 常用数据类型
整数类型
|类型 |字节 |有符号范围 |无符号范围 |说明|
|-|-|-|-|-|
|TINYINT |1 |-128 ~ 127 |0 ~ 255 |适合布尔值/状态码|
|SMALLINT |2| -32,768 ~ 32,767 |0 ~ 65,535| 适合小型数值范围|
|MEDIUMINT |3 |-8,388,608 ~ 8,388,607 |0 ~ 16,777,215 |中等范围数值|
|INT |4 |-2.14e9 ~ 2.14e9| 0 ~ 4.29e9| 最常用的整数类型|
|BIGINT |8 -9.22e18 ~ 9.22e18 |0 ~ 1.84e19 |适合大整数/ID|
浮点数类型
|类型 |字节 |精度 |特点|
|-|-|-|-|
|FLOAT |4 |约7位有效数字| 单精度浮点数|
|DOUBLE |8| 约15位有效数字 |双精度浮点数|
|DECIMAL| 变长 |精确小数| 适合金融等精确计算场景|
字符串类型
|类型| 最大长度 |说明|
|-|-|-|
|CHAR(n) |255字符 |固定长度，效率高|
|VARCHAR(n) |65,535字节 |可变长度，节省空间|
|TINYTEXT |255字节| 短文本|
|TEXT |65KB| 标准文本内容|
|MEDIUMTEXT |16MB |中等长度文本|
|LONGTEXT| 4GB| 超长文本内容|
日期时间类型
|类型 |格式 |范围 |字节 |说明|
|-|-|-|-|-|
|DATE |YYYY-MM-DD |1000-01-01~9999-12-31 |3 |仅日期|
|TIME |HH:MM:SS[.微秒] |-838:59:59~838:59:59 |3| 时间值|
|DATETIME |YYYY-MM-DD HH:MM:SS |1000-01-01~9999-12-31 |8 |日期+时间|
|TIMESTAMP |YYYY-MM-DD HH:MM:SS |1970-01-01~2038-01-19 |4 |自动转换时区的时间戳|
|YEAR |YYYY| 1901~2155 |1| 年份值|
二进制类型
|类型 |最大长度| 说明|
|-|-|-|
|BINARY(n) |255字节| 固定长度二进制数据|
|VARBINARY(n) |65KB |可变长度二进制数据|
|TINYBLOB| 255字节 |小型二进制对象|
|BLOB |65KB |标准二进制对象|
|MEDIUMBLOB|16MB |中等二进制对象|
|LONGBLOB| 4GB| 大型二进制对象(如图片/文件)|
枚举
```sql
gender ENUM('M','F','U') -- 单选
SET 集合
hobbies SET('音乐','运动','阅读','旅行') -- 多选
```
### 0.2 常用约束条件
列级约束
|约束 |说明| 示例|
|-|-|-|
|PRIMARY KEY |主键 |id INT PRIMARY KEY|
|AUTO_INCREMENT |自增 |id INT AUTO_INCREMENT|
|NOT NULL |非空 |name VARCHAR(50) NOT NULL|
|UNIQUE |唯一| email VARCHAR(100) UNIQUE|
|DEFAULT |默认值 |status INT DEFAULT 1|
|FOREIGN KEY |外键 |user_id INT REFERENCES users(id)|
表级约束
```sql
PRIMARY KEY (列名1, 列名2),
FOREIGN KEY (列名) REFERENCES 外表名(列名),
UNIQUE (列名1, 列名2)
```
### 0.3 函数
#### 0.3.1 字符串函数
基础字符串操作
```sql
CONCAT(str1, str2, ...) -- 连接字符串
LENGTH(str) -- 返回字符串长度(字节数)
CHAR_LENGTH(str) -- 返回字符数
UPPER(str) -- 转为大写
LOWER(str) -- 转为小写
```
字符串截取与处理
```sql
SUBSTRING(str, pos, len) -- 截取子串
LEFT(str, len) -- 从左截取
RIGHT(str, len) -- 从右截取
TRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str) -- 去除空格/指定字符
REPLACE(str, from_str, to_str) -- 替换字符串
SELECT SUBSTRING('MySQL', 3, 2); -- 返回 'SQ'
SELECT TRIM(LEADING 'x' FROM 'xxxMySQLxxx'); -- 返回 'MySQLxxx'
```
#### 0.3.2 数值函数
基本数学函数
```sql
ABS(x) -- 绝对值
CEIL(x) -- 向上取整
FLOOR(x) -- 向下取整
ROUND(x, d) -- 四舍五入，d为小数位数
MOD(x, y) -- 取模(求余)
RAND() -- 返回0-1随机数
```
数学运算
```sql
POWER(x, y) -- x的y次方
SQRT(x) -- 平方根
EXP(x) -- e的x次方
LOG(x) -- 自然对数
SIN(x), COS(x), TAN(x) -- 三角函数
```
#### 0.3.3 日期时间函数
获取当前时间
```sql
NOW() -- 当前日期和时间
CURDATE() -- 当前日期
CURTIME() -- 当前时间
UNIX_TIMESTAMP() -- 当前UNIX时间戳
```
日期时间计算
```sql
DATE_ADD(date, INTERVAL expr unit) -- 日期加法
DATE_SUB(date, INTERVAL expr unit) -- 日期减法
DATEDIFF(date1, date2) -- 日期差(天数)
TIMESTAMPDIFF(unit, datetime1, datetime2) -- 时间差(指定单位)
SELECT DATE_ADD('2023-01-01', INTERVAL 1 MONTH); -- 返回 '2023-02-01'
SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 08:00:00', '2023-01-01 18:30:00'); -- 返回 10
```
日期时间提取
```sql
YEAR(date) -- 提取年份
MONTH(date) -- 提取月份
DAY(date) -- 提取日
HOUR(time) -- 提取小时
MINUTE(time) -- 提取分钟
SECOND(time) -- 提取秒
```
#### 0.3.4 聚合函数
```sql
COUNT(expr) -- 计数
SUM(expr) -- 求和
AVG(expr) -- 平均值
MAX(expr) -- 最大值
MIN(expr) -- 最小值
```
#### 0.3.5 加密函数
```sql
MD5(str) -- 计算MD5哈希
SHA1(str) -- 计算SHA1哈希
SHA2(str, hash_length) -- 计算SHA2哈希(224/256/384/512)
AES_ENCRYPT(str, key) -- AES加密
AES_DECRYPT(crypt_str, key) -- AES解密
```
#### 0.3.6 系统信息函数
```sql
DATABASE() -- 当前数据库名
USER() -- 当前用户名
VERSION() -- MySQL版本
CONNECTION_ID() -- 连接ID
LAST_INSERT_ID()-- 最后插入的AUTO_INCREMENT值
```
## 1.数据定义语言（DDL）
### 1.1 创建表
[]表示可以不添加
```sql
CREATE TABLE [IF NOT EXISTS] 表名 (
列名1 数据类型 [约束条件],
列名2 数据类型 [约束条件],
...
[表级约束条件]
) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集] [COMMENT='表注释'];
```
```sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
```
### 1.2 查看表结构
```sql
DESC 命令（常用）
DESC 表名;
# 或者 DESCRIBE 表名;
SHOW CREATE TABLE 命令（查看完整建表语句）
SHOW CREATE TABLE 表名;
SHOW INDEX 命令（查看表索引）
SHOW INDEX FROM 表名;
```
### 1.3 修改表结构
#### 1.3.1 添加列
```sql
ALTER TABLE 表名
ADD COLUMN 列名 数据类型 [约束条件] [FIRST|AFTER 列名];
```
```sql
-- 在最后添加列
ALTER TABLE users ADD COLUMN age INT;
-- 在指定位置之后添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 添加多列
ALTER TABLE users
ADD COLUMN address VARCHAR(100),
ADD COLUMN gender ENUM('M','F') DEFAULT 'M';
```
#### 1.3.2 修改列定义
```sql
ALTER TABLE 表名
MODIFY COLUMN 列名 新数据类型 [约束条件];
```
```sql
-- 修改数据类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
-- 修改约束条件
ALTER TABLE users MODIFY COLUMN age INT NOT NULL DEFAULT 0;
```
#### 1.3.3 重命名列
```sql
ALTER TABLE 表名
CHANGE COLUMN 旧列名 新列名 数据类型 [约束条件];
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20);
```
#### 1.3.4 删除列
```sql
ALTER TABLE 表名
DROP COLUMN 列名;
----
ALTER TABLE users DROP COLUMN age;
```
#### 1.3.5 重命名表
```sql
ALTER TABLE 旧表名 RENAME TO 新表名;
-- 或
RENAME TABLE 旧表名 TO 新表名;
ALTER TABLE users RENAME TO customers;
-- 或
RENAME TABLE users TO customers;
```
#### 1.3.6 添加索引
```sql
ALTER TABLE 表名
ADD INDEX 索引名 (列名);
ALTER TABLE users ADD INDEX idx_email (email);
```
#### 1.3.7 添加唯一索引
```sql
ALTER TABLE 表名
ADD UNIQUE 索引名 (列名);
ALTER TABLE users ADD UNIQUE uk_username (username);
```
#### 1.3.8 添加主键
```sql
ALTER TABLE 表名
ADD PRIMARY KEY (列名);
ALTER TABLE users ADD PRIMARY KEY (id);
```
#### 1.3.9 删除索引
```sql
ALTER TABLE 表名
DROP INDEX 索引名;
ALTER TABLE users DROP INDEX idx_email;
```
#### 1.3.10 删除主键
```sql
ALTER TABLE 表名
DROP PRIMARY KEY;
```
#### 1.3.11 添加外键约束
```sql
ALTER TABLE 表名
ADD CONSTRAINT 约束名 FOREIGN KEY (列名) REFERENCES 外表名(列名);
----
ALTER TABLE orders
ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);
```
#### 1.3.12 删除外键约束
```sql
ALTER TABLE 表名
DROP FOREIGN KEY 约束名;
--
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;
```
#### 1.3.13 修改存储引擎
```sql
ALTER TABLE 表名
ENGINE=存储引擎;
--
ALTER TABLE users ENGINE=InnoDB;
```
#### 1.3.14 修改字符集
```sql
ALTER TABLE 表名
CONVERT TO CHARACTER SET 字符集 [COLLATE 排序规则];
```
#### 1.3.15 修改自增值
```sql
ALTER TABLE 表名
AUTO_INCREMENT=新值;
```
### 1.4 删除表
#### 1.4.1 基本删除语法
删除单个表
```sql
DROP TABLE users;
```
安全删除（表不存在时不报错）
```sql
DROP TABLE IF EXISTS users;
```
删除多个表
```sql
DROP TABLE users, orders, products;
```
#### 1.4.2 处理外键约束
先删除外键约束再删表
```sql
-- 查看外键约束名
SHOW CREATE TABLE orders;
-- 删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;
-- 然后删除表
DROP TABLE users;
```
### 1.5 清空表
直接清空
```sql
TRUNCATE TABLE 表名;
```
记录日志清空
```sql
DELETE FROM 表名;
```
## 2.数据操作语言（DML）
### 2.1 插入数据
#### 2.1.1 基本 INSERT 语法
插入单行数据（指定列名）
```sql
INSERT INTO employees (emp_id, name, department, salary)
VALUES (101, '张三', '技术部', 8500.00);
```
插入单行数据（省略列名）
```sql
INSERT INTO departments
VALUES (10, '财务部', '北京');
```
如果插入的表有自增列，获取自增值
```sql
-- 获取最后插入的ID
SELECT LAST_INSERT_ID() AS new_id;
```
#### 2.1.2 插入多行数据
```sql
INSERT INTO products (product_name, price, category)
VALUES
('笔记本电脑', 5999.00, '电子产品'),
('无线耳机', 399.00, '电子产品'),
('办公椅', 899.00, '家具');
```
#### 2.1.3 特殊插入方式
插入查询结果
```sql
INSERT INTO employee_archive (emp_id, name, leave_date)
SELECT emp_id, name, CURDATE()
FROM employees
WHERE status = '离职';
```
使用 SET 语法插入
```sql
INSERT INTO customers
SET
customer_name = '李四',
phone = '13800138000',
email = 'lisi@example.com',
reg_date = NOW();
```
#### 2.1.4 插入时的数据处理
处理 NULL 值
```sql
-- 显式插入NULL
INSERT INTO orders (order_id, product_id, quantity, notes)
VALUES (1001, 5, 2, NULL);
-- 省略列名自动插入NULL（如果列允许NULL）
INSERT INTO orders (order_id, product_id, quantity)
VALUES (1002, 8, 1);
```
使用默认值
```sql
-- 使用DEFAULT关键字
INSERT INTO users (username, password, status)
VALUES ('user1', '123456', DEFAULT);
-- 省略列名使用默认值
INSERT INTO users (username, password)
VALUES ('user2', '654321');
```
使用函数和表达式
```sql
INSERT INTO log_entries (user_id, action, action_time)
VALUES
(101, '登录', NOW()),
(102, '购买', DATE_ADD(NOW(), INTERVAL 1 HOUR));
```
#### 2.1.5 插入时的约束处理
忽略重复键错误
```sql
INSERT IGNORE INTO unique_users (user_id, username)
VALUES (101, 'user1');
```
替换重复记录
```sql
REPLACE INTO product_inventory (product_id, quantity)
VALUES (5, 100);
```
### 2.2 更新数据
#### 2.2.1 基本 UPDATE 用法
更新单表数据
```sql
-- 更新单个字段
UPDATE employees
SET salary = 10000
WHERE emp_id = 101;
-- 更新多个字段
UPDATE products
SET price = price * 0.9,
stock = stock - 1
WHERE product_id = 5;
```
更新所有行（慎用）
```sql
-- 更新表中所有记录（无WHERE条件）
UPDATE users
SET last_login = NOW();
```
#### 2.2.2 条件更新
使用简单条件
```sql
UPDATE orders
SET status = '已完成'
WHERE order_date < '2023-01-01'
AND status = '待处理';
```
使用子查询条件
```sql
-- 更新有订单的客户状态
UPDATE customers
SET is_active = 1
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date > '2023-01-01'
);
```
#### 2.2.3 *使用 JOIN 更新
```sql
UPDATE employees e
JOIN departments d ON e.dept_id = d.dept_id
SET e.salary = e.salary * 1.1
WHERE d.location = '上海';
```
#### 2.2.4 基于当前值的更新
```sql
-- 数值增减
UPDATE products
SET view_count = view_count + 1
WHERE product_id = 8;
-- 字符串连接
UPDATE articles
SET content = CONCAT(content, '\n更新于: ', NOW())
WHERE article_id = 15;
```
### 2.3 删除数据
```sql
-- 删除特定ID的记录
DELETE FROM users WHERE user_id = 101;
-- 删除30天未活跃的用户
DELETE FROM customers
WHERE last_login < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 删除表中所有记录（慎用）
DELETE FROM log_entries;
```
## 3.数据查询语言（DQL）
#### 3.1 简单查询
查询所有列
```sql
SELECT * FROM employees;
```
查询特定列
```sql
SELECT first_name, last_name, salary FROM employees;
```
使用列别名
```sql
SELECT first_name AS "名", last_name AS "姓" FROM employees;
```
去除重复值
```sql
SELECT DISTINCT department_id FROM employees;
```
### 3.2 WHERE子句 - 条件过滤
```sql
SELECT * FROM employees WHERE salary > 5000;
```
常用条件运算符：
- 比较运算符：=, <>/!=, >, <, >=, <=
- 逻辑运算符：AND, OR, NOT
- 范围查询：BETWEEN...AND..., NOT BETWEEN...AND...
- 集合查询：IN, NOT IN
- 模糊查询：LIKE, NOT LIKE
- 空值判断：IS NULL, IS NOT NULL
```sql
-- 多个条件
SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000
AND department_id = 10;
-- 模糊查询 (%表示任意多个字符，_表示单个字符)
SELECT * FROM employees
WHERE first_name LIKE 'J%';
-- 空值判断
SELECT * FROM employees
WHERE manager_id IS NULL;
```
### 3.3 排序(ORDER BY)
排序默认为ASC（正序），添加DESC变为倒叙
```sql
-- 单列排序
SELECT * FROM employees ORDER BY salary DESC;
-- 单列排序
SELECT * FROM employees ORDER BY salary DESC;
-- 多列排序
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;
```
### 3.4 分组查询(GROUP BY)
```sql
-- 按部门分组计算平均薪资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- 使用HAVING过滤分组结果
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
```
### 3.5 分页查询(LIMIT)
```sql
-- 查询前10条记录
SELECT * FROM employees LIMIT 10;
-- 分页查询(第2页，每页10条)
SELECT * FROM employees LIMIT 10 OFFSET 10;
-- 或
SELECT * FROM employees LIMIT 10, 10;
```
### 3.6 多表连接查询(JOIN)
内连接(INNER JOIN)
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
```
左外连接(LEFT JOIN)
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
```
右外连接(RIGHT JOIN)
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
```
交叉连接(CROSS JOIN)
> 返回两表的笛卡尔积（所有可能的组合）
```sql
-- 显式语法
SELECT e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;
-- 隐式语法
SELECT e.last_name, d.department_name
FROM employees e, departments d;
```
自连接(SELF JOIN)
> 表与自身连接，常用于层级数据（如员工-经理关系）
```sql
SELECT e.employee_id, e.last_name AS employee,
m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
```
### 3.7 子查询
WHERE子句中的子查询
```sql
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```
FROM子句中的子查询
```sql
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS dept_avg
WHERE dept_avg.avg_salary > 5000;
```
EXISTS子查询
```sql
SELECT * FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e
WHERE e.department_id = d.department_id
);
```
### 3.8 联合查询(UNION)
```sql
-- 合并两个查询结果(自动去重)
SELECT employee_id, first_name FROM current_employees
UNION
SELECT employee_id, first_name FROM former_employees;
-- 合并两个查询结果(不去重)
SELECT employee_id, first_name FROM current_employees
UNION ALL
SELECT employee_id, first_name FROM former_employees;
```
### 3.9 提示
1.避免使用SELECT *：只查询需要的列，减少数据传输量
2.合理使用索引：WHERE和JOIN条件中的列最好有索引
3.注意LIKE性能：前导通配符(%xxx)会使索引失效
4.分页优化：大数据量分页使用WHERE id > ? LIMIT ?代替LIMIT offset, size
5.EXPLAIN分析：使用EXPLAIN分析查询执行计划
## 4.数据控制语言（DCL）
数据控制语言(Data Control Language)用于管理数据库访问权限和安全控制。
> 写在前面：修改权限相关操作后记得执行FLUSH PRIVILEGES;刷新权限
### 4.1 用户管理
#### 4.1.1 用户创建与删除
```sql
-- 创建用户（MySQL 5.7+语法）
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
-- 删除用户
DROP USER '用户名'@'主机';
```
#### 4.1.2 用户属性修改
```sql
-- 重命名用户
RENAME USER '旧用户'@'主机' TO '新用户'@'新主机';
-- 修改密码
ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';
-- 设置密码过期
ALTER USER '用户名'@'主机' PASSWORD EXPIRE;
-- 锁定/解锁账户
ALTER USER '用户名'@'主机' ACCOUNT LOCK;
ALTER USER '用户名'@'主机' ACCOUNT UNLOCK;
```
### 4.2 权限管理
#### 4.2.1 权限授予
```sql
-- 基本授权语法
GRANT 权限类型 ON 对象级别 TO '用户'@'主机';
-- 常见权限类型
ALL PRIVILEGES -- 所有权限
SELECT, INSERT, UPDATE, DELETE -- 基本DML
CREATE, ALTER, DROP -- DDL权限
EXECUTE -- 执行存储过程
GRANT OPTION -- 允许转授权
-- 对象级别示例
*.* -- 所有数据库的所有表
数据库名.* -- 指定数据库的所有表
数据库名.表名 -- 指定表
数据库名.存储过程名 -- 指定存储过程
```
#### 4.2.2 权限回收
```sql
REVOKE 权限类型 ON 对象级别 FROM '用户'@'主机';
```
#### 4.2.3 权限查看
```sql
-- 查看用户权限
SHOW GRANTS FOR '用户'@'主机';
-- 查看当前用户权限
SHOW GRANTS;
```
## 5.事务控制（TCL）
事务是数据库操作的最小工作单元，是一组不可分割的SQL操作序列，这些操作要么全部执行成功，要么全部不执行。
### 5.1 事务开启
```sql
-- 标准语法（显式开启事务）
START TRANSACTION;
-- 简写语法
BEGIN;
```
### 5.2 事务提交
```sql
COMMIT;
```
### 5.3 事务回滚
```sql
-- 完全回滚
ROLLBACK;
-- 回滚到特定保存点
ROLLBACK TO SAVEPOINT savepoint_name;
```
### 5.4 保存点管理
```sql
-- 创建保存点
SAVEPOINT savepoint1;
-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint1;
-- 释放保存点
RELEASE SAVEPOINT savepoint1;
```
