Data Definition Language 是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| learning |
| learning3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.02 sec)
mysql> drop database learning3;
Query OK, 1 row affected (0.31 sec)
mysql> create database learning3;
Query OK, 1 row affected (0.07 sec)
mysql> use learning3;
Database changed
mysql> create table houses (name varchar(100) not null,
-> house_location varchar(100) not null,
-> purchasing_year varchar(100) not null);
Query OK, 0 rows affected (0.25 sec)
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| houses |
+---------------------+
1 row in set (0.00 sec)
mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| purchasing_year | varchar(100) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table houses drop purchasing_year;
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc houses;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table houses add purchasing_year varchar(100) after house_location;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| purchasing_year | varchar(100) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table houses modify purchasing_year char(100);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc houses;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| purchasing_year | char(100) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table houses change purchasing_year year varchar(100);
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc houses;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| house_location | varchar(100) | NO | | NULL | |
| year | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table houses rename to bighouses;
Query OK, 0 rows affected (0.24 sec)
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| bighouses |
+---------------------+
1 row in set (0.00 sec)
mysql> create table test(
-> id int(10) unsigned not null auto_increment,
-> name varchar(10) not null,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.27 sec)
mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.06 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.00 sec)
mysql> truncate test;
Query OK, 0 rows affected (0.49 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set, 1 warning (0.11 sec)
mysql> insert into test (name) values('abc');
Query OK, 1 row affected (0.05 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.00 sec)
mysql> delete from test;
Query OK, 1 row affected (0.04 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.07 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 2 | abc |
+----+------+
1 row in set (0.00 sec)
mysql> set global autocommit='off';
Query OK, 0 rows affected (0.03 sec)
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set, 1 warning (0.03 sec)
mysql> delete from test;
Query OK, 1 row affected (0.07 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.07 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 3 | abc |
+----+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test;
Query OK, 1 row affected (0.06 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> set session autocommit='off';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> insert into test (name) values ('abc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 4 | abc |
+----+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
mysql> delete from test;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 4 | abc |
+----+------+
1 row in set (0.00 sec)
mysql> truncate test;
Query OK, 0 rows affected (0.38 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.17 sec)
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| bighouses |
+---------------------+
1 row in set (0.00 sec)
mysql> drop table bighouses;
Query OK, 0 rows affected (0.18 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| learning |
| learning3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database learning3;
Query OK, 0 rows affected (0.08 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| learning |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)