day47(查询关键字,约束条件之外键)

今日内容详细

昨日内容回顾
# 查看当前数据库默认字符编码
	\s
# 必须要统一字符编码防止出现乱码
	my-default.ini	拷贝并修改 	my.ini
	针对5.6和5.7需要修改配置文件
  	[mysqld]
    作用于服务端
    [client]
    作用于客户端(第三方代码编写的)
    [mysql]
    作用于客户端(MySQL自带的)
  针对8.0无需修改 默认就是统一的utf8mb4
  '''
  如果修改的是mysqld下面的配置 则需要重启服务端
  如果修改的是客户端下面的配置 则无需重启服务端 
  '''
#储存引擎
# 什么是存储引擎
	可以简单的理解为是处理数据的不同方式
# 查看所有的存储引擎
	show engines;
    show engines;
# 需要了解的存储引擎
	MyISAM
  	5.1及之前版本默认的存储引擎
    存取数据的速度快 但是功能较少安全性较低
  InnoDB
  	5.1之后版本默认的存储引擎
    功能较多 支持事务、行锁、外键等 安全性较高 
  BlackHole
  	任何写入的数据都会直接丢失
  Memory
  	基于内存存取数据 断电立刻丢失
# 如何指定存储引擎
	create table t1(id int)engine=myisam;
    create table t1(id int)engine=myisam;
#创建表的完整语法
create table +表明(
字段名 字段类型(数字) 约束条件，
字段名1 字段类型(数字) 约束条件
);
'注意最后一行不要加逗号；

1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件可以有多个 空格隔开即可

#字段类型
sql_mode补充
1.如何查看
show variables like '%mode%'
2.单次修改(仅限于当前登录)
set session sql_mode = '...'
3.临时修改(仅限于当前服务)
set global sql_mode = '...'
4.永久修改
在配置文件中的[mysqld]下面添加
# 如果是在5.6版本不会报错 会自动处理成最大范围(没有意义)
  	步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
    步骤2:退出客户端 重新登录即可
  # 如果是在5.7及以上版本 则会直接报错(更加合理)
  验证(结论)发现所有的整型都默认带有正负号 如何修改不带正负号(约束条件)
  create table t6(id tinyint unsigned);
# 整型
tinyint\smallint\int\bigint
存储的数字范围不一样
默认需要使用一个比特位存储正负号
如果想要修改默认方式 移除正负号 需要约束条件
unsigned
#浮点型
float < double < deccimal
精确度不一样根据实际去应用
#字符型
char   varchar   char_length()
定长    变长      统计字符长度
1.验证定长与变长存取数据的特征
sql_mode = 'pad_char_to_full_length'
2.比对定长与变长的优劣势
char存取数据的速度都很快 但是浪费一定存储空间
varcahr节省存储空间 但是存取速度交于char慢一些
'''结合具体情况选择定长与变长'''
3.数字针对整型不是用于限制存储长度而是控制展示长度
以后针对整型不需要自己添加数字 直接使用默认的即可
# 枚举和集合
enum
多选一
\set
多选多也可以对选一
# 日期类型
date\datetime\time\year
#字段约束条件
插入数据的方式
方式一：insert into t1 valuse();
方式二：insert into t1(name,id) valuse();
unsigned 
无需正负号
zerofill
零填充
# 1.not null非空
# 2.default默认值
# 3.unique唯一值
#单列唯一 :某个字段下对应的数据不能重复 是唯一的
create table t20(
    id int,
    name varchar(32) unique
  );
#多列唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的
     create table t21(
  	id int,
    host varchar(32),
    port int,
    unique(host,port)
  );
#主键
primary key
# 6.主键
	primary key
  """
  1.单从约束层面上而言 相当于not null + unique(非空且唯一)
  	create table t22(id int primary key);
  2.是InnoDB存储引擎规定的一张表有且必须要有一个主键 用于构建表
  	主键可以加快数据的查询速度(类似于书的目录)
  	如果创建表创建的时候没有设置主键也没有其他的键 那么InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而无法使用 即无法加快数据查询)
  	如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
  		create table t23(
  			tid int,
  			pid int not null unique,
  			cid int not null unique
  		);
  """
  结论:创建表应该有一个序号字段(id\pid\cid)并且应该将该字段设置成主键
  create table t24(
  	id int primary key,
    name varchar(32)
  );
  # 也可以有联合主键(多个字段组合 本质还是一个主键) 了解即可
  create table t24(
  	id int,
    name varchar(32),
    pwd int,
    primary key(id,pwd)
  );
# 7.自增
	auto_increment  
  专门配合主键一起使用 用户以后在添加数据的时候就不需要自己记忆主键值
  create table t25(
  	id int primary key auto_increment,
    name varchar(32)
  );
"""
总结
	以后在创建规范的表的时候
		一般都会有一个主键字段的编写如下
			id int primary key auto_increment
"""

今日内容详细

自增特性

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
    # 拆表之后上述的三个缺陷解决 
  但是迎来了一个致命问题  >>>	员工与部门之间没有了绑定关系
    在员工表中添加一个部门编号字段与部门表中的主键字段对应
	"""
	该字段其实就是外键字段
		外键字段就是用来记录表与表之间数据的关系
	"""

什么是外键：
主键：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性

外键：是另一表的主键, 外键可以有重复的, 可以是空值，用来和其他表建立联系用的。所以说，如果谈到了外键，一定是至少涉及到两张表。例如下面这两张表：

创建表的代码
create table department(
            id int primary key auto_increment,
            name varchar(20) not null,
            description varchar(100)
);
create table employee(
            id int primary key auto_increment,
            name varchar(10) not null,
            gender varchar(2) not null,
            salary float(10,2),
            age int(2),
            gmr int,
            dept_id int
);

外键关系

你可以说是表与表之间的关系也可以说是数据与数据之间的关系
数据与数据之间的关系有多种情况罗列出以下四种
有以下四种：
  1.一对多
  2.多对多
  3.一对一
  4.没有关系
# 一对多
1.以员工表与部门表为例
先站在员工表的角度
问:一个员工能否对应多个部门
答:不可以，单论员工与员工部门之间的关系别扯太多，一个员工肯定不能是属于好几个部门的
再站在部门表的角度
问:一个部门能否对应多个员工
答:肯定可以的也是毋庸置疑的
结论:换位思考之后得出的答案是一个可以一个不可以
所以关系是"一对多" 部门是'一'员工是'多'
'''关系表达只能用一对多 不能用多对一'''
一对多关系 外键字段建在"多"的一方(员工表)

     本来数据库一对一、一对多、多对多关系并不复杂，但是最近在理解的时候感觉又感觉多了写新意，所以现在在来总结一下吧
一、首先给出三种关系的实例
1、一对一关系实例
   * 一个人对应一张身份证，一张身份证对应一个人
2、一对多关系实例
   * 一个班级拥有多个学生，一个学生只能够属于某个班级
3、多对多实例
   * 一个学生可以选修多门课程，一个课程可以被多个学生选修

一对一关系

一对一关系是最好理解的一种关系，在数据库建表的时候可以将人表的主键放置与身份证表里面，也可以将身份证表的主键放置于人表里面

、一对多关系

班级是1端，学生是多端，结合面向对象的思想，1端是父亲，多端是儿子，所以多端具有1端的属性，也就是说多端里面应该放置1端的主键，那么学生表里面应该放置班级表里面的主键

多对多关系

对于多对多关系，需要转换成1对多关系，那么就需要一张中间表来转换，这张中间表里面需要存放学生表里面的主键和课程表里面的主键，此时学生与中间表示1对多关系，课程与中间表是1对多关系，学生与课程是多对多关系

外键SQL语句

"""
如果表中有外键字段 那么建议你先编写普通字段 最后再考虑外键字段
"""
# 在创建表字段的时候也可以给每个字段添加中文注释
create table emp(
	id int primary key auto_increment comment '编号',(这句话的意思是当前里边的dp_id字段是外键字段)\
    '外键字段是记录数据与数据之间的关系或者说是表与表之间的关系
  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 '部门描述'
);

# 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语句的形式建立逻辑意义上的表关系
"""

多对多关系

以图书与作者表为例
	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','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  # 级联删除
);
     # 针对多对多表关系 
  两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除，绑定之后就不可以了所以要在绑定之前

一对一关系

针对qq用户表 其实里面的数据可以分成两类
	热数据:经常需要使用的数据
    	eg:qq号码 座右铭 个人简介 爱好
  冷数据:不怎么经常需要使用的数据
    	eg:邮箱 电话 学校 ...
  为了节省资源并降低数据库压力 会将表一分为二
  	用户表
    	存使用频率较高的数据字段
    用户详情表
    	存使用频率较低的数据字段
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
);
'加unique就是做限制，有了这个操作之后，并且还是唯一的，这样才是一对一

表查询关键字

# 数据准备
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);

查询关键字之select与from

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

查询关键字之where筛选

# 1.查询id大于等于3 小于等于6的数据
逻辑运算符 and or not
  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;
# 如果要查的具体数字太多 成员运算 in：
  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;

查询关键字之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				统计平均值
  聚合函数有什么特点？
除了 COUNT 以外，聚合函数忽略空值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。

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.统计每个部门下所有员工的姓名
group_concat（）主要功能：能将相同的行组合起来
	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;
"""字段起别名"""
as   可理解为：用作、当成，作为；一般是重命名列名或者表名。（主要为了查询方便）

	select post,group_concat(name) as '姓名' from emp group by post; 
  select id as '序号',name as '姓名' from emp;
  # as关键字也可以不写 但是语义不明确 建议加上
  select id '序号',name '姓名' from emp;

2、完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

3、长度限制：在mysql中，有个函数叫“group_concat”，平常使用可能发现不了问题，在处理大数据的时候，会发现内容被截取了，其实MYSQL内部对这个是有设置的，默认不设置的长度是1024，如果我们需要更大，就需要手工去修改配置文件；或者通过sql语句来修改。

4、查询group_concat最大长度限制

select @@global.group_concat_max_len;

5、修改group_concat最大长度限制


  1).在MySQL配置文件中加上


          group_concat_max_len = 102400 #你要的最大长度
 


   2).可以简单一点，执行语句,可以设置作用范围
 

             SET GLOBAL group_concat_max_len=102400;
 

             SET SESSION group_concat_max_len=102400;

单词概述（仅供参考）

engines:引擎
values:值
desc: 描述
comment： 议论
book :书
on update cascade : 级联更新
cascade： 大量
author:著者,作者
references：提到;谈及;涉及;参考;查询;查阅; 参考
detail:细节，详情;具体情况;(关于某事物的)资料;消息
userdetail:用户详细信息
phone：电话
sex :性别
operation：活动活动;手术;(有组织的)行动;(包括许多部分的)企业;公司;（工商业）活动，业务;运算;运转;军事行动
sale:出售;销售;销售量;销售部;特价销售;销售活动
salary:  薪水 ; 薪金 ; 付…薪水
group by:聚合;聚合函数;分组;进行分组;分组查询