课程回顾

1. 数学相关函数
2. 向下取整 floor()
3. 四舍五入 round()
4. 非四舍五入 truncate()
5. 随机数 rand() 0-1
6. 分组查询 group by
7. having 后面写聚合函数的条件 需要结合group by使用 where后面写普通字段的条件
8. 子查询：嵌套在另外一条sql语句中的查询语句称为子查询，可以嵌套n层
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. 如果查询一张表的全部数据和另外一张表的交集数据使用外链接(左外和右外都可以)
17. 笛卡尔积：关联查询不写关联关系则会得到两张表的乘积，这个乘积称为笛卡尔积

作业：

1. 每个部门的人数，根据人数降序排序 select deptno,count(*) c from emp group by deptno order by c desc;
   • 正确写法 select d.deptno,count(e.empno) c from emp e right join dept d on e.deptno=d.deptno group by d.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 deptno from emp group by deptno having count(*)<=3;
   • 通过部门编号查部门信息 select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3);
   • 包含40号部门的写法： select d.* from emp e right join dept d on e.deptno=d.deptno group by d.deptno having count(e.empno)<=3;

6. 拿最低工资的员工信息 select * from emp where sal=(select min(sal) from emp);

7. 只有一个下属的主管信息 -得到主管编号 select * 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. 每月发工资最多的部门信息

   • 得到部门最高的工资总和 select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1;
   • 通过最高的工资总和查询部门编号 select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1);
   • 通过部门编号得到部门详情 select * from dept where deptno in(上面一坨);

9. 下属最多的人，查询其个人信息 -得到最多的人数 select count() from emp group by mgr order by count() desc limit 0,1; -通过最大人数得到领导的编号 select mgr from emp group by mgr having count(*)=(select count() from emp group by mgr order by count() desc limit 0,1);

   • 通过领导编号得到个人详情 select * from emp where empno in(上面一坨);

10. 拿最低工资员工的同事信息 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);

11. 和最后入职的员工在同一部门的员工信息 select max(hiredate) from emp; select deptno from emp where hiredate=(select max(hiredate) from emp); select * from emp where deptno=(第二坨) and hiredate!=(第一坨);

12. 查询平均工资高于20号平均工资的部门信息 select avg(sal) from emp where deptno=20;

    select d.* from emp e join dept d on e.deptno=d.deptno group by d.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.ename) 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 from emp e left join emp m on e.mgr = m.empno;

21. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址 insert into emp (empno,ename) values(10010,'苍老师');

    select e.empno,e.ename,e.job,d.* 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;

表设计之关联关系

一对一

• 什么是一对一：有A和B两张表，A表中的一条数据对应B表中的一条数据，同时B表的一条数据也对应A表的一条，称为一对一的关系。
• 应用场景： 用户表和用户信息扩展表 ，商品表和商品详情表
• 如何建立一对一的关系：在从表中添加外键指向主表的主键建立关系
• 练习：创建用户表和用户详情表并保存以下信息 user:id,username,password userinfo:user_id,nick,qq
• 创建表 create table user(id int primary key autoincrement,username varchar(10),password varchar(10)); create table userinfo(userid int,nick varchar(10),qq varchar(15)); 用户名 密码 昵称 qq libai admin 小白白 112233 liubei 123456 刘皇叔 667788 diaochan 112233 媳妇儿 998877 -插入数据： insert into user (username,password) values('libai','admin'),('liubei','123456'),('diaochan','112233'); insert into userinfo values(1,'小白白','112233'),(2,'刘皇叔','667788'),(3,'媳妇儿','998877');
• 查询每个用户的用户名，昵称和qq select u.username,ui.nick,ui.qq from user u join userinfo ui on u.id=ui.user_id;
• 查询小白白的用户名和密码 select u.username,u.password from user u join userinfo ui on u.id=ui.user_id where ui.nick='小白白';
• 查询貂蝉的所有信息 select * from user u join userinfo ui on u.id=ui.user_id where u.username='diaochan';

一对多

• 什么是一对多：有AB两张表：A表中的一条数据对应B表的多条数据，同时B表的一条数据对应A表的一条
• 应用场景：商品表和分类表 员工表和部门表
• 如何建立关系： 在两张表中多的表中添加外键指向另外一张表的主键
• 练习： 创建数据库db5并使用，创建员工表和部门表并保存以下数据 emp:id name dept_id dept:id name

• 创建表： create database db5; use db5; create table emp(id int primary key autoincrement,name varchar(10),deptid int); create table dept(id int primary key auto_increment,name varchar(10));

  神仙部的员工 悟空和八戒 妖怪不的员工 蜘蛛精和白骨精

• 插入数据： insert into dept values(null,'神仙'),(null,'妖怪'); insert into emp values(null,'悟空',1),(null,'八戒',1),(null,'蜘蛛精',2),(null,'白骨精',2);
• 查询每个部门对应的员工姓名 select d.name,e.name from emp e join dept d on e.dept_id=d.id;
• 查询八戒的部门名称 select d.name from emp e join dept d on e.dept_id=d.id where e.name='八戒';
• 查询妖怪部有谁 select e.name from emp e join dept d on e.dept_id=d.id where d.name='妖怪';

多对多

• 什么是多对多：AB两张表，A表中一条数据对应B表多条，同时B表中一条数据对应A表多条称为多对多
• 应用场景： 老师表和学生表
• 如何建立关系：创建一张关系表，在关系表中添加两个外键指向另外两张表的主键
• 练习：创建老师和学生表保存以下信息
• 创建表：teacher：id name student：id name ts:tid,sid create table teacher(id int primary key autoincrement,name varchar(10)); create table student(id int primary key autoincrement,name varchar(10)); create table ts(tid int,sid int);
• 保存以下数据： 苍老师：小明 小红 小绿 小黄 王老师：小明 小红 insert into student values(null,'小明'),(null,'小红'),(null,'小绿'),(null,'小黄'); insert into teacher values(null,'苍老师'),(null,'王老师'); insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2);
• 查询每个老师姓名和对应的学生姓名 select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s
  on s.id=ts.sid;
• 查询苍老师的学生姓名 select s.name from teacher t join t_s ts on t.id=ts.tid join student s
  on s.id=ts.sid where t.name='苍老师';
• 查询小明的老师都有谁 select t.name from teacher t join t_s ts on t.id=ts.tid join student s
  on s.id=ts.sid where s.name='小明';

表设计案例： 权限管理

• 三张主表： 用户表，角色表，权限表
• 关系表： 用户角色关系表 角色权限关系表
• 创建表： create table user(id int primary key autoincrement, name varchar(10)); create table role(id int primary key autoincrement, name varchar(10)); create table module(id int primary key auto_increment, name varchar(10)); create table ur(uid int,rid int); create table rm(rid int,mid int);
• 保存以下数据： 用户(苍老师，小明，克晶老师) 角色（男游客，男管理员，女会员，女管理员）权限（男浏览，男发帖，男删帖，女浏览，女发帖，女删帖） insert into user values(null,'苍老师'),(null,'小明'),(null,'克晶老师'); insert into role values(null,'男游客'),(null,'男管理员'),(null,'女会员'),(null,'女管理员'); insert into module values(null,'男浏览'),(null,'男发帖'),(null,'男删帖'),(null,'女浏览'),(null,'女发帖'),(null,'女删帖');
• 用户和角色关系：苍老师（男管理员，女管理员）小明（女会员）克晶老师（女管理员，男游客） insert into u_r values(1,2),(1,4),(2,3),(3,1),(3,4);
• 角色和权限关系：男游客（男浏览）男管理员（男浏览，男发帖，男删帖）女会员（女浏览，女发帖），女管理员（女浏览，女发帖，女删帖） insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(3,5),(4,4),(4,5),(4,6);
• 查询每个用户名称和拥有的权限名称 select u.name,m.name from user u join ur ur on u.id=ur.uid join rm rm on rm.rid=ur.rid join module m on rm.mid=m.id;
• 查询苍老师的权限有哪些 select u.name,m.name from user u join ur ur on u.id=ur.uid join rm rm on rm.rid=ur.rid join module m on rm.mid=m.id where u.name='苍老师';