3.复杂查询
本章重点
- 1.视图
- 2.子查询
- 3.关联子查询
1.视图
- Mysql视图
- 从Mysql的一个表或多个表导出的表，是一种虚拟的表
- 视图表中的数据和它所依赖的表会保持同步
- 原表发生改变，视图的数据也会发生改变
- 使用视图的原因
-
- 1.安全性，可以隐藏(不显示)表格中的敏感信息。
- 2.可以使得复杂的查询易于理解和使用，复杂的SQL语句查询的数据进行放入一个视图中，这样就不用每次都重复的写了。
- 3.视图本身不占用存储空间，所以来的数据表会占用存储空间。
- 4.实时更新，视图会随着依赖的数据表格的更新而发生变化。
- 视图的数据组成
-
- 1.其他数据库或者当前数据库的一张或者多张表
- 2.其他视图----多重视图会降低SQL的性能，应为一级视图其实就是两次的Select语句，依此类推。
- 视图的作用
- 查询数据（会把经常使用的数据创建为视图)
- 提供更加精细的访问控制增加一列标志用户名的列，建立视图，只有用户能看到标志自己用户名的行，并把视图授权给其他用户----如何筛选？
- 逻辑数据的独立性
- 创建视图
- 前提-----需要select_priv.create_view_priv权限
- 创建视图的语法
- create {UNDEFINED|MERGE|TEMPTABLE} View View_name(column1,column2...) as Select 语句 WITH CHECK OPTION
- WITH CHECK OPTION是可选参数，表示更新视图时要保证在该视图的权限范围之内
- 创建视图的算法
-
算法会影响MySQL查询视图的SQL语句的执行情况1. Merge：合并的执行方式，每当执行的时候，先将创建视图的sql语句与外部查询视图的sql语句，混合在一起，最终执行；2.Temptable：临时表模式，每当查询的时候，将视图所使用的select语句生成一个结果的临时表，再在当前的临时表内进行查询------视图不可更新3.UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。
WITH （CASCADED | local CHECK OPTION）表示更新视图时要保证在该试图的权限范围之内（可选参数）CASCADED：更新视图时要满足所有相关视图和表的条件 LOCAL：更新视图时，要满足该视图本身定义的条件即可
- 查看表格的信息 show table status like “view_of_books”: show table status;
- 创建视图注意事项
- 1.用户有create view权限，还有相对应数据的select权限
- 2.不能使用order by关键字---------有的DBMS可以使用order by,但是这不是通用的办法
- 3.select语句中不能包含子查询-----好像也可以
- 4.select语句不能应用系统和用户的变量
- 5.视图不能添加触发器程序
- 修改视图结构
- Create or replace view viwe_name as select 语句 //可以创建和修改视图
- alter view view_name as select;//修改视图
- 修改视图的数据
- 视图是虚拟的表格本身并不占用数据，所以说修改视图其实修改的就是基础表格
- 有如下的几种情况是不能更新视图的
- 1.视图中包含 count(),max()、sum()等函数是不能更新的
- 2.包含union、union by 、distinct、group by 、having 等关键字不能更新
- 3.常量视图不能更新 create or replace view view_name as select “AAA” from table;
- 4.视图创建算法为Temptable时，视图不能更新
- 5.对视图添加新数据，对应的表格中包含有没有默认值而且不在视图中的列时，视图不能添加数据。
- 总结
- 一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，可能会造成数据更新失败。
- 删除视图
- Drop view if exists view_name;
2.子查询
- 常见的子查询形式
- from +子查询--需要设置别名
- eg:SELECT cno FROM (SELECT cno,count(*) as count FROM sc GROUP BY cno) as sc_count WHERE count=1; //查询只有一个人选择的课程的课程号
- 当然简单一点应该这么写 SELECT cno FROM sc GROUP BY `CNO` HAVING count(*)=1;
- 标量子查询---子查询仅仅返回某一行某一列的值
- 优点：可以使用> =等运算符连接子查询
- select cno from grade > (select avg(grade) from sc);//子查询仅仅返回平均分
- 甚至可以嵌入常量查询中 select cno,(select avg(grade) from sc) from sc;
3.关联子查询
- 通过例子进行理解
- SC表，column：sno(学号),cno(课程号),grade(分数)
- select * from sc where grade > (select avg(grade) from sc scx where cno=sc.cno group by cno);
- 运行过程：
- 1.每次select 取一行数据 ，会送给子查询进行执行，如果满足条件就保留不满足就丢弃。
- 2.关键点时内部表和外部表的"连接过程"，scx.cno=sc.cno
- 查询只有一个人选择的课程的课程号
- SELECT cno FROM sc WHERE `CNO` NOT IN (select cno from sc scx WHERE `SNO`!=sc.`SNO`);
- 查询选修课程号为“CS-1”的课程的学生
- SELECT * FROM student WHERE EXISTS (SELECT * from sc WHERE `SNO`=student.`SNO` AND cno="CS-1");
