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

一、子查询

将一个查询结构作为另外一个查询的条件或者查询条件

1.将一个查询结果作为另外一个查询的条件

例如：

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

2.将一个查询结果作为另外一个查询的对象，这个时候查询结果必须重命名

--  查询平均分高于80分的学生的id以及对应的名字
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;

二、连表查询

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

1.直接连表

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

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

2.内连接

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

表名 inner join 表名 on 连接条件

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

3.外连接

a.左外连接 left outer join/left join

b.右连接 right outer join/right join

c.全外连接 full out join/full

ifnull(字段，值)：如果指定字段值为空就返回指定值

round(数字，N)：让数字保留N为小数

select stuname,collname,round(ifnull(avg(score),0),2) as avg_score 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

grand、revoke

1.创建用户

create user 用户名@登录地址 identitied by 密码;

用户名：自己确定（英文）

登录地址：

a.localhost:只能在数据库所在的主机上登录这个用户

b.ip地址

c.%:所有地方都可以登录

2.删除用户

drop user 用户名@登录地址

3.授权

grant 权限类型 on 数据库.对象 to 用户名@登录地址

对象可以是实体表也可以是视图

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

4.召回权限

revoke 权限类型 on 数据库.对象 from 用户名@登录地址

revoke delete on school.tb_student from zhangsan@localhost;

四、视图使用

1.创建/删除视图

create view 视图名 as sql查询语句

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

drop view 视图名

drop view vw_分数;

2.作用

• 简化查询过程

  select stuname,avg(score) from vw_score group by stuid;
  

• 将表中的部分进行授权

  select stuname,stuaddr from tb_student;
  create view vw_student as select stuname,stuaddr from tb_student;
  grant select on school.vw_student to zhangsan@localhost;