部门表

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 ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE 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, 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 job, MIN(sal) as min_sal FROM emp GROUP BY job;

#9.计算出员工的年薪，并且以年薪排序。 SELECT ename, sal*12+IFNULL(comm,0) as annual_salary FROM emp ORDER BY annual_salary DESC;

#10.返回工资处于第四级别的员工的姓名。 SELECT ename FROM emp WHERE sal BETWEEN (SELECT losal FROM salgrade WHERE grade=4) AND (SELECT hisal FROM salgrade WHERE grade=4);