MySQL第六天0323 子查询 视图

• 子查询
  • 题目：查询工资比Jones工资高的员工信息
    • 1、子查询：查询Jones工资
      • select sal from emp where ename='jones'
    • 2、主查询：以步骤1的查询结果为条件，再次进行查询
      • select * from emp where sal>x
      • select * from emp
      • where sal>(select sal from emp where ename='jones')
  • 题目：查询工资最低的员工姓名？
    • 1、子查询：查询最低工资是多少
      • select min(sal) from emp
    • 2、主查询：以步骤1的查询结果为条件，再次进行查询
      • select * from emp where sal=x
      • select * from emp
      • where sal=(select min(sal) from emp)
  • 括号内的查询叫子查询，也叫内部查询，先于主查询执行完毕。
  • 子查询的执行过程，类似函数的执行过程
    • main(){
      • f1();
      • f2();
    • }
    • main开始执行--f1开始执行--f1结束执行--f2开始执行--f2结束执行--main结束执行
  • 子查询使用规范
    • 1、子查询要用括号括起来，必须
      • select * from emp  where sal= select min(sal) from emp
    • 2、将子查询放在比较运算符的右边，建议
      • select * from emp  where (select min(sal) from emp)=sal
    • 3、对于单行子查询要使用单行运算符，建议
      • select * from emp  where sal in (select min(sal) from emp)
    • 4、对于多行子查询要使用多行运算符，必须
      • select * from emp  where ename = (select ename from emp where deptno=10)
  • 子查询分类：单行子查询、多行子查询、多列子查询
    • 1、单行子查询，返回1行1列，运算符>、<、=、>=、<=、<>
      • select empno from emp where ename='jones'
    • 2、多行子查询，返回一行或多行1列，in、any、all
      • select ename from emp where deptno=10
    • 3、多列子查询，返回多列
      • select ename,deptno from emp where deptno=10
  • 1、单行子查询
    • 题目：显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。
    • 1、查询7369从事的工作:
      • select job from emp where empno=7369
    • 题目：查询工资最低的员工姓名，岗位及工资
    • 1、查询最低工资:select min(sal) from emp
      • select * from emp
      • where sal = (select min(sal) from emp)
    • 题目：查询部门最低工资比20部门最低工资高的部门编号(emp)及最低工资(emp的min)
    • 1、查询20部门最低工资:select min(sal) from emp where deptno=20,--800
      • select deptno,min(sal) from emp
      • group by deptno
      • having min(sal)>(select min(sal) from emp where deptno=20)
  • 2、多行子查询
    • 1、自连接
      • 题目：查询经理的姓名工资
        • select e2.ename,e2.sal from emp e1, emp e2
        • where e1.mgr=e2.empno
    • 2、子查询
      • 题目：查询经理的员工号: select mgr from emp
        • select ename,sal from emp
        • where empno in (select mgr from emp)
    • ANY：表示和子查询的任意(存在)一行结果进行比较，有一个满足条件即可
      • <ANY：表示小于子查询结果集中的任意一个，即【小于最大值】
      • >ANY：表示大于子查询结果集中的任意一个，即【大于最小值】
      • =ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN
      • 题目：查询是经理的员工姓名，工资。
        • select ename,sal from emp
        • where empno = any (select mgr from emp)
      • 题目：查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资
        • 10部门员工的工资select sal from emp where deptno=10，(700,2450,5000)
        • select * from emp
        • where deptno!=10
        • and sal>any(select sal from emp where deptno=10)
    • ALL：表示和子查询的【所有】行结果进行比较，每一行必须都满足条件。
      • <ALL:表示小于子查询结果集中的所有行，即小于最小值。
      • >ALL:表示大于子查询结果集中的所有行，即大于最大值。
      • =ALL:表示等于子查询结果集中的所有行，即等于所有值，通常无意义。
      • 题目：查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。
        • select * from emp
        • where deptno != 20
        • and sal >all (select sal from emp where deptno=20)
• 1、视图
  • 视图是逻辑上来自一个或多个表的数据集合
    • 不允许用户a查看emp表、不允许用户b查看emp表
    • 要求用户a查看emp表的empno、ename，怎么办？
    • 要求用户b查看emp表的empno、sal，  怎么办？
      • 办法：在emp表的基础上创建视图x，该视图对用户a可见
      • 办法：在emp表的基础上创建视图y，该视图对用户b可见
  • 创建视图语法：
    • CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    • VIEW view_name [(column_list)]
    • AS select_statement
    • [WITH [CASCADED | LOCAL] CHECK OPTION]
      • OR REPLACE：如果所创建的视图已经存在，该选项表示修改原视图的定义；
      • view_name ：视图的名称；
      • column_list ：列名，列名的数量必须和视图所对应查询语句的列数量相等；
      • select_statement ：一条完整的SELECT语句；
      • WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须满足视图所定义的查询；
  • 1、创建视图myview1
    • create view myview1 as select * from emp where deptno=10
  • 2、从视图myview1检索数据
    • select * from myview1 where sal>2000
  • 3、对比（删除）
    • create view myview1 as select * from emp where deptno=10
    • create view myview2 as select * from emp where deptno=10
    • create table mytab1 as select * from emp where deptno=10
    • select * from myview1
    • select * from myview2
    • delete from emp where empno=7934
    • delete from myview1 where empno=7839
  • 4、视图时一个基于数据表的逻辑表
    • 视图是一个逻辑概念，本身并不存储数据
    • 视图中的数据来源于数据表
  • 操作
    • myview1已经存在，再次create报错
      • create view myview1 as select * from emp where deptno=10
    • myview1已经存在，create or replace运行正常
      • create or replace view myview1 as select * from emp where deptno=10
    • 视图名(字段名1,字段名2,字段名3,,,)
      • create or replace view myview4(no,name,salary)
      • as select empno,ename,sal from emp where deptno=10
    • 使用describe描述视图，效果同describe描述数据表
      • describe emp
      • describe v_emp10
    • select子句中使用列别名
      • create or replace view myview4(no,name,salary)
      • as select empno,ename,sal from emp where deptno=10
      • create or replace view myview5
      • as select empno no,ename name,sal from emp where deptno=10
  • 视图的DML
    • 正常定义，可以delete
      • create table dept2 as select * from dept
      • create or replace view v1 as select * from dept2
      • delete from v1 where deptno=30
    • 使用分组函数、group by
      • create or replace view v2(deptno,count)
      • as select deptno,count(*) from emp group by deptno
      • select * from v2
      • delete from v2 where deptno=30
    • 使用distinct
      • create or replace view v3(deptno)
      • as select distinct(deptno) from emp
      • select * from v3
      • delete from v3 where deptno=30
    • 使用表达式定义的列
      • create or replace view v4(info)
      • as select concat(empno,'--',ename) from emp
      • select * from v4
      • -- delete from v4
      • update v4 set info='abc'