SQL练习

练习

1. 案例：查询没有上级领导的员工的编号，姓名，工资
   select empno,ename,sal 
   from emp 
   where mgr is null;
2. 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
   select ename,job,sal,comm 
   from emp 
   where comm=0 or comm is null;
3. 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
   select empno,ename,job,comm 
   from emp 
   where comm>0;
4. 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
   select ename,sal,mgr 
   from emp 
   where mgr is not null;
5. 案例：查询emp表中名字以‘S’开头的所有员工的姓名
   select ename 
   from emp 
   where ename like 's%';
6. 案例：查询emp表中名字的最后一个字符是'S'的员工的姓名
   select ename 
   from emp 
   where ename like '%s';
7. 案例：查询倒数的第2个字符是‘E’的员工的姓名
   select ename 
   from emp 
   where ename like '%e_';
8. 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
   select ename 
   from emp 
   where ename like '%n__';
9. 案例：查询emp表中员工的名字中包含‘A’的员工的姓名
   select ename 
   from emp 
   where ename like '%a%';
10. 案例：查询emp表中名字不是以'K'开头的员工的所有信息
    select * 
    from emp 
    where ename not like 'k%';
11. 案例：查询emp表中名字中不包含‘A’的所有员工的信息
    select * 
    from emp 
    where ename not like '%a%';
12. 案例：做文员的员工人数（job 中 含有 CLERK 的）
    select count(*) 
    from emp 
    where job='clerk';
13. 案例：销售人员 job: SALESMAN 的最高薪水
    select max(sal) 
    from emp 
    where job='salesman';
14. 案例：最早和最晚入职时间
    select min(hiredate),max(hiredate) 
    from emp;
15. 案例：查询类别 163的商品总库存量
    select sum(num) 
    from t_item 
    where category_id=163;
16. 案例：查询 类别 163 的商品
    select * 
    from t_item 
    where category_id = 163;
17. 案例：查询商品价格不大于100的商品名称列表
    select title 
    from t_item 
    where price <= 100;
18. 案例：查询品牌是联想,且价格在40000以上的商品名称和价格
    select title,price 
    from t_item 
    where title like '%联想%' and price>40000;
19. 案例：查询品牌是三木,或价格在50以下的商品名称和价格
    select title,price 
    from t_item 
    where title like '%三木%' or price<50;
20. 案例：查询品牌是三木、广博、齐心的商品名称和价格
    select title,price 
    from t_item 
    where title like '%三木%' or title like '%广博%' or title like '%齐心%';
21. 案例：查询品牌不是联想、戴尔的商品名称和价格
    select title,price 
    from t_item 
    where title not like '%联想%' and title not like '%戴尔%';
22. 案例：查找品牌是联想且价格大于10000的名称
    select title 
    from t_item 
    where title like '%联想%' and price>10000;
23. 案例：查询联想或戴尔的电脑名称列表
    select title 
    from t_item 
    where title like '%联想%' or title like '%戴尔%';
24. 案例：查询卖点含有'赠'产品名称
    select title 
    from t_item 
    where sell_point like '%赠%';
25. 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
    select empno,ename,job,sal 
    from emp 
    where sal between 1000 and 2000;
26. 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
    select ename,job,mgr,deptno 
    from emp 
    where deptno=10 and mgr is not null;
27. 案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
    select empno,ename,job,sal from emp 
    where ename 
    like '%e%' and job!='manager';    
28. 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
    select empno,ename,deptno 
    from emp 
    where deptno in(10,20);
29. 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
    select empno,eanme,job,comm 
    from emp 
    where comm=0 or comm is null or ename not like '%t_';
30. 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
    select ename,job,sal,hiredate,deptno 
    from emp 
    where sal>3000 or deptno=30;
31. 案例：查询不是30号部门的员工的所有信息
    select * 
    from emp 
    where deptno!=30;
32. 案例：查询奖金不为空的员工的所有信息
    select * 
    from emp 
    where comm is not null;
33. 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
    select empno,ename,job 
    from emp 
    order by empno desc;
34. 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
    select ename,job,sal 
    from emp 
    where deptno in(10,30) 
    order by sal;
35. 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
    select * 
    from emp 
    order by deptno,empno desc;
36. 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
    select empno,ename,sal,deptno,mgr 
    from emp 
    where sal>1000 or mgr is null 
    order by deptno desc,sal;
37. 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
    select empno,ename,sal,comm 
    from emp 
    where ename not like '%s%' 
    order by sal,empno desc;
38. 案例：统计emp表中员工的总数量
    select count(*) 
    from emp;
39. 案例：统计emp表中获得奖金的员工的数量
    select count(*) 
    from emp 
    where comm>0;
40. 案例：求出emp表中所有的工资累加之和
    select sum(sal) 
    from emp;
41. 案例：求出emp表中所有的奖金累加之和
    select sum(comm) 
    from emp;
42. 案例：求出emp表中员工的平均工资
    select avg(sal) 
    from emp;
43. 案例：求出emp表中员工的平均奖金    
    select avg(comm) 
    from emp;
44. 案例：求出emp表中员工的最高工资
    select max(sal) 
    from emp;
45. 案例：求出emp表中员工编号的最大值
    select max(empno) 
    from emp;
46. 案例：查询emp表中员工的最低工资。
    select min(sal) 
    from emp;
47. 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
    select count(*) 总人数,sum(sal) 工资总和,avg(sal) 平均工资,max(comm) 最高奖金, min(comm) 最低奖金 
    from emp;

数学相关函数

1. 向下取整 floor(num)
   select floor(3.8);
2. 四舍五入 round(num)
   select round(23.8);
3. 四舍五入 round(num,m) m代表小数位数
   select round(23.879,2);
4. 非四舍五入 truncate(num,m) m代表小数位数
   select truncate(23.879,2);
5. 随机数 rand()  0-1随机数
   select rand();
6. 3-5的随机整数  3，4，5     0-2+2
  select floor(rand()*3+3);

分组查询

格式：
group by 分组字段名

每个或每种什么，就以什么进行分组查询

1. 查询每个部门的平均工资
   select deptno,avg(sal) 
   from emp 
   group by deptno;
2. 查询每种职业的最高工资
   select job,max(sal) 
   from emp 
   group by job;
3. 查询每个部门的人数
   select deptno,count(*) 
   from emp 
   group by deptno;
4. 查询工资大于1000的员工中每个部门的最高工资
   select deptno,max(sal) 
   from emp 
   where sal>1000 
   group by deptno;
5. 查询每个领导的手下人数
   select mgr,count(*) 
   from emp 
   where mgr is not null 
   group by mgr;

多字段分组在group by后面写多个字段名

1. 查询每个部门，每种职业的平均工资
   select deptno,job,avg(sal) 
   from emp 
   group by deptno,job;
2. 查询emp表中每个部门的编号、人数、工资总和、根据人数进行升序排序，如果人数一致根据工资总和降序排序
   select deptno,count(*),sum(sal) 
   from emp 
   group by deptno 
   order by count(*),sum(sal) desc;
   select deptno,count(*) c,sum(sal) s 
   from emp 
   group by deptno 
   order by c,s desc;
3. 查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排序
   select deptno,avg(sal) a,min(sal),max(sal) 
   from emp 
   where sal between 1000 and 3000 
   group by deptno 
   order by a;
4. 查询有上级领导的员工，每个职业的人数，工资总和，平均工资，根据人数进行降序排序，如果人数一致则根据平均工资进行升序排序
   select job,count(*) c,sum(sal),avg(sal) a 
   from emp 
   where mgr is not null 
   group by job 
   order by c desc,a;

having

where 后面只能写普通字段的条件，不能写聚合函数的条件 把聚合函数的条件写在having后面

各个关键字的顺序
select ..... 
from .... 
where .... 
group by .... 
having .... 
order by ..... 
limit ....;

1. 查询每个部门的平均工资，要求平均工资大于2000
   select deptno,avg(sal) a 
   from emp 
   where a>2000 
   group by deptno;
   select deptno,avg(sal) a 
   from emp 
   group by deptno 
   having a>2000;
2. 查询每个分类category_id的平均单价 要求平均单价低于100
   select category_id,avg(price) a 
   from t_item 
   group by category_id 
   having a<100;
3. 查询分类id为238，917的两个分类的平均单价
   select category_id,avg(price) 
   from t_item 
   where category_id in(238,917) 
   group by category_id;
4. 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
   select deptno,count(*),avg(sal) a 
   from emp 
   group by deptno 
   having a>2000 
   order by a desc; 
5. 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资进行升序排序
   select deptno,sum(sal),avg(sal) a 
   from emp 
   where sal between 1000 and 3000 
   group by deptno 
   having a>=2000 
   order by a;

提高题

1. 查询每年入职的人数
   select extract(year from hiredate) year,count(*) 
   from emp 
   group by year;
2. 查询员工表中最高的平均工资
   select avg(sal) a 
   from emp 
   group by deptno
   order by a desc 
   limit 0,1;

子查询（嵌套查询）

3. 查询emp表中工资最高的员工信息
   select max(sal) 
   from emp;      // 5000
   select * 
   from emp 
   where sal=(
       select max(sal) 
       from emp
   );
4. 查询高于平均工资的员工信息
   select avg(sal) 
   from emp;
   select * 
   from emp 
   where sal>(
       select avg(sal) 
       from emp
   );
5. 查询工资高于20号部门最高工资的员工信息
   select * 
   from emp 
   where sal>(
       select max(sal) 
       from emp 
       where deptno=20
   );
6. 查询和jones相同工作的其它员工信息
   select job 
   from emp 
   where ename='jones';
   select * 
   from emp 
   where job=(
       select job 
       from emp 
       where ename='jones'
   ) and ename!='jones';
7. 查询工资最低的员工的同事们的信息（同事=同一个部门）
   1. 得到最低工资
      select min(sal) 
      from emp;
   2. 通过最低工资找到部门编号
      select deptno 
      from emp 
      where sal=(
          select min(sal) 
          from emp
      );
   3. 通过部门编号查询该部门的人，还要去掉工资最低的
      select * 
      from emp 
      where deptno in(
          select deptno 
          from emp 
          where sal=(
              select min(sal) 
              from emp
          )
      ) and sal != (
          select min(sal) 
          from emp
      );
8. 查询最后入职的员工信息
   select max(hiredate) 
   from emp;
   select * 
   from emp 
   where hiredate=(
       select max(hiredate) 
       from emp
   );
9. 查询king的部门编号和部门名称（需要用到dept表）
   select deptno 
   from emp 
   where ename='king';
   select dname 
   from dept 
   where deptno=(
       select deptno 
       from emp 
       where ename='king'
   );
10. 查询有员工的部门信息（想办法过滤掉40号部门，因为它没员工）
    select distinct deptno 
    from emp; 
    select * 
    from dept 
    where deptno in(
        select distinct deptno 
        from emp
    );
11. 查询平均工资最高的部门信息（难度最高）需要考虑并列最高的问题
    (1). 得到最高的平均工资
       select avg(sal) a 
       from emp 
       group by deptno 
       order by a desc 
       limit 0,1;
    (2). 通过最高平均工资查部门编号
       select deptno 
       from emp 
       group by deptno 
       having avg(sal)=(
           select avg(sal) a 
           from emp 
           group by deptno 
           order by a desc 
           limit 0,1
       );
    (3). 通过部门编号查部门信息
       select * 
       from dept 
       where deptno in(
           上面一坨
       );

• 子查询总结:

1. 可以嵌套无数层
2. 子查询可写的位置：
   1. 把子查询写在where或having的后面，当成查询条件的值
   2. 写在创建表的时候
      create table emp_20 as (select * from emp where deptno=20);
   3. 写在from后面，当成一张虚拟的表 必须有别名
      select ename from (select * from emp where deptno=20) newtable;

关联查询

• 同时查询多张表的数据的查询方式称为关联查询
• 如果关联查询不写关联关系会得到两张表数据的乘积，这种乘积称为笛卡尔积。
  笛卡尔积是一种错误的查询结果会占用大量的内存，工作中切记不要出现。

1. 查询每一个员工的员工姓名和对应的部门名称
   select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
2. 查询在new york工作的员工姓名
   select e.ename
   from emp e,dept d
   where e.deptno=d.deptno and d.loc='new york';

等值连接和内连接

等值连接和内连接查询到的是一样的结果，为两张表的交集数据

1. 等值连接
   select * 
   from A,B 
   where A.x=B.x and A.age=18;
2. 内连接
   select * 
   from A 
   [inner] join B on A.x=B.x 
   where A.age=18;

案例：查询每一个员工的员工姓名和对应的部门名称
select e.ename,d.dname
from emp e 
join dept d on e.deptno=d.deptno;

外连接

外连接查询到的是一张表的全部数据和另外一张表的交集数据

格式
select * 
from A 
left/right [outer] join B on A.x=B.x 
where A.age=18;

查询所有的部门名和其对应的员工名
select d.dname,e.ename
from emp e 
right join dept d on e.deptno=d.deptno;

关联查询总结

1. 查询方式有三种：等值连接、内连接、外连接
2. 如果查询的数据是两张表的交集数据使用等值或内连接(推荐)
3. 如果查询的数据是一张表的全部数据和另外一张表的交集数据使用外连接

练习

1. 每个部门的人数，根据人数降序排序
   select deptno,count(*) c 
   from emp 
   group by deptno 
   order by c desc;
2. 每个部门中，每个主管的手下人数
   select deptno,mgr,count(*) 
   from emp 
   where mgr is not null 
   group by deptno,mgr;
3. 每种工作的平均工资
   select job,avg(sal) 
   from emp 
   group by job;
4. 每年的入职人数
   select extract(year from hiredate) year,count(*) 
   from emp 
   group by year;
5. 少于等于3个人的部门信息
   select d.*
   from emp e 
   right join dept d on e.deptno=d.deptno 
   group by deptno 
   having count(e.ename)<=3;
6. 拿最低工资的员工信息
   select * from emp 
   where sal=(
       select min(sal) 
       from emp
   );
7. 只有一个下属的主管信息
   select mgr 
   from emp 
   where mgr is not null 
   group by mgr 
   having count(*)=1;
   select * 
   from emp 
   where empno in(
       select mgr 
       from emp 
       where mgr is not null 
       group by mgr 
       having count(*)=1
   );
8. 每月发工资最多的部门信息
   (1).得到最高的工资总和
       select sum(sal) s 
       from emp 
       group by deptno 
       order by s desc 
       limit 0,1;
   (2).通过工资总和查部门的编号
       select deptno 
       from emp 
       group by deptno 
       having sum(sal)=(
           select sum(sal) s 
           from emp 
           group by deptno 
           order by s desc 
           limit 0,1
       );
   (3).通过部门编号查部门信息
       select * 
       from dept 
       where deptno in (
           上面一坨
       );
9. 下属最多的人，查询其个人信息
   (1).得到下属最多的人数
       select count(*) c 
       from emp 
       group by mgr 
       order by c desc 
       limit 0,1;
   (2).通过人数找mgr
       select mgr 
       from emp 
       group by mgr 
       having count(*)=(
           select count(*) c 
           from emp 
           group by mgr 
           order by c desc 
           limit 0,1
       );
   (3).通过mgr找个人信息
       select * 
       from emp 
       where empno in(
           上面一坨
       );
10. 拿最高工资员工的同事信息
    select * 
    from emp 
    where deptno = (
        select deptno 
        from emp 
        where sal=(
            select max(sal) 
            from emp
        )
    ) and sal !=(
        select max(sal) 
        from emp
    );
11. 和最后入职的员工在同一部门的员工信息 实现流程和第十题一样
    select max(hiredate) 
    from emp;
    select deptno 
    from emp 
    where hiredate=(
        select max(hiredate) 
        from emp
    );
    select * 
    from emp 
    where deptno=(
        select deptno 
        from emp 
        where hiredate=(
            select max(hiredate) from emp
        )
    ) and hiredate!=(
        select max(hiredate) 
        from emp
    );
12. 查询平均工资高于20号平均工资的部门信息
    select avg(sal) 
    from emp 
    where deptno=20;
    select deptno 
    from emp 
    group by deptno 
    having avg(sal)>(
        select avg(sal) 
        from emp 
        where deptno=20
    );
    select * 
    from dept 
    where deptno in(
        select deptno 
        from emp 
        group by deptno 
        having avg(sal)>(
            select avg(sal) 
            from emp 
            where deptno=20
        )
    );
13. 查询员工信息和员工对应的部门名称
    select e.*,d.dname
    from emp e 
    join dept d on e.deptno=d.deptno;
14. 查询员工信息，部门名称，所在城市
    select e.*,d.dname,d.loc
    from emp e 
    join dept d on e.deptno=d.deptno;
15. 查询Dallas市所有的员工信息
    select e.*
    from emp e 
    join dept d
    on e.deptno=d.deptno
    where d.loc='dallas';
16. 计算每个城市的员工数量
    select d.loc,count(e.empno)
    from emp e 
    right join dept d on e.deptno=d.deptno 
    group by d.loc;
17. 查询员工信息和他的主管姓名  凡是自关联 把一张表当成两张表
    select e.ename,m.ename
    from emp e 
    join emp m on e.mgr = m.empno;
18. 员工信息，员工主管名字，部门名
    select e.ename,m.ename,d.dname
    from emp e 
    join emp m on e.mgr = m.empno
    join dept d on e.deptno=d.deptno;
19. 员工名和他所在部门名
    select e.ename,d.dname
    from emp e 
    join dept d on e.deptno=d.deptno;
20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
    select e.ename,m.empno,m.ename,m.job,m.sal
    from emp e 
    left join emp m on e.mgr=m.empno;
21. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
    select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc
    from emp e 
    left join dept d on e.deptno=d.deptno
    where e.ename not like '%k%';
22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
    select d.*,e.empno,e.ename,e.job,e.sal
    from emp e 
    right join dept d on e.deptno=d.deptno;