MySQL基本数据类型&约束条件

1. 存储引擎

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

# 查看存储引擎的方式
    show engines;

# 需要掌握的四个存储引擎

"InnoDB"
MySQL5.5之后默认的存储引擎
    支持事务，行级锁，外键，针对数据的操作更加安全。

"MyISAM"
MySQL5.5之前默认的存储引擎
    不支持事务，行级锁，外键，针对数据的操作较于innodb不够安全
    数据的存取速度较于innodb更快

"BLACKHOLE"
写入其中的数据都会立刻小时，类似于垃圾处理站。

"MEMORY"
基于内存的存储引擎，基于内存存取数据
    速度快，但是断电立刻丢失，安全性最差
    # 所以一般只做临时存储。


# 存储引擎创建表的不同点。

create table t1(id int)engine=myisam;
"""
myisam会创建三个文件
    .frm    表结构文件
    .MYD    表数据文件
    .MYI    表索引文件（索引用来加快数据查询）
"""


create table t2(id int)engine=innodb;
"""
innodb会创建两个文件
    .frm    表结构文件
    .ibd    表数据和表索引文件
"""

create table t3(id int)engine=memory;
"""
memory会创建一个文件
    .frm    表结构文件
"""

create tablet4(id int)engine=blackhole;
"""
blackhole会创建一个文件
    .frm    表结构文件
"""

2. MySQL基本数据类型

2.1 整型&浮点型

# 整型

tinyint,smallint,int,bigint

不同的int类型能够存储的数字范围是不一样的。
    1. 要注意是否存负数(正负号要占一个比特位)
    2. 针对手机号码要使用binint(如果非要存int类型的话)
"研究默认是否需要正负号"
create table t5(id tinyint);
insert into t5 values(-444),(999);
# 结论：所有的int类型默认都需要正负号

create table t6(id tinyint unsigned);
# 这样就 没有负号了，只存储正数
insert into t6 values(-444),(888);


# 浮点型
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 t8(id decimal(65,30));

insert into t7 values(1.11111111111);
insert into t8 values(1.11111111111);
insert into t9 values(1.11111111111);
# 结论：三者的精确度不同
decimal > double > float

"""
到底使用哪个
    一般小数点后面只保留两位，所有float足够。
    如果从事高精密业务，则需要考虑更高的精确度

ps：有时候很多看似需要数字存储的数据，可能都是存的字符串。

"""
# python本身对数字的精确度很低，之所以能够从事人工智能和数据分析，得益于相应的功能强大的模块。

2.2 字符类型

# 字符类型
char(4)
    定长类型，最多只能存4个字符，多了报错，少了自动空格填充至4个。

varchar(4)
    变长类型，最多只能存4个，多了报错，少了则有几个存几个

"研究上述特性"
create table t10(id int,name char(4));
create table t11(id int,name varchar(4));
insert into t10 values(1,'joshua');
insert into t11 values(1,'陈嘉学');

# 针对5.6版本超出范围不会报错，而是自动帮你截取并保存，其实不合理，应该报错。
# 可以做如下修改
方式一：修改配置文件（永久）
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

方式二：命令修改（临时）
    # 查看
    show variables like '%mode%';
    # 修改
    set session  # 当前窗口有效
    set global   # 当前服务端有效
        set global sql_mode='strict_trans_tables';
    退出客户端，重新进入
    再次执行上述插入命令，就会直接报错了。


# 研究定长与变长特性
insert into t10(1,'j');
insert into t11(1,'t');

# 统计某个字段数据的长度char_length()
select char_length(name) from t20 where id=1;

# 底层确实会填充，但是取出来的时候又会自动去除
set global
sql_mode='strict_trans_tables,pad_char_to_full_length';

"char 与 varchar的对比"
char
    优势：整存整取，速度快
    劣势：浪费存储空间

varchar
    优势：节省存储空间
    劣势：存取数据速度较char慢

##     为什么呢？？？？？？？？？？？？？？
"""
char(5)
    jasontony kevintank tom  oscar
varchar(5)
    1bytes+jason1bytes+tony1bytes+kevin1bytes+tank1bytes+tom1bytes+oscar
    这 1bytes的报头存放了后面数据的长度
    存：先计算数据的长度
    取：先获取报头的数据

"""
# 以前几乎都是char，现在很多情况用varchar
    进公司之后，会通过邮件告诉你每个字段的英文名，中文名以及类型等项目的诸多信息。


"补充知识点：" comment 给字段加中文注释。
create table tt(id int comment "序号",name varchar(16) comment "姓名")

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

create t20(id int(3));
insert into t20 values(33333);

"""
在整型中括号内的数字并不是限制存储的长度，
而是用来控制展示的长度，不到3位0填充

我们以后在定义整型字段的时候，不需要自己添加数字，使用默认的就可以。
"""
create table t22(id int(3) zerofill);
insert table t22(4);

# 结论：整型比较特殊，是唯一 一个不是用来限制存储长度的类型。

2.4 其他数据类型

# 枚举 与 集合类型

枚举    # 多选一
enum()
create table user(
    id int,
    name varchar(32),
    gender enum("male","female","other")
);
insert into user values(1,'joshua','male');
insert into user values(1,'joshua','男')  # 报错


集合    # 多选多（包含多选一）
set()
create table userinfo(
    id int,
    name varchar(32),
    hobby set('basketball','football','dbcball')
);
insert into userinfo values(1,'tony','football');
insert into userinfo values(1,'tony','football basketball');  # 多选
# 注意： 不在集合中的一样会报错。


# 日期类型
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,'joshua','2000-12-22','2000-1-2 11:23:00','11:23:00',1998);

3. 约束条件

前言：创建表的完整语法

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

"""

约束条件：

"""
约束条件相当于是在字段类型的基础之上添加额外约束。
    eg: id int unsigned
"""

unsigned   # 无符号，让数字无正负号
zerofill   # 多的使用数字0填充
not null   # 不能为空，使用频率非常高！！！
"""
新增数据表的
方式1：按照字段顺序一一传值。
    insert into t1 values()
	
方式2：自定义传值顺序，甚至不传
    insert into t1(name,id) values('tony',1);
    insert into t1(id) values(2);
	
"""
在MySQL中不传数据会使用关键字 NULL填充，意思就是空，类似于python的None。
create table t2(
    id int,
    name varchar(32) not null
);
# 此时再给不给name传值就会报错了。


"default    默认值"
所有的字段都可以设置默认值
用户不给该字段传值则 使用默认的，否则使用传了的
create table t3(
    id int,
    name varchar(32) default 'jj',
    age int default 18
);


"unique    唯一值"
# 单列唯一：
create table t4(
    id int,
    name varchar(32) unique
);

# 联合唯一：
create table t5(
    id int,
    host varchar(16),
    port int,
    unique(host,port)
);


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

# innodb存储引擎规定了一张表有且只有一个主键。
# 因为innodb是通过主键的方式构造表的，如果没有设置主键，
分情况讨论：
    情况1：没有主键和其他约束条件
        InnoDB会采用隐藏的字段作为主键，不能加快数据查询

    情况2：没有主键但是有非空且唯一的字段
        则 自动将该字段升级为主键
        create table t6(
            in int,
            age int not null unique,
            pwd int not null unique
        );
        # 此种情况会把age升级为主键，从上到下的第一个。

"结论："
以后创建表的时候一定要设置主键
并且主键字段一般是表的 id字段(名字不唯一)
create table user(
    id int primary key,
    name varchar(32)
);


"auto_increment    自增"   # 只能给主键用
由于主键是类似于数据的唯一标识，并且主键一般都是数字类型，
我们再添加数据的时候不可能记住接下来的序号是多少，所以可以用 auto_increment
create table users(
    id int primary key auto_increment,
    name varchar(32)
);

"自增的特性！！！"
1
2
3
4  # 被删了，就会继续5不会再有4
5

自增不会因为删除操作而回退
delete from 无法影响自增

truncate tablename;
# 清空表数据，并且回归到初始位置，重置主键值。