目錄

• 多表聯(lián)查場(chǎng)景
• 一對(duì)一
• 多對(duì)一
• 多對(duì)多
• 內(nèi)連接查詢(xún)
• 外連接
• 自連接
• 聯(lián)合查詢(xún)
• 子查詢(xún)

多表聯(lián)查場(chǎng)景

一對(duì)一

用戶(hù)與用戶(hù)信息表：當(dāng)用戶(hù)的信息數(shù)據(jù)過(guò)多時(shí)，我們可以將其分成兩個(gè)表分別對(duì)應(yīng)用戶(hù)基本信息和用戶(hù)的詳情信息。

create table user(    `id` int auto_increment primary key comment"用戶(hù)id",    `name` varchar(10),    `age` varchar(10),    `gender` char,    `tel` varchar(30),    `school` varchar(20),    `addr` varchar(20),    `degree` varchar(10),    `university` varchar(10));

當(dāng)用戶(hù)信息過(guò)多時(shí)，就可以使用外鍵進(jìn)行關(guān)聯(lián)。在任意一方加入外鍵，關(guān)聯(lián)另一方主鍵，并且設(shè)置外鍵為唯一的UNIQUE如下實(shí)現(xiàn)：

create table user(    `id`  	 int auto_increment primary key,    `name`   varchar(10),    `age`    varchar(10),    `gender` char);create table user_info(    `id` int auto_increment primary key ,    `tel`varchar(30),    `school`     varchar(20),    `addr`       varchar(20),    `degree`     varchar(10),    `university` varchar(10),    `user_id` int unique,    constraint fk_user_info foreign key (user_id) references user(id));

多對(duì)一

部門(mén)與員工：一個(gè)員工對(duì)應(yīng)一個(gè)部門(mén)，一個(gè)部門(mén)對(duì)應(yīng)多個(gè)員工

員工指向多的一方，部門(mén)指向一的一方。此時(shí)應(yīng)該在員工表中創(chuàng)建外鍵，指向部門(mén)表中的主鍵

# 員工表create table emp(  `emp_id`      int auto_increment primary key,  `emp_name`    varchar(20) not null,  `emp_gender`  char,  `emp_tel`     varchar(30),  `emp_dept_id` int,  constraint fk_emp_dept foreign key (emp_dept_id) references dept (dept_id));# 部門(mén)表create table dept(  `dept_id` int auto_increment primary key,  `dept_name` varchar(20) not null);

查詢(xún)方法：

-- 正常單表查select * from emp;-- 全查 笛卡爾積select * from emp,dept;-- 聯(lián)查 消除無(wú)效的笛卡爾積select * from emp,dept where emp_dept_id = dept.dept_id;

多對(duì)多

學(xué)生與課程：一個(gè)學(xué)生可以選修多門(mén)課程，一門(mén)課程可以被多個(gè)學(xué)生選擇

此時(shí)我們應(yīng)該在學(xué)生表與課程表之間建立中間表。中間表包含兩個(gè)外鍵，分別對(duì)應(yīng)學(xué)生表和課程表的主鍵

首先我們準(zhǔn)備好數(shù)據(jù)，學(xué)生表+課程表+中間信息表

# 學(xué)生表CREATE TABLE student(	id INT auto_increment PRIMARY KEY COMMENT "主鍵ID",	name VARCHAR(10) COMMENT "姓名",	no VARCHAR(10) COMMENT "學(xué)號(hào)")COMMENT "學(xué)生表";INSERT INTO student VALUES(NULL,"小癟三","2001"),(NULL,"小癟四","2002"),(NULL,"小癟五","2003"),(NULL,"小癟六","2004");# 課程表CREATE TABLE course(	id INT auto_increment PRIMARY KEY COMMENT"主鍵ID",	name VARCHAR(10) COMMENT "課程名稱(chēng)")COMMENT "課程表";INSERT INTO course VALUES(NULL,"java"),(NULL,"PHP"),(NULL,"MySQL"),(NULL,"Hadoop");# 學(xué)生課程關(guān)系表（中間表）CREATE TABLE student_course(	id INT auto_increment COMMENT "主鍵" PRIMARY KEY,	student_id INT NOT NULL COMMENT "學(xué)生ID",	course_id INT NOT NULL COMMENT "課程ID",	CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course (id),	CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student (id))COMMENT "學(xué)生——課程關(guān)系中間表";INSERT INTO student_course VALUES(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

我們切換IDEA打開(kāi)可以清晰的看到三個(gè)表之間的關(guān)系，中間信息表中的兩個(gè)外鍵：student_id與course_id將學(xué)生表和課程表關(guān)聯(lián)了起來(lái)

內(nèi)連接查詢(xún)

查詢(xún)A集合與B集合的交集

-- 方式一select [字段列表] from 表1,表2 where 條件...;-- 方式二select [字段列表] from 表1 inner join 表2 on 條件...;

示例：查詢(xún)所有員工與其所屬部門(mén)

-- 方式一select emp_name,dept_name from emp inner join dept d on emp.emp_dept_id = d.dept_id;-- 方式二select emp_name,dept_name from emp,dept where emp.emp_dept_id = dept.dept_id;

外連接

右外連接：查詢(xún)右表所有數(shù)據(jù)以及兩表交集部分?jǐn)?shù)據(jù)

select 字段列表 from 表1 right outer join 表2 on 條件...;

左外連接：查詢(xún)左表所有數(shù)據(jù)以及兩表交集部分?jǐn)?shù)據(jù)

select 字段列表 from 表1 left outer join 表2 on 條件...;

示例：

-- 2. 查詢(xún)員工全部信息及其對(duì)應(yīng)的部門(mén)信息 左外連接select  e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id;-- 3. 查詢(xún)?nèi)坎块T(mén)和其對(duì)應(yīng)的所有員工信息 右外連接select d.*,e.emp_name from emp e right join dept d on e.emp_dept_id = d.dept_id;

自連接

當(dāng)前表與自身的連接查詢(xún)，自連接必須使用別名

格式：

select 字段列表 from 表A 別名 join 表A 別名 on 條件...;

在員工表中，所有的普通員工、管理者都是員工。查詢(xún)每個(gè)員工歸屬哪個(gè)管理者管理就需要用到自連接

select a.name,b.name from emp a join emp b on a.emp_id = b.manager_id;

聯(lián)合查詢(xún)

關(guān)鍵字 union [all] 將兩條sql語(yǔ)句查詢(xún)的結(jié)果拼接起來(lái)

-- 查詢(xún)年齡大于50的員工  和薪資小于10000的員工select * from emp where emp.emp_age > 50union allselect * from emp where emp.emp_salary < 10000;

加上all表示不會(huì)去重，不加all表示去重復(fù)（即同時(shí)滿(mǎn)足兩條sql語(yǔ)句的只出現(xiàn)一次即可）

多張表的列數(shù)必須保持一致，字段類(lèi)型也需要一致

子查詢(xún)

子查詢(xún)是指在SQL語(yǔ)句中嵌套select語(yǔ)句進(jìn)行嵌套查詢(xún)

select * from t1 where column1 =(select column1 from t2)；

子查詢(xún)的外部語(yǔ)句可以是insert、uodate、delete、select的任何一個(gè)

標(biāo)量子查詢(xún)示例：查詢(xún)‘ 開(kāi)發(fā)部 ’的全部員工信息

首先你可以利用正常的兩條sql去查詢(xún)：

-- 查詢(xún)“開(kāi)發(fā)部”的所有員工-- 1. 兩條語(yǔ)句查詢(xún)select dept_id from dept where dept_name = "開(kāi)發(fā)部";select emp_name from emp where emp_dept_id = 1;

或者你可以使用內(nèi)連接方式進(jìn)行聯(lián)查：

-- 內(nèi)連接select e.emp_name,d.dept_name from emp e inner join dept d on e.emp_dept_id = d.dept_id where dept_name = "開(kāi)發(fā)部";

或者使用子查詢(xún)：

-- 使用子查詢(xún)select * from emp where emp_dept_id = (select dept_id from dept where dept_name = "開(kāi)發(fā)部");

到此這篇關(guān)于MySQL多表聯(lián)查的實(shí)現(xiàn)思路的文章就介紹到這了,更多相關(guān)MySQL多表聯(lián)查內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！