行列互换

最新推荐文章于 2023-07-12 11:32:09 发布

原创最新推荐文章于 2023-07-12 11:32:09 发布 · 282 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

笔记专栏收录该内容

33 篇文章

订阅专栏

一.概述

表连接：将多个数据源中的内容拼接在一起，将拼接后的结果作为一个临时的、新的数据源

–为什么？
单独的一张表并不能满足实际需求，数据往往来源于多个表
–查询员工的姓名，工资，以及所在的部门的部门名称
SELECT * FROM EMP;
SELECT * FROM DEPT;

二.表连接的类别及语法
1.内连接

A.语法
SELECT COLUMN1,COLUMN2… FROM TABLE1 T1 [INNER] JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;

分析：
INNER JOIN->指明两个表做内连接操作，在表关联时按照内连接的规则关联
ON->指明两表关联时的关联条件
关联条件往往选择两个表有联系的列作为条件
关联条件也可为多个条件

示例：
1).查询员工表及其部门表的信息
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

B.内连接的连接过程
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

说明:找到两张表对应的数据，按照关联条件匹配数据，
符合关联条件，将对应的数据拼接到EMP表数据后，若不符合关联条件，将该数据舍去
–只显示符合关联条件的两表拼接带一起的数据

C.多表连接
SELECT * FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

如果两张表以上的表进行连接，直接在关联条件后编写内连接语法即可

2.外连接
–左外连接(经常使用)

A.语法
SELECT COLUMN1,COLUMN2… FROM TABLE1 T1 LEFT [OUTER] JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;

分析：
LEFT [OUTER] JOIN->指明两个表做左外连接操作，在表关联时按照左外连接的规则关联
ON->指明两表关联时的关联条件
关联条件往往选择两个表有联系的列作为条件
关联条件也可为多个条件,可以是= > < >= <= IN …

示例：
将EMP表与DEPT表按照左外连接的方式关联
SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

B.左外连接的连接过程
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

说明：优先展示左表中的数据，按照关联条件将右表中的数据拼接到左表对应的数据后，
如果不符合关联条件，会显示左表中的数据，对应的右表中的列的数据为空

C.多表连接
SELECT * FROM EMP E
LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
LEFT OUTER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT * FROM EMP E 
     LEFT  JOIN DEPT D ON E.DEPTNO=D.DEPTNO
     LEFT  JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

–右外连接

A.语法
SELECT COLUMN1,COLUMN2… FROM TABLE1 T1 RIGHT [OUTER] JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;

分析：
RIGHT [OUTER] JOIN->指明两个表做右外连接操作，在表关联时按照右外连接的规则关联
ON->指明两表关联时的关联条件
关联条件往往选择两个表有联系的列作为条件
关联条件也可为多个条件,可以是= > < >= <= IN …

示例：
将EMP表与DEPT表关联
SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

B.右外连接的连接过程
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

说明：以右表中的额数据为主，按照关联条件将左表中的数据拼接到右表对应的数据上，
如果不符合关联条件，会显示右表中的数据，对应的左表中的列的数据为空

C.多表连接
SELECT * FROM EMP E
RIGHT OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
RIGHT OUTER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

D.左右外连接互换

–左外连接
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM DEPT E LEFT JOIN EMP D ON E.DEPTNO=D.DEPTNO;

–右外连接
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
SELECT * FROM DEPT E RIGHT JOIN EMP D ON E.DEPTNO=D.DEPTNO;

–全外连接
A.语法
SELECT COLUMN1,COLUMN2… FROM TABLE1 T1 FULL [OUTER] JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;

分析：
RIGHT [OUTER] JOIN->指明两个表做全外连接操作，在表关联时按照全外连接的规则关联
ON->指明两表关联时的关联条件
关联条件往往选择两个表有联系的列作为条件
关联条件也可为多个条件,可以是= > < >= <= IN …

示例：
将EMP表与DEPT表按照全外连接的方式关联
SELECT * FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

B.全外连接的连接过程
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

说明：展示两表中的数据，符合关联条件的数据按照关联方式将两表中的数据拼接到一起
若左表中有不符合关联条件的数据，显示该数据并且其对应的右表中的列的数据为空
若右表中有不符合关联条件的数据，显示该数据并且其对应的左表中的列的数据为空

C.多表连接
SELECT * FROM EMP E
FULL OUTER JOIN DEPT D ON E.DEPTNO=D.DEPTNO
FULL OUTER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

D.和左、右外连接的关系
–相当于左外和右外的UNION操作
SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO
UNION
SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO

–同一张表中不能存在重复的列名
–外连接中OUTER关键字可以省略

3.交叉连接(笛卡尔连接)

A.语法
SELECT COLUMN1,COLUMN2… FROM TABLE1 CROSS JOIN TABLE2;

示例：
1.EMP表和DEPT表交叉连接
SELECT * FROM EMP CROSS JOIN DEPT;-- 数据条数：COUNT(EMP)*COUNT(DEPT)

B.应用
类似查询比赛名单，比赛场数
–公司团建，要求：两两一组比赛兵乓球，获胜场数多得为冠军，奖励5000￥
查询SMITH 的对手名单
SELECT E1.ENAME, E2.ENAME
FROM EMP E1
CROSS JOIN EMP E2
WHERE E1.ENAME = ‘SMITH’
AND E2.ENAME <> ‘SMITH’;

C.弊端：容易造成数据量暴涨，引起数据库崩溃

笛卡尔积：
A:1 2 3 4
B:A B C D
A和B的笛卡尔积：
1A 1B 1C 1D
2A 2B 2C 2D
3A 3B 3C 3D
4A 4B 4C 4D
最终集合中的元素个数为：A*B

4.小结

1).掌握连接方式的语法
2).知道各个连接方式的数据的数据条数，以及区别

三.特殊的连接方式

1.自然连接(不用)
语法：
SELECT COLUMN1,COLUMN2… FROM TABLE1 NATURAL JOIN TABLE2;

–两表中含有相同的列
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP NATURAL JOIN DEPT;
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

–含有相同的列时，查找两个表中相同的列作为关联条件，并将相同的列放在第一位显示，且只显示一个

–两表中不含有相同的列
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP NATURAL JOIN DEPT;
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPT_NO;

–不含有相同列时，做交叉连接

局限性：一旦表中字段名发生了更改，代码查询出的数据截然不同

2.USING连接(不用)
语法：
SELECT * FROM TABLE1 [INNER] JOIN TABLE2 USING (COLUMN1);

用来连接关联条件的,代替ON,将关联的列显示一个并放在第一位显示
USING后的字段必许为两表共有字段

示例：
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT * FROM EMP INNER JOIN DEPT USING( DEPTNO);–含有相同的列

SELECT * FROM EMP INNER JOIN DEPT USING( DEPTNO,DEPT_NO);–不含有相同的列报错

—局限性：表中没有重复的字段名，查询报错

3.自连接(经常使用)

用于连接得两个表均为同一个名称，可以是内连接，也可以是外连接，还可以是交叉连接

示例：
1.查询EMP表中所有的员工信息以及该员工的领导编号和领导姓名
–查询：员工信息领导编号，领导姓名

SELECT * FROM EMP;
SELECT * FROM EMP3;

SELECT E1.*, E2.EMPNO, E2.ENAME
FROM EMP E1
INNER JOIN EMP E2
ON E1.MGR = E2.EMPNO;

2.统计员工表中工资大于员工本身的工资的员工人数
SELECT * FROM EMP WHERE SAL=800;
SELECT * FROM EMP;

–不包含最高工资

SELECT S1.EMPNO ,COUNT(*) FROM EMP S1 JOIN EMP S2
ON S1.SAL<S2.SAL GROUP BY S1.EMPNO;

SELECT S1.EMPNO,S1.SAL ,COUNT(*) FROM EMP S1 JOIN EMP S2
ON S1.SAL<S2.SAL GROUP BY S1.EMPNO ,S1.SAL;

–包含最高工资
SELECT S1.EMPNO,S1.SAL ,COUNT(S2.EMPNO) FROM EMP S1 LEFT JOIN EMP S2
ON S1.SAL<S2.SAL GROUP BY S1.EMPNO ,S1.SAL;

3.统计每个部门中工资大于该员工本身的工资的员工人数
–法一
SELECT S1.EMPNO,S1.SAL ,COUNT(S2.EMPNO) FROM EMP S1 LEFT JOIN EMP S2
ON S1.SAL<S2.SAL AND S1.DEPTNO=S2.DEPTNO GROUP BY S1.EMPNO ,S1.SAL;
–法二

SELECT S1.DEPTNO,COUNT(S2.EMPNO) FROM EMP S1 LEFT JOIN EMP S2
ON S1.SAL<S2.SAL AND S1.DEPTNO=S2.DEPTNO GROUP BY S1.DEPTNO,S1.SAL;–错误代码将相同工资的人合并到一起

SELECT S1.DEPTNO,COUNT(S2.EMPNO) FROM EMP S1 LEFT JOIN EMP S2
ON S1.SAL<S2.SAL AND S1.DEPTNO=S2.DEPTNO GROUP BY S1.DEPTNO,S1.EMPNO; --正确代码

四.ORACLE独有的连接方式

1.内连接
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1,TABLE2 T2
WHERE T1.COLUMN=T2.COLUMN;
示例:
以EMP表和DEPT表为例
SELECT * FROM EMP E ,DEPT D
WHERE E.DEPTNO=D.DEPTNO;
2.外连接
左外：
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1,TABLE2 T2
WHERE T1.COLUMN=T2.COLUMN(+);

 示例 ： 
    以emp和dept表为例
        SELECT * FROM EMP E ,DEPT D
               WHERE E.DEPTNO=D.DEPTNO(+);

右外:
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1,TABLE2 T2
WHERE T1.COLUMN(+)=T2.COLUMN;

  示例 ： 
    以emp和dept表为例
        SELECT * FROM EMP E ,DEPT D
               WHERE E.DEPTNO(+)=D.DEPTNO;

3.交叉连接
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1,TABLE2;

 示例 ： 
    以emp和dept表为例
        SELECT * FROM EMP E ,DEPT D;

五.省略关键字的连接

1.内连接
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;

2.外连接
左外：
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1 LEFT JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;
右外:
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1 RIGHT JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;
全外连接
语法：
SELECT COLUMN1,COLUMN2 … FROM TABLE1 T1 FULL JOIN TABLE2 T2 ON T1.COLUMN=T2.COLUMN;

3.交叉连接
语法：
SELECT COLUMN1 … FROM TABLE1 CROSS JOIN TABLE2;

六.联合查询的做题步骤

1）明确各个表的内容，及字段名的含义
2）清楚各个表的联系，以确定关联条件
3）按照关联条件将各个表联合在一起，形成一张大表在进行其他查询操作(条件、排序、分组…)

七.小结

1.掌握联合查询的书写规范
2.辨别各个连接方式的区别
3.在写关联条件时一定要注意表中列名的关联
4.关联条件并不是只有一个，也并不是只有等于，AND OR 做连接
5.表关联做题步骤一定要掌握！！！！

八思考题

1.联合查询的适用情况？

2.何时添加别名？
–表别名列别名

3.集合与联合查询的区别?