MySQL多表聯(lián)查的實現(xiàn)思路
多表聯(lián)查場景
一對一
用戶與用戶信息表：當(dāng)用戶的信息數(shù)據(jù)過多時，我們可以將其分成兩個表分別對應(yīng)用戶基本信息和用戶的詳情信息。
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)
);
當(dāng)用戶信息過多時，就可以使用外鍵進行關(guān)聯(lián)。在任意一方加入外鍵，關(guān)聯(lián)另一方主鍵，并且設(shè)置外鍵為唯一的UNIQUE如下實現(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)
);
多對一
部門與員工：一個員工對應(yīng)一個部門，一個部門對應(yīng)多個員工
員工指向多的一方，部門指向一的一方。此時應(yīng)該在員工表中創(chuàng)建外鍵，指向部門表中的主鍵
# 員工表 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; -- 聯(lián)查 消除無效的笛卡爾積 select * from emp,dept where emp_dept_id = dept.dept_id;
多對多
學(xué)生與課程：一個學(xué)生可以選修多門課程，一門課程可以被多個學(xué)生選擇
此時我們應(yīng)該在學(xué)生表與課程表之間建立中間表。中間表包含兩個外鍵，分別對應(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é)號' )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 '課程名稱' )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打開可以清晰的看到三個表之間的關(guān)系，中間信息表中的兩個外鍵：student_id與course_id將學(xué)生表和課程表關(guān)聯(lián)了起來
內(nèi)連接查詢
查詢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;
外連接
右外連接：查詢右表所有數(shù)據(jù)以及兩表交集部分?jǐn)?shù)據(jù)
select 字段列表 from 表1 right outer join 表2 on 條件...;
左外連接：查詢左表所有數(shù)據(jù)以及兩表交集部分?jǐn)?shù)據(jù)
select 字段列表 from 表1 left outer join 表2 on 條件...;
示例：
-- 2. 查詢員工全部信息及其對應(yīng)的部門信息 左外連接 select e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id; -- 3. 查詢?nèi)坎块T和其對應(yīng)的所有員工信息 右外連接 select d.*,e.emp_name from emp e right join dept d on e.emp_dept_id = d.dept_id;
自連接
當(dāng)前表與自身的連接查詢，自連接必須使用別名
格式：
select 字段列表 from 表A 別名 join 表A 別名 on 條件...;
在員工表中，所有的普通員工、管理者都是員工。查詢每個員工歸屬哪個管理者管理就需要用到自連接
select a.name,b.name from emp a join emp b on a.emp_id = b.manager_id;
聯(lián)合查詢
關(guān)鍵字 union [all] 將兩條sql語句查詢的結(jié)果拼接起來
-- 查詢年齡大于50的員工 和薪資小于10000的員工 select * from emp where emp.emp_age > 50 union all select * from emp where emp.emp_salary < 10000;
加上all表示不會去重，不加all表示去重復(fù)（即同時滿足兩條sql語句的只出現(xiàn)一次即可）
多張表的列數(shù)必須保持一致，字段類型也需要一致
子查詢
子查詢是指在SQL語句中嵌套select語句進行嵌套查詢
select * from t1 where column1 =(select column1 from t2)；
子查詢的外部語句可以是insert、uodate、delete、select的任何一個
標(biāo)量子查詢示例：查詢‘ 開發(fā)部 ’的全部員工信息
首先你可以利用正常的兩條sql去查詢：
-- 查詢“開發(fā)部”的所有員工 -- 1. 兩條語句查詢 select dept_id from dept where dept_name = '開發(fā)部'; select emp_name from emp where emp_dept_id = 1;
或者你可以使用內(nèi)連接方式進行聯(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 = '開發(fā)部';
或者使用子查詢：
-- 使用子查詢 select * from emp where emp_dept_id = (select dept_id from dept where dept_name = '開發(fā)部');
到此這篇關(guān)于MySQL多表聯(lián)查的實現(xiàn)思路的文章就介紹到這了,更多相關(guān)MySQL多表聯(lián)查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL 5.7解壓版安裝、卸載及亂碼問題的圖文解決方法,本文分步驟給大家介紹的非常詳細，需要的朋友可以參考下2017-07-07
mysql如何將數(shù)據(jù)庫中的所有表結(jié)構(gòu)和數(shù)據(jù)導(dǎo)入到另一個庫
介紹了如何使用mysqldump命令備份和導(dǎo)入數(shù)據(jù)庫,以及創(chuàng)建目標(biāo)數(shù)據(jù)庫的步驟,首先使用mysqldump備份源數(shù)據(jù)庫,然后在目標(biāo)數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)庫,并將備份文件導(dǎo)入到目標(biāo)數(shù)據(jù)庫,確保數(shù)據(jù)結(jié)構(gòu)和內(nèi)容完整復(fù)制,提到了DataGrip、Navicat在導(dǎo)入導(dǎo)出過程中可能出現(xiàn)的問題2024-10-10
MVCC，即多版本并發(fā)控制(Multi-Version Concurrency Control)指的是，通過版本鏈維護一個數(shù)據(jù)的多個版本，使得讀寫操作沒有沖突，可保證不同事務(wù)讀寫、寫讀操作并發(fā)執(zhí)行，提高系統(tǒng)性能2021-06-06
這篇文章主要介紹了MySQL OOM（內(nèi)存溢出）的解決思路，文中講解非常細致，幫助大家在學(xué)習(xí)工作中解決內(nèi)存溢出的問題，感興趣的朋友可以了解下2020-08-08
mysql中復(fù)制表結(jié)構(gòu)的方法小結(jié)
這篇文章主要介紹了mysql中復(fù)制表結(jié)構(gòu)的方法,需要的朋友可以參考下2014-07-07
這篇文章主要介紹了淺談MySQL觸發(fā)器的原理以及使用,觸發(fā)器的執(zhí)行不需要使用 CALL 語句來調(diào)用，也不需要手工啟動，只要一個預(yù)定義的事件發(fā)生就會被 MySQL自動調(diào)用,需要的朋友可以參考下2023-05-05
這篇文章主要為大家詳細介紹了mysql 5.7.11 winx64初始密碼修改的方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
MYSQL性能優(yōu)化之分庫分表與不停機修改mysql表結(jié)構(gòu)，需要的朋友可以參考下2012-02-02
Mysql字符串截取及獲取指定字符串中的數(shù)據(jù)
小編童鞋最近接了一個新需求，需要在MySql的字段中截取一段字符串中的特定字符，下面小編把我的核心代碼分享給大家，對mysql 字符串截取相關(guān)知識感興趣的朋友一起看看吧2019-11-11
MySQL中可為空的字段設(shè)置為NULL還是NOT NULL
今天小編就為大家分享一篇關(guān)于MySQL中可為空的字段設(shè)置為NULL還是NOT NULL，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03