多表查询

 

1.什么是多表查询？
    一次select语句需要查询的内容来自于不止一张表。
    同时从多张表中查询数据。

    单表查询：
    select id,last_name,salary
    from s_emp
    where salary > 1400 and dept_id in(41,42)
    order by salary desc,id asc;
    单表查询能查到的数据是有限的。

    查询所有员工的id、last_name？
    select id,last_name
    from s_emp;

    查询所有部门的id、name？
    select id,name
    from s_dept;

    查询所有员工id、last_name以及所在部门的
    id和name？
    select id,last_name,id,name
    from s_emp,s_dept;

    语法：
    select ...
    from 表1,表2,表3.....
    where ...
    order by ....;

    出现字段名冲突时，要使用 表名.列名 的形式，
    声明可能出现冲突的字段来自于哪张表。
    select s_emp.id,last_name,s_dept.id,name
    from s_emp,s_dept;

    还可以给表名起一个别名。
    起到的别名可以在整个select语句中代替表名使用。
    语法：
    select 别名1.字段,别名2.字段......
    from 表1 别名1,表2 别名2;

    查询所有员工的id、last_name以及
    所在部门的id、name？
    select s1.id,s1.last_name,s2.id,s2.name
    from s_emp s1,s_dept s2;

    
2.消除笛卡尔积 
    笛卡尔积是数学运算中集合进行乘法运算所产生的结果集。

 

    
    A = { 1 , 2 , 3 }
    B = { a , b }

    计算：A x B = {(1,a),(1,b),(2,a),(2,b)
                                 (3,a),(3,b)}
                一共有2x3=6个结果。

    数据库进行多表查询，就会产生笛卡尔积。

    表1：学生姓名表
    学号        姓名
    1            张三
    2            李四
    3            王五
  -------------------
    表2：学生成绩表
    学号        分数
    1            80
    2            90
    3            70
    要求：查询所有学生的id、姓名以及成绩？

    1        张三        1        80
    1        张三        2        90
    1        张三        3        70

    2        李四        1        80
    2        李四        2        90
    2        李四        3        70

    3        王五        1        80
    3        王五        2        90
    3        王五        3        70

    思路：
    使用where关键字增加、指定查询条件，
    从笛卡尔积中把不需要的数据筛除出去。

    1)等值连接
        将两张表中产生关联的字段使用等号进行连接。
        查询所有员工的id、last_name以及
        所在部门的id、name？
        字段：s_emp.dept_id = s_dept.id

        select s1.id,s1.last_name,s2.id,s2.name
        from s_emp s1,s_dept s2
        where s1.dept_id = s2.id;

    2)不等值连接
        大于
        大于等于
        小于
        小于等于
        逻辑比较符：between in 
        
        查询所有员工id、last_name以及
        工资收入等级？
        select e.id,e.last_name,g.name
        from s_emp e,s_gender g
        where e.salary between g.minSal and g.maxSal;

        或者：where e.salary >=g.minSal 
                            and e.salary <= g.maxSal;

        表1：员工表
        id  salary
        1        900
        2        1300    
        3        2100

        表2：工资等级表
        最小值  最大值  等级名称
        0                1000        蓝领
        1000        1500        白领
        1500        2500        金领

        结果：
        员工ID        员工工资        最小值        最大值        等级名称
        1                900                0                1000        蓝领
        1                900                1000        1500        白领
        1                900                1500        2500        金领
        2                1300            0                1000        蓝领
        2                1300            1000        1500        白领
        2                1300            1500        2500        金领
        3                2100            0                1000        蓝领
        3                2100            1000        1500        白领
        3                2100            1500        2500        金领

    3)外连接
        a)左外连接
            查询所有员工的id、last_name以及
            所在部门的id、name？
            select s1.id,s1.last_name,s2.id,s2.name
            from s_emp s1,s_dept s2
            where s1.dept_id = s2.id;

            SQL:insert into s_emp(id,last_name)
                    values(999,'_briup');

            查询所有员工的id、last_name以及
            所在部门的id、name？要求把没有部门的员工
            也显示出来？
            思路：让员工表 左外连接 到部门表。

            左外连接：
            A左外连接到B，就可以查出来没有B的A。

            语法：
            1)标准SQL
                select...
                from 表1 left [outer] join 表2
                    on 连接条件;

                select e.id,e.last_name,d.id,d.name
                from s_emp e left join s_dept d
                    on e.dept_id = d.id;
                两张表顺序不能颠倒。

            2)Oracle特色语法

        b)右外连接
            右外连接和左外连接就是相反的。
            A表左外连接到B表，相当于B表右外连接到A表。

            语法：
            select...
                from 表1 right [outer] join 表2
                    on 连接条件;
            
            左外连接：
            select e.id,e.last_name,d.id,d.name
                from s_emp e left join s_dept d
                    on e.dept_id = d.id;
            右外连接：
            select e.id,e.last_name,d.id,d.name
                from s_dept d right join s_emp e
                    on e.dept_id = d.id;
            以上两种写法是等价的。

            练习：
            先向数据库中插入一条数据：
            insert into s_dept(id,name)
            values(1000,'Teaching');
            commit;

            查询所有员工的id、last_name以及对应部门的
            id、name？要求把没有员工的部门也查询出来？
            使用左外连接和右外连接两种方式。
            
        c)全连接