MySql学习笔记05

复习

GROUP BY 语句

HAVING 子句

-- 查询每个分类商品所对应的库存总量中,高于1000的总量
select category_id,sum(num) s
from t_item
group by category_id
;

-- 查询每个分类商品所对应的平均单价中,低于100的均价

    select category_id,avg(price) a
    from t_item
    group by category_id
    ;

-- 查询编号238和编号917分类商品的平均单价

    select category_id,avg(price)
    from t_item
    ,)
    group by category_id;

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

子查询

.案例：查询emp表中姓名是‘KING’所属的部门的编号，名称
    select * from dept
    where deptno=(select deptno from emp
    where ename='king');

.案例：查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号

    select * from emp
    where deptno=(select deptno from dept
    where dname='sales');

.案例：查询部门地址是DALLAS的部门下所有员工的所有信息

    select * from emp
where deptno = (select deptno from dept
where loc='dallas');

.案例：查询跟JONES同样工作的员工的所有信息（包含JONES）
    select * from emp
    where job=(select job from emp
    where ename='jones') and ename!='jones';

关联查询数据

使用where关联查询

.查看在new york工作的员工
select * from emp
where deptno = (select deptno from dept
where loc='new york');

使用内连接的关联查询

.查看在new york工作的员工
    select *
    from emp join dept
    on emp.deptno=dept.deptno
    where dept.loc='new york';

.查看工资高于3000的员工，名字，工资，部门名，所在地

select e.ename,e.sal,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno
;
.查询emp表中员工的编号，姓名，职位以及所属部门的编号，名称 .查询emp表中员工的编号，姓名，入职时间以及所属部门的名称和地址

select emp.empno,emp.ename,emp.hiredate,emp.deptno,dept.loc
from emp join dept
on emp.deptno=dept.deptno

外连接
- 左外连接
- 右外连接
-查询hero表中各职业（type）中平均年龄最高的职业名称和平均年龄
1. 第一种实现方案：通过limit得到一条最高的
```
,; 
```
  任何查询语句查询出来的结果都可以作为一张新表
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_item
where category_id is not null
group by category_id
order by s
limit ,

select * from
(select category_id,sum(num) s
from t_item
where category_id is not null
group by category_id
order by s
limit ,)n join t_item
on 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                    userinfo
id 用户名 密码     id 性别 年龄 住址 电话 邮箱 工资 年龄
用户表和用户信息表

一对多关联

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

多对多关联

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

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

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

数据库设计之权限管理

用户表    角色表   模块表

什么是权限管理

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

如何设计多对多关系的表

查询用户名为 小明 的模块名 .先从user表中查询出小明的userid .通过userid 去 ur表中查询 多个roleid .通过roleid 去 rm表中查询 多个moduleid .通过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='小明')));

-面试题：

.创建流水表 和 人物表 ),date timestamp,pid int);

),name ),rel ));

第三题： SELECT SUM(money) FROM trade WHERE DATE > STRTODATE('2018-02-15','%Y-%m-%d');

第四题： ;

第五题：

 group by type

巴特西

MySql学习笔记05

复习

自关联查询

一对一关联

一对多关联

多对多关联

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

数据库设计之权限管理

什么是权限管理

如何设计多对多关系的表

最新文章

热门文章