創(chuàng)建部門表和員工表
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初始值可在創(chuàng)建表的時(shí)候定義 -
zerofill補(bǔ)0 - 關(guān)聯(lián)外鍵的兩個(gè)列名類型必須一致，否則會(huì)報(bào)錯(cuò)
插入數(shù)據(jù)
insert into s_dept(dept_name, is_valid) values ('辦公室', 'Y'), ('技術(shù)部', '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 員工號(hào)矫废，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;
運(yùn)行結(jié)果：
第一題結(jié)果
心得：
- 為什么第三個(gè)不是王五呢岂膳？
答：沒有指定order by 的結(jié)果集磺樱，不能保證結(jié)果的順序，查詢優(yōu)化器按照能盡快出結(jié)果的方式去查詢婆咸，所以結(jié)果順序未知 - MySQL中
select語句中子句的執(zhí)行順序：
-
先連接
from后數(shù)據(jù)源竹捉； 如果有join，則先執(zhí)行on后的條件尚骄，滿足條件再連接數(shù)據(jù)源 -
執(zhí)行
where條件; 按指定條件查詢 -
執(zhí)行
group by; 分組 常與聚合函數(shù)一起使用 -
執(zhí)行
having; 有group by才會(huì)有having块差，對(duì)group by的結(jié)果篩選 -
執(zhí)行
order by; 按照指定字段排序 select輸出結(jié)果-
limit； 限制結(jié)果集
重申：having子句可以讓我們篩選成組后的各種數(shù)據(jù)倔丈，where子句在聚合前先篩選記錄憨闰，也就是說作用在group by和having子句前。而 having子句在聚合后對(duì)組記錄進(jìn)行篩選
第二題
- 請查詢員工號(hào)最大的員工
展示字段：staff_no staff_name dept_name
思路1:
員工表左連接部門表需五，on同上鹉动，order by 員工號(hào)，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 查詢篩選 選擇最大員工號(hào) 使用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的條件可以是某字段 等于 子查詢語句的查詢結(jié)果
第三題
- 前臺(tái)頁面查看每個(gè)部門有多少人，而且按照人數(shù)降序排序蜜氨，人數(shù)為0的部門不顯示
展示字段：dept_no dept_name 人數(shù)
思路：拆分
- 將員工表按照部門編碼
group by統(tǒng)計(jì)數(shù)量得到臨時(shí)表 temp:部門編碼械筛、人數(shù) - 將部門表和temp表，根據(jù)部門編碼左連接
left join,得到：部門編碼飒炎、部門名埋哟、人數(shù) - 降序排列，所以需要
order by... desc - 題目要求人數(shù)為0的時(shí)候不顯示郎汪，這時(shí)候
a. 一個(gè)思路是在order by后使用having, 但是having是對(duì)分組后的結(jié)果進(jìn)行過濾的條件限制赤赊，主查詢中并沒有group by闯狱， 所以不能使用
b.另一個(gè)思路是where, 是查詢數(shù)據(jù)的過濾條件，where 子句執(zhí)行順序早于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;
第三題
