本篇参考：http://www.imooc.com/learn/122
编辑数据表的默认的数据引擎：
用Everything查找my.ini文件，我的电脑 里面是在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
打开编辑，找到相应的行改为：default-storage-engine=INNODB 我这个不用改，因为原本就是。
外键实例：
mysql> USE world;
Database changed
mysql> CREATE TABLE provinces(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.46 sec)
mysql> SHOW CREATE TABLE provinces;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid BIGINT, #引用外键、 -> FOREIGN KEY(pid) REFERENCES provinces(id) -> );ERROR 1215 (HY000): Cannot add foreign key constraintmysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT, #引用外键、 -> FOREIGN KEY(pid) REFERENCES provinces(id) -> );ERROR 1215 (HY000): Cannot add foreign key constraintmysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(10) NOT NULL, -> pid SMALLINT UNSIGNED, #引用外键、符号必须一致、 -> FOREIGN KEY(pid) REFERENCES provinces(id) -> );Query OK, 0 rows affected (0.26 sec)
#其中，provinces是父表，users是子表。
mysql> SHOW INDEXES FROM provinces;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| provinces | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.07 sec)
以网格形式呈现：
mysql> SHOW INDEXES FROM provinces\G;查看users表中的索引：
*************************** 1. row ***************************
Table: provinces
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
*************************** 2. row ***************************
Table: users
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:
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW CREATE TABLE users;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(10) 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 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
CASCADE命令：
mysql> CREATE TABLE users1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED, #引用外键、符号必须一致、
-> FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.22 sec)
mysql> SHOW CREATE TABLE users1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users1 | CREATE TABLE `users1` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
向子表中插入元素必须先向父表中插入元素：
mysql> INSERT provinces(pname) VALUES('a');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT provinces(pname) VALUES('b');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT provinces(pname) VALUES('c');
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+-------+
3 rows in set (0.00 sec)
mysql> INSERT users1(username,pid) VALUES('Tome',3);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users1(username,pid) VALUES('John',1);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT users1(username,pid) VALUES('Rose',2);
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | Tome | 3 |
| 2 | John | 1 |
| 3 | Rose | 2 |
+----+----------+------+
3 rows in set (0.00 sec)
删除pid是3的记录：
mysql> delete from provinces where id=3;
Query OK, 1 row affected (0.12 sec)
mysql> select * from provinces;
+----+-------+
| id | pname |
+----+-------+
| 1 | a |
| 2 | b |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from users1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 2 | John | 1 |
| 3 | Rose | 2 |
+----+----------+------+
2 rows in set (0.00 sec)
添加一列：
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
mysql> #添加列
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 1; Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 1 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> #将插入行置于username后面
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 1 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> #放在第一位置
mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 1 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除列：
mysql> #删除列添加主键约束：
mysql> ALTER TABLE users1 DROP truename;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 1 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> #删除两列：
mysql> ALTER TABLE users1 DROP password,DROP age; Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> CREATE TABLE users2(
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED
-> );
Query OK, 0 rows affected (0.21 sec)
mysql> 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 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> #增加一列；
mysql> ALTER TABLE users2 ADD id SMALLINT UNSIGNED; Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> #修改id为主键，并且给主键起名字为pk；
mysql> ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加唯一约束：
mysql> #添加唯一约束
mysql> ALTER TABLE users2 ADD UNIQUE (username);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> #为users2添加外键约束；
mysql> SHOW COLUMNS FROM provinces;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
Query OK, 0 rows affected (0.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> #添加一个字段
mysql> ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> #设置默认值：
mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> #删除主键约束；
mysql> ALTER TABLE users2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.59 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> #删除唯一约束；
mysql> SHOW INDEXS FROM users2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDEXS FROM users2' at line 1
mysql> SHOW INDEXES FROM users2;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users2 | 0 | username | 1 | username | A | 0 | NULL | NULL | | BTREE | | |
| users2 | 1 | pid | 1 | pid | A | 0 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)
mysql> SHOW INDEXES FROM users2\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:
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> ALTER TABLE users2 DROP INDEX username;
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> 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:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> #删除外键约束：
mysql> 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`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> #删除剩下的外键残留：
mysql> ALTER TABLE users2 DROP INDEX pid;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> #把id字段挪到第一位置；
mysql> ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> #修改字段类型；
mysql> ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> #修改列名称；
mysql> ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL; Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users2;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | NO | | NULL | |
| username | varchar(10) | NO | | NULL | |
| p_id | tinyint(3) unsigned | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> #修改数据表的名称；
mysql> ALTER TABLE users2 RENAME users3;
Query OK, 0 rows affected (0.13 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
| provinces |
| tb1 |
| tb2 |
| tb3 |
| tb4 |
| tb5 |
| tb6 |
| users |
| users1 |
| users3 |
+-----------------+
13 rows in set (0.02 sec)
mysql> #下面方法可以为一张或多张数据表命名；
mysql> RENAME TABLE users3 TO users2;
Query OK, 0 rows affected (0.11 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
| provinces |
| tb1 |
| tb2 |
| tb3 |
| tb4 |
| tb5 |
| tb6 |
| users |
| users1 |
| users2 |
+-----------------+
13 rows in set (0.00 sec)