882
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
最後更新：2017-04-04 07:03:18