课程回顾

1. is null 和 is not null
2. 别名
3. distinct 去重
4. 比较运算符 > < >= <= = !=和<>
5. and 和 or
6. not in
7. not between x and y
8. 模糊查询 like _单个未知字符 %0或多个未知字符
9. 排序 order by 字段名 asc/desc,字段名;
10. 分页 limit 跳过条数,请求条数、每页条数
11. 数值计算 + - * / % mod
12. 日期： now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now())，日期格式化 dateformat(日期,格式) 格式：%YymcdHhis strto_date(字符串时间,格式)
13. ifnull(x,y) 如果x值为null则取y 不为null则取x
14. 聚合函数： 平均值avg 最大值max 最小值min 求和sum 计数count
15. 字符串相关：
16. 拼接 concat()
17. 获取长度 char_length()
18. 获取字符串出现的位置 instr(str,substr) locate(substr,str)
19. 插入字符串 insert(str,start,length,newStr)
20. 转大小写 upper() lower()
21. 去空白 trim()
22. 截取 left() right() subtring()
23. 重复 repeat()
24. 替换 replace()
25. 反转 reverse()

作业

1. 案例：查询没有上级领导的员工的编号，姓名，工资 select empno,ename,sal from emp where mgr is null;
2. 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金 select ename,job,sal,comm from emp where comm=0;
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 titem where categoryid=163;

16. 案例：查询 类别 163 的商品 select * from titem where categoryid=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,price from titem where title like '%力%';

25. 案例：查询卖点含有'赠'产品名称 select title from titem where sellpoint like '%赠%';

26. 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。 select empno,ename,job,sal from emp where sal between 1000 and 2000;

27. 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号 select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;

28. 案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。 select empno,ename,job,sal from emp where ename like '%e%' and job!='manager';
29. 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号 select empno,ename,deptno from emp where deptno in(10,20);
30. 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金 select empno,ename,job,comm from emp where comm=0 or ename not like '%t_';
31. 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号 select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;
32. 案例：查询不是30号部门的员工的所有信息 select * from emp where deptno!=30;
33. 案例：查询奖金不为空的员工的所有信息 select * from emp where comm is not null;
34. 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列 select empno,ename,job from emp order by empno desc;
35. 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列 select ename,job,sal from emp where deptno in(10,30) order by sal;
36. 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列 select * from emp order by deptno,empno desc;

37. 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。 select empno,ename,sal,deptno from emp where sal>1000 or mgr is null order by deptno desc,sal;

38. 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列 select empno,ename,sal,comm from emp where ename not like '%s%' order by sal,empno desc;

39. 案例：统计emp表中员工的总数量 select count(*) from emp;
40. 案例：统计emp表中获得奖金的员工的数量 select count(*) from emp where comm>0;
41. 案例：求出emp表中所有的工资累加之和 select sum(sal) from emp;

42. 案例：求出emp表中所有的奖金累加之和 select sum(comm) from emp;

43. 案例：求出emp表中员工的平均工资 select avg(sal) from emp;

44. 案例：求出emp表中员工的平均奖金 select avg(comm) from emp;
45. 案例：求出emp表中员工的最高工资 select max(sal) from emp;
46. 案例：求出emp表中员工编号的最大值 select max(empno) from emp;
47. 案例：查询emp表中员工的最低工资。 select min(sal) from emp;

48. 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。 select count(*) 人数,sum(sal) 工资总和,avg(sal) 平均工资,max(comm) 最高奖金,min(comm) 最小值 from emp;

    53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。 54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。 55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列

    56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资 select empno,ename,job,sal from emp where sal between 1000 and 3000; 57.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金 select empno,ename,sal,comm from emp where comm between 500 and 2000; 58.案例：查询员工的编号是7369，7521， select * from emp where empno in(7369,7521); 59.案例：查询emp表中，职位是ANALYST， select * from emp where job='analyst'; 60.案例：查询emp表中职位不是ANALYST, select * from emp where job!='analyst';

数学相关的函数

1. 向下取整 floor(num) select floor(3.14);
2. 四舍五入 round(num) select round(23.8);
3. round(num,m) select round(23.879,2);
4. 非四舍五入 truncate(num,m) select truncate(23.879,2);
5. 随机数 rand() 0-1 select rand();
6. 获取0-5的整数随机数 select floor(rand()*6);
7. 3-5的随机数

分组查询 group by

1. 查询每个部门的平均工资 select deptno,avg(sal) from emp group by deptno;
2. 查询每个部门的工资总和 select deptno,sum(sal) from emp group by deptno;
3. 查询每种职业的最高工资 select job,max(sal) from emp group by job;
4. 查询每个领导下的人数 select mgr,count(*) from emp where mgr is not null group by mgr;
5. 查询每个部门工资大于1000的员工数量 select deptno,count(*) from emp where sal>1000 group by deptno;
6. 多字段分组查询 只需要在group by后面写多个字段名通过逗号分隔

7. 每个部门每个主管的手下人数 select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;

   53.案例：查询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;

   54.案例：查询工资在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; 55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列 select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null group by job order by c desc,a;

having

• where后面只能写普通字段的条件 不能写聚合函数的条件
• having和where类似都是用于添加条件的，having后面可以写普通字段的条件也可以写聚合函数的条件，但是建议写聚合函数的条件，而且要结合group by 使用
• 查询每个部门的平均工资，要求平均工资大于2000 -错误写法 where后面不能写聚合函数 select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno; -正确写法： select deptno,avg(sal) a from emp group by deptno having a>2000;
• 查询商品表中每个分类的平均单价，要求平均单价小于100 select categoryid,avg(price) a from titem group by category_id having a<100;
• 查询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 desc;
• 查询emp表中平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序 select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc;
• 查询emp表中不是以s开头的职位，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序，如果人数一致则根据工资总和降序 select job,count(*) c,sum(sal) s,max(sal) from emp where job not like 's%' group by job having avg(sal)!=3000 order by c, s desc;
• 查询每年入职的人数(扩展) select extract(year from hiredate) year,count(*) from emp group by year;
• 查询最高平均工资的部门编号（扩展） （并列第一的问题不能解决） select deptno from emp group by deptno order by avg(sal) desc limit 0,1;

子查询（嵌套查询）

• 可以在查询语句中嵌套另一条sql语句
• 可以嵌套n层
• 查询emp表中工资最高的员工信息 select max(sal) from emp; select * from emp where sal=(select max(sal) from emp);
• 查询emp表中工资大于平均工资的员工信息 select avg(sal) from emp; select * from emp where sal>(select avg(sal) from emp);
• 查询工资高于20号部门最高工资的员工信息 select max(sal) from emp where deptno=20; select * from emp where sal>(select max(sal) from emp where deptno=20);
• 查询和jones相同工作的其它员工信息 select job from emp where ename='jones'; select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
• 查询工资最低的员工的同事们的信息(同事=相同job) -得到最低工资 select min(sal) from emp;
  • 得到拿最低工资员工的职位 select job from emp where sal=(select min(sal) from emp);
  • 通过职位得到此职位的员工信息排除最低工资那个哥们儿 select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
• 查询最后入职的员工信息 select max(hiredate) from emp; select * from emp where hiredate=(select max(hiredate) from emp);
• 查询员工king所属的部门编号和部门名称（需要用到部门表dept） select deptno from emp where ename='king'; select deptno,dname from dept where deptno=(select deptno from emp where ename='king');
• 查询有员工的部门信息(查询在员工表中出现的部门的信息) -得到员工表中的部门编号 select distinct deptno from emp; -查询上面结果对应的部门详情 select * from dept where deptno in (select distinct deptno from emp);
• 查询平均工资最高的部门信息(难度最高 需要考虑并列第一问题) -查询最高的平均工资 select avg(sal) a from emp group by deptno order by a desc limit 0,1; -通过最高的平均工资找到对应的部门编号 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); -通过部门编号查询部门信息 select * from dept where deptno in(上面一坨);
• 子查询总结：
• 嵌套在sql语句中的查询语句称为子查询
• 子查询可以嵌套n层
• 子查询可以写在什么位置？
• 写在where和having的后面 当做查询条件的值
• 写在创建表的时候 -格式： create table 表名 as (子查询) create table newemp as (select * from emp where deptno=10);
• 写在from后面当成一个虚拟表 必须有别名 select ename from (select * from emp where deptno=10) newtable;

关联查询

• 同时查询多张表的查询方式称为关联查询
• 查询每一个员工的姓名和其对应的部门名称 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
• 查询在new york工作的所有员工信息 select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='new york';

笛卡尔积

• 关联查询必须写关联关系，如果不写则得到两张表的乘积，这个乘积称为笛卡尔积
• 工作中不允许出现因为 超级耗内存 有可能直接崩溃

等值连接和内连接

• 等值连接和内连接都是关联查询的查询方式
• 等值连接和内连接查询到的结果一样，都为两张表的交集数据
• 等值连接： select * from A,B where A.x=B.x and A.age=18;
• 内连接：select * from A 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;
• 查询在new york工作的所有员工信息 select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';

外链接

• 格式：select * from A left/right 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. 数学相关：
2. 向下取整 floor()
3. 四舍五入 round()
4. 非四舍五入 turncate()
5. 随机数 rand()
6. 分组查询 group by
7. having 和group by结合使用 having后面写聚合函数的条件 where写普通字段的条件
8. 子查询：写在sql语句中的查询语句称为子查询
9. 子查询可以写在的位置：
   1. 在where和having的后面 当做查询条件的值
   2. 写在创建表的时候 把结果保存到一张新表中
   3. 写在from后面当成一张虚拟表 要起别名
10. 关联查询
11. 查询方式：等值连接、内连接、外链接
12. 等值连接： select * from A,B where A.x=B.x and A.age=18;
13. 内连接：select * from A join B on A.x=B.x where A.age=18;
14. 外连接：select * from A left/right join B on A.x=B.x where A.age=18;
15. 等值连接和内连接查询的都是交集数据
16. 外链接查询的是一张表的全部数据和另外一张表的交集数据

作业： 1. 每个部门的人数，根据人数降序排序 2. 每个部门中，每个主管的手下人数 3. 每种工作的平均工资 4. 每年的入职人数 5. 少于等于3个人的部门信息 6. 拿最低工资的员工信息 7. 只有一个下属的主管信息 8. 每月发工资最多的部门信息 9. 下属最多的人，查询其个人信息 10. 拿最高工资员工的同事信息 11. 和最后入职的员工在同一部门的员工信息 12. 查询平均工资高于20号平均工资的部门信息 13. 查询员工信息和员工对应的部门名称 14. 查询员工信息，部门名称，所在城市 15. 查询Dallas市所有的员工信息