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

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

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

一、復(fù)合查詢

1.1 多表查詢

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

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

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

在這里插入圖片描述

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

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

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 自連接

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

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

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

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

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

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語句中的select語句，也叫嵌套查詢。

1.3.1 單行子查詢

返回一行記錄的子查詢

顯示SMITH同一部門的員工

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 多行子查詢

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

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

mysql> select distinct job from emp where deptno = 10;
+-----------+
| job       |
+-----------+
| MANAGER   |
| PRESIDENT |
| CLERK     |
+-----------+
3 rows in set (0.00 sec)
-- 然后再復(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é)果的所有行比較為真的）：顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號
同樣可以拆分

-- 先找到部門號為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é)果的任意一行比較為真的）：顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工） some是any的別稱很少使用。

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ù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句

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

案例：查詢和SMITH的部門和崗位完全相同的所有雇員，不含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子句中使用子查詢

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

顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資

-- 先找到每個部門的平均工資
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)

顯示每個部門的信息（部門名，編號，地址）和人員數(shù)量
對于有未知的，聚合的信息（人員數(shù)量），先將它求出來

-- 首先拿到相關(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ù)對于某一個字段是不清楚的，比如count(*)，它是統(tǒng)計總數(shù)，并不對應(yīng)某一個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ù)對于某一個字段是不清楚的，比如count(*)，它是統(tǒng)計總數(shù)，并不對應(yīng)某一個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)

使用子查詢

-- 先子查詢出每個部門的人員數(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)
-- 再和部門表組合，根據(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)用中，為了合并多個select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all

1.4.1 union

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

將工資大于2500或職位是MANAGER的人找出來
崗位是MANAGER的必定有工資大于2500的，這里自動去重了

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

該操作符用于取得兩個結(jié)果集的并集。當(dāng)使用該操作符時，不會去掉結(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子句對兩種表形成的笛卡兒積進(jìn)行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開發(fā)過程中使用的最多的連接查詢。

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

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

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

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

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

2.2 外連接

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

2.2.1 左外連接

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

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 右外連接

和左外連接對稱，左側(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)

列出部門名稱和這些部門的員工信息，同時列出沒有員工的部門
顯然要以部門表為主

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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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)文章

最新評論

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

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

常用在線小工具

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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)文章

最新評論

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

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

常用在線小工具

一、復(fù)合查詢