MySQL 数据库 之 存储引擎 & 数据类型 & 约束条件

内容概要

  • 存储引擎
  • 数据库的字符类型
    • 整型
    • 浮点型
    • 字符类型
    • 日期类型
    • 枚举和集合类型
  • 创建表的完整格式
  • 约束条件
    • unsigned
    • zerofill
    • not null
    • unique
    • primary key
    • auto_increment

内容详细

存储引擎

简介：存储引擎可以看做是处理数据的不同方式

查看存储引擎命令：show engines;

image

四种存储引擎：

  • MyISAM

MySQL5.5之前默认的存储引擎

不支持事务，行锁和外键，数据操作不如 InnoDB 安全，但存储速度快

  • InnoDB

MySQL5.5之后默认的存储引擎

支持事务，行锁和外键，对数据的操作非常安全，但存储速度较慢

  • memory

数据存在于内存，存储速度最快，但断电则丢失数据

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

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

不过 8.0 版本的 MySQL 有点不同：

image

数据库的字符类型

1、整型

  • TINYINT

  • smallint

  • INT

  • bigint

tinyint 	smallint 	int 	bigint
不同的int类型能够存储的数字范围是不一样的
    1.要注意是否存负数(正负号需要占一个比特位)
    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),(999);

整型中括号内数字的作用:

create table t13(id int(3));
insert into t13 values(4444444);
"""
在整型中括号内的数字并不是用来限制存储的长度 而是用来控制展示的长度
我们以后在定义整型字段的时候 不需要自己添加数字 使用默认的就可以
"""
create table t14(id int(3) zerofill);
insert into t13 values(4);
# 结论:整型比较的特殊 是唯一个不是用来限制存储长度的类型

image

2、浮点型

  • float

  • double

  • decimal

**精确度： **float < double < decimal

	float		double  	decimal
	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.11111111111111111111111);
    insert into t8 values(1.11111111111111111111111);
    insert into t9 values(1.11111111111111111111111);

image

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,'jason');
    insert into t11 values(1,'jason');
    # 针对5.6版本超出范围不会报错 而是自动帮你截取并保存(此行为不合理)
    方式1:修改配置文件(永久)
    方式2:命令修改(暂时)
        show variables like '%mode%'
        set session  # 当前窗口有效
        set global  # 当前服务端有效
        	set gloabl sql_mode = 'strict_trans_tables'
        修改完毕后退出客户端重新进入即可
    再次执行上述插入命令 会直接报错

image

# 研究定长与变长特性
    	insert into t10 values(1,'j');
    	insert into t11 values(1,'t');
        # 统计某个字段数据的长度  char_length()
        '''底层确实会填充  但是取出来的时候又会自动去除'''
        set global sql_mode = 'strict_trans_tables,pad_char_to_full_length'

char与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','others')
    );
    insert into user values(1,'jason','男');  # 报错
    insert into user values(1,'jason','male');  # 正常
  • 集合

多选多(包含了多选一)

set()
	create table userinfo(
    	id int,
        name char(16),
        hobby set('basketball','football','doublecolorball')
    );

日期类型

date		年月日
datetime	年月日时分秒
time		时分秒
year		年份

create table client(
	id int,
    name varchar(32),
    reg_time date,
    birth datetime,
    study_time time,
    join_time year
);
insert into client values(1,'jason','2000-11-11','2000-1-21 11:11:11','11:11:11',1995);

创建表的完整格式

create table 表名(
	字段名 字段类型（） 约束条件，
    字段名 字段类型（） 约束条件，
    字段名 字段类型（） 约束条件
);

约束条件

约束条件相当于是在字段类型的基础之上添加的额外约束

例子: id int unsigned

  • unsigned

一般与数值类型连用，表示不能有负数

  • zerofill

多余的使用数字0填充

  • not null

非空，不能不填数据

  • default

填入默认值

	所有的字段都可以设置默认值 
		用户不给该字段传值则使用默认的 否则使用传了的
	create table t3(
        id int default 911,
        name varchar(16) default 'jason'
    );
  • 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 + unque

	从约束层面上来说 primary key 相当于是 not null + unique(非空且唯一)
	在此基础之上还可以加快数据的查询

	InnoDB存储引擎规定了一张表必须有且只有一个主键
		因为InnoDB是通过主键的方式来构造表的
		如果没有设置主键
			情况1:没有主键和其他约束条件
				InnoDB会采用隐藏的字段作为主键 不能加快数据的查询
			情况2:没有主键但是有非空且唯一的字段
				自动将该字段升级为主键
				create table t6(
                    id int,
                    age int not null unique,
                    pwd int not null unique
                );
	结论:
		以后我们在创建表的时候一定要设置主键
		并且主键字段一般都是表的id字段(uid sid pid cid)
		create table user(
            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无法影响自增
如果想要重置需需要使用truncate关键字
	truncate 表名  # 清空表数据并且重置主键值

新增表数据的方式

	新增表数据的方式
		方式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

image

注释：针对数据库表的字段的操作

增加字段： 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 字段名
posted @ 2022-02-18 19:23  elijah_li  阅读(51)  评论(0)  编辑  收藏  举报
//一下两个链接最好自己保存下来，再上传到自己的博客园的“文件”选项中