MySQL 存储引擎 、基本数据类型、约束条件

概要

 

1 存储引擎

2 基本数据类型

3 约束条件

4 创建表的完整语法

5 添加表数据的方式

 

详细

 

1 存储引擎

 

1-1 定义: 针对不同数据的而对应的存储机制

• 例如: 生活中不同的文件(txt,pdf,word,mp4),都有对应的处理机制

 

1-2 分类:

 

查看引擎的命令: show engines;

查看正在使用的存储引擎:  show variables like 'storage_engine%';

 

分类

• MyISAM
• MySQL5.5之前默认的存储引擎
• 数据存储速度快;但是相比InnoDB不安全
• 不支持事务、行级锁和外键
• InnoDB
• 存储数据较MyISAM安全,但是速度较慢
• 支持事务、行级锁和外键
• memory
• 基于内存存储数据
• 存储数据快,但是断电容易丢失
• blackhole(黑洞)
• 写入其中的数据会立刻消失,类似垃圾回收站

 

1-3 四种存储引擎创建表的不同点:

 

创建命令:

• 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;

 

不同点

• MyISAM 会创建三个文件
• .frm 表结构文件
• .MYD 表数据文件
• .MYI 表索引文件(索引是用来加快数据查询的)
• InnoDB会创建两个文件
• .frm 表结构文件
• .ibd 表数据和表索引文件
• merory会创建一个文件
• blackhole会创建一个文件

 

 

2 基本数据类型

 

参考

解释: 1个bytes是8bits; 正负号会占一位; 比如tinyint : 若有正负号就是七位,即2的7次方-128;8位是2的8次方256;

 

2-1 整型

 

#1 类型: tinynit  smallint  int  bigint

 

~注: 针对手机号码,只能用bigint

 

#2 """int类型默认带正负号"""

案例

    超出限制只存最大可接受值

 

#3 去掉正负号(让数字没有正负号)[属于 "约束条件"]

 

关键字: unsigned

 

 

# 4 整形中括号内数字的作用

• 控制展示的长度,不是限制位数
• 实际使用: 针对整型,不需要自己往括号内添加数字,使用默认即可(默认宽度足够显示所有数据)

 

演示

create table t13(id int(3));

insert into t13 values(1);

 

• 若没有超出3位,默认用0填充至3位;
• 用到约束条件: zerofill - 含义"用0填充"
• 若超出3位,那么有几位存几位(但还是要遵循最大范围)

 

 

 

2-2 浮点型

 

#1 存储限制

• float(255,30) #总共255位,小数占30位
• double(255,30) #总共255位,小数占30位;
• decimal(255,30) #总共65位,小数30位;

 

#2 精确度差异

       float精确度最小; double次之; decimal精确度最大;

演示

 

#3 实际使用哪个

• 一般float足够
• 若从事业务对精确度要求高,根据情况选择对应的关键字

 

#4 python精确度

• 其精确度很低
• 之所以能够从事人工智能和数据分析完全是有牛人开发出功能强大的模块

 

 

2-3 字符类型(使用频繁)

 

#1 分类:

• char
• 定长
• char(4) 超过四个直接报错,不够四个空格补全
• 优势: 整存整取,速度相对快
• 劣势: 浪费存储空间
• varchar
• 变长
• varchar(4)  超过四个直接保报错,不够有几个存几个
• 劣势: 存取数据速度较char慢
• 1bytes+json 1bytes+tom 1bytes+jerry
• 存的时候需要先制作报头;
• 取的时候也需先读取报头,然后读取真实数据
• 劣势: 节省存储空间

 

#2 实际使用哪个?

以前多用char, 现在用varchar公司也不少.若公司需要处理的数据特别多,优先推荐varchar,节省硬盘资源

 

补充#1 统计字段长度:

• select char_length(字段名) from 表名;
• 如: select char_length(name) from t1;

 

补充#2 修改sql_mode 让MySQL在显示的时候,不要做自动剔除空格操作

背景: char字符类型时,在往硬盘存数据的时,是带空格存的,但是在显示的时候,MySQL会将多余的空格剔除

命令:

set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

 

验证

 

演示1

 

演示2

 

演示3

 

#3  ※修改配置文件

背景:

"针对5.6 版本"上图演示3中字符长度超出范围不会报错,而是截取范围内长度现象

 

文件位置: MySQL解压文件的里自己新建的my.ini文件(在笔记"MySQL_01"的14-3第一次创建)

 

方式1 修改配置文件(永久有效)

 

step1 退出客户端,关闭当前cmd窗口

退出命令: exit;

 

 

step2 修改配置文件

添加如下语句

 

将原配置文件的sql_mode改为注释行

 

step3 重启mysql服务端

以管理员的身份打开cmd窗口

命令: net stop mysql <没有分号>

命令: net start mysql 

 

step4 重新登陆客户端,验证sql模式是否修改成功

 

演示

 

 

方式2 命令修改(临时有效)

 

step1 查看当前sql模式

命令: show variables like '%mode%';

 

step2 设置字符长度超出范围后报错语句

命令: set gloabl sql_mode = 'strict_trans_tables';

 

step3 退出mysql客户端,重新进入

命令: exit;

命令: mysql -uroot -p 回车

命令: 输入密码 回车

 

演示

 

step4 查看是否修改成功

命令: show variables like '%mode%';

 

step5 再次执行超出字符范围的命令语句,会报错

 

演示

 

 

补充3

        进公司后不需要你考虑字段名和字段类型,产品经理会发邮件在其中说明;如果是新来的产品经理,字段没有写明白,那么就是需求文档不明确,需及时回复.

 

 

2-4 日期类型

 

分类

• data: 年月日 1997- 3-6
• datatime: 年月日时分秒 1996-3-4 11:11:11
• time: 时分秒 11:11:11
• year: 年 1997

 

演示1

演示2

注: 除了year,其他时间在输时需要单引号

 

 

2-5 枚举

 

用法: 

在存数据的时候只能从枚举类型里选择其中一个存储,写其他没有的字会报错.

 

关键字:

enum('字1','字2',...)

 

演示

表genderType:

 

2-6 集合

 

用法: 存数据的时候,集合里的数据可以写多个,至少一个,不能写集合之外的字.

 

关键字:

set('字1','字2',...)

 

演示

 

 

3 约束条件

 

 

3-1 unsigined

 

• 含义: 无负号,即无负数
• 见 2-1 #3 

 

3-2 zerofill

 

• 含义: 用零填充
• 见: 2-1 #4

 

3-3 not null: 非空

• 含义: 该字段值对应的值必须有,否则报错

 

演示:

 

 

3-4 default: 设置字段的默认值,用户不给该字段传值,使用默认值;传值,则使用传的值.

 

演示

 

3-5 unique

 

#1 单列唯一

 

演示1

演示2

字段值重复时报错

 

#2 联合唯一

          即: 字段值组合是唯一的

 

演示1

 

演示2

 

3-6 primary key(创建表一定要设置)

 

• 含义: 非空且唯一 (相当于 not null + unique)
• 作用: 类似于数据的唯一标识, 一般都是数字类型,可以加快数据的查询
• [MUST]
• 在创建表的时候,一定要设置主键
• 且主键字段一般都是id字段(字段名可以是 uid sid pid cid)
• 例:
• create table user(
•             id int primary key,
•             name varchar(32)
•         );

 

 

补充:

 

InnoDB存储引擎规定了一张表必须有且只有一个主键,因为InnoDB是通过主键的方式来构造表的

如果没有设置主键

        

• 情况1:没有主键和其他约束条件
• InnoDB会采用隐藏的字段作为主键 不能加快数据的查询

• 情况2:没有主键但是有非空且唯一的字段
• 自动将该字段升级为主键

 

3-7 auto_increment(自增)

       只能给键用,目前也就是配合"primary key"一起用.

 

背景: 由于主键类似于数据的唯一标识 并且主键一般都是数字类型,我们在添加数据的时候不可能记住接下来的序号是多少 太麻烦

 

命令格式例子:

create table user1(

            id int primary key auto_increment,

            name varchar(32)

        );

 

特性:

• 自增不会因为删除操作而回退
• delete from无法影响自增
• 如何重置-使用truncate关键字
• truncate 表名  # 清空表数据并且重置主键值

 

演示1

 

演示2

 

演示3

 

4 创建表的完整语法

 

create table 表名(

    字段名1 字段类型(宽度数字) 约束条件,

    字段名2 字段类型((宽度数字) 约束条件,

    字段名3 字段类型((宽度数字) 约束条件

);

"""

1.字段名和字段类型是必须的

2.数字和约束条件是可选的 并且 约束条件可以有多个空格隔开即可 即 约束条件1 约束条件2

3.最后一个语句的结尾不要加逗号

"""

 

5 添加表数据的方式

   

 """

        方式1:  按照字段顺序依次传值

             insert into t1 values(1,'jason');

        方式2:  自定义传值顺序 甚至不传

            insert into t1(name,id) values('jason',1);

            insert into t1(id) values(1);

    在MySQL中不传数据 会使用关键字NULL填充意思就是空 类似于python的None

"""

 

方式2 演示