MySQL-存储引擎-创建表-字段数据类型-严格模式-字段约束-键-02

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL-存储引擎-创建表-字段数据类型-严格模式-字段约束-键-02，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12475字，纯文字阅读大概需要18分钟。

内容图文

目录

• 扩展点
  • 查看服务端字符、IP、端口配置
  • 取消本次错误输入
    • 例外情况
• database 数据库操作
• table 数据表操作
  • 查看MySQL存储引擎
  • 常见几个存储引擎
    • InnoDB
    • MyISAM
    • MEMORY
    • BLACKHOLE
    • 引擎对应的本地化文件
    • 案例
  • 基本操作
    • 创建表的完整语法
• 表记录基础操作
• 严格模式补充
  • 查看数据库配置中变量名包含mode的配置参数
    • 模糊匹配
• 基本数据类型
  • 数据范围
  • 整型
    • TINYINT SMALLINT MEDIUMINT INT BIGINT
    • 应用场景
    • 结合字段验证数据范围及有无符号
    • 类型的宽度与存储宽度的关系验证
  • 浮点型
    • FLOAT DOUBLE DECIMAL
    • 备注
    • 应用场景
    • 三者最大整数位和小数位对比
  • 字符类型(char与varchar)
    • CHAR VARCHAR
    • 区别
    • 应用场景
    • 案例
  • 日期时间类型
    • DATE TIME DATETIME YEAR TIMESTAMP
    • 应用场景
    • 案例
  • 枚举与集合
    • 枚举 enum
    • 集合 set
    • 案例
• 约束条件
  • 常见约束
  • primary key 主键
    • 主键 -- 单字段 primary key
    • 联合主键 primary key(字段1, 字段2...)
    • InnoDB自带主键科普
  • auto_increment 自动递增
    • 自动递增序号问题
  • unique 唯一
    • 单列唯一
    • 联合唯一
  • not null + default 非空与默认值
  • usinged
  • zerofill

MySQL的字符编码设置已在安装章节的配置文件中指定

扩展点

查看服务端字符、IP、端口配置

在mysql客户端输入 \s 可以查看服务端的一些配置信息

取消本次错误输入

在mysql客户端输入 \c 即可取消当前输入的那些语句

例外情况

单双引号必须配对了 \c 与 ; 才能生效

不小心按错 “ ” ‘’ 单双引号后，可以使用 \c 来放弃当前输入的那些内容

database 数据库操作

# 增：
create database db1 charset utf8;
# 查：
show databases;
show create database db1;
# 改：
alter database db1 charset latin1;
#删除: 
drop database db1;

table 数据表操作

查看MySQL存储引擎

不同的数据应该有不同的处理机制

show engines 查看所有的存储引擎

常见几个存储引擎

InnoDB

MySQL 5.5 开始 默认存储引变更为 InnoDB

默认的存储引擎，支持事务，支持行锁，表锁，外键，比较安全

因为 InnoDB 在存储数据的时候，更加安全，所以默认的存储引擎是InnoDB(虽然 MyISAM 比 InnoDB 快)

建表会建两个文件： .frm表结构 .ibd 真实数据

InnoDB存储引擎在建表的时候，要求表必须有且只有一个主键

• 当你没有设置主键的时候，会自上往下寻找非空且唯一的约束字段自动将其升级为主键字段
• 当你的表中没有任何约束（主键也是约束）字段的时候，InnoDB会使用内部的一个隐藏字段作为主键，我们无法利用该主键

MyISAM

老版本用的存储引擎，支持表锁

建表会建三个文件： .frm 表结构，.MVD真实数据，.MYI索引

MEMORY

内存引擎（数据全部存在在内存中，一断电或重启程序数据就丢失）

建表只会建一个文件： frm表结构，数据存在内存中不需要存文件

BLACKHOLE

正如名字所述，任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；select这张表返回内容永远是空。

建表只会建一个文件： frm表结构，数据不存储

感兴趣的小伙伴可以点这个链接了解一下：MySQL的BlackHole引擎在主从架构中的作用

引擎对应的本地化文件

案例

# 查看所有的存储引擎
show engines;

# 查看不同存储引擎存储表结构文件特点
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

基本操作

# 切换文件夹
user db1;
select database();  # 查看当前所在的库
# 增
create table t1(id int,name char);  # 创建出来的可能是多个文件，解耦管理
# 改
alter table t1 modify name char(16);
# 查
show tables;
show create table t1;       # 查看表的详细信息
describe t1; == desc t1;  # 查看表结构
# 删
drop table t1;

创建表的完整语法

# 语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

# 注意：
# 1. 在同一张表中，字段名不能相同
# 2. 宽度和约束条件可选，字段名和类型是必须的
# 3. 最后一个字段后不能加逗号！

# 补充：
# 1.宽度指的是对存储数据的限制
create table userinfo(name char);
insert into userinfo values('jason');
"""
1.没有安全模式的数据库版本，能够存放数据但是只会存进去一个j
2.最新数据库版本直接报错提示无法存储:Data too long for column 'name' at row 1
"""

# 2.约束条件初识>>> null 与 nut null
create table t1(id int,name char not null);
insert into t1 values(1,'j');  # 正常存储
insert into t1 values(2,null);  # 报错

# 总结 类型与约束条件区别
# 类型：限制字段必须以什么样的数据类型存储
# 约束条件：约束条件是在类型之外添加一种额外的限制

表记录基础操作

记录是存在表里的，表又是存在database数据库里的，所以要先有数据库和表才能有表

# 增
insert into db1.t1 values (1,'egon'),(2,'kevin'),(3,'jason');  # into可加可不加，db1可以不指定，默认就是在当前库下
# 改
update db1.t1 set name='DSB' where id > 1;
update db1.t1 set name='DSB' where id = 2 or id = 3;
# 查
select id,name from db1.t1;  # db1可不指定，默认当前库下
select * from t1;
# 删
delete from db1.t1 where id >3;
delete from db1.t1 where name='egon'  # 这里注意如果少了一个引号，后面无论敲什么都没有用了需要将引号补全

严格模式补充

我们刚刚在上面设置了char, tinyint,存储数据时超过它们的最大存储长度，发现数据也能正常存储进去，只是 mysql 帮我们自动截取了最大长度。但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据即可，这样的情况下就需要设置严格模式

备注：

在 5.7 左右以后的MySQL版本中默认就是严格模式

查看数据库配置中变量名包含mode的配置参数

show variables like "%mode%";

sql_mode 即sql严格模式

# 修改安全模式
set session ...  # 只在当前操作界面临时有效
set global ...  # 全局有效，长期有效，设置完需要重启客户端进入

修改当前 sql 模式为严格模式

set global sql_mode =‘STRICT_TRANS_TABLES‘; # 仅仅char时，分组、char_length 需要额外添加，后面博客会有讲到

设置完需要退出客户端重新进入

5.6.45 默认sql_mode：NO_ENGINE_SUBSTITUTION

模糊匹配

关键字 like

• % 匹配任意多个字符
• _ 匹配任意一个字符

基本数据类型

数据范围

整型

TINYINT SMALLINT MEDIUMINT INT BIGINT

应用场景

存储年龄，等级，id，各种号码等

结合字段验证数据范围及有无符号

create table t1(x tinyint);
insert into t1 values(128),(-129);

create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);  

create table t3(x int unsigned);
insert into t3 values(4294967296);

类型的宽度与存储宽度的关系验证

create table t4(x int(8));
insert into t4 values(4294967296123);

# 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);

# create table t6(id int(10) unsigned);
# create table t7(id int(11));

结论

对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度， 默认的显示宽度，足够显示完整当初存放的数据

浮点型

FLOAT DOUBLE DECIMAL

备注

精确度：float < double < decimal

根据精度选择类型或者转成字符串存储

应用场景

身高，体重，薪资

字段限制特点(5,3)前一位表示所有的位数，后一位表示小数个数

三者最大整数位和小数位对比

字符类型(char与varchar)

CHAR VARCHAR

区别

char(4)

最大只能存4个字符，超出会直接报错（严格模式）或截取，如果少了，会自动用空格填充

varchar(4)

最大只能存4个字符，超出会直接报错（严格模式）或截取，如果少了，有几个字符存几个字符

应用场景

姓名，地址，描述类信息

案例

create table t10(name char(4))  # 超出四个字符报错，不够四个字符空格补全
create table t11(name varchar(4))  # 超出四个字符报错，不够四个有几个就存几个

# 验证存储限制
insert into t12 values('hello');
insert into t13 values('hello');
# 验证存储长度
insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'
select * from t12
select * from t13  # 无法查看真正的结果

select char_length(name) from t12
select char_length(name) from t13  # 仍然无法查看到真正的结果

char_length()

mysql在存储char 类型字段的时候，硬盘上确确实实存的是固定长度的数据，但是再取出来的那一瞬间mysql 会自动将填充的空格去除

可以通过严格模式来修改该机制，让其不自动做去除处理

# 如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
# 退出客户端重新登陆
select char_length(x) from t12; # 4
select char_length(y) from t13; # 1

# 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格

char 与 varchar 的区别

站在二进制数据读取的角度来讲（数据存成文件都是二进制的，文本编辑器等打开都是软件做了处理），char指定了长度，直接按固定的长度读取即可，而varchar无法知道数据有多长，有个标志位来标识多长，要识别等，所以耗费的时间就会相对较长一点

char

字段定长，就算只存了一个字符，还是会占用字段宽度的自负空间，会浪费空间

取的时候方便，按固定字符数存取数据快

varchar

在存的时候根据字段记录实际长度存储（不超过指定长度），比较节省空间

取的时候繁琐，不知道数据到底多长，存储速度相对char慢，（类似报头来确定长度）

前几年大量用char，近几年varchar比较多（无所谓哪个好那个不好，知道优缺点就行了）

"""
char与varchar的使用区别
"""
name char(5)
# 缺点：浪费空间
# 优点：存取速度都快
egon alex lxx  jxx  txx  

name varchar(5)
# 缺点：存取速度慢
# 优点：节省空间
1bytes+egon 1bytes+alex 1bytes+lxx  1bytes+jxx  1bytes+txx 

日期时间类型

DATE TIME DATETIME YEAR TIMESTAMP

应用场景

日期、时间

案例

create table student(
    id int,
  name char(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);
insert into student values(1,'egon','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');

推荐博客：mysql的5种时间类型的比较

枚举与集合

枚举 enum

限制某个字典能够存储的数据内容只能是指定的几个中的一个(多选一)

集合 set

限制某个字段能够存储的数据内容只能是指定的内容中的某几个（多选多）

以字符串的形式传入多个值，每个值之间用 , 隔开，不要乱加空格等（在字符串里加东西是什么概念？）

案例

create table user(
  id int,
  name char(16),
  gender enum('male','female','others')
);
insert into user values(1,'jason','xxx')  # 报错
insert into user values(2,'egon','female')  # 正确！


create table teacher(
  id int,
  name char(16),
  gender enum('male','female','others'),
  hobby set('read','sleep','sanna','dbj')
);
insert into teacher values(1,'egon','male','read,sleep,dbj')  # 集合也可以只存一个

约束条件

约束条件主要是用于保证数据的完整性和一致性

常见约束

PRIMARY KEY (PK)  # 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)  # 标识该字段为该表的外键
NOT NULL  # 标识该字段不能为空
UNIQUE KEY (UK)  # 标识该字段的值是唯一的
AUTO_INCREMENT  # 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT  # 为该字段设置默认值
UNSIGNED  # 无符号
ZEROFILL  # 使用0填充

可以有联合主键PRIMARY KEY、UNIQUE

primary key 主键

主键 -- 单字段 primary key

限制效果跟 not null + unique 组合效果一致，非空且唯一

primary key 也是 innodb 引擎查询必备的索引（类似于书的目录）

通常每张表都应该有一个id（stu_id等都行）字段，并且将id字段设置为表的主键字段

联合主键 primary key(字段1, 字段2...)

多个字段联合起来作为表的一个主键，本质还是一个主键

InnoDB自带主键科普

primary key也是innodb引擎查询必备的索引
    索引你就把当成书的目录
    
innodb引擎在创建表的时候 必须要有一个主键
当你没有指定主键的时候
    1.会将非空切唯一的字段自动升级成主键
    2.当你的表中没有任何的约束条件  innodb会采用自己的内部默认的一个主键字段
        该主键字段你在查询时候是无法使用的
        查询数据的速度就会很慢
        类似于一页一页的翻书
        
        create table t19(
            id int,
          name char(16),
          age int not null unique,
          addr char(16) not null unique
        );
        
主键字段到底设置给谁呢???
    通常每张表里面都应该有一个id字段
    并且应该将id设置为表的主键字段

联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键!!!
    ps:innodb引擎中一张表有且只有一个主键


create table t20(
        ip char(16),
      port int,
      primary key(ip,port)
    );
    desc t20;


主键字段应该具备自动递增的特点
    每次添加数据  不需要用户手动输入

auto_increment 自动递增

该约束条件只能加在被设置成 key 的字段上，不能单独使用，通常都是跟 primary key 联用

主键字段应该具备自动递增的特点，每次添加数据，不需要用户手动输入

自动递增序号问题

删除数据后，序号不会接着上一条存在数据往下递增，而是从上一次的最大序号开始递增

delete from 清空表仅仅是删除数据，不会重置 id

delete from tb1; # 不加条件默认删除表内所有记录

truncate 清空并初始化表，可以重置主键

truncate table tb1; # 清空表，id字段会重置

unique 唯一

单列唯一

限制某个字段是唯一的

联合唯一

在语句的最后，用括号的形式，表示哪几个字段组合的结果是唯一的（应用场景： ip + port）

# 单列唯一
create table user1(
    id int unique, 
  name char(16)
);
insert into user1 values(1,'jason'),(1,'egon')  # 报错
insert into user1 values(1,'jason'),(2,'egon')  # 成功

# 联合唯一
create table server(
    id int,
  ip char(16),
  port int,
  unique(ip,port)
)
insert into server values(1,'127.0.0.1',8080);
insert into server values(2,'127.0.0.1',8080);  # 报错
insert into server values(1,'127.0.0.1',8081);

not null + default 非空与默认值

create table user(
    id int,
  name char(16)
);
insert into user values(1,null)  # 可以修改

alter table user modify name char(16) not null;
insert into user(name,id) values(null,2);  # 报错 插入数据可以在表名后面指定插入数据对应的字段

create table student(
    id int,
  name char(16) not null,
  gender enum('male','female','others') default 'male'
)
insert into student(id,name) values(1,'jason')  # 成功

usinged

无符号，修饰整形和浮点型的数据类型，只存整数，且范围变大

zerofill

给指定了宽度的数据类型的不足长度的字段不足部分用0填充

修改约束条件，不够8位用0填充（zerofill）,会自动加上 unsigned

alter table tb1 modify id int(8) zerofill;

MySQL-存储引擎-创建表-字段数据类型-严格模式-字段约束-键-02

标签：图片   not   信息   初始化   无法   外键   where   重启   推荐   

本文系统来源：https://www.cnblogs.com/suwanbin/p/11396365.html

内容总结

以上是互联网集市为您收集整理的MySQL-存储引擎-创建表-字段数据类型-严格模式-字段约束-键-02全部内容，希望文章能够帮你解决MySQL-存储引擎-创建表-字段数据类型-严格模式-字段约束-键-02所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端