SQL创建公司数据库（使用聚合函数，万用字元，并集，连接，子查询展示数据）

create database SQL1;/*创建数据库*/
use SQL1;/*使用数据库*/
SET SQL_SAFE_UPDATES = 0; 

#创建表格 
create table Employee(
emp_id INT PRIMARY KEY,
name VARCHAR(20),
brith_date 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 #FOREIGN KEY表示外键；REFERENCES 表示外键接的表格名，再加外接的表格的primary key
); 

#对公司表格补上外键foreign key，
ALTER TABLE `Employee`
ADD FOREIGN KEY(`branch_id`) #加外键branch_id属性
REFERENCES `branch`(`branch_id`)#对应到branch表格的branch_id的属性
ON DELETE SET NULL;

ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`) #加外键sup_id
REFERENCES `employee`(`emp_id`)#对应到branch表格的branch_id的属性
ON DELETE SET NULL;

#创建客户表格
create table `Client`(
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);

#创建worh_with表格
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
);

#填写表格数据
#有foreign key时，对应的表没有相应资料时，直接输入会报错，要用NULL代替
INSERT INTO `branch` VALUES(1,'研发',NULL);
INSERT INTO `branch` VALUES(2,'行政',NULL);
INSERT INTO `branch` VALUES(3,'查询',NULL);
INSERT INTO `branch` VALUES(4,'查询',NULL);

INSERT INTO `employee` VALUES(206,'小黄','1998-10-08','F',50000,1,NULL);
INSERT INTO `employee` VALUES(207,'小绿','1995-10-09','M',51000,2,206);
INSERT INTO `employee` VALUES(208,'小灰','1993-11-24','M',23000,3,207);
INSERT INTO `employee` VALUES(209,'小黑','1996-10-08','M',36000,3,208);
INSERT INTO `employee` VALUES(210,'小红','1997-07-05','F',50000,1,209); 

#把原本branch的manager_id=NULL改回来
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,'阿狗','1234567');
INSERT INTO `client` VALUES(401,'阿猫','1673692');
INSERT INTO `client` VALUES(402,'来福','1894738');
INSERT INTO `client` VALUES(403,'路西','1746952');
INSERT INTO `client` VALUES(405,'杰克','9947538');

INSERT INTO `work_with` VALUES(206,400,70000);
INSERT INTO `work_with` VALUES(207,400,'56000');
INSERT INTO `work_with` VALUES(208,402,'35000');
INSERT INTO `work_with` VALUES(209,403,'54000');
INSERT INTO `work_with` VALUES(210,404,'64000'); 

select*from work_with;/*展示表*/

#聚合函数
select count(*) from Employee;/*获得员工数量*/
select avg(salary) from Employee;/*获得员工平均工资*/
select sum(salary) from Employee;/*获得员工工资总和*/
select max(salary) from Employee;/*获得最高员工工资*/
select min(salary) from Employee;/*获得最低员工工资*/


#万用字元
select *from Client
where phone like "%567";/*获取尾号为567的客户*/
select *from Client
where phone like "123%";/*获取开头为123的客户*/
select *from Client
where phone like "%345%";/*获取中间号码为345的客户*/
select *from Employee
where brith_date like "_____10%";/*获取生日月份为10月的员工，一个＿代表一个字元*/

#并集
select name
from Employee
union
select client_name
from Client;

#子查询
select name from Employee
where emp_id=(
select manager_id from Branch 
where branch_name="研发"
);/*找出研发部门经理名字*/

#连接
select *from Employee join  Branch on emp_id=manager_id;/*将表Employee和表Branch,符合emp_id=manager_id的列连接起来