部门表

CREATE TABLE dept( deptno INT PRIMARY KEY, '部门编号' dname VARCHAR(14), '部门名称' loc VARCHAR(13) '部门地址' ); #部门表数据 INSERT INTO dept VALUES (10, '财务部', '北京'), (20, '市场部', '上海'), (30, '销售部', '广州'), (40, '运营部', '深圳');

员工表

CREATE TABLE emp( empno INT PRIMARY KEY, #员工编号 ename VARCHAR(10), #员工姓名 job VARCHAR(20), #员工工作 mgr INT, #员工直属领导编号 hiredate DATE, #入职时间 sal DOUBLE, #工资 comm DOUBLE, #奖金 deptno INT #对应dept表的外键 );

添加 部门 和 员工 之间的主外键关系

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY emp(deptno) REFERENCES dept (deptno);

INSERT INTO emp VALUES(7369, 'smith', '保洁工作', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO emp VALUES(7499, 'allen', '销售工作', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO emp VALUES(7521, 'ward', '销售工作', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO emp VALUES(7566, 'jones', '管理工作', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO emp VALUES(7654, 'martin', '销售工作', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO emp VALUES(7698, 'blake', '管理工作', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO emp VALUES(7782, 'clark', '管理工作', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO emp VALUES(7788, 'scott', '策划工作', 7566, '1987-07-03', 3000, NULL, 20); INSERT INTO emp VALUES(7839, 'king', '大BOSS', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO emp VALUES(7844, 'turner', '销售工作', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO emp VALUES(7876, 'adams', '保洁工作', 7788, '1987-07-13', 1100, NULL, 20); INSERT INTO emp VALUES(7900, 'james', '保洁工作', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO emp VALUES(7902, 'ford', '策划工作', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO emp VALUES(7934, 'miller', '保洁工作', 7782, '1981-01-23', 1300, NULL, 10);

工资等级表

CREATE TABLE salgrade( grade INT, #等级 losal DOUBLE, #最低工资 hisal DOUBLE #最高工资 );

INSERT INTO salgrade VALUES (1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999);

查询练习

1. 返回员工姓名和该员工领导的姓名。

SELECT e.ename, m.ename
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;

2. 返回员工姓名及其所在的部门名称。

SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

3. 返回从事clerk工作的员工姓名和所在部门名称。

SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.job = 'clerk';

4. 返回销售部(sales)所有员工的姓名。

SELECT e.ename
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.dname = '销售部';

5. 返回部门号、部门名、部门所在位置及其每个部门的员工总数。

SELECT d.deptno, d.dname, d.loc, COUNT(e.empno) AS total
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc;

6. 返回员工的姓名、所在部门名及其工资。

SELECT e.ename, d.dname, e.sal
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

7. 返回员工的详细信息。(包括部门名)

SELECT e.*, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

8. 返回员工工作及其从事此工作的最低工资。

SELECT e.job, MIN(e.sal) AS min_sal
FROM emp e
GROUP BY e.job;

9. 计算出员工的年薪，并且以年薪排序。

SELECT e.*, (e.sal + IFNULL(e.comm, 0)) * 12 AS annual_salary
FROM emp e
ORDER BY annual_salary DESC;

10. 返回工资处于第四级别的员工的姓名。

SELECT e.ename
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE s.grade = 4;