mySQL数据库之存储引擎&数据类型&约束条件
内容概要
- 存储引擎
- 数据库的字符类型
- 整型
- 浮点型
- 字符类型
- 日期类型
- 枚举和集合类型
- 创建表的完整格式
- 约束条件
- unsigned
- zerofill
- not null
- unique
- primary key
- zuto_increment
内容详情
存储引擎
简介： 存储引擎可以看作是处理数据的不同方式
查看存储引擎的命令： show engines;
四种存储引擎：
-
mySQL5.5之前默认的存储引擎
不支持事务，行锁和外键，数据操作不如innoDB安全，但是存储的速度快
2.innoDB
mySQL5.5之后默认的存储引擎
支持事务，行锁和外键，对数据的操作非常的安全，但存储的速度较慢
3.momory
数据存在于内存，存储速度最快，但断电则丢失数据
4.blackhole
数据下入进去就会丢失，类似于回收站
存储引擎创建表的不同点
create table t1(id int) engine=myisam create table t2(id int) engine= innodb; create table t3(id int) engine= memory; create table t4(id int) engine =blackhole;
""" MYsql会创建三个文件夹 .frm 表结构文件 .MYD 表数据文件 .myi 表索引文件 InnoDB会创建两个文件 .frm 表结构文件 .ibd 表数据和表索引文件 memory .frm 表结构文件 blackhole .frm 表结构文件 """
数据库的字符类型
1整型
- tinyint
- smallint
- int
- bigint
tinyint smallint int bigint 不同的int类型能够存储的数字范围是不一样的 1、要注意是否存在正负数（正负号需要i占一个比特位） 2、针对手机号码只能用 bigint '''研究默认是否需要正负号''' create table t5(id tinyint); insert into t5 values(-999), (999); #结论 ：所有的int类型默认都需要正负号 create table t6(id tinyint unsigned); insert into t6 values(-999),(9999);
整型中括号内数字的作用
create table t13(id int(3)); insert into t13 values(4444444); """ 在整型中括号内的数字并不是用来限制存储的长度的 而是用来控制展示的长度的 我们以后在定义整型字段的时候 不需要自己填写数字 使用默认的就好""" create table t14(id int(3) zerofill); insert into t13 values(4); #结论 ： 整型比较特殊 是唯一个不是用来限制存储长度的类型
2、浮点型
- float
- double
- decimal
**精确度 ** float< double<decimal
folat double decimal float(255,30) #总共255位，小数占位30位 double(255,30) #总共占位255位，小数占位30位 decimal(65,30) # 总共占位65位，小数占位30 位 """研究三者的不同""" create table t7(id int float(255,30)); create table t8(id double(255,30)); create table t9(id decimal(65,30)); insert into t7 values(1.111111111111111); insert into t8 values(1.122111111111111111111111); insert into t9 values(1.111111111111111);
3、字符类型
- char(4)
定长类型 最多只能存放四个字符 多了报错，少了自动空格填充至四个
- varchar(4)
变长类型 最多只能存放四个字符，多了报错，少了有几个则存几个
二者的区别
- 定长字符串char(4) :整存整取 ，存入的数据字符少于4个，另外任有三个空字符位置一起保存
优势： 存储速度快（整存整取，不用判断去除字节的多少）
缺点： 浪费的资源较多，字符占空间内存
- 可变长字符串varchar(4): 存入多少 ，字符就占多少字节，存入的每一寸数据字符串前都会有1btyes大小字节记录存入的字符有多少， 取出时会先判断要去除多少字节，
优势：减少资源浪费
劣势：存取速度较慢 ，但对人而言也足够快了。
"""研究上述特征""" create table t10 (id int ,name char(4)); create table t11(id int ,name varchar(4)); insert into t10 values(1,'jioajio'); insert into t11 values(2'jasom'); #针对5.6版本超出范围不会报错 而是自动帮你截取多余的部分并保存（此行为不合理） 方式一：修改配置文件（永久） 方式二： 命令修改 show variables like '%mode%' set session # 当前窗口有效 set global # 当前服务端有效 set gloabl sql_mode = 'strict_trans_tables' 修改完毕后退出客户端重新进入即可 再次执行上述插入命令 会直接报错
#研究定长与变长的特性 inserrt into t10 values(1,'jioajiao'); insert into t11 values(1,'jiaojaio'); #统计某个字段数据的长度 char_length() '''底层确实会填充 但是取出来的时候又会自动去除''' set global sql_mode = 'strict_trans_tables,pad_char_to_full_length'
cha与varchar的对比
char; 优势；整存整取速度快 劣势：浪费储存空间 varchar; 优势：节省储存空间 劣势：存取数据的速度比较char慢 """ char(5) jasontony kevintom oscartank sean jerry varchar(5) 1bytes+jason1bytes+tony1bytes+kevin1bytes+tom 存:先计算数据的长度 取:先获取报头的数据 """ # 以前几乎都是char 现在很多情况下使用varchar 进了公司之后 会通过右键告诉你每个字段的英文名和中文名及类型等项目的诸多信息 补充:在创建字段的时候可以加上相应的注释 create table t12( id int comment '序号', name char(4) comment '姓名' );
枚举与集合类型
- 枚举
从多个选项中选一个选项填入 ，一般时性别
enum() create table user( id int, name varchar(32), gender enum('male', 'female','other') ); insert into user values (1,'jason','男‘）; #报错 insert into user values (1,'jiaojaio','male'); #正常
- 集合
多选多（包含了多选一）
set () create table userinfo( id int, name char(16), hobby set ('basketball','football','doublecolorball'); insert into userinfo values(1,'jiaojaio','basketball,football,doublecolorball');
日期类型
data 年月日 datatime 年月日时分秒 time 时分秒 year 年份 create table cilent( id int, name varchar(32), reg_time data, birth_time datatime, study_time time, join_time year); insert into cilent values(1, 'jason','2000-2-2','2000-1-1 11:11:11',11:11:11:',1995);
创建表的完整格式
create table 表名（
字段名 字段类型 （） 约束条件，
字段名 字段类型 （） 约束条件
字段名 字段类型（） 约束条件
）；
约束条件
约束条件相当于在字段类型的基础上添加额外的约束
eg: id int unsigned
- unsigned
一般与数值类型连用，表示不能有负数
- zerofill
多余的使用数字0填充
- not null
非空， 不能不填数据
- default
填入默认值
所有的字段都可以设置默认值 用户不给该字段传值则使用默认的 否则使用传的值 create table t3( id int defult 911, name varchar(32) default 'jiaojaio' );
- unique
唯一值
联合唯一： unique(id name)
单列唯一 create table t4( id int , name varchar(32) unique ); 联合唯一 create table t5( id int , host varchar(32), port int, unique(host,port) );
- primary key
主键：not null+ unique
从约束层面上来说 primary key 相当于是 not null + unique(非空且唯一） 在此基础之上还可以加快数据的查询 InnoDB存储引擎规定了一张表必须有且有一个主键 因为InnoDB是通过主键的方式来构成表的 如果没有设置主键 情况一、没有主键和其他约束条件 InnoDB会采用隐藏的字段作为主键 不能加快数据的查询 情况二、没有主键但是有非空且唯一的字段 自动将该字段升级为主键 create table t45( id int , age int not null unique, pwd int not null unique ); #j结论：：以后我们在创建表的时候一定要设置主键 并且主键字段一般都是表的id 字段（uid sid pid cid) create table user( id int primary key, id int primary key, name varchar(32) );
- auto_increment
主键自增
由于主键类似与数据的唯一标识 并且主键一般都是数字类型 我们在添加数据的时候不可能记住接下来的序号是多少 太麻烦了 create table user1( id int primary key auto_increment, name varchar(32) );
特性：就算删除数据，它的主键仍然会存在，新增数据自增的id不会占用删除的数据Id
取消该特性
自增不会因为删除操作而回退 delete from 无法影响自增 如果想要重置需要使用truncata 关键字 truncata 表名 #清空表数据并且重置主键值
新增表数据的方式
新增表数据的方式 、方式一： 按照字段顺序一一传值 insert into t1 values (1,'jason'); 方式二、自定义传值顺序 甚至不传 insert into t1 (name ,id) values('jason',1); insert into t2(id) values(1); 在MySQL中不传数据 会使用关键字NULL填充意思就是空 类似于python的None
注释：针对数据库表的字段的操作
增加字段： alter table [表名] add 字段名 smallint default 0 增加数字字段，整型，缺省值为0 alter table [表名] add 字段名 int default 0 增加数字字段，长整型，缺省值为0 alter table [表名] add 字段名 single default 0 增加数字字段，单精度型，缺省值为0 alter table [表名] add 字段名 double default 0 增加数字字段，双精度型，缺省值为0 alter table [表名] add 字段名 Tinyint default 0 增加数字字段，字节型，缺省值为0 alter table [表名] add 字段名 text [null] 增加备注型字段,[null]可选参数 alter table [表名] add 字段名 memo [null] 增加备注型字段,[null]可选参数 alter table [表名] add 字段名 varchar(N) [null] 增加变长文本型字段 大小 为N(1～255) alter table [表名] add 字段名 char [null] 增加定长文本型字段 大小固定为255 alter table [表名] add 字段名 Datetime default 函数 增加日期型字段，其中 函数 可以是 now(),date()等，表示缺省值 (上面都是最常用的，还有其他的属性，可以参考下面的数据类型描述) 删除字段： alter table [表名] drop 字段名
浙公网安备 33010602011771号