mysql在哪里写ddl语句_MySQL使用入门--DDL语句

DDL 语句

1、创建数据库

CREATE DATABASE dbname;

查看已经存在的数据库

SHOW DATABASES;

选择数据库

USE dbname;

查看数据库中的表

SHOW TABLES;

2、删除数据库

DROP DATABASE dbname;

3、创建表

CREATE TABLEtablename(column_name_1 column_type_1 constraints,

column_name_2 column_type_2 constraints,

......

column_name_n column_type_n constraints);

查看表

DESC tablename；

查看创建表的SQL语句

SHOW CREATE TABLE tablename \G

4、删除表

DROP TABLE tablename；

5、修改表

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

示例

mysql> CREATE TABLE emp(ename VARCHAR(10),->hiredate DATE,-> sal DECIMAL(10,2),-> deptno INT(2));

Query OK,0 rows affected (0.02sec)

mysql> DESCemp;+----------+---------------+------+-----+---------+-------+

| 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.00sec)

mysql> ALTER TABLE emp MODIFY ename VARCHAR(20);

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCemp;+----------+---------------+------+-----+---------+-------+

| 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];

示例

mysql> ALTER TABLE emp ADD COLUMN age INT(3);

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCemp;+----------+---------------+------+-----+---------+-------+

| 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;

字段改名

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

示例

mysql> DESCemp;+----------+---------------+------+-----+---------+-------+

| 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.00sec)

mysql> ALTER TABLE emp CHANGE COLUMN age age1 INT(4);

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCemp;+----------+---------------+------+-----+---------+-------+

| 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 | |

| age1 | int(4) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

注意：change和modify都可以修改表的定义，

不同的是change后面需要写两次列名，不方便。

但是change的优点是可以修改列名称，modify则不能。

修改字段排列顺序

上面字段增加和修改语法(ADD、CHANGE、MODIFY)中，都有可选项 FIRST | AFTER column_name，默认ADD增加的新字段是加在表的最后位置，而CHANGE/MODIFY默认都不会改变字段的位置。

FIRST :代表将字段位置添加或修改到第一列。

AFTER column_name : 代表将字段位置添加或者修改到column_name字段的后面。

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

修改表名称

ALTER TABLE tablename RENAME [TO] new_tablename;