mysql 设置 表整理_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  FROMtable-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删除表