1.链接查询
什么是连接查询 ?
在实际开发中,大部分情况下都不是从单表查询,一般都是多张表联合查询取出最终的结果
在实际开发中,一般一个业务都会对多张表,比如:学生和班级,起码两张表
2.链接查询的分类
根据语法出现的额年代来划分的话们包括:
sql 92(一些老的dba可能还在使用这种语法,dbs:数据库管理员)
sql 99(比较新的语法)
根据表的连接方式来划分,包括:
内连接:
等值链接
非等值链接
自连接
外连接:
左外连接
右外连接
全连接(很少用)
3.在表的连接查询方面又一种现象被称为:笛卡尔积现象。(笛卡尔乘积现象)
案例:找出每个员工的部门名称,要求显示员工名和部门名
mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
| WARD | OPERATIONS |
| WARD | SALES |
| WARD | RESEARCH |
| WARD | ACCOUNTING |
| JONES | OPERATIONS |
| JONES | SALES |
| JONES | RESEARCH |
| JONES | ACCOUNTING |
| MARTIN | OPERATIONS |
| MARTIN | SALES |
| MARTIN | RESEARCH |
| MARTIN | ACCOUNTING |
| BLAKE | OPERATIONS |
| BLAKE | SALES |
| BLAKE | RESEARCH |
| BLAKE | ACCOUNTING |
| CLARK | OPERATIONS |
| CLARK | SALES |
| CLARK | RESEARCH |
| CLARK | ACCOUNTING |
| SCOTT | OPERATIONS |
| SCOTT | SALES |
| SCOTT | RESEARCH |
| SCOTT | ACCOUNTING |
| KING | OPERATIONS |
| KING | SALES |
| KING | RESEARCH |
| KING | ACCOUNTING |
| TURNER | OPERATIONS |
| TURNER | SALES |
| TURNER | RESEARCH |
| TURNER | ACCOUNTING |
| ADAMS | OPERATIONS |
| ADAMS | SALES |
| ADAMS | RESEARCH |
| ADAMS | ACCOUNTING |
| JAMES | OPERATIONS |
| JAMES | SALES |
| JAMES | RESEARCH |
| JAMES | ACCOUNTING |
| FORD | OPERATIONS |
| FORD | SALES |
| FORD | RESEARCH |
| FORD | ACCOUNTING |
| MILLER | OPERATIONS |
| MILLER | SALES |
| MILLER | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
56 rows in set (0.00 sec)
关于表的别名:
mysql> select e.ename,d.dname from emp e,dept d;
好处:1.执行效率高 2.可读性强
怎么避免笛卡尔现象,当然是加条件进行过滤,不会减少匹配次数
找出每个员工的部门名称,要求显示员工名和部门名
select e.ename,d.dname from emp e,dept d where e.deptno =d.deptno;
mysql> select e.ename,d.dname from emp e,dept d where e.deptno =d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
内连接之等值连接:最大特点是:条件是等量关系。
案例:查询每个员工的部门名称,要求显示员工名和部门名
inner join … on …
inner 可省略,带着inner可读性强
语法:… A
inner join b
on
连接条件
where
…
sql 99语法更清晰:表的连接条件和后来的where条件分离/
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
内连接之非等值连接:最大特点是:连接条件中的关系是非等值连接
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select ename,sal,grade from emp join salgrade on sal <hisal and sal>losal;
mysql> select ename,sal,grade from emp join salgrade on sal <hisal and sal>losal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
12 rows in set (0.00 sec)
/*between 低 and 高*/
mysql> select ename,sal,grade from emp join salgrade on sal between losal and hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
自连接:最大特点是:一张表看作两张表。自己连接自己,重命名。
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。
select a.empno,a.ename,b.ename from emp a,emp b where a.mgr =b.empno;
select a.empno,a.ename,b.ename from emp a join emp b on a.mgr =b.empno;
mysql> select a.empno,a.ename,b.ename from emp a,emp b where a.mgr =b.empno;
+-------+--------+-------+
| empno | ename | ename |
+-------+--------+-------+
| 7369 | SMITH | FORD |
| 7499 | ALLEN | BLAKE |
| 7521 | WARD | BLAKE |
| 7566 | JONES | KING |
| 7654 | MARTIN | BLAKE |
| 7698 | BLAKE | KING |
| 7782 | CLARK | KING |
| 7788 | SCOTT | JONES |
| 7844 | TURNER | BLAKE |
| 7876 | ADAMS | SCOTT |
| 7900 | JAMES | BLAKE |
| 7902 | FORD | JONES |
| 7934 | MILLER | CLARK |
+-------+--------+-------+
13 rows in set (0.00 sec)
/*内连接 join on*/
mysql> select a.empno,a.ename,b.ename from emp a join emp b on a.mgr =b.empno;
+-------+--------+-------+
| empno | ename | ename |
+-------+--------+-------+
| 7369 | SMITH | FORD |
| 7499 | ALLEN | BLAKE |
| 7521 | WARD | BLAKE |
| 7566 | JONES | KING |
| 7654 | MARTIN | BLAKE |
| 7698 | BLAKE | KING |
| 7782 | CLARK | KING |
| 7788 | SCOTT | JONES |
| 7844 | TURNER | BLAKE |
| 7876 | ADAMS | SCOTT |
| 7900 | JAMES | BLAKE |
| 7902 | FORD | JONES |
| 7934 | MILLER | CLARK |
+-------+--------+-------+
13 rows in set (0.00 sec)
外连接
什么是外连接,跟内连接有什么区别?
内连接:假设a和b表进行连接,使用内连接的话,凡是a表和b表能够匹配上的记录查询出来,这就是内连接
a b 两张表没有祝福之分,两张表是平等的
外连接:
假设 a 和 b 表进行连接,使用外连接的话,aB两张表中有一张是主表,一张是副表,主要查询主表中的数据捎带着查询附表,当附表中的数据没有和主表中的数据匹配上,附表自动模拟出mull与之匹配。
左外连接
select
a.ename,b.ename
from
emp a
left join
emp b
on
a.mgr =b.empno;
mysql> select a.ename '员工',b.ename '领导'from emp a left join emp b on a.mgr=b.empno;
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
14 rows in set (0.00 sec)
右 外连接 'outer '可以省略
select a.ename '员工',b.ename '领导' from emp b right outer join emp a on a.mgr=b.empno;
mysql> select a.ename '员工',b.ename '领导' from emp b right outer join emp a on a.mgr=b.empno;
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
14 rows in set (0.00 sec)
案列:找出那个部门没有员工
mysql> select d.* from emp e right join dept d on e.deptno =d.deptno where e.empno is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)
三张表怎么连接查询? a join b join c on …
表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接
案例:找出每一个员工的部门名称以及工资等级
mysql> select e.ename,d.dname,s.grade from emp e join dept d join salgrade s on e.deptno=d.deptno where e.sal between s.losal and hisal;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.00 sec)
案例:找出每个员工的部门名称、工资等级、上级领导
mysql> select e.ename,d.dname,c.ename from emp e join dept d on e.deptno=d.deptno left join emp c on e.mgr=c.empno;
+--------+------------+-------+
| ename | dname | ename |
+--------+------------+-------+
| SMITH | RESEARCH | FORD |
| ALLEN | SALES | BLAKE |
| WARD | SALES | BLAKE |
| JONES | RESEARCH | KING |
| MARTIN | SALES | BLAKE |
| BLAKE | SALES | KING |
| CLARK | ACCOUNTING | KING |
| SCOTT | RESEARCH | JONES |
| KING | ACCOUNTING | NULL |
| TURNER | SALES | BLAKE |
| ADAMS | RESEARCH | SCOTT |
| JAMES | SALES | BLAKE |
| FORD | RESEARCH | JONES |
| MILLER | ACCOUNTING | CLARK |
+--------+------------+-------+
14 rows in set (0.00 sec)
mysql> select e.ename'员工名',d.dname'部门名称',c.ename '领导',s.grade'工资等级' from emp e join dept d on e.deptno=d.deptno left join emp c on e.mgr=c.empno join salgrade s on e.sal between s.losal and s.hisal;
+-----------+--------------+--------+--------------+
| 员工名 | 部门名称 | 领导 | 工资等级 |
+-----------+--------------+--------+--------------+
| SMITH | RESEARCH | FORD | 1 |
| ALLEN | SALES | BLAKE | 3 |
| WARD | SALES | BLAKE | 2 |
| JONES | RESEARCH | KING | 4 |
| MARTIN | SALES | BLAKE | 2 |
| BLAKE | SALES | KING | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| KING | ACCOUNTING | NULL | 5 |
| TURNER | SALES | BLAKE | 3 |
| ADAMS | RESEARCH | SCOTT | 1 |
| JAMES | SALES | BLAKE | 1 |
| FORD | RESEARCH | JONES | 4 |
| MILLER | ACCOUNTING | CLARK | 2 |
+-----------+--------------+--------+--------------+
14 rows in set (0.00 sec)