文章目录
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
数据类型(最常用)
- varchar存储变化长度字符串。该类型字段的长度会根据实际字符数据的长度自动调整。(该列的字符串长度小于定义时的长度,空余部分不会使用空格填充。)
- 当varchar长度在0-255字节时,该类型实际长度为值的实际长度+1,用一个字节保存实际使用的长度
- 当varchar长度大于255字节时,该类型实际长度为值的实际长度+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 key7)根据外键名删除外键约束
ALTER TABLE buy_table DROP FOREIGN KEY buy_table_ibfk_1;
3.删除数据表
3.1 仅删除数据
DELETE FROM user_table;
- 当有外键且已有关联数据时,无法删除所关联的数据,需要解除约束关系才可以删除
3.2 删除结构和数据
DROP TABLE user_table;
- 当有外键且已有关联数据时,无法删除该表,需要解除约束关系才可以删除