当前位置：Gxlcms > 数据库问题 > MySQL操作之DDL

MySQL操作之DDL

时间：2021-07-01 10:21:17 帮助过：14人阅读

；“1 row
affected”表示操作只影响了数据库中一行的记录，“0.00 sec”则记录了操作执行的时间。
如果已经存在这个数据库，系统会提示：

mysql> create database test1;
ERROR 1007 (HY000): Can't create database 'test1'; database exists

这个时候，如果需要知道系统中都存在哪些数据库，可以用以下命令来查看：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| plf                |
| test               |
| test1              |
+--------------------+
6 rows in set (0.00 sec)

可以发现，在上面的列表中除了刚刚创建的test1 外，还有另外4 个数据库，它们都是安装
MySQL 时系统自动创建的，其各自功能如下。

information_schema：主要存储了系统中的一些数据库对象信息。比如用户表信息、列信
息、权限信息、字符集信息、分区信息等。
performance_schema：主要用于收集数据库服务器性能参数
mysql：存储了系统的用户权限信息。
test：系统自动创建的测试数据库，任何用户都可以使用。

在查看了系统中已有的数据库后，可以用如下命令选择要操作的数据库：

USE dbname

例如：选择数据库test1:

mysql> use test1
Database changed

然后在用以下命令来查看test1数据库中创建的所有数据表：

mysql> show tables;
Empty set (0.00 sec)

由于test1 是刚创建的数据库，还没有表，所以显示为空。命令行下面的“Empty set”表示
操作的结果集为空。如果查看一下mysql 数据库里面的表，则可以得到以下信息：

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| employee                  |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
29 rows in set (0.00 sec)

删除数据库

删除数据库的语法很简单，如下所示：

drop database dbname;

例如，要删除test1 数据库可以使用以下语句：

mysql> drop database test1;
Query OK, 0 rows affected (0.00 sec)

可以发现，提示操作成功之后，后面却显示了“0 rows affected”，这个提示可以不用管它，在
MySQL 里面，drop 语句操作的结果显示都是“0 rows affected”。

PS:? ?数据库删除后，下面的所有表数据都会全部删除，所以删除前一定要仔细检查并做好相对应备份

创建表

在数据库中创建一张表的基本语法如下：

CREATE TABLE tablename (column_name_1 column_type_1 constraints，
column_name_2 column_type_2 constraints ， ……column_name_n column_type_n
constraints）

因为MySQL 的表名是以目录的形式存在于磁盘上，所以表名的字符可以用任何目录名允许

的字符。 column_name 是列的名字，column_type 是列的数据类型，contraints 是这个列的约

束条件，在后面的章节中会详细介绍。

例如，创建一个名称为emp 的表。表中包括3 个字段，ename（姓名），hiredate（雇用日期）、
sal（薪水），字段类型分别为varchar（10）、date、int（2）（关于字段类型将会在下一章中
介绍）：

mysql> create table emp(
ename varchar(10),
hiredate date,
sal decimal(10,2),
deptno int(2));
Query OK, 0 rows affected (0.02 sec)

表创建完毕后，如果需要查看一下表的定义，可以使用如下命令：

DESC tablename

例如，查看emp 表，将输出以下信息：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

虽然desc 命令可以查看表定义，但是其输出的信息还是不够全面，为了查看更全面的表定
义信息，有时就需要通过查看创建表的SQL 语句来得到，可以使用如下命令实现：

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(20) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

从上面表的创建SQL 语句中，除了可以看到表定义以外，还可以看到表的engine（存储引擎）
和charset（字符集）等信息。“\G”选项的含义是使得记录能够按照字段竖着排列，对于内
容比较长的记录更易于显示。

删除表

表的删除命令如下：

DROP TABLE tablename

例如，要删除数据库emp 可以使用以下命令：

mysql> drop table emp;
Query OK, 0 rows affected (0.00 sec)

修改表

对于已经创建好的表，尤其是已经有大量数据的表，如果需要对表做一些结构上的改变，我
们可以先将表删除（drop），然后再按照新的表定义重建表。这样做没有问题，但是必然要
做一些额外的工作，比如数据的重新加载。而且，如果有服务在访问表，也会对服务产生影
响。
因此，在大多数情况下，表结构的更改一般都使用alter table 语句，以下是一些常用的命令。

修改表类型，语法如下：

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

例如：修改表emp 的ename 字段定义，将varchar(10)改为varchar(20)：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

增加表字段，语法如下：

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]

例如，表emp 上新增加字段age，类型为int(3)：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp add  column age int(3);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段，语法如下：

ALTER TABLE tablename DROP [COLUMN] col_name

例如，将字段age 删除掉：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp drop age;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

字段改名，语法如下：

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]

例如，将deptno改名为hehe，同时修改字段类型为int(4)：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp change deptno hehe int(4);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hehe     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

PS:? change 和modify 都可以修改表的定义，不同的是change 后面需要写两次列名，不方便。但是change 的优点是可以修改列名称，modify 则不能。

修改字段排列顺序

前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项first|after
column_name，这个选项可以用来修改字段在表中的位置，默认ADD 增加的新字段是加在
表的最后位置，而CHANGE/MODIFY 默认都不会改变字段的位置。
例如，将新增的字段birth date 加在ename 之后：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hehe     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 指定添加到ename之后
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hehe     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

# 默认添加到最后
mysql> alter table emp add hello int(100);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hehe     | int(4)        | YES  |     | NULL    |       |
| hello    | int(100)      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改字段hehe，并将它放到最前面

mysql> alter table emp modify hehe int(2) first;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| hehe     | int(2)        | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hello    | int(100)      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

CHANGE/FIRST|AFTER COLUMN 这些关键字都属于MySQL 在标准SQL 上的扩展，在其他数据库上不一定适用。

更改表名，语法如下：

ALTER TABLE tablename RENAME [TO] new_tablename

例如，将表emp 改名为emp1，命令如下：

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables
    -> ;
+---------------+
| Tables_in_plf |
+---------------+
| emp1          |
| hk_info       |
| log_info      |
| user_info     |
+---------------+
4 rows in set (0.00 sec)

mysql> desc emp;
ERROR 1146 (42S02): Table 'plf.emp' doesn't exist

mysql> desc emp1;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| hehe     | int(2)        | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| hello    | int(100)      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MySQL操作之DDL

标签：column href revoke 比较 har 备份 ica its 帮助

MySQL操作之DDL

删除数据库

创建表

删除表

修改表

修改字段排列顺序

更改表名，语法如下：

人气教程排行