SQL基础

SQL基础-MySQL

【增删改查】主要看【查】的部分

1. 创建【增】

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键（唯一标识），【自动增长】
  username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名，非空且唯一
  age INT DEFAULT 0,  -- 年龄，默认值0
  email VARCHAR(100),
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 创建时间，默认当前时间
);

2. 插入【增】

单行插入

--【以上表为例】
--1.插入部分字段
-- 场景1：插入 username、age、email（id 自增，create_time 用默认当前时间）
INSERT INTO users (username, age, email)
VALUES ('zhangsan', 20, 'zhangsan@test.com');--字符串类型的字段，插入的值必须用引号包裹，单双引号均可。为了保证兼容性，建议单引号。

-- 场景2：插入 username、age（email 允许为 NULL，会自动填 NULL；create_time 用默认值）
INSERT INTO users (username, age)
VALUES ('lisi', 22);  -- email 结果为 NULL

-- 场景3：插入 username（age 用默认值 0；email 为 NULL；create_time 用默认值）
INSERT INTO users (username)
VALUES ('wangwu');  -- age 结果为 0，email 为 NULL

--2. 插入全字段
-- 场景1：id 传 NULL（自增），create_time 用默认值
INSERT INTO users
VALUES (NULL, 'zhaoliu', 25, 'zhaoliu@test.com', DEFAULT);

-- 场景2：id 省略（自增），age 用默认值 0，email 为 NULL
INSERT INTO users
VALUES (NULL, 'sunqi', DEFAULT, NULL, DEFAULT);  -- age 结果为 0，email 为 NULL

多行插入

-- 场景1：批量插入部分字段（指定 username、age、email）
INSERT INTO users (username, age, email)
VALUES 
  ('zhouba', 18, 'zhouba@test.com'),  -- 正常插入
  ('wujiu', 30, NULL),  -- email 显式插入 NULL
--('wujiu', 30),  -- 省略 email，隐式插入 NULL（前提：email 允许 NULL）
  ('zhengshi', DEFAULT, 'zhengshi@te

-- 场景2：批量插入全字段（按顺序传值）
INSERT INTO users
VALUES 
  (NULL, 'a', 22, 'a@test.com', '2023-01-01 08:00:00'),  -- 指定 create_time
  (NULL, 'b', NULL, 'b@test.com', DEFAULT);  -- age 显式插入 NULL（允许，因 age 无 NOT NULL）

3. 删除【删】

删除表

--如果删除一张不存在的表，MySQL 会报错。若`users` 表不存在，则报错
DROP TABLE users;

-- 若 `users` 表存在，则删除；不存在则不执行，也不报错
DROP TABLE IF EXISTS users;

-- 同时删除 `users` 和 `orders` 两张表
DROP TABLE users, orders;

删除数据

--1.删除部分数据
-- 删除 users 表中 age < 18 的用户（按条件删除）
DELETE FROM users 
WHERE age < 18;

--2. 删除全部数据（DELETE 不带条件 / TRUNCATE）
-- 方式1：DELETE 不带条件（逐行删除，可回滚，保留表结构和自增序列）
DELETE FROM users;

-- 方式2：TRUNCATE（清空表，不可回滚，表结构保留，但自增序列重置为1）
TRUNCATE TABLE users;

4. 修改数据【改】

修改表中部分数据（带条件）：

- 将 users 表中 username 为 'zhangsan' 的用户，age 改为 25，email 改为 'zs@example.com'
UPDATE users
SET age = 25, email = 'zs@example.com'
WHERE username = 'zhangsan';

修改全表数据（无条件，慎用）：

- 将所有用户的 create_time 改为当前时间（谨慎！会影响所有行）
UPDATE users
SET create_time = CURRENT_TIMESTAMP;

修改表名：

-- 把表名 `users` 改成 `user_info`
ALTER TABLE users RENAME TO user_info;

-- 省略 TO 的写法（效果相同）
ALTER TABLE user_info RENAME users;  -- 改回原来的名字

5. 查询【查】

注释

 -- 这是单行注释

 /*
这是多行注释
可以写多行说明
*/

基础查询

-- 查询整个表
SELECT * FROM table_name; 

-- 查询指定列
SELECT column1, column2 FROM table_name; 
select name,age from student --关键字和函数名通常不区分大小写。

别名（AS）

SELECT column_name AS alias_name FROM table_name; -- 给列起别名

--例子：
select name as 学生姓名, age as 学生年龄 from student

常量和运算

--查询直接选取常量值
/*SELECT 后面可以跟常量值，它们不依赖任何表。
数据库会直接返回这些值，作为一行结果。这个结果只是读取，不会保存到数据库*/
select 200, '篮球' as hobby;
/*
查询结果如下：
200    hobby
200    篮球
*/

-- 查询中计算新值
--例子：
--从名为student的数据表中选择出所有学生的姓名（name）和分数（score），并且额外计算出分数的 2 倍（double_score）。
select name, score, score*2 as double_score from student

条件查询（WHERE）

SELECT * FROM employee WHERE age = 25;
SELECT * FROM table_name WHERE column = 'value'; --建议单引号
SELECT * FROM table_name WHERE column > 10; 

--例子：
select name,score from student where name='鱼皮'

比较运算符

/*
= ：等于
<> 或 != ：不等于
> ：大于
< ：小于
>= ：大于等于
<= ：小于等于
BETWEEN ... AND ... ：在某个范围内（包含边界）
IN (值列表) ：值属于指定列表中的任意一个
*/
--例子：
select name,age from student where name <> '热dog' --不等于

select name, age, salary from employees where age between 25 and 30;

SELECT name, score FROM student WHERE name IN ('鱼皮', '小明', '张三');

空值（IS NULL / IS NOT NULL）

SELECT * FROM table_name WHERE column IS NULL; -- 查找空值

/*从名为 student 的数据表中选择出所有学生的姓名（name）、
年龄（age）和成绩（score），要求学生年龄不为空值。*/
select name,age,score from student where age is not null --查找非空值

模糊查询（LIKE, NOT LIKE）

SELECT * FROM table_name WHERE column LIKE 'A%'; -- 以 A 开头
SELECT * FROM table_name WHERE column LIKE '%B'; -- 以 B 结尾
SELECT * FROM table_name WHERE column LIKE '%C%'; -- 包含 C

--只查询不包含“张”的数据行
select name, age, position from employees where name not like '%张%';
-- 只查询不以 "张" 开头的数据行
select name, age, position from employees where name not like '张%';
-- 只查询不以 "张" 结尾的数据行
select name, age, position from employees where name not like '%张';

逻辑运算（AND, OR, NOT）

SELECT * FROM table_name WHERE column1 = 'A' AND column2 = 'B'; -- 两个条件都满足
SELECT * FROM table_name WHERE column1 = 'A' OR column2 = 'B'; -- 其中一个满足即可
SELECT * FROM table_name WHERE NOT column = 'A'; -- 取反

--例子：
/*从名为 student 的数据表中选择出所有学生的姓名（name）、成绩（score），
要求学生的姓名包含 "李"，或者成绩（score）大于 500。*/
select name,score from student 
where
name like '%李%' or score>500

去重（DISTINCT）

--单字段去重
select distinct class_id from students;

--多字段去重：根据多个字段的组合来进行去重操作
select distinct class_id, exam_num from student;
--从 student 表里，**查询所有不同的班级ID和考试编号组合**，重复的组合只显示一次。

排序（ORDER BY）

（1）升降序

默认 ORDER BY 是 ASC（从小到大）。
想要降序排序，必须明确写 DESC。

（2）中英文

1️⃣英文默认按 ASCII 码排序（大写在前，小写在后）。

2️⃣汉字默认按拼音排序（如果是 UTF-8）。

3️⃣想忽略大小写，可以用 LOWER(name)。

4️⃣不同数据库的排序方式可能不同，取决于字符集。

SELECT * FROM table_name ORDER BY column ASC; -- 升序排列（从小到大）
SELECT * FROM table_name ORDER BY column DESC; -- 降序排列（从大到小）

-- 从名为 student 的数据表中选择出学生姓名（name）、年龄（age）和成绩（score），首先按照成绩从大到小排序，如果成绩相同，则按照年龄从小到大排序。
select name,age,score from student order by score desc,age asc;

截断和偏移（LIMIT, OFFSET）

select task_name, due_date from tasks limit 2; --取前2条

select task_name, due_date from tasks limit 2, 3; --跳过前2条，取3条【2-5】

select task_name, due_date from tasks limit 2 offset 10;-- 跳过 10 条，取 2条【11-12】

--例子：
--从名为 student 的数据表中选择学生姓名（name）和年龄（age），按照年龄从小到大排序，从第 2 条数据开始、截取 3 个学生的信息。
****select name,age from student order by age asc limit 1,3;

条件分支（CASE）

SELECT name,
       CASE
           WHEN age < 18 THEN '未成年'
           WHEN age BETWEEN 18 AND 60 THEN '成年人' --包含边界 [18,60]
                 --WHEN age >= 18 AND age <= 60 THEN '成年人'【同上】
           ELSE '老年人'
       END AS age_group
FROM user;

时间函数

获取当前时间

SELECT NOW(); -- 当前日期时间，例如：2025-03-22 14:45:30
SELECT SYSDATE; -- 当前系统时间（在语句执行时确定，区别在于执行时机）
SELECT CURDATE(); -- 当前日期，例如：2025-03-22
SELECT CURTIME(); -- 当前时间，例如：14:45:30

---需要参数
SELECT DATE() as current_date;-- 错误：单独使用会报错，因为缺少参数
SELECT DATE(NOW());  -- 正确：从当前时间中提取日期，结果同 CURDATE()
SELECT TIME(NOW()) as current_time;--正确：从当前时间中提取时间，等价于 CURTIME()。

时间格式化

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 格式化日期时间，例如：2025-03-22 14:45:30
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- 星期、月份、日期、年份，例如：Saturday, March 22, 2025

时间计算

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY); -- 当前日期 + 7 天，例如：2025-03-29
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH); -- 当前日期 - 1 个月，例如：2025-02-22

-- 定义两个时间点用于计算（起始时间和结束时间）
SET @start_time = '2023-01-15 08:30:15';
SET @end_time = '2025-03-20 14:45:30';
SET @now_time = NOW();           -- 当前日期时间
SET @today = CURDATE();          -- 当前日期

-- 1. 计算年差（YEAR）
SELECT TIMESTAMPDIFF(YEAR, @start_time, @end_time) AS year_diff;
-- 结果：2（相差2年多，取整数部分）

-- 2. 计算月差（MONTH）
SELECT TIMESTAMPDIFF(MONTH, @start_time, @end_time) AS month_diff;
-- 结果：26（从2023-01到2025-03共26个月）

-- 3. 计算周差（WEEK）
SELECT TIMESTAMPDIFF(WEEK, @start_time, @end_time) AS week_diff;
-- 结果：113（总天数÷7后取整）

-- 4. 计算分钟差（MINUTE）
SELECT TIMESTAMPDIFF(MINUTE, @start_time, @end_time) AS minute_diff;
-- 结果：1,140,675（总秒数÷60后取整）

-- 5. 计算秒差（SECOND）
SELECT TIMESTAMPDIFF(SECOND, @start_time, @end_time) AS second_diff;
-- 结果：68,440,515（精确到秒的差值）

时间提取

SELECT YEAR(NOW()); -- 获取年份，例如：2025
SELECT MONTH(NOW()); -- 获取月份，例如：3
SELECT DAY(NOW()); -- 获取日期，例如：22
SELECT HOUR(NOW()); -- 获取小时，例如：14
SELECT MINUTE(NOW()); -- 获取分钟，例如：45
SELECT SECOND(NOW()); -- 获取秒，例如：30
SELECT DAYNAME(NOW()); -- 获取星期几，例如：Saturday

时间转换

SELECT STR_TO_DATE('2025-03-22', '%Y-%m-%d'); -- 字符串转日期，例如：2025-03-22
SELECT UNIX_TIMESTAMP(NOW()); -- 转换为 Unix 时间戳，例如：1745558415
SELECT FROM_UNIXTIME(1745558415); -- 时间戳转普通时间，例如：2025-03-22 14:45:30

时间比较

SELECT DATEDIFF('2025-04-01', '2025-03-22'); -- 计算日期差，例如：10（相差 10 天）
SELECT TIMEDIFF('14:30:00', '12:00:00'); -- 计算时间差，例如：02:30:00（2 小时 30 分钟）
SELECT IF(NOW() > '2025-03-22 12:00:00', '已过', '未到'); -- 判断时间，例如："已过"

字符串处理

SELECT UPPER(name), LOWER(name) FROM table_name; -- 转大写/小写

SELECT name, LENGTH(name) AS name_length FROM employees; --计算长度
SELECT CONCAT(first_name, ' ', last_name) FROM table_name; -- 字符串拼接

聚合函数

--计算指定列的行数或非空值的数量。
select count(*) as order_num from orders;--订单表中的总订单数

select count(distinct customer_id) as customer_num from orders; --计算订单表中不同客户的数量

--计算指定列的数值之和
select sum(amount) as total_amount from orders;

--计算指定列的数值平均值
select avg(payments) as avg_pay from orders;

--找出指定列的最大值
select max(payments) as avg_pay from orders;

--找出指定列的最小值
select min(payments) as avg_pay from orders;

分组聚合—单字段分组（GROUP BY）

-- 按类别分组统计
SELECT category, COUNT(*) FROM products
GROUP BY category; --每个类别下面有多少条产品记录

SELECT category, COUNT(DISTINCT product_name) FROM products 
GROUP BY category;--每个类别下面有多少种产品

分组聚合—多字段分组（GROUP BY 多列）

SELECT category, subcategory, COUNT(*) FROM products GROUP BY category, subcategory; -- 先按 category 分组，再按 subcategory 分组

--例子：
--假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、exam_num（考试编号）、score（成绩）。请你编写一个 SQL 查询，统计学生表中的班级编号（class_id），考试编号（exam_num）和**每个班级参加每场考试**的总学生人数（total_num）。
select class_id, exam_num, count(distinct id) as total_num from student
group by class_id, exam_num;

分组筛选（HAVING）

--分组聚合之后对分组进行过滤
--假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）。
select class_id, sum(score) as total_score from student
group by class_id
having total_score >150;

关联查询（JOIN）

CROSS JOIN（笛卡尔积）——无差别地组合所有行

在 SQL 中，JOIN 的默认行为是 INNER JOIN，但如果没有 ON 条件，它就变成了 CROSS JOIN。

SELECT * FROM student CROSS JOIN class; -- 生成所有组合

INNER JOIN（内连接）

使用 INNER_JOIN 后，只有两个表之间存在对应关系的数据才会被放到查询结果中。

SELECT s.name, c.name
FROM student s
INNER JOIN class c ON s.class_id = c.id; -- 仅匹配的行

OUTER JOIN（外连接）

在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

SELECT s.name, c.name
FROM student s
LEFT JOIN class c ON s.class_id = c.id; -- 左表全部 + 右表匹配

SELECT s.name, c.name
FROM student s
RIGHT JOIN class c ON s.class_id = c.id; -- 右表全部 + 左表匹配

子查询（Subquery）

SELECT name, age FROM student 
WHERE age > (SELECT AVG(age) FROM student); -- 查询大于平均年龄的学生

EXISTS子查询

子查询中的一种特殊类型是 "exists" 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

/*从 table1 中查询出所有 “在 table2 中存在相同 id 的记录”。*/
SELECT * FROM table1 
WHERE EXISTS 
(SELECT 1 FROM table2 WHERE table1.id = table2.id);

这里的 1 其实没有实际意义，它只是一个 占位符，用来告诉 SQL：
“如果 orders 里有匹配的行，就返回 TRUE！”

实际上，这里写 SELECT 1、SELECT 'hello'、SELECT 99 都可以：

但通常大家都写 SELECT 1，因为它最简单、最直观、最标准。

这类子查询通常配合 EXISTS 或 NOT EXISTS 使用，它们的核心逻辑是「判断子查询是否能找到符合条件的行」，而不是「子查询返回什么具体值」。

-- 查找没有对应班级id的学生记录。
select name, age, class_id from student 
where not exists 
(select class_id from class where class.id = student.class_id);

组合查询（UNION / UNION ALL）

在 SQL 中，组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作。

包括两种常见的组合查询操作：UNION 和 UNION ALL。

UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。
UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

SELECT name FROM student UNION SELECT name FROM teacher; -- 合并去重
SELECT name FROM student UNION ALL SELECT name FROM teacher; -- 合并不去重

窗口函数（OVER）【重点】

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

开窗函数允许我们在查询中进行对分组数据进行计算，同时保留原始行的详细信息。

SELECT name, department, SUM(salary) OVER (PARTITION BY department) FROM employee; -- 按部门计算累计工资
--返回姓名，部门，部门累计工资

PARTITION BY——分区

GROUP BY——分组

排序开窗函数（ORDER BY）

（1）升降序

默认 ORDER BY 是 ASC（从小到大）。
想要降序排序，必须明确写 DESC。

（2）中英文

1️⃣英文默认按 ASCII 码排序（大写在前，小写在后）。

2️⃣汉字默认按拼音排序（如果是 UTF-8）。

3️⃣想忽略大小写，可以用 LOWER(name)。

4️⃣不同数据库的排序方式可能不同，取决于字符集。

加上 ORDER BY 后，窗口函数的默认计算范围会自动变成从分组的第一行，到 “当前行”（按 ORDER BY 排好的顺序），自然就形成了 “累加” 效果。

SELECT name, salary, SUM(salary) OVER (ORDER BY salary) FROM employee; -- 计算按工资排序的累计和

--第一行的累计和 = 第一个人的工资
--第二行的累计和 = 第一个 + 第二个人的工资
--第三行的累计和 = 前三个人工资之和
--…以此类推

排名函数（RANK, ROW_NUMBER）

RANK() 👉 排名（相同名次会占位，可能有跳跃）
ROW_NUMBER() 👉 简单编号（从 1 开始）——相比Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。
DENSE_RANK() 👉 紧密排名（相同名次不占位，不会跳跃）—— 同样处理并列名次，但下一个名次不会跳过（两个 90 分之后，80 分直接是第 2 名），名次始终「连续递增」，不会出现间隔。

-- 示例：按部门分组，再按分数降序排名（带并列）
SELECT 
  name, 
  department, 
  score,
  RANK() OVER (
    PARTITION BY department  -- 按部门分组（每组内单独排名）
    ORDER BY score DESC      -- 组内按分数降序排（分数高的排前面）
  ) AS dept_score_rank
FROM students;

-- 注释1：如果你改 ORDER BY score DESC 为 ORDER BY score ASC，排名结果会完全不同（分数低的排前面）
-- 注释2：如果去掉 PARTITION BY，就是全表按分数排序（不分组，所有员工一起排）

-- 创建示例数据
WITH scores (student_id, score) AS (
  SELECT 1, 90 UNION ALL
  SELECT 2, 90 UNION ALL  -- 与student_id=1并列第一
  SELECT 3, 80 UNION ALL
  SELECT 4, 80 UNION ALL  -- 与student_id=3并列
  SELECT 5, 70
)
-- 应用三个排名函数（按分数降序）
SELECT 
  student_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,  -- 唯一序号，无并列
  RANK() OVER (ORDER BY score DESC) AS rnk,            -- 并列会占位，导致跳跃
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk -- 并列不占位，连续递增
FROM scores;

-- 结果：
-- student_id | score | row_num | rnk | dense_rnk
-- -----------|-------|---------|-----|----------
-- 1          | 90    | 1       | 1   | 1
-- 2          | 90    | 2       | 1   | 1  -- 与1并列，ROW_NUMBER仍递增
-- 3          | 80    | 3       | 3   | 2  -- RANK跳过2（因前2个占了1的位置）为3，DENSE_RANK连续到2
-- 4          | 80    | 4       | 3   | 2
-- 5          | 70    | 5       | 5   | 3  -- RANK跳过4为5，DENSE_RANK连续到3

SUM() 累计求和

SELECT 员工, 月份, 工资,
       SUM(工资) OVER (PARTITION BY 员工 ORDER BY 月份) AS **累积工资**
FROM 工资表;

AVG() 计算平均值

SELECT 姓名, 成绩,
       AVG(成绩) OVER () AS 全班平均分
FROM 学生成绩;

LAG / LEAD（前后行数据）

如果只提供一个参数，那么默认情况下，第二个参数（即“偏移量”）是 1，第三个参数（即“默认值”）是 NULL。

SELECT name, salary, LAG(salary) OVER (ORDER BY salary) AS prev_salary FROM employee; -- 取上一行数据，查看当前薪资与上一个薪资的差异

📌 解释

ORDER BY salary 确保按照 salary 递增排序。
LAG(salary) 取 前一行 的 salary 值。
LEAD(salary) 取 后一行 的 salary 值。

select id,name,age,score,class_id,
lag(name,1,NULL) over (partition by class_id order by score DESC)
as prev_name,
lead(name,1,NULL) over (partition by class_id order by score DESC)
as next_name
from student

滑动窗口`ROWS BETWEEN` / `RANGE BETWEEN`（控制计算范围）

`ROWS BETWEEN`

👉 定义计算时，窗口的前后范围，常用于滚动计算。

SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示 当前行 + 前两行的数据 进行计算。
这样就可以得到一个 滚动累计工资！

SUM(salary) OVER (
  PARTITION BY department
  ORDER BY hire_date
  ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING  -- 范围：当前行 + 后2行（共3行）
)

ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING 表示 当前行 + 前两行的数据 进行计算。

`RANGE BETWEE`

按照数值范围设定窗口，而不是行数
适用于数值或日期排序

例子：

--统计 “每个员工入职后 30 天内，同批入职同事的工资总和”（适合新人薪资成本分析）
SELECT 
  name, 
  hire_date,  -- 入职日期（日期型字段）
  salary,
  -- 窗口范围：当前员工入职日期 ± 30天内的所有记录
  SUM(salary) OVER (
    ORDER BY hire_date  -- 按入职日期排序
    RANGE BETWEEN INTERVAL **'30 days'** PRECEDING AND CURRENT ROW  -- 向前30天到当前行
  ) AS 30天内入职同事工资总和
FROM employees;

为什么 INTERVAL '30 days' 能识别为 30 天？

这是数据库专门为 “日期 / 时间计算” 设计的 INTERVAL 语法，核心作用是 “告诉数据库：这是一个时间间隔，数值是 30，单位是天”。

结构固定：INTERVAL '数值' 时间单位（数值可以加引号，单位需用数据库支持的固定写法）；

支持的单位：除了 days（天），还有 hour（小时）、minute（分钟）、month（月）、year（年）等，比如 INTERVAL '2' hour（2 小时）、INTERVAL '1' month（1 个月）。

--查看 “与当前员工薪资差距在 5000 以内的所有同事的平均薪资”（适合分析薪资梯队）
SELECT 
name, 
salary,  -- 薪资（数值型字段）
-- 窗口范围：当前薪资-5000 到 当前薪资+5000 之间的所有记录
AVG(salary) OVER (
  ORDER BY salary  -- 按薪资排序
  RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING  -- 向前减5000，向后加5000
) AS 薪资±5000内的平均工资
FROM employees;

→ 计算窗口

当前工资 - 5000（往前找）
当前工资 + 5000（往后找）

`EXCLUDE`（排除某些行）

👉 用来排除某些行不参与窗口计算【很少用】。

--对每个员工，计算 “入职时间相邻的前 2 人 + 后 2 人” 的平均绩效分，但不包含自己的分数（用于分析 “周围同事对个人绩效的影响”）。
SELECT 
  name,
  score,
  -- 窗口范围：当前行的前2行到后2行，但排除当前行
  AVG(score) OVER (
    ORDER BY hire_date  -- 按入职日期排序（确定“相邻”顺序）
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING  -- 先定义大范围：前2行到后2行
    EXCLUDE CURRENT ROW  -- 从大范围内排除当前行
  ) AS 前后2名同事的平均绩效
FROM performance;

💡 解释：

EXCLUDE CURRENT ROW：计算时排除自己，只看前后两行的数据。
还有 EXCLUDE TIES【排除与当前行 “排序值相同” 的并列行】、EXCLUDE NO OTHERS【不排除任何行（默认行为）】 。

⚠ 特殊情况（使用表里没出现过的列）

SELECT name, salary,
       COUNT(*) OVER () AS total_employees
FROM employees;

💡 这里 COUNT(*) 其实不需要用 salary 或 name，它只是计算 整张表的行数，然后每一行都会得到 相同的值（总行数）。

🚀 进阶情况（额外计算不在 `SELECT` 里的内容）

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY hire_date) AS hiring_order
FROM employees;

💡 这里 DENSE_RANK() 用的是 hire_date 排序，但 hire_date 本身 并没有出现在 SELECT 里！

但是它仍然能用，因为 SQL 允许我们在 ORDER BY 里引用表的其他列。

视图（View）

虚拟表，通过查询数据表生成的结果集合，不存储实际的数据，每次查询动态生成。

不能索引，不能直接修改数据。

创建后视图会作为数据库对象（类似表、函数）长期存在，后续可直接通过 SELECT * FROM 视图名 重复调用。

CREATE VIEW repeated_views AS
SELECT uid, cid, COUNT(*) AS view_count
FROM play_record_tb
GROUP BY uid, cid
HAVING view_count > 1;

公共表表达式（CTE - Common Table Expressions）

临时的查询块

本质是对 SQL 语句的逻辑封装，不单独存储数据（除非显式物化）。

WITH repeated_views AS (
    SELECT uid, cid, COUNT(*) AS view_count
    FROM play_record_tb
    GROUP BY uid, cid
    HAVING view_count > 1
)
SELECT * FROM repeated_views;

会话（Session）

会话是指与数据库的连接过程。每当你打开一个数据库连接时，都会开启一个新的会话。在会话期间，你可以执行查询、更新数据等操作。会话结束后，临时表、会话变量等都会自动销毁。

特点：
- 会话是与数据库服务器之间的一个连接。
- 在会话期间，所有的操作和临时数据存储都对当前会话可见。
- 会话结束后，所有临时数据和状态都会丢失。
创建会话：
会话是自动创建的，无需显式操作。只要连接到数据库，就是一个会话。

事务（Transaction）

事务是指一组数据库操作，它们要么全部成功执行，要么全部回滚，不会只执行其中一部分。事务用于确保数据库操作的原子性、一致性、隔离性和持久性（ACID特性）。

临时表（Temporary Table）

临时表是一个只在当前会话或事务中有效的表。当会话结束或事务完成后，临时表会被自动删除。

存储在内存中。

临时表可以与常规表一样被查询、修改。

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product_id;

存储过程（Stored Procedure）

定义：预先编译并存储在 MySQL 服务器中的一组 SQL 语句集合，可通过“调用命令”重复执行。
核心作用：
- 封装复杂逻辑（比如多步查询、更新操作），避免重复写相同 SQL（类似“函数”）；
- 减少客户端与服务器的通信量（只需传“调用命令”，不用传完整 SQL）；
- 增强安全性（可控制用户仅调用存储过程，不直接操作表）。

示例：

-- 创建存储过程：根据分类查商品平均价格
DELIMITER //
CREATE PROCEDURE get_avg_price(IN cat VARCHAR(50), OUT avg_p DECIMAL(10,2))
BEGIN
  SELECT AVG(price) INTO avg_p FROM products WHERE category = cat;
END //
DELIMITER ;

-- 调用存储过程
CALL get_avg_price('electronics', @result);--传递给存储过程 get_avg_price 的第一个参数 IN cat VARCHAR(50)。
SELECT @result; -- 查看结果

和之前概念的关联：常和“事务”结合（存储过程内可包含事务逻辑），也可操作“临时表”处理中间数据。

索引（Index）

定义：给表的字段创建的“数据结构（如 B+树）”，类似书籍的“目录”，目的是加快查询速度。
核心作用：
- 优化查询效率（比如“给商品表的 category 字段建索引，查‘电子产品’分类的商品时，不用扫描全表，直接通过索引定位”）；
- 注意：索引会加快查询，但会减慢 INSERT/UPDATE/DELETE（因为修改数据时要同步更新索引），需合理创建（比如给“常查询、少修改”的字段建索引）。

示例：

-- 给 products 表的 category 字段建普通索引
CREATE INDEX idx_product_category ON products(category);

-- 给 orders 表的 order_id 字段建唯一索引（确保 order_id 不重复）
CREATE UNIQUE INDEX idx_order_id ON orders(order_id);

和之前概念的关联：视图、CTE、临时表都可以基于“有索引的表”查询，间接享受索引的加速效果。

触发器（Trigger）

定义：绑定在表上的“自动执行规则”——当表发生 INSERT/UPDATE/DELETE 操作时，触发器会自动执行预设的 SQL 语句。
核心作用：
- 保证数据完整性（比如“订单表插入数据时，自动减少商品表的库存”）；
- 记录操作日志（比如“用户表更新时，自动把旧数据插入日志表”）；
- 避免手动执行漏操作（无需开发者额外写代码触发，MySQL 自动执行）。

示例：

-- 创建触发器：订单插入时，自动减少商品库存
CREATE TRIGGER update_stock AFTER INSERT ON orders
FOR EACH ROW -- 每行数据插入都触发
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity -- NEW 代表刚插入的订单数据
  WHERE id = NEW.product_id;
END;

特点：完全“被动触发”，无法手动调用；操作的是“触发表相关的数据”（如订单表触发，操作商品表）。

游标（Cursor）

定义：MySQL 中用于“逐行处理查询结果”的工具——默认 SQL 是“批量处理结果”，游标可以把结果集拆成“单行数据”，逐行读取和操作。
核心作用：
- 处理复杂的行级逻辑（比如“查询所有余额小于 0 的用户，逐行给他们发送提醒”）；
- 常用于存储过程或函数中（单独使用较少），弥补“SQL 批量处理无法逐行操作”的不足。

示例（简化）：

DELIMITER //
CREATE PROCEDURE check_low_balance()
BEGIN
  DECLARE user_id INT;
  -- 1. 定义游标，关联“余额<0的用户”查询结果
  DECLARE cur CURSOR FOR SELECT id FROM users WHERE balance < 0;

  -- 2. 打开游标
  OPEN cur;

  -- 3. 逐行读取游标数据
  FETCH cur INTO user_id;
  WHILE @@FETCH_STATUS = 0 DO
    -- 逐行操作：比如插入提醒日志
    INSERT INTO reminders(user_id, content) VALUES(user_id, '余额不足');
    FETCH cur INTO user_id; -- 读取下一行
  END WHILE;

  -- 4. 关闭游标
  CLOSE cur;
END //
DELIMITER ;

特点：效率较低（逐行处理不如批量处理快），仅在“必须逐行操作”时使用（如行级业务逻辑复杂）。

posted @ 2026-03-24 19:54 Stéphane 阅读(0) 评论(0) 收藏举报

刷新页面返回顶部

Stéphane