三篇sql基础之2

/*这样的查询形式称为非关联子查询。语句的执行顺序是先执行子查询（只执行一遍，若子查询的返回结果为多条，系统会自动去重），将子查询的结果代入主查询，执行后产生最终结果。子查询主查询先执行子查询，讲子查询执行的结果集返回给主查询，再执行主查询子查询返回一条结果可以用单值运算符返回多条结果必须用多值运算符子查询结果有null值整个查询结果会没有数据多列子查询关联子查询关联子查询，循环主查询的记录一条条的执行，把记录传递到子查询，子查询查询的数据返回主查询子查询符合条件的记录可能有多条 */select s1.* from student s1where 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 studentwhere (c_class,c_school) in (select max(c_class),c_school from student group by c_school);select * from studentwhere (c_class,c_school) in (select c_class,c_school from student);/*关联子查询：子查询和主查询有关联条件*/--关联子查询一条条的执行符合条件的查询出来--子查询语句会根据本次查询的出的多次记录作为一个组处理select s1.* from student s1where 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 s1where   exists (select 1 from student s2 where s1.c_school=s2.c_school) order by id;/*非关联子查询：select real_name from accountwhere id in (select account_id from service);关联子查询select real_name from account owhere exists(select 1 from service iwhere o.id = i.account_id);*//*用关联子查询的not exists形式解决该查询执行后返回no rows selected。注意not exists关心是否有返回结果而不是返回结果具体是什么。所以select后面跟什么并不重要。*/select s1.* from student s1where 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_schoolfrom student s join t_class t on s.c_class=t.id;--先过滤再连接select s.name,t.id,t.class_name,s.c_schoolfrom student s join t_class t on s.c_class=t.idand s.name='java';select s.name,t.id,t.class_name,s.c_schoolfrom student s join t_class t on s.c_class=t.idwhere 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 allselect * from student where id in (24,25) ;select * from student where id in (1,2,21,24,25,41) INTERSECTselect * from student where id in (24,25) ;select * from student where id in (1,2,21,24,25,41) MINUSselect * from student where id in (24,25) ;select * from student where id in (24,25) MINUSselect * from student where id in (1,2,21,24,25,41) ;/*排名分页问题roenum 是一个伪列*/select s.*,rownum from student s ; --分页select id,name,c_schoolfrom (            select rownum rn,id,name,c_school            from student            where rownum <= 6     )where rn >= 4;select rn,id,name,c_schoolfrom (        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_schoolfrom (            select rownum rn,id,name,c_school            from student            where rownum <= 6     )where rn >= 4;select rn,id,name,c_schoolfrom (            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_namefrom 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);*/