复习

• GROUP BY 语句

• HAVING 子句

  -- 查询每个分类商品所对应的库存总量中,高于1000的总量select category_id,sum(num) sfrom t_itemgroup by category_idhaving s>1000;-- 查询每个分类商品所对应的平均单价中,低于100的均价    select category_id,avg(price) a    from t_item    group by category_id    having a<100;-- 查询编号238和编号917分类商品的平均单价    select category_id,avg(price)    from t_item    where category_id in (238,917)    group by category_id;

   

having子句中经常跟聚合函数经常使用，如果没有使用到聚合函数，就要注意是否可以写在where条件中。

• 子查询

  1.案例：查询emp表中姓名是‘KING’所属的部门的编号，名称    select * from dept     where deptno=(select deptno from emp     where ename='king');2.案例：查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号    select * from emp    where deptno=(select deptno from dept    where dname='sales');3.案例：查询部门地址是DALLAS的部门下所有员工的所有信息    select * from empwhere deptno = (select deptno from deptwhere loc='dallas');4.案例：查询跟JONES同样工作的员工的所有信息（包含JONES）    select * from emp    where job=(select job from emp    where ename='jones') and ename!='jones';

   

• 关联查询数据

  • 使用where关联查询

    1.查看在new york工作的员工select * from empwhere deptno = (select deptno from deptwhere loc='new york');

     

  • 使用内连接的关联查询

    1.查看在new york工作的员工    select *    from emp join dept    on emp.deptno=dept.deptno    where dept.loc='new york';2.查看工资高于3000的员工，名字，工资，部门名，所在地select e.ename,e.sal,d.dname,d.locfrom emp e join dept don e.deptno=d.deptnowhere e.sal>3000;3.查询emp表中员工的编号，姓名，职位以及所属部门的编号，名称 select emp.empno,emp.ename,emp.job,emp.deptno,dept.dname from emp join dept on emp.deptno=dept.deptno 4.查询emp表中员工的编号，姓名，入职时间以及所属部门的名称和地址select emp.empno,emp.ename,emp.hiredate,emp.deptno,dept.locfrom emp join depton emp.deptno=dept.deptno

     

• 外连接

  • 左外连接

  • 右外连接

  -查询hero表中 各职业（type）中 平均年龄最高的 职业名称和平均年龄

  1. 第一种实现方案： 通过limit得到一条最高的

     select type,avg(age) a from hero group by type order by a desc limit 0,1;

      

     任何查询语句查询出来的结果都可以作为一张新表

  2. 第二种实现方案：通过子查询的嵌套 select type,avg(age) a from hero group by type having a=(select max(a) from (select type,avg(age) a from hero group by type) n);

  上面第二种实现 因为平均年龄是个小数 所以会出现误差，如果想解决需要去掉小数点后面某位小数后进行比较 ，但是此种方案 不可取 太麻烦

  -查询每个职业（type） 英雄总金额 最大的职业，并且显示结果只要职业的名称 -第一种解决方案 select type from hero group by type order by sum(money) desc limit 0,1 -第二种解决方案 select type from (select type,sum(money) s from hero group by type order by s desc limit 0,1)n;

  -查询各种分类中 平均价格最高的分类 显示 分类的id和分类的名称

  1. 先得到分类的平均价格
     select categoryid,avg(price) a from titem group by category_id

  2. 得到最高的平均价格，直接在上面代码上排序去第一条 select categoryid,avg(price) a from titem group by category_id

     order by a desc limit 0,1 3.把查询出来的结果当成一张新表 select c.id,c.name from (select categoryid,avg(price) a from titem group by categoryid order by a desc limit 0,1)n join titemcategory c on n.categoryid=c.id

  -查询 每个分类下 库存总量 最少的分类 该分类下的所有商品的信息

  select category_id,sum(num) s from t_itemwhere category_id is not nullgroup by category_idorder by slimit 0,1select * from (select category_id,sum(num) s from t_itemwhere category_id is not nullgroup by category_idorder by slimit 0,1)n join t_itemon n.category_id=t_item.category_id

   

• 练习

  1. 每个部门的人数,根据人数排序 select deptno,count(*) c from emp group by deptno order by c;
  2. 每个部门中，每个主管的手下人数 select deptno,mgr,count(*) from emp 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 deptno,count(*) c from emp group by deptno having c<=3;

  6. 拿最低工资的员工信息

     select * from emp where sal=(select min(sal) from emp);

  7. 只有一个下属的主管信息

     select e.* from (select mgr,count(*) c from emp group by mgr having c=1)n join emp e on n.mgr=e.empno;

  8. 平均工资最高的部门编号

     select deptno from emp group by deptno order by avg(sal) desc limit 0,1

  9. 下属人数最多的人，查询其个人信息

     select * from emp where empno=(select mgr from emp group by mgr order by count(*) desc limit 0,1);

  10. 拿最低工资的人的信息

      select * from emp where sal=(select min(sal) from emp);

  11. 最后入职的员工信息

      select * from emp where hiredate = (select max(hiredate) from emp);

  12. 工资多于平均工资的员工信息

      select * from emp where sal>(select avg(sal) from emp);

  13. 查询员工信息，部门名称

      select emp.*,dept.dname from emp join dept on emp.deptno=dept.deptno;

  14. 员工信息，部门名称，所在城市

      select emp.*,dept.dname,dept.loc from emp join dept on emp.deptno=dept.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(*) from emp e join dept d on e.deptno=d.deptno group by d.loc

  17. 查询员工信息和他的主管姓名

      select e.*,m.ename from emp e join emp m on e.mgr=m.empno

  18. 员工信息，员工主管名字，部门名 1.直接在第17题后面join 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; 2.把第17题查询出来的数据当成一张新表 select n.ename,n.mname,d.dname from (select e.ename,e.deptno,m.ename mname from emp e join emp m on e.mgr=m.empno)n join dept d on n.deptno=d.deptno

  19. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及该员工上级领导的编号，姓名，职位，工资 SELECT e.EMPNO,e.ENAME,e.SAL,e.JOB,m.EMPNO,m.ENAME,m.JOB,m.SAL FROM emp e JOIN emp m ON e.MGR=m.EMPNO

  20. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址

      select * from emp e join dept d on e.deptno=d.deptno where e.ename not like '%k%';

  21. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资

      select * from dept d left join emp e on d.deptno=e.deptno

自关联查询

如果一张表中保存的数据存在层级关系如：员工和直属上级领导、分类和上级分类、部门和直属部门注意事项：查询数据时 把一张表当成两张表用

一对一关联

user                    userinfoid 用户名 密码     id 性别 年龄 住址 电话 邮箱 工资 年龄 用户表和用户信息表

一对多关联

部门和员工 商品和分类学生和班级：**一对多的关系，在多的表中添加一个关联关系的字段**

多对多关联

老师表 和 学生表**多对多必须使用中间表保存两张表之间的关系**

设连接方式和关联关系的区别

-连接方式：     使用sql语句查询存在关联关系的表的数据的时候 使用的查询方式 包括：内连接  外连接（左、右）-关联关系：      数据表之间存在的逻辑关系 包括：1对1  1对多  多对多

数据库设计之权限管理

用户表    角色表   模块表

什么是权限管理

每个用户会有不同的权限 通过 用户 角色 模块 和 用户角色关系表、角色模块关系表来保存每个用户所对应的模块信息

如何设计多对多关系的表

查询用户名为 小明 的模块名 1.先从user表中查询出小明的userid 2.通过userid 去 ur表中查询 多个roleid 3.通过roleid 去 rm表中查询 多个moduleid 4.通过moduleid 去 module表中查询 模块名select modulename from module where moduleid in(select moduleid from rm where roleid in (select roleid from ur where userid=(select user_id from user where name='小明')));-面试题：1.创建流水表 和 人物表 create table trade (id int primary key auto_increment,money double,type varchar(10),date timestamp,pid int);create table person(id int primary key auto_increment,sex char(5),name varchar(10),rel varchar(10));第三题： SELECT SUM(money) FROM trade WHERE DATE > STRTODATE('2018-02-15','%Y-%m-%d');第四题： SELECT p.name,p.sex,t.money FROM trade t JOIN person p ON t.pid=p.id WHERE p.sex='女' AND DATE > STRTODATE('2018-02-15','%Y-%m-%d') AND p.rel='亲戚' AND money>100;第五题：select type,sum(money) from trade where money>0 group by type