MySQL数据库03

自增特性

create table t1(
    id int primary key auto_increment,
  name varchar(32)
);


insert into t1(name) values('jason'),('kevin'),('tony');
insert into t1(name) values('oscar');  # id=4

delete from t1 where id=4;
insert into t1(name) values('oscar');  # id=5

自增不会随着数据的删除而回退！！！

delete from t1;  # 删除数据但无法重置主键
insert into t1(name) values('jason'),('kevin'),('tony');
truncate t1;  # 删除数据并重置主键值
insert into t1(name) values('jason'),('kevin'),('tony');

外键引入

'''
    创建一张员工表
      id name age dep_name dep_desc
  缺陷:
      1.表的重点不清晰(可以忽略) 
           既可以说是员工表也可以说是部门表
        2.表中某些字段对应的数据一直在重复(可以忽略)
           浪费存储空间
        3.表的扩展性极差 牵一发而动全身(不能忽略)
           耦合度太高 不利于维护
  解决:
      将一张表一分为二
    员工表                        部门表
    id    name age                id   dep_name  dep_desc
'''
'''
拆分之后，员工与部门之间没有了绑定关系；
在员工表中添加一个部门编号字段与部门表中的主键字段对应；
这个字段就是外键，因此引入了外键的概念
'''

社会中存储需要可以构建成表的数据, 它们形成的表，往往之间存储某种或某些社会关系，mysql数据库建立表结构就是社会中产生的各种数据,，分门别类管理但mysql建立的(代码层次的)表之间,，同样需要处理表与表之间的关系，形成了 多对一 、多对多 、一对一的关系。

外键字段就是用来记录表与表之间数据的关系。

外键关系

外键关系总共分为三种：一对多；多对多；一对一。

note：关系表达只能用一对多，不能用多对一。

外键关系的判断方法>>>:换位思考，即：思考两个表格数据之间的''【一对多】''的对应关系。

• 一对多>>>:外键字段建在"多"的一方

一个员工对应一个部门，但是一个部门可以对应多个员工。

create table emp(
    id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

在创建表字段的时候也可以给每个字段添加中文注释>>>:comment '中文注释'

外键关键字：

foreign key(dep_id) references dep(id)

foreign key的约束效果：

1. 创建表的时候，应该先创建被关联表(没有外键字段的表)
2. 插入数据的时候，应该先插入被关联表(没有外键字段的表)；外键字段填入的值只能是被关联表中已经存在的值
3. 修改、删除被关联表数据都会出现障碍（是不被允许的）

级联更新和级联删除：

要想打破这种约束效果，实现表数据之间的联动，需要使用级联更新和级联删除方法。

create table emp1(
    id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep1(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
);
create table dep1(
    id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

ps：

在实际工作环境中，外键也可能不会使用，因为外键会消耗额外的资源，并且会增加表的复杂度；
表很多的情况下，我们也可以通过SQL语句的形式建立逻辑意义上的表关系。

• 多对多>>>:外键字段重新建立一个存储表关系的表格数据

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price float(10,2)
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others')
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id) 
    on update cascade   # 级联更新
    on delete cascade,  # 级联删除
    foreign key(book_id) references book(id) 
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

ps：

两张基表内的数据没有在第三张表内绑定关系的情况下，是可以随意进行增、改、删等操作的。

• 一对一>>>:外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的基表那一方

create table User(
  id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others'),
  user_detail_id int unique,  # 好好体会为什么加unique
  foreign key(user_detail_id) references UserDetail(id) 
  on update cascade   # 级联更新
  on delete cascade,  # 级联删除
);
create table UserDetail(
  id int primary key auto_increment,
  phone bigint,
  age int
);

 表查询关键字

• 表查询关键字之select与from

select用于指定查询的字段【自定义查询表中字段对应的数据】；from用于指定查询的表【指定操作的对象(到底是哪张表，也可能是多张)】

eg：select id,name from user;

PS：查询关键字其实有先后执行顺序的。【先执行from确定表，再执行查询字段】

• 表查询关键字之where筛选

• 查询id大于等于3，小于等于6的数据

方法1：select * from emp where id>=3 and id<=6;
方法2：select * from emp where id between 3 and 6;

• 查询薪资是20000或者18000或者17000的数据

方法1：select * from emp where salary=20000 or salary=18000 or salary=17000;

方法2：select * from emp where salary in (20000,18000,17000);

• 查询id小于3或者大于6的数据

使用逻辑判断not来实现：select * from emp where id not between 3 and 6;

• 查询薪资不在20000,18000,17000的数据

使用成员运算in来实现：select * from emp where salary not in (20000,18000,17000);

• 查询岗位描述为空的数据

select * from emp where post_comment=null;   报错

select * from emp where post_comment is null;   不报错

note：针对null只能用is不能用等号！！！

• 查询员工姓名中包含字母o的员工姓名和薪资

查询条件如果不是很明确的情况下，我们统一称之为【模糊查询】

模糊查询关键字：like>>>:开启模糊查询的关键字

模糊查询的关键符号：%：匹配任意个数的任意字符；_:匹配单个个数的任意字符

eg：select name,salary from emp where name like '%o%';

• 查询员工姓名是由四个字符组成的数据

方法1：select * from emp where name like '____'; 【查询名字有四位的，有几位加几个下划线】

方法2：select * from emp where char_length(name)=4;

• 表查询关键字之group by分组

什么是分组：

按照指定的条件将单个单个的个体组织成一个个整体。eg：按照性别分组、按照部门分组、按照年龄分组

分组的好处在于可以快速统计出某些数据。eg：获取最大薪资、平均年龄、最小年龄、总人数

分组>>>:select * from emp group by post;    按照部门（post）分组

聚合函数：专门用于分组之后的数据统计。

1. max>>>:统计最大值
2. min>>>:统计最小值
3. sum>>>:统计求和
4. count>>>:统计计数
5. avg>>>:统计平均值

关于是否需要分组，我们可以在题目或者需求中发现：

• 统计每个部门的最高薪资

select post,max(salary) from emp group by post;

• 统计每个部门的平均薪资

select post,avg(salary) from emp group by post;

• 统计每个部门的员工人数

select post,count(id) from emp group by post;

• 统计每个部门的月工资开销

select post,sum(salary) from emp group by post;

• 统计每个部门最小的年龄数

select post,min(age) from emp group by post;

间接获取分组以外其他字段的数据：获取分组的内部个体数据>>>group_concat

• 统计每个部门下所有员工的姓名

select post,group_concat(name) from emp group by post;

• 统计每个部门下所有员工的姓名和年龄

select post,group_concat(name,age) from emp group by post;

select post,group_concat(name,'|',age) from emp group by post;

字段起别名：

select post,group_concat(name) as '姓名' from emp group by post; 

select id as '序号',name as '姓名' from emp;

note：as关键字也可以不写，但是语义不明确，建议加上
select id '序号',name '姓名' from emp;