第三章 MySQL表结构的管理

3.1数据类型

MySQL数据库的数据类型包括：数值型、日期时间型、字符串型、集合型。最常用的是数值型，日期时间型以及字符串型这三个大类。如图可以明确归类此三种类型。

• decimal(D, M)用于表示精度确定（小数点后数字的位数确定）的小数类型。
• D决定了该小数的最大位数，M用于设置精度（小数点后数字的位数）。
• decimal (5,2)表示小数取值范围：-999.99 ～999.99
• decimal (5,0)表示： -99999～99999的整数。

3.1.1 字符串

数据类型	取值范围	说明
char(M)	0~255个字符	固定长度为M的字符串，其中M的取值范围为0~255。
varchar(M)	与字符集有关	变长字符串类型，占用的储存空间就是字符串自身占用的储存空间，与m无关
text	0-216-1	储存大量的字符串
blob	0-216-1	储存图片、视频、音频等

• char()与varchar()的区别

1.char的长度是不可变的，而varchar的长度是可变的
类型char(10)   输入值为：abc，存储为：abc+7个空格
类型varchar(10) 输入值为：abc，存储为：abc （自动变为3个的长度）

2.超出长度自动截取
类型char(3)值为abcdefg，存储为：abc（defg自动删除）
类型varchar(3)值为abcdefg，存储为：abc （defg自动删除）

3.char最多可以存放255个字符，varchar的最大长度为65535个字节（mysql5.7）
4.char和varchar占用的储存空间  GBK1个汉字占2个   UTF8占3个  
例如一个‘中’字，varchar(255)仅仅占用1个字符（两个字节）的储存空间；而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。 

3.1.2 日期类型

数 据 类 型	取 值 范 围	说 明
DATE	1000-01-01 9999-12-31	日期，格式YYYY-MM-DD2022-03-15
TIME	-838:58:59 835:59:59	时间，格式HH：MM：SS20:01:52
DATETIME	1000-01-01 00:00:009999-12-31 23:59:59	日期和时间，格式YYYY-MM-DD HH：MM：SS2022-03-15 20：01:52
TIMESTAMP	1970-01-01 00:00:00UTC到2038-01-01 00:00:00UTC	时间标签
YEAR	1901-2155	年份可指定两位数字和四位数字的格式，乳2022

• datetime与timestamp都是日期和时间的混合类型，区别在于：表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围。将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

3.1.3复合类型

数 据 类 型	取 值 范 围	说 明
SET	多选多（64）	性别设置为set类型 {唱歌，跳舞，游泳，学习，购物}
ENUM	多选一（65535）	爱好设置为enum类型 {男，女}

3.1.4二进制类型

• BLOB：二进制字符串、可以保存照片、音乐、视频、文件等、没有字符集
• TEXT：非二进制字符串、只可以存纯文本、有字符集

3.1.5如何选择合适的字符串类型?（性质、取值范围、精度）

(1)身份证号(18)位电话号码 用字符串储存，不建议使用整数尽量采用精确小数decimal();

(2)采用日期和时间储存时间数据，不建议使用字符串储存

(3)尽量避免NULL字段，建议将字段指定为NOT NULL约束

(4)尽可能用NOT NULL定义字段约束，避免允许字段为NULL值。比如InnoDB存储引擎中NULL既需要额外存储开销，又要增加磁盘IO次数和计算开销。

3.2 创建表

3.2.1 设计表遵循的原则

（1）字段名要通俗易懂且具有代表性，为了程序设计方便，尽量使用英文名字。

（2）选择合适的字段类型。比如age用tinyint而不是int；不参加数学计算的数字定义为字符串型，如编号、身份证号、电话号码等。

（3）字段长度应足以容纳该字段的最大值，设置过短存在截断风险，设置过长会造成存储空间浪费；

（4）当数据表中存储的内容包含中文字符时，建议采用UTF8或GBK字符集编码，避免出现乱码。

3.2.2语法格式

1.创建表

create table [if not exists] 表名 (
字段名  数据类型 [约束条件 ],
字段名 数据类型  [约束条件 ],
... ...
[其他约束条件]  
)[储存引擎]  [表字符集];

2.约束

约束是保证数据库表中数据的完整性和一致性的手段。

当对表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则的话，系统就会拒绝执行这个操作。

数据完整性分为：

①实体完整性，主键中属性值不能为空（最基本要求）；

②参照完整性，外键要么为空要么关联实际存在的元组；

③自定义完整性，根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件。

---

①主键约束（字段值不为空，不重复）

②字段名 数据类型 primary key,

③非空约束(字段必须有数据)

④字段名 数据类型 not null,

⑤默认值(字段没有输入数据，直接存入默认值)

⑥字段名 数据类型 default,

⑦唯一约束(字段值可以为空，不重复)

⑧字段名 数据类型 unique,

⑨外键约束 (两个表建立联系，A表外键字段的取值，要么是空null，要么只能来自于表B主键字段的取值)

constraint <外键名> foreign key字段名  
references <主表名> 主键列,

3.2.3 自增

默认情况下，MySQL自增型字段的值从1开始递增，且步长为1。一个表只能有一个字段使用auto_increment 约束，且该字段必须为主键的一部分。

设置自增型字段的语法格式如下：

字段名 数据类型 auto_increment
例如：id  int  auto_increment primary key,

3.2.4 非空约束（Not Null，NK）

语法如下：

create table 表名（
	属性名  数据类型  not null
	... ...
）；

3.2.5默认值(default)

语法如下：

create table 表名（
	属性名  数据类型  default 默认值，
	... ...
）；

3.2.6唯一约束（unique，UK）

create table 表名 (
  列名 数据类型 ... ,
  列名 数据类型 unique,
  ......
);

说明：该方法不能指定约束名。可以使用【show index from 表名; 】命令查看索引名。

3.2.7主键约束（primary key,PK）

用于定义主表和从表之间的关系，外键约束要定义 在 从 表 上 ， 主 要 则 必 须 具 有 主 键 约 束 或 是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

语法格式：

create table 表名（
属性名  数据类型  primary key，
	... ...
）；

（1）主键是单个字段。

字段名 数据类型 primary key

例如：

student_no char(11) primary key

（2）主键是多个字段的组合。

primary key (字段名1, 字段名2)

3.2.8 外键约束（foreign key）

用于定义主表和从表之间的关系，外键约束要定义 在 从 表 上 ， 主 要 则 必 须 具 有 主 键 约 束 或 是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

语法如下：

alter table 表名

add constraint 外键约束名

foreign key(外键字段 )

references 主表(主键字段 );

3.2.9检查约束（check）

用于强制行数据必须满足的条件。

CHECK <[表达式]>或CHECK(<检查约束>)

• 其中：<表达式>指的就是 SQL 表达式，用于指定需要检查的限定条件。

• 注意：若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件

• 在修改表时添加检查约束语法如下：

alter table  表名  add constraint  检查约束名  check(检查表达式);

3.3 复制表

1.复制表结构：字段名 数据类型 约束条件

create table <新表名> like <旧表名>;

2.复制表结构+表记录：

create table <新表名> select *from <旧表名>;

字段名 数据类型 约束条件

表数据

3.4 查看表结构

desc 表名;

show create table 表名;

3.5 修改表结构

3.5.1添加新字段

1.向表添加新字段时，通常需要指定新字段在表中的位置。向表添加新字段的

语法格式如下：

alter table 表名 add  <字段名> <字段类型> ;

2.在表的第一个位置增加字段

语法形式如下：

alter table 表名 add 字段名 数据类型 first；

3.在表的指定字段之后增加字段

语法形式如下：

alter table 表名 add 字段名 数据类型 after 字段名；

3.5.2修改字段名

1.修改字段名（及数据类型）的语法格式如下。

alter table 表名 change 旧字段名 新字段名 新数据类型 ;

2.对字段数据类型进行修改语法格式如下:

alter table 表名 modify 字段名 新数据类型 ;

3.5.3删除字段

语法格式如下:

alter table 表名 drop 字段名;

3.5.4修改表名

语法格式如下:

alter table 表名 rename  [to] 新表名;

3.5.5修改字段默认值

语法格式如下:

alter table 表名 alter  字段名set  default '值'；

3.5.6修改表的其他选项

alter table 表名 engine=新的存储引擎类型;

alter table 表名 default charset=新的字符集;

alter table 表名 auto_increment=新的初始值;

1.主键约束(primary key)

用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。一张表最多只能有一个主键。

语法如下：

alter table 表名
add constraint 主键约束名
primar y key(主键字段);

2.外键约束(foreign key)

用于定义主表和从表之间的关系，外键约束要定义在从表上,主要则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

语法如下：

alter table 表名
add constraint 外键约束名
foreign key(外键字段)
references 主表(主键字段);

3.唯一约束(unique)

当定义了唯一约束后，该列值是不能重复的，但是可以为null。

语法如下：

alter table 表名
add constraint 唯一约束名
unique(唯一字段);

3.5.7约束条件删除

1.删除主键约束（一张表一个主键，联合主键也只有一个主键）

alter table 表名 drop primary key;

2.删除唯一约束（约束名默认字段名）

alter table 表名  drop index 约束名；

3.删除外键约束

alter table 表名 drop foreign key 约束名;

3.6 删除表

删除表的SQL语法格式比较简单。唯一需要强调的是删除表时，如果表之间存在外键约束关系，此时需要注意删除表的顺序。

语法如下：

drop table 表名;