首页> 基础笔记 >Mysql基础学习 Mysql基础学习
MySQL数据字段属性基础学习
作者：小萝卜 2019-08-28 【 MySql 数据库 】 浏览 871
简介MySQL数据字段属性基础学习，ZEROFILL、UNSIGNED、AUTO_INCREMENT、NULL和NOT NULL、DEFAULT
MySQL数据字段属性基础学习
UNSIGNED
只能用于设置数值类型，不允许出现负数
最大存储长度会增加一倍
ZEROFILL
只能用于设置数值类型，在数值之前会自动用0补齐不足的位数
AUTO_INCREMENT
用于设置字段的自动增长属性，每增加一条记录，该字段的值会自动加1
NULL和NOT NULL
默认为NULL，即插入值时没有在此字段插入值，默认为NULL值，如果指定了NOT NULL，则必须在插入值时在此字段填入值
DEFAULT
可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值
实例：
-- 创建一个学生信息表
mysql> create table stu(
-> id int unsigned not null auto_increment primary key,
-> name varchar(16) not null unique,
-> age tinyint unsigned not null default 20,
-> sex enum('m','w') not null default 'm',
-> classid char(7)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> desc stu;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 20 | |
| sex | enum('m','w') | NO | | m | |
| classid | char(7) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.06 sec)
mysql>
--修改表结构
mysql> select * from uu;
+------+-----------+------+------+
| id | name | age | sex |
+------+-----------+------+------+
| 1 | zhangsan | 20 | m |
| 2 | lisi | 25 | w |
| 4 | zhaoliu | 25 | w |
| 5 | xiaozhang | 24 | m |
| 6 | xiaoli | 23 | w |
+------+-----------+------+------+
5 rows in set (0.00 sec)
-- 为uu表的name字段后添加一个m字段，int类型非空默认值为20
mysql> alter table uu add m int not null default 20 after name;
Query OK, 5 rows affected (0.11 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc uu;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| m | int(11) | NO | | 20 | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('m','w') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from uu;
+------+-----------+----+------+------+
| id | name | m | age | sex |
+------+-----------+----+------+------+
| 1 | zhangsan | 20 | 20 | m |
| 2 | lisi | 20 | 25 | w |
| 4 | zhaoliu | 20 | 25 | w |
| 5 | xiaozhang | 20 | 24 | m |
| 6 | xiaoli | 20 | 23 | w |
+------+-----------+----+------+------+
5 rows in set (0.00 sec)
mysql>
mysql> desc uu;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| m | int(11) | NO | | 20 | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('m','w') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from uu;
+------+-----------+----+------+------+
| id | name | m | age | sex |
+------+-----------+----+------+------+
| 1 | zhangsan | 20 | 20 | m |
| 2 | lisi | 20 | 25 | w |
| 4 | zhaoliu | 20 | 25 | w |
| 5 | xiaozhang | 20 | 24 | m |
| 6 | xiaoli | 20 | 23 | w |
+------+-----------+----+------+------+
5 rows in set (0.00 sec)
-- 在uu表的第一列添加一个x字段
mysql> alter table uu add x int first;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from uu;
+------+------+-----------+----+------+------+
| x | id | name | m | age | sex |
+------+------+-----------+----+------+------+
| NULL | 1 | zhangsan | 20 | 20 | m |
| NULL | 2 | lisi | 20 | 25 | w |
| NULL | 4 | zhaoliu | 20 | 25 | w |
| NULL | 5 | xiaozhang | 20 | 24 | m |
| NULL | 6 | xiaoli | 20 | 23 | w |
+------+------+-----------+----+------+------+
5 rows in set (0.00 sec)
-- 删除uu表的x字段。
mysql> alter table uu drop x;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from uu;
+------+-----------+----+------+------+
| id | name | m | age | sex |
+------+-----------+----+------+------+
| 1 | zhangsan | 20 | 20 | m |
| 2 | lisi | 20 | 25 | w |
| 4 | zhaoliu | 20 | 25 | w |
| 5 | xiaozhang | 20 | 24 | m |
| 6 | xiaoli | 20 | 23 | w |
+------+-----------+----+------+------+
5 rows in set (0.00 sec)
mysql>
-- 修改字段
mysql> desc uu;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| m | int(11) | NO | | 20 | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('m','w') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table uu change m mm tinyint not null default 20;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc uu;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
| mm | tinyint(4) | NO | | 20 | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('m','w') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
很赞哦！ (0)
上一篇：Mysql四类索引学习笔记
下一篇：MySQL类型转换介绍
