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.
相关推荐
SQL主要包括DQL(Data Query Language)用于查询数据,DML(Data Manipulation Language)用于插入、更新和删除数据,DDL(Data Definition Language)用于定义数据结构,以及DCL(Data Control Language)用于控制...
#### DDL(Data Definition Language)数据定义语言 **创建数据库CREATE** - 命令:`CREATE DATABASE database_name;` **删除数据库DROP** - 命令:`DROP DATABASE database_name;` **表的管理** - **创建表...
DDL (Data Definition Language) - **CREATE**:创建新的数据库或表。 - **ALTER**:修改现有的数据库或表。 - **DROP**:删除数据库或表。 这些命令主要用于管理表的结构而非表中的数据。 ##### 4. TCL ...
- SQL语句分为DML(Data Manipulation Language), DDL(Data Definition Language), DCL(Data Control Language)和TCL(Transaction Control Language)。 - DML包括INSERT、UPDATE、DELETE,用于数据的增、删...
- SQL命令通常被分为几大类:DDL(Data Definition Language)、DML(Data Manipulation Language)、DQL(Data Query Language)等。 - **流行的SQL开发工具:** - Oracle SQL Developer:一款免费的图形化工具...
- **DDL (Data Definition Language)**: 包括`CREATE`, `DROP`, `ALTER`等用于定义和修改数据库结构的语句。 - **DCL (Data Control Language)**: 包括`GRANT`, `REVOKE`, `COMMIT`, `ROLLBACK`等用于控制数据访问...
- **DDL**(Data Definition Language):用于定义数据库结构,如创建、修改或删除数据库对象,命令有`CREATE`、`DROP`、`ALTER`、`TRUNCATE`。 - **DCL**(Data Control Language):用于控制访问权限,包括`GRANT`和`...
包括了DDL(Data Definition Language)、DML(Data Manipulation Language)、DQL(Data Query Language)等组成部分。 - **流行的SQL开发工具**:如MySQL Workbench、SQL Server Management Studio、Oracle SQL ...
SQL 可以分为两大部分:**数据操作语言 (Data Manipulation Language, DML)** 和 **数据定义语言 (Data Definition Language, DDL)**。 - **DML** 包括用于查询、更新、插入和删除数据的命令。例如: - `SELECT`...
- **SQL DDL(Data Definition Language)** - **CREATE DATABASE**:用于创建新的数据库。 - **ALTER DATABASE**:用于修改现有的数据库。 - **CREATE TABLE**:用于创建新的表。 - **ALTER TABLE**:用于修改...
- 主要包括DDL(Data Definition Language,数据定义语言)、DML(Data Manipulation Language,数据操纵语言)、DCL(Data Control Language,数据控制语言)和DQL(Data Query Language,数据查询语言)。...