MySQL-多表查詢-有解無憂

MySQL-多表查詢

文章目錄

- MySQL-多表查詢
- - 課前題目：
  - 1. 多表連接查詢
  - - 1.1 等值連接（相等連接）：從where中連接（用=號進行匹配）
    - 1.2 自然連接
    - 等值連接和自然連接的區別：
    - 1.3 自/內/外連接：從from中連接（JION連接)
  - 2. 子查詢
  - - 2.1 帶有IN謂詞的子查詢
    - 2.2 帶有ANY（SOME）或ALL謂詞的子查詢
    - 2.3 帶有條件判斷符的子查詢
    - 2.4 帶有EXISTS謂詞的子查詢
  - 3. 派生表
  - 4. 合并查詢結果
  - 5. 實驗題：

課前題目：

建立一個school資料庫，將school.sql資料匯入到shool資料庫中，建立xkgl資料庫，將xkgl.sql資料匯入到xkgl資料庫中，

create database school;
create database xkgl;

觀察school資料庫中course表每一列的列名，將xkgl資料庫中course表前三行資料對應列內容插入到school的course表中，（請用insert陳述句+select陳述句完成將查找到的資訊插入表的操作），資料插入后，打開school資料庫的course表，應當看到如下三行內容）

INSERT INTO school.course 

SELECT courseid,coursename,bookname 

FROM course

LIMIT 0,3

輸出平均成績大于68分的同學的學號和他的平均成績[school資料庫]

SELECT studentid,AVG(grade)

FROM grade

GROUP BY school.grade

HAVING AVG(grade)>68

1. 多表連接查詢

在關系型資料庫管理系統中，通常一張表只會存盤一個物體的相關資訊，如果用戶需要查詢多張表中不同物體的資料，必須要將多個表的資料進行連接，

[shool資料庫：]

[例]查詢每條成績對應的學生姓名，課程號，成績

#1、自然連接

SELECT studentname,courseid,grade

FROM grade,student

WHERE grade.studentid = student.studentid

#2、內連接

SELECT studentname, courseid, grade 

FROM student

INNER JOIN grade ON grade.studentid = student.studentid

如果用戶需要查詢多張表中不同物體的資料，可以使對表執行連接查詢操作，但前提條件是，這些表中必須存在具有相同意義的欄位，

連接查詢：同時涉及兩個以上的表的查詢，用連接欄位連接

1.1 等值連接（相等連接）：從where中連接（用=號進行匹配）

SELECT {*|col_list}

FROM table1,table2

WHERE table1.section_id=table_2.section_id;

1.2 自然連接

資料庫應用中最常用的是“自然連接”，它在目標列中去除相同的欄位名，

進行自然連接運算要求兩個表有共同屬性（列），自然連接運算的結果表是在參與操作的兩個表的共同屬性上進行等值連接后，再去除重復的屬性后所得的新表，

等值連接和自然連接的區別：

1）等值連接中不要求相等屬性值的屬性名相同，而自然連接要求相等屬性值的屬性名必須相同，即兩關系只有在同名屬性才能進行自然連接，
2）等值連接不將重復屬性去掉，而自然連接去掉重復屬性，也可以說，自然連接是去掉重復列的等值連接，

1.3 自/內/外連接：從from中連接（JION連接)

自連接作為一種特例，可以將一個表與它自身進行連接，稱為自連接，若要在一個表中查找具有相同列值的行，則可以使用自連接，使用自連接時需為表指定兩個別名，且對所有列的參考均要用別名限定，

SELECT a.學號, a.課程號, b.課程號, a.成績
	FROM CJB a  JOIN  CJB b 
			ON  a.成績=b.成績 AND  a.學號=b.學號 AND  a.課程號!=b.課程號

指定了INNER關鍵字的連接是內連接，內連接按照ON所指定的連接條件合并兩個表，回傳滿足條件的行，內連接是系統默認的，可以省略INNER關鍵字，使用內連接后仍可使用WHERE子句指定條件，

指定了left/right關鍵字的為外連接，外連接的結果表不但包含滿足連接條件的行，還包括相應表中的所有行

SELECT {*|col_list} 
 
FROM table_name1

INNER/LEFT/RIGHT JOIN table_name2 ON condition

[例]查詢每條成績對應的學生學號，姓名，課程號，成績

SELECT grade.studentid,studentname,courseid,grade

FROM grade,student

WHERE grade.studentid = student.studentid

[例]查詢每個同學的成績，輸出資訊包括學號，學生名，課程號，成績，如果該學生沒有選過課，則輸出該同學的學號和姓名，課程號和成績資訊為空

SELECT student.studentid,studentname,courseid,grade

FROM student 

left JOIN grade

ON grade.studentid = student.studentid 

#權利大的表是基本表，基本表中的資料都會保留

[學習通]查詢每條成績對應的學號，課程號，課程名，成績

[分別用自然連接和外連接兩種方式做]

自然連接

SELECT studentid,grade.courseid,coursename,grade

FROM grade,course

WHERE grade.courseid = course.courseid

外連接

SELECT studentid,grade.courseid,coursename,grade

FROM grade

LEFT JOIN course 

ON grade.courseid = course.courseid

復合條件連接查詢是通過在連接查詢中添加過濾條件，以達到限制查詢結果和篩選資料的目的

[例2]查詢選修java程式設計課程且成績在85分以上的所有學生的學號和姓名以及成績，

#1、內連接

SELECT student.studentid,studentname,coursename,grade

FROM grade

INNER JOIN course ON grade.courseid = course.courseid

INNER JOIN student ON grade.studentid = student.studentid

WHERE coursename LIKE "J%" AND grade>85


#2、自然連接

SELECT student.studentid,studentname,coursename,grade

FROM grade,student,course

WHERE grade.studentid = student.studentid 

AND grade.courseid = course.courseid

[xkgl資料庫：]

[課堂練習]查詢計算機系所有學生的姓名和所在班級名

#1、自然連接

SELECT studentname,classname 

FROM department,student,class 

WHERE departmentname = "計算機系" 

AND class.DepartmentID = department.DepartmentID 

AND student.ClassID = class.ClassID

 

#2、內連接

SELECT studentname,classname 

FROM department

INNER JOIN class ON class.DepartmentID = department.DepartmentID

INNER JOIN student ON student.ClassID = class.ClassID 

WHERE departmentname = "計算機系"

[學習通]：查詢每個同學的平均成績，輸出姓名，平均成績

1、自然連接

SELECT studentname,AVG( grade ) AS 平均成績 ,student.studentid

FROM grade,student

WHERE grade.StudentID = student.StudentID 

GROUP BY Studentid

#2、內連接

SELECT studentname,AVG( grade ) AS 平均成績,student.studentid

FROM grade

INNER JOIN student ON grade.StudentID = student.StudentID

GROUP BY studentid

2. 子查詢

如果一個查詢陳述句中嵌套了一個或若干個其他的查詢陳述句，那么在整個陳述句中，外層查詢稱為主查詢，內層查詢稱為子查詢或者嵌套查詢，該類查詢可以基于一個表或多個表，在此類查詢中，系統會先執行子查詢，將子查詢的結果作為主查詢的過濾條件，子查詢分為以下幾種：

2.1 帶有IN謂詞的子查詢

2.2 帶有ANY（SOME）或ALL謂詞的子查詢

2.3 帶有條件判斷符的子查詢

2.4 帶有EXISTS謂詞的子查詢

2.1 帶有IN謂詞的子查詢

當子查詢回傳的是一個資料集合，主查詢需要回傳符合集合中條件的記錄時，可以使用IN關鍵字，語法形式如下：

SELECT {*|col_list} FROM table_name1 

WHERE col_name1 IN 

(SELECT col_name2 FROM table_name2 [WHERE condition]);

[例]查詢屬于計算機系和信管系的班級名稱，輸出班級名稱和所屬系名稱

SELECT

 ClassName,

 DepartmentName

FROM

 class c,

 department d 

WHERE

 c.DepartmentID = d.DepartmentID 

 AND c.DepartmentID IN ( SELECT DepartmentID FROM department d WHERE d.DepartmentName IN ( "計算機系", "信管系" ) );

[學習通]查詢計算機系年齡最大的學生姓名及當前年齡（假設計算機系年齡最大的學生為20歲，將所有計算機系20歲的同學姓名都輸出）

SELECT s.StudentName,'2020'-YEAR(birth1) as 年齡

FROM student s,department d,class c

WHERE s.ClassID = c.ClassID 

AND c.DepartmentID = d.DepartmentID

AND d.DepartmentName = "計算機系"

AND YEAR(birth1) =(

  SELECT DISTINCT YEAR(birth1) AS z

  FROM student

  ORDER BY z

  LIMIT 1

)

另外，子查詢還可以和NOT IN配合使用，表示：不在此范圍內任一資料都可以

[課堂練習]查詢不屬于計算機系的班級名稱

SELECT

 ClassName,

 DepartmentName

FROM

 class c,

 department d 

WHERE

 c.DepartmentID = d.DepartmentID 

 AND c.DepartmentID IN (

 SELECT

  DepartmentID

 FROM

  department d 

 WHERE

 d.DepartmentName NOT IN ( "計算機系" ));

2.2 使用ANY、ALL關鍵字的子查詢

l ANY（some）表示滿足其中任一條件，

該類查詢會創建一個運算式對子查詢的回傳值串列進行比較，只要滿足子查詢中的任一個比較條件，就回傳一個結果，其語法形式如下：

SELECT {*|col_list} FROM table_name1 

WHERE col_name1<any 

(SELECT col_name2 FROM table_name2 [WHERE condition]);

ALL表示滿足所有條件，

與ANY不同，使用關鍵字ALL的子查詢，表示當一條記錄符合子查詢結果中所有的條件時，才會回傳該記錄，其語法形式如下：

SELECT {*|col_list} FROM table_name1 

WHERE col_name1>All 

(SELECT col_name2 FROM table_name2 [WHERE 條件]);

[例]找出其他班級中比“10電子商務1班“最大的學生年齡還大的同學姓名，

#方法一

SELECT StudentName,birth1

FROM student s,class c

WHERE s.classid=c.ClassID

AND c.ClassName != "10電子商務1班"

AND YEAR(birth1)<(

  SELECT MIN(YEAR(birth1))

  FROM student s,class c

  WHERE ClassName = '10電子商務1班'

  AND s.ClassID = c.ClassID

)

#方法二

SELECT StudentName,birth1

FROM student

WHERE YEAR(birth1) <ALL (

  SELECT YEAR(birth1)

  FROM student s,class c

  WHERE ClassName = '10電子商務1班'

  AND s.ClassID = c.ClassID

)

[課堂練習]找出其他班級中年齡比“10電子商務1班“最小的學生年齡大的同學姓名，

#方法一

SELECT StudentName,birth1

FROM student

WHERE YEAR(birth1)<(

  SELECT max(YEAR(birth1))

  FROM student s,class c

  WHERE ClassName = '10電子商務1班'

  AND s.ClassID = c.ClassID

);

 
#方法二

SELECT StudentName,birth1

FROM student

WHERE YEAR(birth1)<ANY(

  SELECT YEAR(birth1)

  FROM student s,class c

  WHERE ClassName = '10電子商務1班'

  AND s.ClassID = c.ClassID

);

2.3 使用條件判斷符的子查詢

在子查詢中，還可以單獨使用條件判斷符，其語法形式如下：

SELECT {*|col_list} FROM table_name 1

WHERE col_name1 operators

(SELECT col_name2 FROM table_name2);

[例]找出’St0109010001’號學生超過他選修課程平均成績的課程號，

SELECT courseid

FROM grade

WHERE studentid = "St0109010001" 

AND grade >(

 SELECT AVG(grade)

?     FROM grade

?     WHERE studentid = "St0109010001" 

)

[課堂練習]找出其他班級中比“10電子商務1班“最大的學生年齡還大的同學姓名，

SELECT StudentName,birth1

FROM student s,class c

WHERE s.classid=c.ClassID

AND c.ClassName != "10電子商務1班"

AND YEAR(birth1)<(

  SELECT MIN(YEAR(birth1))

  FROM student s,class c

  WHERE ClassName = '10電子商務1班'

  AND s.ClassID = c.ClassID

)

[思考題]找出每個學生超過他選修課程平均成績的課程號，

SELECT courseid

FROM grade x

WHERE grade>(

  SELECT AVG(grade)

  FROM grade y

  WHERE y.studentid = x.studentid

)

不相關子查詢：

子查詢的查詢條件不依賴于父查詢

程序：由里向外逐層處理，即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件，

相關子查詢：

子查詢的查詢條件依賴于父查詢

程序：首先取外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若WHERE子句回傳值為真，則取此元組放入結果表，然后再取外層表的下一個元組，重復這一程序，直至外層表全部檢查完為止，

2.4 含有EXISTS關鍵字的子查詢

2.4.1 EXISTS謂詞

帶有EXISTS謂詞的子查詢不回傳任何資料，只產生邏輯真值“true”或邏輯假值“false”，

若內層查詢結果集非空，則外層的WHERE子句回傳真值

若內層查詢結果集為空，則外層的WHERE子句回傳假值

由EXISTS引出的子查詢，其目標列運算式通常都用 * ，因為帶EXISTS的子查詢只回傳真值或假值，給出列名無實際意義，

2.4.2 NOT EXISTS謂詞

若內層查詢結果非空，則外層的WHERE子句回傳假值

若內層查詢結果為空，則外層的WHERE子句回傳真值

Exists采用逐條遍歷的方式，每次查詢都會在內層進行匹配，若該記錄回傳條件為真，則輸出當前記錄，如果為假，則舍棄當前記錄

[例]查詢所有選修了Dp010001號課程的學生姓名，

SELECT studentname

FROM student s

WHERE EXISTS(

 SELECT *

 FROM grade g

 WHERE g.studentid = s.studentid 

 AND courseid = 'Dp010001'

)

[課堂練習]查詢沒有選修Dp010001號課程的學生姓名，

SELECT studentname

FROM student s

WHERE NOT EXISTS(

 SELECT *

 FROM grade g

 WHERE g.studentid = s.studentid 

 AND courseid = 'Dp010001'

)

[例]查詢選修了全部課程的學生姓名，

SELECT studentname

FROM student s,grade g

WHERE s.studentid = g.studentid

GROUP BY s.studentid

HAVING COUNT(*) = (SELECT COUNT(*) FROM course)

所有帶IN謂詞、比較運算子、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換

一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換

用EXISTS/NOT EXISTS實作全稱量詞（難點）

SQL語言中沒有全稱量詞? （For all）

可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：

（?x）P ≡ ? （? x（? P））

[課堂練習]查詢至少選修了學生St0109010002選修的全部課程的學生號，**

SELECT studentname

FROM student s

WHERE NOT EXISTS(

 SELECT *mysql

 FROM grade a

 WHERE a.studentid = 'St0109010002'

 AND NOT EXISTS(

  SELECT * 

  FROM grade b

  WHERE b.studentid = s.studentid

  AND a.courseid = b.courseid)

)

[課堂練習]查詢所有同時選修了Dp010001號課程和Dp010004號課程的學生姓名，

SELECT StudentName

FROM student s

WHERE NOT EXISTS(

 SELECT *

 FROM course co 

 WHERE (CourseID = 'Dp010001' OR CourseID = 'Dp010004')

 AND not EXISTS(

  SELECT *

  FROM Grade g

  WHERE s.StudentID = g.StudentID

  AND g.CourseID = co.CourseID

 )

)

3. 派生表

子查詢不僅可以出現在WHERE子句中，還可以出現在FROM子句中

將子查詢做為一個表來處理，這個由子查詢得出的新表就是我們說的"派生表"，

[例]利用派生表的方式查詢所有選修了Dp010001號課程的學生姓名

SELECT StudentName 

FROM student s,( SELECT CourseID, StudentID FROM Grade WHERE CourseID

= 'Dp010001' ) AS co 

WHERE s.studentid = co.StudentID

【練習】用派生表方式找出每個學生超過他自己選修課程平均成績的課程號

SELECT s.studentid,courseid

FROM grade g,( SELECT studentid, AVG( grade ) avg_grade

FROM grade GROUP BY studentid ) s

WHERE g.studentid = s.studentid

AND grade>avg_grade;

4. 合并查詢結果

合并查詢結果就是使用UNION關鍵字，將多條查詢陳述句的結果合并在一起顯示，UNION有兩種使用方法

1.查詢結果不重復（過濾掉重復的記錄）

2.保留所有查詢結果，

應用場景：適用于查詢多個表的時候，但多個表沒有直接的關系，查詢的列基本是一致時

特點

1、要求多條查詢陳述句的查詢列數必須一致

2、要求多條查詢陳述句的查詢的各列型別、順序最好一致

3、union 去重，union all包含重復項

[例]查詢所有選修了Dp010001號或Dp010004號課程的學生姓名，

SELECT

  StudentName ,st.StudentID 

FROM

 student st,

 grade g 

WHERE

 st.StudentID = g.StudentID 

 AND CourseID = 'Dp010001' 

UNION 

SELECT

 StudentName , st.StudentID

FROM

 student st,

 grade g 

WHERE

 st.StudentID = g.StudentID 

 AND CourseID = 'Dp010004';

[例]查詢選修了Dp010001號或Dp010004號課程的記錄一共有多少條?

SELECT

  StudentName ,st.StudentID,CourseID 

FROM

 student st,

 grade g 

WHERE

 st.StudentID = g.StudentID 

 AND CourseID = 'Dp010001' 

UNION ALL

SELECT

 StudentName , st.StudentID,CourseID 

FROM

 student st,

 grade g 

WHERE

 st.StudentID = g.StudentID 

 AND CourseID = 'Dp010004';

[練習]查找學生總數以及出生日期最早的同學姓名，將這兩個查詢結果合并到一個結果集中

SELECT COUNT(*) 學生總人數和出生最早學生

FROM student

UNION

SELECT StudentName

FROM student

WHERE birth1 = (SELECT MIN(birth1) FROM student)

5. 實驗題：

1. 查詢選修了課程的學生姓名

SELECT

 DISTINCT student.studentid,studentname

FROM

 student,grade

WHERE student.StudentID = grade.StudentID

2. 查詢11網路工程和09資料庫班每個學生的姓名和他所在的班級名，即使該班沒有學生，也要輸出班級名，

SELECT

 classname,studentname

FROM

 class LEFT JOIN student ON class.ClassID = student.classid

WHERE classname = "11網路工程" OR classname = "09資料庫班"

3. 求出JAVA程式設計考試的前三名的姓名和成績

SELECT

 student.studentid,

 grade,

 studentname,

 coursename 

FROM

 course,

 student,

 grade 

WHERE

 course.CourseID = grade.CourseID 

 AND student.StudentID = grade.StudentID 

 AND coursename = "JAVA程式設計" 

GROUP BY

 grade DESC,

 student.studentid 

 LIMIT 3;

4. 求信管系以外的同學姓名，所在班級名和所在系名

SELECT 

 studentname,classname,departmentname

FROM

 student,class,department

 WHERE department.Departmentid = class.DepartmentID

 AND student.ClassID = class.ClassID

and departmentname != "信管系"

5. 求姓名第二個字是麗的同學選修了哪些課程，請寫出同學名和課程名，

SELECT

 studentname,

 coursename 

FROM

 student,

 grade,

 course 

WHERE

 student.studentid = grade.StudentID 

 AND grade.CourseID = course.CourseID 

 AND studentname LIKE "_麗%";

6. 查詢11網路工程和10電子商務2班一共有多少人

SELECT count( StudentID ) 人數

FROM class c,student s 

WHERE s.ClassID = c.ClassID 

AND classname IN ('11網路工程','10電子商務2班')

7. 查詢哪些課程沒有被選修過

方法一：

SELECT CourseName

FROM course

WHERE CourseID not IN(

SELECT DISTINCT c.CourseID

FROM grade g

LEFT JOIN course c

ON c.CourseID = g.CourseID)

方法二：

SELECT CourseName 

FROM course 

WHERE coursename NOT IN (

SELECT DISTINCT CourseName 

FROM course co,grade g 

WHERE co.courseid = g.courseid 

AND StudentID IN ( SELECT DISTINCT StudentID 
	FROM course co, grade g 
	WHERE co.CourseID = g.CourseID )
	)

8. 找出選修了全部課程的同學的名字

SELECT StudentName

FROM student s

WHERE NOT EXISTS(

 SELECT *

 FROM course co

 WHERE NOT EXISTS(

  SELECT *

  FROM grade g

  WHERE g.StudentID = s.StudentID

  AND co.CourseID = g.CourseID

 )

)

9. 查詢有哪些同學，沒有選修學生st0210010005選修的課程（即st0210010005選修的課程他一門都沒選）

SELECT StudentName

FROM Student s

WHERE NOT EXISTS(

 SELECT *

 FROM grade a

 WHERE a.StudentID = 'st0210010005'

 AND EXISTS(

  SELECT *

  FROM grade g

  WHERE g.studentid = s.StudentID

  AND a.courseid = g.CourseID

 )

)

10. 找出每個學院每個班級各有多少人（需要通過student表找），需要輸出的結果如下：

 

SELECT

 DepartmentID,

 c.ClassID,

  COUNT(*) 

FROM

 student s,

  class c 

WHERE

 s.ClassID = c.ClassID 

GROUP BY

 DepartmentID,

  ClassID

11.12題選做

11. 查找每個科目前三名的學號和成績（需要輸出課程號，學號和成績）

SELECT

 CourseID 課程號,StudentID 學號,grade 成績

FROM

 grade 

WHERE( 

 SELECT count(*) 

 FROM grade AS a

 WHERE grade.CourseID = a.CourseID 

 AND Grade.Grade < a.Grade 

)< 3 

ORDER BY

 CourseID ASC,

 Grade.Grade DESC;

12. 求每個學生的課是哪位教師教授的（求學號，學生姓名，教師姓名，課程名稱）

SELECT
	student.studentname,
	course.coursename,
	teachername 
FROM
	student,
	grade,
	SCHEDULE,
	teacher,
	course 
WHERE
	student.studentid = grade.studentid 
	AND SCHEDULE.classid = student.classid 
	AND SCHEDULE.courseid = grade.courseid 
	AND course.courseid = grade.courseid 
	AND SCHEDULE.teacherid = teacher.teacherid;

本文都是自己上課的筆記和作業，如果有覺得我題目做的不對的或者麻煩的希望能指出來，讓我學到更多的知識，如果想要具體的資料庫檔案也可以聯系我，

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/203165.html

標籤：java

上一篇：部門頁面的回顯

下一篇：MySQL的介紹--下載與安裝--設定環境變數