mysql 数据库自增序列号 mysql数据库设置自增语句

概要

自增特性

约束条件之外键

外键的作用主要是用来表示表与表之间关系的 是关系型数据库最重要的特征

查询关键字

select\from\where\group
by\having\distinct\order by\limit\regexp

多表查询思路

实际工作中 很多时候所需要的数据是来源于多张表作用在一起结果集合的拼接

内容

1、auto_increment 自增特性

# 1.首先先创建一个表t1
create table t1(
	id int primary key auto_increment,
	name vachar(32)
);
# 2.往t1表内添加数据
insert into t1(name) values('jason'),('kevin'),('tony');# 往表内已增加了三个数据id编号为1,2,3
insert into t1(name) values('oscar');  # id=4

delete from t1 where id=4;  # 这个时候我们这里将id编号为4的数据从表内删除 表内已不存在id编号4的数据

# 然后再往表内重新添加一个新的数据
insert into t1(name) values(oscar);  # 结果表内id编号直接会使用5 而不是id编号为4
"""所以我们不难推出结论:自增不会随着数据的删除而回退原来的编号id 而是使用过的编号id不会再继续使用 表会自动往下排号 主要是用于告知用户这个空缺的标号id已使用过而且已被删除过"""
delete from t1;  # 就算删除数据也无法重置主键
insert into t1(name) values('jason'),('kevin'),('tony');
truncate t1;  # 删除数据并重置主键值
insert into t1(name) values('jason'),('kevin'),('tony');

2、外键简介

# 外键前戏
首先先创建一张员工表(里面含有部门 员工)
id name age dep_name dep_desc
这样一张表存在的缺陷主要有:
    1.表的重点不清晰
    就是这张表我们即可以说是员工表 也可以说是部门表
    2.表中某些字段对应的数据一直在重复 比如:部门1 财务部 财务计算 可能有多个员工 那就需要在表内写多行 所以重复可能就会存在浪费存储空间的问题
    3.这张表的扩展性极差 数据扩大至千万级别时会造成一个牵一发而动全身的局面 所以会影响效率 也会造成耦合度太高 不利于维护
    
针对上述问题 解决办法:
    将一张表一分为二
    员工表               部门表
    id name age          id 
	dep_name             dep_desc
    # 将表拆分为两张表会上述的三个缺陷会得到解决
    但是也会迎来了一个致命的问题>>>>>> 那就是员工与部门这两张表之间没有的绑定的关系
    针对这个问题解决的思路: 我们在想两张表内我们要保持两张表的保持相同的动态变化(你删除 我也跟着删除 你变化里面的数据 我也跟着变化)
     所以得到解决的办法:在员工表中添加一个部门编号字段与部门表中的主键字段动态变化对应即可
      """
      该字段其实就是外键字段 
      	外键字段就是用来记录表与表之间数据的关系"""

3、外键关系

针对外键关系 总共可以分为四种关系
1.一对多
2.多对多
3.一对一
4.没有关系(不存在关系)
对于这四种关系的判断 记住四字口诀即可:换位思考
比如:
# 一对多
1.以员工表与部门表为例
	先站在员工表的角度
    问:一个员工能否对应多个部门  答案肯定是不能
    再站在部门表的角度
    问:一个部门能否对应多个员工  答案肯定是可以
    得出结论:换位思考后得出的答案是一个可以一个不可以 所以关系是"一对多" 部门是'一' 员工是'多'
    """需要注意的是关系表达只能是一对多 不能用多对一 因为关系中不存在多对一的关系定义"""
    针对一对多的关系 外键字段建立在"多"的一方(员工表)

4、外键SQL语句

"""
如果表中有外键字段 那么建议你先编写普通字段 最后在考虑外键字段
"""
# 在创建表字段的时候也可以给每个字段添加中文注释
create table emp(
	id int primary key auto_increment comment '编号'，
	name varchar(32) commeent '姓名',
	age int comment '年龄',
	dep_id int comment '部门编号',
	foreign key(dep_id) reference dep(id)
);  # 这句是外键关键字段 将两个表链接在一起
	create table dep(
    	id int primary key auto_increment comment '编号',
    	dep_name varchar(32) comment '部门名称',
    	dep_desc varchar(32) comment '部门描述');
    
    # foreign key的约束效果
    1.创建表的时候 应该先创建被关联表(没有外键字段的表)
    2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
    	外键字段填入的值只能是被关联表中已经存在的值
    3.修改、删除被关联表数据都会出现障碍
    	update dep set id=200 where id=2;
        delete from dep where id=2;
        
  # 级联更新 级联删除
	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 '部门描述');
    """在实际工作中外键也可能不会使用 因为外键会消耗额外的资源 并且会增加表的复杂度
    表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系"""

5、多对多关系

以图书与作者为例
1.先站在图书表的角度
问:一本书籍能否对应多名作者
答:可以
2.再站在作者表的角度
问:一名作者能否对应多本书籍
答:可以
结论:换位思考后两边都可以 那么就是"多对多"关系
    create table book(
    	id int primary key auto_increment,
    	title varchar(32),
    	price float(10,2),
    	author_id int,
    	foreign key(author_id) references author(id)
    	on update cascade # 级联更新
    	on delete cascade # 级联删除
    );
    create table author(
    	id int primary key auto_increment,
    	name varchar(32),
    	gender enum('male','female','others'),
    	book_id int,
    	foreign key(book_id) references book(id)
    	on update cascade # 级联更新
    	on delete cascade # 级联删除
    );
    """针对多对多关系 需要单独开设第三张表专门存储关系"""
    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','other')
        );
        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 # 级联删除
   );
        
 # 针对多对多表关系
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

6、一对一关系

针对qq用户表 其实里面的数据可以分成两类
热数据:经常需要使用的数据
   	  eg:在qq软件中的qq号码 座右铭数据 个人简介 爱好数据 等经常需要用到
冷数据:不怎么经常需要使用的数据
    eg:比如qq软件里面的具体信息 需要我们我们点击进去才能看得到的数据 比如有邮箱数据 电话数据 学校数据
将数据分为热数据与冷数据的目的是:为了节省资源并降低数据库压力 会将表一分为二
  一个是用户表:
    存使用频率较高的数据字段
 另外一个是用户详情表:
    主要存使用频率较低的数据字段
    
1.先站在用户表的角度
	问:一个用户数据能否对应多个用户详情数据
    答:不可以
2.再站在用户详情表的角度
	问:一个用户详情表数据能否对应多个用户数据
    答: 不可以
结论:换位思考后两边都不可以 那么关系可能有两种 
    '没有关系': 这个我们大致看一下就可以判断出来
    '一对一关系':
        针对一对一关系外键字段建立在任意一方都可以 但是推荐在查询频率比较高的较好的一方
        
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
    );

7、表查询关键字

# 数据准备 
create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	sex enum('male','female') not null default 'male',  # 大部分是男的
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int,  # 一个部门一个屋子
    depart_id int
);

# 插入记录
# 三个部门:教学,销售,运营
insert into
emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);')

8、查询关键字之select与from

select用于指定查询的字段
from用于指定查询的表
select id,name from mysql.user;
"""查询关键字其实有先后执行顺序 但是无需过多在意！！！"""

9、查询关键字之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= or salary=18000 or salary=17000;
select*from emp where salary in (20000,18000,17000);
# 3.查询id小于3或者大于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);
# 5.查询岗位描述为空的数据
	select * from emp where post_comment=null;
# 不可以
'''针对null只能用is不能用等号'''
	select *from emp where post_comment is null;  # 可以
# 6.查询员工姓名中包含字母o的员工姓名和薪资
"""
	查询条件如果不是很明确的情况下 我们统一称之为'模糊查询'
	关键字 				
			like:开启模糊查询的关键字
	关键符号
			%:匹配任意个数的任意字符	
			_:匹配单个个数的任意字符
	"""
  select name,salary from emp where name like '%o%';
# 7.查询员工姓名是由四个字符组成的数据
	select * from emp where name like '____';
  select * from emp where char_length(name)=4;

10、查询关键字之group by分组

# 什么是分组？
按照指定的条件将单个单个的个体组织成一个个整体
eg:按照性别分组 按照部门分组 按照年龄分组 按照国家分组
# 为什么需要分组？
	分组的好处在于可以快速统计出某些数据
    eg:最大薪资 平均年龄 最小年龄 总人数
# 如何分组
	"""按照部分分组"""
    select * from emp group by post;
    
    mysql5.7及以上版本默认自带sql_mode=only_full_group_by
  该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段
  
  原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
  
  如果是MySQL5.6及以下版本 需要自己手动添加
  """
# 聚合函数
	专门用于分组之后的数据统计
  	max				统计最大值
    min				统计最小值
    sum				统计求和
    count			统计计数
    avg				统计平均值
  
ps:是否需要分组 我们可以在题目或者需求中发现
# 1.统计每个部门的最高薪资
  select post,max(salary) from emp group by post;
# 2.统计每个部门的平均薪资
	select post,avg(salary) from emp group by post;
# 3.统计每个部门的员工人数
	select post,count(id) from emp group by post;
# 4.统计每个部门的月工资开销
	select post,sum(salary) from emp group by post;
# 5.统计每个部门最小的年龄数
	select post,min(age) from emp group by post;
 
"""间接获取分组以外其他字段的数据"""
# 1.统计每个部门下所有员工的姓名
	select post,group_concat(name) from emp group by post;
# 2.统计每个部门下所有员工的姓名和年龄
	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;
  # as关键字也可以不写 但是语义不明确 建议加上
  select id '序号',name '姓名' from emp;