MySQL復(fù)合查詢和內(nèi)外連接的操作代碼

更新時(shí)間：2022年09月29日 09:08:27 作者：s_persist

實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢，但是可以將多張表做笛卡爾積后的表當(dāng)做是一張表，也就是單表查詢，這篇文章主要介紹了MySQL復(fù)合查詢和內(nèi)外連接,需要的朋友可以參考下

一、復(fù)合查詢

1.1 多表查詢

實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢，但是可以將多張表做笛卡爾積后的表當(dāng)做是一張表，也就是單表查詢。
我們用一個(gè)簡(jiǎn)單的公司管理系統(tǒng)，有三張表EMP、DEPT、SALGRADE來(lái)演示如何進(jìn)行多表查詢。

語(yǔ)法：from 表1, 表2 ...
這樣其實(shí)就是對(duì)多張表進(jìn)行笛卡爾積，假設(shè)是兩張表：

第一張表的第一行和第二張表的所有行組合。
第一張表的第二行和第二張表的所有行組合。…

在這里插入圖片描述

這樣所有的組合，并不都是有意義的，所有一般都是根據(jù)條件篩選的。

在多表查詢中，有可能多張表有相同的字段，為了便于區(qū)分，可以使用表名.字段名表示某一張表的某一個(gè)字段。

mysql> select dept.dname, emp.ename, emp.sal from emp,dept where dept.deptno = emp.deptno;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| RESEARCH   | SMITH  |  800.00 |
| SALES      | ALLEN  | 1600.00 |
| SALES      | WARD   | 1250.00 |
| RESEARCH   | JONES  | 2975.00 |
| SALES      | MARTIN | 1250.00 |
| SALES      | BLAKE  | 2850.00 |
| ACCOUNTING | CLARK  | 2450.00 |
| RESEARCH   | SCOTT  | 3000.00 |
| ACCOUNTING | KING   | 5000.00 |
| SALES      | TURNER | 1500.00 |
| RESEARCH   | ADAMS  | 1100.00 |
| SALES      | JAMES  |  950.00 |
| RESEARCH   | FORD   | 3000.00 |
| ACCOUNTING | MILLER | 1300.00 |
+------------+--------+---------+
14 rows in set (0.00 sec)

1.2 自連接

自連接是對(duì)一張表的查詢，但是使用多張表的查詢方式，自己做笛卡爾積。
例如：

mysql> show create table emp \G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號(hào)',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領(lǐng)導(dǎo)編號(hào)',
  `hiredate` datetime DEFAULT NULL COMMENT '雇傭時(shí)間',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '獎(jiǎng)金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門(mén)編號(hào)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

顯示員工FORD的上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名（mgr是員工領(lǐng)導(dǎo)的編號(hào)）。
這是員工表，領(lǐng)導(dǎo)也是屬于員工，所以無(wú)法只用一條簡(jiǎn)單查找語(yǔ)句對(duì)一張表查找兩次，這就需要用到復(fù)合查詢。
子查詢 — 即嵌套查詢，使用兩條查詢語(yǔ)句

mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec

使用自連接的兩張表查詢，這里需要對(duì)表使用別名

mysql> select leader.empno, leader.ename from emp as worker, emp leader where worker.mgr = leader.empno and worker.ename='FORD';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

1.3 子查詢

子查詢是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢。

1.3.1 單行子查詢

返回一行記錄的子查詢

顯示SMITH同一部門(mén)的員工

mysql> select deptno, ename from emp where deptno = (select deptno from emp where ename = 'SMITH');
+--------+-------+
| deptno | ename |
+--------+-------+
|     20 | SMITH |
|     20 | JONES |
|     20 | SCOTT |
|     20 | ADAMS |
|     20 | FORD  |
+--------+-------+
5 rows in set (0.00 sec)

1.3.2 多行子查詢

返回多行記錄的子查詢，仍然是一列（一個(gè)字段）。

in關(guān)鍵字（屬于查詢出來(lái)的多行中的一行，無(wú)法用于比較）：查詢和10號(hào)部門(mén)的工作崗位相同的雇員的名字，崗位，工資，部門(mén)號(hào)，但是不包含10自己的
先拆分，找到10號(hào)部門(mén)的崗位：

mysql> select distinct job from emp where deptno = 10;
+-----------+
| job       |
+-----------+
| MANAGER   |
| PRESIDENT |
| CLERK     |
+-----------+
3 rows in set (0.00 sec)
-- 然后再?gòu)?fù)合
mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <=> 10;
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| CLARK  | MANAGER   | 2450.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| MILLER | CLERK     | 1300.00 |     10 |
+--------+-----------+---------+--------+
3 rows in set (0.00 sec)

all關(guān)鍵字（與查詢結(jié)果的所有行比較為真的）：顯示工資比部門(mén)30的所有員工的工資高的員工的姓名、工資和部門(mén)號(hào)
同樣可以拆分

-- 先找到部門(mén)號(hào)為30的所有工資
mysql> select sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
6 rows in set (0.00 sec)
-- 判斷 > 所有行
mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

any關(guān)鍵字（與查詢結(jié)果的任意一行比較為真的）：顯示工資比部門(mén)30的任意員工的工資高的員工的姓名、工資和部門(mén)號(hào)（包含自己部門(mén)的員工） some是any的別稱(chēng)很少使用。

mysql> select ename, sal, deptno from emp where sal > any (select sal from emp where deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

1.3.3 多列子查詢

單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)
多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句

其實(shí)很簡(jiǎn)單，多列也可以比較，和單列一樣。

案例：查詢和SMITH的部門(mén)和崗位完全相同的所有雇員，不含SMITH本人

mysql> select ename, deptno, job from emp where (job, deptno) = (select job, deptno from emp where ename = 'SMITH') and ename <=> 'SMITH';
+-------+--------+-------+
| ename | deptno | job   |
+-------+--------+-------+
| SMITH |     20 | CLERK |
+-------+--------+-------+
1 row in set (0.00 sec)

1.3.4 在from子句中使用子查詢

子查詢語(yǔ)句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。

顯示每個(gè)高于自己部門(mén)平均工資的員工的姓名、部門(mén)、工資、平均工資

-- 先找到每個(gè)部門(mén)的平均工資
mysql> select deptno, avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
-- 然后再用這張表和原本的表做笛卡爾積，多表查詢
mysql> select emp.ename, emp.deptno, emp.sal, format(dept_avg,2) from emp , (select deptno , avg(sal) dept_avg from emp group by deptno) tmp wheree tmp.deptno = emp.deptno and emp.sal > dept_avg;
+-------+--------+---------+--------------------+
| ename | deptno | sal     | format(dept_avg,2) |
+-------+--------+---------+--------------------+
| ALLEN |     30 | 1600.00 | 1,566.67           |
| JONES |     20 | 2975.00 | 2,175.00           |
| BLAKE |     30 | 2850.00 | 1,566.67           |
| SCOTT |     20 | 3000.00 | 2,175.00           |
| KING  |     10 | 5000.00 | 2,916.67           |
| FORD  |     20 | 3000.00 | 2,175.00           |
+-------+--------+---------+--------------------+
6 rows in set (0.00 sec)

顯示每個(gè)部門(mén)的信息（部門(mén)名，編號(hào)，地址）和人員數(shù)量
對(duì)于有未知的，聚合的信息（人員數(shù)量），先將它求出來(lái)

-- 首先拿到相關(guān)表的信息
mysql> select * from dept limit 3;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
+--------+------------+----------+
3 rows in set (0.00 sec)

mysql> select * from emp limit 3;
+--------+-------+----------+------+---------------------+---------+--------+--------+
| empno  | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+-------+----------+------+---------------------+---------+--------+--------+
| 007369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
| 007521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 |     30 |
+--------+-------+----------+------+---------------------+---------+--------+--------+
3 rows in set (0.01 sec)

暴力使用多表查詢
注意：和聚合函數(shù)一起顯示的字段，需要被分組，因?yàn)榫酆虾瘮?shù)對(duì)于某一個(gè)字段是不清楚的，比如count(*)，它是統(tǒng)計(jì)總數(shù)，并不對(duì)應(yīng)某一個(gè)deptno,ename這些字段

mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.dept.dname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
---- 注意：和聚合函數(shù)一起顯示的字段，需要被分組，因?yàn)榫酆虾瘮?shù)對(duì)于某一個(gè)字段是不清楚的，比如count(*)，它是統(tǒng)計(jì)總數(shù)，并不對(duì)應(yīng)某一個(gè)deptno,ename這些字段
mysql> select dept.dname, dept.deptno, dept.loc, count(*) from dept, emp where dept.deptno = emp.deptno group by deptno, dept.dname, dept.loc;
+------------+--------+----------+----------+
| dname      | deptno | loc      | count(*) |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.00 sec)

使用子查詢

-- 先子查詢出每個(gè)部門(mén)的人員數(shù)量
mysql> select deptno, count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)
-- 再和部門(mén)表組合，根據(jù)條件篩選。
mysql> select dept.dname, dept.deptno, dept.loc, dept_cnt from dept, (select deptno, count(*) dept_cnt from emp group by deptno) cnt_table where
cnt_table.deptno = dept.deptno;
+------------+--------+----------+----------+
| dname      | deptno | loc      | dept_cnt |
+------------+--------+----------+----------+
| ACCOUNTING |     10 | NEW YORK |        3 |
| RESEARCH   |     20 | DALLAS   |        5 |
| SALES      |     30 | CHICAGO  |        6 |
+------------+--------+----------+----------+
3 rows in set (0.00 sec)

1.4 合并查詢

在實(shí)際應(yīng)用中，為了合并多個(gè)select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all

1.4.1 union

該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。

將工資大于2500或職位是MANAGER的人找出來(lái)
崗位是MANAGER的必定有工資大于2500的，這里自動(dòng)去重了

mysql> select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
6 rows in set (0.00 sec)

1.4.2 union all

該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。

mysql> select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
8 rows in set (0.00 sec)

二、內(nèi)外連接

2.1 內(nèi)連接

內(nèi)連接inner join實(shí)際上就是利用where子句對(duì)兩種表形成的笛卡兒積進(jìn)行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開(kāi)發(fā)過(guò)程中使用的最多的連接查詢。

select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件；

前面使用的都是內(nèi)連接。

on后面是兩張表的連接條件，后可以跟where條件語(yǔ)句查詢表。

-- 用前面的寫(xiě)法
select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='SMITH';
-- 用標(biāo)準(zhǔn)的內(nèi)連接寫(xiě)法
select ename, dname from emp inner join dept on emp.deptno=dept.deptno and ename='SMITH';

兩張表連接，并不是所有行都是有效的，所有使用連接條件更加明確。

2.2 外連接

外連接分為左外連接和右外連接

2.2.1 左外連接

如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說(shuō)是左外連接。
什么叫完全顯示呢？
就是兩張表通過(guò)連接條件連接，但是左側(cè)的表有不滿足條件的也要全部顯示，右側(cè)的表顯示為NULL。
語(yǔ)法：

select 字段名  from 表名1 left join 表名2 on 連接條件

創(chuàng)建兩張表

mysql> select * from exam;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |     8 |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)

使用內(nèi)連接

mysql> select * from stu inner join exam on exam.id = stu.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
+------+------+------+-------+
2 rows in set (0.00 sec)

使用stu為左表的左外連接

mysql> select * from stu left join exam on exam.id = stu.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
|    3 | kity | NULL |  NULL |
|    4 | nono | NULL |  NULL |
+------+------+------+-------+
4 rows in set (0.00 sec)

使用exam為左表的左外連接

mysql> select * from exam left join stu on exam.id = stu.id;
+------+-------+------+------+
| id   | grade | id   | name |
+------+-------+------+------+
|    1 |    56 |    1 | jack |
|    2 |    76 |    2 | tom  |
|   11 |     8 | NULL | NULL |
+------+-------+------+------+
3 rows in set (0.00 sec)

2.2.1 右外連接

和左外連接對(duì)稱(chēng)，左側(cè)不滿足右側(cè)的填充為NULL。

select 字段 from 表名1 right join 表名2  on 連接條件；

mysql> select * from exam right join stu on exam.id = stu.id;
+------+-------+------+------+
| id   | grade | id   | name |
+------+-------+------+------+
|    1 |    56 |    1 | jack |
|    2 |    76 |    2 | tom  |
| NULL |  NULL |    3 | kity |
| NULL |  NULL |    4 | nono |
+------+-------+------+------+
4 rows in set (0.00 sec)

列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息，同時(shí)列出沒(méi)有員工的部門(mén)
顯然要以部門(mén)表為主

mysql> select dept.deptno, dept.dname, emp.ename, emp.job from dept left join emp on emp.deptno = dept.deptno;
+--------+------------+--------+-----------+
| deptno | dname      | ename  | job       |
+--------+------------+--------+-----------+
|     20 | RESEARCH   | SMITH  | CLERK     |
|     30 | SALES      | ALLEN  | SALESMAN  |
|     30 | SALES      | WARD   | SALESMAN  |
|     20 | RESEARCH   | JONES  | MANAGER   |
|     30 | SALES      | MARTIN | SALESMAN  |
|     30 | SALES      | BLAKE  | MANAGER   |
|     10 | ACCOUNTING | CLARK  | MANAGER   |
|     20 | RESEARCH   | SCOTT  | ANALYST   |
|     10 | ACCOUNTING | KING   | PRESIDENT |
|     30 | SALES      | TURNER | SALESMAN  |
|     20 | RESEARCH   | ADAMS  | CLERK     |
|     30 | SALES      | JAMES  | CLERK     |
|     20 | RESEARCH   | FORD   | ANALYST   |
|     10 | ACCOUNTING | MILLER | CLERK     |
|     40 | OPERATIONS | NULL   | NULL      |
+--------+------------+--------+-----------+
15 rows in set (0.00 sec)

左外連接是以左表為主
右外連接是以右表為主

到此這篇關(guān)于MySQL復(fù)合查詢和內(nèi)外連接的文章就介紹到這了,更多相關(guān)MySQL復(fù)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL復(fù)合查詢和內(nèi)外連接的操作代碼

目錄

一、復(fù)合查詢

1.1 多表查詢

1.2 自連接

1.3 子查詢

1.3.1 單行子查詢

1.3.2 多行子查詢

1.3.3 多列子查詢

1.3.4 在from子句中使用子查詢

1.4 合并查詢

1.4.1 union

1.4.2 union all

二、內(nèi)外連接

2.1 內(nèi)連接

2.2 外連接

2.2.1 左外連接

2.2.1 右外連接

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL復(fù)合查詢和內(nèi)外連接的操作代碼

目錄

一、復(fù)合查詢

1.1 多表查詢

1.2 自連接

1.3 子查詢

1.3.1 單行子查詢

1.3.2 多行子查詢

1.3.3 多列子查詢

1.3.4 在from子句中使用子查詢

1.4 合并查詢

1.4.1 union

1.4.2 union all

二、內(nèi)外連接

2.1 內(nèi)連接

2.2 外連接

2.2.1 左外連接

2.2.1 右外連接

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、復(fù)合查詢