CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司,接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
CREATE TABLE emp (
id INT PRIMARY KEY,
ename VARCHAR(50),
job_id INT,
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)
);
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);
CREATE TABLE salarygrade (
grade INT PRIMARY KEY,
losalary INT,
hisalary INT
);
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
SELECT
t1.id,
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`
FROM
emp t1,
job t2
WHERE
t1.`job_id`=t2.`id`;
SELECT
t1.`id`,
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`
FROM
emp t1,
job t2,
dept t3
WHERE
t1.`job_id`=t2.`id` AND t1.`dept_id`=t3.`id`;
SELECT
emp.`ename`,
emp.`salary`,
salarygrade.`grade`
FROM
emp,
salarygrade
WHERE
emp.`salary` BETWEEN salarygrade.`losalary` AND salarygrade.`hisalary`;
SELECT
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`,
t4.`grade`
FROM
emp t1,
job t2,
dept t3,
salarygrade t4
WHERE
t1.`job_id`=t2.`id` AND
t1.`dept_id`=t3.`id` AND
t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
SELECT
t1.id,
t1.dname,
t1.loc,
COUNT(t1.id)
FROM (
SELECT
dept.`id`,
dept.`dname`,
dept.`loc`
FROM
dept,
emp
WHERE
emp.`dept_id`=dept.`id`) t1 GROUP BY t1.dname;
SELECT
t1.`ename` "员工",
t2.`ename` "老板"
FROM
emp t1
LEFT JOIN
emp t2
ON
t1.`mgr`=t2.`id`;