day03 伪列：

伪列：

rowid: 物理地址 唯一标示表中的数据

  rowid 不能修改，只能用于查询，

    在往表中添加数据时系统会自动给这个数据添加一个物理地址

rownum：行号 ,在执行查询的时候，对查询出来的数据进行编号

            1开始。自增

 select t.*,  rownum

 from student t

 where sid>10

 1:查询学生表中前10条数据

select t.*,  rownum

from student t

where sid>=10

 

 2:查询学生表中年龄第2大的学生信息

select t1.* from

--将以下查询的结果当是一张表

select t.*

from student t

where sage is not null

order by sage desc

 

 子查询：查询语句的嵌套

 --1

 select t1.* from (

 select t.*

 from student t

 where sage is not null

 order by sage desc

 ) t1

 where rownum=2---查询不出数据，永远都是1开始查询

 --当取出一条数据时，系统就给改数据编号，由于前面没有数据，则

 --编号都是从1开始，判断rownum=2不符合，则过滤，之后都是数据编号1开始

   ---2

  select t2.*,rownum from(

  select t1.*, rownum num from  

 (

 select t.*

 from student t

 where sage is not null

 order by sage desc

 ) t1

 )t2 

 where num=2

 4:查询学生表中第5条数据到第10条数据

  --  f分页查询

 select t1.*, rownum from

 (

 select t.*, rownum num

 from student t

 where rownum<=10

 )t1

 where num>=5

 

 5:查询学号为5的学生班级名称

 select cname from t_class

  where cid=(

 select cid from student where sid=5

  )

 6:查询 学生表中学生的年龄18-20所有学生的班级名称

  select cname from t_class

 where cid in

 (

 select cid

 from student

 where sage between 18 and 20

 )

 

 7:连接查询：

        交叉连接：结果集的个数是两张表的笛卡尔乘机

        交叉连接中没有where条件

        有重复的数据，在数据量比较大的时候，执行效力差

        一般情况下都是数据比较少的情况使用

        select t1.*,t2.*

        from student t1,t_class t2

 

 8:等值连接:在交叉连接的基础上不符合的条件的，去除掉

        将学生表中没有班级的学生信息，也全部去除掉

        

 select t1.* ,t2.*

 from student t1, t_class t2

 where t1.cid=t2.cid

 

 9:内连接：inner join ....on 内连接比交叉连接，等值连接 效率高

    还是将学生表中或者班级中的不符合条件，也全部去除掉

 select *

 from student t1 inner join t_class t2 on t1.cid=t2.cid

 

左边表     右边表

1000          2   执行1000次

           2          1000 执行2次

      当左边表跟右边表数据差不多的时候，执行效率差不多

      当左边和右边的数据个数相差比较大的时候，

       左边数据少的情况下，执行效率比较高

    select *

    from t_emp t1 inner join t_dept t2 on t1.DEPTID=t2.DEPTID

 3: 外连接：  

 查询学生表中所有学生基本信息及对应的班级名称

         左外连接：left join ...on

                   以左边为主，左边的数据全部显示，右边的数据显示符合条件的，

                   不符合条件的，干掉

                   select *

                   from student t1 left join t_class t2 on t1.cid=t2.cid

         右外连接:   right join ...on

                 以右边为主，右边的数据全部显示，左边的数据显示符合条件的，

                   不符合条件的，干掉

                  select *

                  from student t1 right join t_class t2 on t1.cid=t2.cid

         全连接:      full join ...on

                   左边和右边的数据全部显示

                   全连接=左外连接+右连接+去掉重复的数据

                    select *

                    from student t1 full join t_class t2 on t1.cid=t2.cid      

    

联合查询：将两个查询的结果集合并

    union   和   union all

    union :两个结果的集进行合并，对重复的数据去掉

          并按照第一 列的数据进行升序排序

     union all：将两个结果集进行合并，没有去掉重复数据，没有排序

    

     select *from student t1 left join t_class t2 on t1.cid=t2.cid

     union all

     select *from student t1 right join t_class t2 on t1.cid=t2.cid      

  -- 注意：合并两个结果集，必须是两个结果集中的字段个数，类型必须一致

   如： select sid,sname,sage from student

           union

           select sid,sname,sage,ssex from student

 

视图：可以把它看成是一个虚表

在开发中，如果需要隐藏一些字段，不暴露给用户看，就需要使用视图

作用:提高查询的效率

  1.将表结构中特定的字段提取出来，提高了数据的安全性

  2.将复杂的计算或查询频率比较高的操作封装到视图中，简化SQL语句

conn / as sysdba  

grant create view to j1705;  ---授予创建视图的权限

创建视图

  --create or replace  创建或者替换  如果创建的视图已存在则替换，否则创建

  create or replace view v_t_student

  as

  select sid,sname,

  extract(year from sysdate)-extract(year from sbirthday) age

  from t_student

  with read only   ---创建的视图是只读的 不能操作的 只能查询

  

使用：

     1.查询

         select * from v_t_student

     2.修改视图中的数据其实就是修改的是视图对应的表的数据

         由于视图是一张虚拟表，所以在视图中是没有数据的

         所以修改的数据其实都是真实存在的表中的数据

       对简单的视图可以修改，对复杂的视图是不能修改的

         update v_t_student set sbirthday=sysdate where sid=1

         update v_t_student set sname='隔壁老王' where sid=2

         --update v_t_student set age=20 where sid=2

         

---强制创建一个视图（t_oa表不存在的） 

create or replace force view v_t_oa

as 

select * from t_oa

---由于t_oa不存在  所以创建的视图有错误 不能使用

select * from v_t_oa

---如果要使用这个强制创建的视图，则需要给这个视图创建对应的表

create table t_oa(

oaid number(3),

oaname varchar2(20)

)