Mysql的查询语句

Mysql的查询语句

例如：有以下表

SQL语句

CREATE DATABASE mydb;

USE mydb;

CREATE TABLE dept( 
deptno	INT PRIMARY KEY, 
dname	VARCHAR(50), 
loc	VARCHAR(50) 
);

CREATE TABLE emp( 
empno		INT PRIMARY KEY, 
ename		VARCHAR(50), 
job		VARCHAR(50), 
mgr		INT, 
hiredate	DATE, 
sal		DECIMAL(7,2), 
comm		DECIMAL(7,2), 
deptno		INT, 
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) 
);

CREATE TABLE salgrade( 
grade	INT	PRIMARY KEY, 
losal	INT, 
hisal	INT 
);

INSERT INTO dept VALUES (10, '教研部', '北京'); 
INSERT INTO dept VALUES (20, '学工部', '上海'); 
INSERT INTO dept VALUES (30, '销售部', '广州'); 
INSERT INTO dept VALUES (40, '财务部', '武汉');

INSERT INTO emp VALUES (1009, '曾阿牛',	'董事长', NULL, '2001-11-17',	50000, NULL, 10); 
INSERT INTO emp VALUES (1004, '刘备',	'经理',   1009, '2001-04-02',	29750, NULL, 20); 
INSERT INTO emp VALUES (1006, '关羽',	'经理',   1009, '2001-05-01',	28500, NULL, 30); 
INSERT INTO emp VALUES (1007, '张飞',	'经理',	  1009, '2001-09-01',	24500, NULL, 10); 
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19',	30000, NULL, 20); 
INSERT INTO emp VALUES (1013, '庞统',	'分析师', 1004, '2001-12-03',	30000, NULL, 20); 
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20',	16000, 3000, 30); 
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22',	12500, 5000, 30); 
INSERT INTO emp VALUES (1005, '谢逊',	'销售员', 1006, '2001-09-28',	12500, 14000,30); 
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08',	15000, 0,    30); 
INSERT INTO emp VALUES (1012, '程普',	'文员',   1006, '2001-12-03',	9500,  NULL, 30); 
INSERT INTO emp VALUES (1014, '黄盖',	'文员',   1007, '2002-01-23',	13000, NULL, 10); 
INSERT INTO emp VALUES (1011, '周泰',	'文员',   1008, '2007-05-23',	11000, NULL, 20); 
INSERT INTO emp VALUES (1001, '甘宁',	'文员',   1013, '2000-12-17',	8000,  NULL, 20);

INSERT INTO salgrade VALUES (1, 7000,  12000); 
INSERT INTO salgrade VALUES (2, 12010, 14000); 
INSERT INTO salgrade VALUES (3, 14010, 20000); 
INSERT INTO salgrade VALUES (4, 20010, 30000); 
INSERT INTO salgrade VALUES (5, 30010, 99990);

一、单表练习

having的用法

having字句可以让我们筛选成组后的各种数据，
where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而 having子句在聚合后对组记录进行筛选。

显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列

SELECT job, SUM(sal) sall FROM emp WHERE job <> '销售员' GROUP BY job
HAVING sall > 50000 ORDER BY sall ASC

1.查询出部门编号为30的所有员工

SELECT * FROM `emp` where deptno='30'

2.所有销售员的姓名、编号和部门编号。

 select ename,deptno,empno from emp where job='销售员'

3.找出奖金高于工资的员工。

 select * FROM emp WHERE comm>sal

4.找出奖金高于工资60%的员工。

 SELECT * FROM emp WHERE comm > sal * 0.6

5.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

 select *  from emp where deptno=10 and job='经理' or deptno=20 and job='销售员'

6.找出部门编号为10中所有经理，部门编号为20中所有销售员，

还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料

 SELECT * FROM emp WHERE deptno = 10 AND job = '经理'
OR deptno = 20 AND job = '销售员'
or job not in ('经理','销售员') and sal>20000

7.有奖金的工种。

 SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL

8.无奖金或奖金低于1000的员工。

select ename from emp where comm is null  or comm<1000

9.查询名字由三个字组成的员工。

 select * from emp where ename LIKE '___'     //下划线是英文状态下

10.查询2000年入职的员工。

SELECT * FROM emp WHERE YEAR(hiredate) = 2000

11.查询所有员工详细信息，用编号升序排序

SELECT * FROM emp ORDER BY empno ASC

12.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

SELECT * FROM emp ORDER BY sal DESC, hiredate ASC

13.查询每个部门的平均工资

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno

14.求出每个部门的雇员数量。

select deptno,COUNT(1) from emp GROUP BY deptno

15.查询每种工作的最高工资、最低工资、人数

select job, MAX(SAL),MIN(sal),count(*) from emp GROUP BY job

16.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列

 SELECT job, SUM(sal) sall FROM emp WHERE job <> '销售员' GROUP BY job
HAVING sall > 50000 ORDER BY sall ASC

二、子查询练习

1.查出至少有一个员工的部门。 显示部门编号、部门名称、部门位置、部门人数。

SELECT	T1.*,
	T2.cnt
FROM 	dept T1
JOIN 	(SELECT deptno, COUNT(1) cnt FROM emp GROUP BY deptno HAVING cnt >= 1) T2
ON 	T1.deptno = T2.deptno

2.列出薪金比关羽高的所有员工。

用查出来关羽的薪资作为where 条件

SELECT * FROM emp WHERE sal > (
SELECT sal FROM emp WHERE ename = '关羽')

三、多表查询练习

1. 列出所有员工的姓名及其直接上级的姓名。

SELECT t1.ename empname,
t2.ename mgrname
from 
emp t1
left JOIN 
emp t2
on t1.mgr=t2.empno

2. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

SELECT 
		T1.empno, T1.ename, D.dname
FROM    	emp T1
JOIN    	emp T2
ON 		T1.mgr = T2.empno AND T1.hiredate < T2.hiredate
JOIN 		dept D
ON 		T1.deptno = D.deptno

3.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select T1.*,
T2.*
from
emp T1
left JOIN
dept T2
on
T2.deptno=T1.deptno

4.列出最低薪金大于15000的各种工作及从事此工作的员工人数。

SELECT job, COUNT(1) FROM emp GROUP BY job HAVING MIN(sal) > 15000

5. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

SELECT * FROM emp WHERE deptno = 
(SELECT deptno FROM dept WHERE dname = '销售部')

6.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

1.公司平均薪金

SELECT AVG(sal) from emp

2.列出薪金高于公司平均薪金的所有员工信息

select * from emp where sal >(SELECT AVG(sal) from emp)

3.所在部门名称，上级领导

select e1.empno, e1.ename, e2.mgr as '上级',d.dname
  from emp e1, emp e2, dept d
 where e1.mgr = e2.empno
   and e1.deptno = d.deptno;

4.员工工资等级。

select T1.grade,
	 t2.ename,
	 t2.empno
	 from
	 salgrade T1,emp t2
	 where t2.sal
	 BETWEEN 	T1.losal AND T1.hisal

5.全部整合

select 
T1.empno, T1.ename, d.dname, T1.sal, sg.grade
from 
emp T1, emp T2, dept d, salgrade sg
where 
T1.sal > (select avg(sal) from emp)
   and T1.mgr = T2.empno
   and T1.deptno = d.deptno
   and T1.sal between losal and hisal