mysql之表结构的修改

最新推荐文章于 2022-12-05 15:39:59 发布

mercies

最新推荐文章于 2022-12-05 15:39:59 发布

阅读量396

点赞数

分类专栏： MYSQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/mercies/article/details/109696897

版权

MYSQL 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在MySQL中对数据表进行操作，包括添加、删除字段，一次进行多个操作，动态添加和删除默认值，修改字段类型和属性，更改字段名称，以及添加和删除主键和唯一性约束。此外，还涵盖了修改数据表名称的方法，是数据库管理的重要参考资料。

摘要由CSDN通过智能技术生成

文章目录

我们如果创建了一个表，但是现在需要多添加一个字段，你可以删除这个表，再重新创建一个表，但是这种方法比较麻烦，费时。那么就需要以下的方式

1. 添加字段

ALTER TABLE tbl_name
ADD 字段名称 [字段完整性约束条件] [FIRST | AFTER 字段名称]

mysql> CREATE TABLE IF NOT EXISTS user1(
    -> id INT UNSIGNED AUTO_INCREMENT KEY
    -> );
Query OK, 0 rows affected (0.81 sec)

mysql> DESC user1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+
1 row in set (0.06 sec)

mysql> ALTER TABLE user1 ADD username VARCHAR(20);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

– 添加用户名字段username varchar(20)

mysql> ALTER TABLE user1 ADD username VARCHAR(20);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

– 添加密码password char(32) not null

mysql> ALTER TABLE user1 ADD password CHAR(30) NOT NULL;
Query OK, 0 rows affected (1.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

默认添加字段在最后添加，我们可以指定添加位置

mysql> ALTER TABLE user1 ADD email VARCHAR(23) NOT NULL UNIQUE AFTER username;
Query OK, 0 rows affected (1.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | YES  |     | NULL    |                |
| email    | varchar(23)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

把字段添加在最前面用FIRST

mysql> ALTER TABLE user1 ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;
Query OK, 0 rows affected, 1 warning (1.12 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> DESC user1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| test     | tinyint(1)   | NO   |     | 0       |                |
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | YES  |     | NULL    |                |
| email    | varchar(23)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)

2. 删除字段

ALTER TABLE tbl_name
DROP 字段名称 [字段完整性约束条件] [FIRST | AFTER 字段名称]

删除数据表中列的操作语句为
ALTER TABLE tbl_name DROP col_nam
LTER TABLE tbl_name DROP COLUMN col_name

mysql> ALTER TABLE user1 DROP test;
Query OK, 0 rows affected (1.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | YES  |     | NULL    |                |
| email    | varchar(23)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

3. 一次进行多个操作

mysql> ALTER TABLE user1
    -> ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    -> ADD addr VARCHAR(50) NOT NULL DEFAULT '北京',
    -> DROP email;
Query OK, 0 rows affected (0.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | YES  |     | NULL    |                |
| password | char(30)         | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 18      |                |
| addr     | varchar(50)      | NO   |     | 北京    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)

4.动态添加默认值

语法：
ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

mysql> CREATE TABLE IF NOT EXISTS user2(
    ->     id INT UNSIGNED AUTO_INCREMENT KEY,
    ->     username VARCHAR(20) NOT NULL,
    ->     age TINYINT UNSIGNED NOT NULL  DEFAULT 18,
    ->     email VARCHAR(50) NOT NULL
    -> );
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    10
Current database: imooc_user

Query OK, 0 rows affected (1.54 sec)

mysql> DESC user2;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int unsigned     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | NO   |     | NULL    |                |
| age      | tinyint unsigned | NO   |     | 18      |                |
| email    | varchar(50)      | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)

给email添加默认值

mysql> ALTER TABLE user2
    -> ALTER email SET DEFAULT '1111@qq.com';
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user2;
+----------+------------------+------+-----+-------------+----------------+
| Field    | Type             | Null | Key | Default     | Extra          |
+----------+------------------+------+-----+-------------+----------------+
| id       | int unsigned     | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)      | NO   |     | NULL        |                |
| age      | tinyint unsigned | NO   |     | 18          |                |
| email    | varchar(50)      | NO   |     | 1111@qq.com |                |
+----------+------------------+------+-----+-------------+----------------+
4 rows in set (0.03 sec)

5.动态删除默认值

语法：
ALTER TABLE tb_name ALTER age DROP DEFAULT

删除age字段的默认值

mysql> ALTER TABLE user2
    -> ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user2;
+----------+------------------+------+-----+-------------+----------------+
| Field    | Type             | Null | Key | Default     | Extra          |
+----------+------------------+------+-----+-------------+----------------+
| id       | int unsigned     | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)      | NO   |     | NULL        |                |
| age      | tinyint unsigned | NO   |     | NULL        |                |
| email    | varchar(50)      | NO   |     | 1111@qq.com |                |
+----------+------------------+------+-----+-------------+----------------+
4 rows in set (0.04 sec)

6.动态修改字段的类型和属性

语法：
ALTER TABLE tbl_name
MODIFY 字段名称 [字段类型] [FIRST | AFTER 字段名称]

mysql> CREATE TABLE user3(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(30) NOT NULL ,
    -> email VARCHAR(40) NOT NULL
    -> );
Query OK, 0 rows affected (0.98 sec)

mysql> DESC user3;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
| email    | varchar(40)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

– 将用户名的字段类型改为25

mysql> ALTER TABLE user3
    -> MODIFY username VARCHAR(25) NOT NULL UNIQUE;
Query OK, 0 rows affected, 1 warning (0.81 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> DESC user3;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(25)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
| email    | varchar(40)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

7. 修改字段名称，字段类型和字段属性

语法:
ALTER TABLE tbl_name
CHANGE 原字段名称新字段名称字段类型 [FIRST | AFTER 字段名称]
将username字段改为user

mysql> ALTER TABLE user3
    -> CHANGE username user VARCHAR(25) NOT NULL;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user3;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| user     | varchar(25)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
| email    | varchar(40)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

8. 添加主键字段

语法：
ALTER TABLE tbl_name
ADD PRIMARY KEY (字段名称);

mysql> DESC user4;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int unsigned | YES  |     | NULL    |       |
| username | varchar(20)  | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> ALTER TABLE user4
    -> ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user4;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)  | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

9. 删除主键字段

语法：(由于每个表中主键只有一个，所以直接删除)
ALTER TABLE tbl_name
DROP PRIMARY KEY;

mysql> ALTER TABLE user4
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (2.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user4;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int unsigned | NO   |     | NULL    |       |
| username | varchar(20)  | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

但是在删除主键时，会有另一种情况：当主键和自动增长同时存在时，这样删除就会报错误。

mysql> DESC user3;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| user     | varchar(25)  | NO   | UNI | NULL    |                |
| password | char(30)     | NO   |     | NULL    |                |
| email    | varchar(40)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

比如user3这个表，我们对他进行删除，

mysql> ALTER TABLE user3
    -> DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

错误说明是因为这个字段是auto_increment所以必须作为主键。
那么如果想删除这个主键，就得先改变这个字段的属性，把auto_increment属性去掉。再进行删除语句的操作，就可以进行删除了。

mysql> ALTER TABLE user3
    -> MODIFY id INT UNSIGNED NOT NULL ;
Query OK, 0 rows affected (2.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user3;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int unsigned | NO   |     | NULL    |       |
| user     | varchar(25)  | NO   | PRI | NULL    |       |
| password | char(30)     | NO   |     | NULL    |       |
| email    | varchar(40)  | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

但是删除后，发现了一个奇怪的现象就是，这个主键的属性，自动移动到了之前是not null unique 这个字段上去，但是在想要删除自动赋值为主键的这个字段的主键，就删不掉了

mysql> DESC user5;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int unsigned | NO   |     | NULL    |       |
| username | varchar(20)  | NO   | PRI | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> ALTER TABLE user5
    -> DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

会显示不存在主键
如果没有字段为unique，则删除后，也不会有其他字段显示为主键

mysql> ALTER TABLE user6
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (1.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int unsigned | NO   |     | NULL    |       |
| username | varchar(20)  | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

10. 添加unique

语法：
ALTER TABLE tbl_name
ADD UNIQUE KEY（字段名称）

或者
ALTER TABLE tbl_name
ADD UNIQUE INDEX ind_name(字段名称)

mysql> ALTER TABLE user6
    -> ADD UNIQUE KEY(username);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE user6;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user6 | CREATE TABLE `user6` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `passwoed` varchar(20) NOT NULL,
  `email` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE user6
    -> ADD UNIQUE INDEX uni_email(email);
Query OK, 0 rows affected (0.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE user6;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user6 | CREATE TABLE `user6` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `passwoed` varchar(20) NOT NULL,
  `email` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `uni_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

11.删除unique

语法：
ALTER TABLE tbl_name
DROP INDEX 字段名称 / 索引名称
如果不指定索引名称，则默认为该字段的名称，可用SHOW CREATE TABLE tbl_name进行查看

mysql> CREATE TABLE user6(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> passwoed VARCHAR(20) NOT NULL,
    -> email VARCHAR(30) NOT NULL UNIQUE
    -> );
Query OK, 0 rows affected (1.22 sec)

mysql> DESC user6;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | NO   | UNI | NULL    |                |
| passwoed | varchar(20)  | NO   |     | NULL    |                |
| email    | varchar(30)  | NO   | UNI | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

mysql> ALTER TABLE user6
    -> DROP INDEX username;
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE user6
    -> DROP INDEX email;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | NO   |     | NULL    |                |
| passwoed | varchar(20)  | NO   |     | NULL    |                |
| email    | varchar(30)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)


mysql> ALTER TABLE user6
    -> DROP INDEX uni_email;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | NO   | UNI | NULL    |                |
| passwoed | varchar(20)  | NO   |     | NULL    |                |
| email    | varchar(30)  | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

修改数据表名称

语法：
ALTER TABLE tbl_name
RENAME [TO | AS ] newtbl_name
或者
RENAME tbl_name TO new_tbl_name

mysql> SHOW TABLES;
+----------------------+
| Tables_in_imooc_user |
+----------------------+
| test_auto_increment  |
| test_date            |
| test_datetime        |
| test_default         |
| test_default1        |
| test_enum            |
| test_fill0           |
| test_float           |
| test_float1          |
| test_notnull         |
| test_primary_key     |
| test_primary_key1    |
| test_primary_key2    |
| test_primary_key3    |
| test_set             |
| test_str             |
| test_time            |
| test_timestamp       |
| test_unique          |
| test_unsigned        |
| test_year            |
| user                 |
| user1                |
| user2                |
| user3                |
| user4                |
| user5                |
| user666              |
+----------------------+
28 rows in set (0.03 sec)

mysql> ALTER TABLE user666 RENAME TO user6;
Query OK, 0 rows affected (0.43 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_imooc_user |
+----------------------+
| test_auto_increment  |
| test_date            |
| test_datetime        |
| test_default         |
| test_default1        |
| test_enum            |
| test_fill0           |
| test_float           |
| test_float1          |
| test_notnull         |
| test_primary_key     |
| test_primary_key1    |
| test_primary_key2    |
| test_primary_key3    |
| test_set             |
| test_str             |
| test_time            |
| test_timestamp       |
| test_unique          |
| test_unsigned        |
| test_year            |
| user                 |
| user1                |
| user2                |
| user3                |
| user4                |
| user5                |
| user6                |
+----------------------+
28 rows in set (0.04 sec)

mercies

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql之表结构的修改

文章目录1. 添加字段2. 删除字段3. 一次进行多个操作4.动态添加默认值5.动态删除默认值6.动态修改字段的类型和属性7. 修改字段名称，字段类型和字段属性8. 添加主键字段9. 删除主键字段10. 添加unique11.删除unique修改数据表名称我们如果创建了一个表，但是现在需要多添加一个字段，你可以删除这个表，再重新创建一个表，但是这种方法比较麻烦，费时。那么就需要以下的方式1. 添加字段ALTER TABLE tbl_nameADD 字段名称 [字段完整性约束条件] [FIRST |
复制链接

扫一扫

专栏目录