#列类型

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