mysql進階(九)多表查詢

MySQL多表查詢

一 使用SELECT子句進行多表查詢

SELECT 字段名 FROM 表1，表2 … WHERE 表1.請欄的程式碼中，以兩張表的id字段資訊相同作為條件建立兩表關聯，但在實際開發中不應該這樣使用，最好用主外鍵約束來實現

二 使用表的別名進行多表查詢

如:

SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id

SQL語言中，可以透過兩種方式為表指定別名 

第一種是透過關鍵字AS指定,如

SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065  a,tb_demo065_tel  b WHERE a.id=b.id 
AND b.id='$_POST[textid]'

第二種是在表名後直接加表的別名實作

SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065 AS a,tb_demo065_tel AS b WHERE a.id=b.id

使用表的別名應注意幾下幾點

(1)別名通常是一個縮短了的表名，用於在連接中引用表中的特定列，如果連接中的多個表中有相同的名稱列存在，必須用表名或表的別名限定列名

(2)如果定義了表的別名就不能再使用表名

三 合併多個結果集

SQL語言中，可以透過UNION 或 ALL將多個SELECT語句的查詢結果合併輸出，這兩個關鍵字的使用說明如下：

UNION:利用該關鍵字可以將多個SELECT 語句的查詢結果合併輸出，並刪除重複行

ALL:利用該關鍵字可以將多個SELECT 語句的查詢結果合併輸出，但不會刪除重複行

在使用UNION或ALL關鍵字將多個表合併輸出時，查詢結果必須具有相同的結構且資料類型必須相容,另外使用UNION時兩張表的欄位數量也必須相同，否則會提示SQL語句有錯誤。

SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065  a,tb_demo065_tel  b WHERE a.id=b.id

四 簡單巢狀查詢

子查詢:子查詢是一個SELECT查詢，傳回單一值且巢狀在SELECT、INSERT、UPDATE和DELETE語句或其它查詢語句中，任何可以使用表達式的地方都可以使用子查詢.

e.x:SELECT id,name,pwd FROM tb_demo067 UNION SELECT  uid,price,date FROM tb_demo067_tel

內連接：把查詢結果作為WHERE子句的查詢條件即稱為內連接

五 複雜的嵌套查詢

多表之間的嵌套查詢可以透過謂詞IN實現，語法格式如下:

SELECT id,name,sex,date FROM tb_demo068 WHERE id in(SELECT id FROM tb_demo068 WHERE id='$_POST[test]')

參數說明：test_expression指SQL表達式，subquery包含某結果集的子查詢

多表嵌套查詢的原理:無論是多少張表進行嵌套，表與表之間一定存在某種關聯，透過WHERE子句建立此種關聯實現查詢

六 嵌套查詢在查詢統計中的應用

實現多表查詢時，可以同時使用謂詞ANY、SOME、ALL,這些謂詞稱為定量比較謂詞，可以和比較運算子聯合使用，判斷是否全部回傳值都滿足搜尋條件.SOME和ANY謂詞是存在量的，只注重是否有回傳值滿足搜尋條件，這兩個謂詞的意思相同，可以替換使用;ALL謂詞稱為通用謂詞，它只關心​​是否有謂詞滿足搜尋要求.

test_expression[NOT] IN{
 subquery
}

>ANY 大於子查詢中的某個值 

 

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某個值  

>=ANY 大於等於子查詢中的某值

=ANY 等於子查詢中的某個值  

!=ANY或ANY 不等於子查詢中的某值  

>ALL 中的所有值  

 

>=ALL 大於等於子查詢中的所有值 

🎜=ALL 等於子查詢中的所有值 >ALL 不等於子查詢中的所有值🎜🎜七 使用子查詢作派生的表🎜🎜在實際項目開發過程中經常用到從一個信息較為完善的表中派生出一個只含有幾個關鍵字段的資訊表，透過子查詢就可以來實現這一目標,如🎜

SELECT * FROM tb_demo069_people WHERE uid IN(SELECT deptID FROM tb_demo069_dept WHERE deptName='$_POST[select]')
SELECT a.id,a.name FROM tb_demo067 AS a WHERE id<3)

🎜注:子查詢應遵循以下規則:🎜🎜

(1)由比较运算符引入的内层子查询只包含一个表达式或列名，在外层语句中的WHERE子句内命名的列必须与内层子查询命名的列兼容

(2)由不可更改的比较运算符引入的子查询(比较运算符后面不跟关键字ANY或ALL)不包括GROUP BY 或 HAVING子句，除非预先确定了成组或单个的值

(3)用EXISTS引入的SELECT列表一般都由*组成，不必指定列名

(4)子查询不能在内部处理其结果

八 使用子查询作表达式

SELECT (SELECT AVG(chinese)FROM tb_demo071),(SELECT AVG(english)FROM tb_demo071),(SELECT AVG(math)FROM tb_demo071) FROM tb_demo071

注：在使用子查询时最好为列表项取个别名，这样可以方便用户在使用mysql_fetch_array()函数时为表项赋值,如

SELECT (SELECT AVG(chinese) FROM tb_demo071) AS yuwen ,(SELECT AVG(english) FROM tb_demo071) 
AS yingyu,(SELECT AVG(math) FROM tb_demo071) AS shuxue FROM tb_demo071

九 使用子查询关联数据

SELECT * FROM tb_demo072_student WHERE id=(SELECT id FROM tb_demo072_class WHERE className = '$_POST[text]')

十 多表联合查询

利用SQL语句中的UNION，可以将不同表中符合条件的数据信息显示在同一列中。

e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten

注:使用UNION时应注意以下两点：

(1)在使用UNION运算符组合的语句中，所有选择列表的表达式数目必须相同，如列名、算术表达式及聚合函数等

(2)在每个查询表中，对应列的数据结构必须一样。

十一 对联合后的结果进行排序

为了UNION的运算兼容，要求所有SELECT语句都不能有ORDER BY语句，但有一种情况例外，那就是在最后一个SELECT语句中放置ORDER BY 子句实现结果的最终排序输出。

e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten ORDER BY id

使用UNION条件上相对比较苛刻，所以使用此语句时一定要注意两个表项数目和字段类型是否相同

十二 条件联合语句

SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name='人民邮电出版社' OR name='机械工业出版社' UNION SELECT * FROM tb_demo076_BEIJING 
GROUP BY name HAVING name <>&#39;人民邮电出版社&#39; AND name <>&#39;机械工业再版社&#39; ORDER BY id

上面语句应用了GROUP BY分组语句和HAVING语句实现条件联合查询。其实现目的是先保证将'人民邮电出版社'和'机械工业出版社'始终位于名单最前列，然后再输出其它的出版社

十三 简单内连接查询

SELECT filedlist FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column1

其中，filedlist是要显示的字段,INNER表示表之间的连接方式为内连接，table1.column1=table2.column1用于指明两表间的连接条件，如:

SELECT a.name,a.address,a.date,b.chinese,b.math,b.english FROM tb_demo065 AS a INNER JOIN tb_demo065_tel AS b on a.id=b.id

十四 复杂内连接查询

复杂的内连接查询是在基本的内连接查询的基础上再附加一些查询条件，如:

SELECT a.name,a.address,a.date,b.chinese,b.math,b.english FROM tb_demo065 AS a INNER JOIN tb_demo065_tel AS b on a.id=b.id 
WHERE b.id=(SELECT id FROM  tb_demo065 WHERE tb_demo065.name='$_POST[text]')

总之，实现表与表之间的关联的本质是两表之间存在共同的数据项或者相同的数据项，通过WHERE 子句或内连接INNER JOIN … ON 语句将两表连接起来，实现查询

十五 使用外连接实现多表联合查询

(1)LEFT OUTER JOIN表示表之间通过左连接方式相互连接，也可简写成LEFT JOIN,它是以左侧的表为基准故称左连接，左侧表中所有信息将被全部输出，而右侧表信息则只会输出符合条件的信息，对不符合条件的信息则返回NULL

e.x:SELECT a.name,a.address,b.math,b.english FROM tb_demo065 AS A LEFT OUTER JOIN tb_demo065_tel AS b ON a.id=b.id

(2)RIGHT OUTER JOIN表示表之间通过右连接方式相互连接，也可简写成RIGHT JOIN,它是以右侧的表为基准故称右连接，右侧表中所有信息将被全部输出，而左侧表信息则只会输出符合条件的信息，对不符合条件的信息则返回NULL

E.X:SELECT a.name,a.address,b.math,b.english FROM tb_demo065 AS A RIGHT OUTER JOIN tb_demo065_tel AS b ON a.id=b.id

十六 利用IN或NOTIN关键字限定范围

e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code BETWEEN '$_POST[text1]' AND '$_POST[text2]')

利用IN可指定在范围内查询，若要求在某范围外查询可以用NOT IN代替它

十七 由IN引入的关联子查询

e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code = '$_POST[text]')

十八 利用HAVING语句过滤分组数据

HAVING子句用于指定组或聚合的搜索条件，HAVING通常与GROUP BY 语句一起使用，如果SQL语句中不含GROUP BY子句，则HAVING的行为与WHERE子句一样.

e.x:SELECT name,math FROM tb_demo083 GROUP BY id HAVING math > '95'