您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:Oracle的内外连接,DQL之连接查询

新葡亰496net:Oracle的内外连接,DQL之连接查询

发布时间:2019-09-27 14:16编辑:网络数据库浏览(127)

    新葡亰496net:Oracle的内外连接,DQL之连接查询。简介

    等值连接  非等值连接 特殊的自连接都属于内连接

    • 内连接:符合连接的条件的数据被选中,不符合条件的数据被滤去
    • 外连接:外连接的结果集等于内连接的结果集加上匹配不上的记录(一个也不能少)

    目录结构:

    又称多表查询,当查询的字段来自于多个表时,就会用到连接查询。

    回顾:
    1.单行函数
    2.表连接
    oracle中的表连接
    内连接
    等值连接
    select e.id,e.first_name,d.name
    from s_emp e,s_dept d
    where e.dept_id = d.id;
    select d.id,d.name dname,r.name rname
    from s_dept d,s_region r
    where d.region_id = r.id;
    select e.id,e.first_name,r.name
    from s_emp e,s_dept d,s_region r
    where e.dept_id=d.id and d.region_id = r.id;
    非等值连接
    select e.id,e.first_name,e.salary,g.grade
    from s_emp e,salgrade g
    where e.salary between g.losal and g.hisal;
    自连接
    select distinct m.id,m.first_name
    from s_emp e,s_emp m
    where e.manager_id = m.id;

    连接可以在SELECT 语句的FROM子句或WHERE子句中建立,似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以,在Transact-SQL中推荐使用这种方法。 

    如何实现外连接

    新葡亰496net 1

    ( )  把( )字段对面的表的数据全部被匹配出来

    select  distinactt m.id,m.first_name from s_emp e,s_emp m where  e.manager_id( )=m.id;
    

    ( )把领导表里的普通员工用NULL匹配(这是连接条件)

    找普通员工也要从领导表中找,因为领导表中有绝对条件证明谁是领导,但是员工表里没有这个绝对条件。所以要把领导表中的数据全部匹配出来,所以( )要加到员工表的字段上,这样领导表里的所有数据就都被匹配了(普通员工匹配的是NULL),要找普通员工时,只要找员工表里匹配的manager_id是NULL的就能找到普通员工(这是过滤条件)

    select distinct m.id, m.first_name from s_emp e, s_emp m where e.manager_id( )=m.id and e.manager_id is null;
    

    新葡亰496net 2

    contents structure [-]

    2.1、按年代分类:

    sql92标准:仅仅支持内连接

    sql99标准:支持内连接 外连接 交叉连接

    外连接
    等值连接
    select e.id,e.first_name,d.name
    from s_emp e,s_dept d
    where e.dept_id = d.id( );
    select d.id,d.name dname,r.name rname
    from s_dept d,s_region r
    where d.region_id = r.id( );
    非等值连接
    select e.id,e.first_name,e.salary,g.grade
    from s_emp e,salgrade g
    where e.salary between g.losal( ) and g.hisal( );
    自连接
    select m.id,m.first_name
    from s_emp e,s_emp m
    where e.manager_id( ) = m.id
    and e.id is null;

    SQL-92标准所定义的FROM子句的连接语法格式为: 

    演示一:

    select  *  from s_dept;
    select  *  from s_region;
    

    (1)内连接:要求显示每个部门的id  和部门对应的名字  以及对应的地区名

    select d.id, d.name, r.name from s_dept d, s_region r where d.region_id=r.id;
    

     新葡亰496net 3

      显示了12条数据。

     

    (2)业务扩展,成立新部门  id为100,name为test  region_id为NULL

     insert into  s_dept  values(100 ,‘test’,NULL);(表中的增加)
    

     新葡亰496net 4新葡亰496net:Oracle的内外连接,DQL之连接查询。 

    再用上面的方法查找就只找得到原来的,新增加的找不到,因为region_id为NULL。

     

    (3)所以用外连接使没有地区编号的部门也要显示出来,因为要把部门表里的所有部门匹配显示出来,那么就要把( )加在地区表上

    select d.id, d.name, r.name from s_dept d, s_region r where d.region_id=r.id( )
    

    新葡亰496net 5

    。。。。。。

    新葡亰496net 6 

    Oracle的内外连接

    2.2、按功能分类:

    内连接:等值连接、非等值连接、自连接

    外连接:左外连接、右外连接、全外连接

    交叉连接

      select d.id,d.name
          from s_emp e,s_dept d
              where e.dept_id( ) = d.id
                 and e.id is null;
    

    FROM join_table join_type join_table 
    [ON (join_condition)] 

    演示二:

    (1)内连接:统计每个员工的id  salary,并显示工资的工资级别(用到s_emp和salgrade表)

    select e.id, e.salary, s.grade from s_emp e, salgrade s where e.salary between s.losal and s.hisal;
    

    新葡亰496net 7

      。。。。。。

    新葡亰496net 8

     

    (2)有一天把老板的工资改为12500,超出了salgrade的统计范围。

    update s_emp set salary=12500;
    

    新葡亰496net 9

    再用上面的方法找,老板的就丢失了!

    select e.id, e.salary, s.grade from s_emp e, salgrade s where e.salary between s.losal and s.hisal order by id;
    

    新葡亰496net 10

      。。。。。。

      新葡亰496net 11

      从结果可以看出,没有了“id=1”的老板的项目。

      

    (3)所以要用外连接把超出统计范围的员工信息也要显示出来。因为要把员工信息表的所有信息匹配显示出来,所以要在salgrade对应的的字段上加( ),这里要加两个。

    select e.id, e.salary, s.grade from s_emp e, salgrade s where e.salary between s.losal( ) and s.hisal( ) order by id;
    

    新葡亰496net 12

    内连接

    3.1、等值连接:

    ① 多表等值连接的结果为多表的交集部分

    ②n表连接,至少需要n-1个连接条件

    ③ 多表的顺序没有要求

    ④一般需要为表起别名

    ⑤可以搭配前面介绍的所有子句使用,比如排序、分组、筛选

    简单查询

    案例:查询员工名和对应的部门名

    SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id`=departments.`department_id`;

    新葡亰496net 13

    为表起别名

    ①提高语句的简洁度

    ②区分多个重名的字段

    注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定

    案例:查询员工名、工种号、工种名

    SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id`;

    新葡亰496net 14

    两个表的顺序可以调换

    可以加筛选

    案例:查询有奖金的员工名、部门名

    SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;

    新葡亰496net 15

    可以加分组

    案例:查询每个城市的部门个数

    SELECT COUNT 个数,city FROM departments d,locations l WHERE d.`location_id`=l.`location_id` GROUP BY city;

    新葡亰496net 16

    可以加排序

    案例:查询每个工种的工种名和员工的个数,并且按员工个数降序

    SELECT job_title,COUNT FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GROUP BY job_title ORDER BY COUNT DESC;

    新葡亰496net 17

    可以实现三表连接

    SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` ORDER BY department_name DESC;

    新葡亰496net 18


    其中join_table指出参与连接操作的表名,连接可以对同一个表操作,也可以对多表操作,对同一个表操作的连接又称做自连接。join_type 指出连接类型,可分为三种:内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同,内连接又分为等值连接、自然连接和不等连接三种。 

    总结

    每次写外连接时,可以先写号内连接(写好内连接要搞清楚表和表之间关系以及业务逻辑),再加上( ),就是用NULL匹配。因为外连接的结果集等于内连接的结果集加上匹配不上的记录。

    ( )字段对面的表的数据全部被匹配出来

    ( )只针对oracle数据库

    但是所有数据库的语法机制是差不多的,只是表现形式有变化 

    • 表连接:
      • 内连接:
        • 等值
        • 非等值
        • 自连接
      • 外连接:
        • 等值
        • 非等值
        • 自连接
    1. 等值连接
    2. 非等值连接
    3. 自连接

    3.2、非等值连接

    SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

    新葡亰496net 19

    1.sql99标准中的表连接
    内连接:
    select 字段列表
    from 表1 [inner] join 表2
    on 关联条件;

    外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是,外连接不只列出与连接条件相匹配的行,而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 

     额外补充

    外连接

    3.3、自连接

    案例:查询员工名和上级的名称

    SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;

    新葡亰496net 20

     select e.id,e.first_name,d.name
        from s_emp e inner join s_dept d
           on e.dept_id = d.id;
    
      三表连接:
      select 字段列表
        from 表1 join 表2 on 关联条件1
                       join 表3 on 关联条件2;
      select e.first_name,d.name,r.name
         from s_emp e join s_dept d on e.dept_id = d.id
              join s_region r on d.region_id = r.id; 
    

    交叉连接(CROSS JOIN)没有WHERE 子句,它返回连接表中所有数据行的笛卡尔积,其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

    sql99中规定的内外连接

    • 内连接:

      from a表,b表 where 连接条件变为99标准: from a表 join b表 on 连接条件(过滤条件不能写在一起) 或者from a表 inner join b表 on 连接条件

    1. 外连接的特点
    2. 如何实现外连接

    4.1、语法:

    select 查询列表 from 表1 别名 join 表2 别名 on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】

    外连接:
    左外连接:内连接的结果集 左表匹配不上的数据
    select 字段列表
    from 左表 left [outer] join 右表
    on 关联条件;
    select e.id,e.first_name,d.name
    from s_emp e left join s_dept d
    on e.dept_id = d.id;
    右外连接:内连接的结果集 右表匹配不上的数据
    select 字段列表
    from 左表 right [outer] join 右表
    on 关联条件;
    select e.id,e.first_name,d.name
    from s_emp e right join s_dept d
    on e.dept_id = d.id;
    全外连接:内连接的结果集 两表匹配不上的数据
    select 字段列表
    from 左表 full [outer] join 右表
    on 关联条件;
    select e.id,e.first_name,d.name
    from s_emp e full join s_dept d
    on e.dept_id = d.id;

    连接操作中的ON (join_condition) 子句指出连接条件,它由被连接表中的列和比较运算符、逻辑运算符等构成。 

    演示:列出每个部门的名字和对应的地区名

    通常使用的:

    select d.name, r.name from s_dept d, s_resgion r where d.region_id=r.id;
    

    新葡亰496net 21

     

      99标准的:(一般不怎么用)

    select d.name, r.name from s_dept d, join s_region r on d.region_id=r.id;
    

      新葡亰496net 22

    • 外连接

      • 左外连接:a表发起连接,a表的数据全部被匹配出来(通过NULL记录来全部匹配)

      from a表 left outer join b表 on 连接条件;

       oracle使用的( ):

    select d.name, r.name from s_dept d, s_region r where d.region_id=r.id( );
    

      新葡亰496net 23

     

    99标准的:

    select d.name, r.name from s_dept d left outer join s_region r on d.region_id=r.id;
    

    新葡亰496net 24

           

      • 右外连接:b表发起连接,b表的数据全部被匹配出来

      from a表 right outer join b表 on 连接条件;

      • 全外连接:实际上只是一个逻辑概念,实际用途中没什么用。全外连接的结果集等于左外连接的结果集加上右外连接的结果集,并且排除重复部分(就是左右两张表都全部匹配,a表b表书写顺序随便)

         oracle无法使用( )实现全外连接

    from  a表  full outer  join  b表  on  连接条件;
    

     新葡亰496net 25

    SQL99的内外连接

    4.2、分类:

    内连接:inner

    外连接

    左外:left

    右外:right

    全外:full(mysql不支持

    交叉连接:cross

         select d.id,d.name
           from s_emp e right join s_dept d
              on e.dept_id = d.id
                 where e.id is null;
    

    无论哪种连接都不能对text、ntext和image数据类型列进行直接连接,但可以对这三种列进行间接连接。例如: 

    oracle的全外连接

    引用两个关键字union和union  all

    • union:可以合并两个结果集,然后排重
    • union  all:可以合并两个结果集(不排重)

    比如:

    select  id from  s_emp  unoin ; 输出25条数据
    select  id  from s_emp  unoin  all ; 输出50条数据
    

      

    1. SQL99的内连接
    2. SQL99的内连接

    4.3、内连接

    select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;

    等值、非等值、自连接

    ①添加排序、分组、筛选

    inner可以省略

    ③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读

    ④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集

    案例1.查询员工名、部门名

    新葡亰496net 26

    案例2.查询名字中包含e的员工名和工种名

    新葡亰496net 27

    案例3.”查询员工名、部门名、工种名,并按部门名降序

    新葡亰496net 28

    案例1、查询员工的工资级别

    新葡亰496net 29

    案例1、查询员工的名字、上级的名字

    新葡亰496net 30

    2.集合运算(合并结果集)
    union: 两个结果集取并集 并排重、排序
    union all:两个结果集直接取并集
    select id from s_emp union
    select id from s_dept;
    select id from s_emp union all
    select id from s_dept;
    intersect: 取两个结果集的交集
    select id from s_emp intersect
    select id from s_dept;
    minus: 第一个结果集 - 第二个结果集
    select id from s_emp minus
    select id from s_dept;
    -- 两个结果集的字段列表的数量和数据类型必须匹配
    select id,first_name from s_emp minus
    select id,name from s_dept;

    SELECT p1.pub_id,p2.pub_id,p1.pr_info 
    FROM pub_info AS p1 INNER JOIN pub_info AS p2 
    ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info) 

    1,Oracle中的内外连接

    4.4、外连接

    应用场景:用于查询一个表中有,另一个表没有的记录

    特点:

    1、外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,则显示匹配的值,如果从表中没有和它匹配的,则显示null;外连接查询结果=内连接结果 主表中有而从表没有的记录

    2、左外连接,left join左边的是主表;右外连接,right join右边的是主表

    3、左外和右外交换两个表的顺序,可以实现同样的效果

    4、全外连接=内连接的结果 表1中有但表2没有的 表2中有但表1没有的

    新葡亰496net,查询哪个部门没有员工

    左外

    SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;

    右外

    SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;

      select id,first_name from s_emp union
      select null,name from s_dept;
    

    (一)内连接 

    1.1,内连接

    内连接就是符合条件的数据被选中,不符合条件的数据被过滤掉。

    4.5、交叉连接

    语法:select 查询列表 from 表1 别名 cross join 表2 别名;

    特点:类似于笛卡尔乘积(表1有5条记录,表2有6条记录,结果就是30条记录)

    关注公众号:Java后端生活,干货文章第一时间送达!

    新葡亰496net 31

    1. 组函数和分组
      3.1 组函数
      1) 常用的组函数
      count(par|) : 统计一组数据的行数
      参数可以是任何类型 还可以是

      -- 统计工资高于1500的员工数
      select count(*) from s_emp where salary>1500;
      max(par) : 统计一组数据中的最大值
      min(par):统计一组数据中的最小值
      参数可以是数字、字符串、日期类型
      -- 列出最早和最晚入职的员工
      select max(to_char(start_date,'yyyy-mm-dd')),
      min(to_char(start_date,'yyyy-mm-dd'))
      from s_emp;
      sum(par) : 统计一组数据的和
      avg(par) : 统计一组数据的平均值
      参数是数字类型
      -- 列出销售部的总工资和平均工资
      select sum(salary),avg(salary)
      from s_emp e,s_dept d
      where e.dept_id = d.id and d.name='Sales';

      2) 组函数对null的处理: 忽略
      select count(commission_pct) from s_emp;
      3) 组函数 可以排重
      select sum(salary),sum(distinct salary) from s_emp;
      select count(salary),count(distinct salary) from s_emp;

    内连接查询操作列出与连接条件匹配的数据行,它使用比较运算符比较被连接列的列值。内连接分三种: 

    1.1.1 等值连接

    先看看需要使用的表结构(在其它的例子中也可能被用上,其余代码也是):

    新葡亰496net 32新葡亰496net 33

     1 table name:s_emp
     2 Name                Description
     3 ------------------  -------------
     4 ID                   员工编号
     5 LAST_NAME            名称
     6 FIRST_NAME           姓氏
     7 USERID               身份证号
     8 START_DATE           开始时间
     9 COMMENTS             评论内容
    10 MANAGER_ID           上级编号
    11 TITLE                标题
    12 DEPT_ID              部门编号
    13 SALARY               薪水
    14 COMMISSION_PCT       提成
    

    s_emp表(员工表)结构

    新葡亰496net 34新葡亰496net 35

    1 table name:s_dept
    2 Name                Description
    3 ------------------  -------------
    4 ID                  部门编号
    5 NAME                部门名称
    6 REGION_ID           地区编号
    

    s_dept表(部门表)结构

    显示每个员工的编号 姓氏 部门名称

    select e.id,e.first_name,d.name
            from s_emp e,s_dept d
                --使用'='号表示等值连接
                   where e.dept_id=d.id;
    

    3.2 分组
    3.2.1 语法
    group by 分组标准

    1、等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。 

    1,1,2 非等值连接

    新葡亰496net 36新葡亰496net 37

    1 table name:salgrade
    2 Name                Description
    3 ------------------  -------------
    4 GRADE                薪水级别
    5 LOSAL                下区间
    6 HISAL                上区间
    

    salgrade表(薪水级别表)结构

    显示每个员工的编号 姓氏 薪水 薪水级别

    select e.id,e.first_name,e.salary,s.grade
           from s_emp e,salgrade s
                --也可以使用 where e.salary >= s.losal and e.salary <= s.hisal
                where e.salary between losal and hisal;
    
        .....
            where ....
               group by ...
                 ...
                   select
                     order by
    
      3.2.2 分组统计每个部门的人数
          select dept_id,count(*) cnt
             from s_emp
                group by dept_id;
          /* 分组语句中列出的字段必须是分组标准
              或者是组函数的参数*/
          select id,dept_id,count(*) cnt
             from s_emp
                group by dept_id;  -- 错误
    
      3.2.2 多列分组
         分组统计每个部门的人数,显式部门的名称和人数
         select e.dept_id,d.name,count(e.id) cnt
            from s_emp e join s_dept d on e.dept_id = d.id
                group by e.dept_id,d.name;
    
    
         分组统计每个部门的人数,列出人数超过2个的部门
    
         select dept_id,count(*) cnt
             from s_emp
                -- 错误:where子句中不能使用组函数
                -- where count(*)>2 
                  group by dept_id;
    
     3.2.3 having子句
        分组后,根据条件筛选出符合条件的组
        select dept_id,count(*) cnt
            from s_emp
               where 1=1
                 group by dept_id
                    having count(*)>2
                       order by cnt;
    
        -- 语法顺序
        select 字段列表
            from 表名
               where 条件   -- 从表中根据条件筛选符合条件的行
                 group by 分组标准  -- 根据分组标准分成多个组
                    having 条件  -- 从分组结果中根据条件筛选符合条件的组
                       order by 排序标准 排序方式; 
    
         -- 执行顺序
         from
           where 
              group by
                having
                  select
                     order by
    

    2、不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 

    1,1,3 自连接

    显示所有领导的编号 姓氏

    在s_emp表中,所有领导者的编号都出现在manager_id一栏,根据这一特点我们来进行如下分析:

    新葡亰496net 38

    所以代码如下:

    select distinct m.id,m.first_name
           from s_emp e,s_emp m  
                  where e.manager_id=m.id;
    

    -- 练习:列出平均工资大于1000 的部门的信息
    select dept_id,avg(salary) avgsal
    from s_emp
    group by dept_id
    having avg(salary)>1000;

    3、自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。 

    1.2 外连接

    select d.id,d.name,avg(e.salary) avgsal
        from s_emp e,s_dept d
            where e.dept_id = d.id
                group by d.id,d.name
                    having avg(e.salary)>1000
                        order by avgsal;
    

    例,下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社: 

    1.2.1 外连接的特点

    读者需要注意外连接和内连接不是完全对立的,它们的关系如下:

    外连接的结果集=内连接的结果集 匹配不上的数据。

    1. 子查询
      子查询指的是把一条select语句嵌入到另一条sql语句中
      执行时,先执行嵌入的子查询,然后再执行外层的sql语句
      4.1 where子句
      1) 单行单列的子查询
      -- 列出工资比'Ben'高的员工的信息
      a. 列出'Ben'的工资
      select salary from s_emp where first_name='Ben';
      -- 1100
      b. 列出工资高于'Ben'的员工的信息
      select id,first_name,salary from s_emp
      where salary>1100;
      c. 合并
      select id,first_name,salary from s_emp
      where salary>(
      select salary from s_emp where first_name='Ben'
      );
      2) 多行单列的结果集
      子查询的结果集为多值时,不能使用比较运算符
      需要使用处理多值的运算值,比如in、not in、any、all等
      (any、all要和比较运算符配合使用:>any、<all)
      -- 使用子查询列出所有的领导的信息
      a) 列出领导的编号
      select distinct manager_id from s_emp;
      -- null,1,2,3,6,7,8,9,10
      b) 根据编号,列出领导的信息
      select id,first_name,title from s_emp
      where id in(null,1,2,3,6,7,8,9,10);
      c) 合并
      select id,first_name,title from s_emp
      where id in(
      select distinct manager_id from s_emp);
      -- 使用子查询列出普通员工的信息
      select id,first_name,title from s_emp
      where id not in(
      select distinct manager_id from s_emp
      where manager_id is not null);
      3) 使用exists
      -- 列出有员工的部门的信息
      select * from s_dept d where exists(
      select * from s_emp e where e.dept_id=d.id
      );

    SELECT * 
    FROM authors AS a INNER JOIN publishers AS p 
    ON a.city=p.city 

    1,2,2 如何实现外连接

        通过在字段后加上( )来实现

           ( )字段所在的表的对面表的数据全部被选中 。除此之外,内连接中被过滤的数据是通过NULL记录进行的匹配。

        比如还是1,3,1的那个案例,我们已经求出了领导的编号,那么员工的信息又该怎么求呢?

        经过分析我们只需要用外连接得到表中所有员工的信息,然后再减去领导的信息,得到的就是普通员工的信息了。

        新葡亰496net 39

     通过上面的动图我们可以看出,( )字段对应表的全部数据都用null匹配出来了,因此内连接也可以通过外连接来表达,比如上面的1,3,1的案例也可以用如下的方式实现:

    select distinct m.id,m.first_name
           from s_emp e,s_emp m
                   --通过is not null筛选得到实际数据
                   where e.manager_id( )=m.id and e.manager_id is not null;
    

     这样也可以得出领导的信息。下面的代码,可以显示普通员工的信息(下面的代码和上面的代码恰好对立是相反的):

    select distinct m.id,m.first_name
           from s_emp e,s_emp m
                   where e.manager_id( )=m.id and e.manager_id is null;
    

    4.2 having子句
    -- 列出平均工资高于公司平均工资的部门的信息
    select dept_id,avg(salary) from s_emp
    group by dept_id
    having avg(salary)>(
    select avg(salary) from s_emp
    );
    4.3 from子句
    一个select语句产生的结果集,可以看成是一个内视图或者匿名视图,只能在当前语句使用
    select id,name,yearsal from
    (select id,first_name name,12*salary 1000 yearsal from s_emp) e
    where yearsal>15000;

    又如使用自然连接,在选择列表中删除authors 和publishers 表中重复列(city和state): 

    2,SQL99的内外连接

    为了在各个数据库厂商之间取得更大的统一性,美国国家标准学会(American NationalStandards Institute,ANSI)于1986年发布了第一个SQL标准,并于1989年发布了第二个版本,该版本已经被广泛地采用。ANSI在1992年更新了SQL标准的版本,即SQL92和SQL2,并于1999年再次更新为SQL99和SQL3标准。在每一次更新中,ANSI都在SQL中添加了新特性,并在语言中集成了新的命令和功能。

    Oracle的内外连接和SQL99的内外连接可以相互转化。

    --  列出工资高于本部门平均工资的员工的信息    
     a) 列出每个部门的编号及其平均工资
       select dept_id,avg(salary) avgsal from s_emp
            group by dept_id;  
       -- 相当于包含dept_id和avgsal两个字段的一张表  s
     b) 使用表连接(s_emp、s) 实现功能
        s_emp: id,first_name,salary
        s: avgsal
        关联字段:  s_emp.dept_id和s.dept_id
        select e.id,e.first_name,e.salary,s.avgsal
          from s_emp e,(
              select dept_id,avg(salary) avgsal
                 from s_emp
                   group by dept_id) s
               where e.dept_id = s.dept_id
                  and e.salary > s.avgsal;
    

    SELECT a.*,p.pub_id,p.pub_name,p.country 
    FROM authors AS a INNER JOIN publishers AS p 
    ON a.city=p.city 

    2.1 SQL99的内连接

    内连接只有满足表连接条件的数据才会被选出。

     语法格式:

            from    a表    join    b表   on 表的连接条件  where 过滤条件;
            from    a表   [inner]  join    b表   on 表的连接条件  where 过滤条件;
    

    s_dept表结构:

    新葡亰496net 40s_dept表(部门表)结构

    s_dept表数据:

    新葡亰496net 41

    s_region表结构:

    新葡亰496net 42新葡亰496net 43

    table name:s_region
    Name                Description
    ------------------  -------------
    ID                  地区编号
    NAME                地区名称
    

    s_region(地区信息)表结构

    s_region表数据:

    新葡亰496net 44

    下面是内部连接的代码:

    select distinct d.id,d.name,r.name
           from s_region r join s_dept d
                   on r.id=d.region_id;
    

    结果如下,可以看出结果只有前12行数据被匹配出来:

    新葡亰496net 45

    下面这段代码和上面的一样:

    select distinct d.id,d.name,r.name
           from s_region r,s_dept d
                 where r.id=d.region_id;
    

    4.4 select之后
    外连接的另一种实现方式,并且更为灵活
    -- 列出员工及其所在部门的信息
    select id,first_name,salary,(
    select name from s_dept d where e.dept_id=d.id
    ) dname
    from s_emp e;

    (二)外连接 

    2.2 SQL99的外连接

    -- 练习:列出所有和'Mark'在同一部门的员工的信息
    select id,first_name,dept_id from s_emp
        where dept_id=
    (select dept_id from s_emp where first_name='Mark');
    

    内连接时,返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时,它返回到查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。 

     2.2.1 语法

    from a表  left  [outer]  join   b表   on  表的连接条件    where  过滤条件;
    from a表  right  [outer]  join   b表   on  表的连接条件    where  过滤条件;
    from a表  full  [outer]  join   b表   on  表的连接条件    where  过滤条件;
    

    left join 形式的连接称为左连接。

    查询结果包含join左侧表中的所有记录以及右侧表满足条件的记录。也就是a表中的全部数据都会被匹配出来,b表中符合条件的数据才会被匹配出来。

    right join 形式的连接称为右连接。

    查询结果包含join右侧表中的所有记录以及左侧表中满足条件的记录。也就是b表中的全部数据都会被匹配出来,a表中符合条件的数据才会被匹配出来。

    full join 形式的连接称为全连接。

    查询结果包含join左侧和右侧的全部数据。也就是a表和b表的所有数据都会被匹配出来。

     

    本文为博主原创文章,转载请注明出处。

    对象名_姓名缩写_座位号

    如下面使用左外连接将论坛内容和作者信息连接起来: 

    xxxx_zsm_00

    5.表的操作
    5.1 表的创建和删除
    5.1.1 标识符的命名
    1) 由a-z、A-Z、0-9、_、$、#构成
    2)必须用字母开头
    3) 不能和关键字重名
    4) 不能和其他的数据库对象重名
    5) 1-30位
    5.1.2 创建表
    -- 语法
    create table 表名(
    字段名 数据类型,
    ....
    字段名 数据类型
    );
    create table testid_zsm_00(
    id number,
    name varchar2(20)
    );
    5.1.3 删除表
    drop table 表名;
    drop table testid_zsm_00;

    5.2 数据操作语句(DML)
    -- 创建一个测试表
    三个字段:
    编号 数字
    名字 字符串
    入职日期 日期
    create table emp_zsm_00(
    id number(7),
    name varchar2(20),
    start_date date
    );

    5.2.1 插入语句(insert)  -- 一次一整行
      1) 语法
       insert into 表名[(字段列表)] values(值列表);
       字段列表的数量和顺序和值列表的数量和顺序必须一致
      2) 不省略字段列表
       -- 写入全部字段
       insert into emp_zsm_00(id,name,start_date)
             values(1,'test1','13-OCT-17');
       commit;
       -- 写入部分字段 ( 没有给值的字段必须允许为null )
       insert into emp_zsm_00(id,name)
             values(2,'test2');
       commit;
     3) 省略字段列表(相当于给出了全部字段,并且顺序和表结构一致)
         -- 给全部字段提供值
         insert into emp_zsm_00 values(3,'test3',sysdate);  
         commit;       
          insert into emp_zsm_00 values(4,'test4',
               to_date('2017-10-13','yyyy-mm-dd')); 
         commit;
         -- 给部分字段提供值
          insert into emp_zsm_00 values(5,'test5',null); 
         commit;
    

    5.2.2 更新语句(update)
    1) 语法
    update 表名 set 字段 = 新值[,字段 = 新值,...][where 条件];
    2) -- 更新 把入职日期改为'2017-01-20'
    update emp_zsm_00 set start_date='12-JAN-17';

      3) -- 把id=1的名字改为'Ben',入职日期改为当前日期
          update emp_zsm_00 set name='Ben',
              start_date=sysdate where id=1;
          commit;
    
    5.2.3 删除语句(delete) -- 整行删除
      1) 语法
        delete [from] 表名 [where子句];
      2) 删除表中全部数据
         delete from emp_zsm_00;
         rollback; -- 撤销没有提交的操作
      3) 带where子句的删除
         delete from emp_zsm_00 where name like 'test%';
         commit;
    

    5.3 事务控制语句 (TCL)
    select: 和事务无关
    ddl: 隐式提交的事务
    dml: 默认需要显式提交

    5.3.1 事务控制语句的含义
      commit;     确认事务(提交所有未提交的操作)
      savepoint 保存点;  定义保存点
      rollback;   回滚事务(撤销所有未提交的操作)
      rollback to 保存点; 回顾到保存点的位置
    
    5.3.2 事务的四大特性(ACID)
      1) 原子性:事务中的语句是一个不可分割的整体
        转账:
        账户表:account
        字段:id   balance
        A账户---> B账户  2000
        update account set balance = balance - 2000
                  where  id='A';
        a
        update account set balance = balance   2000
                  where  id='B'; 
        b
        if a&&b
          commit;
        else
           rollback;
    
        要么全部成功,要么全部失败
    
     2)一致性
       事务执行的结果必须是使数据库从一种一致性状态变为另一种一致性状态
    
     3) 隔离性
        一个事务对数据的改变,在提交之前,对于其他的事务是不可见的。
     4)  持久性
       事务一旦提交,对数据的改变就是永久的
    

    5.3.3 部分成功 部分失败
    -- 删除表中全部数据 属于ddl语句,不能撤销
    truncate table emp_zsm_00;

       insert into emp_zsm_00(id,name) values(1,'test1');
       savepoint a;
       insert into emp_zsm_00(id,name) values(2,'test2');
       savepoint b;
       insert into emp_zsm_00(id,name) values(3,'test3');
       savepoint c;
       insert into emp_zsm_00(id,name) values(4,'test4');
       select * from emp_zsm_00;
       rollback to b;
       commit;
       select * from emp_zsm_00;
    

    练习:
    1.使用select语句建表:
    drop table emp_zsm_00;
    create table emp_zsm_00 as select * from s_emp;
    2.给所有'Carmen'的下属涨工资
    1) 查询'Carmen'的编号
    2) 根据'Carmen'的编号 查询其下属的编号
    3) 根据员工编号 改工资
    3.删除和'Ben'同部门的员工

    SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b 
    ON a.username=b.username 

    下面使用全外连接将city表中的所有作者以及user表中的所有作者,以及他们所在的城市: 

    SELECT a.*,b.* 
    FROM city as a FULL OUTER JOIN user as b 
    ON a.username=b.username 

    (三)交叉连接 

    交叉连接不带WHERE 子句,它返回被连接的两个表所有数据行的笛卡尔积,返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

    例,titles表中有6类图书,而publishers表中有8家出版社,则下列交叉连接检索到的记录数将等于6*8=48行。 

    SELECT type,pub_name 
    FROM titles CROSS JOIN publishers 
    ORDER BY type

    介绍了表连接,更确切的说是inner joins內连接. 
    內连接仅选出两张表中互相匹配的记录.因此,这会导致有时我们需要的记录没有包含进来。 
    为更好的理解这个概念,我们介绍两个表作演示。苏格兰议会中的政党表(party)和议员表(msp)。 

    party(Code,Name,Leader) 
    Code: 政党代码 
    Name: 政党名称 
    Leader: 政党领袖 

    msp(Name,Party,Constituency) 
    Name: 议员名 
    Party: 议员所在政党代码 
    Constituency: 选区 

    在介绍左连接、右连接和全连接前,有一个数据库中重要的概念要介绍一下,即空值(NULL)。 

    有时表中,更确切的说是某些字段值,可能会出现空值, 这是因为这个数据不知道是什么值或根本就不存在。 
    空值不等同于字符串中的空格,也不是数字类型的0。因此,判断某个字段值是否为空值时不能使用=,<>这些 
    判断符。必需有专用的短语:IS NULL 来选出有空值字段的记录,同理,可用 IS NOT NULL 选出不包含空值的记录。 

    例如:下面的语句选出了没有领导者的政党。(不要奇怪,苏格兰议会中确实存在这样的政党) 

    SELECT code, name FROM party 
    WHERE leader IS NULL 

    又如:一个议员被开除出党,看看他是谁。(即该议员的政党为空值) 

    SELECT name FROM msp 
    WHERE party IS NULL 

    好了,让我们言归正传,看看什么叫左连接、右连接和全连接。 

    A left join(左连接)包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。 
    同理,也存在着相同道理的 right join(右连接),即包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。 
    而full join(全连接)顾名思义,左右表中所有记录都会选出来。 

    讲到这里,有人可能要问,到底什么叫:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。 
    Ok,我们来看一个实例: 

    SELECT msp.name, party.name 
    FROM msp JOIN party ON party=code 

    这个是我们上一节所学的Join(注意:也叫inner join),这个语句的本意是列出所有议员的名字和他所属政党。 
    你可以在  亲自执行一下该语句,看看结果是什么。 

    很遗憾,我们发现该查询的结果少了两个议员:Canavan MSP, Dennis。为什么,因为这两个议员不属于任 
    和政党,即他们的政党字段(Party)为空值。那么为什么不属于任何政党就查不出来了?这是因为空值在 
    作怪。因为议员表中政党字段(Party)的空值在政党表中找不到对应的记录作匹配,即 
    FROM msp JOIN party ON party=code 没有把该记录连接起来,而是过滤出去了。 
    在该短语中,msp在Join的左边,所有称为左表。party在Join的右边,所有称为右表。 

    Ok,现在再看看这句话,“包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录”, 
    意思应该很明白了吧。执行下面这个语句,那两个没有政党的议员就漏不了了。 

    SELECT msp.name, party.name 
    FROM msp LEFT JOIN party ON party=code 

    关于右连接,看看这个查询就明白了: 

    SELECT msp.name, party.name 
    FROM msp RIGHT JOIN party ON msp.party=party.code 

    这个查询的结果列出所有的议员和政党,包含没有议员的政党,但不包含没有政党的议员。 

    那么既要包含没有议员的政党,又要包含没有政党的议员该怎么办呢,对了,全连接(full join)。 

    SELECT msp.name, party.name 
    FROM msp FULL JOIN party ON msp.party=party.code
    当检索数据时,通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性,他们可以在任何时候增加新的数据类型。为不同实体创建新的表,尔后通过连接进行查询。

    您可能感兴趣的文章:

    • 浅谈内联函数与宏定义的区别详解
    • 深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接
    • 解析:内联,左外联,右外联,全连接,交叉连接的区别

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:Oracle的内外连接,DQL之连接查询

    关键词:

上一篇:没有了

下一篇:没有了