mysql 表的基本操作整理

1mysql创建表

a. 语法列表

CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS]  tbl_name

       [(create_definition…)]

       [table_options] [select_statement]

OR

CREATE   [TEMPORARY]  TABLE  [IF  NOT  EXISTS]  tbl_name

       [(] LIKE  old_tbl_name [)]; 

create_definition:

       column_definition

       | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, …)

       | KEY [index_name] [index_type] (index_col_name,…)

       | INDEX [index_name] [index_type] (index_col_name,…)

       | [CONSTRAINT [symbol]] UNIQUE [INDEX]  [index_name] [index_type] (index_col_name, …)

       | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,…)

       | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, …) [reference_definition]

       | CHECK (expr)

column_definition:

       col_name type[NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]

                            [UNIQUE [KEY] |[PRIMARY] KEY] [COMMENT ‘string’] [reference_definition]

type:

       TINYINT[(length)] [UNSIGNED] [ZEROFILL]

       | SAMLLINT[(length)] [UNSIGNED] [ZEROFILL]

       | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

       | INT[(length)] [UNSIGNED] [ZEROFILL]

       | INTEGER[(length)] [UNSIGNED] [ZEROFILL]

       | BIGINT[(length)] [UNSIGNED] [ZEROFILL]

       | REAL[(length, decimals)][UNSIGNED] [ZEROFILL]

       | DOUBLE[(length, decimals)][UNSIGNED] [ZEROFILL]

       | FLOAT[(length, decimals)][UNSIGNED] [ZEROFILL]

       | DECIMAL(length, decimals)[UNSIGNED] [ZEROFILL]

       | NUMERIC(length, decimals)[UNSIGNED] [ZEROFILL]

       | DATE

       | TIME

       | TIMESTAMP

       | DATETIME

       | YEAR

       | CHAR(length) [BINARY | ASCII | UNICODE]

       | VARCHAR(length) [BINARY]

       | BINARY(length)

       | VARBINARY(length)

       | TINYTEXT [BINARY]

       | TEXT [BINARY]

       | MEFIUMTEXT [BINARY]

       | LONGTEXT [BINARY]

       |ENUM(value1, value2, value3, …)

       |SET(value1, value2, value3, …)

       |spatial_type

index_col_name:

       col_name [(length)] [ASC | DESC]

reference_definition:

       REFERENCES tbl_name [(index_col_name, …)]

                             [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

                             [ON DELETEreference_option]

                             [ON UPDATE reference_option]

reference_option:

       RESTRICT| CASCADE | SET NULL | NO ACTION

table_options: table_option [table_option] …

table_option:

       {ENGINE | TYPE} [=] engine_name

       | AUTO_INCREMENT [=] value

       | AVG_ROW_LENGTH [=] value

       |[DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

       | CHECKSUM [=] {0 | 1}

       | COMMENT [=] ‘string’

       | CONNECTION [=] ‘connect_string’

       |MAX_ROWS [=] value

       |MIN_ROWS [=] value

       | PACK_KEYS [=] {0 | 1 | DEFAULT}

       | PASSWORD [=] ‘string’

       | DELAY_KEY_WRITE [=] {0 | 1}

       | ROW_FORMAT [=] {DEFAULT | DYNAMIC |FIXED | COMPRESSED | REDUNANT | COMPACT}

       | UNION [=] (tbl_name[, tbl_name] …)

       | INSERT_METHOD [=] ‘absolute path to directory’

       | DATA DIRECTORY [=] ‘absolute path to directory’

       |INDEX DIRECTORY[=] ‘absolute path to directory’

select_statement:

       [IGNORE | REPLACE] [AS] SELECT … (Some legal select statement)

b. 创建表

创建部门表，部门表的表结构如表1部门表表结构所示。

表1部门表表结构

创建部门表的过程如图1创建部门表所示。

图1创建部门表

 

创建职员表，职员表的表结构如表2职员表表结构所示。

表2职员表表结构

创建职员表的过程如图2-1-2创建职员表所示。

图2创建职员表

2 修改表结构

a. 语法列表

ALTER  [IGNORE]  TABLE  table_name

alter_specification [, alter_specification] …

alter_specification:

        ADD [COLUMN] column_definition [FIRST| AFTER col_name]

       | ADD [COLUMN] (column_definition, …)

       | ADD INDEX [index_name] [index_type] (index_col_name,…)

       | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, …)

       | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name, …)

       | ADD [FULLTEXT | SPATIAL] [INDEX] [index_name] (index_col_name,…)

       | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, …) [reference_definition]

       | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

       | CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name]

       | MODIFY  [COLUMN]  column_definition  [FIRST| AFTER col_name]

       | DROP  [COLUMN]  col_name

       | DROP  PRIMARY  KEY

       | DROP  INDEX  index_name

       | DROP  FOREIGN  KEY  fk_symbol

       | DISABLE  KEYS

       | ENABLE  KEYS

       | RENAME [TO]  new_tbl_name

       | ORDER  BY  col_name

       | CONVERT  TO  CHARACTER  SET  charset_name [COLLATE collation_name]

       | [DEFAULT]  CHARACTER  SET  charset_name [COLLATE collation_name]

       | DISCARD  TABLESPACE

       | IMPORT  TABLESPACE

       | table_options

b. 修改表结构示例

       修改表tbl_clerk的结构，添加外键约束。具体操作步骤如图3添加外键约束所示。

图添加外键约束

 

现在删除新添加的外键约束，具体的操作步骤如图4l删除外键约束所示。

图4删除外键约束

 

现在修改tbl_clerk的表结构，重新定义clerk_name字段，具体的操作步骤如图5修改字段所示。

图5修改字段

 

3 查看表结构

a. 语法列表

DESC  table-name;

OR

SHOW  COLUMNS  FROM  table-name;

b. 查看表结构示例

用第一种方法查询部门表，查询的语法如图6查询部门表结构所示。

图6查询部门表结构

 

用第二种方法查询职员表，查询的语法如图7查询职员表结构所示。

图7查询职员表结构

 

4 mysql中表的重命名

a. 语法结构

RENAME  TABLE  old-table-name  TO  new-table-name;

b. 重命名示例

将表tbl_dept重命名为tbl_dept1，在进行重命名操作之前，先查看数据库中都有那些表，如图8查看数据库中的表所示。

图8查看数据库中的表

 

现在开始进行表的重命名操作，具体的操作步骤如图9表的重命名所示。

图9表的重命名

 

现在再来查看数据库，看看有表名是否有所改变，具体的操作步骤如图10查看数据库中的表所示。

图10查看数据库中的表

 

5 mysql查看表的创建语句

a. 语法结构

SHOW  CREATE  TABLE  table_name;

b. 查看表的创建语句示例

查看表tbl_dept1的建表语句，具体的操作步骤如图11查看建表语句所示。

图11查看建表语句

 

6 mysql复制表

a. 语法结构

复制表：

CREATE  TABLE  new_table_name

SELECT (*| column_name [,column_name , …])

FROM  old_table_name

WHERE  where_clause;

需要注意的是，该语法结构并不能从源表中复制键，相应的键需要手动添加，也可以使用如下语句进行表的复制。

CREATE TABLE new_table_name LIKE old_table_name;

 

b. 复制表示例

使用第一种语句结构为表tbl_dept1复制一个新的表结构tbl_dept，具体的操作步骤如图12复制表结构所示。

图12复制表结构

 

使用第二种语句结构为表tbl_dept1复制一个新的表结构tbl_dept2，具体的操作步骤如图13复制表结构所示。

图13复制表结构

 

7 mysql删除表

a. 建表语句

DROP  [TEMPORARY]  TABLE [IF EXISTS]

       tbl_name[, tbl_name] …

       [RESTRICT | CASCADE]

b. 删除表示例

删除表tbl_dept和表tbl_dept2，具体的操作步骤如图14删除表所示。

图14删除表