多表查詢

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-FBdzXkoQ-1659581225088)(C:\Users\L00589~1\AppData\Local\Temp\1659337934641.png)]

左外連接&右外連接

-- 查詢emp表所有數據和對應的部門信息
select * from emp left join dept on emp.dep_id = dept.did;-- 右外連接
-- 查詢 dept 表所有數據和對應的員工信息select * from emp right join dept on emp.dep_id = dept.did;

內連接

select * from emp where salary > (select salary from emp where name = '豬八戒')-- 多行單列子查詢
-- 查詢 '財務部' 和 '市場部' 所有員工的信息
select * from emp where dep_id in (select did from dept where dname = '財務部' or dname = '市場部');-- 查詢入職日期是 '2011-11-11' 之后的員工信息和部門信息
select * from (select * from emp where join_date > '2011-11-11') t1, dept where t1.dep_id = dept.did;

查詢練習

DROP TABLE
IFEXISTS emp;
DROP TABLE
IFEXISTS dept;
DROP TABLE
IFEXISTS job;
DROP TABLE
IFEXISTS salarygrade;-- 部門表
CREATE TABLE dept ( id INT PRIMARY KEY, -- 部門iddname VARCHAR ( 50 ), -- 部門名稱loc VARCHAR ( 50 ) -- 部門所在地
) -- 職位表, 職務名稱, 職務描述
CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR ( 20 ), description VARCHAR ( 50 ) );-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY,-- 員工 idename VARCHAR ( 50 ),-- 員工姓名job_id INT,-- 職務 idmgr INT,-- 上級領導joindate DATE,-- 入職日期salary DECIMAL ( 7, 2 ),-- 工資bonus DECIMAL ( 7, 2 ),-- 獎金dept_id INT,-- 所在部門編號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 ) 
);-- 工資等級表
CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 級別losalary INT, -- 最低工資hisalary INT -- 最高工資
);-- 添加4個部門
INSERT INTO dept ( id, dname, loc )
VALUES( 10, '教研部', '北京' ),( 20, '學工部', '上海' ),( 30, '銷售部', '廣州' ),( 40, '財務部', '深圳' );-- 添加4個職務
INSERT INTO job ( id, jname, description )
VALUES( 1, '董事長', '管理整個公司，接單' ),( 2, '經理', '管理部門員工' ),( 3, '銷售員', '向客人推銷產品' ),( 4, '文員', '使用辦公軟件' );-- 添加員工
INSERT INTO emp ( id, ename, job_id, mgr, joindate, salary, bonus, dept_id )
VALUES( 1001, '孫悟空', 4, 1004, '2000-12-17', '8000.0', 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', '5000.00', 30 ),( 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, '關羽', 4, 1007, '2002-01-23', '13000.00', NULL, 10 );-- 添加5個工資等級
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES( 1, 7000, 12000 ),( 2, 12010, 14000 ),( 3, 14010, 20000 ),( 4, 20010, 30000 ),( 5, 30010, 99990 );-- 1、查詢所有員工信息,查詢員工編號,員工姓名,工資,職務名稱,職務描述
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description
FROMemp,job 
WHEREemp.job_id = job.id
ORDER BY(emp.id);-- 2、查詢員工編號,員工姓名,工資,職務名稱,職務描述,部門名稱,部門位置
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
FROM emp,job,dept
WHERE (emp.job_id = job.id AND emp.dept_id = dept.id)
ORDER BY (emp.id);-- 3、查詢員工姓名,工資,工資等級
SELECT emp.ename,emp.salary,t2.grade
FROMemp, salarygrade t2
WHERE emp.salary >= t2.losalary AND emp.salary <= t2.hisalary;-- 4、查詢員工姓名,工資,職務名稱,職務描述,部門名稱,部門位置,工資等級
SELECT emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,t3.grade
FROMemp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.id
INNER JOIN salarygrade t3 ON emp.salary BETWEEN t3.losalary AND t3.hisalary;-- 5、查詢出部門編號、部門名稱、部門位置、部門人數
SELECTdept.id,dept.dname,dept.loc,count(*) count
FROM emp
INNER JOIN dept ON emp.dept_id = dept.id
GROUP BY(dept_id);