创建部门dept、员工emp数据表,并插入数据
部门dept数据表
1.创建部门信息:
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13)
);
2.插入部门信息:
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'),(20, 'RESEARCH', 'DALLAS'),(30, 'SALES', 'CHICAGO'),(40, 'OPERATIONS', 'BOSTON');
3.查看确定:
SELECT * FROM dept;
员工emp数据表
1.创建员工信息:
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
2.插入员工信息:
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10),(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);
3.查看确定:
SELECT * FROM emp;
实操1:基本查询
--所有员工的信息
--薪资大于等于1000并且小于等于2000的员工信息
--从员工表中查询出所有的部门编号
--查询出名字以A开头的员工的信息
--查询出名字第二个字母是L的员工信息
--查询出没有奖金的员工信息
--所有员工的平均工资
--所有员工的工资总和
--所有员工的数量
--最高工资
--最少工资
--最高工资的员工信息
--最低工资的员工信息
1.所有员工的信息:SELECT * FROM emp;
2.薪资大于等于1000并且小于等于2000的员工信息:
SELECT empno AS 员工编号,ename AS 姓名,job AS 工作,mgr AS 上级编号,hiredate AS 出生日期,sal AS 薪资(1000元至2000元),comm AS 佣金,deptno AS 部门 FROM emp WHERE sal>=1000 AND sal<=2000;
3.从员工表中查询出所有的部门编号:
SELECT DISTINCT deptno FROM emp;
4.查询出名字以A开头的员工的信息:
SELECT empno AS 员工编号,ename AS 姓名(以A开头),job AS 工作 FROM emp WHERE ename LIKE "A%";
5.查询出名字第二个字母是L的员工信息
SELECT empno AS 员工编号,ename AS 姓名(第二个字母是L),job AS 工作 FROM emp WHERE ename LIKE "_L%";
6.查询出没有奖金的员工信息:
SELECT empno AS 员工编号,ename AS 姓名(没有奖金),job AS 工作 FROM emp WHERE comm=" " OR comm IS NULL;
7.所有员工的平均工资:
SELECT AVG(sal) AS 平均工资 FROM emp;
8.所有员工的工资总和:
SELECT SUM(sal) AS 工资总和 FROM emp;
9.所有员工的数量:
SELECT COUNT(ename) AS 员工数量 FROM emp;
10.最高工资:
SELECT MAX(sal) AS 最高工资 FROM emp;
11.最少工资:
SELECT MIN(sal) AS 最高工资 FROM emp;
12.最高工资的员工信息:
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
13.最低工资的员工信息:
SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);
实操2:分组查询
--每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
group by关键字,详见“(数据库-MySQL)表-数据查询”的“其他查询方式:”-“7、分组”。
实操3:子查询
-- 单行子查询(> < >= <= = <>)
-- 查询出高于10号部门的平均工资的员工信息
-- 多行子查询(in not in any all) >any >all
-- 查询出比10号部门任何员工薪资高的员工信息
-- 多列子查询(实际使用较少) in
-- 和10号部门同名同工作的员工信息
-- Select接子查询
-- 获取员工的名字和部门的名字
-- from后面接子查询
-- 查询emp表中经理信息
-- where 接子查询
-- 薪资高于10号部门平均工资的所有员工信息
-- having后面接子查询
-- 有哪些部门的平均工资高于30号部门的平均工资
-- 工资>JONES工资
-- 查询与SCOTT同一个部门的员工
-- 工资高于30号部门所有人的员工信息
-- 查询工作和工资与MARTIN完全相同的员工信息
-- 有两个以上直接下属的员工信息
-- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
1.单行子查询(> < >= <= = <>)
-- 查询出高于10号部门的平均工资的员工信息
第一步:确定“10号部门的平均工资”
SELECT AVG(sal) FROM emp WHERE deptno=10;
第二步:查询高于上面结果(10号部门的平均工资)员工的信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);
2.多行子查询(in not in any all) >any >all
-- 查询出比10号部门任何员工薪资高的员工信息
常规实现也是两步
第一步:既然是比高,那就先找出10号部门的最高薪资
SELECT MAX(sal) FROM emp WHERE deptno = 10;
第二步:薪资对比,得出结果
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 10);
-- 查询出比10号部门任意一个员工薪资高的所有员工信息 : 只要比其中随便一个工资都可以。
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno = 10) AND deptno != 10;
3.多列子查询(实际使用较少) in
-- 和10号部门同名同工作的员工信息
第一步:查询出10号部门所有人的名字和工作
第二步:名字和工作对比,得出结果(使用in)
SELECT * FROM emp WHERE (ename,job) IN (SELECT ename,job FROM emp WHERE deptno=10) AND deptno !=10;
4.Select后面接子查询
-- 获取员工的名字和部门的名字
SELECT e.empno 员工编号,e.ename 姓名,(SELECT d.dname FROM dept d WHERE e.deptno=d.deptno) 部门,e.deptno 部门编号 FROM emp e;
5.from后面接子查询
-- 查询emp表中经理信息
首先,我们一起来分析emp表中所有信息,谁是谁的领导,怎么判断呢? 不难看出,mgr是每个员工的上级编码,该编码是否与员工编码empno列中值有对应呢? 除了董事长president的上级编码为NULL,其他都有,那么,我们可以先使用“DISTINCT”对mgr进行筛选,得出所有领导层的编码;董事长president的上级编码为NULL,就不用考虑啦!
第一步:SELECT DISTINCT mgr FROM emp;
第二步:SELECT * FROM emp e,(SELECT DISTINCT mgr FROM emp) mgrtable WHERE e.empno = mgrtable.mgr;
上下对比,mgr数据在下表查询的empno数据中都有对应值;而“(SELECT DISTINCT mgr FROM emp) mgrtable”将查询得到的数据以mgrtable表的形式出现,更容易被调用!这样,子查询出现在from后面是允许的,一定要有括号“()”括起来!
6.where 接子查询
-- 薪资高于10号部门平均工资的所有员工信息
之前“多行子查询”有使用过查询,自己慢慢分析:SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=10);
7.having后面接子查询
-- 有哪些部门的平均工资高于30号部门的平均工资
传统的写法,很长的代码:
SELECT allavgsal.deptno,allavgsal.avgsal FROM (SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) allavgsal,(SELECT AVG(sal) avgsal FROM emp WHERE deptno=30) tavgsal WHERE allavgsal.avgsal>tavgsal.avgsal;
如果使用“having”,我们3步可以完成:
第一步:统计所有的部门的平均工资
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
第二步:确定30号部门的平均工资
SELECT AVG(sal) FROM emp WHERE deptno=30;
第三步:使用GROUP BY分组之后结合“having”判断,得出结果
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=30);
此时,我们可以如此理解: having单独使用,与where类似。(此处,由于使用了GROUP BY分组,生成新的数据组合,where只能使用传统的方式实现)
例如,我们如果想实现,查询所有薪资高于30号部门的平均工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=30);
SELECT * FROM emp HAVING sal>(SELECT AVG(sal) FROM emp WHERE deptno=30); (换having)
前后对比,可以看出,显示效果一样!
8.工资>JONES工资(所有员工的信息)
SELECT * FROM emp HAVING sal>(SELECT sal FROM emp WHERE ename="JONES");
9.查询与SCOTT同一个部门的员工
SELECT * FROM emp HAVING deptno=(SELECT deptno FROM emp WHERE ename="SCOTT");
10.工资高于30号部门所有人的员工信息
SELECT * FROM emp HAVING sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
11.查询工作和工资与MARTIN完全相同的员工信息
SELECT * FROM emp HAVING (sal,job) IN (SELECT sal,job FROM emp WHERE ename="MARTIN");
12.有两个以上直接下属的员工信息
第一步:查出emp表中mgr信息
SELECT mgr FROM emp;
第二步:分组统计mgr的信息
SELECT mgr,COUNT(*) FROM emp GROUP BY mgr HAVING COUNT(*)>2;
第三步:使用“in”遍历查询,得出结果
SELECT * FROM emp e1 WHERE e1.empno IN (SELECT e2.mgr FROM emp e2 GROUP BY e2.mgr HAVING COUNT(*)>2);
13.查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
较为容易理解的:SELECT e2.ename,e2.sal,d.dname,d.loc FROM emp e2,dept d WHERE d.deptno=(SELECT e1.deptno FROM emp e1 WHERE e1.empno=7788) AND e2.empno=7788;
简洁的写法:SELECT ename,sal ,dname, loc FROM emp ,dept WHERE emp.deptno = dept.deptno AND empno=7788;
综合查询
-- 查询出高于本部门平均工资的员工信息
-- 列出达拉斯加工作的人中,比纽约平均工资高的人
-- 查询7369员工编号,姓名,经理编号和经理姓名
-- 查询出各个部门薪水最高的员工所有信息
1.查询出高于本部门平均工资的员工信息
SELECT * FROM emp e1 WHERE e1.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e1.deptno=e2.deptno GROUP BY e2.deptno);
附带部门平均工资:
SELECT e1.*,deptavgsal.avgsal 部门平均工资 FROM emp e1,(SELECT deptno,AVG(sal) avgsal FROM emp e2 GROUP BY e2.deptno) deptavgsal WHERE e1.deptno=deptavgsal.deptno AND e1.sal>deptavgsal.avgsal;
2.列出达拉斯加(DALLAS)工作的人中,比纽约(NEW YORK)平均工资高的人
SELECT * FROM emp e1 WHERE e1.deptno = (SELECT d1.deptno FROM dept d1 WHERE d1.loc="DALLAS") AND e1.sal>(SELECT AVG(e2.sal) FROM emp e2 WHERE e2.deptno=(SELECT d2.deptno FROM dept d2 WHERE d2.loc="NEW YORK"));
3.查询7369员工编号,姓名,经理编号和经理姓名
根据员工emp数据表,可以先确定“7369员工编号,姓名,经理编号”
SELECT empno 员工编号,ename 姓名,mgr 经理编号 FROM emp WHERE empno=7369;
再把确定的经理编号mgr的值,查询经理的姓名
SELECT empno,ename FROM emp WHERE emp.empno=(SELECT mgr FROM emp WHERE empno=7369);
整合之后
SELECT e1.empno 员工编号,e1.ename 姓名,e1.mgr 经理编号,(SELECT ename FROM emp WHERE emp.empno=(SELECT mgr FROM emp WHERE empno=7369)) 经理姓名 FROM emp e1 WHERE e1.empno=7369;
简化
SELECT e1.empno 员工编号,e1.ename 姓名,e1.mgr 经理编号,(SELECT e2.ename FROM emp e2 WHERE e2.empno=e1.mgr) 经理姓名 FROM emp e1 WHERE e1.empno=7369;
4.查询出各个部门薪水最高的员工所有信息
第一步:确定所有部门薪资最高是多少
SELECT e1.deptno,MAX(e1.sal) FROM emp e1 GROUP BY e1.deptno;
第二步:根据部门和薪资获取员工信息
SELECT * FROM emp e2 WHERE (e2.deptno,e2.sal) IN (SELECT e1.deptno,MAX(e1.sal) sal FROM emp e1 GROUP BY e1.deptno);
前面使用in实现,如果使用where咋样呢?
SELECT * FROM emp e1 WHERE e1.sal = (SELECT MAX(sal) FROM emp e2 WHERE e1.deptno = e2.deptno GROUP BY e2.deptno);
显示效果一样!
持续更新之中...