部门表

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. 查找部门30中员工的详细信息。

SELECT * FROM emp WHERE deptno=30;

2. 找出从事'clerk'工作的员工的编号、姓名、部门号。

SELECT empno, ename, deptno FROM emp WHERE job='clerk';

3. 检索出奖金多于基本工资的员工信息。

SELECT * FROM emp WHERE comm>sal;

4. 检索出奖金多于基本工资60%的员工信息。

SELECT * FROM emp WHERE comm>sal*0.6;

5. 找出获得奖金的员工的工作。

SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

6. 找出奖金少于100或者没有获得奖金的员工的信息。

SELECT * FROM emp WHERE comm<100 OR comm IS NULL;

7. 找出姓名以'a'、'b'、's'开始的员工信息。

SELECT * FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'b%' OR ename LIKE 's%';

8. 找到名字长度为6个字符的员工信息。

SELECT * FROM emp WHERE LENGTH(ename)=6;

9. 名字中不包含'r'字符的员工信息。

SELECT * FROM emp WHERE ename NOT LIKE '%r%';

10. 返回员工的详细信息并按姓名排序。

SELECT * FROM emp ORDER BY ename;

11. 返回员工的信息并按工作降序工资升序排列。

SELECT * FROM emp ORDER BY job DESC, sal ASC;

12. 找出姓名中包含'a'的员工信息。

SELECT * FROM emp WHERE ename LIKE '%a%';

多表查询

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

SELECT e1.ename, e2.ename AS leader_name 
FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.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 emp_count 
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno 
GROUP BY d.deptno;

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+COALESCE(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;