4.14

4.14

子查询和连表查询

use school;
– 1.子查询
– 将一个查询的结果作为另外一个查询的条件或者查询对象

– 用法一：将一个查询结果作为另外一个查询的条件
– 获取分数最高的学生的学号
– 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; – 查询条件

– 练习：获取所有的学生的姓名、学院名称和平均分
– 张三 计算机学院 89
– 方法一:
select stuname, collname, avg(score) as avg_score from tb_student, tb_college, tb_record
where
tb_student.collid=tb_college.collid
and
tb_student.stuid=tb_record.sid
group by sid;

– 方法二:
– select sid, avg(score) as avg_s from tb_record group by sid;
select stuname, collname, avg_s from tb_student t1, tb_college t2, (select sid, avg(score) as avg_s from tb_record group by sid) t3
where
t1.collid=t2.collid
and
t1.stuid=t3.sid;

– 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
use school;
select * from tb_student,tb_record,tb_course
where stuid=sid and cid=couid;

– 创建视图
– 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’@’%’;

homework

– 查询年龄最大的学生的姓名
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_course, tb_student, tb_record where couid=cid and stuid=sid;
– 查询学生姓名、课程名称以及成绩按成绩从高到低查询第11-15条记录
select stuname, couname, score from tb_course, tb_student, tb_record where couid=cid and stuid=sid order by (score) desc limit 10,5;
– 查询选课学生的姓名和平均成绩(连接查询，子查询)
select stuname, avg(score) from tb_student, tb_record where sid=stuid group by sid;
select stuname, avg_score from tb_student, (select sid, avg(score) avg_score from tb_record group by sid) as t1 where stuid=sid;
– 查询每个学生的姓名和选课数量
select stuname, count(sid) 选课数量 from tb_student, tb_record where stuid=sid group by sid;

– 查询月薪最高的员工姓名和工资
select ename, sal + ifnull(comm,0) 工资 from tb_emp where sal + ifnull(comm,0) = (select max(sal + ifnull(comm,0)) 工资 from tb_emp);
– 查询员工的姓名和年薪((月薪+补贴)*13)
select ename, (ifnull(comm,0) + sal) * 13 年薪 from tb_emp;
– 查询有员工的部门的编号和人数
select dno, count(dno) 人数 from tb_emp group by dno;
– 查询所有部门的名称和人数
select dname, ifnull(num, 0) 人数 from tb_dept left join (select dno, count(dno) num from tb_emp group by dno) as t1 on tb_dept.dno=t1.dno;
– 查询月薪最高的员工(Boss除外)的姓名和工资
select ename, ifnull(comm,0) + sal 工资 from tb_emp where (select max(ifnull(comm,0)+sal) from tb_emp where job <> ‘总裁’) = ifnull(comm,0) + sal;
– 查询薪水超过平均薪水的员工的姓名和工资
select ename, ifnull(comm,0) + sal 工资 from tb_emp where ifnull(comm,0)+sal > (select avg(ifnull(comm,0)+sal) from tb_emp);
– 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资
select ename, t1.dno, ifnull(comm,0)+sal 工资, avg_sal from (select dno, avg(ifnull(comm,0)+sal) avg_sal from tb_emp group by dno) as t1, tb_emp where t1.dno=tb_emp.dno and ifnull(comm,0)+sal > avg_sal;
– 查询部门中薪水最高的人姓名、工资和所在部门名称
select max(ifnull(comm,0)+sal) ssal, dname from tb_emp, tb_dept where tb_emp.dno=tb_dept.dno group by tb_emp.dno;
select ename, ssal, dname from tb_emp, (select max(ifnull(comm,0)+sal) ssal, dname from tb_emp, tb_dept where tb_emp.dno=tb_dept.dno group by tb_emp.dno) as t1 where ifnull(comm,0)+sal = ssal;
– 查询主管的姓名和职位
select ename, job from tb_emp where job<>‘总裁’ group by mgr;
– 查询月薪排名4~6名的员工姓名和工资
select ename, ifnull(comm,0) + sal 工资 from tb_emp order by (ifnull(comm,0) + sal) limit 3,3;