十、视图与临时表
10.1 视图
-- 创建视图
CREATE VIEW user_orders AS
SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;
-- 使用视图
SELECT * FROM user_orders WHERE total_amount > 1000;
-- 可更新视图（满足条件时可更新）
CREATE VIEW active_users AS
SELECT id, name, email, age FROM users WHERE status = 'active';
-- 检查视图
CHECK TABLE active_users;
-- 查看视图定义
SHOW CREATE VIEW user_orders;
-- 修改视图
CREATE OR REPLACE VIEW user_orders AS
SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;
-- 删除视图
DROP VIEW IF EXISTS user_orders;
-- 视图优点
-- 1. 简化复杂查询
-- 2. 数据安全（隐藏敏感字段）
-- 3. 逻辑数据独立性
10.2 临时表
-- 创建临时表
CREATE TEMPORARY TABLE temp_user_stats (
user_id INT,
order_count INT,
total_amount DECIMAL(10,2)
);
-- 插入数据
INSERT INTO temp_user_stats
SELECT user_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY user_id;
-- 使用临时表
SELECT * FROM temp_user_stats WHERE order_count > 5;
-- 连接查询中使用临时表
SELECT u.name, t.order_count
FROM users u
INNER JOIN temp_user_stats t ON u.id = t.user_id;
-- 临时表特点
-- 1. 只在当前会话可见
-- 2. 会话结束自动删除
-- 3. 可以创建索引
CREATE TEMPORARY TABLE temp_data (id INT, name VARCHAR(50));
CREATE INDEX idx_temp_id ON temp_data(id);
-- 内存临时表
CREATE TEMPORARY TABLE temp_memory (
id INT,
value VARCHAR(100)
) ENGINE=MEMORY;
十一、用户与权限管理
11.1 用户管理
-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
CREATE USER 'remote_user'@'192.168.1.%' IDENTIFIED BY 'password';
CREATE USER 'web_user'@'%' IDENTIFIED BY 'web_pass';
-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'app_user'@'localhost' = PASSWORD('new_password');
-- 删除用户
DROP USER 'app_user'@'localhost';
-- 查看所有用户
SELECT user, host FROM mysql.user;
-- 重命名用户
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';
11.2 权限管理
-- 授予权限
-- 所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost';
-- 指定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO 'app_user'@'localhost';
-- 数据库级别权限
GRANT CREATE, ALTER, DROP ON mydb.* TO 'admin'@'localhost';
-- 全局权限
GRANT SUPER, PROCESS ON *.* TO 'dba'@'localhost';
-- 授予权限并允许传递
GRANT SELECT ON mydb.* TO 'read_user'@'localhost' WITH GRANT OPTION;
-- 查看权限
SHOW GRANTS FOR 'app_user'@'localhost';
SHOW GRANTS FOR CURRENT_USER();
-- 撤销权限
REVOKE INSERT, UPDATE ON mydb.users FROM 'app_user'@'localhost';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'app_user'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
11.3 角色管理（MySQL 8.0+）
-- 创建角色
CREATE ROLE 'app_read';
CREATE ROLE 'app_write';
CREATE ROLE 'app_admin';
-- 授予角色权限
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';
-- 授予用户角色
GRANT 'app_read' TO 'user1'@'localhost';
GRANT 'app_write' TO 'user2'@'localhost';
GRANT 'app_admin' TO 'admin'@'localhost';
-- 设置默认角色
SET DEFAULT ROLE 'app_read' TO 'user1'@'localhost';
-- 激活角色
SET ROLE 'app_read';
-- 查看角色
SELECT * FROM mysql.role_edges;
十二、备份与恢复
12.1 逻辑备份（mysqldump）
# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql
# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > dbs_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_dbs.sql
# 备份特定表
mysqldump -u root -p mydb users orders > users_orders.sql
# 只备份结构（无数据）
mysqldump -u root -p --no-data mydb > mydb_structure.sql
# 只备份数据（无结构）
mysqldump -u root -p --no-create-info mydb > mydb_data.sql
# 备份时包含存储过程、函数、触发器
mysqldump -u root -p --routines --triggers mydb > mydb_full.sql
# 条件备份（使用 WHERE）
mysqldump -u root -p mydb users --where="age > 18" > adult_users.sql
# 恢复备份
mysql -u root -p mydb < mydb_backup.sql
# 恢复时创建数据库
mysql -u root -p < all_dbs.sql
12.2 物理备份
-- 冷备份（停止服务）
sudo systemctl stop mysql
cp -r /var/lib/mysql /backup/mysql_backup
sudo systemctl start mysql
-- 热备份（使用 Percona XtraBackup）
# 安装 Percona XtraBackup
# 全量备份
xtrabackup --backup --target-dir=/backup/full
# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full
# 准备恢复
xtrabackup --prepare --target-dir=/backup/full
# 恢复
xtrabackup --copy-back --target-dir=/backup/full
12.3 二进制日志恢复
-- 查看二进制日志状态
SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;
-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
-- 根据时间点恢复
mysqlbinlog --start-datetime="2024-01-01 10:00:00" --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 | mysql -u root -p
-- 根据位置恢复
mysqlbinlog --start-position=100 --stop-position=500 mysql-bin.000001 | mysql -u root -p
12.4 表空间传输（MySQL 8.0+）
-- 导出表空间
FLUSH TABLES users FOR EXPORT;
-- 复制 .ibd 和 .cfg 文件
UNLOCK TABLES;
-- 导入表空间
CREATE TABLE users (...) ENGINE=InnoDB;
ALTER TABLE users DISCARD TABLESPACE;
-- 复制 .ibd 文件
ALTER TABLE users IMPORT TABLESPACE;
