三篇sql基础之2

/*
这样的查询形式称为非关联子查询。
语句的执行顺序是先执行子查询（只执行一遍，若子查询的返回结果为多条，系统会自动去重），
将子查询的结果代入主查询，执行后产生最终结果。

子查询
主查询

先执行子查询，讲子查询执行的结果集返回给主查询，再执行主查询

子查询返回一条结果可以用单值运算符
返回多条结果必须用多值运算符

子查询结果有null值整个查询结果会没有数据

多列子查询

关联子查询
关联子查询，循环主查询的记录一条条的执行，把记录传递到子查询，子查询查询的数据返回主查询
子查询符合条件的记录可能有多条 

*/
select s1.* from student s1
where s1.age > (select avg(s2.age) a1 from student s2 where s1.c_school=s2.c_school) order by id;

select * from student 
where c_school   in (select null from dual) order by id;

select * from student 
where c_school  not in (select null from dual) order by id;


select * from student 
where c_school in (select c_school from student where id=1) order by id;


select * from student 
where c_school in (select min(c_school) from student group by c_school) order by id;

--该查询的返回结果是多值，需要用多值运算符，>号后面需要增加all或any，即>all，>any。
select * from student 
where c_class > any (select c_class from student where id in(1,2)) order by id;
--主查询的where条件中可以用多个列进行过滤，称之为多列子查询。
select * from student
where (c_class,c_school) in (select max(c_class),c_school from student group by c_school);

select * from student
where (c_class,c_school) in (select c_class,c_school from student);

/*
关联子查询：子查询和主查询有关联条件
*/
--关联子查询一条条的执行符合条件的查询出来
--子查询语句会根据本次查询的出的多次记录作为一个组处理
select s1.* from student s1
where s1.age > (select avg(s2.age) from student s2 where s1.c_school=s2.c_school) order by id;

--关联子查询的exists形式解决，对于主表中的记录只要存在（exists）如下条件，就将其放入结果集中。
--注意exists关心是否有返回结果而不是返回结果具体是什么。所以select后面跟什么并不重要。
select s1.* from student s1
where   exists (select 1 from student s2 where s1.c_school=s2.c_school) order by id;

/*
非关联子查询：
select real_name from account
where id in (select account_id from service);

关联子查询
select real_name from account o
where exists
(select 1 from service i
where o.id = i.account_id);
*/

/*
用关联子查询的not exists形式解决
该查询执行后返回no rows selected。注意not exists关心是否有返回结果而不是返回结果具体是什么。所以select后面跟什么并不重要。
*/
select s1.* from student s1
where not  exists (select 1 from student s2 where s1.c_school=s2.c_school) order by id;


create  table  t_class(
id number(5),
class_name varchar2(200)
);

select * from t_class;

insert into t_class values(5,'五班');
insert into t_class values(6,'六班');
insert into t_class values(8,'八班');
insert into t_class values(9,'九班');

commit;

/*
多表查询

主要解决匹配问题和不匹配问题

交叉连接 cross join  产生m*n条记录
内连接 inner join /join   如果有多个条件on后跟一个，其他的在and之后
                  驱动表 和 匹配表
                  循环驱动表的记录 ，一条条在匹配表中一条条的匹配，符合的放入结果集
                  内连接中用on和where都一样
                  无论哪个表做驱动表，结果集都一样，效率不一样
外连接 outer join
                  左连接 left  join /  left outer join 左表的数据都显示
                  右连接 right  join / right outer join 右表的数据都显示
                  全连接 full  join / full outer join  左右表所有的记录都显示出来
                  
                   外连接的结果集是内连接结果集+不匹配的结果集


结果集的信息来自两张表的记录组合。
如果两张表的记录满足条件,那么就将它们的组合放入结果集中，称为连接操作，即通常所说的内连接（inner join）。
*/

select s.name,t.id,t.class_name,s.c_school
from student s join t_class t on s.c_class=t.id;

--先过滤再连接
select s.name,t.id,t.class_name,s.c_school
from student s join t_class t on s.c_class=t.id
and s.name='java';

select s.name,t.id,t.class_name,s.c_school
from student s join t_class t on s.c_class=t.id
where s.name='java';

--from 后跟子查询
--自连接  同一张表的匹配关系  通过起不同的别名 实现

select * from student;
insert into student(name) values('join');
commit;
select * from t_class;
--交叉连接
select * from student s cross join t_class t ; 
--内连接
select * from student s  join t_class t on s.c_class=t.id ; 
--外连接
       --左连接
       select * from student s left join t_class t on s.c_class=t.id ; 
       select * from t_class t  left join student s on s.c_class=t.id ; 
       --右连接
       select * from student s right  join t_class t on s.c_class=t.id; 
      select * from t_class t  right  join student s on s.c_class=t.id ;
      --全连接
      select * from student s full  join t_class t on s.c_class=t.id; 
      select * from t_class t  full  join student s on s.c_class=t.id ;
      
 
/*
 集合运算
 
 UNION：（并集）返回两个集合去掉重复值的所有的记录
 UNION ALL：（并集）返回两个集合的所有的记录       
 INTERSECT：（交集）返回两个集合的交集记录，重复的只取一次
 MINUS：（差集）返回属于第一个集合，但不属于第二个集合的所有记录
 
 集合运算的要求两个select的语句是同构的，即列的个数和数据类型必须一致

*/

select * from student where id in (1,2,21,24,25,41) 
union 
select * from student where id in (24,25) ;

select * from student where id in (1,2,21,24,25,41) 
union all
select * from student where id in (24,25) ;


select * from student where id in (1,2,21,24,25,41) 
INTERSECT
select * from student where id in (24,25) ;

select * from student where id in (1,2,21,24,25,41) 
MINUS
select * from student where id in (24,25) ;


select * from student where id in (24,25) 
MINUS
select * from student where id in (1,2,21,24,25,41) ;


/*
排名分页问题

roenum 是一个伪列

*/

select s.*,rownum from student s ; 

--分页
select id,name,c_school
from (
            select rownum rn,id,name,c_school
            from student
            where rownum <= 6
     )
where rn >= 4;

select rn,id,name,c_school
from (
        select rownum rn,id,name,c_school
        from
            (select id,name,c_school
            from student
            )
            where rownum <= 6
     )
where rn >= 4;



select rn,id,name,c_school
from (
            select rownum rn,id,name,c_school
            from student
            where rownum <= 6
     )
where rn >= 4;


select rn,id,name,c_school
from (
            select rownum rn,id,name,c_school
            from student
            where rownum <= 6
     )
where rn >= 4;


/*
约束

primary key 主键约束
unique key 唯一性约束
not null 非空约束
references foreign key 外键约束
check 检查约束

*/


--主键约束
      --列级约束
      create table parent (
             c1 number(2) constraint parent_c1_pk primary key,
             c2 number );
      --表级约束
      create table parent (
             c1 number(2),
             c2 number,
             constraint parent_c1_pk primary key(c1)
             );
       --给存在的表添加约束        
       alter table parent  add constraint parent_c1_pk primary key(c1);    

--非空约束  只有列级约束形式
        --...colname DATATYPE NOT NULL

--唯一键约束
        --列级约束               
          create table parent (
             c1 number(2) constraint parent_c1_pk primary key,
             c2 number constraint parent_c2_uk unique
             );
         --表级约束
             c1 number(2) constraint parent_c1_pk primary key,
             c2 number，
             c3 number，
             constraint parent_c2_c3_uk unique(c2,c3)
             );

--外键约束
        --列级约束               
          create table child1(
             c1 number(2) constraint child_c1_pk primary key,
             c2 number constraint child_c2_fk references parent1(c1)
             );
             
          create table child1 (
             c1 number(2) constraint child_c1_pk primary key,
             c2 number constraint child_c2_fk references parent1(c1) on delete cascade
             );
             
          create table child1 (
             c1 number(2) constraint child_c1_pk primary key,
             c2 number constraint child_c2_fk references parent1(c1) on delete set all
             );                          
             
         --表级约束
          create table child1 (
             c1 number(2) constraint child_c1_pk primary key,
             c2 number,
              constraint  child_c2_fk foreign key(c2) references parent1(c1)
             );

            --on delete cascade  删除父表的记录前，先删除子表的数据,级联删除
            --on delete set all 删除父表的记录前，子表的关联字段设置为空,父表再删除

--检查约束
        --列级约束               
          create table parent (
             c1 number(2) constraint parent_c1_pk primary key,
             c2 number constraint parent_c2_ck check(c2>100)
             );
         --表级约束
             c1 number(2) constraint parent_c1_pk primary key,
             c2 number，
             c3 number，
             constraint parent_c2_c3_ck check((c2+c3)>100)
             );            

/*
事务
commit 提交
rollback 回滚
*/
commit;
rollback;
--保留点 savepoint
--savepoint s1
--rollback to s1


/*
查询表中所有约束的意思

select c.constraint_name,c.constraint_type,cc.column_name
from student c join t_class cc on c.c_class=cc.id;
*/

/*
约束的操作
删除操作
     alter table tabname drop constraint_name;
删除主键约束
     alter table tabname drop primary key;
增加约束
     alter table tabname add 表级约束语法 
*/


/*
Alter table

将not null列改为null列
     alter table tabname modify(colname null);
将 null列改为not null列
     alter table tabname modify(colname default 1 not null);
增加一列
     alter table tabname add(同建表时的列定义方法);
删除一列
     alter tablel tabname drop(colname);
*/