mysql多表查詢實例（mysql多對多查詢）

本文目錄一覽：

• 1、mysql多表查詢並創建視圖
• 2、mysql複雜查詢–多表查詢
• 3、MYSQL多表聯合查詢 （4表）

mysql多表查詢並創建視圖

MySQL在多個數據表上創建視圖

在MySQL中，使用CREATE VIEW語句也可以在兩個或兩個以上的數據表上創建視圖。

實例

在department表和worker表上創建一個名為worker_view1的視圖。

1. 創建視圖的代碼如下：

CREATE ALGORITHM=MERGE VIEW

worker_view1(name,department,sex,age,location)

AS SELECT name,department.d_name,sex,birthday,address

FROM worker,department WHERE worker.d_id=department.d_id

WITH LOCAL CHECK OPTION;

在DOS提示符窗口中查看MySQL在多個數據表上創建視圖的操作效果。如下圖所示：

在DOS提示符窗口中查看MySQL在多個數據表上創建視圖的操作效果

從上圖中可以看出，代碼的執行結果顯示視圖創建成功。

2. 使用DESC語句查詢虛擬表的結構。如下圖所示：

使用DESC語句查詢虛擬表的結構

從上圖中可以看出，視圖worker_view1的屬性分別為name、department、sex、age和location。

視圖指定的屬性列表對應着兩個不同的表的屬性列（字段）。

視圖的屬性名與屬性列表中的屬性名相同。

該實例中的SELECT語句查詢出了department表的d_name字段，還有worker表的name、sex、birthday和address。其中，department表的d_name字段對應視圖的department字段；worker表的birthday字段對應視圖的age字段。

同時，視圖worker_view1的ALGORITHM的值指定為MERGE。還增加了WITH LOCAL CHECK OPTION約束。

本示例說明，視圖可以將多個表上的操作簡潔的表示出來。

提示

同時在多個表上創建視圖是非常有用的。比如，系統中有student表、department表、score表和grade表，分別存儲學生的信息、院系信息、課程信息和成績信息。可以在這4個表上創建一個視圖，用來顯示學生姓名、學號、班級、院系、所選課程和課程成績。

mysql複雜查詢–多表查詢

多表查詢是指基於兩個和兩個以上的表或是視圖的查詢.在實際應用中,查詢單個表可能不能滿足你的需求,（如顯示sales部門位置和其員工的姓名),這種情況下需要使用到(dept表和emp表)

自連接是指在同一張表的連接查詢

select * from emp where empno=(select mgr from emp where ename=’ford’);

子查詢是指嵌入在其它sql查詢語句中的select語句,也叫嵌套查詢

單行子查詢是 指只返回一行數據的子查詢語句

select * from emp where deptno=(select deptno from emp where ename=’smith’);

多行子查詢指返回多行數據的子查詢 使用關鍵字 in

如果我們的一個子查詢，返回的 結果是多列，就叫做列子查詢

合併多個select語句的結果，可以使用集合操作符 union,union all

MYSQL多表聯合查詢 （4表）

select * from 表1 inner join 表2 on 關聯條件

select * from 表1 left outer join 表2 on 關聯條件

select * from 表1 right outer join 表2 on 關聯條件

select * from 表1 cross join 表2 on 關聯條件

MYSQL查詢

查詢平均成績大於70分的同學的學號和平均成績

SELECT s.id,AVG(sc.score) FROM student s,studentcourse sc WHERE s.id=sc.student_id GROUP BY s.id HAVING  AVG(sc.score)70;

查詢所有同學的學號、姓名、選課數、總成績

SELECT id,NAME

FROM student

WHERE id NOT IN (SELECT student_id

FROM studentcourse

WHERE course_id IN (SELECT course.id

FROM teacher,course

WHERE teacher.id=course.teacher_id

AND teacher.name=’關羽’));

查詢學生信息和平均成績

SELECT s.id,s.name,s.city,s.age, c.name,sc.score,t.name

FROM student s,studentcourse sc,course c,teacher t

WHERE s.id=sc.student_id AND c.id=sc.course_id AND c.teacher_id=t.id GROUP BY s.id;