当前位置:首页 > 开发 > 数据库 > 正文

MySQL: DDL ( Data Definition Language) for Column Management

发表于: 2013-04-20   作者:DavyJones2010   来源:转载   浏览次数:
摘要: 1. Create table create table table_name( column_name column_type [, column_attribute] [, column_default_value],  --> Column Declaration column_name column_type [, column_attribute] [, colum

1. Create table

create table table_name(

column_name column_type [, column_attribute] [, column_default_value],  --> Column Declaration

column_name column_type [, column_attribute] [, column_default_value],  --> Column Declaration

......

)[charset=utf8/gbk];

    Eg:

create table user(
user_id int primary key auto_increment,
user_name varchar(20) not null default '',
user_gender varchar(20) not null default 'male'
);
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(20) | NO   |     | male    |                |
+-------------+-------------+------+-----+---------+----------------+

 

2. Alter table --->( Insert Column & Drop Column & Update Column & Retrieve Column)

       1) Insert Column: alter table table_name add Column Declaration;  ---> The new column is added as the last column of the table by default. We can use after column_name to set its position manually.

                                                                                                                ---> What if the new added column should be the first column? alter table table_name add Column Declaration first

       2) Drop Column:  alter table table_name drop [column] column_name;

       3) Update Column: alter table table_name change target_column_name new_column_declaration;

       4) Retrieve Column: desc table_name;

    Eg:

#1. Insert Column
alter table user add user_age int not null default 0;
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(20) | NO   |     | male    |                |
| user_age    | int(11)     | NO   |     | 0       |                |
+-------------+-------------+------+-----+---------+----------------+

#2. Drop Column
alter table user drop column user_age;
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(20) | NO   |     | male    |                |
+-------------+-------------+------+-----+---------+----------------+
alter table user drop user_gender;
desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| user_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(20) | NO   |     |         |                |
+-----------+-------------+------+-----+---------+----------------+

#3. Update Column
alter table user change user_gender user_gender varchar(30) not null default 'female';
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(30) | NO   |     | female  |                |
+-------------+-------------+------+-----+---------+----------------+

#4. Retrieve Column
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(30) | NO   |     | female  |                |
+-------------+-------------+------+-----+---------+----------------+

    Comments:

        1) Know what is Column Declaration! And Column Declaration is very import in column DDL.

        2) Know what is DDL and DML.

        3) Know how to manage column using DDL.

MySQL: DDL ( Data Definition Language) for Column Management

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
在导入Mysql数据库时,发现怎么也导入不进去数据,报错: 查看表定义结构: 可以看到comm 定义类型
参考与感谢: Flying Report : CodeProject- Project RDL - Open Source Report Definition Language
参考与感谢: Flying Report : CodeProject- Project RDL - Open Source Report Definition Language
最近在研究数据中心的数据管理和性能优化,看了一篇2010的论文Nectar:Automatic Management of Data
最近在研究数据中心的数据管理和性能优化,看了一篇2010的论文Nectar:Automatic Management of Data
最近在研究数据中心的数据管理和性能优化,看了一篇2010的论文Nectar:Automatic Management of Data
mantis bug管理系统的二次asp.net 接口开发的时候,碰到编码的问题,如上述标题。 此系统的功能比较
【Data Management in Quartz 2D】   The preferred way to read and write image data is to use
robot and nature,becoming one? What's their aim.. and the like.. re-brained new machine era i
MySQL5 LOAD DATA 深入研究 数据库中,最常见的写入数据方式是通过SQL INSERT来写入,另外就是通过
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号