[TOC]
## 1：連接查詢基本介紹
將多張表(>=2)進行記錄的連接(按照某個指定的條件進行數據拼接)。
連接查詢的意義: 在用戶查看數據的時候,需要顯示的數據來自多張表.
連接查詢: join, 使用方式: 左表 join 右表；左表: 在join關鍵字左邊的表；右表: 在join關鍵字右邊的表
**連接查詢分類：** SQL中將連接查詢分成四類:?**內連接,外連接,自然連接和交叉連接**
**交叉連接：** 交叉連接: cross join, 從一張表中循環取出每一條記錄, 每條記錄都去另外一張表進行匹配: 匹配一定保留(沒有條件匹配), 而連接本身字段就會增加(保留),最終形成的結果叫做: 笛卡爾積。但是基本不會用到
## left join 是左連接
cmf_users 是主表，如果，從表（cmf_users_live）數據不夠則展示null on是兩張表的鏈接條件，意思是兩張表誰和誰關聯去匹配數據
>>>注意select 和from中間要展示的字段必須是表名點字段
```sql
SELECT
*
FROM
cmf_users
LEFT JOIN cmf_users_live ON cmf_users.id = cmf_users_live.uid
WHERE
cmf_users.id <= 1900;
```
## right?join 右連接
### 右連接是已cmf_users_live為主表，
```sql
SELECT
cmf_users.id,
cmf_users.coin,
cmf_users_live.uid,
.cmf_users_live.city
FROM
cmf_users
RIGHT JOIN cmf_users_live ON cmf_users.id = cmf_users_live.uid;
```
<br>
<br>
首先我們新建兩張相親表，兩表之間使用id連接
beauty表
```sql
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '?',
`borndate` datetime NULL DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`photo` blob NULL,
`boyfriend_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
```
**添加數據：**
```sql
INSERT INTO `beauty` VALUES (1, '柳巖', '女', '1988-02-03 00:00:00', '18209876577', NULL, 8);
INSERT INTO `beauty` VALUES (2, '蒼老師', '女', '1987-12-30 00:00:00', '18219876577', NULL, 9);
INSERT INTO `beauty` VALUES (3, 'Angelababy', '女', '1989-02-03 00:00:00', '18209876567', NULL, 3);
INSERT INTO `beauty` VALUES (4, '熱巴', '女', '1993-02-03 00:00:00', '18209876579', NULL, 2);
INSERT INTO `beauty` VALUES (5, '周冬雨', '女', '1992-02-03 00:00:00', '18209179577', NULL, 9);
INSERT INTO `beauty` VALUES (6, '周芷若', '女', '1988-02-03 00:00:00', '18209876577', NULL, 1);
INSERT INTO `beauty` VALUES (7, '岳靈珊', '女', '1987-12-30 00:00:00', '18219876577', NULL, 9);
INSERT INTO `beauty` VALUES (8, '小昭', '女', '1989-02-03 00:00:00', '18209876567', NULL, 1);
INSERT INTO `beauty` VALUES (9, '雙兒', '女', '1993-02-03 00:00:00', '18209876579', NULL, 9);
INSERT INTO `beauty` VALUES (10, '王語嫣', '女', '1992-02-03 00:00:00', '18209179577', NULL, 4);
INSERT INTO `beauty` VALUES (11, '夏雪', '女', '1993-02-03 00:00:00', '18209876579', NULL, 9);
INSERT INTO `beauty` VALUES (12, '趙敏', '女', '1992-02-03 00:00:00', '18209179577', NULL, 1);
```
<br>
<br>
boys表
```sql
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`userCP` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
```
**添加數據：**
```sql
INSERT INTO `boys` VALUES (1, '張無忌', 100);
INSERT INTO `boys` VALUES (2, '鹿晗', 800);
INSERT INTO `boys` VALUES (3, '黃曉', 50);
INSERT INTO `boys` VALUES (4, '段譽', 300);
```
<br>
<br>
### **簡單案例：**
#### 1：查看女星對應的男星
```sql
SELECT
`name`,
boyName
FROM
boys,
beauty
WHERE
beauty.id = boys.id;
```
<br>
<br>
#### 2：查詢員工部門對應的員工
```sql
SELECT
last_name,
dname
FROM
gin_employees,
gin_dept_bigdata
WHERE
gin_employees.`employee_id` = gin_dept_bigdata.`id`;
```
如果你有很多張表，則注意要在**select** 和**from** 查看的數據帶上表名例如
```sql
select one.name,tow.name
```
<br>
<br>
#### 3：查詢員工表中first_name字符中第二個是o的員工名，員工薪資，以及所屬部門id
```sql
SELECT
first_name,salary,
id
FROM
gin_employees AS users,
gin_dept_bigdata as department
WHERE
users.employee_id = department.id
AND first_name LIKE '_o%';
```
