Mysql数据库操作

Mysql数据库操作

Mysql 语句分类

一、DDL（数据定义语言）

功能：定义或修改数据库、表、视图等对象的结构。
特点：操作不可回滚，直接修改元数据。
常用命令：CREATE、ALTER、DROP、TRUNCATE、RENAME。

二、DCL（数据控制语言）

功能：管理用户权限和访问控制。
特点：涉及用户账户、角色和权限分配。
常用命令：GRANT、REVOKE、CREATE USER、DROP USER、RENAME USER。

三、DML（数据操作语言）

功能：操作表中的数据（增删改查）。
特点：在事务中可回滚（如未提交）。
常用命令：INSERT、UPDATE、DELETE、MERGE。

四、DQL（数据查询语言）

功能：查询数据，不修改数据结构。
特点：只读操作，结果为临时数据集。
核心命令：SELECT。

对比总结

类型	主要功能	是否修改数据	是否可回滚	典型场景
DDL	定义 / 修改数据库结构	❌	❌（通常不可回滚）	创建表、添加字段
DCL	管理用户权限	❌	❌	创建用户、分配权限
DML	操作表中的数据	✅	✅（事务中未提交时）	插入订单、更新库存
DQL	查询数据	❌	❌	统计销售额、查找用户

注意事项

1. 事务安全：
   • DML 操作建议在事务中执行（如BEGIN; UPDATE...; COMMIT;），以便回滚。
   • DDL 操作会隐式提交当前事务，无法回滚。
2. 权限管理：
   • 使用 DCL 时应遵循最小权限原则（如只授予用户必要的SELECT权限）。
3. 性能影响：
   • TRUNCATE（DDL）比DELETE（DML）更快，但会删除所有数据且不可回滚。

一、数值类型

1. 整数类型

类型	有符号范围	无符号范围	存储空间
TINYINT	-128 至 127	0 至 255	1 字节
SMALLINT	-32,768 至 32,767	0 至 65,535	2 字节
MEDIUMINT	-8,388,608 至 8,388,607	0 至 16,777,215	3 字节
INT	-2,147,483,648 至 2,147,483,647	0 至 4,294,967,295	4 字节
BIGINT	-9,223,372,036,854,775,808 至 9,223,372,036,854,775,807	0 至 18,446,744,073,709,551,615	8 字节

2. 浮点类型

类型	精度	存储空间	范围（近似值）
FLOAT	单精度（约 7 位小数）	4 字节	±1.175E-38 至 ±3.402E+38
DOUBLE	双精度（约 15 位小数）	8 字节	±2.225E-308 至 ±1.797E+308
DECIMAL(m,n)	高精度小数（m 为总位数，n 为小数位）	可变	取决于 m 和 n 的设置

二、字符串类型

1. 固定长度字符串

类型	最大长度	存储方式	适用场景
CHAR(n)	固定长度 n（1-255）	占用固定字节（不足补空格）	短字符串（如性别、状态）

2. 可变长度字符串

类型	最大长度	存储方式	适用场景
VARCHAR(n)	可变长度（0-65,535）	实际长度 + 1/2 字节（记录长度）	变长文本（如用户名、邮箱）
TEXT	65,535 字节	动态存储	长文本（如文章内容）
MEDIUMTEXT	16,777,215 字节	动态存储	中等长度文本
LONGTEXT	4,294,967,295 字节	动态存储	极大文本（如大型文档）

3. 二进制字符串

类型	最大长度	适用场景
BINARY(n)	固定长度 n 字节	存储二进制数据（如加密密钥）
VARBINARY(n)	可变长度 n 字节	存储变长二进制数据
TINYBLOB	255 字节	小二进制数据（如缩略图）
BLOB	65,535 字节	二进制数据（如图像）
MEDIUMBLOB	16,777,215 字节	中等大小二进制数据
LONGBLOB	4,294,967,295 字节	大二进制数据（如视频）

三、日期和时间类型

类型	格式	范围	存储空间
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 00:00:00 至 9999-12-31 23:59:59	8 字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC	4 字节
YEAR	YYYY	1901 至 2155	1 字节

四、枚举和集合类型

1. ENUM（枚举）

• 从预定义的字符串列表中选择一个值。

2. SET（集合）

• 从预定义的字符串列表中选择 0 个或多个值（用逗号分隔）。

五、JSON 类型

类型	描述	最大长度
JSON	存储 JSON 格式数据	与 LONGTEXT 相同（4GB）

六、空间数据类型

类型	描述
GEOMETRY	任意几何形状
POINT	点（x,y 坐标）
LINESTRING	线（由多个点连接而成）
POLYGON	多边形
MULTIPOINT	多个点
MULTILINESTRING	多条线
MULTIPOLYGON	多个多边形
GEOMETRYCOLLECTION	几何集合

七、特殊类型

类型	描述
BIT(n)	位字段类型，n 为位数（1-64）
BOOLEAN	布尔值（实际存储为 TINYINT (1)，0 为假，非 0 为真）
SERIAL	自增 BIGINT UNSIGNED 的别名

数据类型选择原则

1. 存储空间优先：选择能满足需求的最小数据类型（如年龄用TINYINT而非INT）。
2. 精度要求：对金额等需要精确计算的数据使用DECIMAL，避免浮点误差。
3. 性能考虑
   • CHAR比VARCHAR查询效率高（固定长度）。
   • TIMESTAMP比DATETIME节省空间，但范围较小。
4. 避免过度设计：如无特殊需求，尽量使用常用类型（如INT、VARCHAR、DATETIME）。

二.表的约束

表的约束条件

在数据库领域，表的约束条件能够保证数据的完整性、准确性以及一致性。下面为你介绍常见的约束类型及其作用：

1. 主键约束（PRIMARY KEY）

主键约束用于唯一标识表中的每一行数据。其特点如下：

• 主键列的值不能重复。
• 主键列的值不能为 NULL。
• 一个表只能有一个主键，但主键可以由多个列组合而成（复合主键）。

2. 唯一约束（UNIQUE）

唯一约束确保列中的所有值都是唯一的，不过它允许 NULL 值存在。与主键约束不同的是，一个表可以有多个唯一约束。

3. 非空约束（NOT NULL）

非空约束规定列的值不能为 NULL，在插入或更新数据时，该列必须有值。

4. 外键约束（FOREIGN KEY）

外键约束用于建立和加强两个表数据之间的链接。其作用包括：

• 保证引用完整性，即外键的值必须存在于关联表的主键中。
• 防止删除或修改关联表中的关键数据而导致数据孤立。

5. 检查约束（CHECK）

检查约束确保列中的值满足特定的条件。例如，可限制年龄列的值必须大于 0。

6. 默认约束（DEFAULT）

默认约束为列提供默认值，当插入数据时若该列没有明确赋值，就会使用默认值。

示例

下面通过 SQL 示例来说明约束的用法：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE,
    age INT CHECK (age > 0),
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    department_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

作用

• 数据完整性：约束条件可以防止无效数据进入数据库。
• 数据一致性：确保数据在整个系统中的一致性。
• 简化业务逻辑：部分业务规则可通过约束在数据库层面实现，减少应用层代码。

使用建议

• 依据业务需求合理选择约束类型。
• 避免定义相互冲突的约束条件。
• 在设计表结构时尽早规划约束，后期修改可能会影响数据。

三.Mysql 库操作

讲解默认库

1.information_schema

这是一个特殊的系统数据库，它存储着 MySQL 服务器的所有元数据信息。这里的元数据涵盖了数据库、表、列、索引、权限等数据库结构的详细信息。

-- 可以通过以下查询获取某个表的结构信息
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table';

主要作用：

• 用于编写动态 SQL 脚本，借助元数据来生成表结构报告或者自动化执行 DDL 操作。
• 数据库管理员能够通过查询这个数据库，快速了解数据库的整体结构情况。

2.mysql

该数据库是 MySQL 的核心系统数据库，主要用于存储用户账户、权限设置、主从复制配置以及服务器运行状态等关键信息。
重要表介绍：

• user表：保存了用户账户和全局权限相关的数据。
• db表：存储着数据库级别的权限信息。
• tables_priv表：记录了表级别的权限情况。

-- 查看用户权限配置
SELECT User, Host, Select_priv, Insert_priv 
FROM mysql.user 
WHERE User = 'your_username';

主要作用：

• 是权限管理的核心存储位置，所有用户权限的变更都会记录在这里。
• 服务器启动时，会从这个数据库加载配置信息。

3.performance_schema

这是 MySQL 5.5 版本之后新增的一个数据库，专门用于收集服务器的性能统计数据。它提供了有关查询执行、锁等待、内存使用等多方面的详细信息。

-- 查询执行时间最长的前10个SQL语句
SELECT DIGEST_TEXT, SUM_TIMER_WAIT/1000000000000 AS execution_time_ms 
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY execution_time_ms DESC LIMIT 10;

主要作用：

• 是性能调优的重要工具，能够帮助快速定位执行缓慢的查询语句。
• 可以监控服务器的运行状态，实时了解数据库的性能表现。

4.sys

此数据库是 MySQL 5.7 版本引入的，它基于 performance_schema 构建，提供了更易于理解的视图，让开发者和管理员能够更方便地分析性能问题。

-- 查看当前最活跃的连接
SELECT * FROM sys.connections ORDER BY current_statements DESC LIMIT 5;

主要作用：

• 提供了一系列方便的视图，帮助快速诊断常见的性能瓶颈问题。
• 适合运维人员快速获取关键指标，提高问题诊断效率。

总结

数据库名称	主要功能
information_schema	存储数据库元数据（如表结构、索引信息），用于动态查询和系统监控。
mysql	存储用户权限、服务器配置和复制信息，是权限管理的核心。
performance_schema	收集服务器性能数据（如查询执行时间、锁等待），用于性能调优。
sys	基于 performance_schema 的便捷视图，简化性能分析流程。

注意事项：

• 不要随意修改这些系统数据库中的数据，因为这可能会导致 MySQL 服务器无法正常运行。
• 在进行权限管理时，建议使用 GRANT 和 REVOKE 命令，而不是直接操作 mysql 数据库中的表。
• 对于大型生产环境，建议启用 performance_schema 以监控服务器性能，但要注意其可能带来的轻微性能开销。

操作

单表查语法

select  字段 1 ，字段二 ... from 表名

​				where 条件

​				group by 条件

​				having 筛选

​				order by 字段

​				limit 限制条数

关键字执行的优先级
from 
where
group by  
having
select
order by
limit

创建数据库

create database 库名

mysql> create database dufu;
Query OK, 1 row affected (0.02 sec)
   ### 创建一个名为dufu的数据库

数据库命名规则

1. 可以是字母，数字，下划线，@，#，$
2. 区分大小写
3. 唯一性
4. 不能使用关键字 如：create select drop等。
5. 不能使用单独数字。
6. 最长可以支持128位

选择数据库

use 库名

mysql> use dufu
Database changed
### use可以理解为使用或者切换的意思 使用dufu数据库
mysql> select database()
    -> ;
+------------+
| database() |
+------------+
| dufu       |
+------------+
1 row in set (0.00 sec)

###可以使用 select database （）：查看当前使用的数据库命令来证明我们use成功  

删除库 （执行这个操作要谨慎谨慎非常谨慎）

show databases

mysql> show databases; ###查看所有的库
+--------------------+
| Database           |
+--------------------+
| dufu               |
| information_schema |
| libai              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> drop database libai;   ###删除名为李白的数据库
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;  ###再次查看所有数据库
+--------------------+
| Database           |
+--------------------+
| dufu               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

创建表

create table 表名 （列名 （数据类型））

mysql> create table aaa (id int(1))  ###create 创建 一个表明为aaa的 （列名 列的数值类型（））
    -> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show tables ; ####查看当前库线下的表有哪些
+----------------+
| Tables_in_dufu |
+----------------+
| aaa            |
| bbb            |
+----------------+
2 rows in set (0.00 sec)

查看表的结构

desc 表名 ：相对路径

desc 库名.表名：绝对路径

mysql> desc aaa ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc dufu.aaa ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

###这两个方法类似于linux的绝对路径和相对路径都可以看到表的结构

修改表名

alter table 当前表名 rename 新表名

mysql> alter table aaa rename 3a    ### 修改表aaa的名字为3a
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;    #### 查看当前库下的所有表
+----------------+
| Tables_in_dufu |
+----------------+
| 3a             |
| bbb            |
| libai          |
+----------------+
3 rows in set (0.01 sec)

给表添加新的列

alter table 表名 add 列名 数据类型（）

mysql> alter table 3a add class int(10) not null ;  ###添加新的列为class并且不允许为空
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc 3a;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| class | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除列

alter table 表名 drop 列名

mysql> alter table 3a drop age ;   ###删除名为age的列
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc 3a;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| class | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段

alter table 表名 modify 表名 数据类型（）

mysql> alter table 3a modify name char(20);  ###更改列的数据类型
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc 3a;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| class | int      | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

alter table 表名 change 旧表名 新表名 数据类型（）

mysql> alter table 3a change name user_name varchar (20) ; ###change更改 表中name 为user_name
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc 3a;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| user_name | varchar(20) | YES  |     | NULL    |       |
| class     | int         | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

复制表

create table 新的 like 被复制 该命令只会复制表的结构表内的内容不会被复制如果想复制表内的内容可以使用下面这条命令

create table 新的 select * from 旧的

mysql> create table 3b like 3a;      ###将名为3a的表复制一份名表3b
Query OK, 0 rows affected (0.01 sec)

mysql> desc 3b;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| user_name | varchar(20) | YES  |     | NULL    |       |
| class     | int         | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

查看数据库中某个表是否存在

show tables from 库名 like '表名'

mysql> SHOW TABLES FROM dufu LIKE '3b'; ### 查看当前数据库中的3b表是否存在
+---------------------+
| Tables_in_dufu (3b) |
+---------------------+
| 3b                  |
+---------------------+
1 row in set (0.01 sec)

在表中插入数据

mysql> insert into 3a values (1,'zzh',20);  ### 在3a表中插入 值为 1，zzh，20 他们分别对应默认的列名
Query OK, 1 row affected (0.01 sec)
mysql> select * from 3a;
+------+-----------+-------+
| id   | user_name | class |
+------+-----------+-------+
|    1 | zzh       |    20 |
+------+-----------+-------+
1 row in set (0.00 sec)
###插入多个列的话可以使用  `insert into 3a values (1,'zzh',20),(2,'zxx',50)

删除数据insert

delete from 表名 where 列名=值

mysql> delete from 3a where id=1;  
Query OK, 1 row affected (0.00 sec)

mysql> select * from 3a;
Empty set (0.00 sec)

更新数据

mysql> update 3a set id=3 where user_name='bbbbbb' ;   ###将bbbbbbb的id改为3
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from 3a;
+------+-----------+-------+
| id   | user_name | class |
+------+-----------+-------+
|    1 | zzh       |    20 |
|    1 | zzx       |    20 |
|    3 | bbbbbb    |    20 |
+------+-----------+-------+
3 rows in set (0.00 sec)

mysql> 

多表连接查询

SELECT o.order_id, u.name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'Completed';

含义：通过多表连接查询已完成订单的信息，包括订单 ID、用户姓名和商品名称。

聚合函数与分组

SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY AVG(price) DESC;

含义：统计每个类别下的商品数量和平均价格，筛选出商品数量超过 5 的类别，并按平均价格降序排列。

子查询示例

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

含义：查询工资高于全体员工平均工资的员工信息。

常用函数

SELECT 
  CONCAT(first_name, ' ', last_name) AS full_name,
  DATE_FORMAT(birth_date, '%Y-%m-%d') AS formatted_date,
  UPPER(email) AS email_upper
FROM users;

含义：使用字符串拼接、日期格式化和大小写转换函数处理数据。

事务处理示例

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

含义：将用户 1 的账户余额减少 100 并增加到用户 2 的账户，作为一个原子操作执行。

帮我生成mysql的语法例子并解释含义 例子有查询 插入更新 where 要求例子多详细并且有代码解释

以下是详细的 MySQL 语法示例及代码解释，包含查询、插入、更新、WHERE 子句等核心操作：

查询语句（SELECT）

-- 查询所有字段
SELECT * FROM employees;

-- 查询特定字段
SELECT first_name, last_name, salary 
FROM employees 
WHERE department = 'IT' AND salary > 50000;

-- 带计算字段
SELECT 
  product_name, 
  price, 
  price * 0.9 AS discounted_price  -- 计算9折价格
FROM products;

-- 去重查询
SELECT DISTINCT department FROM employees;

-- 排序与分页
SELECT * 
FROM orders 
ORDER BY order_date DESC  -- 按日期降序
LIMIT 10 OFFSET 20;       -- 取第21-30条记录

代码解释：

• * 表示查询所有字段，也可指定字段名（如first_name）。
• WHERE 子句过滤条件支持逻辑运算符（AND/OR）和比较运算符（>/=）。
• AS 为计算字段或表达式设置别名。
• DISTINCT 去重重复值。
• ORDER BY 排序（ASC升序 /DESC降序），LIMIT和OFFSET用于分页。

插入语句（INSERT）

-- 单条插入
INSERT INTO customers (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

-- 批量插入
INSERT INTO products (name, price, category)
VALUES 
  ('iPhone', 999, 'Electronics'),
  ('iPad', 799, 'Tablets'),
  ('MacBook', 1999, 'Computers');

-- 插入查询结果
INSERT INTO logs (user_id, action, created_at)
SELECT id, 'login', NOW() FROM users WHERE status = 'active';

代码解释：

• 字段名与值需按顺序对应，可省略自增 ID 或允许NULL的字段。
• 批量插入用逗号分隔多组VALUES。
• INSERT INTO ... SELECT 将查询结果直接插入表中，NOW()返回当前时间。

更新语句（UPDATE）

-- 单条件更新
UPDATE products 
SET price = price * 1.1  -- 价格上调10%
WHERE category = 'Electronics';

-- 多条件更新
UPDATE employees
SET salary = salary + 500, 
    department = 'Engineering'
WHERE hire_date < '2020-01-01' AND salary < 60000;

-- 关联子查询更新
UPDATE orders o
SET status = 'Shipped'
WHERE EXISTS (
  SELECT 1 FROM shipments s 
  WHERE s.order_id = o.order_id 
  AND s.ship_date < CURDATE()
);

代码解释：

• 可同时更新多个字段，用逗号分隔。
• WHERE 子句限定更新范围，无WHERE则更新全量数据。
• 子查询可用于复杂条件判断（如本例通过shipments表关联更新orders状态）。

WHERE 子句详解

-- 比较运算符
SELECT * FROM users
WHERE age BETWEEN 18 AND 30    -- 范围查询
  AND country IN ('US', 'UK')  -- 枚举匹配
  AND email LIKE '%@gmail.com' -- 模糊匹配
  AND is_active = 1;           -- 布尔判断

-- 空值处理
SELECT * FROM products
WHERE discount IS NULL;        -- 查找无折扣商品

-- 逻辑组合
SELECT * FROM tasks
WHERE (priority = 'High' OR due_date < CURDATE())
  AND status != 'Completed';

代码解释：

• BETWEEN 包含边界值（18 和 30）。
• LIKE 中%表示任意字符，_表示单个字符。
• IS NULL 用于判断空值，!= 或 <> 表示不等于。
• 括号可调整逻辑优先级，避免歧义。

高级查询示例

-- 多表连接
SELECT 
  o.order_id, 
  u.username, 
  p.product_name, 
  oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN users u ON o.user_id = u.user_id
WHERE o.status = 'Paid';

-- 聚合函数
SELECT 
  department,
  COUNT(*) AS total_employees,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5  -- 过滤分组结果
ORDER BY avg_salary DESC;

-- 窗口函数
SELECT 
  product_name,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

代码解释：

• JOIN 按条件关联多表数据，常用INNER JOIN（交集）、LEFT JOIN（保留左表全部记录）。
• 聚合函数（COUNT/AVG/MAX）配合GROUP BY分组统计，HAVING过滤分组结果。
• 窗口函数（如RANK()）为每行数据计算排名，PARTITION BY按类别分组计算。

数据类型与约束

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 18),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users
ADD CONSTRAINT fk_country
FOREIGN KEY (country_id) REFERENCES countries(id);

代码解释：

• AUTO_INCREMENT 自动生成唯一 ID，PRIMARY KEY 主键约束。
• UNIQUE 确保字段值不重复，NOT NULL 禁止空值。
• CHECK 自定义约束条件（如年龄≥18），DEFAULT 设置默认值。
• FOREIGN KEY 建立表关联，确保引用完整性。

事务处理

START TRANSACTION;
SAVEPOINT before_update;  -- 设置保存点

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

IF (SELECT balance FROM accounts WHERE user_id = 2) < 0 THEN
  ROLLBACK TO before_update;  -- 回滚到保存点
ELSE
  COMMIT;  -- 提交事务
END IF;

代码解释：

• START TRANSACTION 开启事务，COMMIT 提交，ROLLBACK 回滚。
• SAVEPOINT 允许部分回滚，避免整个事务失败。
• 事务确保数据一致性（如转账操作的原子性）。

索引优化

-- 创建单列索引
CREATE INDEX idx_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users (last_name, first_name, age);

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

代码解释：

• 索引加速查询（如WHERE email = ...），但会降低写入性能。
• 复合索引遵循 “最左前缀” 原则（如last_name、last_name + first_name可命中，仅first_name无法命中）。
• EXPLAIN 分析查询是否使用索引及扫描行数。

视图与存储过程

-- 创建视图
CREATE VIEW active_orders AS
SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'Active';

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
  SELECT * FROM orders WHERE user_id = user_id;
END$$
DELIMITER ;

代码解释：

• 视图是虚拟表，简化复杂查询（如SELECT * FROM active_orders）。
• 存储过程封装 SQL 逻辑，通过CALL GetUserOrders(1)调用。

常用函数

-- 字符串函数
SELECT 
  CONCAT(first_name, ' ', last_name) AS full_name,
  SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username
FROM users;

-- 日期函数
SELECT 
  order_id,
  order_date,
  DATE_ADD(order_date, INTERVAL 7 DAY) AS estimated_delivery,
  DATEDIFF(NOW(), order_date) AS days_since_order
FROM orders;

-- 条件函数
SELECT 
  product_name,
  price,
  CASE 
    WHEN price > 1000 THEN 'Expensive'
    WHEN price > 500 THEN 'Mid-range'
    ELSE 'Budget'
  END AS price_category
FROM products;

代码解释：

• CONCAT 拼接字符串，SUBSTRING截取子串，LOCATE查找字符位置。
• DATE_ADD/DATEDIFF处理日期运算，NOW()返回当前时间。
• CASE 实现条件判断，类似编程语言的if-else。

权限管理

-- 创建用户并授权
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON ecommerce.orders TO 'app_user'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 撤销权限
REVOKE INSERT ON ecommerce.orders FROM 'app_user'@'localhost';

代码解释：

• 用户权限按用户@主机维度管理，密码需用IDENTIFIED BY指定。
• GRANT 授权（如SELECT/INSERT），REVOKE 回收权限，ALL PRIVILEGES表示所有权限。

备份与恢复

# 命令行导出数据库
mysqldump -u root -p ecommerce > ecommerce_backup.sql

# 命令行导入数据库
mysql -u root -p ecommerce < ecommerce_backup.sql

代码解释：

• mysqldump导出 SQL 脚本（含表结构和数据），>重定向输出到文件。
• 导入时需先创建空数据库，mysql命令通过<读取 SQL 文件执行。

性能优化

-- 优化查询
SELECT SQL_NO_CACHE * FROM products WHERE category = 'Books';  -- 禁用查询缓存

-- 分析慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录到慢查询日志

-- 优化表结构
ALTER TABLE users ENGINE=InnoDB;  -- 使用InnoDB存储引擎支持事务
OPTIMIZE TABLE orders;  -- 重建表并优化空间

代码解释：

• SQL_NO_CACHE 避免使用查询缓存，用于测试真实查询性能。
• 慢查询日志（slow_query_log）帮助定位性能瓶颈，需配合EXPLAIN分析执行计划。
• OPTIMIZE TABLE 碎片整理，适合频繁更新 / 删除的表。

高级特性

-- 临时表
CREATE TEMPORARY TABLE temp_sales (product_id INT, total_sales DECIMAL(10,2));
INSERT INTO temp_sales SELECT product_id, SUM(quantity) FROM order_items GROUP BY product_id;
SELECT * FROM temp_sales;

-- 递归CTE（公共表表达式）
WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

代码解释：

• 临时表在会话结束自动删除，用于存储中间结果。
• 递归 CTE 通过WITH RECURSIVE实现层级查询（如组织架构），需定义初始查询和递归部分。

安全注意事项

-- 防止SQL注入
-- 错误示例（存在注入风险）
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

-- 正确做法（使用预处理语句）
PREPARE stmt FROM "SELECT * FROM users WHERE username = ? AND password = ?";
SET @a = 'test_user';
SET @b = 'hashed_password';
EXECUTE stmt USING @a, @b;

代码解释：

• SQL 注入通过拼接恶意输入破坏查询逻辑（如' OR 1=1 --）。
• 预处理语句（PREPARE/EXECUTE）将参数与 SQL 逻辑分离，自动转义特殊字符。