MySQL基础知识一：数据表的创建、维护、删除

1.创建数据表

1.1 数据类型

1）字符类型

数据类型	是否需要声明长度	实际存储长度	空余部分处理	适用情况
char	√	声明时定义的长度	自动补空格	字符长度固定
varchar	√	实际值的长度+1 / +2	不处理	字符长度可能很大也可能很小，但最大长度已知
text	×	实际值的长度+2	不处理	不知道最大长度

数据的检索效率：char>varchar>text

char、varchar、text最大存储的字节数均为65535，采用utf8编码可保存65535/3=21844个字符；采用utf8mb4编码可保存65535/4=16383个字符

不同大小text数据类型	大小（字节）
tinytext	256
text	65535
mediumtext	16777215
longtext	4294967295

• char数据类型

char类型用于存储固定长度的字符串。声明时指定最大字符串长度，此后一直不变。（若赋予长度较短的值，空余部分用空格自动填充）

• varchar数据类型（最常用）

1. varchar存储变化长度字符串。该类型字段的长度会根据实际字符数据的长度自动调整。（该列的字符串长度小于定义时的长度，空余部分不会使用空格填充。）

• 当varchar长度在0-255字节时，该类型实际长度为值的实际长度+1，用一个字节保存实际使用的长度
• 当varchar长度大于255字节时，该类型实际长度为值的实际长度+2，用2个字节保存实际使用的长度

2. mysql5.0.3以前版本varchar(n)中的n表示字节数；mysql5.0.3以后版本varchar(n)中的n表示字符数。最大长度为65535字节。采用utf8编码可保存65535/3=21844个字符；采用utf8mb4编码可保存65535/4=16383个字符

• text数据类型

存储变化长度的字符串。事先并不知道最大长度是多少，例如简介、文章内容等情况。

2）时间和日期类型

时间和日期类型	占用空间	日期格式	最小值	最大值	零值表示
datetime	8 字节	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
timestamp	4 字节	YYYY-MM-DD hh:mm:ss	19700101080001	2038 年的某个时刻	00000000000000
date	4 字节	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
time	3 字节	hh:mm:ss	-838:59:59	838:59:59	00:00:00
year	1字节	YYYY	1901	2155	0000

DEFAULT CURRENT_TIMESTAMP 可在创建表的时候指明默认值为当前系统时间

• datetime数据类型（最常用）

表示年月日时分秒。是date与time的结合。与时区无关，记录日期年份长。默认null。
1.只保存格式正确的日期，因此可保存非法日期。

1）1000-00-00 00:00:00 √
2）9999-00-00 00:00:00 √
3）9999-13-00 00:00:00 ×月份最大12
4）9999-00-32 00:00:00 ×日期最大31

• timestamp数据类型

表示年月日时分秒。对应的数字值表示时间戳。与时区相关，更能反映当前时间，记录日期年份短。默认null。
1.插入日期时会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。
2.只保存合法的日期，且日期不能早于1970年，不能晚于2037年。

1）2000-00-00 00:00:00 ×不存在0月和0日,错误日期
2）1900-01-12 00:00:00 ×日期早于1970年
3）2038-12-31 00:00:00 ×日期晚于2037年

3）数值数据类型

• decimal数据类型

可表示自定义精度的小数。decimal(m,n) 前者m表示整数位的位数，后者n表示小数位的位数。

decimal(5,1)可表示的范围是-99999.9 到 99999.9

4）enum数据类型

不推荐使用。可以用tinyint代替
1.容易混淆 因为enum底层是用整型保存的，每个枚举值都有一个对应的索引，从 1 开始。如果用 enum 来存储字符串 ‘3’、‘2’、‘1’ 这样的数字字符串，那对应的索引就是 1、2、3，要是不熟悉的开发人员，很容易就会造成混淆，新增错误的数据。
2.修改字段频繁 每增加一个类型就需要修改当前字段，代价太大。一般这种存储类型的字段建议用 tinyint 来存储。

1.2 创建数据表sql命令

create table user_table(
	user_id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	username varchar(20) NOT NULL UNIQUE COMMENT '用户名',
	sex enum('男','女') NOT NULL,
	create_datetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

create table book_table(
	book_id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	bookname varchar(20) NOT NULL UNIQUE 
);

create table buy_table(
	user_id int(10),
	book_id int(10) NOT NULL,
	PRIMARY KEY(user_id,book_id),
	FOREIGN KEY(user_id) REFERENCES user_table(user_id),
	FOREIGN KEY(book_id) REFERENCES book_table(book_id)
);

• AUTO_INCREMENT 自增
• PRIMARY KEY 主键
• UNIQUE 唯一
• NOT NULL 非空
• FOREIGN KEY 外键
• COMMENT 注释
• NOT NULL 非空
• DEFAULT 默认

2.维护数据表

2.1 增加、修改和删除字段

1）增加一个/多个字段

ALTER TABLE book_table ADD(price DECIMAL(5,1),publisher VARCHAR(255));

• 若声明VARCHAR(255)则不能添加UNIQUE约束，会报错[Err] 1071 - Specified key was too long; max key length is 767 bytes
  该错表明超出索引字节的限制，这是由于在MySQL 5.5中引入了innodb_large_prefix，用来禁用大型前缀索引。对于使用REDUNDANT或COMPACT行格式的InnoDB表，索引键前缀长度限制为767字节。在utf8mb4字符编码中，767/4 = 191个字符，故该唯一字段不能超过191个字符。

2）修改字段属性

ALTER TABLE book_table MODIFY price DECIMAL(5,2);

3）修改字段名称

ALTER TABLE book_table CHANGE publisher publish VARCHAR(50) NOT NULL;

• 相当于删除该字段重新建一个新字段

4）删除一个字段

ALTER TABLE book_table DROP COLUMN price;

5）删除多个字段

ALTER TABLE book_table DROP COLUMN bookname,DROP COLUMN publisher;

2.2 增加、修改和删除约束

1）增加匿名主键约束

ALTER TABLE book_table ADD PRIMARY KEY(book_id);

2）增加匿名外键约束

ALTER TABLE buy_table ADD FOREIGN KEY(user_id) REFERENCES user_table(user_id);

3）增加自命名主键约束

ALTER TABLE user_table ADD CONSTRAINT user_id_pk PRIMARY KEY(user_id);

4）增加自命名外键约束

ALTER TABLE book_table ADD CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES user_table(user_id);

5）增加唯一约束

ALTER TABLE book_table ADD UNIQUE(user_id);

6）删除主键约束

ALTER TABLE book_table DROP PRIMARY KEY;

• 在该主键字段没有设置为自增时可以删除，如果设置为自增，则报错
  [Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

7）根据外键名删除外键约束

ALTER TABLE buy_table DROP FOREIGN KEY buy_table_ibfk_1;

3.删除数据表

3.1 仅删除数据

DELETE FROM user_table;

• 当有外键且已有关联数据时，无法删除所关联的数据，需要解除约束关系才可以删除

3.2 删除结构和数据

DROP TABLE user_table;

• 当有外键且已有关联数据时，无法删除该表，需要解除约束关系才可以删除