子查询、连表查询、DCL、视图使用

子查询、连表查询、DCL、视图使用

子查询

– 将一个查询的结果作为另外一个查询的条件或者查询对象

– 用法一：将一个查询结果作为另外一个查询的条件

-- 获取分数最高的学生的学号 
-- select max(score) from tb_record;
select sid from tb_record where score=(select max(score) from tb_record);

-- 获取分数大于90分的学生姓名 
-- select distinct sid from tb_record where score>90;
select stuname from tb_student where stuid in (select distinct sid from tb_record where score>90);

-- 练习：获取所有平均分高于80分的学生的姓名 
-- select sid from tb_record GROUP BY sid having avg(score) > 80;
select stuname from tb_student where stuid in (select sid from tb_record GROUP BY sid having avg(score) > 80);

用法二：将一个查询结果作为另外一个查询的查询对象，这个时候查询结果必须重命名

-- 查询平均分高于80分的学生的id以及对应的平均 
select sid, avg(score) as avg_s from tb_record group by sid having avg_s > 80 ;

-- select sid, avg(score) as avg_s from tb_record group by sid;
select * from (select sid, avg(score) as avg_s from tb_record group by sid) as t1 where avg_s > 80;

– 2.连表查询
– 连表本质就是将多张表的数据合并成一张表，然后再在合并后的表中进行数据查询

– 1）直接连表
– select * from 表名1,表名2,表名3 连接条件 查询条件;

-- 获取所有学生名和学生对应的学院名称 
select stuname, collname from tb_student, tb_college where tb_student.collid=tb_college.collid;

-- 获取所有男同学的姓名和对应的学院名称
select stuname, collname from tb_student, tb_college 
where tb_student.collid=tb_college.collid  -- 连表条件
and stusex=1;  -- 查询条件

– 2)内连接
– 内连接和直接连接只是写法不同，功能一模一样

select * from tb_student 
inner join
tb_college on tb_student.collid = tb_college.collid;

select * from tb_student
inner join 
tb_record on stuid=sid
inner join 
tb_course on cid=couid;

– 3)外连接
– a.左外连接：left outer join / left join
– b.右外连接：right outer join / right join
– c.全外连接： full outer join / full join

-- 练习：获取所有的学生的姓名、学院名称和平均分
-- ifnull(字段, 值): 如果指定字段值为空就返回指定值
-- round(数字, N): 让数字保留N位小数
use school;
select stuname, collname, round(ifnull(avg(score), 0), 2) as avg_s from tb_student
left join 
tb_record on stuid=sid 
left join 
tb_college on tb_student.collid=tb_college.collid
group by stuid;

DCL

– DCL: grant、revoke
– 1.创建用户
– create user 用户名@登录地址 identified by 密码;
– 用户名：自己取名字
– 登录地址：a.localhost - 只能在数据库所在主机上登录这个用户 b.IP地址 c. %

create user zhangsan@localhost identified by 'yuting123456';
create user 'lisi'@'118.122.119.%' identified by 'yuting123456';
create user 'xiaoming'@'%' identified by 'yuting123456';

– 2.删除用户

drop user zhangsan@localhost;
drop user 'lisi'@'118.122.119.%';
drop user 'xiaoming'@'%';

– 3.授权
– grant 权限类型 on 数据库.对象 to 用户名;

grant select on school.tb_student to zhangsan@localhost;
grant insert on school.tb_student to zhangsan@localhost;
-- 授权 school数据库中 tb_teacher表中所有数据操作的权限
grant all privileges on school.tb_teacher to zhangsan@localhost;

-- 授权 school数据库中所有的表的select权限
grant select on school.* to zhangsan@localhost;

– 4.召回权限

revoke delete on school.tb_student from zhangsan@localhost;

flush privileges;

视图的使用

– 1.简化频繁使用的复杂的sql

– 创建视图
– create view 视图名 as sql查询语句;
– 用法一：简化查询过程

create view vw_score as 
select * from tb_student,tb_record,tb_course 
where stuid=sid and cid=couid;

select stuname, avg(score) from vw_score GROUP BY stuid;

– 用法二：将表中的部分进行授权
– select stuname as 姓名, stuaddr as 家庭住址 from tb_student;

create view vw_studentInfo as 
select stuname as 姓名, stuaddr as 家庭住址 from tb_student;

create user 'yuting'@'%' identified by 'yuting123456';
grant select on school.vw_studentinfo to 'yuting'@'%';

作业

一

-- 查询年龄最大的学生的姓名
select stuname from tb_student where stubirth=(select min(stubirth) from tb_student);

-- 查询年龄最大的学生姓名和年龄
select stuname,DATEDIFF(CURDATE(),stubirth) div 365 from tb_student where stubirth=(select min(stubirth) from tb_student);

-- 查询选了两门以上的课程的学生姓名(子查询，分组，聚合)
select stuname from tb_student where stuid in (select sid from tb_record group by sid having count(sid)>2)

-- 查询学生姓名、课程名称以及成绩(连接查询)
select stuname,couname,score from tb_student,tb_record,tb_course where stuid=sid and cid=couid;

-- 查询学生姓名、课程名称以及成绩按成绩从高到低查询第11-15条记录
select stuname,couname,score from tb_student,tb_record,tb_course where stuid=sid and cid=couid ORDER BY score limit 10,5;

-- 查询选课学生的姓名和平均成绩(连接查询，子查询)
select stuname,avg(score) from tb_student,tb_record where stuid=sid group by stuid;

-- 查询每个学生的姓名和选课数量
select stuname,count(sid) from tb_student,tb_record where stuid=sid group by sid;

二

-- 查询月薪最高的员工姓名和工资
use hrs;
select ename,max(sal) from tb_emp;

-- 查询员工的姓名和年薪((月薪+补贴)*13)
select ename,(sal+ifnull(comm,0))*13 from tb_emp;

-- 查询有员工的部门的编号和人数
select tb_emp.dno,count(tb_emp.dno) from tb_dept,tb_emp where tb_dept.dno=tb_emp.dno group by tb_emp.dno;

-- 查询所有部门的名称和人数
select tb_dept.dno,count(tb_emp.dno) from tb_dept left join tb_emp on tb_dept.dno=tb_emp.dno group by tb_emp.dno;

-- 查询月薪最高的员工(Boss除外)的姓名和工资
select ename,max(sal) from (select ename,sal from tb_emp where job <> '总裁') as t1;
-- 查询薪水超过平均薪水的员工的姓名和工资
select ename,sal from tb_emp where sal > (select avg(sal) from tb_emp);

-- 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资ename,sal,tb_emp.dno,avg(sal)
select ename,dno,sal from (select ename,sal,tb_emp.dno,avg_sal from tb_emp,(select dno,avg(sal) as avg_sal from tb_emp group by dno) as t1 where tb_emp.dno=t1.dno) as t3 where sal>avg_sal;

-- 查询部门中薪水最高的人姓名、工资和所在部门名称
select ename,sal,dname,max_sal from (select ename,sal,dname,max_sal from tb_emp,tb_dept,(select dno,max(sal)as max_sal from tb_emp group by dno)as t1 where tb_emp.dno=tb_dept.dno and tb_emp.dno=t1.dno)as t2 where sal=max_sal;
-- 查询主管的姓名和职位
select ename,job from tb_emp where job like '%主管';

-- 查询月薪排名4~6名的员工姓名和工资
select ename,sal from tb_emp ORDER BY sal limit 3,3;