MySQL微讲解(三)
自增特性
'auto_increment'专门配合主键一起使用，用户以后再添加数据的时候就不需要自己记忆主键值了
create table a1(
id int primary key auto_increment,
name varchar(255)
);
insert into a1(name) values('oscar'),('jason'),('tom');
select * from a1;
insert into a1(name) values('jerry'); # id = 4
delete from a1 where id = 4; # 删除id4
insert into a1(name) values('jerry'); # 再添加一遍，发现id=5，那是因为自增属性不会随着数据的删除而回退
delete from a1; # 可以删除表中所有的数据，但是不会重置主键值
truncate a1; # 删除表中数据并重置主键值
外键
1.外键简介
当我们在操作表格创建一个员工信息表的时候，表里面有编号、姓名、年龄、部门、工作等等，虽然在一张表我们是可以创建的，但是会有很多的缺陷，因为表的重点不清晰，既可以说是员工表，也可以说是部门表，如果我们想要用数据库来存储的话会浪费很多的存储空间，因为有很多重复的数据，而且这个员工表的扩展性还很差，改一个地方很多相同的都要改，耦合度太高，不利于维护。
所以我们就可以把表分为两份，一份是员工表，只存储员工的个人信息，另一份是部门表，存储员工所在的部门及工作等等，但是这样呢我们又面临了一个问题，员工表与部门表之间没有了绑定关系，所以我们就可以在员工表添加一个部门编号字段，与部门表中的主键对应，该字段就是'外键字段'，外键字段就是用来记录表与表之间数据的关系的。
外键字段的关系总共有四种，分别是一对多、多对多、一对一、没有关系，判断外键字段是什么关系，只需要记住口诀'换位思考'来判断外键关系。
2.一对多关系
我们以刚刚的员工表和部门表作为例子，来判断是什么外键关系。
我们站在员工表的角度发现一个员工不可以对应多个部门，站在部门表的角度发现一个部门是可以有多个员工的，所以外键关系为'一对多'，部门是'一'，员工'多'，这里要强调一下，关系表达只能用一对多，不能用多对一。'一对多的关系，外键字段在多的一方'。
我们现在知道外键关系了，我们来实际操作一下:
create table info(
id int primary ker auto_increment,
name varchar(255),
age int,
dep_id int,
foreign key(dep_id) references dep(id) # 外键的编写方式，但是需要注意的是必须先要有外键对应的表，才能设置外键，否则就会报错
);
create table dep(
id int primary key auto_increment,
dep_name varchar(255),
dep_desc varcahr(255)
);
insert into dep(dep_name,dep_desc) values('技术部','程序员'),('安保部','保安队长');
insert into info(name,age,dep_id) values('宋晓峰',35,2);
# foreign key的约束条件
1.创建表的时候应该先创建被关联的表
2.插入数据的时候应该先插入被关联表的数据，外键字段填入的值只能是被关联表中已经存在的值
3.删除、修改被关联表数据的时候会出现障碍
# 级联更新、级联删除
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
create table info1(
id int primary key auto_increment,
name varchar(255),
age int,
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
添加级联更新、级联删除之后，在修改被关联表的时候就没有障碍了。
'''在实际工作中外键可能也不会使用，以为外键会消耗额外的资源，并且会增加表的复杂度，表很多情况下我们也可以通过SQL语句的形式建立逻辑意义上的表关系'''
3.多对多关系
多对多我们来举个例子，以大桥和工程师为例，站在大桥表的角度，一座大桥可以对应很多工程师，站在工程是的角度，一个工程师也可以对应很多大桥，所以是'多对多关系'。
但是针对多对多关系，我们还需要单独开设第三张表来存储关系。
create table qiao( # 大桥表
id int primary key auto_increment,
name varchar(255)
);
create table gong( # 工程师表
id int primary key auto_increment,
name varchar(255),
gender enum('male','female','others')
);
create table gong_qiao( # 用来关联大桥表和工程师表，让两者建立起关系
id int primary key auto_increment,
gong_id int,
qiao_id int,
foreign key(gong_id) references gong(id)
on update cascade
on delete cascade,
foreign key(qiao_id) references qiao(id)
on update cascade
on delete cascade);
'''两张基础表内的数据没有在第三张表内绑定的情况下是可以随意修改的'''
4.一对一关系
一对一关系我们可以qq用户表来举例子，qq数据我们可以分为热数据(经常使用的数据)和冷数据(不经常使用的),然后我们创建两个表，一个是用户表，存储热数据，一个数用户详情表，用来存储冷数据。站在用户表的角度，一个用户数据只能对应一个用户详情数据，站在用户详情表，一个用户详情数据只能对应一个用户数据，所以是'一对一'的关系，针对一对一关系外键字段建立的地方，一般建议在查询频率较高的一方。
create table user_detail(
id int primary key auto_increment,
phone bigint,
age int);
create table user(
id int primary key auto_increment,
name varchar(255),
gendar enum('male','female','others'),
user_detail_id int unique,
foreign key(user_detail_id) references user_detail(id)
on update cascade
on delete cascade);
查询关键字
1.select与from
select用于指定查询字段
from用于指定查询表
select 字段名 from 表名;
2.where筛选
# 1.查询id大于3并且小于6的数据
select * from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 3.查询id小于3或者小于6的
select * from emp where not id >= 3 or not id <= 6;
select * from emp where id not between 3 and 6;
# 4.查询薪资不在20000,18000,17000的数据
select * from emp where salary not in (20000,18000,17000);
select * from emp where not salary = 20000 and not salary = 18000 and not salary = 17000;
# 5.查询岗位描述为空的数据
select * from emp where post_comment is null; # 针对null只能用is不能用等号
# 6.查询员工姓名中包含字母o的员工姓名和薪资
select * from emp where name like '%o%';
'''
查询条件不是很明确的情况下，我们一般统称为模糊查询
关键字:like # 开启模糊查询的关键字
关键符号:%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
'''
# 7.查询员工姓名是由四个字符组成的数据
select * from emp where name like '____';
select * from emp where char_length(name) = 4;
3.group by分组
分组就是按照指定的条件将单个单个的个体组织成一个个整体，分组的好处就在于可以快速的统计出某些数据。
# 1.如何分组
select * from emp group by post;
5.7及以上的版本默认自带 mysql5.7及以上版本默认自带sql_mode=only_full_group_by，该模式要求分组之后默认只可以直接获取分组的数据，不可以直接获取其他字段，原因就是分组的目的就是按照分组的条件来管理诸多数据的，最小单位应该是分组的数据，而不是单个单个的数据
# 2.聚合函数(专门用于分组之后的数据统计)
max:统计最大值
min:统计最小值
sum:统计求和
count:统计计数
avg:统计平均值
# 3.统计每个部门的最高薪资
select post,max(salary) from emp group by post;
# 4.统计每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 5.统计每个部门的员工人数
select post,count(id) from emp group by post;
# 6.统计每个部门的月工资开销
select post,sum(salary) from emp group by post;
# 7.统计每个部门最小的年龄数
select post,min(age) from emp group by post;
# 8.统计每个部门下所有员工的姓名
select post,group_concat(name) from emp group by post;
# 9.统计每个部门下所有的员工的姓名和年龄
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post; # 中间可以添加指定符号的
# 10.字段起别名
select post,group_concat(name) as '姓名' from emp group by post;
select id as '序号',name as '姓名' from emp;
'''as关键字也可以不写,但是语义不明确,建议加上'''
