MySQL数据类型和表的操作方法_Mysql

一、常用数据类型分类

与其他编程语言类似，sql中也规定了用于描述属性的数据类型。常用的数据类型有以下几类：

数据值类型
字符串类型
二进制类型
日期类型

二、数据值类型

类型	大小	说明
bit[(m)]	默认bit	m表示每个值的位数，取值范围为1~64。如果省略m，则默认为1
tinyint[(m)]	1byte	取值范围-2^7~2^7-1，无符号取值范围2^8-1
bool	1byte	tinyint(1)的同义词。值为零被认为是假，非零值被认为是true
smallint[(m)]	2byte	取值范围 -2^15 ~ 2^15-1 ，⽆符号取值范围 2^16-1
mediumint[(m)]	3byte	取值范围 -2^23 ~ 2^23-1 ，⽆符号取值范围 2^24-1
int[(m)]	4byte	取值范围 -2^31 ~ 2^31-1 ，⽆符号取值范围 2^32-1
integer[(m)]	4byte	int[(m)]的同义词
bigint[(m)]	8byte	取值范围 -2^63 ~ 2^63-1 ，⽆符号取值范围 2^64-1
float[(m,d)]	4byte	单精度浮点型，m是总位数，d是⼩数点后⾯的位数，⼤约可以精确到⼩数点后7位
double[(m,d)]	8byte	双精度浮点型，m是总位数，d是⼩数点后⾯的位数，⼤约可以精确到⼩数点后15位。
decimal[(m[,d])]	动态	不存在精度损失，m是总位数，d是⼩数点后的位数。decimal的最⼤位数(m)为65，最⼤⼩数位数(d)为30。如果省略m，则默认为10，如果省略d，则默认为0。m中不计算⼩数点和负数的-号，如果d为0，则值没有⼩数点和⼩数部分。

三、字符串类型

类型	说明
char[(m)]	固定⻓度字符串， m 表⽰⻓度，以字符为单位，取值范围 0 ~ 255 ， m 省略则⻓度为 1
varchar(m)	可变⻓度字符串， m 表⽰字符最⼤⻓度，的范围 0 ~ 65535 ，有效字符个数取决于实际字符数和使⽤的字符集
tinytext	⼩⽂本类型，最⼤⻓度为 255 (2^8-1)个字符，有效字符个数取决于使⽤的字符集
text[(m)]	⽂本类型，最⼤⻓度为 65535 (2^16-1)个字符，有效字符个数取决于使⽤的字符集
mediumtext	中⽂本类型，最⼤⻓度为 16,777,215 (2^24-1)个字符，有效字符个数取决于使⽤的字符集
longtext	⼤⽂本类型，最⼤⻓度为 4,294,967,295 即 4gb (2^32-1)个字符，有效字符个数取决于使⽤的字符集
binary[(m)]	固定⻓度⼆进制字节，于char类似，但存储的是⼆进制字节⽽不是字符串。 m 表⽰⻓度，以字节为单位，取值范围 0 ~ 255 ，m 省略则⻓度为 1
varbinary(m)	可变⻓度⼆进制字节，于varchar类似，但存储的是⼆进制字节⽽不是字符串。 m 表⽰⻓度，以字节为单位
tinyblob	⼩⼆进制字节类型，最⼤⻓度为 255 (2^8-1)个字节
blob[(m)]	⼆进制字节类型，最⼤⻓度为 65535 (2^16-1)个字节
mediumblob	中⼆进制字节类型，最⼤⻓度为 16,777,215 (2^24-1)个字节
longblob	⼤⼆进制字节类型，最⼤⻓度为 4,294,967,295 即 4gb (2^8-1)个字节
enum('value1','valu e2',...)	• 枚举 • 从值列表中选⼀个值 • 最多可以有 65,535 个不同的元素 • 单个元素的最⼤⻓度是 m <= 255 或 (m x w) <= 1020 ，其中 m 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数 • enum的值在内部表⽰为整数'value1','value2' 或 ''(空字符串) 和 null
set('value1','value 2',...)	• 集合 • 从值列表 'value1','value2' 中选零个或多个值 • 最多64个元素 • 单个元素的最⼤⻓度是 m <= 255 或 (m x w) <= 1020 ，其中 m 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数 • set值在内部表⽰为整数

四、日期类型

类型	大小	说明	0值
timestamp[(fsp)]	4bytes	• 时间戳类型 • ⽀持范围 1970-01-01 00:00:01.000000 ~ 20388-01-19 03:14:07.499999 • 0值为 0000-00-00 00:00:00	0000-00- 0000:00:00
datetime[(fsp)]	8bytes	• ⽇期类型和时间类型的组合 •⽀持范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999 • 显⽰格式为 yyyy-mm-dd hh:mm:ss[.fraction] • 0值为 0000-00-00 00:00:00	0000-00- 00 00:00:00
date	3bytes	• ⽇期类型 • ⽀持范围 1000-01-01 ~ 9999-12-31 • 显⽰格式为 yyyy-mm-dd • 0值为 0000-00-00
time[(fsp)]	3bytes	• 时间类型 • ⽀持范围 -838:59:59.000000 ~ 838:59:59.000000 • 显⽰格式为 hh:mm:ss[.fraction] • 0值为 00:00:00	00:00:00
year[(4)]	1byte	• 4位格式的年份 • ⽀持范围 1901 ~ 2155 • 显⽰格式为 yyyy • 0值为 0	0

• fsp 为可选设置，⽤来指定⼩数秒精度，范围从0到6，值为0表⽰没有⼩数部分，如果省略，默认
精度为0
• current_date和 current_date() 是 curdate() 的同义词⽤于获取当前⽇期
• current_time和current_time([fsp]) 是curtime() 的同义词⽤于获取当前时间
• current_timestamp和current_timestamp([fsp]) 是 now() 的同义词⽤于获取当前⽇期和时间

五、表的操作

5.1 查看所有表

show tables;

5.2 创建表

create [temporary] table [if not exists] tbl_name
field datatype [约束] [comment '注解内容']
[, field datatype [约束] [comment '注解内容']] ...
) [engine 存储引擎] [character set 字符集] [collate 排序规则];

temporary：表⽰创建的是⼀个临时表
field：列名
datatype：数据类型
comment：对列的描述或说明
engine：存储引擎，不指定则使⽤默认存储引擎
character set：字符集，不指定则使⽤默认字符集
collate：排序规则，不指定则使⽤默认排序规则

创建了一个user表，含四个列，id，name，password，birthday

5.3 查看表结构

desc 表名

field：表中的列名
type：列的数据类型
null：该列的值是否允许为null
key：该列的索引类型
default：该列的默认值
extra：扩展信息

5.4 修改表

alter table tbl_name [alter_option [, alter_option] ...];
alter_option: {
                table_options
        | add [column] col_name column_definition [first | after col_name]
        | modify [column] col_name column_definition [first | after col_name]
        | drop [column] col_name
        | rename column old_col_name to new_col_name
        | rename [to | as] new_tbl_name