/*
-- 創(chuàng)建部門表
create table tb_dept(
id int primary key auto_increment,
name varchar(20)
);
insert into tb_dept (name) values ('開(kāi)發(fā)部'),('市場(chǎng)部'),('財(cái)務(wù)部');
--創(chuàng)建員工表
create table tb_emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性別
salary double, -- 工資
join_date date, -- 入職日期
dept_id int,
foreign key (dept_id) references tb_dept(id) -- 外鍵诈嘿，關(guān)聯(lián)部門表(部門表的主鍵)
);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('孫悟空','男',7200,'2013-02-24',1);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('豬八戒','男',3600,'2010-12-02',2);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into tb_emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);
*/
###############################多表查詢的操作########################
1.多表查詢的經(jīng)典問(wèn)題：笛卡兒積現(xiàn)象
CREATE DATABASE dlei;
直接進(jìn)行兩張表的相乘組合窃肠。
就是笛卡爾積現(xiàn)象，應(yīng)該被避免西疤，因?yàn)樗泻芏鄶?shù)據(jù)是無(wú)效的
SELECT * FROM tb_emp , tb_dept;
2.內(nèi)連接可以解決笛卡爾積現(xiàn)象迈倍，得到準(zhǔn)確的有效的結(jié)果數(shù)據(jù)
內(nèi)連接分為：顯示內(nèi)連接迎变，隱式內(nèi)鏈接蟆技，功能是一樣的熊楼，只是寫法不同而已。
a.隱式內(nèi)連接
SELECT * FROM tb_emp e , tb_dept d WHERE e.dept_id = d.id;
b.顯示內(nèi)連接 INNER JOIN ... ON 連表的條件觉啊。
SELECT * FROM tb_emp e INNER JOIN tb_dept d ON e.dept_id = d.id;
INNER可以省略不寫(這種可以采用)
SELECT * FROM tb_emp e JOIN tb_dept d ON e.dept_id = d.id;
1.查詢出唐僧的部門名稱
SELECT d.name 部門名稱 , e.name員工名稱
FROM tb_emp e JOIN tb_dept d ON e.dept_id= d.id WHERE e.name = '唐僧';
#######################外連接的操作###################################
外連接：分為左外連接（左連接）拣宏，右外連接（右連接）
1.左連接：在內(nèi)連接的基礎(chǔ)之上，保全左表的全部數(shù)據(jù),右表沒(méi)有對(duì)應(yīng)的數(shù)據(jù)默認(rèn)為null
格式：LEFT OUTER JOIN 右表 ON 左表外鍵=右表主鍵
OUTER可以省略不寫
SELECT * FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id= d.id;
2.右連接：在內(nèi)連接的基礎(chǔ)之上杠人，保全右表的全部數(shù)據(jù),左表沒(méi)有對(duì)應(yīng)的數(shù)據(jù)默認(rèn)為null
格式：RIGHT OUTER JOIN 右表 ON 左表外鍵=右表主鍵
OUTER可以省略不寫
SELECT * FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id= d.id;
在內(nèi)鏈接的基礎(chǔ)上保留左右表的全部數(shù)據(jù)
SELECT * FROM tb_emp e LEFT JOIN tb_dept d ON e.dept_id = d.id
UNION
SELECT * FROM tb_emp e RIGHT JOIN tb_dept d ON e.dept_id = d.id
############################子查詢的操作####################################
子查詢：是多表查詢方案中的一種（表連接勋乾，子查詢）
子查詢的含義是：一條SQL語(yǔ)句中又包含了一條select語(yǔ)句。
1.需求:查詢出工資最高的那個(gè)人的名字嗡善。
a.查詢出最高工資
SELECT MAX(salary) FROM tb_emp ;
b.查詢出最高工資的那個(gè)人的名字(以下SQL語(yǔ)句就是子查詢)
第一種情況：子查詢的結(jié)果是單列情況辑莫，只能用=這樣的判斷（有一個(gè)值）
SELECT NAME FROM tb_emp WHERE salary = (SELECT MAX(salary) FROM tb_emp)
--查詢工資小于平均工資的員工有哪些？
SELECT * FROM tb_emp WHERE salary < (SELECT AVG(salary) FROM tb_emp )
第二種情況：子查詢的結(jié)果是多行單列數(shù)據(jù)（有多個(gè)值）：必須用IN來(lái)實(shí)現(xiàn)判斷
SELECT 查詢字段 FROM 表 WHERE 字段 IN （子查詢）;
2.需求：查詢工資大于4000的員工罩引，來(lái)自于哪些部門的名字
a.查詢工資大于4000的員工的部門編號(hào)
SELECT dept_id FROM tb_emp WHERE salary > 4000;
b.再查詢出他們所屬的部門信息
SELECT NAME FROM tb_dept WHERE id IN(SELECT dept_id FROM tb_emp WHERE salary > 4000)
3.需求：查詢開(kāi)發(fā)部與財(cái)務(wù)部所有的員工信息
a.查詢出開(kāi)發(fā)部和財(cái)務(wù)部的編號(hào)
SELECT id FROM tb_dept WHERE NAME = '開(kāi)發(fā)部' || NAME = '財(cái)務(wù)部';
b.再查詢出這個(gè)部門下的員工信息
SELECT * FROM tb_emp WHERE dept_id IN (SELECT id FROM tb_dept WHERE NAME = '開(kāi)發(fā)部' || NAME = '財(cái)務(wù)部');
第三種情況：子查詢的結(jié)果是多行多列（查詢出來(lái)就是一個(gè)新表（虛表）各吨，直接當(dāng)表使用）
4.需求查詢出2011年以后入職的員工信息，包括部門名稱
a.查詢出2011年之后的員工信息
SELECT * FROM tb_emp WHERE join_date >= '2011-01-01'
b.把新查詢出的員工表（2011年之后的員工）與部門表左外連接
SELECT * FROM (SELECT * FROM tb_emp WHERE join_date >= '2011-01-01') new_emp LEFT JOIN tb_dept d
ON new_emp.dept_id = d.id;
多表查詢練習(xí)題
-- 部門表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部門id
dname VARCHAR(50), -- 部門名稱
loc VARCHAR(50) -- 部門位置
);
-- 添加4個(gè)部門
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'學(xué)工部','上海'),
(30,'銷售部','廣州'),
(40,'財(cái)務(wù)部','深圳');
-- 職務(wù)表袁铐，職務(wù)名稱揭蜒，職務(wù)描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4個(gè)職務(wù)
INSERT INTO job (id, jname, description) VALUES
(1, '董事長(zhǎng)', '管理整個(gè)公司，接單'),
(2, '經(jīng)理', '管理部門員工'),
(3, '銷售員', '向客人推銷產(chǎn)品'),
(4, '文員', '使用辦公軟件');
-- 員工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 員工id
ename VARCHAR(50), -- 員工姓名
job_id INT, -- 職務(wù)id
mgr INT , -- 上級(jí)領(lǐng)導(dǎo)
joindate DATE, -- 入職日期
salary DECIMAL(7,2), -- 工資
bonus DECIMAL(7,2), -- 獎(jiǎng)金
dept_id INT, -- 所在部門編號(hào)
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加員工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孫悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'盧俊義',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林沖',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'劉備',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'豬八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'羅貫中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吳用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龍',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'關(guān)羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工資等級(jí)表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY,
losalary INT,
hisalary INT
);
-- 添加5個(gè)工資等級(jí)
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
1.查詢所有員工信息昭躺。顯示員工編號(hào)忌锯，員工姓名，工資领炫，職務(wù)名稱偶垮，職務(wù)描述
-- a.明確表：?jiǎn)T工表，職務(wù)表
-- b.連接表：左連接
SELECT e.id , e.ename , e.salary, j.jname , j.description
FROM emp e LEFT JOIN job j ON e.job_id = j.id;
2.查詢所有員工信息。顯示員工編號(hào)似舵，員工姓名脚猾，工資，職務(wù)名稱砚哗，職務(wù)描述龙助，部門名稱，部門位置
-- a.明確表：?jiǎn)T工表蛛芥，職務(wù)表,部門表
-- b.連接表：左連接
SELECT e.id, e.ename , e.salary , j.jname, j.description , d.dname, d.loc
FROM emp e LEFT JOIN job j ON e.job_id= j.id LEFT JOIN dept d
ON e.dept_id= d.id
不考慮員工沒(méi)有部門和職位的人提鸟！
--SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d ON e.job_id=j.id AND e.dept_id=d.id;
3.查詢所有員工信息。顯示員工姓名仅淑，工資称勋，職務(wù)名稱，職務(wù)描述涯竟，部門名稱赡鲜，部門位置，工資等級(jí)
-- a.明確表：?jiǎn)T工表庐船，職務(wù)表, 部門表 ,工資等級(jí)表
-- b.連接表：左連接
-- 最通用做法：
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc, sg.grade , sg.losalary,sg.hisalary FROM emp e LEFT JOIN job j ON e.job_id=j.id
LEFT JOIN dept d ON e.dept_id= d.id
JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary
4.查詢經(jīng)理的信息银酬。顯示員工姓名，工資筐钟，職務(wù)名稱揩瞪，職務(wù)描述，部門名稱篓冲，部門位置壮韭，工資等級(jí)
SELECT * FROM emp e JOIN JOB j ON e.job_id = j.id AND j.jname='經(jīng)理'
JOIN dept d ON e.dept_id= d.id
JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary
5.查詢出部門編號(hào)、部門名稱纹因、部門位置、部門人數(shù)
-- a.簡(jiǎn)單粗暴連接表：dept , emp
-- 1.直接到員工表根據(jù)部門id分組查詢
SELECT COUNT() , dept_id FROM emp GROUP BY dept_id;
-- 2.拿著部門員工的信息數(shù)據(jù)與部門表實(shí)現(xiàn)右連接
SELECT * FROM (SELECT COUNT() , dept_id FROM emp GROUP BY dept_id) new_emp_nums RIGHT JOIN
dept d ON new_emp_nums.dept_id = d.id;
