MySQL多表聯查的實現思路

2023-02-24 06:03:11

多表聯查場景

一對一

使用者與使用者資訊表：當用戶的資訊資料過多時，我們可以將其分成兩個表分別對應使用者基本資訊和使用者的詳情資訊。

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多表聯查的實現思路的文章就介紹到這了,更多相關MySQL多表聯查內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL多表聯查的實現思路

目錄

多表聯查場景

一對一

多對一

多對多

內連線查詢

外連線

自連線

聯合查詢

子查詢

熱門文章