网站底部放什么,做的好看的国内网站欣赏,u网站建设,在谷歌上怎么做网页在前面的文章中#xff0c;我们主要学习了数据库的基础知识以及基本的增删改查的操作。接下去将以一个比较实际的公司数据库为例子#xff0c;进行讲解一些较为复杂且现时需求的例子。
基础知识#xff1a;
一文清晰梳理Mysql 数据库基础知识_字段变动如何梳理清楚-CSDN博…在前面的文章中我们主要学习了数据库的基础知识以及基本的增删改查的操作。接下去将以一个比较实际的公司数据库为例子进行讲解一些较为复杂且现时需求的例子。
基础知识
一文清晰梳理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, nameas 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_idfrom branchwhere branch_name 研发
); select manager_idfrom branchwhere branch_name 研发
首选通过子查询查询到了研发部门的manager_id然后再通过这个子查询的结果去查询该id在employee中对应的名称。 -- 2.找出对单一位客戶销售金额超过50000的员工名字
select name
from employee
where emp_id in (select emp_id from works_withwhere 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分是充满希望的早晨
