【无标题】

MySQL学习 小型公司数据库练习

create database `learn`;
show databases;
use learn;
set sql_safe_updates=0;


create table `employee`(
	`emp_id` int primary key,
    `name` varchar(20),
    `birth_data` date,
    `sex` varchar(1),
	`salary` int,
	`branch_id` int,
    `sup_id` int
);
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
);
-- 添加employee的外键 修改表格属性 
create table `client`(
	`client_id` int primary key,
    `client_name` varchar(20),
    `phone` varchar(20)
);
-- works_with 的primary key 同时也是 foreign key
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
);


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

--  因为employee的外键涉及到branch的主键  所以应该先将branch的表格建立 且为了解决同样的问题将 manager_id的值设为NULL
insert into `branch` values(1, '研发', NULL);
insert into `branch` values(2, '行政', NULL);
insert into `branch` values(3, '资讯', NULL);

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);

--  两个表格的基本信息填写完毕后 将 branch的manager_id 使用update 更新修改
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;

--  加入客户信息
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');

--  销售信息
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(209, 403, '24000');
insert into `works_with` values(210, 404, '87940');


select * from `employee`;

-- 练习 
--  1.取得所有员工资料
select * from `employee`;
--  2.取得所有客户资料
select * from `client`;
--  3.按薪水低到高取得员工资料
select * from `employee`
order by `salary`;
--  4.取得薪水前3高的员工   默认order by为 asc由低到高排列   desc有高到低排列
select * from `employee`
order by `salary` desc
limit 3;
--  5.取得所有员工的名字
select `name` from `employee`;