MySQL DDL 修改表字段 修改表类型

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

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test1;                                                              //创建数据库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;                                         //选择要操作的数据库test1
Database changed
mysql> show tables;                                    //查看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 -A

Database 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;                             //删除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 specified

        Example 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 ename varchar(20);                         //修改表emp的ename字段定义，将varchar(10)改成varchar(20)
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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.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: 0

mysql> 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: 0

mysql> 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);                    //将age改为agel，同时修改字段类型为int(4)
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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 ename;                      //在表emp中新增加字段birth，类型date,并把它放在字段ename的后面
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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: 0

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

转载自：

http://blog.163.com/wxiaoch_0904/blog/static/96091031201141331336574/

对原作者表示感谢