DROP TABLE IF EXISTS emp01;

DROP TABLE IF EXISTS emp02;

DROP TABLE IF EXISTS emp03;

DROP TABLE IF EXISTS emp11;

DROP TABLE IF EXISTS dept02;

DROP TABLE IF EXISTS dept11;

DROP TABLE IF EXISTS dept;

DROP TABLE IF EXISTS emp;

DROP TABLE IF EXISTS job;

DROP TABLE IF EXISTS salarygrade;

-- 部門表

CREATE TABLE dept (

? id INT PRIMARY KEY PRIMARY KEY, -- 部門id

? dname 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, -- 員工id

? ename VARCHAR(50), -- 員工姓名

? job_id INT, -- 職務id

? mgr 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, '經(jīng)理', '管理部門員工'),

(3, '銷售員', '向客人推銷產(chǎn)品'),

(4, '文員', '使用辦公軟件');

-- 添加員工

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,'關羽',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.員工編號，員工姓名，工資 信息在emp 員工表中

2.職務名稱，職務描述，信息在job職務表中

3.job職務表和emp員工表 是一對多的關系 emp.job_id =job.id

*/

--? 1.查詢所有員工信息。查詢員工編號，員工姓名，工資，職務名稱，職務描述

-- 隱式內(nèi)連接

select emp.id,emp.ename,emp.salary,job.jname,job.description from emp,job where emp.job_id=job.id order by id asc;

-- 顯示內(nèi)連接

select emp.id,emp.ename,emp.salary,job.jname,job.description from emp inner join job on emp.job_id=job.id order by id asc;

select* from emp;

select*from job;

-- 2.查詢員工編號，員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置

?/*

1.員工編號，員工姓名，工資 信息在emp 員工表中

2.職務名稱，職務描述，信息在job職務表中

3.job職務表和emp員工表 是一對多的關系 emp.job_id =job.id

4.部門名稱，部門位置 來自于 部門表dept

5.dept和emp一對多關系dept.id=emp.dept_id

*/

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description,

? dept.dname,

? dept.loc

FROM

emp,

job,

dept

WHERE

emp.job_id = job.id?

and dept.id=emp.dept_id

ORDER BY

id ASC;

-- 顯示連鏈接

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description,

dept.dname,

dept.loc?

FROM

emp

INNER JOIN?

job?

ON?

emp.job_id = job.id

INNER JOIN?

dept?

ON

dept.id=emp.dept_id

ORDER BY

id ASC;

-- 3.查詢員工姓名，工資，工資等級

?/*

1.員工編號，員工姓名，工資 信息在emp 員工表中

2.工資等級 信息在salarygrade 職務表中

3.emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalary

*/

SELECT

emp.ename,

emp.salary,

t2.grade?

FROM

emp,

salarygrade t2?

WHERE

emp.salary >= t2.losalary?

AND emp.salary <= t2.hisalary;

-- 4.查詢員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置，工資等級

SELECT

emp.ename,

emp.salary,

t2.grade?

FROM

emp,

salarygrade t2?

WHERE

emp.salary >= t2.losalary?

AND emp.salary <= t2.hisalary;

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description,

dept.dname,

dept.loc?

FROM

emp

INNER JOIN job on emp.job_id=job.id;

INNER JOIN job dept.id = emp.dept_id;

INNER JOIN salarygrade t2 on emp.salary between t2.salary and t2.hisalary;

?

-- 5.查詢出部門編號、部門名稱、部門位置、部門人數(shù)

/*

1.部門編號,部門名稱，部門位置，來自于部門dept表

2.部門人數(shù)；在emp表中 按照dept_id進行分組，然后count(*)統(tǒng)計數(shù)量

3.使用子查詢，讓部門表和分組后的表進行內(nèi)連接

*/

select*from dept;

select dept_id,count(*)from emp group by dept_id;

select*from dept,(select dept_id,count(*)from emp group by dept_id) t1 where dept.id=t1.dept_id;