mysql大表更新字段类型_mysql 的列类型，增加更新表字段

#列类型

mysql 三大列类型

数值型

tinyint

占据空间 1字节 8个位 存储范围，-128-127，0-255

0-2^8 0-255

-2^7 2^7-1

smallint

mediuint

int

bigint

int系列声明时的参数

(M) unsigned zerofill

分析M参数 M表示补0宽度

M必须和zerofill配合才有意义, zerofill 表示的unsigned

alter table calss add snum smallint(5) zerofill not bull default 0;

小数(浮点型/定点型)

float(M,D) decimal(M,D)

M 叫精度，代表总位数，而D 是标度，代表小数位(小数点右边的位数)

float(6,2) 表示 -9999.99到9999.99

字符串

char varchar text blob

char定长，和varchar变长

char(M) varchar(M) 中的M限制的是字符，不是字节

即char(2)charset utf8 ,是2个字符，如：'中国'

text 不用加默认值

blob 是二进制类型，用来存储图像，音频等二进制信息

意义：2进制，0-255都有可能出现

日期/时间类型

date

time

建表

SNS白领社交网站

会员表

create table snsmember (

id int unsigned primary key auto_increment,

username char(20) not null default '',

gender char(1) not null default '',

weight tinyint unsigned not null default 0,

birth date not null default '0000-00-00',

salary decimal(8,2) not null default 0.00,

lastlogin int unsigned not null default 0

)engine myisam charset utf8

/*有所优化，字段都是定长*/

#增加列或者删除列

alter table 表名 add 列名称 列类型 列参数

#在最前边增加一行

alter table 表名 add 列名称 列类型 列参数 first

#删除列

alter table 表名 drop 列名

#修改列

把char(1) 改为char(4)

alter table 表名 modify 列名 新类型 新参数

mysql> use test;

Database changed

mysql> create table test1(

-> star varchar(20) not null default '',

-> birth date not null default '0000-00-00'

-> )engine myisam charset utf8;

Query OK, 0 rows affected (0.23 sec)

mysql> insert into test1

-> values

-> ('lin1ingxia','1967-3-23');

Query OK, 1 row affected (0.05 sec)

mysql> select * from test1;

+------------+------------+

| star | birth |

+------------+------------+

| lin1ingxia | 1967-03-23 |

+------------+------------+

1 row in set (0.01 sec)

mysql> create table snsmember (

-> id int unsigned primary key auto_increment,

-> username char(20) not null default '',

-> gender char(1) not null default '',

-> weight tinyint unsigned not null default 0,

-> birth date not null default '0000-00-00',

-> salary decimal(8,2) not null default 0.00,

-> lastlogin int unsigned not null default 0

-> )engine myisam charset utf8;

Query OK, 0 rows affected (0.11 sec)

mysql> create table m (

-> id int unsigned primary key auto_increment

-> )engine myisam charset utf8;

Query OK, 0 rows affected (0.06 sec)

mysql> alter table m add username char(20) not null default '';

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from m;

Empty set (0.02 sec)

mysql> desc m;

+----------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| username | char(20) | NO | | | |

+----------+------------------+------+-----+---------+----------------+

2 rows in set (0.08 sec)

mysql> alter table m drop username;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc m;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+-------+------------------+------+-----+---------+----------------+

1 row in set (0.00 sec)

mysql> alter table m add username char(20) not null default '';

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table m add email char(28) not null default '';

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table m modify email varchar(30) not null default '';

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc m;

+----------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| username | char(20) | NO | | | |

| email | varchar(30) | NO | | | |

+----------+------------------+------+-----+---------+----------------+

3 rows in set (0.03 sec)

mysql> exit