综合查询

条件查询（综合）
（一）、创建库：（管理系统）
create database offcnoa; 
（二）、创建表：
CREATE TABLE DEPT (
DEPTNO INT(2) NOT NULL ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	PRIMARY KEY (DEPTNO)
	);
CREATE TABLE EMP(
EMPNO INT(4)  NOT NULL ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	PRIMARY KEY (EMPNO),
	DEPTNO INT(2) 
	);

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 

 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 

 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
（三）、基本表的介绍
dept (部门表)  
   DEPTNO (部门编号)
   NAME(部门名称)
   LOC(部门的地址)
emp(员工表)
   EMPNO(员工编号)  
   ENAME(员工的名称)
   JOB(工作岗位) 
   MGR（直接上级领导编号）
   HIREDATE（入职日期）
   SAL（薪资，工资）
   COMM(补助)
   DEPTNO（部门编号）
salgrade（薪资等级）
   GRADE(薪资编号)
   LOSAL(最低薪资)
   HISAL(最高薪资)

做题

DESC emp;
#查询员工的姓名
SELECT ename FROM emp;
#查询员工的薪水及姓名
SELECT ename, sal FROM emp;
#查询员工表所有的数据
SELECT * FROM emp;
#查询每一个员工的年薪
SELECT sal*16 AS 年薪 FROM emp;
#######################################################################
#1.查询薪水为5000的员工
SELECT * FROM emp WHERE sal=5000;
#2.查询薪 水不等于5000的员工
SELECT * FROM emp WHERE sal<>5000;
SELECT * FROM emp WHERE sal NOT IN (5000);
#3.查询薪水在1600-3000之间
SELECT * FROM emp WHERE sal BETWEEN 1600 AND 3000;
#4.查询部门编号为20或者30的部门信息
SELECT * FROM dept;
SELECT * FROM dept WHERE deptno=20 OR deptno=30;
SELECT * FROM dept WHERE deptno IN (20,30);
#5.查询部门编号不为20或者30的部门信息
SELECT * FROM dept WHERE deptno NOT IN (20,30);
#6.查询补助为空(不为空)的员工isn't
SELECT * FROM emp WHERE comm IS NOT NULL;
#7.员工姓名第一个字母带S的员工信息
SELECT * FROM emp WHERE ename LIKE 's%';
SELECT * FROM emp WHERE ename LIKE '%s%';
#8.名字总共5个字母并且以S结尾
SELECT * FROM emp WHERE ename LIKE '____s';
#####################################################
# order by 字段 asc(升序，默认) desc（降序）
#1.根据员工的薪水排序
SELECT * FROM emp ORDER BY sal;
#2.根据员工的薪水降序
SELECT * FROM emp ORDER BY sal DESC;
#3.根据员工的入职日期进行降序
SELECT * FROM emp ORDER BY hiredate DESC;
#4.查询职位为manager，并且按照薪资从低到高排序
SELECT * FROM emp WHERE job='MANAGER' ORDER BY SAL;
#######################################
#聚合函数 max min count sum avg
#1.求当前所有员工的最高工资
SELECT MAX(sal) AS '最牛工资' FROM emp;
#2.求当前所有员工年支出
SELECT SUM(sal*12) AS '年支出工资' FROM emp;
#3.每个月的平均薪水
SELECT AVG(sal) FROM emp;
#4.公司总共多少个人
SELECT COUNT(*) FROM emp;###
SELECT COUNT(comm) FROM emp;
SELECT * FROM emp;
##############################
#忽略人的情况，查询公司一共有几个部门
SELECT DISTINCT deptno FROM emp;
##############################
#分组 group by  having
#1.找出不同工作类别中最高的工资。
SELECT * FROM emp;
SELECT MAX(sal),job FROM emp GROUP BY job;
#2,找出不同工作类别中最高的工资,显示的时候工资按照从高到低显示
SELECT MAX(sal) AS t,job FROM emp GROUP BY job ORDER BY t DESC;
#3.求每个部门的平均薪资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
#4.求每个岗位的最高薪资，除Manager之外
SELECT job,MAX(sal) FROM emp WHERE job<>'manager' GROUP BY job;
#5.找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的
#having不能单独使用，必须配合group by
SELECT job,AVG(sal) AS t FROM emp GROUP BY job HAVING t >2000;
#####################################################
#子查询  一个查询的结果当作另一个查询的条件 嵌套查询
#找出薪水比公司平均薪水高的员工，要求显示员工的名字和薪水
SELECT AVG(sal) FROM emp;
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);