文章目录

• 1. 复合查询
• • 多表笛卡尔积
  • 自连接
  • 在where子句使用子查询
  • • 单行子查询
    • 多行子查询
    • • in关键字
      • all关键字
      • any关键字
      • 多列子查询
      • 在from子句中使用子查询
      • 合并查询
      • • union
        • union all
        • 2. 内连接
        • 3. 外连接
        • • 左外连接
          • 右外连接

            1. 复合查询

            多表笛卡尔积

            显示雇员名、雇员工资以及所在部门的名字

            由于员工 信息属于 emp表 而所在部门名字属于 dept表

            数据来自不同的表，所以需要进行多表查询

            ---

            

            表示从 emp (员工表) 和dept (部门表) 中获取信息

            ---

            

            分别用emp员工的信息 与 dept 部门表的信息 做 穷举 组合

            就称 两张表 进行笛卡尔积

            就把两张表 变成了 一张表 ，就可以进行单表查询

            ---

            

            如 smith 本来是20号部门的，把10号部门的信息传过来 是没有意义的

            ---

            

            输入 select * from emp,dept where emp.deptno =dept.deptno;

            把对应的部门号 与部门信息 匹配上

            ---

            

            输入 select ename ,sal,dname from emp,dept where emp.deptno =dept.deptno;

            就可以显示雇员名、雇员工资以及所在部门的名字

            自连接

            自连接 指的是在同一张表连接查询

            ---

            

            将同一张表单纯的写两次，是不可以一起查询的

            ---

            

            可以把同一张表的表名通过重命名的方式 分别 进行不同命名 即可

            把对同一张表 进行笛卡尔积的 行为 叫做 自连接

            ---

            显示员工FORD的上级领导的编号和姓名

            先找 员工FORD 和领导编号 ，与员工表有关

            再通过领导的编号，找领导信息，也是与员工表有关

            所以就需要借助 自连接

            ---

            

            输入 select e2.ename ,e2.empno from emp e1,emp e2 where e1.ename=‘FORD’ and e1.mgr=e2.empno;

            通过第一张表emp中的 员工FORD， 找到对应的领导编号，正好对应第二张emp表的员工编号

            所以设置 e1.mgr =e2.empno

            这样就可以 通过员工FORD 找到 对应的 领导 的名字和编号

            在where子句使用子查询

            子查询 是指嵌入其他sql语句中的select语句，也叫嵌套查询

            单行子查询

            返回一行记录的子查询

            

            查看emp表 的员工信息

            ---

            

            想要查看工资最高的员工的名字和工作岗位

            首先 要找到 最高工资，但是发现这样写是不可以的

            子查询： 在 一条sql内部 再执行 select查询

            先执行内部的sql语句，再执行外部的sql语句

            ---

            

            先找到 最高工资 ，再通过最高工资找到对应这个人的名字和工作信息

            ---

            多行子查询

            返回多行记录的子查询

            in关键字

            用来进行判断一个对应的列值是否在一个集合当中

            若在集合中，则为查找成功

            ---

            查询和10号部门的工作岗位相同雇员的名字、岗位、工资、部门号 ，但是不包含10号自己的

            

            首先要找到10号部门的工作岗位

            ---

            

            再通过in关键字，只有工作种类在 10号部门的工作岗位的集合中 ，才能返回

            ---

            

            使用and 关键字 ，同时也不包含10号部门的人的信息

            ---

            all关键字

            表示所有人

            ---

            显示工资比部门30的所有员工的工资高的员工的姓名 、工资 和部门号

            

            先取到 30号部门的所有员工对应的工资

            加入 distinct 去除重复的工资

            ---

            

            all (select distinct sal from emp where deptno=30) 表示大于 30号部门的所有员工的薪资

            ---

            any关键字

            表示比任意一个高

            ---

            显示工资比部门30的任意员工的工资高的员工的排名(包含自己部门)

            

            先取到 30号部门的所有员工对应的工资

            加入 distinct 去除重复的工资

            ---

            

            any(select distinct sal from emp where deptno=30) 表示大于 30号部门的任意员工的薪资

            ---

            多列子查询

            多列子查询 是指 返回多个列数据的子查询语句

            ---

            查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

            

            首先要找到SMITH对应的部门和岗位

            ---

            

            因为要比较的是 两个条件 部门和岗位

            而mysql 支持 圆括号 直接写两列

            将SMITH 对应的部门和岗位作为筛选条件

            ---

            

            还需添加条件 ename <> ‘SMITH’ 不能为SMITH员工本身

            ---

            在from子句中使用子查询

            在查询语句在from子句中，把一个子查询当作一个临时表使用

            ---

            显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

            

            首先找到每个部门的平均工资

            通过group by 将各个部门分开，再获取对应的每个部门的平均工资

            ---

            

            将 select deptno,avg(sal) from emp group by deptno 整体操作所形成的数据 看作一张表，并重命名为 tmp，与emp表 做 笛卡尔积

            ---

            

            寻找到emp表的部门号 与 tmp表的部门号 相同的 数据 才是合适的，所以加上 emp.deptno=tmp.deptno

            ---

            

            select deptno,avg(sal) myavg from emp group by deptno

            为了不让其在后面比较起冲突，所以 将avg(sal) 重命名为 myavg

            再添加 emp.sal > tmp.myavg 筛选条件，显示每个高于自己部门平均工资的员工信息

            ---

            合并查询

            为了合并多个select的执行结果，可以使用集合符 union 和 union all

            ---

            union

            

            该操作符用于取得 两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行

            ---

            

            select * from emp where sal>2500 即工资大于2500 的人的信息

            ---

            

            select * from emp where job=‘MANAGER’ 即工作岗位为 manager的人的信息

            ---

            发现两者使用重合信息存在的，所以可以使用 union

            

            使用 union ，会自动去除 两者 重合部分的 重复数据

            ---

            union all

            在union的基础上，不会去除重复部分的重复数据

            

            重复的数据会被保留

            ---

            2. 内连接

            内连接 是指 使用where子句对两种表形式的笛卡尔积 进行筛选

            ---

            语法：

            select 字段 from 表1 inner join 表2 on 连接条件 and 连接条件;

            ---

            显示SMITH的名字和部门名称

            

            筛选条件为 emp.deptno=dept.deptno and ename=‘SMITH’

            表示 两张表的 对应 部门号 要相同 ，同时 名字为 SMITH

            3. 外连接

            左外连接

            左侧表完全显示 右侧的表按条件拼接(条件满足拼接，条件不满足拼NULL)

            ---

            语法：

            select 字段名 from 表名1 left join 表名2 on 连接条件

            相对于内连接的语法，外连接语法只是把inner 替换成了 left

            ---

            

            创建一张表 stu ，内部包含 id 和 name

            ---

            

            向stu表中 插入对应的数据 ，并 将其显示出来

            ---

            

            再次创建一张成绩表 exam ，内部 包含id 和grade

            ---

            

            向exam表中 插入对应的数据，并将其显示出来

            ---

            

            发现stu 学生表中 有四名同学，而exam成绩表中 只有三个成绩

            而且成绩表中有一个 不存在的id值为11 的学生

            ---

            查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

            

            由于有些学生是没有成绩的，就导致两张表中的id 匹配不上

            若只拿id 做内连接，就只有 id值为 1 2 的信息能够显示

            ---

            

            为了保证所有学生的信息都显示，所以使用 左外连接

            右外连接

            左侧的表按条件拼接(条件满足拼接，条件不满足拼NULL) 右侧表完全显示

            ---

            语法：

            select 字段 from 表名1 right join 表名2 on 连接条件

            ---

            对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要

            显示出来

            

            stu 学生表中 有四名同学，而exam成绩表中 只有三个成绩

            而且成绩表中有一个 不存在的id值为11 的学生

            ---

            

            此时右侧exam表的三个id值 全部显示出来了

            左侧stu表 由于只有两个id值与exam表中的id值对应，所以只显示2个，剩下显示为NULL