MySQL表的基本操作和数据类型

表的操作

创建表

语法：

CREATE TABLE table_name(field1 datatype,field2 datatype,field3 datatype
)character set 字符集 coate 校验规则 engine 存储引擎;

简洁版：create table tab_name（……）;

说明：

• field 表示列名
• datatype 表示列的类型
• characterset 字符集，如果没有指定字符集，则以所在数据库的字符集为准
• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

案例：

create table users(id int,name varchar(20) comment'用户名'password char(32) comment'密码是32位的md5值'birthday date comment'生日'
)character set utf8 engine MyISAM;   
//上面一行可以不写，即：使用默认的字符集、校验规则及引擎

修改表

添加字段（添加列）：

ALTER TABLE tab_name ADD(column datatype[DEFAULT expr][,column
datatype]...);

简洁版：alter table tab_name add(col_name datatype …);

示例：

//在users表birthday字段后添加一个字段，用于保存图片路径
alter table users add assets varchar(100) after birthday;

修改字段（修改列）：

//--这个字段是已有的，（）内直接填修改后的内容
ALTER TABLE tab_name MODIfy(column datatype [DEFAULT expr][,column
datatype]...);

简洁版：alter table tab_name modify(col_name new_datatype …);

示例：

alter table users modify name varchar(60);  //修改列的数据类型alter table employee change name new_name varchar(60);
//修改列名，新列名后面要带上数据类型

删除字段（删除列）：

ALTER TABLE tab_name DROP(column);

简洁版：alter table tab_name drop(col_name);

示例：

alter table users drop password;

修改表名：

alter table old_name rename to new_name;

示例：

alter table users rename to employee;

删除表

语法：

DROP [TEMPORARY] TABLE [IF ExISTS] tbl_name [,tbl_name] …;

简略版：drop table tab_name;

示例：

drop table users;

数据类型

数值类型

整型

bit类型：

bit(M)		位类型。M为指定位数，默认为1，范围1-64

tinyint [unsigned]类型：

1字节
tinyint				范围-128~127  默认有符号
tinyint unsigned 	范围0~255

smallint [unsigned]类型：

2字节
smallint				范围-32768~32767  默认有符号
smallint unsigned 		范围0~65535

int [unsigned]类型：

4字节
int				范围-2147483648~2147483647 默认有符号
int unsigned 	范围0~4294967295

bigint [unsigned]类型：

8字节
bigint				范围-9223372036854775808~9223372036854775807  默认有符号
bigint unsigned 	范围0~218446744073709551615

bool类型：

boolean	使用1和0表示真和假

浮点型

float [unsigned]类型：

float(m，d) [unsigned]：m指定显示长度，d指定小数位数，占用空间4个字节

案例:
小数：float(4,2)表示的范围是-99.99~99.99，MySQL在保存值时会进行四舍五入。

double [unsigned]类型：

double(m，d) [unsigned]：m指定显示长度，d指定小数位数，占用空间8个字节

decimal [unsigned]类型：

decimal(m，d) [unsigned]：m指定显示长度，d指定小数位数decimal和float很像，但是decimal的精度更高

文本、二进制类型

char类型：

char(L):固定长度字符串，L是设定的存储长度，单位为字符，最大长度值可以为255

示例：
char(4)表示可以存放四个字符（不是字节，是字符），可以是字母或汉字，不能超过4个，但所占内存固定为四字符。

varchar类型：

varchar(L):可变长度字符串，L表示字符长度，最大长度65535个字节

说明：
关于varchar(len),len到底是多大，这个len值，和表的编码密切相关:

• varchar长度可以指定为0到65535之间的值，但是有1.3个字节用于记录数据大小，所以说有效字节数是65532。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766(因为gbk中，一个字符占用2字节)。

blob类型：
二进制数据。

text类型：
大文本，不支持全文索引，不支持默认值。

日期时间类型

常用的日期有如下三个：
date：日期’yyyy-mm-dd’，占用三字节。

datetime：时间日期格式’yyyy-mm-dd HH:ii:ss’表示范围从1000到9999，占用八字节。

timestamp：时间戳，从1970年开始的yyyy-mmdd HH:ii:ss格式和 datetime 完全一致，占用四字节。

示例：

ENUM类型和SET类型

enum类型：

语法:

enum：枚举，“单选"类型;
enum('选项1','选项2','选项3');

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值;而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字:1，2，3，最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set类型：

set：集合，“多选”类型;
set('选项值1', '选项值2', '选项值3');

集合查询使用find_in_set函数:
find_in_set(sub,str_1ist)：如果sub在str_1ist中，则返回下标；如果不在，返回0；str_1ist用逗号分隔的字符串。
功能：查询set中包含sub的行。