约束
1.约束保证数据的完整性和一致性
2.约束分为表级约束和列级约束
3.约束的类型包括：
notnull 非空约束
primarykey 主键约束
uniquekey 唯一约束
default默认约束
foreignkey 外键约束
外键约束
保持数据一致性，完整性。实现一对一或者一对多关系。
要求：
1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2.数据表的存储引擎只能为InnoDB。
3.外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号为必须相同，而字符的长度则可以不同
4.外键列和参照列必须创建索引，如果外键列不存在索引，则系统自动创建索引。
编辑数据表的默认存储引擎
MySQL配置文件
default-storage-engine=INNODB(默认是INNODB)
外键约束
mysql>create table provinces(
-> id smallint unsigned primary keyauto_increment,
-> pname varchar(20) not null
-> );
QueryOK, 0 rows affected (0.25 sec)
mysql>show create table provinces;
+------------------------------------------------------------------------------+
|Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------+
|provinces | CREATE TABLE `provinces` (
`id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------------------------------+
1row in set (0.26 sec)
mysql>show create table provinces;
+--------------------------------------------------------------------------+
|Table | Create Table
|
+--------------------------------------------------------------------------+
|provinces | CREATE TABLE `provinces` (
`id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------------------------------------------------------------+
1row in set (0.00 sec)
mysql>create table users(
-> id smallint unsigned primary keyauto_increment,
-> username varchar(20) not null,
-> pid smallint unsigned,
-> foreign key (pid) referencesprovinces (id)
-> );
QueryOK, 0 rows affected (0.06 sec)
mysql>show indexes from provinces;
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
|Table | Non_unique | Key_name |Seq_in_index | Column_name | Collation | C
ardinality| Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
|provinces | 0 | PRIMARY | 1 | id | A |
0 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+
-----------+----------+--------+------+------------+---------+---------------+
1row in set (0.04 sec)
mysql>show indexes from users;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
|Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality| Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
|users | 0 | PRIMARY | 1 | id | A |
0 | NULL | NULL | | BTREE | | |
|users | 1 | pid | 1 | pid | A |
0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
2rows in set (0.01 sec)
mysql>show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table | Create Table
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
|users | CREATE TABLE `users` (
`id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`)REFERENCES `provinces` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1row in set (0.01 sec)外键约束的参照操作
1.cascade：从父表删除或更新且自动删除或更新子表中匹配的行。
2.setnull：从父表删除或更新行，并设置表中的外键列为null。如果使用该选项，必须保证子表列没有指定notnull。
3.restrict:拒绝对附表的删除或者更新操作。
4.noaction:标准SQL的关键字，在MySQL中与restrict相同。
外键约束的参照操作cascade
mysql>create table users1(
-> id smallint unsigned primary keyauto_increment,
-> username varchar(20) not null,
-> pid smallint unsigned,
-> foreign key (pid) referencesprovinces (id) on delete cascade
-> );
QueryOK, 0 rows affected (0.01 sec)
mysql>show create table users1;
+-----------------------------------------------------------------------------------------------------+
|Table | Create Table |
+-----------------------------------------------------------------------------------------------------+
|users1 | CREATE TABLE `users1` (
`id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users1_ibfk_1` FOREIGN KEY(`pid`) REFERENCES `provinces` (`id`) O
NDELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------------------------------------------------------------------------------------------+
1row in set (0.00 sec)
mysql>insert provinces(panme) values('A');
ERROR1054 (42S22): Unknown column 'panme' in 'field list'
mysql>insert provinces(pname) values('A');
QueryOK, 1 row affected (0.03 sec)
mysql>insert provinces(pname) values('B');
QueryOK, 1 row affected (0.00 sec)
mysql>insert provinces(pname) values('C');
QueryOK, 1 row affected (0.00 sec)
mysql>insert provinces(pname) values('D');
QueryOK, 1 row affected (0.00 sec)
mysql>insert provinces(pname) values('E');
QueryOK, 1 row affected (0.00 sec)
mysql>select * from provinces;
+----+-------+
|id | pname |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+----+-------+
5rows in set (0.00 sec)
mysql>insert users1(username,pid) values('tom',3);
QueryOK, 1 row affected (0.01 sec)
mysql>insert users1(username,pid) values('haha',4);
QueryOK, 1 row affected (0.00 sec)
mysql>insert users1(username,pid) values('zhangsan',5);
QueryOK, 1 row affected (0.00 sec)
mysql>select * from users1;
+----+----------+------+
|id | username | pid |
+----+----------+------+
| 1 | tom | 3 |
| 2 | haha | 4 |
| 4 | zhangsan | 5 |
+----+----------+------+
3rows in set (0.00 sec)
mysql>delete from provinces where id=3;
QueryOK, 1 row affected (0.08 sec)
mysql>select * from users1;
+----+----------+------+
|id | username | pid |
+----+----------+------+
| 2 | haha | 4 |
| 4 | zhangsan | 5 |
+----+----------+------+
2rows in set (0.00 sec)
mysql>select * from provinces;
+----+-------+
|id | pname |
|---- | ----- |
+----+-------+
|1 | A |
|2 | B |
|4 | D |
|5 | E |
+----+-------+
4rows in set (0.00 sec)开发过程中更多的使用逻辑的物理约束。
表级约束与列级约束
对一个数据列建立的约束，称为列级约束。(notnull default)
对多个数据列建立的约束，称为表级约束。
列级约束既可以在列定义时声明，也可以在列定义后期声明。
表级约束只能在列定义后声明。
修改数据表
添加列
altertable table_name add [column] col_namecolumn_definition [first|after col_name]
rootlocalhostt2>show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default |Extra |
+----------+----------------------+------+-----+---------+----------------+
|id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|username | varchar(20) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3rows in set (0.04 sec)
rootlocalhostt2>alter table users1 add age tinyint unsigned not null default 10
;
QueryOK, 0 rows affected (0.15 sec)
Records:0 Duplicates: 0 Warnings: 0
rootlocalhostt2>show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default |Extra |
+----------+----------------------+------+-----+---------+----------------+
|id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|username | varchar(20) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
4rows in set (0.00 sec)
rootlocalhostt2>alter table users1 add password varchar(32) not null after use
name;
QueryOK, 0 rows affected (0.07 sec)
Records:0 Duplicates: 0 Warnings: 0
rootlocalhostt2>show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default |Extra |
+----------+----------------------+------+-----+---------+----------------+
|id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|username | varchar(20) | NO | | NULL | |
|password | varchar(32) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5rows in set (0.00 sec)
rootlocalhostt2>alter table users1 add turename varchar(20) not null first;
QueryOK, 0 rows affected (0.05 sec)
Records:0 Duplicates: 0 Warnings: 0
rootlocalhostt2>show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default |Extra |
+----------+----------------------+------+-----+---------+----------------+
|turename | varchar(20) | NO | | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|username | varchar(20) | NO | | NULL | |
|password | varchar(32) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6rows in set (0.00 sec)添加多列
alter table table_name add [columns](col_name column_definition,...)
修改数据表
alter table table_name drop [column]col_name
alter table table_name drop [column]col_name1,drop [column] col_name2;
rootlocalhostt2>alter table users1 drop turename;
QueryOK, 0 rows affected (0.05 sec)
Records:0 Duplicates: 0 Warnings: 0
rootlocalhostt2>alter table users1 drop age,drop password;
QueryOK, 0 rows affected (0.06 sec)
Records:0 Duplicates: 0 Warnings: 0
rootlocalhostt2>show columns from users1;
+----------+----------------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default |Extra |
+----------+----------------------+------+-----+---------+----------------+
|id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|username | varchar(20) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3rows in set (0.00 sec)添加主键约束
alter table table_name add [constraint[symbol]] primary key [index_type] (index_col_name,...)
rootlocalhost t2>create table users2(
-> username varchar(10) not null,
-> pid smallint unsigned
-> );
QueryOK, 0 rows affected (0.04 sec)
rootlocalhostt2>show create table users2;
+------------------------------------------------------------------+
|Table | Create Table
+-------------------------------------------------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------------------------------------------------------+
1row in set (0.00 sec)
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | | NULL | |
|id | smallint(5) unsigned |YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
3rows in set (0.00 sec)添加主键约束
root@localhostt2>alter table users2 add constraint PK_users2_id primary key(id)
;
QueryOK, 0 rows affected (0.04 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3rows in set (0.00 sec)添加唯一约束
alter table table_name add [constraint[sysbol]] unique [index|key] [index_name] [index_type] (index_col_name,....)
root@localhostt2>alter table users2 add unique (username);
QueryOK, 0 rows affected (0.02 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | UNI | NULL | |
|pid | smallint(5) unsigned |YES | | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3rows in set (0.00 sec)
root@localhostt2>show create table users2;
+--------------------------------------------------------------------+
|Table | Create Table
+--------------------------------------------------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------------------------------------------------------+
1row in set (0.00 sec)添加外键约束
altertable table_name add [constraint [symbol]] foreign key [index_name](index_col_name,...) references _definition
root@localhostt2>show columns from provinces;
+-------+----------------------+------+-----+---------+----------------+
|Field | Type | Null | Key| Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
|id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|pname | varchar(20) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
2rows in set (0.03 sec)
root@localhostt2>alter table users2 add foreign key (pid) references provinces
(id);
QueryOK, 0 rows affected (0.05 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show create table users2;
+-------------------------+
|Table | Create Table
+-------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `pid` (`pid`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY(`pid`) REFERENCES `provinces` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------------------------------------------------------------------------------------------------+
1row in set (0.00 sec)添加或删除默认约束
altertable table_name alter [column] col_name {set default literal |drop default}
root@localhostt2>alter table users2 add age tinyint unsigned not null;
QueryOK, 0 rows affected (0.03 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | UNI | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | |
|age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)
root@localhostt2>alter table users2 alter age set default 15;
QueryOK, 0 rows affected (0.01 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | UNI | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | |
|age | tinyint(3) unsigned | NO | | 15 | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)
root@localhostt2>alter table users2 alter age drop default;
QueryOK, 0 rows affected (0.01 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | UNI | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | |
|age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)删除主键约束
alter table table_name drop primary key;
root@localhost t2>show columns fromusers2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | UNI | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|id | smallint(5) unsigned | NO | PRI | NULL | |
|age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)
root@localhostt2>alter table users2 drop primary key;
QueryOK, 0 rows affected (0.02 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | PRI | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|id | smallint(5) unsigned | NO | | NULL | |
|age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)删除唯一约束
alter table table_name drop {index|key}index_name
root@localhost t2>show indexes fromusers2\G;
***************************1. row ***************************
Table: users2
Non_unique: 0
Key_name: username
Seq_in_index: 1
Column_name: username
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
***************************2. row ***************************
Table: users2
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2rows in set (0.55 sec)
ERROR:
Noquery specified
root@localhostt2>alter table users2 drop index username;
QueryOK, 0 rows affected (0.03 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra|
+----------+----------------------+------+-----+---------+-------+
|username | varchar(10) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | MUL | NULL | |
|id | smallint(5) unsigned | NO | | NULL | |
|age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)
root@localhostt2>show indexes from users2\G;
***************************1. row ***************************
Table: users2
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1row in set (0.00 sec)
ERROR:
Noquery specified删除外键约束
alter table table_name drop foreignkey fk_symbol
root@localhost t2>show create tableusers2;
+------------------------------------------------------------+
|Table | Create Table
|
+------------------------------------------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
KEY `pid` (`pid`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY(`pid`) REFERENCES `provinces` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------------------------------------------------+
1row in set (0.00 sec)
root@localhostt2>alter table users2 drop foreign key users2_ibfk_1;
QueryOK, 0 rows affected (0.00 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show create table users2;
+------------------------------------------------------------+
|Table | Create Table
|
+-------------------------------------------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
KEY `pid` (`pid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------------------------------------------------+
1row in set (0.00 sec)
root@localhostt2>alter table users2 drop index pid;
QueryOK, 0 rows affected (0.01 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show create table users2;
+---------------------------------------------------------------+
|Table | Create Table
|
+---------------------------------------------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
`age` tinyint(3) unsigned NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------------------------------------------------------+
1row in set (0.00 sec)
修改数据表
修改列定义
altertable table_name modify [column] col_name column_definition [first|aftercol_name]
root@localhostt2>show create table users2;
+--------------------------------------------------------------+
|Table | Create Table |
+--------------------------------------------------------------+
|users2 | CREATE TABLE `users2` (
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
`age` tinyint(3) unsigned NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------------------------------------------------+
1row in set (0.00 sec)
root@localhostt2>alter table users2 modify id smallint first;
QueryOK, 0 rows affected (0.07 sec)
Records:0 Duplicates: 0 Warnings: 0
root@localhostt2>show columns from users2;
+----------+----------------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
|id | smallint(6) | YES | | NULL | |
|username | varchar(10) | NO | | NULL | |
|pid | smallint(5) unsigned |YES | | NULL | |
|age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4rows in set (0.00 sec)
root@localhostt2>alter table users2 modify id tinyint first;
QueryOK, 0 rows affected (0.02 sec)
Records:0 Duplicates: 0 Warnings: 0
修改列名称
altertable table_name change [column] old_col_name new_col_name column_definition[first | after col_name]本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
