数据库MySQL学习笔记（五）

关联查询

 

在数据库的使用中，很多时候需要用到关联查询，同时从两个表或更多的表中同时获得所有想要的信息。

1、概念：当在查询时，我们所需要的数据不在一张表中，可能在两个表或更多的表中，此时我们需要同时操作这些表，即关联查询。

2、等值连接：在做多张表查询时，这些表中存有关联的两个字段，使用某张表中的一条记录通过相关联的字段与另一张表的记录匹配，组合成一条记录。

3、笛卡尔积：在做多张表查询时，从某张表中取一条记录，与另一张表的所有记录进行组合。比如表a有x条记录，表b有y条记录，则对a和b进行笛卡尔积后得到的结果条数为x*y条。（笛卡尔积通常没有意义）

4、内连接：使用了join in的就是内连接，效果和等值连接相同

                      用法：表a [inner] join 表b on 关联条件

5、外连接

（1）概念：在做多表查询时，我们所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据，此时需要使用外连接。外连接的结果集是内连接的结果集+驱动表中不匹配的结果集。

（2）相关概念：外连接涉及两个表：

          驱动表（主表）：主表是需要全部显示的表，其中的记录不管满足条件与否都会显示在结果中。

          匹配表（从表、副表）：只显示满足关联条件的数据

（3）外连接的种类：

           1⃣️ 左外连接（左边的表作驱动表）

                 用法：表a left [outer] join 表b on 关联条件

           2⃣️ 右外连接（右边的表作驱动表）

                 用法：表a right [outer] join 表b on 关联条件

           3⃣️ 全外连接 （两边的表不管满足条件与否，都显示）

                 用法：表a full [outer] join 表b on 关联条件  

6、自连接：同一张表自己和自己做关联查询

7、高级关联查询

 （1）一些自己的理解：

          很多时候，一个简单的查询语句不能满足我们的需求，有时需要预先经过一次查询之后，利用得到的结果进行第二次查询。在书写sql语言时，总感觉不理解它的查询语句的感觉，思考之后有一点自己的淡淡的理解。

          首先，在需要进行两次查询才可以获得结果的sql语句中，先执行的查询叫子查询，被子查询嵌入的查询语句称为父查询。例如查找和“刘晨”在同一个系学习的学生， 此时有student表，记录了学生的学号、姓名和所在系。解决这个问题，应先查询“刘晨”所在的系，再查询这个系里的学生，sql语句如下：select sdept from student where sname="刘晨"  select sno,sname,sdept from student where sdept='刚才查询出的系名'  

          在这种嵌套查询中，存在两种查询种类：相关子查询和不相关子查询。这两种查询方式在原理上有很大差别，也是我认为理解sql语句的关键。

          相关子查询即子查询的查询条件依赖于父查询，查询时的原理是：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回值为真，则此元组放入结果表。然后再取外层查询的下一个元组重复这个过程，知道外层查询的表全部检查完为止。

          不相关子查询即子查询的查询条件不依赖父查询（例如上面的例子就是不相关子查询），是从内到外逐层进行处理，子查询的结果构成父查询的查找条件。

          在自己思考具体的sql语句时，将具体问题和这两个查询方式进行结合，判断问题用哪个查询方式更好，会对sql语言有一个更好的了解。

（2）子查询存在的形式：

          子查询可以在where、from、having、select子句中出现。

8、一些具体的语句实例

/*select max(sal),min(sal),job,deptno from emp group by job,deptno*/

/*1.查询平均工资大于2000的部门的最高工资和最低工资，
以及平均工资
select MAX(sal),MIN(sal),AVG(sal),deptno from emp group by deptno having AVG(sal) > 2000*/

/*2.查询平均工资大于1500的职位的最高工资以及最低工资，
并且按照职位降序排序
select job,max(sal),min(sal) from emp group by job having avg(sal) > 1500 order by job desc*/

/*3.查询平均工资大于1500,平均奖金小于200的职位。
select job,avg(sal) from emp group by job having avg(sal) > 1000 and avg(comm) > 200*/

/*TCL*/
/*1.开启事务
start TRANSACTION*/
/*2.设置保留点
SAVEPOINT a*/
/*3.向a表中插入一条数据
insert into a values(2,'李四')*/
/*4.回滚
ROLLBACK*/
/*5.查询a表中的数据
select * from a*/
/*6.向a表中插入一条数据
insert into a values(2,'李四')*/

/*7.将数据提交到数据库
commit*/
/*8.查询a表中的数据
select * from a*/

/*DCL*/
/*9.创建用户
create user shiyu@localhost IDENTIFIED by '123456'*/

/*10.显示权限
show GRANTS for shiyu@localhost*/

/*11.授权
grant create on jsd1707.*  to shiyu@localhost*/

/*12. 查询emp表中和dept表中所有的数据
select * from emp,dept*/

/*13.查询emp表中每个员工的公司都在那个城市
select e.ename,e.job,e.deptno,d.loc from emp e,dept d where e.deptno=d.deptno*/

/*14.查询emp表中每个员工的公司都在那个城市
select * from emp e join dept d on e.deptno = d.deptno*/

/*15.查询smith所在的部门的部门名称以及位置以及smith的
年薪以及年奖金
select dname,loc,sal*12,comm from emp e,dept d where e.deptno=d.deptno and e.ename='SMITH'

select dname,loc,sal*12,comm from emp join dept on emp.deptno = dept.deptno WHERE ename='SMITH'*/

/*16.查询20和30部门的部门名称，部门地址及其员工的姓名和工资
select dname,loc,ename,sal from dept join emp on dept.deptno=emp.deptno where emp.deptno in (20,30)*/

/*17.查询emp表中所有员工的部门信息以及员工信息和薪资
select dname,loc,e.deptno,ename,sal from emp e left join dept d on e.deptno = d.deptno

select dname,loc,e.deptno,ename,sal from dept d right join emp e on d.deptno = e.deptno*/

/*18.查询所有部门的员工信息以及部门名称和位置
select dname,loc,d.deptno,ename from dept d left join emp e on d.deptno = e.deptno

select dname,loc,d.deptno,ename from emp e right join dept d on e.deptno = d.deptno*/

/*19.查询员工表中的所有员工以及部门表中所有部门
select e.*,d.* from emp e full join dept d on e.deptno = d.deptno*/

/*20.查询员工表中的员工姓名和薪水以及其领导的姓名
以及薪水
select e.ename "员工姓名",e.sal "员工工资",m.ename "领导姓名",m.sal "领导工资" from emp e join emp m on e.mgr = m.empno 
order by e.ename asc*/

/*21.查询员工表中的员工姓名以及薪水以及其下属的
员工姓名和薪水
select m.ename "领导姓名",m.sal "领导工资",e.ename "下属名称",e.sal "下属工资" from emp m join emp e on m.empno = e.mgr*/

/*22.查询emp表中哪些人有领导
select ename from emp where mgr is not null*/

/*23.查询emp表中谁是最高领导
select ename from emp where mgr is null*/

/*24.查询工资大于员工JONES工资的员工的信息
select sal from emp where ename = 'JONES' 

select * from emp where sal > 2975

select * from emp where sal > (select sal from emp where ename = 'JONES')*/

/*25.查询员工表中工资大于10号部门的平均工资的
员工的姓名，职位，工资及其部门号
select avg(sal) from emp where deptno=10

select ename,job,sal,deptno from emp where sal > (select avg(sal) from emp where deptno=10)*/

/*26.查询工资等于每个部门的平均工资的人员信息
select avg(sal) from emp group by deptno
select * from emp where sal in (select avg(sal) from emp group by deptno)*/

/*27.查询工资大于所有部门的平均工资的人员信息
select * from emp where sal >all (select avg(sal) from emp group by deptno)*/

/*28.查询工资大于任意部门的平均工资的人员信息
select * from emp where sal >any (select avg(sal) from emp group by deptno)*/

/*29.查询工资和奖金与员工scott相同的其他员工信息
select sal from emp where ename='SCOTT'
select comm from emp where ename='SCOTT'

select ename from emp where sal = (select sal from emp where ename='SCOTT') and IFNULL(comm,0) = IFNULL((select comm from emp where ename='SCOTT'),0) and ename <> 'SCOTT'*/