MySQL关联数据表操作方式

1、准备工作（创建数据表）

create table `employee`(
`emp_id` int primary key,
`name` varchar(20),
`birth_date` date,
`sex` varchar(1),
`salary` int,
`branch_id` int,
`sup_id` int
);

create table `client`(
`client_id` int primary key,
`client_name` varchar(20),
`phone` varchar(20)
);

create table `work_with`(
`emp_id` int,
`client_id` int,
`total_sales` int,
primary key(`emp_id`, `client_id`),
foreign key(`emp_id`) references `employee`(`emp_id`) on delete cascade,
foreign key(`client_id`) references `client`(`client_id`) on delete cascade
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2、添加数据（包括关联表）
– relation insert

insert into `employee` values(206,'小黄','2000-10-08','F',50000,1,null);
insert into `employee` values(207,'小红','2001-10-08','M',29000,2,206);

insert into `client` values(400,'阿狗','22334455');
insert into `client` values(401,'阿猫','22334456');

insert into `work_with` values(206,400,'70000');
insert into `work_with` values(207,401,'24000');
1
2
3
4
5
6
7
8

3、关联查询

-- relation query
SELECT * FROM sql_tutorial.employee where emp_id=206;
select * from sql_tutorial.client where client_id in (select client_id from sql_tutorial.work_with where emp_id=206);
1
2
3

4、关联更新

-- relation update
update sql_tutorial.client 
inner join sql_tutorial.work_with on sql_tutorial.client.client_id=sql_tutorial.work_with.client_id 
set sql_tutorial.client.client_name="孙悟空" 
where sql_tutorial.work_with.emp_id=206;
1
2
3
4
5

5、关联删除

-- relation delete
delete sql_tutorial.employee, sql_tutorial.work_with, sql_tutorial.client
from sql_tutorial.employee 
left join sql_tutorial.work_with on sql_tutorial.employee.emp_id=sql_tutorial.work_with.emp_id
left join sql_tutorial.client on sql_tutorial.client.client_id=sql_tutorial.work_with.client_id
 where sql_tutorial.employee.emp_id =206;
1
2
3
4
5
6