MySQL数据库复杂的增删改查操作

在前面的文章中，我们主要学习了数据库的基础知识以及基本的增删改查的操作。接下去将以一个比较实际的公司数据库为例子，进行讲解一些较为复杂且现时需求的例子。

基础知识：

一文清晰梳理Mysql 数据库基础知识_字段变动如何梳理清楚-CSDN博客

该公司的数据库设计如下：

一、创建公司数据库表格

创建employee表格 

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

在这里并没有将branch_id和sup_id设计为外键，是应为其branch和employee表格还没有创建，所以之后会再返回来设计。

创建branch表格

create table branch(
	branch_id int primary key,
    branch_name varchar(20),
    manager_id int,
    foreign key (manager_id) references employee(emp_id) on delete set null
);

这里设置manager_id为外键，references关联上employee表格的emp_id属性，这里先不用知道为什么加上 on delete set null，之后会详细的说。

现在补上branch_id和sup_id为外键，因为现在branch和employee表格已经创建好了。

branch_id：

alter table employee 
add foreign key (branch_id) 
references branch(branch_id) 
on delete set null;

sup_id：

alter table employee 
add foreign key (sup_id) 
references employee(emp_id) 
on delete set null;

创建clint表格

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

创建works_with表格

create table `works_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
);

同样的这里先不用知道为什么加上 on delete cascade，之后会详细的说。

二、添加数据

branch数据：

添加数据的时候不能直接添加employee数据，因为在employee表格中外键还没添加数据，所以换种方式，就先添加branch表格数据，但是又由于branch表格中也有外键manager_id，所以再添加 的时候先设置为null，之后再加。

insert into branch values(1, '研发', null);
insert into branch values(2, '行政', null);
insert into branch values(3, '资讯', null);
select * from branch;

employee数据： 

insert into employee 
values(206, '小黄', '1998-10-08', 'F', 50000, 1, null);
insert into employee 
values(207, '小绿', '1985-09-16', 'M', 29000, 2, 206);
insert into employee 
values(208, '小黑', '2000-12-19', 'M', 35000, 3, 206);
insert into employee 
values(209, '小白', '1997-01-22', 'F', 39000, 3, 207);
insert into employee 
values(210, '小乐', '1925-11-10', 'F', 84000, 1, 207);
select * from employee;

现在再为branch表格添加上manager_id(branch表的外键)的值。

update branch
set manager_id = 206
where branch_id = 1;

update branch
set manager_id = 207
where branch_id = 2;

update branch
set manager_id = 208
where branch_id = 3;

client数据：

insert into `client` values(400, '阿狗', '254354335');
insert into `client` values(401, '阿猫', '25633899');
insert into `client` values(402, '旺来', '45354345');
insert into `client` values(403, '路西', '54354365');
insert into `client` values(404, '艾瑞克', '18783783');
select * from `client`;

works_with数据：

insert into `works_with` values(206, 400, '70000');
insert into `works_with` values(207, 401, '24000');
insert into `works_with` values(208, 402, '9800');
insert into `works_with` values(208, 403, '24000');
insert into `works_with` values(210, 404, '87940');
select * from `works_with`;

三、相应的简单操作

-- 1.取得所有员工的数据

select * from employee;

-- 2.取得所有客戶数据

select * from client;

-- 3.按薪水低到高取得员工数据

select * from employee 
order by salary

-- 4.取得薪水前3高的员工

select * from employee 
order by salary desc
limit 3;

-- 5.取得所有员工的名子

select `name` from employee;

假设这里想要取得有哪些性别，或者说有哪些名字得话，也就是不想让取出得数据重复得话，我们可以在要取得得属性前面加上distinct

四、聚合函数（aggregate functions）

聚合函数可以让我们更加方便得知道数据库得一些数据，举例如下。

--1.取得员工人数

select count(*) from employee;

--2.取得所有出生于1970-01-01 之后的女性员工人数

select count(*) from employee
where birth_date > '1970-01-01' and sex = 'F';

--3.取得所有员工的平均薪水

select avg(salary) from employee;

--4.取得所有员工薪水的总和

select sum(salary) from employee;

--5.取得薪水最高的员工

select max(salary) from employee;

--6.取得薪水最低的员工

select min(salary) from employee;

五、万用字元

% 表示多个字元，_ 表示代表一个字元。

--1.取得电话号码尾数是335的客戶

select * from `client` 
where phone like '%335';

--2.取得姓艾的客戶

select * from `client` 
where client_name like '艾%';

--3.取得生日在12月的员工

select * from `employee` 
where birth_date like '_____12%';

六、Union 

通过union操作，就可以将搜寻结果结合在一起。

-- 1.员工名字 union 客户名字

select `name` 
from employee
union 
select `client_name`
from `client`;

在使用union得时候，其属性得数量是要求一致的，不能属性数量不一致，否则就会导致出错。

-- 2.员工id +员工名子 union 客户id + 客户名字

select `emp_id`, `name` 
from employee
union 
select `client_id`, `client_name`
from `client`;

可以看见这样返回的结果其实是以第一个用了第一个选择的属性的，如果想改变其和并后的属性名字的话，使用以下代码就可以：

select `emp_id` as `total_id`, `name`as `total_name`
from employee
union 
select `client_id`, `client_name`
from `client`;

-- 3.员工薪水 union 销售金额

select `salary` 
from employee
union 
select `total_sales`
from `works_with`;

七、join 连接

可以帮助我们将两个表格连接在一起

-- 取得所有部门经理的名字

要想取得部门经理的名字，那我们就得先去找部门（branch）的表格，而branch表格中之后中只有manmager_id，并没有manager的名字，所以要通过manager_id找到对应的名字。

select *
from employee
join branch on manager_id = emp_id;

为什么只显示了三条数据，是因为再进行连接的时候是通过manager_id = emp_id进行连接的，所以只有206、207、208匹配上了。

现在不想显示那么多的数据，就可以通过以下代码来实现：
 

select emp_id, `name`, branch_name
from employee
join branch on manager_id = emp_id;

若在连接的时候，两个表格中的属性，有重复，比如假设branch表中的manager_id属性叫id，而employee表中emp_id也叫id的话，那再连接的时候应该改成`employee`.`emp_id` = `branch`.`brach_id`，这样指定好属性来自于哪个表格。

八、subquery 子查询

是表示在一个查询语句中可以使用另外一个查询语句。

-- 1.找出研发部门的经理名字

select `name` 
from employee
where emp_id = (
	select manager_id
	from branch
	where branch_name = '研发'
);

select manager_id
	from branch
	where branch_name = '研发'

首选通过子查询，查询到了研发部门的manager_id，然后再通过这个子查询的结果去查询该id在employee中对应的名称。
-- 2.找出对单一位客戶销售金额超过50000的员工名字

select `name` 
from employee
where emp_id in (
	select emp_id 
	from works_with
	where total_sales > 50000
);

九、解释on delete set null 和on delete cascade

on delete set null：当时在设置branch表格的时候，其中的外键manager_id是referneces到了employee表格的emp_id的，那假设employee中的id为206的员工离职了，那branch表格中的manager_id该怎么办呢？所以加上on delete set null表示在外键manager_id所referneces的employee表格中的emp_id被删除后，自动将manager_id设置为null。

测试：

delete from employee
where emp_id = 207;
select * from employee;

当207被删除后，其外键sup_id中是207的都被设置为了空（即209和210）同时branch表中的外键manager_id也被设置为了空。

on delete cascade：当时在设置works_with表格的时候，其中的外键emp_id是referneces到了employee表格的emp_id的，那假设employee中的id为206的员工离职了，那works_with表格中的emp_id该怎么办呢？这里加上on delete cascade表示在外键emp_id所referneces的employee表格中的emp_id被删除后，自动将works_with这行删除。注意：这里不能设置为on delete set null，因为works_with表格中的emp_id同时是主键和外键，主键不能为空，所以只能是被删除该记录而不能被设置为空null。

测试：

delete from employee
where emp_id = 207;
select * from works_with;

207那条记录被删除了！到这里为止。数据库的复习就到此结束了！

2025/3/9

慢慢来，21岁不过是一天当中的6点18分，是充满希望的早晨！