mysql 部门领导表_MySQL 练习笔记 | 员工部门表实例

创建部门表和员工表

create table if not exists s_dept(

dept_no int(3) unsigned zerofill primary key auto_increment,

dept_name varchar(32) not null,

is_valid char(2) not null

)engine = innodb default charset = utf8;

create table if not exists s_staff(

staff_no int(6) unsigned primary key auto_increment,

staff_name varchar(32) not null,

is_valid char(2) not null,

dept_no int(3) unsigned zerofill,

foreign key(dept_no) references s_dept(dept_no)

)engine = innodb auto_increment = 100001 default charset = utf8;

心得：

auto_increment 初始值可在创建表的时候定义

zerofill 补0

关联外键的两个列名类型必须一致，否则会报错

插入数据

insert into s_dept(dept_name, is_valid) values ('办公室', 'Y'), ('技术部', 'N'), ('人力资源部', 'Y'), ('销售部', 'Y');

insert into s_staff(staff_name, is_valid, dept_no) values ('张三', 'Y', 001), ('李四', 'N', 002), ('王五', 'Y', 002), ('黄三', 'Y', 001), ('陈二', 'Y', 001);

得到部门表和员工表如下：

s_dept (部门表)

s_staff (员工表)

第一题

请查询员工表前三条有效记录

展示字段：staff_no staff_name dept_name

思路：

员工表和部门表左连接，on dept_no相同，where 有效，order by 员工号，limit 3

select a.staff_no, a.staff_name, b.dept_name from s_staff as a

left join s_dept as b

on a.dept_no = b.dept_no

where a.is_valid ='Y' and b.is_valid = 'Y'

order by a.staff_no

limit 3;

运行结果：

第一题结果

心得：

为什么第三个不是王五呢？

答：没有指定order by 的结果集，不能保证结果的顺序，查询优化器按照能尽快出结果的方式去查询，所以结果顺序未知

MySQL中select语句中子句的执行顺序：

先连接from后数据源； 如果有join，则先执行on后的条件，满足条件再连接数据源

执行where条件; 按指定条件查询

执行group by; 分组 常与聚合函数一起使用

执行having; 有group by 才会有having，对group by 的结果筛选

执行order by; 按照指定字段排序

select输出结果

limit； 限制结果集

重申：having子句可以让我们筛选成组后的各种数据，where子句在聚合前先筛选记录，也就是说作用在group by和having子句前。而 having子句在聚合后对组记录进行筛选

第二题

请查询员工号最大的员工

展示字段：staff_no staff_name dept_name

思路1:

员工表左连接部门表，on同上，order by 员工号，limit1

select a.staff_no, a.staff_name, b.dept_name from s_staff as a

left join s_dept as b

on a.dept_no = b.dept_no

order by a.staff_no

limit 1;

思路2:

员工表左连接部门表，on同上，where 查询筛选 选择最大员工号 使用max

select a.staff_no, a.staff_name, b.dept_name from s_staff as a

left join s_dept as b

on a.dept_no = b.dept_no

where a.staff_no = (select max(c.staff_no) from s_staff as c);

第二题

心得：

where的条件可以是某字段 等于 子查询语句的查询结果

第三题

前台页面查看每个部门有多少人，而且按照人数降序排序，人数为0的部门不显示

展示字段：dept_no dept_name 人数

思路：拆分

将员工表按照部门编码 group by统计数量得到临时表 temp:部门编码、人数

将部门表和temp表，根据部门编码左连接 left join,得到：部门编码、部门名、人数

降序排列，所以需要order by... desc

题目要求人数为0的时候不显示，这时候

a. 一个思路是在 order by 后使用 having, 但是 having 是对分组后的结果进行过滤的条件限制，主查询中并没有 group by， 所以不能使用

b.另一个思路是 where, 是查询数据的过滤条件，where 子句执行顺序早于order by，所以放在 order by 之前

select a.dept_no, a.dept_name, temp.dept_num from s_dept as a

left join

(select b.dept_no, count(b.dept_no) as dept_num from s_staff as b group by b.dept_no) as temp

on a.dept_no = temp.dept_no

where temp.dept_num > 0

order by temp.dept_num desc;

第三题