单表查询练习

# ORDER BY price DESC  排序查询, DESC是降序 ASC是升序 默认 ASC
--  通过order  by语句，可以将查询出的结果进行排序。放置在select语句的最后。
--  格式:
--  SELECT * FROM 表名 ORDER BY 排序字段ASC|DESC;
--  ASC 升序 (默认)
--  DESC 降序

SELECT pname,price FROM product ORDER BY price DESC; 
SELECT pid,pname,price FROM product ;



-- 聚合查询
#2.在价格排序(降序)的基础上，以分类排序(降序),先按第一条件排序
#如果第一条件可以排出先后,就不再用第二条件判断
#如果第一条件判断结果一样再用第二条件判断
SELECT * FROM product ORDER BY price DESC,category_id DESC;

#1 查询商品的总条数
SELECT COUNT(*) FROM product;
#2 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price>200;
#3 查询分类为'c001'的所有商品价格的总和

#4 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id='c002';
SELECT MAX(price) AS 最大值,SUM(price) AS 总和,AVG(price) AS 平均值 ,MIN(price) AS 最小值 FROM product ;

-- 
-- 分组查询是指使用group by字句对查询信息进行分组。
-- 格式：
-- SELECT 字段1,字段2… FROM 表名GROUP BY分组字段 HAVING 分组条件;

# 分组优先于计算函数,先分组再求函数
SELECT SUM(price) FROM product GROUP BY category_id;
#0 统计所有商品的个数
SELECT COUNT(*) FROM product;
#1 统计各个分类商品的个数
SELECT category_id,COUNT(*) FROM product GROUP BY category_id;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id,COUNT(*) FROM product GROUP BY category_id  HAVING COUNT(*)>1;
SELECT category_id,COUNT(*) 个数 FROM product GROUP BY category_id  HAVING 个数>1;


-- 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
--  
-- having与where的区别:
-- having是在分组后对数据进行过滤.
-- where是在分组前对数据进行过滤
-- having后面可以使用统计函数过滤数据
-- where后面不可以使用统计函数。

SELECT category_id,COUNT(*) FROM product WHERE price<=1000 GROUP BY category_id;

# 分页查询
 
-- SELECT 字段1，字段2... FROM 表明 LIMIT M,N  M表示下表(从0开始) N表示查询的条数,
--  M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
--  N: 整数，表示查询多少条数据
--  SELECT 字段1，字段2... FROM 表明 LIMIT 0,5
--  SELECT 字段1，字段2... FROM 表明 LIMIT 5,5
SELECT * FROM product LIMIT 0,5;
SELECT * FROM product LIMIT 5,5;

/*
    课后练习:
    
*/

-- 1.   查询工资大于1200的员工姓名和工资
SELECT ename,sal FROM emp WHERE sal>1200;

-- 2.   查询员工号为7654的员工的姓名和部门号
SELECT ename,deptno FROM emp WHERE empno = 7654;


-- 3.   选择工资不在1000到2000的员工的姓名和工资
SELECT ename,sal FROM emp WHERE sal < 1000 OR sal > 2000;
SELECT ename,sal FROM emp WHERE NOT (sal>1000 AND sal <2000)

SELECT ename,sal FROM emp WHERE sal NOT BETWEEN 1000 AND 2000;
-- 4.   选择雇用时间在1981-05-01到1981-10-01之间的员工姓名，job和雇用时间
SELECT ename,job,hiredate FROM emp WHERE hiredate BETWEEN '1981-05-01' AND '1981-10-01';
-- 5.   选择在20或10号部门工作的员工姓名和部门号

SELECT ename,deptno FROM emp WHERE deptno IN (20,10);
-- 6.   选择在1981年雇用的员工的姓名和雇用时间
SELECT ename,hiredate FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';
SELECT ename,hiredate FROM emp WHERE hiredate LIKE '1981%';
-- 7.   选择公司中没有管理者的员工姓名及job

SELECT ename,job FROM emp WHERE mgr IS NULL;

-- 8.   选择公司中有奖金的员工姓名，工资和奖金
SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL;


-- 9.   选择员工姓名的第三个字母是a的员工姓名
SELECT ename FROM emp WHERE ename LIKE '__a%';

-- 10.  选择姓名中有字母a和e的员工姓名
SELECT ename FROM emp WHERE ename LIKE '%a%e%' OR ename LIKE '%e%a%';

SELECT ename FROM emp WHERE ename LIKE '%a%' AND  ename LIKE '%e%';


-- 12.  查询员工号，姓名，工资，以及工资提高百分之20%后的结果（请使用别名new salary）
SELECT empno,ename,sal,sal * 1.2 `new salary`  FROM emp;


-- 13.  将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT ename,LENGTH(ename) FROM emp ORDER BY ename;

-- 14.  查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(sal), MIN(sal), AVG(sal), SUM(sal) FROM emp;
-- 15.  查询各job的员工工资的最大值，最小值，平均值，总和
SELECT job, MAX(sal), MIN(sal), AVG(sal), SUM(sal) FROM emp GROUP BY job;


-- 16.  查询具有各个job的员工人数
SELECT job,COUNT(*) FROM emp GROUP BY job;


-- 17.  查询员工最高工资和最低工资的差距（请用别名 DIFFERENCE）

SELECT MAX(sal)-MIN(sal) DIFFERENCE FROM emp;


-- 18.  查询各个管理者手下员工的最低工资，其中最低工资不能低于1000，没有管理者的员工不计算在内

SELECT mgr,MIN(sal) AS minsal FROM emp GROUP BY mgr HAVING minsal > 1000 AND mgr IS NOT NULL;

-- 19.  查询所有部门的名字，loc，员工数量和工资平均值
SELECT dept.dname,dept.loc,COUNT(*),AVG(sal) FROM dept,emp WHERE emp.deptno = dept.deptno GROUP BY dept.dname;

SELECT dept.dname,dept.loc,COUNT(emp.empno),AVG(emp.sal) FROM dept,emp WHERE emp.deptno = dept.deptno GROUP BY emp.deptno;
-- 1、按员工编号升序排列不在10号部门工作的员工信息
SELECT * FROM emp WHERE NOT deptno=10 ORDER BY empno DESC; -- 降序
SELECT * FROM emp WHERE deptno !=10 ORDER BY empno; -- 升序

-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列

select * FROM emp WHERE ename NOT LIKE '_A%'  AND sal >800 ORDER BY sal DESC;

-- 3、求每个部门的平均薪水

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

-- 4、求各个部门的最高薪水

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;

-- 5、求每个部门每个岗位的最高薪水

SELECT deptno,job, MAX(sal) FROM emp GROUP BY deptno,job;


-- 6、求平均薪水大于2000的部门编号

SELECT emp.deptno , AVG(sal) FROM emp GROUP BY deptno  HAVING AVG(sal)>2000;


-- 8、求最高薪水的员工信息

SELECT * FROM emp WHERE sal = 
    (SELECT MAX(sal) FROM emp);
    

-- 9、求多于平均薪水的员工信息



SELECT * FROM emp WHERE sal > 
    (SELECT AVG(sal) FROM emp);


-- 11、求各个部门薪水最高的员工信息

#每个部门的最高薪水

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;

SELECT * FROM emp WHERE emp.sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);

SELECT * FROM emp,(SELECT deptno, MAX(sal) maxsal FROM emp GROUP BY deptno) d
  WHERE emp.deptno = d.deptno AND emp.sal = d.maxsal;

===========================================================

-- 1.  查询和SCOTT相同部门的员工姓名和雇用日期


     
SELECT ename,hiredate,deptno FROM emp WHERE deptno = 
        (SELECT deptno FROM emp WHERE ename = 'SCOTT');
-- 2.   查询工资比公司平均工资高的员工的员工号，姓名和工资。


SELECT empno,ename,sal FROM emp WHERE sal >
    (SELECT AVG(sal) FROM emp);
     
-- 3.(难度较高,可删除)查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT ename euser,empno,sal  FROM emp WHERE sal 
>(SELECT AVG( sal) FROM emp GROUP BY (SELECT deptno FROM emp WHERE ename='euser')) ORDER BY sal;
    

-- SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 
SELECT ename,empno,sal FROM emp,
    (SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) d
    WHERE emp.deptno = d.deptno AND emp.sal > d.avgsal ORDER BY sal;
-- 4.   查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT empno,ename FROM emp WHERE deptno=
    (SELECT deptno FROM emp WHERE ename LIKE '%u%');

-- 6. 查询管理者是KING的员工姓名和工资 

  SELECT ename,sal FROM emp WHERE mgr = 
    (SELECT empno FROM emp WHERE ename = 'KING');