MySQL多表聯查如何實現

本文小編為大家詳細介紹“MySQL多表聯查如何實現”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MySQL多表聯查如何實現”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

多表聯查場景

一對一

用戶與用戶信息表：當用戶的信息數據過多時，我們可以將其分成兩個表分別對應用戶基本信息和用戶的詳情信息。

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

當用戶信息過多時，就可以使用外鍵進行關聯。在任意一方加入外鍵，關聯另一方主鍵，并且設置外鍵為唯一的UNIQUE如下實現：

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)
);

多對一

部門與員工：一個員工對應一個部門，一個部門對應多個員工

員工指向多的一方，部門指向一的一方。此時應該在員工表中創建外鍵，指向部門表中的主鍵

# 員工表
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)
);
# 部門表
create table dept
(
  `dept_id` int auto_increment primary key,
  `dept_name` varchar(20) not null
);

查詢方法：

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

多對多

學生與課程：一個學生可以選修多門課程，一門課程可以被多個學生選擇

此時我們應該在學生表與課程表之間建立中間表。中間表包含兩個外鍵，分別對應學生表和課程表的主鍵

首先我們準備好數據，學生表+課程表+中間信息表

# 學生表
CREATE TABLE student(
	id INT auto_increment PRIMARY KEY COMMENT '主鍵ID',
	name VARCHAR(10) COMMENT '姓名',
	no VARCHAR(10) COMMENT '學號'
)COMMENT '學生表';
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 '課程名稱'
)COMMENT '課程表';
INSERT INTO course VALUES(NULL,'java'),(NULL,'PHP'),(NULL,'MySQL'),(NULL,'Hadoop');

# 學生課程關系表（中間表）
CREATE TABLE student_course(
	id INT auto_increment COMMENT '主鍵' PRIMARY KEY,
	student_id INT NOT NULL COMMENT '學生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 '學生——課程關系中間表';
INSERT INTO student_course VALUES(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

我們切換IDEA打開可以清晰的看到三個表之間的關系，中間信息表中的兩個外鍵：student_id與course_id將學生表和課程表關聯了起來

內連接查詢

查詢A集合與B集合的交集

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

示例：查詢所有員工與其所屬部門

-- 方式一
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;

外連接

右外連接：查詢右表所有數據以及兩表交集部分數據

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

左外連接：查詢左表所有數據以及兩表交集部分數據

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

示例：

-- 2. 查詢員工全部信息及其對應的部門信息 左外連接
select  e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id;
-- 3. 查詢全部部門和其對應的所有員工信息 右外連接
select d.*,e.emp_name from emp e right join dept d on e.emp_dept_id = d.dept_id;

自連接

當前表與自身的連接查詢，自連接必須使用別名

格式：

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

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

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

聯合查詢

關鍵字 union [all] 將兩條sql語句查詢的結果拼接起來

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

加上all表示不會去重，不加all表示去重復（即同時滿足兩條sql語句的只出現一次即可）

多張表的列數必須保持一致，字段類型也需要一致

子查詢

子查詢是指在SQL語句中嵌套select語句進行嵌套查詢

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

子查詢的外部語句可以是insert、uodate、delete、select的任何一個

標量子查詢示例：查詢‘ 開發部 ’的全部員工信息

首先你可以利用正常的兩條sql去查詢：

-- 查詢“開發部”的所有員工
-- 1. 兩條語句查詢
select dept_id from dept where dept_name = '開發部';
select emp_name from emp where emp_dept_id = 1;

或者你可以使用內連接方式進行聯查：

-- 內連接
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 = '開發部';

或者使用子查詢：

-- 使用子查詢
select * from emp where emp_dept_id = (select dept_id from dept where dept_name = '開發部');

讀到這里，這篇“MySQL多表聯查如何實現”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。