mysql表的连接

建表前准备

#建表
#部门表
create table department(
id int,
name varchar(20) 
);

#员工表，之前我们学过foreign key，强行加上约束关联，但是我下面这个表并没有直接加foreign key，这两个表我只是让它们在逻辑意义上有关系，并没有加foreign key来强制两表建立关系，为什么要这样搞，是有些效果要给大家演示一下
#所以，这两个表是不是先建立哪个表都行啊，如果有foreign key的话，是不是就需要注意表建立的顺序了。那我们来建表。
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#给两个表插入一些数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'); #注意这一条数据，在下面的员工表里面没有对应这个部门的数据

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204) #注意这条数据的dep_id字段的值，这个204，在上面的部门表里面也没有对应的部门id。所以两者都含有一条双方没有涉及到的数据，这都是为了演示一下效果设计的昂
;


#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | yes | | null | |
| name | varchar(20) | yes | | null | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| 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 department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

1.笛卡尔积:将两表所有的数据一一对应,生成一张大表

select * from dep,emp;  #两个表拼一起
select * from dep,emp where dep.id = emp.dep_id; #找到两表之间对应的关系记录
select * from dep,emp where dep.id = emp.dep_id and dep.name='技术'; #筛选部门名称为技术的大表中的记录
select emp.name from dep,emp where dep.id = emp.dep_id and dep.name='技术'; #拿到筛选后的记录的员工姓名字段数据

2.连表查询

1.inner join 内连接

第一步:连表
    select * from dep inner join emp on dep.id=emp.dep_id;
第二步: 过滤
    select * from dep inner join emp on dep.id=emp.dep_id where dep.name='技术';
第三步:找对应字段数据
    select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name='技术';

2.left join 左连接(left join左边的表为主表,主表记录必须全部显示,辅表没办法对应上的,就通过null来补全)

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

3.right join 右连接

select * from dep right join emp on dep.id=emp.dep_id;

4.union 全连接

mysql> select * from dep left join emp on dep.id=emp.dep_id
    -> union
    -> select * from dep right join emp on dep.id=emp.dep_id;

5.子查询:(一个查询结果集作为另一个查询的条件)

select name from emp where dep_id = (select id from dep where name = '技术');