oracle从入门到精通(3)------多表连接查询

oracle数据库入门(3)

-------------------------------------

多表连接查询

-------------------------------------

oracle的语句分为三种类型:

    1        DDL语句(数据定义语言)    

    

                   create-------- 创建对象

                   drop --------- 删除对象

                   

    2        DML语句(数据操作语言)   ------------主要是对数据表进行相关操作                       

    

                   insert -------添加

                   delete -------删除

                   update -------修改

                   select -------查询

    3         DCL(数据控制语言)                                                        

              

                   grant-----------授权

                   revoke----------撤销权限

                   

drop table inf;                   

create table inf

(

       id number(11) primary key,

       name varchar2(20) not null,

       age number(11)

);                   

insert into inf values(1,'jack',23); 

insert into inf values(2,'tom',22);                 

                   

commit;                   

           

insert into inf values(3,'cindy',22);

insert into inf values(4,'kendy',26);        

insert into inf values(5,'chris',27);

insert into inf values(6,'christina',24);        

insert into inf values(7,'mariah',26);

insert into inf values(8,'austin',29);                          

--如果对数据表,做了增\删\改的操作,要提交后以后,数据才会更新到数据表中

提交的方式有三种:

1  点击提交安钮    

2  执行commit命令

3  如果再执行一次DDL语句,之前没有提交的语句,将会自动提交               

                   

create table inf4

(

       id number(11) primary key,

       name varchar2(29)

)                   

                   

select * from inf;                   

                   

select * from inf where age>22; --带条件查询       

select * from inf where age>22 and name like '%a%';---多条件查询,姓名模糊匹配   

--查询年龄是22,24,27,29的人员信息

select * from inf where age =22 or age=24 or age=27 or age =29;       

select * from inf where age not in (22,24,27,29);                   

                   

--查询年龄在23-26岁之间的人员信息

select * from inf where age >=23 and age<=26;                   

select * from inf where age between 23 and 26;    

--查询姓名的长度为5个字的

select * from inf where length(name)=5;               

                   

--查询姓名的长度为5,其中第一个字符必须是c的

select * from inf where name like 'c%' and length(name)=5;                   

select * from inf where name like 'c____';                   

                   

-------------------------------------------------------------

在oracle查询前三条语句

---***************注意:  top 与  limit都不能使用,如果要达到相同的效果,只能使用伪列--rownum

select t.*,rownum 序号值 from inf t where rownum<=3;

delete  from inf where id=2;                   

子查询:

        在一个查询语句中包含有多个select子语,就称为：子查询语句                   

                   

oracle中的分页查询语句:

        select * from inf;        --每页显示2条,当前第3页

 

第一步:先计算第1页加上第2,3页,总共应该显示的条数:        当前页 *  每一页显示的条数  = 应显示总数

                 2 * 3 = 6;                                                   

第二步:再计算第3页之前,已经显示的总条数:    (当前页-1)*每一页显示的条数

        

                  (3-1) * 2 = 4;

        

        

(select p.*,rownum r  from inf p   where rownum <=6)  --前三页的数据

把已经查询到的结果,当作一张表来继续查询

select * from (select p.*,rownum r  from inf p   where rownum <=6) k where k.r  >4;

        

--

聚合函数: min,max,avg,count,sum

select count(*) from inf;

select max(age) from inf;

select min(age) from inf;

select avg(age) from inf;

select sum(age) from inf;

--oracle中,多表连接的方式

       1       自连接(不需要设置任何连接条件,数据表会自动连接)  natural join

       

       2       内连接  inner join

       

       3       外连接 (左外连接 右外连接  完全外连接) left outer join,right outer join,full outer join

       

create table stu

(

       sid number(11) primary key,

       sname varchar2(20) not null

); 

insert into stu values(1,'jack');

insert into stu values(2,'bruce');

insert into stu values(3,'cindy');

drop table score;

create table score

(

       id number(11) primary key,---成绩编号

       score number(11) not null,--成绩

       stuid number(11) not null--哪一个学员的成绩

)

insert into score values(1,99,1);

insert into score values(2,87,3);

select * from stu natural join score;

--自连接的前提的条件,连接的       "两张表中必须有相同的字段"   名称,才可以使用自连接,如果没有相同的名称,

--则不允许使用,如果两张表同一字段的值是一样的,系统就会自动把这两条数据关联起来

--查询没有参加考试的学员信息

select * from stu where sid  not in (select sid from score);

----------------------------------------------------------------------

内连接 ：　连接的两张表,不需要有相同的字段名,只需要设置连接条件即可

第一种:    select a.字段名,b.字段名  from A表 a ,B表  b where a.字段=b.字段 

第二种:    select a.字段名,b.字段名  from A表 a inner join B表  on (a.字段=b.字段);

        

select a.sid,a.sname,b.score  from stu a,score b where a.sid = b.stuid and b.score>=90

select a.sid,a.sname,b.score from stu a inner join score b on(a.sid=b.stuid and b.score>=90);

        

drop table stu;

drop table score;

create table stu

(

       sid number(11) primary key,

       sname varchar2(20) not null

); 

insert into stu values(1,'jack');

insert into stu values(2,'bruce');

insert into stu values(3,'cindy');

drop table score;

create table subject

(

       subId number(11) primary key,

       subName varchar(20) not null

)

insert into subject values(1,'英语');

insert into subject values(2,'化学');

insert into subject values(3,'物理');

create table score

(

       id number(11) primary key,---成绩编号

       score number(11) not null,--成绩

       subId  number(11) not null,---哪一个科目的成绩

       stuid number(11) not null--哪一个学员的成绩

       

)

insert into score values(1,99,2,1);

insert into score values(2,87,3,3);

                   

                   

select * from stu;                   

select * from subject;

select * from score;

                   

select a.sid,a.sname,b.subname,c.score 

       from stu a,subject b,score c 

            where 

                a.sid=c.stuid 

                          and 

                b.subid = c.subid

                          and 

                b.subname='化学';                                    

                                                         

-----------------

内连接的两张表之间,是平级关系,必须两张表都有的数据,才可以建立关联

--------------------------------------------------------------

外连接的两张表不是平级关系,而是主次关系,主表中的数据必须全部显示,次表中的数据,只有在与主表对应的上的

才可以显示

create table stu

(

       sid number(11) primary key,

       sname varchar2(20) not null

); 

insert into stu values(1,'jack');

insert into stu values(2,'bruce');--该学生没有参加考试

insert into stu values(3,'cindy');

create table score

(

       id number(11) primary key,---成绩编号

       score number(11) not null,--成绩

       stuid number(11) not null--哪一个学员的成绩

       

)

insert into score values(1,99,1);

insert into score values(2,65,3);

insert into score values(3,100,9);--编号为9的学生,在学生表中找不到对应的信息

select a.sname,b.score  from stu a,score b where a.sid=b.stuid;

left outer join左外连接-----------左边的表是主表,右边的表是次表

select a.sid,a.sname,b.score from stu a left outer join score b on(a.sid=b.stuid);

语法:

       select a.字段,b.字段 from A表 a, B表 b where a.字段 = b.字段(+);  --左外连接

       select a.字段,b.字段 from A表 a, B表 b where a.字段(+) = b.字段;  --右外连接

select a.sid,a.sname,b.score from stu a,score b where a.sid(+)=b.stuid;

--完全外连接   两张表都是主表,都要全部显示

select a.sid,a.sname,b.score from stu a full outer join score b on(a.sid=b.stuid);

create table inf3

(

       id number primary key,

       name varchar2(20) not null,

       bir date

)

insert into inf3 values(1,'jack',date'2011-10-02');

insert into inf3 values(2,'chris',date'2001-02-12');

insert into inf3 values(3,'andy',date'2002-03-12');

insert into inf3 values(4,'rose',date'2010-11-11');

insert into inf3 values(5,'cindy',date'2008-10-22');

select * from inf3;

select * from inf3 where bir >date'2005-01-01';

select * from inf3 where bir between date'2002-01-01' and date'2011-01-01' order by bir asc;

---oracle中,没有自动增长列

--但通过sequence (序列)可以达到相同的效果

创建序列的语法:

              create sequence 序列名  start with 从哪一个值开始  increment by 每一次递增几个值;

创建序列:

              create sequence inf3_seq start with 1 increment by 1;

drop table inf3;

create table inf3

(

       id number primary key,

       name varchar2(20) not null

)

insert into inf3 values(inf3_seq.nextval,'abc');

select * from inf3;