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
);
##这里为什么没有先设定foreign key呢，因为branch_id对应到branch表，此时branch表还没有创建，因此无法定义branch表的外键。
##而sup_id对于到当前employee这张表格，而此时employee表还没创建完成因此无法定义sup_id为employee表的外键。
##待到后面把相应的表格创建好后，再进行外键的定义。

2.创建部门表

create table `branch`(
	branch_id int,
    primary key(`branch_id`),
    branch_name varchar(20),
    manager_id int,
    foreign key (`manager_id`) references `employee`(`emp_id`) on delete set null
);
##这里可以直接将manager_id定义为employee表的emp_id的外键，因为employee表已经建好。

3.建好部门表以后，可以定义employee表未定义的外键。

 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;

4.创建客户表


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

5.创建work_with表

 ##这里比较特殊，有两个主键和外键，分别是emp_id和client_id,他们分别是work_with表的主键，也是employee表和client表的外键。
 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
 );

6.把所有表格的数据写入

insert into employee 
values(206,'小黄','1998-10-08','F','50000',NULL,NULL);
insert into employee 
values(207,'小黄','1985-09-16','M','29000',NULL,NULL);
insert into employee 
values(208,'小黄','2000-12-19','M','35000',NULL,NULL);
insert into employee 
values(209,'小黄','1997-01-22','F','39000',NULL,NULL);
insert into employee 
values(210,'小黄','1925-11-10','F','84000',NULL,NULL);
##由于branch_id是一个外键，他对应到branch表的branch_id，而此时branch表还没有数据，无法对应，执行时会报错。
##那么尝试先为branch表写入数据，也会发现branch表的manager_id也是外键，对应到employee表的emp_id，此时写入数据也会保错。
##因此，可以在写入employee表数据时，branch_id的值写为NULL，再正常写入其他表格数据，最后再回来重新给branch_id写入数据。
##或者先写入branch表数据，manager_id的值写为NULL，最后再回来写入属性。

##写入branch表数据
insert into `branch` 
values(1,'研发',206);
insert into `branch` 
values(2,'行政',207);
insert into `branch` 
values(3,'实训',208);

##修改employee表数据（以下为范例，其余省略。）
 update `employee` 
 set `branch_id` = 1
 where `emp_id` = 207 or `emp_id` = 210;

 update `employee` 
 set `sup_id` = 206
 where `emp_id` = 207 or `emp_id` = 208;

##写入client表和work_with表数据，略。

7.aggregate function 聚合函数。

COUNT,AVG,SUM,MIN


##查询员工人数
SELECT COUNT(*) FROM `employee`; 

##查询上级数量。
select COUNT(sup_id) FROM `employee`;

##查询所有出生于1970-01-01之后的女性员工的人数。
SELECT COUNT(*) FROM `employee` 
WHERE `birth_date` > '1970-01-01' 
AND `sex` = 'F';

##查询所有员工的平均薪水
SELECT avg(`salary`) from `employee`; 

##查询所有员工薪水总和
SELECT sum(`salary`) from `employee`;

##查询最高、最低工资
SELECT max(`salary`) from `employee`;

SELECT min(`salary`) 
from `employee`;

8.万用字元 %代表多个字元， _代表一个字元

##查询电话号码位数是355的客户
SELECT * FROM `client` 
WHERE `phone_num` 
LIKE '%355';

##查询电话号码开头是254的客户的数据。
SELECT * FROM `client` 
WHERE `phone_num` 
LIKE '254%';

##查询包含电话号码包含254的客户数据。
SELECT * FROM `client` 
WHERE `phone_num` 
LIKE '%254%';

##查询姓艾的客户数据。
SELECT * FROM `client` 
WHERE `client_name` 
LIKE '艾%';

##查询生日在12月的员工数据。
SELECT * FROM `employee`
WHERE `birth_date`
LIKE '_____12%';  ##12前面5个底线，因为日期的格式是xxxx-xx-xx，这里因为月份前面的字元个数是5个，因此用5个底线来代替。

9.union 联集。union可以把两个或多个查询的结果合并在一起。

##把员工名字和客户名字合并在一起。
SELECT `name`
FROM `employee`
UNION
SELECT `client_name`
FROM `client`;

##把员工名字和客户名字和部门名字合并在一起。
SELECT `name`
FROM `employee`
UNION
SELECT `client_name`
FROM `client`
UNION
SELECT `branch_name`
FROM `branch`;

##UNION的用法有讲究，合并的数据的数据类型要一致，合并的属性数量要相同。
##输出结果的属性名称为第一个查询的属性名称。

##把员工名字+id和客户名字+id合并在一起。
SELECT `name`,`emp_id`
FROM `employee`
UNION
SELECT `client_name`,`client_id`
FROM `client`;

##如果要指定输出结果的属性名称则可以使用AS
SELECT `name` AS `name2`,`emp_id` AS `emp_id2`
FROM `employee`
UNION
SELECT `client_name`,`client_id`
FROM `client`;

10.Join 连接，可以把两个表格连接在一起（join，left join, right join）

##获取所有部门经理的数据。
select * FROM `employee`
join `branch`
on `emp_id` = `manager_id`;

##获取所有部门经理的名字和id和薪水。
select `name`,`manager_id`,`emp_id`,`salary`
FROM `employee`
join `branch`
on `emp_id` = `manager_id`;

##获取所有部门经理的名字和id和薪水。
select `name`,`emp_id`,`manager_id`,`salary`
FROM `employee`
join `branch`
on `employee`.`emp_id` =  `branch`.`manager_id`;

##LEFT JOIN 功能是左边的表格不管条件成不成立都会输出表格，而右边表格条件成立则输出数据，不成立的部分输出NULL。
##区分左右的方法是，在join左边的就是左边的表格，在join右边就是右边的表格。
select `name`,`manager_id`,`emp_id`,`salary`
FROM `employee` left join `branch`
on `emp_id` = `manager_id`;

输出结果如下

##RIGHT JOIN的功能与LEFT JOIN相反。

##当属性出现重复时，比如当初的branch_name也叫做name时，就会和员工表的name重复，那么当把员工表和部门表做连接的时候，就会有两个name属性，那么就需要区分它们。
##区分重复的属性的办法就是加上表格的名字用“.”隔开。

##先把branch_name改为name，语法为:
alter table `table_name` rename column `old_column_name` to `new_column_name`;

#此时员工表和部门表都有同样的name属性，执行会报错。
alter table branch rename column `branch_name` to `name`;
select `name`,`emp_id`,`manager_id`,`name`
FROM `employee`
right join `branch`
on `employee`.`emp_id` =  `branch`.`manager_id`;
##报错提示：Error Code: 1052. Column 'name' in field list is ambiguous

##区分两个name的方法时在前面加上表格名并用“.”隔开。
select `employee`.`name`,`emp_id`,`manager_id`,`branch`.`name`
FROM `employee`
right join `branch`
on `employee`.`emp_id` =  `branch`.`manager_id`;

输出结果如下：

11.subquery 子查询

可以在一个查询语句里面，使用另外一个查询语句的结果

##找出研发部门经理的数据
select `name` 
from `employee` 
where `emp_id` = (
	select `manager_id` 
    FROM `branch` 
    where `name` = '研发'
);

##找出对单一位客户销售金额超过50000的员工名字
select `employee`.`name`
from `employee`
where   `emp_id` in (
	select `emp_id`
    from `work_with`
    where `total_sales`
    >50000
);
##问题：in和“=”的区别。

12.on delete set null和on delete cascade

在之前创建表格设定foreign key的时候，进行过on delete的约束。

on delete set null ,当数据对应不到设为null。

on delete cascade:当数据对应不到，删除对应的一行数据。


foreign key (`sup_id`) references `employee`(`emp_id`) on delete set null;
##当员工表某员工数据删除时，对应的sup_id会设定为null。比如小绿的上级是小黄，小黄离职了，数据需要删除，删除后，则小绿的sup_id=NULL

foreign key(emp_id) references employee(emp_id) on delete cascade；
##这是创建work_with表的语句，当小黄离职了，员工表的小黄数据需要删除，删除后，work_with表对应小黄的一行数据也会被删除。

#删除实验，删除小绿数据，查看对应的外键的表格数据。
delete * from
`employee` 
where `name` = '小绿';
select * from `branch`;
#查看结果，发现部门表对应小绿的manager_id值变为NULL。
select * from `work_with`;
#查看结果，发现work_with对应的小绿的一整行数据被删除。

当某个字段同时是主键和外键时，就不能设置on delete net null的约束，因为主键的值不能为空。可以设置为on delete cascade。

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31