DDL（Data Definition Languages）语句：数据定义语言，简单说就是对数据库内部的对象进行创建、修改、删除的操作语言。DDL语句更多的被数据库管理人员（DBA）使用，一般开发人员很少用。
Example 1: 创建数据库
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.45Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create databasetest1; //创建数据库test1
Query OK, 1 row affected (0.04 sec) //创建成功mysql> create database test1;
ERROR 1007 (HY000): Can't create database 'test1'; database exists //因test1已存在，所以创建失败
mysql> show databases; //查看已存在的数据库 ，除了test1以外，是安装mysql是系统自动创建的
+--------------------+
| Database |
+--------------------+
| information_schema | //存储了系统中的一些数据库对象信息，如用户表信息、列信息、权限信息等
| mysql | //存储的系统的用户权限信息
| test | //任何用户都可使用的测试数据库
| test1 |
+--------------------+
4 rows in set (0.13 sec)mysql> use //选择要操作的数据库test1
Database changed
mysql> show//查看test1中的所有数据表
Empty set (0.00 sec)mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables; //查看mysql中的所有数据表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
Example 2 : 删除数据库mysql> drop database //删除test1
Query OK, 0 rows affected (0.05 sec) Example 3 : 创建表
基本语法：
CREATE TABLE tablename(column_name_1 column_type_1 constraints,column_name_2 column_type_2 constraints,...column_name_n column_type_n constraints) //其中column_name列名，column_type列的数据类型， constraints列的约束条件eg NOT NULL | NULL,指定该列是否允许为空,如果既不指定NULL也不指定NOT NULL，列被认为指定了NULL。
mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2)); //创建表emp，表中包含四个字段ename , hiredate , sal , deptno
Query OK, 0 rows affected (0.09 sec) //创建成功mysql> desc emp; //查看emp表
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+----------+-------+
| ename | varchar(10) | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
+-----------+------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)mysql> show create table emp \G; //查看相对desc更全面的表定义信息，查看创建表的SQL语句，“\G”选项的含义使得记录能够按照字段竖着排列，易于显示
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) default NULL,
`hiredate` date default NULL,
`sal` decimal(10,2) default NULL,
`deptno` int(2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 //除了显示表的定义外，还可以看到表的引擎和字符集等信息
1 row in set (0.00 sec)ERROR:
No query specifiedExample 4 : 删除表
mysql> drop table emp; //删除表
Query OK, 0 rows affected (0.00 sec)
Example 5 : 修改表
5.1 修改表格类型
基本语法：
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] ，[FIRST | AFTER col_name]，用来修改字段在表中的位置，默认不会修改，新增加的字段放在最后，该关键字是MySQL在标准SQL上的扩展
mysql> alter table emp modify //修改表emp的ename字段定义，将varchar(10)改成varchar(20)
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc//查看修改后的表
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5.2 增加表字段mysql> alter table emp add column age int(3); //为表emp增加新字段age,类型为int(3)
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
| age | int(3) | YES | | | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
5.3 删除表字段mysql> alter table emp drop column age; //删除age字段
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5.4 字段改名********************change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便，而只有 change 可以修改列的名称********************
mysql> alter table emp change //将age改为agel，同时修改字段类型为int(4)
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
| agel | int(4) | YES | | | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)5.5 修改字段排列顺序
mysql> alter table emp add birth date after //在表emp中新增加字段birth，类型date,并把它放在字段ename的后面
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp ;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | | |
| birth | date | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
| age | int(3) | YES | | | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)mysql> alter table emp modify age int(3) first; //将age字段放到最前面
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age | int(3) | YES | | | |
| ename | varchar(20) | YES | | | |
| birth | date | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)5.6 更改表名
mysql> alter table emp rename emp1; //更改表名为emp1
Query OK, 0 rows affected (0.00 sec)
mysql> desc emp;
ERROR 1146 (42S02): Table 'mysql.emp' doesn't exist
mysql> desc emp1;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age | int(3) | YES | | | |
| ename | varchar(20) | YES | | | |
| birth | date | YES | | | |
| hiredate | date | YES | | | |
| sal | decimal(10,2) | YES | | | |
| deptno | int(2) | YES | | | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
