在使用數據庫查詢語句時,單表的查詢有時候不能滿足項目的業務需求;在項目開發過程中,有很多需求都是要涉及到多表的連接查詢。
連接查詢:也可以叫跨表查詢,需要關聯多個表進行查詢。
以下通過兩表實例來詳細介紹連接的使用方式。
department表:
建表語句:
<code>DROP TABLE IF EXISTS `department`;CREATE TABLE `department` ( `id` int(11) NOT AUTO_INCREMENT, `deptName` varchar(30) DEFAULT , `address` varchar(40) DEFAULT , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;/<code>
表數據:
<code>INSERT INTO `department`VALUES ('1', '研發部(RD)', '2層'); INSERT INTO `department`VALUES ('2', '人事部(HR)', '3層'); INSERT INTO `department`VALUES ('3', '市場部(MK)', '4層'); INSERT INTO `department`VALUES ('4', '後勤部(MIS)', '5層'); INSERT INTO `department`VALUES ('5', '財務部(FD)', '6層');/<code>
employee表:
建表語句:
<code>DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` ( `id` int(11) NOT AUTO_INCREMENT, `name` varchar(20) DEFAULT , `dep_id` int(11) DEFAULT , `age` int(11) DEFAULT , `salary` decimal(10,2) DEFAULT , `cus_id` int(11) DEFAULT , PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;/<code>
表數據:
<code>INSERT INTO `employee`VALUES ('1', '魯班', '1', '10', '1000.00', '1');INSERT INTO `employee`VALUES ('2', '後裔', '1', '20', '2000.00', '1');INSERT INTO `employee`VALUES ('3', '孫尚香', '1', '20', '2500.00', '1');INSERT INTO `employee`VALUES ('4', '凱', '4', '20', '3000.00', '1');INSERT INTO `employee`VALUES ('5', '典韋', '4', '40', '3500.00', '2');INSERT INTO `employee`VALUES ('6', '貂蟬', '6', '20', '5000.00', '1');INSERT INTO `employee`VALUES ('7', '孫臏', '6', '50', '5000.00', '1');INSERT INTO `employee`VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');/<code>
下面進入正題!
01 內連接
圖示:
作用:查詢兩張表的共有部分
語句:
<code>Select from tableA AInner join tableB Bon A.Key = B.Key/<code>
原表數據:
示例:
<code>SELECT * from employee eINNER JOIN department don e.depart_id = d.id;/<code>
查詢結果數據:
02 左連接
圖示:
作用:把左邊表的內容全部查出,右邊表只查出滿足條件的記錄
語句:
<code>Select from tableA ALeft Join tableB Bon A.Key = B.Key/<code>
原表數據:
示例:
<code>SELECT * from employee eLEFT JOIN department don e.depart_id = d.id;/<code>
查詢結果數據:
03 右連接
圖示:
作用:把右邊表的內容全部查出,左邊表只查出滿足條件的記錄
語句:
<code>Select from tableA ALeft Join tableB Bon A.Key = B.Key/<code>
原表數據:
示例:
<code>SELECT * from employee eRIGHT JOIN department don e.depart_id = d.id;/<code>
查詢結果數據:
04 查詢左表獨有數據
圖示:
作用:查詢A的獨有數據
語句:
<code>Select from tableA ALeft Join tableB Bon A.Key = B.Key where B.key IS /<code>
原表數據:
示例:
<code>SELECT * from employee eLEFT JOIN department don e.depart_id = d.id WHERE d.id IS ; /<code>
查詢結果數據:
05 查詢右表獨有數據
圖示:
作用:查詢B的獨有數據
語句:
<code>Select from tableA ARight Join tableB Bon A.Key = B.Key where A.key IS /<code>
原表數據:
示例:
<code>SELECT * from employee eRIGHT JOIN department don e.depart_id = d.id WHERE e.id IS ;/<code>
查詢結果數據:
06 全連接
圖示:
作用:查詢兩個表的全部信息
語句:
<code>Select from tableA A Full Outter Join tableB B on A.Key = B.Key/<code>
注:Mysql默認不支持此種寫法;Oracle支持,在Mysql中可以使用UNION來實現相應操作。
原表數據:
示例:
<code>SELECT * from employee eLEFT JOIN department don e.depart_id = d.id UNION SELECT * from employee eRIGHT JOIN department don e.depart_id = d.id/<code>
查詢結果數據:
07 查詢左右表各自的獨有的數據
圖示:
作用:查詢A和B各自的獨有的數據
語句:
<code>Select from tableA A Full Outter Join tableB B on A.Key = B.Key where A.key = or B.key=/<code>
原表數據:
示例:
<code>SELECT * from employee eLEFT JOIN department don e.depart_id = d.id WHERE d.id is UNION SELECT * from employee eRIGHT JOIN department don e.depart_id = d.idWHERE e.depart_id is /<code>
查詢結果數據:
End.
作者:冰峰
來源:博客園
零基礎入職數據分析就業班
課程的形式主要是“直播+錄播”
報名專享:課程項目作業+1v1班主任監督學習+愛數據學院學員專屬網站+班級答疑群
課程結束後能熟練掌握SQL、Python、Excel、PPT等工具
適合人群:
1.轉行(崗位相關,專業相關、對數據分析感興趣)
2.從事數據分析工作,但是需要提升技能以及增加實戰經驗
3.應屆畢業生入職數據分析
聲明:本文來源於網絡,如有侵權,請聯繫我刪除!