mysql连表

#建表
create table dep(
id int,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('大娃','male',18,200),
('二娃','female',48,201),
('三娃','male',38,201),
('四娃','female',28,202),
('五娃','male',18,200),
('六娃','female',18,204)
;


#查看表结构和数据
mysql> desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | 大娃 | male | 18 | 200 |
| 2 | 二娃 | female | 48 | 201 |
| 3 | 三娃 | male | 38 | 201 |
| 4 | 四娃 | female | 28 | 202 |
| 5 | 五娃 | male | 18 | 200 |
| 6 | 六娃 | female | 18 | 204 |
+----+------------+--------+------+--------+

dep表和emp表

 

模式：

SELECT 字段列表

FROM 表1

INNER|LEFT|RIGHT JOIN 表2

ON 表1.字段 = 表2.字段;

 

1、交叉连接（不适用任何匹配条件，会生成笛卡尔积）【这是所有查询的基础】

emp表中每条记录与dep表中所有记录都连接一次。

查询出来的结果是，俩张表的所有组合情况。

 

二、内连接（只连接匹配的行）

找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选正确的结果。

因此，笛卡尔积还是有点用处的，那就是为我们提供了所有可能的情况。

可以类比下图看：

select emp.id, emp.name, emp.age, emp.sex, dep.name from emp inner join dep on emp.dep_id = dep.id;

我们在SQL语句中可以看到 inner join，表示内连接。

注意：是找公共的部分，寻找条件是 emp.dep_id = dep.id

最终的结果为：

内连接的SQL语句可以简写为：

select emp.id, emp.name, emp.age, emp.sex, dep.name from emp, dep where emp.dep_id = dep.id;

此时去掉了 inner join，同时加上了where。

换句话说，如果显式使用了inner join，则需要用on；如果没有用inner join，则需要用where。

另外，mysql的连表查询默认是内连接，因为我们写连表查询语句时，一般都是用where语句。

如果我们的连表查询不想用内连接，则需要显式使用left join，right join，然后结合on。

 

三、外链接之左连接（优先显示左表的全部数据）

以左表为准，即找出所有员工信息，当然包括没有部门的员工。

本质就是：在内连接的基础上增加左边有右边没有的结果。

select emp.id, emp.name, dep.name from emp left join dep on emp.dep_id = dep.id;

这里使用的是 left join，即优先照顾左边的表，如果左边的记录没有满足 emp.dep_id = dep.id 条件，还是依然展示它们。

第6条原本是不匹配的，但由于left join的缘故，导致也显示了。

 

四、外链接之右连接（优先显式右表的全部数据）

以右表为准，即找出所有部门信息，包括没有员工的部门（即部门人员数为0）

本质就是：在内连接的基础上增加右边有左边没有的结果

select emp.id, emp.name, dep.name as depart_name from emp right join dep on emp.dep_id = dep.id;

 

五、全外链接（显式左右两个表的全部记录）

在内连接的基础上增加左边有右边没有的和右边有左边没有的结果。

注意：mysql不支持全外连接 full JOIN

强调：mysql可以使用 union 方式间接实现全外连接

注意 union与union all的区别：union会去掉相同的纪录。

 

inner join ... on ... where ...

----------------------- ------------

用于连表              用于查询

 

select emp.name,dep.name from emp inner join dep
    on emp.dep_id = dep.id
    where age > 25;

select emp.id,emp.name,emp.age,dep.name from emp,dep
    where emp.dep_id = dep.id
    and age > 25
    order by age asc;

 

六、子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。

2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

4：还可以包含比较运算符：= 、 !=、> 、<等