day46(* MySQL字段类型 和字段约束条件)
昨日内容回顾
* 存取演变史 ```python # 1.普通文本文件 # 2.软件开发目录规范 # 3.数据库应用 ``` * 应用发展史 ```python # 1.本地服务 # 2.网络服务 ``` * 数据库本质 ```python # 数据库与数据库软件的区别 数据库可以指代正在运行的专门处理数据的进程 数据库软件指代的是操作该进程的快捷方式(软件) # 数据库软件的本质 CS架构的软件 ``` * 数据库分类 ```python # 1.关系型数据库 主要特征就是有固定的表结构 MySQL、Oracle、PostgreSQL、MariaDB、sqlite、sql server # 2.非关系型数据库 主要特征就是K:V键值对存取 Redis、MongoDB、Memcache ``` * SQL与NoSQL ```python # 语句的由来 为了解决服务端兼容性问题 SQL指代的就是操作关系型数据库的语句 NoSQL指代的就是操作非关系型数据库的语句 ``` * MySQL下载与安装 ```python # 版本问题 window使用5.6.X即可(5.6.44) mac使用8.0X linux目前无需掌握 # 下载安装 下载压缩包并解压 先测试启动是否可行(cmd命令) ``` * 系统服务制作 ```python 1.环境变量添加 2.系统服务制作 mysqld --install 3.服务启动与关闭 net start mysql net stop mysql ''' 系统服务移除 1.先确保服务停止 net stop mysql 2.再移除服务 mysqld --remove''' 操作库的基本SQL语句 # 查: show create database 库名; # 指定查看某个库的信息 show databases; # 查看所有的库名称 #增 create database 库名; # 改； alter database 库名 charset='gbk'; #修改字符编码 #删： drop database 库名; 操作表的基本SQL语句 表是存放于库下面的 所以操作表之前 应该先确定库 如何查看当前在哪个库下 select database();结果如果是NULL表示当前没有切换到任何库下 如何切换到指定库下; use 库名; 我们在操作库、表、记录的时候 MySQL默认自带的都不要动 步骤1 create database 库名; (创建库)步骤 2 use 库名； 最后在 # 查 show tables;# 查看当前库下所有的表名称 show crate table 表名; # 指定查看某个表的信息 describe 表名; # 指定查看表的字段信息 简写 desc 表名; #增 create table 表名字 ( 字段名称1 字段类型1, 字段名称2 字段类型2 ); #改 alter table 表名字 rename 新表名字; #修改表名字 # 删 drop table 表名; 操作记录的基本SQL语句 #要想操作记录 肯定得先有库和表 # 查 select * from 表名; # 查看表里面所有的数据 select * from 库名.表名; # 查看指定库下面的指定表里面的所有数据 有些表里面的字段太多 展示的时候会错乱 可以考虑逐行展示 select * from 库名.表名\G; select Host,User from mysql.user # 查看指定字段 # 增 insert into 表名 values(值1,值2); # 单条数据 insert into 表名 values(值1,值2),(值3,值4); # 多条数据 # 改 update 表名 set 字段=新值 where 筛选条件; update t1 set name='jasonNB' where id=1; '''将id是1的数据中name字段对应的值改成jasonNB''' # 删 delete from 表名 where 筛选条件; delete from t1 where id=3; '''将id是3的数据删除''' 如果不加筛选条件就是删除所有的数据 今日内容详细
字符编码与配置文件
# 查看MySQL默认字符编码 \s 如果是5.X系列 显示的编码有多种 latin1 gbk 如果是8.X系列 显示的统一是utf8mb4 utf8mb4是utf8优化版本 支持存储表情 # 统一字符编码 5.X默认编码有多种 可能会导致乱码的情况 所以应该统一编码 my-default.ini配置文件 步骤1:拷贝一份该配置文件并修改名称为my.ini 步骤2:清空my.ini文件内的内容 步骤3:添加固定的配置信息即可 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] default-character-set=utf8 步骤4:保存并重启服务端即可生效 net stop mysql net start mysql 附上代码： [mysql] # 设置mysql客户端默认编码 default-character-set=utf8 [mysqld] # 服务端默认utf8编码 character-set-server=utf8 # 默认存储 default-storage-engine=INNODB [client] #设置客户端编码 default-character-set=utf8 存储引擎
数据库存储引擎：是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。 MySQL中的数据用各种不同的技术存储在文件(或者内存)中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。 简单理解；存储引擎可以理解为处理数据的不同方式 eg: 有一个a.txt文件不同的人会有不同的处理方式 戴裤衩会放在密码箱中 张开罪会转成pdf存储 李一刀会做多个备份 潘短江会制作封面美化等 查看储存引擎； show engines; （切记加分号） 初学者所需要了解的引擎； MyISAM： MyISAM存储引擎独立于操作系统,数据文件可以跨平台使用 采用表级锁来提供并发支持能力 执行读取操作的速度很快,而且不占用大量的内存和存储资源 数据和索引分开存储,减少操作系统的大文件访问情况,以提高性能 单独存储了表的行数数据,统计行数的不用遍历数据 不支持事务 MySQL以前的版本默认的数据库引擎是MyISAM Mysql8.0官方废弃MyISAM 5.1之前版本MySQL默认的存储引擎比较老牌 特点:存取数据的速度快 但是功能很少 安全性较低 适用场景 不需要事务支持的场景,或者有事务需求但是系统在service层自行实现事务支持的场景 需要做很多count计算的场景 插入不频繁,查询非常频繁的场景 不要考虑灾难恢复的场景,不要记录binlog,减少IO操作,可大幅提升性能 InnoDB 5.1之后版本MySQL默认的存储引擎 特点:有诸多功能 安全性较高 存取速度没有MyISAM快 innodb是第一个完整支持ACID事务的mysql存储引擎,特点是行锁设计,支持mvcc,支持外检,提供一致性非锁定读,同事被设计用来最有效地利用及上使用内存和cpu 特点:有诸多功能 安全性较高 存取速度没有MyISAM快 很明显我们会选择后者因为现在这个时代安全性远比内存更重要 保证安全的情况下才能去主动追求一些其他的东西、 
Memory
Memory用来存储和读写的大量的二进制数据。 以内存作为数据存取地 速度快但是断电立刻丢失这也是他的一个缺点 
BlackHole（‘人如其名’ 黑洞)
BlackHole（‘人如其名’ 黑洞） 任何写入的数据都会立刻消失(类似于垃圾回收处理站) blackhole 应该不存储数据，主要是用于结构同步的。所以不能用于日志和文档的记录。可用日志记录的的引擎应该是archive。 自定义选择存储引擎
（可以一次性执行多条命令语句）（根据业务需求的不同可以选择不同引擎） 1.create table t1(id int) engine = innodb 会出现2个文件 2.create table t2(id int) engine = myisam 会出现3个文件（速度比上面的快），但是我们还是用innodb存储 3.create table t3(id int) engine = memory 只有表结构没有表数据,是创建到内存中的 4.create table t4(id int) engine = blackhole 黑洞，吃数据（数据存进去就没有了，存进去就没有了） 创建表的完整语法
#语法： create table 表名( 字段名1 字段类型(数字) 约束条件, 字段名2 字段类型(数字) 约束条件, 字段名3 字段类型(数字) 约束条件 ); 约束条件：是在数据类型之外对字段附加的额外的限制 #注意： 1、最后一个字段之后不能加逗号 2.字段名和字段类型是必须的 3.数字和约束条件是可选的 4.约束条件可以写多个 空格隔开即可(后面详细讲解) 字段名1 字段类型(数字) 约束条件1 约束条件2 约束条件3 字段类型之整形
整数类型 Tinyint 1bytes 迷你整形，系统采用一个字节来保存的整形：一个字节 = 8位，最大能表示的数值是0-255 Smallint 2bytes 小整形，系统采用两个字节来保存的整形：能表示0-65535之间 Mediumint 中整形，采用三个字节来保存数据。 Int 4bytes 整形（标准整形），采用四个字节来保存数据。 Bigint 8bytes 大整形，采用八个字节来保存数据。 上述整型的区别在于从上往下能够存储的数字范围越来越大 
注意事项 1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位 2.注意手机号如果使用整型来存储 需要使用bigint才可以 工作小技巧:有时候看似需要使用数字类型存储的数据其实可能使用的是字符串 因为字符串可以解决不同语言对数字不精确的缺陷!!! create table t5(id tinyint); insert into t5 values(-129),(256); # 如果是在5.6版本不会报错 会自动处理成最大范围(没有意义) 步骤1:set global sql_mode = 'STRICT_TRANS_TABLES'; 步骤2:退出客户端 重新登录即可 # 如果是在5.7及以上版本 则会直接报错(更加合理) 验证(结论)发现所有的整型都默认带有正负号 如何修改不带正负号(约束条件) create table t6(id tinyint unsigned); create table 表名( 字段名1 字段类型(数字) 约束条件,); 字段类型之浮点型
# 浮点型 1.float(m,d) # m指最大的字符长度 255 ，d是指小数长度 30 2.double(m,d) # m指最大的字符长度 255 ，d是指小数长度 30 3.decimal(m,d) # m指最大的字符长度 65 ，d是指小数长度 30 # 三者区别 精度不一样 decimal > double > float # 底层就是：decimal用的是字符串的形式，你写多少，给你存多少 范围不一样 float和double 的整数部分 255 - 30 decimal 整数部分 65 - 30 上述浮点型从上往下精确度越来越高，重点研究的也是精确度问题 float(255，30)总共255位 小数位占30位 double(255,30) 总共255位 小数位占30位 decimal(65,30) 总共65位 小数位占30位 重点研究精确度问题 create table t7(id float(255,30)); create table t8(id double(255,30)); create table t9(id decimal(65,30)); insert into t7 values(1.11111111111111111); insert into t8 values(1.11111111111111111); insert into t9 values(1.11111111111111111); 虽然三者精确度有差距 但是具体用哪个应该结合实际情况 比如正常业务 使用float足够 如果是高精尖 可以使用decimal 字段类型之字符类型
char 与 varchar类型 CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。 CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。 CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。 VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由整张表所有varhcar列大小和使用的字符集确定。整体最大长度是65,532字节）。 同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。 VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。 简单来说就是 char 是定长类型 varchar是变长类型 char(4) 定长 最大只能存储四个字符 超出则报错 不够则空格填充至四个 varchar(4) 变长 最大只能存储四个字符 超出则报错 不够则有几个存几个 怎么去验证： create table t10(name char(4)); create table t11(name varchar(4)); insert into t10 values('aaaaaaaa'); insert into t11 values('aaaaaaaa'); # 如果是5.6版本并且没有修改严格模式 则会自动截取四个字符(不合理) # 临时修改 步骤1:set global sql_mode = 'STRICT_TRANS_TABLES'; 步骤2:退出客户端 重新登录即可 # 永久修改 修改my.ini配置文件 sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY' 重启服务端之后永久生效 工作中使用char还是varchar char 整存整取 速度快 会造成一定的存储空间浪费 ''' jasonkevintony tom jerry ''' varchar 节省存储空间 存取数据的速度没有char快(取数据不知道数据的精确长度) ''' varchar在存数据的时候会生成一个1bytes的报头 记录数据长度 varchar在取数据的时候先会读取1bytes的报头 从中获取真实数据长度 1bytesjason1bytes+kevin1bytes+tony ''' # 以前几乎使用的都是char 现在varchar使用频率也越来越高 两者都有使用场景 比如: 针对统一中国人的姓名 应该采取那个类型 varchar 规模较小 数据量相对固定的字典 char 很多时候字段类型的选取和命名都会在邮件中标明(组长、架构师、产品) ''' CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。 该字段数据集的平均长度与最大长度是否相差很小，若相差很小优先考虑CHAR类型，反之，考虑VARCHAR类型。 若字段存储的是MD5后的哈希值，或一些定长的值，优先选取CHAR类型。 若字段经常需要更新，则优先考虑CHAR类型，由于CHAR类型为定长，因此不容易产生碎片。 对于字段值存储很小的信息，如性别等，优先选取CHAR类型，因为VARCHAR类型会占用额外的字节保存字符串长度信息。 总之一句话，当我们能够选取CHAR类型的时候，或者说空间消耗相对并不是影响因素的重点时，尽量选取CHAR类型，因为在其他方面，CHAR类型都有着或多或少的优势。而当空间消耗成为了很大的影响因素以后，我们则考虑使用VARCHAR类型。 数字含义
字段类型括号内的数字大部分情况下是用来限制存储的长度 但是在整型中并不是用来限制长度 而是用来控制展示长度 create table t12(id int(3)); insert into t12 values(1111); # 不会报错 create table t13(id int(3) zerofill); # 位数不够用0填充 insert into t13 values(1); # 001 insert into t13 values(1111); # 有几位就展示几位 结论 以后涉及到整型字段 都无需自己定义长度 直接使用自带的即可 而针对其他类型的字段 则需要自己添加数字 字段类型之枚举与集合
1、enum枚举类型 enum枚举类型是一种可以在事先定义好(枚举出来)的各个可取值中选择一个的数据类型， 其定义方法是使用“enum(枚举值1，枚举值2，枚举值3……)”,定义为此种类型后，将对有关字段(变量)的取值范围进行了限定，只能取各枚举值中的某一个，它可以实现类似单选按钮的功能。另外，使用枚举类型数据的还可以提高对数据的存取等操作的速度。 如enum(‘男’，‘女’)定义了一个枚举类型数据，其允许的取值为字符串‘男’或‘女’。 一个枚举类型数据最多可以有65535个枚举项，占用空间2个字节。 2、set集合类型 set集合类型是一种可以在事先定义好的各个可取值中选择若干个进行组合的数据类型， 其定义方法是使用“set(元素值1，元素值2，元素值3……)”,定义为此种类型后，有关字段(变量)的取值应是所列的若干个元素值的组合，它可以实现类似复选按钮的功能。使用集合类型数据也可以提高对数据的存取等操作的速度。 如set(‘读书’，‘听音乐’，‘打球’，‘游泳’)定义了一个集合类型数据，其允许的取值为‘读书’，‘听音乐’，‘打球’，‘游泳’中某一个或某几个的组合。 一个集合类型数据最多包含64个元素值，占用空间8个字节。 # 枚举 多选一 create table t14( id int, name varchar(32), gender enum('male','female','others') ); '''插入数据的时候 针对gender只能填写提前定义好的数值''' # 集合 多选多(也可以多选一) create table t15( id int, name varchar(32), hobby set('篮球','足球','双色球','排球','水球','肉球') ); 字段类型之日期类型
date 年月日 datetime 年月日时分秒 time 时分秒 year 年 create table t16( id int, name varchar(32), reg_time datetime, birth date, study_time time, join_time year ); # 针对时间数据一般都是通过代码自动获取并添加 我们这里手动模拟 insert into t16 values(1,'jason','2000-11-11','2002-01-21','11:11:11','2015'); 
约束条件
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录 FOREIGN KEY (FK) 标识该字段为该表的外键 NOT NULL 标识该字段不能为空 UNIQUE KEY (UK) 标识该字段的值是唯一的 AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） DEFAULT 为该字段设置默认值 UNSIGNED 无符号 ZEROFILL 使用0填充 null与default 是否可空，null表示空，非字符串 not null - 不可空 null - 可空 default - 默认值 设置默认值后，则无论字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值。 unique 设置位唯一不重复 可以设置复合唯一，单列可以重复，但不能同时重复 primary key 主键，存储引擎innodb一张表必须有主键，不指定会自动检索不为空且唯一的字段创建，检索不到会创建一个隐藏字段。建表时指定主键。 通常一张表，都应该有一个id字段用来标识记录编号，id通常设置为主键。 一个表内只能有一个主键primary key =not null unique 不为空且唯一 单列做主键：不为空且唯一；常用。 多列做主键（复合主键）：多个字段连在一起，当作主键； # 字段类型与约束条件的关系 约束条件是基于字段类型之上的额外限制 eg: id int unsigned 字段类型int规定了id字段只能存整数 约束条件unsigned指的是整数基础之上还必须是正数 # 1.无需正负号 unsigned # 2.零填充 zerofill # 3.非空 not null create table t17(id int,name varchar(32)); '''插入数据的另外一种方式 打破字段顺序''' insert into t17(name,id) values('jason',1); insert into t17(id) values(2); create table t18(id int,name varchar(32) not null); insert into t18(id) values(2); # 报错 insert into t18(id,name) values(2,null); # 报错 insert into t18(id,name) values(2,''); # 不报错 # 4.默认值 default create table t19(id int,name varchar(32) default 'jason'); insert into t19(id) values(1); insert into t19(id,name) values(2,'kevin'); # 5.唯一值 unique '''单列唯一:某个字段下对应的数据不能重复 是唯一的''' create table t20( id int, name varchar(32) unique ); '''多列唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的''' create table t21( id int, host varchar(32), port int, unique(host,port) ); # 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 单词仅供参考
primary key :主键 primary:主要的 ; 最重要的 auto_increment：自动增量 unique: 唯一的 default：系统设定值 ; not null: 非空 zerofill： 零填充 hobby：爱好 date：日期 datetime： 约会时间 year：年 ball: 球 Basketball：篮球 football：足球 two color ball：双色球 Volleyball：排球 water polo：水球 meatball：肉球 gender：性别 male：男性 female：女性 others：其他的 enum：枚举 char_length：字符长度 sql_mode：SQL模式 double：双重的 decimal：十进制 tiny：极小的 small:小的 big ：大的 engine：引擎 发动机