数据库day07

having where后面只能写普通的字段条件，聚合函数的条件不能写在where后面 如果要查询数据时，条件为聚合函数的条件则使用having关键字，此 关键字还要结合group by使用 having写在group by后面。

1.查询每个部门的平均工资要求工资大于2000 select deptno,avg(sal) a from emp group by deptno having a>2000; 2.查询每个部门的人数，只查询部门人数为3的 select deptno,count(*) c from emp group by deptno having c=3; 3.查询每个部门的平均工资，只查询工资在1000-3000之间的，并且 过滤掉平均工资低于2000的部门 select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000;

子查询（嵌套查询） 查询工资高于1号部门平均工资的员工信息 1号部门的平均工资 select avg(sal) from emp where deptno=1; select * from emp where sal> (select avg(sal) from emp where deptno=1);

查询工资最高的员工信息 select * from emp where sal= (select max(sal) from emp); 查询和孙悟空相同工作的其他员工信息 得到孙悟空的工作： select job from emp where ename='孙悟空'; select *from emp where job = (select job from emp where ename='孙悟空'); 查询拿最低工资员工的同事们的信息(同事指的是同一部门) 得到最低工资：select min(sal) from emp; 通过最低工资得到部门编号: select deptno from emp where sal =(select min(sal) from emp)； select *from emp where deptno = ( select deptno from emp where sal =(select min(sal) from emp)); 查询白骨精的部门信息(需要用到两张表) select deptno from emp where ename='白骨精'; select *from dept where deptno = ( select deptno from emp where ename='白骨精');

数学相关函数 floor(num) 向下取整 select floor(3.84); 值为3 round(num) 四舍五入 select round(23.8); 值为24 round(mum,m) 四舍五入 m代表小数位 select round(23.869,2); 23.87 truncate(mum,m) 非四舍五入 m代表小数位 select truncate(23.869,2);23.86 rand() 随机数 获取0-1的随机数 select rand(); 获取0-5的随机数 3-8 select floor(rand()6)+3; 获取8-10的随机数 0-2 12-20 select floor(rand()3)+8;

表设计 关联关系 一对一 什么是一对一:两张表A表和B表，A表中一条数据对应B表中的一条 数据，这种关系称为一对一的关系。 应用场景：用户表和用户信息扩展表 商品表和商品信息扩展表 如何建立关系：在从表中添加一个外键字段指向主表的主键字段

练习： 创建user(id,username,password)和userinfo(nick,qq,phone,userid) create table user(id int primary key auto_increment,username varchar(10),password varchar(10)); create table userinfo(nick varchar(10),qq varchar(10), phone varchar(13),user_id int); 两张表各插入3条数据 insert into user values(null,'wukong','admin'), (null,'bajie','admin'),(null,'wujing','admin'); insert into userinfo values('悟空','32123','13838384388',1), ('八戒','31123','13838384488',2),('沙僧','32124','13838384588',3); 关联查询 同时查询多张表的查询方式称为关联查询 关联查询的查询方式有：1.等值连接，2，内连接，3外连接 等值连接: 格式：select * from A,B where 关联关系 and 其他条件 查询每个用户的所有信息 select *from user u,userinfo uf where u.id = uf.user_id; 查询名字叫悟空的用户名和密码 select user.username,user.password from user ,userinfo where user.id = userinfo.user_id and userinfo.nick='悟空';

查询每个员工的姓名和对应的部门名 查询工资高于2000的员工姓名工资和部门名称，部门地点

一对多

什么是一对多:AB两张表，A表中一条数据对应B表中多条数据，B表中的一条数据对应A表中的一条数据，称为一对多。
应用场景：部门和员工，分类和商品
如何建立关系：一对多的两张表，在多的表中添加外键记录另外一个表主键值

内连接

内连接和等值连接作用是一样的，查询到的是两张表的交集部分
格式：select *from A join B on 关联关系 where 条件

练习：

1、查询每个部门对应的每个员工
select e.ename,d.dname from emp e join dept d on e.deptno=
d.deptno;
2、查询盘丝洞工作的所有员工姓名
select e.ename from emp e join dept d on e.deptno = d.deptno
where d.loc='盘丝洞';
select e.ename from emp e,dept d where e.deptno = d.deptno
and d.loc = '盘丝洞';
3、查询孙悟空的工作地点和部门名称
select d.loc,d.dname from emp e join dept d on e.deptno=d.deptno
where e.ename = '孙悟空';

练习：

1、查询工资高于2000的员工姓名，工资和部门名称，部门地点
select e.ename,e.sal,d.dname,d.loc from emp e join dept d
on e.deptno = d.deptno where e.sal>2000;
2、查询1号部门有领导的员工姓名和部门信息
select e.ename,d.* from emp e join dept d on e.deptno= d.deptno
where d.deptno=1 and e.mgr is not null;

多对多

1、什么是多对多:AB两张表，A表中一条数据对应B表多条数据，同时B表中的一条数据
对应A表中的多条数据称为多对多关系
2、应用场景：老师和学生，用户表和角色表
3、如何建立关系：需要创建单独个关系表，表中两个外键字段保存两个表的主键值。

练习：

创建三张表：
student(id,name)1,2,3,4,5
create table student(id int primary key auto_increment,
                     name varchar(10));
teacher(id,name)1,2
create table teacher(id int primary key auto_increment,
                    name varchar(10));
ts(sid,tid)1,1; 2,1; 3,1; 4,2; 5,2 1,2
create table ts(sid int,tid int);
插入以下数据：
苍老师对应的学生：小明，小红，小花
insert into teacher values(null,'苍老师'),(null,'刘老师');
刘老师对应的学生：小绿，小蓝，小明
insert into student values(null,'小明'),(null,'小红'),
(null,'小花'),(null,'小绿'),(null,'小蓝');
insert into ts values(1,1),(2,1),(3,1),(4,2),(5,2),(1,2);
​
查询每个老师对应的所有学生
select t.name,s.name from student s
join ts on s.id = ts.sid 
join teacher t  on t.id = ts.tid;
查询小明的老师都有谁
select t.name,s.name from student s join ts on s.id =ts.sid
join teacher t on t.id = ts.tid where s.name = '小明';
查询苍老师的所有学生
select s.name from student s 
join ts on s.id = ts.sid 
join teacher t on t.id = ts.tid
where t.name = '苍老师';

外连接

等值连接和内连接查询到的是两张表的交集部门，外连接查询到的是一张表的全部和另外一张表的交集数据
格式：select * from A left/right join B on 关联关系 where 条件。
join两边的表是可以交互位置的，所以只需要掌握一个左外连接就可以了。
​
往员工表中插入一条数据
insert into emp (empno,ename)values(20,'灭霸');
查询所有的员工姓名以及员工所对应的部门名
select e.ename,d.dname from emp e left join dept d
on e.deptno = d.deptno;