MySQL数据库之表的详细操作：存储引擎、表介绍、表字段之数据类型，约束条件

一、存储引擎

• MySQL中建立的库====》文件夹

• 库中建立 表====》文件

# 存储引擎可以看成是处理数据的不同方式

# 查看存储引擎的方式
	show engines;
    
# 需要掌握的四个存储引擎
	MySQL
    	MySQL5.5之前默认的存储引擎
        	不支持事物、行级锁和外键 针对数据的操作较于InnoDB不够安全
            但是数据的存取速度较于InnoDB更快
    InnoDB
    	MySQL5.5之后默认的存储引擎
        	支持事物、行级锁和外键 针对数据的操作更加的安全
    memory
    	基于内存存取数据
        	速度最快但是断电离开丢失
    blackhole
    	写入其中的数据都会立刻消失 类似于垃圾处理站
        

1、存储引擎创建表的不同点

    create database dy1;
    use dy1;
    create table t1(id int)engine=myisam;
    create table t2(id int)engine=innodb;
    create table t3(id int)engine=blackhole;
    create table t4(id int)engine=memory;
    
    # 给表内插入信息
    insert t1 values(111);
    insert t2 values(222);
    insert t3 values(333);
    insert t4 values(444);
    
    #  效果一模一样 加（into）显得语法更完美
    insert into t1 values(111);
    
    
    # 查看表内信息
    select * from t1;
    
     """
    MyISAM会创建三个文件
    	.frm  	表结构文件
    	.MYD	表数据文件
    	.MYI	表索引文件(索引是用来加快数据查询的)
    InnoDB会创建两个文件
    	.frm	 表结构文件
    	.ibd     表数据和表索引文件
   	memory
   		.frm	 表结构文件
   	blackhole
   		.frm	 表结构文件
    """

2、表介绍

• 表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。id，name，qq，age称为字段，其余的，一行内容称为一条记录

二、MySQL基本数据类型

• 存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的。

1、整型

# 1、整型
	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);

2、浮点型

# 2.浮点型
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);
    # 结论:三者的精确度不一样
    	float < double < decimal
    """
    到底使用哪个
    	一般情况下小数点后面只保留两位 所以float足矣
    	如果是从事高精密业务 则需要考虑更高的精确度
    ps:有时候很多看似需要用数字存储的数据 可能都是存的字符串
    课外扩展:python本身对数字的精确度很低 之所以能够从事人工智能和数据分析完全得益于功能强大的模块
    """

3、字符类型

• 虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度 更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方 面的因素，以求达到最佳的平衡

# 3.字符类型
	char(4)
    	定长类型 最多只能存四个字符 多了报错少了自动空格填充至四个
    varchar(4)
    	变长类型 最多只能存四个字符 多了报错少了有几个则存几个
        
   """研究上述特征"""
	create table t10(id int,name char(4));
    create table t11(id int,name char(4));
    insert into t10 values(1,'jason');
    insert into t11 values(1,'jason');
     # 针对5.6版本超出范围不会报错 而是自动帮你截取并保存(此行为不合理)
    方式1:修改配置文件(永久)
     # 直接cv sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES到MY.
 		
    方式2:命令修改(暂时)
        show variables like '%mode%'
        set session  # 当前窗口有效
        set global  # 当前服务端有效
        	set gloabl sql_mode = 'strict_trans_tables'
        修改完毕后退出客户端重新进入即可
    再次执行上述插入命令 会直接报错
    # 研究定长与变长特性
    	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'

4、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 '姓名'
    );

5、整型中括号内数字的作用

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

6、枚举与集合类型

• 字段的值只能在给定范围中选择，如单选框，多选框

枚举
	多选一
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')
    );

7、日期类型

 时间类型：year  date  time  datetime  timestamp
 作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
   最常用：datetime
    year: 年
         YYYY（1901/2155）
    date: 年月日
         YYYY-MM-DD（1000-01-01/9999-12-31）
    time: 时分秒
         HH:MM:SS（'-838:59:59'/'838:59:59'）
    datatime: 年月日时分秒
         YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
    timestamp：年月日时分秒（有年范围限制，但在这个时间之前使用更方便）
         YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

 # --------------------------------------------------

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 表名(
	字段名1 字段类型(数字) 约束条件,
	字段名2 字段类型(数字) 约束条件,
	字段名3 字段类型(数字) 约束条件
);
"""
1.字段名和字段类型是必须的
2.数字和约束条件是可选的 并且 约束条件可以有多个空格隔开即可
3.最后一个语句的结尾不要加逗号
"""

# 例子

create table t33(
	id int,
    name varchar(32),
	gender enum('male','female','others')
);

insert into t33 values(1,'geng','male');

1、表的约束条件

• 约束条件与数据类型的宽度一样，都是可选参数

• 作用：用于保证数据的完整性和一致

"""
约束条件相当于是在字段类型的基础之上添加的额外约束
	eg: id int unsigned
	ps: 意思就是只能存数字并且还得是正数
"""
unsigned		让数字没有正负号
zerofill		多余的使用数字0填充
not null		非空
	"""
	新增表数据的方式
	
	重新创库：create database dy2;
	切换库： use dy2;
	创表：create table t1(id int,name char(4));
	
	
		方式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
	"""
    create table t2(
    	id int,
        name varchar(32) not null
    );
default			默认值
	"""
	所有的字段都可以设置默认值 
		用户不给该字段传值则使用默认的 否则使用传了的
	create table t3(
		id int default 911,
		name varchar(16) default 'jason'
	);
	"""
unique			唯一值
	"""
	单列唯一
		create table t4(
			id int,
			name varchar(32) unique
		);
	联合唯一
		create table t5(
			id int,
			host varchar(32),
			port int,
			unique(host,port)
		);
	"""

2、primary ley （主键）

• InnoDB存储引擎规定了一张表必须有且只有一个主键

"""
	但从约束层面上来说 相当于是 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)
		);
	"""

3、auto_increment（自增）

"""
	由于主键类似于数据的唯一标识 并且主键一般都是数字类型
	我们在添加数据的时候不可能记住接下来的序号是多少 太麻烦
	create table user1(
			id int primary key auto_increment,
			name varchar(32)
		);
	"""



# 自增的特性

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