数据库语法汇总

MySQL 数据库操作命令

[TOC]

1. 数据库操作

创建数据库

-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
-- 创建数据库时推荐使用 utf8mb4 字符集以支持完整的 Unicode 字符(包括emoji)
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建数据库(如果不存在)
CREATE DATABASE IF NOT EXISTS database_name;

修改数据库

-- 修改数据库字符集
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 重命名数据库(MySQL没有直接重命名数据库的命令，通常需要导出再导入)
-- 替代方案：创建新数据库，迁移数据，删除旧数据库

删除数据库

-- 删除数据库
DROP DATABASE database_name;

-- 删除数据库(如果存在)
DROP DATABASE IF EXISTS database_name;

使用数据库

-- 选择/使用数据库
USE database_name;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;

数据库备份与恢复

# 备份数据库(命令行)
mysqldump -u username -p database_name > backup_file.sql

# 恢复数据库(命令行)
mysql -u username -p database_name < backup_file.sql

2. 数据表操作

2.1. 创建数据表

基本创建语法

CREATE TABLE 表名 (
    -- 列名 数据类型 [约束条件]
    列名1 数据类型 [约束条件] [COMMENT '列注释'],
    列名2 数据类型 [约束条件] [COMMENT '列注释'],
    ...
    -- 表级约束（如主键、外键、唯一约束等）
    [CONSTRAINT 约束名] 约束类型 (列名)
) 
-- 表选项
[ENGINE=存储引擎] 
[DEFAULT CHARSET=字符集] 
[COMMENT='表注释'];

完整创建示例

CREATE TABLE 表名 (
    -- 列定义开始
    id INT AUTO_INCREMENT,               -- 整数类型，自增长
    username VARCHAR(50) NOT NULL,       -- 变长字符串，不能为空
    password CHAR(60) NOT NULL,          -- 定长字符串，不能为空
    
    -- 列约束示例
    age INT UNSIGNED DEFAULT 18,         -- 无符号整数，默认值18
    email VARCHAR(100) UNIQUE,           -- 唯一约束
    balance DECIMAL(10,2) CHECK (balance >= 0),  -- 检查约束
    
    -- 日期时间类型
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,           -- 默认当前时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 自动更新
    
    -- 表级约束（定义在所有列之后）
    PRIMARY KEY (id),                    -- 主键约束
    UNIQUE KEY uk_username (username),   -- 唯一键约束
    INDEX idx_email (email),             -- 普通索引
    FOREIGN KEY (role_id) REFERENCES roles(id) -- 外键约束
)
-- 表选项
ENGINE=InnoDB                          -- 存储引擎（默认InnoDB）
DEFAULT CHARSET=utf8mb4                -- 字符集（推荐utf8mb4支持emoji）
COLLATE=utf8mb4_unicode_ci             -- 排序规则
COMMENT='用户信息表';                  -- 表注释

1. 列定义:

   • 格式：列名 数据类型 [约束] [COMMENT '注释']
   • 常用数据类型：INT, VARCHAR, CHAR, TEXT, DATE, TIMESTAMP, DECIMAL等
   • 常用约束：NOT NULL, DEFAULT, UNIQUE, CHECK, AUTO_INCREMENT等

2. 表级约束:

   • 主键：PRIMARY KEY (列名)
   • 外键：FOREIGN KEY (列名) REFERENCES 外表名(列名)
   • 索引：INDEX 索引名 (列名)
   • 唯一约束：UNIQUE KEY 约束名 (列名)

3. 表选项:

   • ENGINE：指定存储引擎（InnoDB/MyISAM等）
   • DEFAULT CHARSET：设置默认字符集
   • COLLATE：设置排序规则
   • COMMENT：为表添加注释

4. 注释方式:

   • 列注释：在列定义中使用 COMMENT '注释内容'
   • 表注释：在表选项中使用 COMMENT='注释内容'

从现有表创建新表

-- 只复制结构
CREATE TABLE new_table LIKE original_table;

-- 复制结构和数据
CREATE TABLE new_table AS SELECT * FROM original_table;

2.2. 修改数据表

添加列：

ALTER TABLE table_name
-- 修改指定表
ADD COLUMN column_name datatype constraints;
-- 添加新列（列名、数据类型、约束）

修改列：

-- 修改列数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

-- 重命名列
ALTER TABLE table_name
CHANGE COLUMN old_name new_name datatype;  -- 重命名列（必须指定数据类型）

删除列：

ALTER TABLE table_name 
DROP COLUMN column_name;  -- 删除指定列

添加约束：

-- 添加主键
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);  -- 将指定列设为主键

-- 添加外键
ALTER TABLE table_name
ADD CONSTRAINT fk_name  -- 指定外键约束名称
FOREIGN KEY (column_name) REFERENCES other_table(column_name);  -- 设置外键关系

-- 添加唯一约束
ALTER TABLE table_name
ADD UNIQUE (column_name);  -- 使指定列的值唯一

删除约束：

-- 删除主键
ALTER TABLE table_name 
DROP PRIMARY KEY;
-- 删除外键
ALTER TABLE table_name 
DROP FOREIGN KEY fk_name;

重命名表：

-- 方法1
RENAME TABLE old_name TO new_name;  -- 重命名表
-- 方法2
ALTER TABLE old_name RENAME TO new_name;  -- 重命名表的另一种方式

2.3. 删除数据表

-- 删除表
DROP TABLE table_name;
-- 删除表(如果存在)
DROP TABLE IF EXISTS table_name;
-- 截断表(删除所有数据但保留结构)
TRUNCATE TABLE table_name;

2.4. 使用数据表

查看表信息：

SHOW TABLES;  -- 显示当前数据库中的所有表

DESCRIBE table_name;  -- 显示表结构（列名、类型、约束等）
DESC table_name;  -- DESCRIBE的简写形式
SHOW COLUMNS FROM table_name;  -- 显示表列的另一种方式

SHOW CREATE TABLE table_name;  -- 显示创建该表的完整SQL语句

表数据操作：

-- 插入数据
INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

-- 查询数据
SELECT * FROM table_name;

-- 更新数据
UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

-- 删除数据
DELETE FROM table_name WHERE condition;

索引操作：

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 删除索引
DROP INDEX index_name ON table_name;

2.5. 实用技巧

复制表结构及数据：

CREATE TABLE new_table SELECT * FROM original_table;

快速清空并重置自增ID：

TRUNCATE TABLE table_name;

添加注释：

-- 表注释
ALTER TABLE table_name COMMENT '表注释内容';

-- 列注释
ALTER TABLE table_name 
MODIFY COLUMN column_name datatype COMMENT '列注释内容';

3. 查询操作

3.1. 单表查询

基本查询

-- 查询表中所有列的所有数据
SELECT * FROM table_name;

-- 仅查询表中的特定列
SELECT column1, column2 FROM table_name;

-- 为查询结果的列设置别名（便于理解和展示）
SELECT column1 AS '列1别名', column2 AS '列2别名' FROM table_name;

条件查询

-- 基础条件查询（condition可以是任何有效的条件表达式）
SELECT * FROM table_name WHERE column1 = 'value';

-- 多条件AND查询（必须同时满足所有条件）
SELECT * FROM table_name WHERE condition1 AND condition2;

-- 多条件OR查询（满足任一条件即可）
SELECT * FROM table_name WHERE condition1 OR condition2;

-- 范围查询（包含边界值）
SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

-- IN查询（检查列值是否在指定列表中）
SELECT * FROM table_name WHERE column IN ('value1', 'value2', 'value3');

-- LIKE模糊查询（%表示任意多个字符，_表示单个字符）
SELECT * FROM table_name WHERE column LIKE '张%';  -- 查找以"张"开头的值

排序查询

-- 单列排序（ASC升序-默认，DESC降序）
SELECT * FROM table_name ORDER BY column1 DESC;

-- 多列排序（先按column1排序，相同值再按column2排序）
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

分页查询

-- 传统LIMIT分页（offset从0开始，count表示每页数量）
SELECT * FROM table_name LIMIT 10, 20;  -- 跳过前10条，返回接下来的20条

-- 现代分页写法（语义更明确）
SELECT * FROM table_name LIMIT 20 OFFSET 10;  -- 同上，跳过10条取20条

3.2. 分组查询

基本分组

-- 按单列分组并计算每组的记录数
SELECT department, COUNT(*) AS '员工数量' 
FROM employees 
GROUP BY department;

-- 按多列分组（复合分组）
SELECT department, gender, COUNT(*) 
FROM employees 
GROUP BY department, gender;

分组后过滤

-- HAVING用于对分组结果进行过滤（WHERE是对原始数据过滤）
SELECT department, COUNT(*) as emp_count 
FROM employees 
GROUP BY department 
HAVING emp_count > 5;  -- 只显示员工数大于5的部门

聚合函数

-- 常用聚合函数示例
SELECT 
    COUNT(*) AS '总记录数',
    SUM(salary) AS '薪资总额',
    AVG(salary) AS '平均薪资',
    MAX(salary) AS '最高薪资',
    MIN(salary) AS '最低薪资'
FROM employees;

-- 分组聚合（计算每个部门的平均薪资）
SELECT department, AVG(salary) AS '部门平均薪资'
FROM employees 
GROUP BY department;

3.3. 连接查询

内连接 (INNER JOIN)

-- 基本内连接（只返回两表中匹配的行）
SELECT e.name, d.department_name 
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 多表内连接（连接三个表）
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;

外连接

-- 左外连接（返回左表所有行，右表无匹配则为NULL）
SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

-- 右外连接（返回右表所有行，左表无匹配则为NULL）
SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

-- 全外连接模拟（MySQL没有FULL JOIN，用UNION实现）
SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id UNION SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

交叉连接 (CROSS JOIN)

-- 显式交叉连接（返回两表的笛卡尔积）
SELECT p.product_name, s.size_name FROM products p CROSS JOIN sizes s;

-- 隐式交叉连接（不推荐使用，不易读）
SELECT p.product_name, s.size_name FROM products p, sizes s;

自连接

-- 自连接示例：查找员工及其经理（同一表内连接）
SELECT e1.name AS '员工姓名', e2.name AS '经理姓名'
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

3.4. 嵌套查询 (子查询)

WHERE子句中的子查询

-- 单行子查询（子查询返回单行单列）
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 多行子查询（使用IN操作符）
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE is_active = 1);

-- 比较子查询（使用ALL/ANY）
SELECT * FROM employees 
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 10);

FROM子句中的子查询

-- 将子查询结果作为临时表
SELECT dept_stats.dept_name, dept_stats.avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM departments d
    JOIN employees e ON d.id = e.dept_id
    GROUP BY d.name
) AS dept_stats
WHERE dept_stats.avg_salary > 5000;

SELECT子句中的子查询

-- 在SELECT中使用子查询（相关子查询）
SELECT p.product_name, 
       (SELECT COUNT(*) FROM order_items WHERE product_id = p.id) AS order_count
FROM products p;

EXISTS子查询

-- 使用EXISTS检查是否存在相关记录
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.total_amount > 1000
);

3.5. 集合查询

UNION (去重合并)

-- 合并两个查询结果并去重
SELECT product_name FROM current_products UNION 
SELECT product_name FROM discontinued_products;

UNION ALL (不去重合并)

-- 合并两个查询结果保留所有重复项
SELECT employee_name FROM full_time_employees 
UNION ALL 
SELECT employee_name FROM part_time_employees;

INTERSECT (交集 - MySQL 8.0+)

-- 获取两个查询结果的交集
SELECT customer_id FROM premium_customers 
INTERSECT
SELECT customer_id FROM active_customers;

EXCEPT/MINUS (差集 - MySQL 8.0+)

-- 获取第一个查询有而第二个查询没有的结果
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

3.6. 高级查询技巧

公用表表达式 (CTE)

-- 基本CTE（提高复杂查询的可读性）
WITH dept_salary AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_salary WHERE avg_salary > 5000;

-- 递归CTE (处理层级数据，如组织结构)
WITH RECURSIVE org_tree AS (
    -- 基础查询（顶级节点）
    SELECT id, name, parent_id, 1 AS level
    FROM employees
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询（下级节点）
    SELECT e.id, e.name, e.parent_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

窗口函数 (MySQL 8.0+)

-- ROW_NUMBER() 为每行分配唯一序号
SELECT employee_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- RANK() 和 DENSE_RANK() 处理并列排名
SELECT product_name, sales,
       RANK() OVER (ORDER BY sales DESC) AS rank,          -- 标准排名（会跳号）
       DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank  -- 密集排名（不跳号）
FROM products;

-- 聚合窗口函数（计算移动平均值等）
SELECT date, revenue,
       SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 3_day_total,
       AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 3_day_avg
FROM daily_sales;

JSON查询 (MySQL 5.7+)

-- 从JSON字段提取数据
SELECT id, 
       JSON_EXTRACT(product_details, '$.specs.weight') AS product_weight
FROM products;

-- 使用->操作符简写JSON路径
SELECT id, product_details->'$.price' AS product_price
FROM products;

-- JSON条件查询
SELECT * FROM products 
WHERE product_details->'$.rating' >= 4;

-- JSON数组处理
SELECT id, 
       JSON_EXTRACT(tags, '$[0]') AS first_tag,
       JSON_LENGTH(tags) AS tag_count
FROM articles;

4. 插入数据（INSERT）

基本插入

-- 插入单行数据（指定列）
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
#	1. 列名和值必须一一对应
#	2. 可以省略自增主键列
#	3. 对于有默认值的列可以省略

-- 插入单行数据（省略列名，必须提供所有列的值）
INSERT INTO table_name VALUES (value1, value2, value3, ...);

批量插入

-- 批量插入多行数据（高效方式）
INSERT INTO table_name (column1, column2)
VALUES 
    (value1, value2),
    (value3, value4),
    (value5, value6);
-- 优点：
#  1. 减少客户端与服务器通信次数
#  2. 提高插入效率

-- 使用SET语法插入（较少用）
INSERT INTO table_name 
SET column1 = value1, 
    column2 = value2;

特殊插入方式

-- 从其他表复制数据插入
INSERT INTO target_table (column1, column2)
SELECT source_col1, source_col2 
FROM source_table
WHERE condition;

-- 插入时忽略重复键错误
INSERT IGNORE INTO table_name (column1, column2)
VALUES (value1, value2);

-- 1. 对于主键或唯一键冲突的记录会被静默忽略
-- 2. 不会报错中断执行

-- 替换已有记录（先删除后插入）
REPLACE INTO table_name (id, column1, column2) VALUES (1, 'new_value1', 'new_value2');
/* 等同于：
DELETE FROM table_name WHERE id = 1;
INSERT INTO table_name (id, column1, column2) VALUES (1, 'new_value1', 'new_value2');
*/

5. 更新数据（UPDATE）

基本更新

-- 更新符合条件的记录
UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2
WHERE condition;

-- 1. 一定要有WHERE条件，否则会更新所有行
-- 2. 可以同时更新多列

-- 使用表达式更新
UPDATE products
SET price = price * 1.1  -- 价格上调10%
WHERE category = '电子产品';

特殊更新方式

-- 使用子查询更新
UPDATE employees
SET salary = salary * 1.05
WHERE department IN (
    SELECT department FROM high_performance_depts
);

-- 多表关联更新
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'VIP'
WHERE c.level = '黄金会员';

-- 使用ORDER BY和LIMIT限制更新
UPDATE log_entries
SET processed = 1
WHERE processed = 0
ORDER BY created_at
LIMIT 1000;  -- 每次只处理1000条

增量更新

-- 基于当前值的增量更新
UPDATE inventory
SET stock = stock - 5  -- 库存减少5
WHERE product_id = 123;

-- 时间戳自动更新
UPDATE articles
SET view_count = view_count + 1,
    last_viewed = CURRENT_TIMESTAMP
WHERE id = 456;

6. 删除数据（DELETE）

基本删除

-- 删除符合条件的记录
DELETE FROM table_name WHERE condition;

-- 1. 没有WHERE条件会删除表中所有数据！
-- 2. 删除操作不可逆，建议先备份

-- 示例：删除30天前的日志
DELETE FROM system_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

特殊删除方式

-- 多表关联删除
DELETE p  -- 只删除products表中的记录
FROM products p
JOIN discontinued_items d ON p.id = d.product_id
WHERE d.discontinued_date < '2023-01-01';

-- 使用ORDER BY和LIMIT限制删除
DELETE FROM temp_data
WHERE expired = 1
ORDER BY created_at
LIMIT 1000;  -- 每次只删除1000条

-- 快速清空表（不可回滚）
TRUNCATE TABLE table_name;
/* 与DELETE的区别：
1. 速度更快
2. 重置自增计数器
3. 无法通过事务回滚
4. 不触发DELETE触发器
*/

7. 综合操作技巧

事务处理

-- 使用事务确保数据一致性
START TRANSACTION;

INSERT INTO orders (customer_id, amount) 
VALUES (123, 99.99);

UPDATE accounts 
SET balance = balance - 99.99
WHERE user_id = 123;

COMMIT;  -- 或 ROLLBACK 回滚

条件插入/更新

-- 存在则更新，不存在则插入（MySQL特有语法）
INSERT INTO users (id, name, login_count)
VALUES (1, '张三', 1)
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = NOW();

使用临时表批量操作

-- 创建临时表存储要处理的数据
CREATE TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    new_value VARCHAR(255)
);

-- 批量插入要更新的数据
INSERT INTO temp_updates VALUES (1,'A'),(2,'B'),(3,'C');

-- 使用临时表批量更新
UPDATE main_table m
JOIN temp_updates t ON m.id = t.id
SET m.column = t.new_value;

8. 安全操作建议

1. 重要数据操作前先备份

   -- 创建备份表
   CREATE TABLE orders_backup AS SELECT * FROM orders;

2. 使用SELECT测试WHERE条件

   -- 执行DELETE前先用SELECT确认影响范围
   SELECT * FROM orders WHERE status = 'cancelled';

3. 启用安全更新模式

   -- 防止无WHERE条件的更新/删除
   SET SQL_SAFE_UPDATES = 1;

4. 分批处理大数据量

   -- 使用LIMIT分批删除
   DELETE FROM large_table WHERE condition LIMIT 10000;

5. 考虑使用软删除

   -- 添加is_deleted标记而非物理删除
   UPDATE customers SET is_deleted = 1 WHERE id = 123;

9. MySQL 视图操作命令

9.1. 创建视图

基本视图创建

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name
WHERE condition;

-- 1. 视图是基于SQL查询的虚拟表
-- 2. 不实际存储数据，每次访问时动态生成
-- 3. 可以简化复杂查询

创建带检查选项的视图

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name
WHERE condition
WITH [CASCADED|LOCAL] CHECK OPTION;

-- CHECK OPTION作用：
-- 1. 通过视图修改数据时，必须符合视图定义的条件
-- 2. CASCADED：严格检查所有底层视图条件
-- 3. LOCAL：只检查当前视图条件

创建算法确定的视图

CREATE ALGORITHM = MERGE VIEW view_name AS
SELECT * FROM table_name WHERE condition;
/*
算法选项：
1. MERGE：将视图查询与外部查询合并执行（性能最佳）
2. TEMPTABLE：先执行视图查询创建临时表
3. UNDEFINED：由MySQL自动选择（默认）
*/

创建带有安全限制的视图

CREATE SQL SECURITY DEFINER VIEW view_name AS
SELECT * FROM table_name;
/*
安全选项：
1. DEFINER：以视图创建者权限执行（默认）
2. INVOKER：以调用者权限执行
*/

9.2. 修改视图

完全替换视图定义

CREATE OR REPLACE VIEW view_name AS
SELECT new_columns
FROM new_tables
WHERE new_conditions;

使用ALTER修改视图

ALTER VIEW view_name AS
SELECT updated_columns
FROM updated_tables
WHERE updated_conditions;

修改视图属性

ALTER VIEW view_name ALGORITHM = TEMPTABLE
SQL SECURITY INVOKER
WITH CHECK OPTION;

9.3. 删除视图

-- 基本删除语法
DROP VIEW [IF EXISTS] view_name;

-- 同时删除多个视图
DROP VIEW view1, view2, view3;

-- 安全删除（视图不存在时不报错）
DROP VIEW IF EXISTS view_name;

9.4. 使用视图

查询视图数据

-- 像普通表一样查询视图
SELECT * FROM view_name WHERE condition;

-- 查看视图结构
DESCRIBE view_name;
DESC view_name;  -- 简写

-- 查看视图创建语句
SHOW CREATE VIEW view_name;

通过视图更新数据

-- 通过视图插入数据
INSERT INTO view_name (column1, column2) 
VALUES (value1, value2);
/* 限制条件：
1. 视图必须基于单表
2. 不能包含聚合函数、DISTINCT、GROUP BY等
3. 必须包含基表中所有NOT NULL列
*/

-- 通过视图更新数据
UPDATE view_name 
SET column1 = new_value 
WHERE condition;

-- 通过视图删除数据
DELETE FROM view_name 
WHERE condition;

9.5. 视图管理命令

查看所有视图

-- 查看当前数据库所有视图
SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';

-- 从information_schema查询视图信息
SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'your_database';

视图依赖关系查询

-- 查看视图依赖的表
SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'your_database';

-- 查看依赖于某表的所有视图
SELECT * FROM information_schema.VIEWS WHERE VIEW_DEFINITION LIKE '%your_table%';

9.6. 高级视图技巧

嵌套视图

-- 创建基于其他视图的视图
CREATE VIEW nested_view AS SELECT * FROM base_view WHERE column1 > 100;

联合视图

-- 创建合并多个查询结果的视图
CREATE VIEW union_view AS SELECT * FROM table1 WHERE condition1
UNION ALL
SELECT * FROM table2 WHERE condition2;

聚合视图

-- 创建包含聚合数据的视图
CREATE VIEW sales_summary AS
SELECT 
    product_id,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY product_id;

可更新视图限制

-- 检查视图是否可更新
SELECT TABLE_NAME, IS_UPDATABLE 
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'your_database';
/*
不可更新视图常见情况：
1. 包含聚合函数
2. 使用DISTINCT
3. 包含GROUP BY/HAVING
4. 包含UNION
5. 包含子查询在某些情况下
*/

9.7. 视图最佳实践

1. 命名规范：使用v_或view_前缀区分视图和表

   CREATE VIEW v_customer_orders AS ...

2. 性能考虑：复杂视图可能影响查询性能

   -- 考虑物化视图替代方案
   CREATE TABLE mv_sales_summary AS SELECT ...;

3. 安全应用：使用视图实现行列级安全

   -- 创建只显示部分数据的视图
   CREATE VIEW v_employee_limited AS
   SELECT id, name, department FROM employees
   WHERE department = CURRENT_USER_DEPARTMENT();

4. 版本控制：将视图定义纳入数据库版本管理

5. 文档注释：为视图添加注释说明

   CREATE VIEW v_monthly_sales COMMENT '月度销售汇总视图' AS
   SELECT ...;

9.8. 视图使用示例

简化复杂查询

CREATE VIEW v_order_details AS
SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name,
    od.quantity,
    od.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

数据安全控制

-- 创建仅显示公开产品的视图
CREATE VIEW v_public_products AS
SELECT product_id, product_name, price
FROM products
WHERE is_public = 1;

报表视图

CREATE VIEW v_monthly_sales_report AS
SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);