MySQL数据操作与查询笔记之DML、DQL、多表连接与子查询

-- 不指定字段：必须按表结构的列顺序给全所有列的值
insert into 表 values(值1, 值2, 值3, ...);

-- 指定字段：只给指定列赋值，未指定的列使用默认值或 null
insert into 表(字段1, 字段2, ...) values(值1, 值2, ...);

-- 不指定字段，批量插入多行
insert into 表 values(值1, 值2, ...), (值1, 值2, ...), ...;

-- 指定字段，批量插入多行
insert into 表(字段1, 字段2, ...) values(值1, 值2, ...), (值1, 值2, ...), ...;

-- 示例
insert into users(username, password, email, age) 
values('zhangsan', '123456', 'zs@xx.com', 25);

-- 批量
insert into users(username, password, email) values
('lisi', 'abc123', 'ls@xx.com'),
('wangwu', 'qwe456', 'ww@xx.com');

update 表名 set 字段1 = 值1, 字段2 = 值2, ... where 条件;

-- 修改指定用户的邮箱和年龄
update users set email = 'new_zs@xx.com', age = 26 where username = 'zhangsan';

delete from 表名 where 条件;    -- 删除符合条件的行
delete from 表名;               -- ⚠️ 删除全表数据（逐行删，慢）
truncate table 表名;            -- 清空全表数据（直接释放空间，快）

select [distinct] 字段1 [as 别名], 字段2, ...
from 表名 [as 别名]
[where 条件]
[group by 分组字段]
[having 分组后过滤条件]
[order by 排序字段 [asc|desc]]
[limit 返回行数 [offset 起始行]];

select * from 表名;                          -- 查全表所有行所有列
select 列1, 列2 from 表名;                    -- 查指定列（所有行）
select 列1 as 别名1, 列2 as 别名2 from 表名;  -- 给结果列起别名
select 表别名.列1 from 表名 as 表别名;         -- 给表起别名（多表查询时常用）

select * from 表名 where 条件;

-- 聚合函数示例
select count(*)   from users;              -- 统计总行数
select max(age)   from users;              -- 最大年龄
select min(age)   from users;              -- 最小年龄
select sum(balance) from users;            -- 余额总和
select avg(age)   from users;              -- 平均年龄
select count(*), max(age), min(age), avg(age) from users;  -- 一次查多个

-- 按城市分组，统计每个城市的人数和平均年龄
select city, count(*) as 人数, avg(age) as 平均年龄
from users
group by city;

-- 错误示例：select 里写了非分组字段 username，且未被聚合函数包裹
-- mysql 5.7+ 默认报错：expression #2 of select list is not in group by clause
select city, username, count(*) from users group by city;   -- ❌

-- 正确写法：只放分组字段和聚合函数
select city, count(*), max(age) from users group by city;   -- ✅

-- where 先筛人，group by 再分组，having 再筛分组结果
select city, count(*) as cnt
from users
where age >= 18           -- 先筛选成年人
group by city             -- 再按城市分组
having cnt >= 5;          -- 最后只要人数 ≥5 的城市

select * from 表名 order by 列1 [asc|desc], 列2 [asc|desc];

select distinct 列1, 列2 from 表名;

select * from 表名 limit n;             -- 只返回前 n 行
select * from 表名 limit m, n;          -- 跳过 m 行，返回 n 行（m 从 0 开始）
select * from 表名 limit n offset m;    -- 同上，更明确的写法

-- 假设每页 10 条
-- 第 1 页：limit 0, 10   （跳过 0 行，取 10 行）
-- 第 2 页：limit 10, 10  （跳过 10 行，取 10 行）
-- 第 3 页：limit 20, 10  （跳过 20 行，取 10 行）
-- 公式：limit (页码-1)*每页条数, 每页条数
select * from users limit 0, 10;   -- 第 1 页
select * from users limit 10, 10;  -- 第 2 页

-- 书写顺序（你敲的）：
select city, count(*) as cnt
from users
where age >= 18
group by city
having cnt >= 5
order by cnt desc
limit 3;

-- 执行顺序（数据库实际做的）：
-- ① from users           → 找到 users 表
-- ② where age >= 18      → 筛掉未成年，只保留成年人行
-- ③ group by city        → 把剩下的行按 city 分组
-- ④ count(*)             → 计算每组有多少人
-- ⑤ having cnt >= 5      → 只保留人数 ≥5 的城市组
-- ⑥ order by cnt desc    → 按人数降序排列
-- ⑦ limit 3              → 取前 3 行

拆分前：一个大表中，部门信息每个员工行都存一份
拆分后：
  employees 表：id, name, dept_id          ← 只存部门编号
  departments 表：id, dept_name, location, budget  ← 部门信息独立存储

departments（1） ←──→（n） employees
  一个部门有多个员工，一个员工只属于一个部门

-- "一"的一方
create table departments (
    id       int unsigned primary key auto_increment,
    name     varchar(50) not null,
    location varchar(100),
    budget   decimal(12, 2)
);

-- "多"的一方：dept_id 是外键
create table employees (
    id       int unsigned primary key auto_increment,
    name     varchar(30) not null,
    salary   decimal(10, 2),
    dept_id  int unsigned,
    foreign key (dept_id) references departments(id)
);

users（1） ←──→（1） user_profiles
  一个用户有一份详细资料

create table user_profiles (
    id     int unsigned primary key,
    avatar varchar(200),
    bio    text,
    foreign key (id) references users(id)
);

students（m） ←──→（n） courses
  一个学生选多门课，一门课有多个学生选

-- 学生表
create table students (
    id   int unsigned primary key auto_increment,
    name varchar(30) not null
);

-- 课程表
create table courses (
    id   int unsigned primary key auto_increment,
    name varchar(50) not null
);

-- 中间表（选课记录）：两个外键组合
create table student_courses (
    student_id int unsigned,
    course_id  int unsigned,
    score      decimal(3, 1),
    primary key (student_id, course_id),          -- 复合主键，防止重复选课
    foreign key (student_id) references students(id),
    foreign key (course_id)  references courses(id)
);

create table employees (
    id       int unsigned primary key auto_increment,
    name     varchar(30) not null,
    salary   decimal(10, 2),
    dept_id  int unsigned,
    foreign key (dept_id) references departments(id)
);

foreign key (dept_id) references departments(id)
    on delete cascade      -- 主表行被删时，从表引用行也跟着删
    on update cascade      -- 主表主键被改时，从表外键也跟着改

-- 假设 departments 有 3 行，employees 有 5 行
-- 下面的查询返回 3 × 5 = 15 行
select * from departments, employees;
-- 或显式写法
select * from departments cross join employees;

departments                     employees
┌────┬──────────┐              ┌────┬──────┬─────────┐
│ id │ name     │              │ id │ name │ dept_id │
├────┼──────────┤              ├────┼──────┼─────────┤
│ 1  │ 技术部   │              │ 1  │ 张三 │ 1       │
│ 2  │ 市场部   │              │ 2  │ 李四 │ 1       │
│ 3  │ 财务部   │              │ 3  │ 王五 │ 2       │
└────┴──────────┘              └────┴──────┴─────────┘

笛卡尔积（3 × 3 = 9 行）：
┌────┬──────────┬────┬──────┬─────────┐
│d.id│ d.name   │e.id│e.name│ dept_id │
├────┼──────────┼────┼──────┼─────────┤
│ 1  │ 技术部   │ 1  │ 张三 │ 1       │  ← 有意义：d.id = e.dept_id
│ 1  │ 技术部   │ 2  │ 李四 │ 1       │  ← 有意义
│ 1  │ 技术部   │ 3  │ 王五 │ 2       │  ← 无意义：技术部 id≠市场部员工的 dept_id
│ 2  │ 市场部   │ 1  │ 张三 │ 1       │  ← 无意义
│ 2  │ 市场部   │ 2  │ 李四 │ 1       │  ← 无意义
│ 2  │ 市场部   │ 3  │ 王五 │ 2       │  ← 有意义
│ 3  │ 财务部   │ 1  │ 张三 │ 1       │  ← 无意义
│ 3  │ 财务部   │ 2  │ 李四 │ 1       │  ← 无意义
│ 3  │ 财务部   │ 3  │ 王五 │ 2       │  ← 无意义
└────┴──────────┴────┴──────┴─────────┘

-- 显式写法（推荐）
select e.name as 员工, d.name as 部门
from employees e
inner join departments d on e.dept_id = d.id;

-- 隐式写法（不推荐，原因见下方）
select e.name as 员工, d.name as 部门
from employees e, departments d
where e.dept_id = d.id;

departments                       employees
┌────┬──────────┐               ┌────┬──────┬─────────┐
│ id │ name     │               │ id │ name │ dept_id │
├────┼──────────┤               ├────┼──────┼─────────┤
│ 1  │ 技术部   │               │ 1  │ 张三 │ 1       │
│ 2  │ 市场部   │               │ 2  │ 李四 │ 1       │
│ 3  │ 财务部   │               │ 3  │ 王五 │ 2       │
└────┴──────────┘               └────┴──────┴─────────┘

inner join 结果（只保留匹配行，取交集）：
┌────────┬──────────┬────────┬────────┐
│ dept.id│ dept.name│ emp.id │ emp.name│
├────────┼──────────┼────────┼────────┤
│   1    │ 技术部   │   1    │ 张三   │  ← d.id=1 = e.dept_id=1 ✓ 匹配
│   1    │ 技术部   │   2    │ 李四   │  ← d.id=1 = e.dept_id=1 ✓ 匹配
│   2    │ 市场部   │   3    │ 王五   │  ← d.id=2 = e.dept_id=2 ✓ 匹配
└────────┴──────────┴────────┴────────┘
← 财务部(d.id=3)：employees 中无 dept_id=3 → 不出现
← 若 employees 中有 dept_id=null 的行：null 无法匹配任何值 → 不出现

-- 列出所有部门及其员工人数（包括没有员工的部门）
select d.name, count(e.id) as 员工数
from departments d
left join employees e on d.id = e.dept_id
group by d.id, d.name;

departments (左)                employees (右)
┌────┬──────────┐              ┌────┬──────┬─────────┐
│ id │ name     │              │ id │ name │ dept_id │
├────┼──────────┤              ├────┼──────┼─────────┤
│ 1  │ 技术部   │              │ 1  │ 张三 │ 1       │
│ 2  │ 市场部   │              │ 2  │ 李四 │ 1       │
│ 3  │ 财务部   │              │ 3  │ 王五 │ 2       │
└────┴──────────┘              └────┴──────┴─────────┘

left join 结果：
┌────────┬──────────┬────────┬────────┐
│ dept.id│ dept.name│ emp.id │ emp.name│
├────────┼──────────┼────────┼────────┤
│   1    │ 技术部   │   1    │ 张三   │  ← 匹配成功，拼接
│   1    │ 技术部   │   2    │ 李四   │  ← 匹配成功，拼接
│   2    │ 市场部   │   3    │ 王五   │  ← 匹配成功，拼接
│   3    │ 财务部   │  null  │ null   │  ← 财务部无员工，右表全填 null
└────────┴──────────┴────────┴────────┘

-- 查询所有部门及其员工（包括没有员工的部门）
-- 写法1：right join
select e.name as 员工, d.name as 部门
from employees e
right join departments d on e.dept_id = d.id;

-- 写法2：等价 left join（推荐用这种，语义更直观）
select e.name as 员工, d.name as 部门
from departments d
left join employees e on d.id = e.dept_id;

employees (左)                   departments (右)
┌────┬──────┬─────────┐         ┌────┬──────────┐
│ id │ name │ dept_id │         │ id │ name     │
├────┼──────┼─────────┤         ├────┼──────────┤
│ 1  │ 张三 │ 1       │         │ 1  │ 技术部   │
│ 2  │ 李四 │ 1       │         │ 2  │ 市场部   │
│ 3  │ 王五 │ 2       │         │ 3  │ 财务部   │
└────┴──────┴─────────┘         └────┴──────────┘

right join 结果（等价于 left join 调换表顺序）：
┌────────┬────────┬──────────┬──────────┐
│ emp.id │emp.name│ dept.id  │ dept.name│
├────────┼────────┼──────────┼──────────┤
│   1    │ 张三   │    1     │ 技术部   │  ← 匹配成功，拼接
│   2    │ 李四   │    1     │ 技术部   │  ← 匹配成功，拼接
│   3    │ 王五   │    2     │ 市场部   │  ← 匹配成功，拼接
│  null  │ null   │    3     │ 财务部   │  ← 财务部无员工，左表全填 null
└────────┴────────┴──────────┴──────────┘

left join 结果（from departments left join employees）：
┌──────────┬──────────┬────────┬────────┐
│ dept.id  │ dept.name│ emp.id │emp.name│
├──────────┼──────────┼────────┼────────┤
│    1     │ 技术部   │   1    │ 张三   │
│    1     │ 技术部   │   2    │ 李四   │
│    2     │ 市场部   │   3    │ 王五   │
│    3     │ 财务部   │  null  │ null   │
└──────────┴──────────┴────────┴────────┘
← 列顺序不同，但信息完全等价

-- "查询薪资高于公司平均薪资的员工"
select name, salary from employees
where salary > (select avg(salary) from employees);

-- "查询有员工的部门"
select name from departments
where id in (
    select distinct dept_id from employees where dept_id is not null
);

-- "薪资高于技术部任意一人的员工"（只要比最低的那个高就行）
select name, salary from employees
where salary > any (
    select salary from employees where dept_id = 1
);

-- "薪资高于技术部所有人的员工"（比最高的还高）
select name, salary from employees
where salary > all (
    select salary from employees where dept_id = 1
);

-- "查询和'张三'同部门且同薪资的员工"
select name from employees
where (dept_id, salary) = (
    select dept_id, salary from employees where name = '张三'
);
-- 等价于 where dept_id = (...) and salary = (...)，但一行搞定

-- "列出所有部门，每个部门后面显示该部门的员工人数"
select d.name as 部门,
       (select count(*) from employees e where e.dept_id = d.id) as 员工数
from departments d;

departments 表                    select 后的子查询为每行附加计算结果：
┌────┬──────────┐                 ┌──────────┬────────┐
│ id │ name     │                 │ 部门     │ 员工数 │
├────┼──────────┤                 ├──────────┼────────┤
│ 1  │ 技术部   │  → 子查 count  │ 技术部   │   3    │
│ 2  │ 市场部   │  → 子查 count  │ 市场部   │   2    │
│ 3  │ 财务部   │  → 子查 count  │ 财务部   │   2    │
│ 4  │ 人事部   │  → 子查 count  │ 人事部   │   0    │
└────┴──────────┘                 └──────────┴────────┘

-- "从各部门的平均薪资统计中，筛选出平均薪资 > 12000 的部门"
select dept_name, 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.id, d.name
) as dept_stats          -- ← 必须给临时表起别名！
where avg_salary > 12000;

内层结果（dept_stats）：          外层 where 筛选后：
┌───────────┬────────────┐       ┌───────────┬────────────┐
│ dept_name │ avg_salary │       │ dept_name │ avg_salary │
├───────────┼────────────┤       ├───────────┼────────────┤
│ 技术部    │   17666    │  ✓    │ 技术部    │   17666    │
│ 市场部    │   11500    │  ✗    │ 财务部    │   13000    │
│ 财务部    │   13000    │  ✓    └───────────┴────────────┘
└───────────┴────────────┘

-- "平均薪资高于全公司总平均的部门"
select dept_id, avg(salary) as dept_avg
from employees
group by dept_id
having avg(salary) > (select avg(salary) from employees);

-- "查询技术部的所有员工"
select name from employees
where dept_id = (select id from departments where name = '技术部');

执行过程：
┌─────────────────────────────────────────────┐
│ ① 子查询先跑：select id from departments    │
│    where name = '技术部' → 结果：1           │
│                                             │
│ ② 用 1 替代子查询：                         │
│    where dept_id = 1                        │
│                                             │
│ ③ 跑外层查询，返回结果                       │
└─────────────────────────────────────────────┘

-- "查询薪资高于自己部门平均薪资的员工"
select name, salary
from employees e
where salary > (
    select avg(salary) from employees where dept_id = e.dept_id
);
                                       ↑ 引用了外层 e.dept_id

执行过程（类比双层循环）：
┌─────────────────────────────────────────────┐
│ 外层取第1行：张三（dept_id=1, salary=15000） │
│   → 子查询：avg(salary) where dept_id = 1   │
│   → 结果：17666                             │
│   → 15000 > 17666？ → false → 丢弃           │
│                                             │
│ 外层取第2行：李四（dept_id=2, salary=12000） │
│   → 子查询：avg(salary) where dept_id = 2   │
│   → 结果：11500                             │
│   → 12000 > 11500？ → true → 保留            │
│                                             │
│ 外层取第3行：王五（dept_id=1, salary=18000） │
│   → 子查询：avg(salary) where dept_id = 1   │
│   → 结果：17666                             │
│   → 18000 > 17666？ → true → 保留            │
│  ...                                        │
└─────────────────────────────────────────────┘

-- "查询至少有一名员工的部门"
select name from departments d
where exists (
    select 1 from employees e where e.dept_id = d.id
);

-- "查询没有员工的部门"，两种写法对比
-- 写法a：not in（有 null 风险）
select name from departments
where id not in (select dept_id from employees where dept_id is not null);
                                          -- ↑ 必须加 is not null 防 null

-- 写法b：not exists（天然安全，推荐）
select name from departments d
where not exists (
    select 1 from employees e where e.dept_id = d.id
);

-- 把北京和上海员工的名单纵向合并为一张表
select name, phone, city from employees where city = '北京'
union
select name, phone, city from employees where city = '上海';

-- 常见误区：order by 不能写在中间的 select 里
select name, salary from employees where dept_id = 1
union all
select name, salary from employees where dept_id = 2
order by salary desc;   -- ✅ 对整个合并结果排序