[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%';
```
