4-14总结

高级查询、DCL、视图

高级查询

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);

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

select sid,avg(score) as avg_score from tb_record group by sid;

select * from (select sid,avg(score) as avg_score from tb_record group by sid;) as t1 where avg_score >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;

练习：获取所有的学生的姓名、学院名称和平均分

f方法一：

select stuname,collname,avg(score) as avg_score from tb_student,tb_college,tb_record

where

tb_student.collid=rb_college.collid

and stuid=sid group by sid;

方法二：

select sid avg(score) as avg_score from tb_record group by sid;

select stuname,collname,avg_score from tb_student,tb_collage,(select sid avg(score) as avg_score from tb_record group by sid) as t1

where

tb_student.collid=tb_record.collid

and tb_student.stuid=t1.sid;

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

select * from tb_student

inner join

tb_college on tb_student.collid=tb_record.collid;

select * from tb_student

inner join

tb_record on stuid=sid

inner join

tb_college on cid=.couid;

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

– ifnull （字段，值）：如果指定字段为空就返回只当值
– round（数字，N）：让数字保留N位小数

– 练习：获取所有的学生的姓名、学院名称和平均分

select stuname,collname,round(ifnull(avg(score),0),2) from tb_student

left join

tb_record on stuid=sid

left join

tb_college on cid=couid

group by stuid;

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

create user zhansan@localhost identified by ‘clm12345’;

create user lisi@118.122.119.205 identified by ‘clm12345’;
create user ‘si’@’%’ identified by ‘clm12345’;

– 2、删除账户
use mysql;
drop user zhansan@localhost;
drop user lisi@118.122.119.205;
drop user ‘si’@’%’;

– 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 权限类型 on 数据库.对象 from 用户名;
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查询语句;
– 用法1：简化查询过程
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 ‘zhangsan’@’%’ identified by ‘clm12345’;
grant select on school.vw_studentinfo to ‘zhangsan’@’%’;