首页 > 代码库 > 3.复杂查询

3.复杂查询

2024-09-05 07:36:43   221人阅读

本章重点

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

 

3.复杂查询


联系
我们