MySQL字段约束条件、字段类型、存储引擎、配置文件

字符编码与配置文件

# 查看MySQL默认字符编码
\s
'''
如果是5.X系列 显示的编码有很多种
Latin1
gbk
如果是8.X系列 显示的统一是utf8mb4
      utf8mb4是utf8优化版本 支持存储表情
'''
# 统一字符编码
    5.X默认编码有多种 可能会导致乱码的情况 所以应该统一编码
      my-default.ini配置文件
  步骤1:拷贝一份该配置文件并修改名称为my.ini
  步骤2:清空my.ini文件内的内容
  步骤3:添加固定的配置信息即可
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8
  步骤4:保存并重启服务端即可生效
    net stop mysql
    net start mysql

存储引擎

# 什么是存储引擎
    存储引擎可以理解为处理数据的不同方式
      eg:
      有一个a.txt文件
          戴某某会放在密码箱中
        张某某会转成pdf存储
        李某某会做多个备份
        潘某某会制作封面美化等
# 查看存储引擎
    show engines;
# 需要了解的引擎
    MyISAM
      5.1之前版本MySQL默认的存储引擎
    特点:存取数据的速度快 但是功能很少 安全性较低
  InnoDB
      5.1之后版本MySQL默认的存储引擎
    特点:有诸多功能 安全性较高 存取速度没有MyISAM快
  BlackHole
      任何写入的数据都会立刻消失(类似于垃圾回收处理站)
  Memory
      以内存作为数据存取地 速度快但是断电立刻丢失 
# 自定义选择存储引擎
    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;

创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件,
    字段名2 字段类型(数字) 约束条件,
  字段名3 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件可以写多个 空格隔开即可(后面详细讲解)
    字段名1 字段类型(数字) 约束条件1 约束条件2 约束条件3
4.最后一行字段结尾不能加逗号
    极其容易被忽略!!!

字段类型之整型

# 整型
  tinyint                                1bytes                
  smallint                        　　　　2bytes
  int                                    4bytes
  bigint                            　　　8bytes
  上述整型的区别在于从上往下能够存储的数字范围越来越大
  注意事项
      1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位
    2.注意手机号如果使用整型来存储 需要使用bigint才可以
"""
工作小技巧:有时候看似需要使用数字类型存储的数据其实可能使用的是字符串
    因为字符串可以解决不同语言对数字不精确的缺陷!!!
"""
    create table t5(id tinyint);
  insert into t5 values(-129),(256);
  # 如果是在5.6版本不会报错 会自动处理成最大范围(没有意义)
      步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
    步骤2:退出客户端 重新登录即可
  # 如果是在5.7及以上版本 则会直接报错(更加合理)
  验证(结论)发现所有的整型都默认带有正负号 如何修改不带正负号(约束条件)
  create table t6(id tinyint unsigned);

 

 

 

 

字段类型之浮点型

# 浮点型
float      double         decimal
精确度是越来越高
float(255,30)    总共255位 小数占30
double(255,30)    总共255位 小数占30
ddecimal(65,30)    总共65位    小数占30

create table t3(id float(255,30));
create table t4(id double(255,30));
create table t5(id decimal(65,30));
insert into t3 values(2.2222222222222);
insert into t4 values(2.2222222222222222222);
insert into t5 values(2.22222222222222222222);
有上述结果可知
decimal>double>float

"""
虽然三者精确度有差距 但是具体用哪个应该结合实际情况
比如正常业务 使用float足够
如果是高精尖 可以使用decimal
"""

 

 

 

 

 

字段类型之字符类型

# 字符类型
char varchar
区别在于一个定长一个是变长
eg:
char(4)
定长 最大只能存储四个字符 超出则报错 不够则空格填充至四个
varchar(4)
变长 最大只能存储四个字符 超出则报错 不够则有几个存几个
验证定长与变长特性
create table t6(name char(4));
create table t7(name varchar(4));
insert into t6 values('jason');
insert into t7 values('jason');
# 如果是5.6版本并且没有修改严格模式 则会自动截取四个字符(不合理)
# 临时修改
步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
步骤2:退出客户端 重新登录即可
# 永久修改
修改my.ini配置文件
sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
重启服务端之后永久生效
# 工作中使用char还是varchar
char 整存整取 速度快 会造成一定储存空间浪费
varchar 节约储存空间
存取数据的速度没有char快（取数据不知道的精确长度）

 

 

 

 

数字的含义

字段类型括号内数字大部分情况下是用来限制储存的长度
但是在整型中并不是用来限制长度 而是用来控制展示长度

create table t9(id int(3));
insert into t9 values(1111);    不会报错

create table t10(id int(3) zerofill); # 位数不够用0填充
insert into t10 values(1);    # 001
insert into t10 values(1111);  # 有几位就展示几位
"""
结论
    以后涉及到整型字段 都无需自己定义长度 直接使用自带的即可
    而针对其他类型的字段 则需要自己添加数字
"""

 

 

 

 

字段类型之枚举与集合

# 枚举
    多选一
  create table t11(
      id int,
    name varchar(32),
    gender enum('male','female','others')
  );
  '''插入数据的时候 针对gender只能填写提前定义好的数值'''
  
# 集合
    多选多(也可以多选一)
  create table t12(
      id int,
    name varchar(32),
    hobby set('篮球','足球','双色球','排球','水球','肉球')
  );

 

 

字段类型之日期类型

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

create table t13(
    id int,
  name varchar(32),
  reg_time datetime,
  birth date,
  study_time time,
  join_time year
);
# 针对时间数据一般都是通过代码自动获取并添加 我们这里手动模拟
insert into t13 values(1,'jason','2002-12-12','2003-01-21','11:11:11','2015');

 

 

 

约束条件

 

 

 

# 字段类型与约束条件的关系
    约束条件是基于字段类型之上的额外限制
      eg: id int unsigned
      字段类型int规定了id字段只能存整数 
      约束条件unsigned指的是整数基础之上还必须是正数
 
# 1.无需正负号
    unsigned
# 2.零填充
    zerofill
# 3.非空
    not null 
    create table t17(id int,name varchar(32));
  '''插入数据的另外一种方式 打破字段顺序'''
  insert into t17(name,id) values('jason',1);
  insert into t17(id) values(2);
  
  create table t18(id int,name varchar(32) not null);
  insert into t18(id) values(2);  # 报错
  insert into t18(id,name) values(2,null);  # 报错
  insert into t18(id,name) values(2,'');  # 不报错
# 4.默认值
    default
  create table t19(id int,name varchar(32) default 'jason');
  insert into t19(id) values(1);
  insert into t19(id,name) values(2,'kevin');
# 5.唯一值
    unique
  '''单列唯一:某个字段下对应的数据不能重复 是唯一的'''
  create table t20(
    id int,
    name varchar(32) unique
  );
  '''多列唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的'''
  create table t21(
      id int,
    host varchar(32),
    port int,
    unique(host,port)
  );
# 6.主键
    primary key
  """
  1.单从约束层面上而言 相当于not null + unique(非空且唯一)
      create table t22(id int primary key);
  2.是InnoDB存储引擎规定的一张表有且必须要有一个主键 用于构建表
      主键可以加快数据的查询速度(类似于书的目录)
      如果创建表创建的时候没有设置主键也没有其他的键 那么InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而无法使用 即无法加快数据查询)
      如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
          create table t23(
              tid int,
              pid int not null unique,
              cid int not null unique
          );
  """
  结论:创建表应该有一个序号字段(id\pid\cid)并且应该将该字段设置成主键
  create table t24(
      id int primary key,
    name varchar(32)
  );
  # 也可以有联合主键(多个字段组合 本质还是一个主键) 了解即可
  create table t24(
      id int,
    name varchar(32),
    pwd int,
    primary key(id,pwd)
  );
# 7.自增
    auto_increment  
  专门配合主键一起使用 用户以后在添加数据的时候就不需要自己记忆主键值
  create table t25(
      id int primary key auto_increment,
    name varchar(32)
  );
"""
总结
    以后在创建规范的表的时候
        一般都会有一个主键字段的编写如下
            id int primary key auto_increment
"""