MySQL數(shù)據(jù)庫(kù)的內(nèi)嵌函數(shù)和聯(lián)合查詢實(shí)例代碼

更新時(shí)間：2025年06月24日 11:12:52 作者：快去睡覺(jué)~

聯(lián)合查詢是一種將多個(gè)查詢結(jié)果組合在一起的方法,通常使用UNION、UNION ALL、INTERSECT和EXCEPT關(guān)鍵字,下面這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)的內(nèi)嵌函數(shù)和聯(lián)合查詢的相關(guān)資料,需要的朋友可以參考下

一.數(shù)據(jù)庫(kù)的內(nèi)嵌函數(shù)

1.1聚合函數(shù)

在MySQL中有著一類聚合函數(shù)來(lái)方便我們完成數(shù)據(jù)的各自統(tǒng)計(jì)工作。

COUNT([DISTINCT] expr)

返回查詢到的數(shù)據(jù)的數(shù)量

//表中一共有幾條記錄
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

//表中math列有幾條記錄
mysql> select count(math) from exam;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

SUM([DISTINCT] expr)

返回查詢到的數(shù)據(jù)的總和，不是數(shù)字沒(méi)有意義

//表中math列的總和
mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
|       837 |
+-----------+
1 row in set (0.00 sec)

//表中math+chinese+english三列的總和
mysql> select sum(math+chinese+english) from exam;
+---------------------------+
| sum(math+chinese+english) |
+---------------------------+
|                      1740 |
+---------------------------+
1 row in set (0.00 sec)

AVG([DISTINCT] expr)

返回查詢到的數(shù)據(jù)的平均值，不是數(shù)字沒(méi)有意義

//英語(yǔ)成績(jī)的平均值
mysql> select avg(english) from exam;
+--------------------+
| avg(english)       |
+--------------------+
| 62.285714285714285 |
+--------------------+
1 row in set (0.01 sec)

//總成績(jī)的平均值
mysql> select avg(english+math+chinese) from exam;
+---------------------------+
| avg(english+math+chinese) |
+---------------------------+
|        248.57142857142858 |
+---------------------------+
1 row in set (0.00 sec)

MAX([DISTINCT] expr)

返回查詢到的數(shù)據(jù)的最大值，不是數(shù)字沒(méi)有意義

//總成績(jī)最高
mysql> select max(english+math+chinese) from exam;
+---------------------------+
| max(english+math+chinese) |
+---------------------------+
|                       276 |
+---------------------------+
1 row in set (0.00 sec)

MIN([DISTINCT] expr）

返回查詢到的數(shù)據(jù)的最小值，不是數(shù)字沒(méi)有意義

//總成績(jī)最低
mysql> select min(english+math+chinese) from exam;
+---------------------------+
| min(english+math+chinese) |
+---------------------------+
|                       230 |
+---------------------------+
1 row in set (0.00 sec)

1.2日期函數(shù)

函數(shù)	說(shuō)明
CURDATE()	返回當(dāng)前日期，同義詞 CURRENT_DATE ， CURRENT_DATE()
CURTIME()	返回當(dāng)前時(shí)間，同義詞 CURRENT_TIME ， CURRENT_TIME([fsp])
NOW()	返回當(dāng)前日期和時(shí)間，同義詞CURRENT_TIMESTAMP，CURRENT_TIMESTAMP()
DATE(data)	提取date或者datetime中的日期部分
ADDDATE(date,INTERVAL expr unit)	向日期指中添加時(shí)間值(間隔)，同義詞DATE_ADD()
SUBDATE(date,INTERVAL expr unit)	向日期中減去時(shí)間值(間隔)，同義詞DATE_SUB()
DATEDIFF(expr1,expr2)	兩個(gè)日期的差，以天為單位，expr1 - expr2

日期函數(shù)都是我們一眼就能理解的所以我們就不做多演示了

1.3字符串處理函數(shù)

函數(shù)	說(shuō)明
CHAR_LENGTH(str)	返回給定字符串的長(zhǎng)度，同義詞 CHARACTER_LENGTH()
LENGTH(str)	返回給定字符串的字節(jié)數(shù)，與當(dāng)前使?的字符編碼集有關(guān)
CONCAT(str1,str2,…)	返回拼接后的字符串
CONCAT_WS(separator,str1,str2,…)	返回拼接后帶分隔符的字符串
LCASE(str)	將給定字符串轉(zhuǎn)換成小寫，同義詞 LOWER()
UCASE(str)	將給定字符串轉(zhuǎn)換成大寫，同義詞 UPPER()
HEX(str), HEX(N)	對(duì)于字符串參數(shù)str, HEX()返回str的十六進(jìn)制字符串表示形式，對(duì)于數(shù)字參數(shù)N, HEX()返回?個(gè)十六進(jìn)制字符串表示形式
INSTR(str,substr)	返回substring第一次出現(xiàn)的索引
INSERT(str,pos,len,newstr)	在指定位置插入子字符串，最多不超過(guò)指定的字符數(shù)
SUBSTR(str,pos) ，SUBSTR(str FROM pos FOR len)	返回指定的子字符串，同義詞 SUBSTRING(str,pos) ，SUBSTRING(str FROM pos FOR len)
REPLACE(str,from_str,to_str)	把字符串str中所有的from_str替換為to_str，區(qū)分大小寫
STRCMP(expr1,expr2)	逐個(gè)字符比較兩個(gè)字符串，返回 -1， 0 ， 1
LEFT(str,len) ，RIGHT(str,len)	返回字符串str中最左/最右邊的len個(gè)字符
LTRIM(str) ， RTRIM(str) ，TRIM(str)	刪除給定字符串的前導(dǎo)、末尾、前導(dǎo)和末尾的空格
TRIM([{LEADING / TRAILING / BOTH } remstr FROM str）	刪除給定符串的前導(dǎo)、末尾或前導(dǎo)和末尾的指定字符串

字符串處理函數(shù)很多，我們就挑其中一些不好理解的來(lái)給大家演示一下

#length（str），根據(jù)字符集返回字節(jié)數(shù)
mysql> select name,length(name) from exam;#這是utf8的所以一個(gè)字符占三個(gè)字節(jié)
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 唐三藏    |            9 |
| 孫悟空    |            9 |
| 豬悟能    |            9 |
| 曹孟德    |            9 |
| 劉?德    |            9 |
| 孫權(quán)      |            6 |
| 宋公明    |            9 |
+-----------+--------------+
7 rows in set (0.00 sec)

#hex（str | sum）將字符串或者數(shù)字轉(zhuǎn)為16進(jìn)制
mysql> select id,hex(id),name,hex(name) from exam;
+------+---------+-----------+--------------------+
| id   | hex(id) | name      | hex(name)          |
+------+---------+-----------+--------------------+
|    1 | 1       | 唐三藏    | E59490E4B889E8978F |
|    2 | 2       | 孫悟空    | E5AD99E6829FE7A9BA |
|    3 | 3       | 豬悟能    | E78CAAE6829FE883BD |
|    4 | 4       | 曹孟德    | E69BB9E5AD9FE5BEB7 |
|    5 | 5       | 劉?德    | E58898E2BD9EE5BEB7 |
|    6 | 6       | 孫權(quán)      | E5AD99E69D83       |
|    7 | 7       | 宋公明    | E5AE8BE585ACE6988E |
+------+---------+-----------+--------------------+
7 rows in set (0.00 sec)

#instr（str，substr） 返回substr在str中第一次出現(xiàn)的索引
mysql> select name,instr(name,'孫') from exam;\
+-----------+-------------------+
| name      | instr(name,'孫')  |
+-----------+-------------------+
| 唐三藏    |                 0 |
| 孫悟空    |                 1 |
| 豬悟能    |                 0 |
| 曹孟德    |                 0 |
| 劉?德    |                 0 |
| 孫權(quán)      |                 1 |
| 宋公明    |                 0 |
+-----------+-------------------+
7 rows in set (0.00 sec)

#trim({LEADING / TRAILING / BOTH} str from ori_str) 刪除給定符串的前導(dǎo)、末尾或前導(dǎo)和末尾的指定字符串
mysql> select trim(both 'xxx' from 'xxxworldxxx');
+-------------------------------------+
| trim(both 'xxx' from 'xxxworldxxx') |
+-------------------------------------+
| world                               |
+-------------------------------------+
1 row in set (0.00 sec)

1.4 數(shù)學(xué)函數(shù)

函數(shù)	說(shuō)明
ABS(X)	返回X的絕對(duì)值
CEIL(X)	返回不小于X的最小整數(shù)值，同義詞是 CEILING(X)
FLOOR(X)	返回不大于X的最大整數(shù)值
CONV(N,from_base,to_base)	不同進(jìn)制之間的轉(zhuǎn)換
FORMAT(X,D)	將數(shù)字X格式化為“#，###，###”的格式。##'，四舍五入到小數(shù)點(diǎn)后D位，并以字符串形式返回
RAND([N])	返回?個(gè)隨機(jī)浮點(diǎn)值，取值范圍 [0.0, 1.0)
ROUND(X), ROUND(X,D)	將參數(shù)X舍入到小數(shù)點(diǎn)后D位
CRC32(expr)	計(jì)算指定字符串的循環(huán)冗余校驗(yàn)值并返回?個(gè)32位無(wú)符號(hào)整數(shù)

一樣我們挑幾個(gè)來(lái)演示

#ceil(x) 向上取整
mysql> select ceil(3.14),ceil(3.9),ceil(-3.1),ceil(-3.9);
+------------+-----------+------------+------------+
| ceil(3.14) | ceil(3.9) | ceil(-3.1) | ceil(-3.9) |
+------------+-----------+------------+------------+
|          4 |         4 |         -3 |         -3 |
+------------+-----------+------------+------------+
1 row in set (0.00 sec)

#floor(x) 向下取整
mysql> select floor(3.14),floor(3.9),floor(-3.1),floor(-3.9);
+-------------+------------+-------------+-------------+
| floor(3.14) | floor(3.9) | floor(-3.1) | floor(-3.9) |
+-------------+------------+-------------+-------------+
|           3 |          3 |          -4 |          -4 |
+-------------+------------+-------------+-------------+
1 row in set (0.00 sec)

我們想要了理解向上和向下取整分別是什么意思我們需要把數(shù)軸旋轉(zhuǎn)90度讓其變成豎直的樣子。

#format(x,D) 把數(shù)字轉(zhuǎn)換為'x.xxx.xxx.xxxx...'的形式，小數(shù)點(diǎn)四舍五入保留D位并且是以字符串的形式
mysql> select format(1564679.1566,3);
+------------------------+
| format(1564679.1566,3) |
+------------------------+
| 1,564,679.157          |
+------------------------+
1 row in set (0.00 sec)

#crc32(x) 將字符串轉(zhuǎn)換為一個(gè)32位無(wú)符號(hào)整型
mysql> select crc32('hello'),crc32('world');
+----------------+----------------+
| crc32('hello') | crc32('world') |
+----------------+----------------+
|      907060870 |      980881731 |
+----------------+----------------+
1 row in set (0.00 sec)

1.5其他常用的函數(shù)

函數(shù)	說(shuō)明
version()	顯示當(dāng)前數(shù)據(jù)庫(kù)版本
database()	顯示當(dāng)前正在使用的數(shù)據(jù)庫(kù)
user()	顯示當(dāng)前用戶
md5(str)	對(duì)一個(gè)字符串進(jìn)行md5摘要，摘要后得到?個(gè)32位字符串
ifnull(val1, val2)	如果val1為NULL，返回val2，否則返回 val1

這五個(gè)函數(shù)除了md5需要講一下之外其他的函數(shù)我們直接看說(shuō)明就知道是什么作用了，所以我們只需要講講md5的作用。
我們知道數(shù)據(jù)庫(kù)是用來(lái)存儲(chǔ)數(shù)據(jù)的例如用戶的賬號(hào)密碼，但是數(shù)據(jù)庫(kù)也是很好入侵的如果我們的密碼就那么明晃晃的存儲(chǔ)在數(shù)據(jù)庫(kù)中黑客一入侵就可以把存儲(chǔ)的賬號(hào)密碼全部盜走了所以我們需要對(duì)密碼進(jìn)行加密而md5函數(shù)就是一個(gè)加密函數(shù)它可以把一個(gè)字符串通過(guò)特殊的算法轉(zhuǎn)換為一串32位的字符串。

mysql> select md5('123456789');
+----------------------------------+
| md5('123456789')                 |
+----------------------------------+
| 25f9e794323b453885f5181f1b624d0b |
+----------------------------------+
1 row in set (0.00 sec)

二.聯(lián)合查詢

2.1笛卡爾積

在掌握了基礎(chǔ)的查詢方法后我們可以發(fā)現(xiàn)我們剛剛的例子都是單表查詢但是在實(shí)際應(yīng)用中有時(shí)候我們是需要從多張表中查詢出一條數(shù)據(jù)的完整消息的，所以這時(shí)候我們就需要用到多表查詢也就是聯(lián)合查詢。

想進(jìn)行多表查詢的話我們就需要將多張表組合在一起這個(gè)組合的過(guò)程在MySQL中叫做取笛卡爾積，這個(gè)概念大家可能在數(shù)學(xué)學(xué)習(xí)中聽(tīng)到過(guò)指的是將兩個(gè)集合構(gòu)成有序二元組也叫做兩個(gè)集合的笛卡爾積。MySQL中表就代替了集合，所以是給表取笛卡爾積。

mysql> select * from exam,emp;
+------+-----------+---------+------+---------+----+-----------+--------+------------+
| id   | name      | chinese | math | english | id | name      | role   | salary     |
+------+-----------+---------+------+---------+----+-----------+--------+------------+
|    7 | 宋公明    |      75 |  125 |      30 |  1 | 馬云      | 老板   | 1500000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  1 | 馬云      | 老板   | 1500000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  1 | 馬云      | 老板   | 1500000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  1 | 馬云      | 老板   | 1500000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  1 | 馬云      | 老板   | 1500000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  1 | 馬云      | 老板   | 1500000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  1 | 馬云      | 老板   | 1500000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  2 | 馬化騰    | 老板   | 1300000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  3 | 張三      | 經(jīng)理   |   13000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  4 | 趙四      | 經(jīng)理   |   11000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  5 | 王五      | 員工   |    5000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  5 | 王五      | 員工   |    5000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  5 | 王五      | 員工   |    5000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  5 | 王五      | 員工   |    5000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  5 | 王五      | 員工   |    5000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  5 | 王五      | 員工   |    5000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  5 | 王五      | 員工   |    5000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  6 | 田七      | 員工   |    6000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  6 | 田七      | 員工   |    6000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  6 | 田七      | 員工   |    6000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  6 | 田七      | 員工   |    6000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  6 | 田七      | 員工   |    6000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  6 | 田七      | 員工   |    6000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  6 | 田七      | 員工   |    6000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  7 | 路人甲    | 保安   |    2000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  7 | 路人甲    | 保安   |    2000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  7 | 路人甲    | 保安   |    2000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  7 | 路人甲    | 保安   |    2000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  7 | 路人甲    | 保安   |    2000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  7 | 路人甲    | 保安   |    2000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  7 | 路人甲    | 保安   |    2000.00 |
|    7 | 宋公明    |      75 |  125 |      30 |  8 | 路人乙    | 保安   |    2000.00 |
|    6 | 孫權(quán)      |      70 |  103 |      78 |  8 | 路人乙    | 保安   |    2000.00 |
|    5 | 劉?德    |      55 |  145 |      45 |  8 | 路人乙    | 保安   |    2000.00 |
|    4 | 曹孟德    |      82 |  100 |      60 |  8 | 路人乙    | 保安   |    2000.00 |
|    3 | 豬悟能    |      88 |   98 |      90 |  8 | 路人乙    | 保安   |    2000.00 |
|    2 | 孫悟空    |      30 |  138 |      77 |  8 | 路人乙    | 保安   |    2000.00 |
|    1 | 唐三藏    |      67 |  128 |      56 |  8 | 路人乙    | 保安   |    2000.00 |
+------+-----------+---------+------+---------+----+-----------+--------+------------+
56 rows in set (0.00 sec)

從這兩個(gè)表的笛卡爾積我們就能發(fā)現(xiàn)僅僅兩張表的笛卡爾積就已經(jīng)很繁瑣了更別提三張四張甚至更多了所以我們?cè)谌〉芽柗e的時(shí)候一定進(jìn)行條件判斷來(lái)去除哪些無(wú)用的重復(fù)的數(shù)據(jù)從而減少行數(shù)來(lái)減輕字節(jié)的負(fù)擔(dān)。

所以我現(xiàn)在用一個(gè)更具體的例子來(lái)給大家完成一個(gè)完整的聯(lián)合查詢

//兩個(gè)表的笛卡爾積
mysql> select * from student,class;
+----+--------------+--------+------+--------+-------------+----------+----+--------------+
| id | name         | sno    | age  | gender | enroll_date | class_id | id | name         |
+----+--------------+--------+------+--------+-------------+----------+----+--------------+
|  1 | 唐三藏       | 100001 |   18 |      1 | 1986-09-01  |        1 |  3 | 前端001班    |
|  1 | 唐三藏       | 100001 |   18 |      1 | 1986-09-01  |        1 |  2 | C++001班     |
|  1 | 唐三藏       | 100001 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班    |
|  2 | 孫悟空       | 100002 |   18 |      1 | 1986-09-01  |        1 |  3 | 前端001班    |
|  2 | 孫悟空       | 100002 |   18 |      1 | 1986-09-01  |        1 |  2 | C++001班     |
|  2 | 孫悟空       | 100002 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班    |
|  3 | 豬悟能       | 100003 |   18 |      1 | 1986-09-01  |        1 |  3 | 前端001班    |
|  3 | 豬悟能       | 100003 |   18 |      1 | 1986-09-01  |        1 |  2 | C++001班     |
|  3 | 豬悟能       | 100003 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班    |
|  4 | 沙悟凈       | 100004 |   18 |      1 | 1986-09-01  |        1 |  3 | 前端001班    |
|  4 | 沙悟凈       | 100004 |   18 |      1 | 1986-09-01  |        1 |  2 | C++001班     |
|  4 | 沙悟凈       | 100004 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班    |
|  5 | 宋江         | 200001 |   18 |      1 | 2000-09-01  |        2 |  3 | 前端001班    |
|  5 | 宋江         | 200001 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班     |
|  5 | 宋江         | 200001 |   18 |      1 | 2000-09-01  |        2 |  1 | Java001班    |
|  6 | 武松         | 200002 |   18 |      1 | 2000-09-01  |        2 |  3 | 前端001班    |
|  6 | 武松         | 200002 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班     |
|  6 | 武松         | 200002 |   18 |      1 | 2000-09-01  |        2 |  1 | Java001班    |
|  7 | 李逹         | 200003 |   18 |      1 | 2000-09-01  |        2 |  3 | 前端001班    |
|  7 | 李逹         | 200003 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班     |
|  7 | 李逹         | 200003 |   18 |      1 | 2000-09-01  |        2 |  1 | Java001班    |
|  8 | 不想畢業(yè)     | 200004 |   18 |      1 | 2000-09-01  |        2 |  3 | 前端001班    |
|  8 | 不想畢業(yè)     | 200004 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班     |
|  8 | 不想畢業(yè)     | 200004 |   18 |      1 | 2000-09-01  |        2 |  1 | Java001班    |
+----+--------------+--------+------+--------+-------------+----------+----+--------------+
24 rows in set (0.00 sec)

//增加條件判斷，通過(guò)student中的class_id和class中的id
mysql> select * from student,class where student.class_id = class.id;
+----+--------------+--------+------+--------+-------------+----------+----+------------+
| id | name         | sno    | age  | gender | enroll_date | class_id | id | name       |
+----+--------------+--------+------+--------+-------------+----------+----+------------+
|  1 | 唐三藏       | 100001 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班  |
|  2 | 孫悟空       | 100002 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班  |
|  3 | 豬悟能       | 100003 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班  |
|  4 | 沙悟凈       | 100004 |   18 |      1 | 1986-09-01  |        1 |  1 | Java001班  |
|  5 | 宋江         | 200001 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班   |
|  6 | 武松         | 200002 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班   |
|  7 | 李逹         | 200003 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班   |
|  8 | 不想畢業(yè)     | 200004 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班   |
+----+--------------+--------+------+--------+-------------+----------+----+------------+
8 rows in set (0.00 sec)

//再增加條件判斷
mysql> select * from student,class where student.class_id = class.id and student.name = '宋江';
+----+--------+--------+------+--------+-------------+----------+----+-----------+
| id | name   | sno    | age  | gender | enroll_date | class_id | id | name      |
+----+--------+--------+------+--------+-------------+----------+----+-----------+
|  5 | 宋江   | 200001 |   18 |      1 | 2000-09-01  |        2 |  2 | C++001班  |
+----+--------+--------+------+--------+-------------+----------+----+-----------+
1 row in set (0.00 sec)

//修改查詢字段，可以給表取別名來(lái)方便編寫
mysql> select s.id,s.name,s.sno,s.age,s.gender,s.enroll_date,s.class_id,c.name from student s,class c where s.class_id = c.id and s.name = '宋江';
+----+--------+--------+------+--------+-------------+----------+-----------+
| id | name   | sno    | age  | gender | enroll_date | class_id | name      |
+----+--------+--------+------+--------+-------------+----------+-----------+
|  5 | 宋江   | 200001 |   18 |      1 | 2000-09-01  |        2 | C++001班  |
+----+--------+--------+------+--------+-------------+----------+-----------+
1 row in set (0.00 sec)

2.2內(nèi)連接

select 
{col_name | expr} ,...  #字段
from 
tal_name1 name1, #表1 別名1
tal_name2 name2  #表2 別名2
where where_condition #where 判斷條件
and other_condition; #and 其他條件

select 
{col_name | expr} ,... #字段
from 
tal_name1 name1 #表1 別名1
[inner] join #內(nèi)連接
tal_name2 name2 #表2 別名2
on connect_condition #on 連接條件
where other_condition; #where 其他條件

這兩種方法都是內(nèi)連接所以大家可以發(fā)現(xiàn)我們?cè)谏厦娅@得笛卡爾積再增加判斷條件的時(shí)候就是在把這兩張表進(jìn)行內(nèi)連接。既然現(xiàn)在知道了是將這兩個(gè)表進(jìn)行內(nèi)連接那么大家以后就把不要用上面那種方法了用下面這種可以顯式說(shuō)明是內(nèi)連接的。

2.3外連接

外連接分為左外連接，右外連接以及全外連接但是MySQL不支持全外連接。

左外連接：返回左表的所有記錄和右表中匹配的記錄。如果右表中沒(méi)有匹配的記錄，則結(jié)果集對(duì)應(yīng)字段會(huì)顯式為NULL。
右外連接：與左外連接相反，返回右表的所有記錄和左表中匹配的記錄。如果左表中沒(méi)有匹配的記錄，則結(jié)果集中對(duì)應(yīng)字段會(huì)顯式為NULL。
全外連接：結(jié)合了左外連接和右外連接的特點(diǎn)，返回左右表中的所有記錄。如果某一邊表中沒(méi)有匹配的記錄，則結(jié)果集中對(duì)應(yīng)字段會(huì)顯示為NULL。

#左外連接，表1完全顯?
select 字段名 from 表名1 left join 表名2 on 連接條件;
#右外連接，表2完全顯?
select 字段 from 表名1 right join 表名2 on 連接條件;

我們來(lái)各自舉個(gè)例子方便大家理解

#左外連接
#列出每個(gè)學(xué)生的每門課程的考試成績(jī)
mysql> select s.id,s.name,s.sno,s.age,sc.* from student s left join score sc on s.id = sc.student_id;
+----+--------------+--------+------+------+-------+------------+-----------+
| id | name         | sno    | age  | id   | score | student_id | course_id |
+----+--------------+--------+------+------+-------+------------+-----------+
|  1 | 唐三藏       | 100001 |   18 |    1 |  70.5 |          1 |         1 |
|  1 | 唐三藏       | 100001 |   18 |    2 |  98.5 |          1 |         3 |
|  1 | 唐三藏       | 100001 |   18 |    3 |    33 |          1 |         5 |
|  1 | 唐三藏       | 100001 |   18 |    4 |    98 |          1 |         6 |
|  2 | 孫悟空       | 100002 |   18 |    5 |    60 |          2 |         1 |
|  2 | 孫悟空       | 100002 |   18 |    6 |  59.5 |          2 |         5 |
|  3 | 豬悟能       | 100003 |   18 |    7 |    33 |          3 |         1 |
|  3 | 豬悟能       | 100003 |   18 |    8 |    68 |          3 |         3 |
|  3 | 豬悟能       | 100003 |   18 |    9 |    99 |          3 |         5 |
|  4 | 沙悟凈       | 100004 |   18 |   10 |    67 |          4 |         1 |
|  4 | 沙悟凈       | 100004 |   18 |   11 |    23 |          4 |         3 |
|  4 | 沙悟凈       | 100004 |   18 |   12 |    56 |          4 |         5 |
|  4 | 沙悟凈       | 100004 |   18 |   13 |    72 |          4 |         6 |
|  5 | 宋江         | 200001 |   18 |   14 |    81 |          5 |         1 |
|  5 | 宋江         | 200001 |   18 |   15 |    37 |          5 |         5 |
|  6 | 武松         | 200002 |   18 |   16 |    56 |          6 |         2 |
|  6 | 武松         | 200002 |   18 |   17 |    43 |          6 |         4 |
|  6 | 武松         | 200002 |   18 |   18 |    79 |          6 |         6 |
|  7 | 李逹         | 200003 |   18 |   19 |    80 |          7 |         2 |
|  7 | 李逹         | 200003 |   18 |   20 |    92 |          7 |         6 |
|  8 | 不想畢業(yè)     | 200004 |   18 | NULL |  NULL |       NULL |      NULL |
+----+--------------+--------+------+------+-------+------------+-----------+
21 rows in set (0.00 sec)

可以發(fā)現(xiàn)當(dāng)student為左表時(shí)即使左表的某行在右表中沒(méi)有數(shù)據(jù)但是也會(huì)顯示出來(lái)只不過(guò)右表的數(shù)據(jù)為空而已，具體來(lái)說(shuō)就是最后一行。

#右外連接
#查詢沒(méi)有學(xué)生的班級(jí)
mysql> select s.id,s.name,s.class_id,cl.* from student s right join class cl on s.class_id = cl.id;
+------+--------------+----------+----+--------------+
| id   | name         | class_id | id | name         |
+------+--------------+----------+----+--------------+
|    1 | 唐三藏       |        1 |  1 | Java001班    |
|    2 | 孫悟空       |        1 |  1 | Java001班    |
|    3 | 豬悟能       |        1 |  1 | Java001班    |
|    4 | 沙悟凈       |        1 |  1 | Java001班    |
|    5 | 宋江         |        2 |  2 | C++001班     |
|    6 | 武松         |        2 |  2 | C++001班     |
|    7 | 李逹         |        2 |  2 | C++001班     |
|    8 | 不想畢業(yè)     |        2 |  2 | C++001班     |
| NULL | NULL         |     NULL |  3 | 前端001班    |
+------+--------------+----------+----+--------------+
9 rows in set (0.00 sec)

右外連接則與左外連接相反，它會(huì)完全展示出右表的數(shù)據(jù)即使右表對(duì)應(yīng)行在左表中沒(méi)有數(shù)據(jù)。

2.4 自連接

內(nèi)外連接都說(shuō)完了就到了自連接了它也很好理解就是自己和自己取笛卡爾積再進(jìn)行條件判斷?？梢杂糜谕瓿杀碇行信c行的對(duì)比但是在使用時(shí)必須給每個(gè)表取別名。

#顯?所有"MySQL"成績(jī)?"JAVA"成績(jī)?的成績(jī)信息

#1.獲取java和MySQL的課程代碼
mysql> select * from course where name = 'Java' or name = 'MySQL';
+----+-------+
| id | name  |
+----+-------+
|  1 | Java  |
|  3 | MySQL |
+----+-------+
2 rows in set (0.00 sec)

#2.獲取MySQL成績(jī)比java好的信息
mysql> select s1.* from score s1, score s2 where s1.student_id = s2.student_id 
    -> and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  2 |  98.5 |          1 |         3 |
|  8 |    68 |          3 |         3 |
+----+-------+------------+-----------+
2 rows in set (0.00 sec)

#也可以將其組合在一起
mysql> select s1.* from
    ->  score s1, 
    ->  score s2, 
    ->  course c1,
    ->  course c2
    -> where
    ->  s1.student_id = s2.student_id
    -> and
    ->  s1.course_id = c1.id
    -> and
    ->  s2.course_id = c2.id 
    -> and
    ->  s1.score > s2.score
    -> and
    ->  c1.`name` = 'MySQL'
    -> and
    ->  c2.`name` = 'Java';
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  2 |  98.5 |          1 |         3 |
|  8 |    68 |          3 |         3 |
+----+-------+------------+-----------+
2 rows in set (0.01 sec)

2.5子查詢

子查詢就是將一個(gè)select的結(jié)果當(dāng)作另外一個(gè)select的條件，也被叫做嵌套查詢。一樣我們還是開(kāi)始舉例。
同時(shí)子查詢也被分為三種：?jiǎn)涡凶硬樵?，多行子查詢，多列子查?/p>

#單行子查詢
#查詢不想畢業(yè)的同班同學(xué)
mysql> select * from student where class_id = (select class_id from student where name = '不想畢業(yè)');
+----+--------------+--------+------+--------+-------------+----------+
| id | name         | sno    | age  | gender | enroll_date | class_id |
+----+--------------+--------+------+--------+-------------+----------+
|  5 | 宋江         | 200001 |   18 |      1 | 2000-09-01  |        2 |
|  6 | 武松         | 200002 |   18 |      1 | 2000-09-01  |        2 |
|  7 | 李逹         | 200003 |   18 |      1 | 2000-09-01  |        2 |
|  8 | 不想畢業(yè)     | 200004 |   18 |      1 | 2000-09-01  |        2 |
+----+--------------+--------+------+--------+-------------+----------+
4 rows in set (0.01 sec)

#多行子查詢
#查詢課程Java或者M(jìn)ySQL的成績(jī)信息
mysql> select * from score where course_id in (select id from  course where name = 'Java' or name = 'MySQL');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  1 |  70.5 |          1 |         1 |
|  5 |    60 |          2 |         1 |
|  7 |    33 |          3 |         1 |
| 10 |    67 |          4 |         1 |
| 14 |    81 |          5 |         1 |
|  2 |  98.5 |          1 |         3 |
|  8 |    68 |          3 |         3 |
| 11 |    23 |          4 |         3 |
+----+-------+------------+-----------+
8 rows in set (0.00 sec)

#多列子查詢
#插入重復(fù)成績(jī)
mysql> insert into score(score, student_id, course_id) values
    -> (70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

#查找重復(fù)的成績(jī)
mysql> SELECT * FROM score WHERE (score, student_id, course_id ) IN ( 
    ->  SELECT score, student_id,
    ->  course_id FROM score GROUP BY score, student_id, course_id HAVING
    ->  count( 0 ) > 1);
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
|  1 |  70.5 |          1 |         1 |
|  2 |  98.5 |          1 |         3 |
|  5 |    60 |          2 |         1 |
| 21 |  70.5 |          1 |         1 |
| 22 |  98.5 |          1 |         3 |
| 23 |    60 |          2 |         1 |
+----+-------+------------+-----------+
6 rows in set (0.00 sec)

之前我們說(shuō)過(guò)MySQL中一切都是表所以select的結(jié)果不光可以當(dāng)作判斷條件來(lái)完成子查詢也可以當(dāng)作from后的表來(lái)完成子查詢，這是因?yàn)閟elect的結(jié)果就是一張臨時(shí)表。

2.6合并查詢

當(dāng)我們想要合并多個(gè)select的結(jié)果，我們就可以使用集合操作符union和union all。

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

mysql> select * from student1;
+----+-----------+--------+------+--------+-------------+----------+
| id | name      | sno    | age  | gender | enroll_date | class_id |
+----+-----------+--------+------+--------+-------------+----------+
|  1 | 唐三藏    | 100001 |   18 |      1 | 1986-09-01  |        1 |
|  2 | 劉備      | 300001 |   18 |      1 | 1993-09-01  |        3 |
|  3 | 張?      | 300002 |   18 |      1 | 1993-09-01  |        3 |
|  4 | 關(guān)?      | 300003 |   18 |      1 | 1993-09-01  |        3 |
+----+-----------+--------+------+--------+-------------+----------+
4 rows in set (0.00 sec)

mysql> select * from student;
+----+--------------+--------+------+--------+-------------+----------+
| id | name         | sno    | age  | gender | enroll_date | class_id |
+----+--------------+--------+------+--------+-------------+----------+
|  1 | 唐三藏       | 100001 |   18 |      1 | 1986-09-01  |        1 |
|  2 | 孫悟空       | 100002 |   18 |      1 | 1986-09-01  |        1 |
|  3 | 豬悟能       | 100003 |   18 |      1 | 1986-09-01  |        1 |
|  4 | 沙悟凈       | 100004 |   18 |      1 | 1986-09-01  |        1 |
|  5 | 宋江         | 200001 |   18 |      1 | 2000-09-01  |        2 |
|  6 | 武松         | 200002 |   18 |      1 | 2000-09-01  |        2 |
|  7 | 李逹         | 200003 |   18 |      1 | 2000-09-01  |        2 |
|  8 | 不想畢業(yè)     | 200004 |   18 |      1 | 2000-09-01  |        2 |
+----+--------------+--------+------+--------+-------------+----------+
8 rows in set (0.00 sec)

mysql> select * from student where id < 3 union select * from student1;
+----+-----------+--------+------+--------+-------------+----------+
| id | name      | sno    | age  | gender | enroll_date | class_id |
+----+-----------+--------+------+--------+-------------+----------+
|  1 | 唐三藏    | 100001 |   18 |      1 | 1986-09-01  |        1 |
|  2 | 孫悟空    | 100002 |   18 |      1 | 1986-09-01  |        1 |
|  2 | 劉備      | 300001 |   18 |      1 | 1993-09-01  |        3 |
|  3 | 張?      | 300002 |   18 |      1 | 1993-09-01  |        3 |
|  4 | 關(guān)?      | 300003 |   18 |      1 | 1993-09-01  |        3 |
+----+-----------+--------+------+--------+-------------+----------+
5 rows in set (0.00 sec)

mysql> select * from student where id < 3 union all select * from student1;
+----+-----------+--------+------+--------+-------------+----------+
| id | name      | sno    | age  | gender | enroll_date | class_id |
+----+-----------+--------+------+--------+-------------+----------+
|  1 | 唐三藏    | 100001 |   18 |      1 | 1986-09-01  |        1 |
|  2 | 孫悟空    | 100002 |   18 |      1 | 1986-09-01  |        1 |
|  1 | 唐三藏    | 100001 |   18 |      1 | 1986-09-01  |        1 |
|  2 | 劉備      | 300001 |   18 |      1 | 1993-09-01  |        3 |
|  3 | 張?      | 300002 |   18 |      1 | 1993-09-01  |        3 |
|  4 | 關(guān)?      | 300003 |   18 |      1 | 1993-09-01  |        3 |
+----+-----------+--------+------+--------+-------------+----------+
6 rows in set (0.00 sec)

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)的內(nèi)嵌函數(shù)和聯(lián)合查詢的文章就介紹到這了,更多相關(guān)MySQL內(nèi)嵌函數(shù)和聯(lián)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: