Mysql基礎教程 mysql進階

Mysql修改表結構



修改表結構


查看表


mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test10          |
| test11          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| test9           |
| user            |
| user1           |
| user2           |
| user3           |
| user4           |
| user5           |
| user6           |
| user7           |
| user8           |
| user9           |
+-----------------+
24 rows in set (0.00 sec)


創建用戶表


mysql> CREATE TABLE IF NOT EXISTS user10(
    -> id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(32) NOT NULL,
    -> email VARCHAR(50) NOT NULL DEFAULT '[email protected]',
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> sex ENUM('男','女','保密') DEFAULT '保密',
    -> addr VARCHAR(200) NOT NULL DEFAULT '北京',
    -> salary FLOAT(6,2),
    -> regTime INT UNSIGNED,
    -> face CHAR(100) NOT NULL DEFAULT 'default.jpg'
    -> );
Query OK, 0 rows affected (0.52 sec)


查看表結構


mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field      | Type                       | Null | Key | Default          | Extra          |
+----------+----------------------------+------+-----+------------------+----------------+
| id        | smallint(5) unsigned       | NO   | PRI | NULL             | auto_increment |
| username   | varchar(20)              | NO   | UNI | NULL             |                |
| password    | char(32)                 | NO   |     | NULL             |                |
| email      | varchar(50)              | NO   |     | [email protected] |                |
| age       | tinyint(3) unsigned          | YES  |      | 18               |                |
| sex       | enum('男','女','保密')         | YES  |      | 保密             |                |
| addr       | varchar(200)               | NO   |      | 北京             |                |
| salary      | float(6,2)                 | YES  |      | NULL             |                |
| regTime     | int(10) unsigned           | YES  |      | NULL             |                |
| face       | char(100)              | NO  |    | default.jpg      |                |
+----------+----------------------------+------+-----+------------------+----------------+
10 rows in set (0.04 s)



如何修改表結構?


修改表名


ALTER TABLE tbl_name RENAME [TO|AS] new_name
RENAME TABLE tbl_name TO new_name


將user10重命名成User11,再轉變回來


mysql> ALTER TABLE user10 RENAME TO user11;
Query OK, 0 rows affected (0.20 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test10          |
| test11          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| test9           |
| user            |
| user1           |
| user11          |
| user2           |
| user3           |
| user4           |
| user5           |
| user6           |
| user7           |
| user8           |
| user9           |
+-----------------+
25 rows in set (0.00 sec)
mysql> ALTER TABLE user11 RENAME AS user10;
Query OK, 0 rows affected (0.15 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test10          |
| test11          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| test9           |
| user            |
| user1           |
| user10          |
| user2           |
| user3           |
| user4           |
| user5           |
| user6           |
| user7           |
| user8           |
| user9           |
+-----------------+
25 rows in set (0.00 sec)


可以省略AS來重命名,并查詢


mysql> ALTER TABLE user10 RENAME user11;
Query OK, 0 rows affected (0.18 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test10          |
| test11          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| test9           |
| user            |
| user1           |
| user11          |
| user2           |
| user3           |
| user4           |
| user5           |
| user6           |
| user7           |
| user8           |
| user9           |
+-----------------+
25 rows in set (0.00 sec)


運用RENAME TABLE user11 TO user10來重命名


mysql> RENAME TABLE user11 TO user10;
Query OK, 0 rows affected (0.17 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news       |
| course          |
| test1           |
| test10          |
| test11          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| test9           |
| user            |
| user1           |
| user10          |
| user2           |
| user3           |
| user4           |
| user5           |
| user6           |
| user7           |
| user8           |
| user9           |
+-----------------+
25 rows in set (0.00 sec)


運用RENAME TABLE user11 TO user10來重命名,不能省略to否則會報錯


mysql> RENAME TABLE user10 user11;
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 'user11' at line 1



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5