一 复合查询
1 单表查询回顾
like模糊匹配
还可以用字符串函数截取名字首字母出来。
需求2,按两个条件排序,一个order后面跟着两个条件。
按照两个条件来排序
需3 按照年薪降序排序
表结构只有月薪,那我们直接按照月薪*12 + comm奖金排列的啦
可是如果comm是null,此时我们就发现和为null,因为任意运算完都是null,此时我们就可以用先前学的函数isnull来判断comm是不是null,是就应该加0,不是就返回comm。
先筛选再排序,所以可以直接用年薪这个重命名的。
需求4
最高工资可以用select以及聚合函数找出来,然后where筛选判断。由此我们发现select返回的可以用于做where判断。
我们还可以排序后直接Limit显示。
需求5
平均工资不存在,所以需要我们先计算出来,同样用where比较。
而且我们不能直接where sal > avg(sal),聚合函数的使用必须结合select,先有select筛选数据,才能聚合统计。
需求6 分组查询
显示每个部门的平均工资和最高工资
规整化,这些内置函数的传参可以是列名,也可以是数字,我们就认为select会把对应列的数字填入,替换列名。
需求7
对分组查询结果做判断用having。
需求8
count(*)表示查找所有行,包括空行,若是传个列名,则会统计该列不为null的行。
注意: select job 分组和不分组的含义是不一样的,分组后,会将单个组的唯一行返回,不分组则是将整个表返回。
2 多表笛卡尔积
雇员表只有部门编号,没有部门名,部门名在其它表
所以我们需要用多表查询来获取我们需要的数据。
select直接查询两张表,但是每一行都和另一个表的所有行做匹配,这个穷举组合就是笛卡尔积。显示的字段顺序默认和from后面的表名顺序,显示emp表的,然后是dept的。
做如下筛选,筛选后重复行就少了。
我们select可以用表名.+成员来指定,也可以直接用列名称
两个表中要查的列名重复怎么办吗,此时要指定是哪个表的。
需求2
多加一个判断即可。
需求3
工资等级在另一张表中,员工工资应该只和对应范围的工资等级组合。
要用工资做筛选判断,当sal不在对应行的工资范围内是无意义的,
3 自链接
一张表和其它表穷举 - 笛卡尔积,同一张表一起做笛卡尔积,称为自连接,重命名原因: 免得表名一样,列名一样,到时候指定列名取数据,不知道从哪列取数据。有意思的是我们可以在from中重命名,先前的重命名是在select 和from中对列名重命名。
使用场景 1 ford的员工信息中有上级领导的员工编号,所以我们可以先找领导编号,再根据员工编号在员工表找领导信息,都和一张表有关。
领导员工编号存在mgr中。
方案1 子查询
先把员工的领导的编号查出来,再去select找信息。
方案2 将员工和领导信息一起显示
两个表做笛卡尔积,此时每行都会和表中的每一行做组合,我们只要name为ford员工的信息,而他的领导信息行满足e1.mgr = e2.empno。
4 单列子查询
单行子查询,子查询只会查出一行,列可能是一列,也可能是多列,但是返回值如果是给where做判断,肯定只能是单列。
需求1
先用select查出smith的部门号,然后用部门号再筛选,
需求2
在表中找和十号部门中的岗位相同的员工,也就是说10号部门内有多个工作岗位,我们要去其它部门找相同岗位的员工。此时返回结果是多行,单列。这个in表示在某个集合中,和先前提及的in运算符类似。
保证员工不是10号部门的。!= 和 <>等价。
如果我们还要获取部门信息,此时就是要和另一张部门表做笛卡尔积,select出来的一张表(该表要起个名字)和部门表做笛卡尔积。
对笛卡尔积结果做筛选,筛选一些无意义的组合。
此时我们发现一个select出来的表也能做笛卡尔积,而且from语句也能和select子查询组合。
需求3 找工资比30号部门所有员工高的员工
聚合返回的就是30号部门的最高工资,一行一列,可以用于where判断。
把30部门工资筛选出来,然后用all关键字,条件表示比该集合内所有工资都要高。
需求4
所以我们可以用min找到最低的或者用any关键字
5 多列子查询
需求1
单行两列信息如何比较和其它行比较。
直接用where等于判断或者in。
子查询要么和where,充当判断条件,要么和from,做笛卡尔积。
6 from字句和子查询
需求1
先把每个部门的平均工资找出来
将员工表和平均工资表做笛卡尔积,这样每个员工就拿到了自己部门的平均工资
我们要对这个暴力组合做筛选,就是只需要和自己部门的平均工资做链接。
最后的筛选,员工工资高于部门平均工资。
变化: 还要获取他们的办公地点,这就要再和部门表做笛卡尔积。
需求2
先获取部门最高工资,然后和员工表做笛卡尔积。
where筛选无意义的组合,以及找到每个部门最高工资的员工。
需求3
下面表里有三个元素,还缺人员数量。
显然我们需要计算出各个部门人员数量的一张表格。
两张表做笛卡尔积,筛选掉多余项,10号部门和其它部门的员工人数做拼接无意义。
7 合并查询
实际上我们可以直接在where用or就行了。
二 表的连接
1 内连接
内连接就是笛卡尔积的标准写法。
先前笛卡尔积写法,员工表和部门表做穷举组合,where筛选,只保留员工和自己部门的组合行,和其它部门信息的组合要筛选掉。
内连接写法
2 左外连接
建立学生表
插入数据
成绩表
插入数据,显然成绩表用id和学生表的id相关联,
需求1
我们将上面两张表做左外链接,如果做笛卡尔积(内连接),此时我们发现如果左表的学生成绩不存在,也就是找不到相等的exam.id,此时就会被舍去,左右表都是如此,
但是左外连接则是会保留左边的全部行,就算没有符合的exam行进行组合,也会填上空。
3 右外连接
上下两句select等效,显然我们在写左右连接时要注意表名的位置。
场景1
尝试用左外连接,保留部门表的信息,同时和员工表穷举组合,on筛选掉无意义的组合。
由于会有多个员工属于同一个部门,所以一个部门信息能和多个员工信息组合,不会被筛选掉。