devbox
羊村懒王
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL系列复习（4）DDL常见操作_drop table tablefortest if exists

作者：羊村懒王 | 2024-03-18 20:29:27

赞

踩

drop table tablefortest if exists

目录

1、库的管理

1.1、创建库

1.2、删除库

1.3、建库通用的写法

1.4、示例

2、表管理

2.1、创建表

2.1.1、格式

2.1.2、注意

2.1.2、约束说明

2.2、删除表

2.3、修改表名

2.4、表设置备注

2.5、复制表

2.6、复制表结构+数据

3、表中列的管理

3.1、添加列

3.2、修改列

3.3、删除列

环境：mysql5.7.30，cmd命令中进⾏演⽰。

DDL：Data Define Language数据定义语⾔，主要⽤来对数据库、表进⾏⼀些管理操作。如：建库、删库、建表、修改表、删除表、对列的增删改等等。
⽂中涉及到的语法⽤[]包含的内容属于可选项，下⾯做详细说明。

1、库的管理

1.1、创建库

create database [if not exists] 库名 [default character set=utf8mb4];

1.2、删除库

drop database [if exists] 库名;

1.3、建库通用的写法

drop database if exists 旧库名;

create database 新库名;

1.4、示例

  1. mysql> drop database if exists test01;
  2. Query OK, 6 rows affected (0.17 sec)
  3.  
  4. mysql> show databases like 'test01';
  5. Empty set (0.00 sec)
  6.  
  7. mysql> create database test01;
  8. Query OK, 1 row affected (0.01 sec)
  9.  
  10. mysql> show databases like 'test01';
  11. +-------------------+
  12. | Database (test01) |
  13. +-------------------+
  14. | test01            |
  15. +-------------------+
  16. 1 row in set (0.00 sec)
  17.  
  18. mysql>

2、表管理

2.1、创建表

2.1.1、格式

create table 表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
) [表的⼀些设置];

2.1.2、注意

  • 1. 在同⼀张表中，字段名不能相同；
  • 2. 宽度和约束条件为可选参数，字段名和类型是必须的；
  • 3. 最后⼀个字段后不能加逗号；
  • 4. 类型是⽤来限制字段，必须以何种数据类型来存储记录；
  • 5. 类型其实也是对字段的约束(约束字段下的记录必须为XX类型)；
  • 6. 类型后写的约束条件是在类型之外的额外添加的约束。

2.1.2、约束说明

1）not null：标识该字段不能为空

  1. mysql> use test01;
  2. Database changed
  3. mysql> select database();
  4. +------------+
  5. | database() |
  6. +------------+
  7. | test01     |
  8. +------------+
  9. 1 row in set (0.00 sec)
  10.  
  11. mysql> create table test1(a int not null comment '字段a');
  12. Query OK, 0 rows affected (0.08 sec)
  13.  
  14. mysql> desc test1;
  15. +-------+---------+------+-----+---------+-------+
  16. | Field | Type    | Null | Key | Default | Extra |
  17. +-------+---------+------+-----+---------+-------+
  18. | a     | int(11) | NO   |     | NULL    |       |
  19. +-------+---------+------+-----+---------+-------+
  20. 1 row in set (0.04 sec)
  21.  
  22. mysql> insert into test1 values(null);
  23. ERROR 1048 (23000): Column 'a' cannot be null
  24. mysql> insert into test1 values(1);
  25. Query OK, 1 row affected (0.04 sec)
  26.  
  27. mysql> select * from test1;
  28. +---+
  29. | a |
  30. +---+
  31. | 1 |
  32. +---+
  33. 1 row in set (0.00 sec)
  34.  
  35. mysql>

2）default value：为该字段设置默认值，默认值为value

  1. mysql> create table test2(
  2.     ->   a int not null comment '字段a',
  3.     ->   b int not null default 0 comment '字段b'
  4.     -> );
  5. Query OK, 0 rows affected (0.09 sec)
  6.  
  7. mysql> desc test2;
  8. +-------+---------+------+-----+---------+-------+
  9. | Field | Type    | Null | Key | Default | Extra |
  10. +-------+---------+------+-----+---------+-------+
  11. | a     | int(11) | NO   |     | NULL    |       |
  12. | b     | int(11) | NO   |     | 0       |       |
  13. +-------+---------+------+-----+---------+-------+
  14. 2 rows in set (0.00 sec)
  15.  
  16. mysql> insert into test2(a) values(1);
  17. Query OK, 1 row affected (0.04 sec)
  18.  
  19. mysql> select * from  test2;
  20. +---+---+
  21. | a | b |
  22. +---+---+
  23. | 1 | 0 |
  24. +---+---+
  25. 1 row in set (0.00 sec)
  26.  
  27. mysql>

上⾯插⼊时未设置b的值，⾃动取默认值0。

3）primary key：标识该字段为该表的主键，可以唯⼀的标识记录，插⼊重复的会报错
两种写法，如下：
方式1：跟在列后，如下：

  1. mysql> create table test3(
  2.     ->   a int not null comment '字段a' primary key
  3.     -> );
  4. Query OK, 0 rows affected (0.09 sec)
  5.  
  6. mysql> desc test3;
  7. +-------+---------+------+-----+---------+-------+
  8. | Field | Type    | Null | Key | Default | Extra |
  9. +-------+---------+------+-----+---------+-------+
  10. | a     | int(11) | NO   | PRI | NULL    |       |
  11. +-------+---------+------+-----+---------+-------+
  12. 1 row in set (0.00 sec)
  13.  
  14. mysql> insert into test3(a) values(1);
  15. Query OK, 1 row affected (0.04 sec)
  16.  
  17. mysql> insert into test3(a) values(1);
  18. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  19. mysql> select * from test3;
  20. +---+
  21. | a |
  22. +---+
  23. | 1 |
  24. +---+
  25. 1 row in set (0.00 sec)
  26.  
  27. mysql>

方式2：在所有列定义之后定义，如下：

  1. mysql> drop table IF EXISTS test4;
  2. ERROR 2006 (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id:    14
  5. Current database: test01
  6.  
  7. Query OK, 0 rows affected, 1 warning (0.02 sec)
  8.  
  9. mysql> create table test4(
  10.     ->   a int not null comment '字段a',
  11.     ->   b int not null default 0 comment '字段b',
  12.     ->   primary key(a)
  13.     -> );
  14. Query OK, 0 rows affected (0.07 sec)
  15.  
  16. mysql> desc test4;
  17. +-------+---------+------+-----+---------+-------+
  18. | Field | Type    | Null | Key | Default | Extra |
  19. +-------+---------+------+-----+---------+-------+
  20. | a     | int(11) | NO   | PRI | NULL    |       |
  21. | b     | int(11) | NO   |     | 0       |       |
  22. +-------+---------+------+-----+---------+-------+
  23. 2 rows in set (0.01 sec)
  24.  
  25. mysql> insert into test4(a,b) values(1,1);
  26. Query OK, 1 row affected (0.02 sec)
  27.  
  28. mysql> select * from test4;
  29. +---+---+
  30. | a | b |
  31. +---+---+
  32. | 1 | 1 |
  33. +---+---+
  34. 1 row in set (0.02 sec)
  35.  
  36. mysql> insert into test4(a,b) values(1,2);
  37. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  38. mysql>

插⼊重复的值，会报违法主键约束
方式2⽀持多字段作为主键，多个之间⽤逗号隔开，语法：primary key(字段1,字段2,字段n)，示例：

  1. mysql> drop table IF EXISTS test7;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> create table test7(
  5.     ->   a int not null comment '字段a',
  6.     ->   b int not null comment '字段b',
  7.     ->  PRIMARY KEY (a,b)
  8.     -> );
  9. Query OK, 0 rows affected (0.03 sec)
  10.  
  11. mysql> desc test7;
  12. +-------+---------+------+-----+---------+-------+
  13. | Field | Type    | Null | Key | Default | Extra |
  14. +-------+---------+------+-----+---------+-------+
  15. | a     | int(11) | NO   | PRI | NULL    |       |
  16. | b     | int(11) | NO   | PRI | NULL    |       |
  17. +-------+---------+------+-----+---------+-------+
  18. 2 rows in set (0.00 sec)
  19.  
  20. mysql> insert into test7(a,b) values(1,1);
  21. Query OK, 1 row affected (0.04 sec)
  22.  
  23. mysql> select * from test7;
  24. +---+---+
  25. | a | b |
  26. +---+---+
  27. | 1 | 1 |
  28. +---+---+
  29. 1 row in set (0.00 sec)
  30.  
  31. mysql> insert into test7(a,b) values(1,1);
  32. ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'
  33. mysql>

4）foreign key：为表中的字段设置外键
语法：foreign key(当前表的列名) references 引⽤的外键表(外键表中字段名称)

  1. mysql> drop table IF EXISTS test6;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> drop table IF EXISTS test5;
  5. Query OK, 0 rows affected, 1 warning (0.00 sec)
  6.  
  7. mysql>
  8. mysql> create table test5(
  9.     ->   a int not null comment '字段a' primary key
  10.     -> );
  11. Query OK, 0 rows affected (0.02 sec)
  12.  
  13. mysql> desc test5;
  14. +-------+---------+------+-----+---------+-------+
  15. | Field | Type    | Null | Key | Default | Extra |
  16. +-------+---------+------+-----+---------+-------+
  17. | a     | int(11) | NO   | PRI | NULL    |       |
  18. +-------+---------+------+-----+---------+-------+
  19. 1 row in set (0.00 sec)
  20.  
  21. mysql>
  22. mysql>
  23. mysql> create table test6(
  24.     ->  b int not null comment '字段b',
  25.     ->  ts5_a int not null,
  26.     ->  foreign key(ts5_a) references test5(a)
  27.     -> );
  28. Query OK, 0 rows affected (0.03 sec)
  29.  
  30. mysql> desc test6;
  31. +-------+---------+------+-----+---------+-------+
  32. | Field | Type    | Null | Key | Default | Extra |
  33. +-------+---------+------+-----+---------+-------+
  34. | b     | int(11) | NO   |     | NULL    |       |
  35. | ts5_a | int(11) | NO   | MUL | NULL    |       |
  36. +-------+---------+------+-----+---------+-------+
  37. 2 rows in set (0.01 sec)
  38.  
  39. mysql> insert into test5(a) values(1);
  40. Query OK, 1 row affected (0.04 sec)
  41.  
  42. mysql> insert into test6(b,test6.ts5_a) values(1,1);
  43. Query OK, 1 row affected (0.01 sec)
  44.  
  45. mysql> select * from test6;
  46. +---+-------+
  47. | b | ts5_a |
  48. +---+-------+
  49. | 1 |     1 |
  50. +---+-------+
  51. 1 row in set (0.00 sec)
  52.  
  53. mysql> select * from test5;
  54. +---+
  55. | a |
  56. +---+
  57. | 1 |
  58. +---+
  59. 1 row in set (0.00 sec)
  60.  
  61. mysql> insert into test6(b,test6.ts5_a) values(2,2);
  62. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test01`.`test6`, CONSTRAINT `test6_ibfk_1` FOREIGN KEY (`ts5_a`) REFERENCES `test5` (`a`))
  63. mysql>

说明：表⽰test6中ts5_a字段的值来源于表test5中的字段a。
注意⼏点：

  • 两张表中需要建⽴外键关系的字段类型需要⼀致
  • 要设置外键的字段不能为主键
  • 被引⽤的字段需要为主键
  • 被插⼊的值在外键表必须存在，如上⾯向test6中插⼊ts5_a为2的时候报错了，原因：2的值在test5表中不存在

5）unique key(uq)：标识该字段的值是唯⼀的
⽀持⼀个到多个字段，插⼊重复的值会报违反唯⼀约束，会插⼊失败。
定义有2种⽅式。
方式1：跟在字段后，如下：

  1. mysql> drop table IF EXISTS test8;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> create table test8(
  5.     ->   a int not null comment '字段a' unique key
  6.     -> );
  7. Query OK, 0 rows affected (0.05 sec)
  8.  
  9. mysql> desc test8;
  10. +-------+---------+------+-----+---------+-------+
  11. | Field | Type    | Null | Key | Default | Extra |
  12. +-------+---------+------+-----+---------+-------+
  13. | a     | int(11) | NO   | PRI | NULL    |       |
  14. +-------+---------+------+-----+---------+-------+
  15. 1 row in set (0.01 sec)
  16.  
  17. mysql> insert into test8(a) values(1);
  18. Query OK, 1 row affected (0.01 sec)
  19.  
  20. mysql> insert into test8(a) values(1);
  21. ERROR 1062 (23000): Duplicate entry '1' for key 'a'
  22. mysql> select * from test8;
  23. +---+
  24. | a |
  25. +---+
  26. | 1 |
  27. +---+
  28. 1 row in set (0.00 sec)
  29.  
  30. mysql>

方式2：所有列定义之后定义，如下：

  1. mysql> drop table IF EXISTS test9;
  2. Query OK, 0 rows affected, 1 warning (0.01 sec)
  3.  
  4. mysql> create table test9(
  5.     ->   a int not null comment '字段a',
  6.     ->  unique key(a)
  7.     -> );
  8. Query OK, 0 rows affected (0.03 sec)
  9.  
  10. mysql> desc test9;
  11. +-------+---------+------+-----+---------+-------+
  12. | Field | Type    | Null | Key | Default | Extra |
  13. +-------+---------+------+-----+---------+-------+
  14. | a     | int(11) | NO   | PRI | NULL    |       |
  15. +-------+---------+------+-----+---------+-------+
  16. 1 row in set (0.00 sec)
  17.  
  18. mysql> insert into test9(a) values(1);
  19. Query OK, 1 row affected (0.02 sec)
  20.  
  21. mysql> insert into test9(a) values(1);
  22. ERROR 1062 (23000): Duplicate entry '1' for key 'a'
  23. mysql>

方式2⽀持多字段，多个之间⽤逗号隔开，语法：primary key(字段1,字段2,字段n)，示例：

  1. mysql> drop table IF EXISTS test10;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> create table test10(
  5.     ->   a int not null comment '字段a',
  6.     ->   b int not null comment '字段b',
  7.     ->   unique key(a,b)
  8.     -> );
  9. Query OK, 0 rows affected (0.06 sec)
  10.  
  11. mysql> desc test10;
  12. +-------+---------+------+-----+---------+-------+
  13. | Field | Type    | Null | Key | Default | Extra |
  14. +-------+---------+------+-----+---------+-------+
  15. | a     | int(11) | NO   | PRI | NULL    |       |
  16. | b     | int(11) | NO   | PRI | NULL    |       |
  17. +-------+---------+------+-----+---------+-------+
  18. 2 rows in set (0.00 sec)
  19.  
  20. mysql> insert into test10(a,b) values(1,1);
  21. Query OK, 1 row affected (0.04 sec)
  22.  
  23. mysql> insert into test10(a,b) values(1,1);
  24. ERROR 1062 (23000): Duplicate entry '1-1' for key 'a'
  25. mysql> select * from test10;
  26. +---+---+
  27. | a | b |
  28. +---+---+
  29. | 1 | 1 |
  30. +---+---+
  31. 1 row in set (0.00 sec)
  32.  
  33. mysql>

6）auto_increment：标识该字段的值⾃动增长（整数类型，⽽且为主键）

  1. mysql> drop table IF EXISTS test11;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> create table test11(
  5.     ->   a int not null AUTO_INCREMENT KEY comment '字段a',
  6.     ->   b int not null comment '字段b'
  7.     -> );
  8. Query OK, 0 rows affected (0.05 sec)
  9.  
  10. mysql> desc test11;
  11. +-------+---------+------+-----+---------+----------------+
  12. | Field | Type    | Null | Key | Default | Extra          |
  13. +-------+---------+------+-----+---------+----------------+
  14. | a     | int(11) | NO   | PRI | NULL    | auto_increment |
  15. | b     | int(11) | NO   |     | NULL    |                |
  16. +-------+---------+------+-----+---------+----------------+
  17. 2 rows in set (0.01 sec)
  18.  
  19. mysql> insert into test11(b) values(10);
  20. Query OK, 1 row affected (0.01 sec)
  21.  
  22. mysql> insert into test11(b) values(20);
  23. Query OK, 1 row affected (0.02 sec)
  24.  
  25. mysql> select * from test11;
  26. +---+----+
  27. | a | b  |
  28. +---+----+
  29. | 1 | 10 |
  30. | 2 | 20 |
  31. +---+----+
  32. 2 rows in set (0.00 sec)
  33.  
  34. mysql>

字段a为⾃动增长，默认值从1开始，每次+1
关于⾃动增长字段的初始值、步长可以在mysql中进⾏设置，比如设置初始值为1万，每次增长10

注意：

⾃增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中⾃增列的最⼤值作为当前值，如果表数据被清空之后，数据库重启了，⾃增列的值将从初始值开始。
我们来演⽰⼀下：

  1. mysql> delete from test11;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> insert into test11(b) values(10);
  5. Query OK, 1 row affected (0.04 sec)
  6.  
  7. mysql> select * from test11;
  8. +---+----+
  9. | a | b  |
  10. +---+----+
  11. | 3 | 10 |
  12. +---+----+
  13. 1 row in set (0.00 sec)
  14.  
  15. mysql>

上⾯删除了test11数据，然后插⼊了⼀条，a的值为3，执⾏下⾯操作：
删除test11数据，重启mysql，插⼊数据，然后看a的值是不是被初始化了？如下：

  1. mysql> delete from test11;
  2. Query OK, 1 row affected (0.01 sec)
  3.  
  4. mysql> select * from test11;
  5. Empty set (0.00 sec)
  6.  
  7. mysql> exit
  8. Bye
  1. PS C:\Windows\system32> net stop mysql;
  2. MySQL 服务正在停止.
  3. MySQL 服务已成功停止。
  4.  
  5. PS C:\Windows\system32> net start mysql;
  6. MySQL 服务正在启动 .
  7. MySQL 服务已经启动成功。
  8.  
  9. PS C:\Windows\system32>
  10. PS C:\Windows\system32> mysql -uroot -p
  11. Enter password: ****
  12. Welcome to the MySQL monitor.  Commands end with ; or \g.
  13. Your MySQL connection id is 2
  14. Server version: 5.7.30 MySQL Community Server (GPL)
  15.  
  16. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  17.  
  18. Oracle is a registered trademark of Oracle Corporation and/or its
  19. affiliates. Other names may be trademarks of their respective
  20. owners.
  21.  
  22. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  23.  
  24. mysql> use test01;
  25. Database changed
  26. mysql> insert into test11(b) value(100);
  27. Query OK, 1 row affected (0.04 sec)
  28.  
  29. mysql> select * from test11;
  30. +---+-----+
  31. | a | b   |
  32. +---+-----+
  33. | 1 | 100 |
  34. +---+-----+
  35. 1 row in set (0.00 sec)
  36.  
  37. mysql>

2.2、删除表

删除表结构和数据。

drop table [if exists] 表名;

  1. mysql> create table test12(
  2.     ->   a int not null comment '字段a' primary key,
  3.     ->   b int not null comment '字段b'
  4.     -> );
  5. Query OK, 0 rows affected (0.05 sec)
  6.  
  7. mysql> insert into test12(a,b) values(1,1);
  8. Query OK, 1 row affected (0.04 sec)
  9.  
  10. mysql> select * from test12;
  11. +---+---+
  12. | a | b |
  13. +---+---+
  14. | 1 | 1 |
  15. +---+---+
  16. 1 row in set (0.00 sec)
  17.  
  18. mysql> drop table test12;
  19. Query OK, 0 rows affected (0.04 sec)
  20.  
  21. mysql> show tables;
  22. +------------------+
  23. | Tables_in_test01 |
  24. +------------------+
  25. | test1            |
  26. | test10           |
  27. | test11           |
  28. | test2            |
  29. | test3            |
  30. | test4            |
  31. | test5            |
  32. | test6            |
  33. | test7            |
  34. | test8            |
  35. | test9            |
  36. +------------------+
  37. 11 rows in set (0.00 sec)
  38.  
  39. mysql>

2.3、修改表名

alter table 表名 rename [to] 新表名;

  1. mysql> show tables;
  2. +------------------+
  3. | Tables_in_test01 |
  4. +------------------+
  5. | test1            |
  6. | test10           |
  7. | test11           |
  8. | test2            |
  9. | test3            |
  10. | test4            |
  11. | test5            |
  12. | test6            |
  13. | test7            |
  14. | test8            |
  15. | test9            |
  16. +------------------+
  17. 11 rows in set (0.00 sec)
  18.  
  19. mysql> alter table test9 rename to test99;
  20. Query OK, 0 rows affected (0.04 sec)
  21.  
  22. mysql> show tables;
  23. +------------------+
  24. | Tables_in_test01 |
  25. +------------------+
  26. | test1            |
  27. | test10           |
  28. | test11           |
  29. | test2            |
  30. | test3            |
  31. | test4            |
  32. | test5            |
  33. | test6            |
  34. | test7            |
  35. | test8            |
  36. | test99           |
  37. +------------------+
  38. 11 rows in set (0.00 sec)
  39.  
  40. mysql>

2.4、表设置备注

alter table 表名 comment '备注信息';

  1. mysql> alter table test99 comment '测试表99';
  2. Query OK, 0 rows affected (0.04 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> show create table test99;
  6. +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
  7. | Table  | Create Table                                                                                                                                        |
  8. +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
  9. | test99 | CREATE TABLE `test99` (
  10.   `a` int(11) NOT NULL COMMENT '字段a',
  11.   UNIQUE KEY `a` (`a`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表99'      |
  13. +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
  14. 1 row in set (0.00 sec)
  15.  
  16. mysql>

2.5、复制表

只复制表结构

create table 表名 like 被复制的表名;

  1. mysql> create table test12 like test11;
  2. Query OK, 0 rows affected (0.03 sec)
  3.  
  4. mysql> select * from test12;
  5. Empty set (0.00 sec)
  6.  
  7. mysql> desc test12;
  8. +-------+---------+------+-----+---------+----------------+
  9. | Field | Type    | Null | Key | Default | Extra          |
  10. +-------+---------+------+-----+---------+----------------+
  11. | a     | int(11) | NO   | PRI | NULL    | auto_increment |
  12. | b     | int(11) | NO   |     | NULL    |                |
  13. +-------+---------+------+-----+---------+----------------+
  14. 2 rows in set (0.00 sec)
  15.  
  16. mysql> show create table test12;
  17. +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  18. | Table  | Create Table                                                                                                                                                                        |
  19. +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  20. | test12 | CREATE TABLE `test12` (
  21.   `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  22.   `b` int(11) NOT NULL COMMENT '字段b',
  23.   PRIMARY KEY (`a`)
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8     |
  25. +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  26. 1 row in set (0.00 sec)
  27.  
  28. mysql>

2.6、复制表结构+数据

create table 表名 [as] select 字段,... from 被复制的表 [where 条件];
如：

  1. mysql> create table test13 as select * from test11;
  2. Query OK, 1 row affected (0.04 sec)
  3. Records: 1  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> select * from test13;
  6. +---+-----+
  7. | a | b   |
  8. +---+-----+
  9. | 1 | 100 |
  10. +---+-----+
  11. 1 row in set (0.00 sec)
  12.  
  13. mysql>

表结构和数据都过来了。

3、表中列的管理

3.1、添加列

alter table 表名 add column 列名 类型 [列约束];
示例：

  1. mysql> drop table IF EXISTS test14;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> create table test14(
  5.     ->   a int not null AUTO_INCREMENT PRIMARY KEY comment '字段a'
  6.     -> );
  7. Query OK, 0 rows affected (0.03 sec)
  8.  
  9. mysql> desc test14;
  10. +-------+---------+------+-----+---------+----------------+
  11. | Field | Type    | Null | Key | Default | Extra          |
  12. +-------+---------+------+-----+---------+----------------+
  13. | a     | int(11) | NO   | PRI | NULL    | auto_increment |
  14. +-------+---------+------+-----+---------+----------------+
  15. 1 row in set (0.00 sec)
  16.  
  17. mysql> alter table test14 add column b int not null default 0 comment '字段b';
  18. Query OK, 0 rows affected (0.07 sec)
  19. Records: 0  Duplicates: 0  Warnings: 0
  20.  
  21. mysql> alter table test14 add column c int not null default 0 comment '字段c';
  22. Query OK, 0 rows affected (0.06 sec)
  23. Records: 0  Duplicates: 0  Warnings: 0
  24.  
  25. mysql> desc test14;
  26. +-------+---------+------+-----+---------+----------------+
  27. | Field | Type    | Null | Key | Default | Extra          |
  28. +-------+---------+------+-----+---------+----------------+
  29. | a     | int(11) | NO   | PRI | NULL    | auto_increment |
  30. | b     | int(11) | NO   |     | 0       |                |
  31. | c     | int(11) | NO   |     | 0       |                |
  32. +-------+---------+------+-----+---------+----------------+
  33. 3 rows in set (0.00 sec)
  34.  
  35. mysql> insert into test14(b) values(10);
  36. Query OK, 1 row affected (0.01 sec)
  37.  
  38. mysql> select * from test14;
  39. +---+----+---+
  40. | a | b  | c |
  41. +---+----+---+
  42. | 1 | 10 | 0 |
  43. +---+----+---+
  44. 1 row in set (0.00 sec)
  45.  
  46. mysql>

3.2、修改列

alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];
2种⽅式区别：modify不能修改列名，change可以修改列名
我们看⼀下test14的表结构：

  1. mysql> show create table test14;
  2. +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table  | Create Table                                                                                                                                                                                                                                                           |
  4. +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | test14 | CREATE TABLE `test14` (
  6.   `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  7.   `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
  8.   `c` int(11) NOT NULL DEFAULT '0' COMMENT '字段c',
  9.   PRIMARY KEY (`a`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8       |
  11. +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. 1 row in set (0.00 sec)
  13.  
  14. mysql>

我们将字段c名字及类型修改⼀下，如下:

  1. mysql> alter table test14 change column c d varchar(10) not null default '' comment '字段d';
  2. Query OK, 1 row affected (0.06 sec)
  3. Records: 1  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> desc test14;
  6. +-------+-------------+------+-----+---------+----------------+
  7. | Field | Type        | Null | Key | Default | Extra          |
  8. +-------+-------------+------+-----+---------+----------------+
  9. | a     | int(11)     | NO   | PRI | NULL    | auto_increment |
  10. | b     | int(11)     | NO   |     | 0       |                |
  11. | d     | varchar(10) | NO   |     |         |                |
  12. +-------+-------------+------+-----+---------+----------------+
  13. 3 rows in set (0.00 sec)
  14.  
  15. mysql> show create table test14;
  16. +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  17. | Table  | Create Table                                                                                                                                                                                                                                                              |
  18. +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  19. | test14 | CREATE TABLE `test14` (
  20.   `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  21.   `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
  22.   `d` varchar(10) NOT NULL DEFAULT '' COMMENT '字段d',
  23.   PRIMARY KEY (`a`)
  24. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8       |
  25. +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  26. 1 row in set (0.00 sec)
  27.  
  28. mysql>

3.3、删除列

alter table 表名 drop column 列名;

  1. mysql> alter table test14 drop column d;
  2. Query OK, 0 rows affected (0.11 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> desc test14;
  6. +-------+---------+------+-----+---------+----------------+
  7. | Field | Type    | Null | Key | Default | Extra          |
  8. +-------+---------+------+-----+---------+----------------+
  9. | a     | int(11) | NO   | PRI | NULL    | auto_increment |
  10. | b     | int(11) | NO   |     | 0       |                |
  11. +-------+---------+------+-----+---------+----------------+
  12. 2 rows in set (0.00 sec)
  13.  
  14. mysql> show create table test14;
  15. +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. | Table  | Create Table                                                                                                                                                                                                     |
  17. +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  18. | test14 | CREATE TABLE `test14` (
  19.   `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  20.   `b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
  21.   PRIMARY KEY (`a`)
  22. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8     |
  23. +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  24. 1 row in set (0.00 sec)
  25.  
  26. mysql>

 

 

 

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/265557
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号