MySQL③

MySQL③

准备工作

假设有以下两张表：

— 部门表 departments
create table departments (
dept_id int primary key,
dept_name varchar(50) not null,
location varchar(100)
);

— 员工表 employees
create table employees (
emp_id int primary key,
emp_name varchar(50) not null,
dept_id int,
salary decimal(10, 2),
hire_date date,
bonus decimal(10, 2),
foreign key (dept_id) references departments(dept_id)
);

— 部门数据
insert into departments (dept_id, dept_name, location) values (10, '技术部', '北京');
insert into departments (dept_id, dept_name, location) values (20, '市场部', '上海');
insert into departments (dept_id, dept_name, location) values (30, '财务部', '广州');
insert into departments (dept_id, dept_name, location) values (40, '人事部', '深圳');
insert into departments (dept_id, dept_name, location) values (50, '运营部', '杭州');

— 员工数据（20条）
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1001, '张伟', 10, 12000.00, '2020-03-15', 2000.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1002, '李娜', 10, 9500.00, '2021-06-20', 1500.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1003, '王强', 10, 8500.00, '2022-01-10', 1000.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1004, '刘洋', 10, 7800.00, '2022-08-05', 800.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1005, '陈敏', 10, 6800.00, '2023-03-01', 500.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1006, '赵磊', 20, 11000.00, '2019-11-01', 2500.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1007, '孙悦', 20, 9000.00, '2020-09-15', 1800.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1008, '周慧', 20, 8200.00, '2021-12-20', 1200.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1009, '吴迪', 20, 7500.00, '2022-07-11', 900.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1010, '郑爽', 20, 6500.00, '2023-02-14', 400.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1011, '钱华', 30, 10000.00, '2019-08-20', 2200.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1012, '冯静', 30, 8800.00, '2020-12-01', 1600.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1013, '蒋明', 30, 8000.00, '2021-10-15', 1100.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1014, '韩雪', 30, 7200.00, '2022-09-20', 700.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1015, '沈鹏', 30, 6200.00, '2023-06-01', null);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1016, '魏星', 40, 9500.00, '2020-05-10', 1400.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1017, '褚丹', 40, 7800.00, '2021-08-25', 1000.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1018, '卫东', 40, 7000.00, '2022-11-30', null);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1019, '任小强', 50, 8800.00, '2020-07-07', 1300.00);
insert into employees (emp_id, emp_name, dept_id, salary, hire_date, bonus) values (1020, '姚丽', 50, 7600.00, '2021-04-18', 800.00);

sql练习题

1. 查询所有员工的姓名和工资。

2. 查询所有员工信息，按照工资从高到低排序。

3. 查询工资大于8000的所有员工的姓名和工资。

4. 查询部门编号为10或20的所有员工的姓名和部门编号。

5. 查询工资在5000到10000之间（包含边界）的员工姓名和工资。

6. 查询姓名以"王"开头的所有员工信息。

7. 查询姓名中包含"小"字的所有员工姓名。

8. 查询姓名为两个字的员工姓名和工资。

9. 查询奖金（bonus）为空的员工姓名和部门编号。

10. 查询奖金不为空的员工姓名和奖金金额。

11. 查询部门编号为30且工资大于7000的员工姓名、工资和部门编号。

12. 查询每个部门的员工人数和平均工资。

13. 查询每个部门的最高工资。

14. 查询员工人数大于3的部门编号和员工人数。

15. 查询平均工资大于8000的部门编号和平均工资。

16. 查询工资最高的前3名员工的姓名和工资。

17. 查询每个员工及其所在部门的名称（使用内连接）。

18. 查询所有部门及其员工信息，要求显示所有部门（即使没有员工），使用左外连接。

19. 查询每个部门的名称、员工人数和平均工资。

20. 查询工资高于公司平均工资的员工姓名、工资和部门名称（使用子查询）。

#1
select emp_name,salary
from employees;

#2
select *
from employees
order by salary desc ;

#3
select emp_name,salary
from employees
where salary > 8000;

#4
/*
select emp_name,d.dept_id
from employees
join departments d on employees.dept_id = d.dept_id
where d.dept_id = 10 or 20;

IN = "等于其中某一个"（离散集合）
BETWEEN = "在这个区间内"（连续范围）
*/

select emp_name, dept_id
from employees
where dept_id in (10, 20);

#5
select emp_name,salary
from employees
where salary between 5000 and 10000;

#6
select *
from employees
where emp_name like '王%';

#7
select emp_name
from employees
where emp_name like '%小%';

#8
select emp_name,salary
from employees
where emp_name like '__';

#9
select emp_name,dept_id
from employees
where bonus is null ;

#10
select emp_name,bonus
from employees
where bonus is not null ;

#11
select emp_name,salary,dept_id
from employees
where dept_id = 30 and salary > 7000;

#12
select
dept_id,
count(*),
avg(salary)
from employees
group by dept_id;

#13
select
dept_id,
max(salary)
from employees
group by dept_id;

#14
/*
select
dept_id,
count(*) as c
from employees
where c > 3 ;
WHERE 子句中不能引用 SELECT 中定义的别名。

WHERE 的执行顺序在 SELECT 之前，此时别名 c 还未定义，会报错。
*/
select
dept_id,
count(*) as c
from employees
group by dept_id
having c > 3 ;

#15
select
dept_id,
avg(salary) as a
from employees
group by dept_id
having a > 8000;

#16
select emp_name,salary
from employees
order by salary desc
limit 3;

#17
select emp_name,dept_name
from employees
join departments d on d.dept_id = employees.dept_id;

#18
/*
select
d.dept_id,dept_name,emp_name
from employees
left join departments d on d.dept_id = employees.dept_id;
左连接部门表应该作为主表而不是e表
*/
select
d.dept_name,
e.dept_id,
e.emp_name
from departments d
left join employees e on d.dept_id = e.dept_id;

#19
select
d.dept_name,
count(e.emp_name) as 人数,
avg(salary) as 平均工资
from employees e
join departments d on d.dept_id = e.dept_id
group by d.dept_id;

#可读性更好的19
select
d.dept_name,
count(e.emp_name) as 人数,
avg(e.salary) as 平均工资
from employees e
join departments d on d.dept_id = e.dept_id
group by d.dept_id, d.dept_name;

#20
select
emp_name,
salary,
dept_name
from employees
join departments d on d.dept_id = employees.dept_id
where salary >(
select
avg(salary)
from employees
);

错误类型：

题号错误类型
4or 条件写法不完整
7关键字写错（“王” vs “小”）
12缺少别名（小问题）
13WHERE 中引用别名 + 逻辑错误
14WHERE 中引用别名（重复错）
18主表选反了（左连接方向错误）
核心知识点回顾：

or 条件要写完整：col = 1 or col = 2

WHERE 中不能用 SELECT 定义的别名

分组后用 HAVING 筛选，不用 WHERE

左连接以哪个表为主，取决于 FROM 后面的表