day4&5-2023年2/26&3/4

回顾前面学的

 

补充：

• distinct关键字--去重

(1)用于将查询结果去除重复记录，注意原表数据不会修改，只是查询结果去重。

select distinct job from emp;

(2)distinct 只能出现在所有字段的最前方，否则会出现语法错误。

X select ename,distinct job from emp;

(3)distinct 出现在job,deptno 两个字段前，表示两个字段联合起来去重。-->即：当两个字段都重复时只保留一个结果，否则有一个字段重复时仍然显示

(4)统计一下工作岗位的数量？

select count(distinc job) from emp;

结论：distinct可以用在分组函数中。

八、连接查询

8.1 什么是连接查询？

从一张表中单独查询，称为单表查询。

emp表和dept表联合起来查询数据，从emp表中区员工名字，从dept表中取部门名字，这种跨表查询，多张表联合起来查询数据，被称为连接查询。

8.2连接查询的分类？

• 根据语法年代分：

  SQL92:1992年出现的语法

  SQL99:1999年出现的语法（本课学习重点）

  • 根据表连接的方式分类：

    内连接（inner join)：

            等值连接，

            非等值连接，

            自连接。

    外连接：

            左外连接（左连续），

            右外连续（右连续）。

            全连接（不讲）

8.3 笛卡尔积现象

• 当两张表进行连接查询时，没有任何条件的限制会发生什么现象？

• 

select ename,dname from emp,dept;

当两张表进行连接查询，没有任何条件限制的时候，最终查询数据是两张表条数的乘积-->笛卡尔积现象。

• 如何避免笛卡尔积现象？

  连接时加条件，满足条件的记录才会被筛选出来！

  eg.查询员工名字及其（emp表）所属的部门(deptno)和部门名（dname)。（利用emp和dept两表都有deptno作为连接条件）

  【SQL92写法】

  select

          ename,dname

  from

          emp,dept

  where

          emp.deptno =dept,deptno;

• 思考：结果显示14条变少了，但是匹配次数有没有减少？

  实际上没有还是56次，只不过进行了四选一，次数没有减少。

  • ！优化：(给表起别名，效率问题)

    select

            e.ename , d.dname

    from

            emp e,dept d

    where

            e.deptno=d.deptno;

通过笛卡尔积知道，表的连接次数越多，表的执行效率越低，需要尽量降低表连接的次数。

8.4内连接之等值连接

eg.查询员工名字及其（emp表）所属的部门(deptno)和部门名（dname)。（利用emp和dept两表都有deptno作为连接条件）

【SQL92写法】

select

        e.name,e.deptno,d.dname

from

        emp e,dept d

where

        e.deptno=d.deptno;

• SQL92的缺点：结构不清晰，表的连接条件和后期进一步筛选的条件，都写在where后面。

【SQL99写法】

select

        e.ename,e.deptno,d.dname

from

        emp e

join

        dept d

on

        e.deptno=d.deptno;

• SQL99语法格式：

  • select

            ...

    from

            tableA a

    [innner] join

            tableB b

    on

            两表的连接条件

    where

            进一步筛选条件

• 优点：

• 表连接的条件是独立的，连接之后需要进行进一步筛选，再往后添加where子句。

8.5内连接之非等值连接

select

        e.ename,e.sal,s.grade

from

        emp e

inner join

        salgrade s

on

        e.sal between s.losal and s.hisal;//条件不是一个等量关系称为非等值连接

8.6内连接之自连接

eg.查询员工的上级领导，要求显示员工名和对应的领导名？

内连接-自连接：就是把一张表当成两张表，给表起两个别名。

select

        a.ename as '员工姓名',b.ename as '领导姓名'

from

        emp a

inner join

        emp b

on

        a.mgr=b.empno;//利用emp表的mgr找对应的领导姓名

8.7外连接（outer join)

【外连接和内连接的区别：】

• 外连接有主表，分主次，分为左(外)连接和右(外)连接，左连接就是把join关键字左边的表看成主表，主要是为了将主表中数据全部查询出来，捎带着关联查询右边的表。

• 任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法。区别是显示结果会按from后面 join关键字前面的表格式来显示。

• 内连接的两张表是平等的关系不分主次，只会显示满足条件的结果。

思考：外连接的查询结果条数一定是>= 内连接 的查询结果条数？

正确。

【语法格式】

//outer 可以省略，带着可读性更强

select

        ...

from

        tableA a

right/left [outer] join

        tableB b

on

        连接条件;

• eg.查询每个员工的上级领导，要求显示所有员工的名字和领导名。

select

        a.ename as '员工名字',b.ename as '领导名字'

from

        emp a

right outer join

// join 关键字右边的是主表-b

        emp b

on

        a.mgr=b.empno;

• eg.显示员工名及其所在部门名,要求显示所有部门名。

select

        e.ename,d.dname

from

        emp e

right join

        dept d

on

        e.deptno=d.deptno;

8.8多表连接

【语法格式】

select

        ...

from

        tableA a

join

        tableB b

on

        a和b连接条件

join

        c

on

        a和c连接条件

join

        d

on

        a和d连接条件

...

【案例】e.g.找出每个员工的部门名以及工资等级，要求显示员工名(emp)、部门名(dept)、薪资(emp)、薪资等级(salgrade)？

select

        e.ename,d.dname,e.salary,s.salgrade

from

        emp e

join

        dept d

on

        e.deptno=d.deptno

join

        salgrade s

on

        e.salary between s.losal and s.hisal;

【案例】e.g.找出每个员工的部门名以及工资等级，还有上级领导（注意：BOSS没有上级领导，需要使用外连接才能显示全），要求显示员工名(emp)、部门名(dept)、薪资(emp)、薪资等级(salgrade)？

select

        e.ename,d.deptno,d.dname,e.salary,s.grade,l.ename '上级领导'

from

        emp e join dept d

on

        e.deptno=d.deptno

join

        salgrade s

on

        e.salary between s.losal and s.hisal

left join

        emp l

on

        e.mgr=l.empno;

(课程p47-p58)