关系型数据库-MySQL：表管理

focus_lyh

已于 2022-04-14 17:30:09 修改

阅读量150

点赞数

分类专栏： # MySQL 文章标签： mysql

于 2021-01-29 15:32:27 首次发布

本文链接：https://blog.csdn.net/focus_lyh/article/details/113385219

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章目录

一、查看表

查看当前数据库下的所有表

> show tables;

查看当前数据库下所有表的具体属性信息

> show tables status\G

查看当前库中某张表的状态，通过like匹配表名

> show table status like 'table_name'\G;

查看表结构

> desc table_name;

查看表被创建时的sql语句

> show create table table_name;

二、创建表

创建表有多种情况，比如，我们可以直接创建一个新的空表，也可以将数据从数据库中查询出来并填充到创建的新表，还可以复制别的表的表结构从而创建一个新表，此处，我们先来了解最基本的也是最常用的语法，创建一个简单的新的空表。

语法格式：

create table [if not exists] table_name(create_definition,...)

create_definition 分类：字段定义、表级别约束定义、索引定义。

字段定义

格式：column_name column_defination

注释：column_name 指明字段名称，通过 column_defination 指定字段定义。

column_defination 可以简单的理解为字段的数据类型的定义，同时，还能为当前字段定义一些单独的约束，所以，column_defination 大概分为两部分，字段类型和可选约束设置：

常用的 data_type 不再描述，而且data_type是不可省的。

常用的可选约束设置如下，注意，并不完全，列出较为常用的。

[not null | null] [default default_value] [auto_increment] [unique[key] | [primary][key][comment ‘string’]]

not null 表示对应字段不能为空，不设置默认为 null，表示对应字段可以为空。

default default_value 表示设置字段默认值。

auto_increment 表示对应字段使用自动增长，一个表中只有一个字段能被设置为自动增长，而且这个字段必须被定义为key（或者索引），mysql 默认也会认为"自动增长的键字段"为主键字段。所以，结合着一个表中只能有一个主键的定义，auto_increment 往往只针对于主键字段进行设置，因为一个表中如果已经存在主键，当我们对非主键的键字段定义auto_increment 时会报错，因为一个表中只能有一个主键，mysql 又默认认为自动增长的键字段为主键，这样就会产生冲突，mysql 认为这样会产生两个主键，所以，我们往往只对主键字段设置 atuo_increment , 在 mariadb5.5.4 中，如果我们创建了一个新表，在表中创建了一个字段，将第一个字段定义为索引（不是key），然后为这个字段设定 auto_increment 这时，表中不存在任何主键，因为 mysql 会认为自动增长的键字段为主键，不会认为自动增长的索引字段为主键，如果我们再添加一个新的字段，而且将第二个字段也定义为索引，再对第二个字段设置auto_increment，就会报错，因为一个表中，只能有一列被设置为auto_increment，如果此时，我们再添加第三个字段，并将其设置为主键字段，那么这个主键字段将不能拥有自动增长功能，因为第一个字段已经被设置为了自动增长。所以，综上所述，由于诸多的限制，我们往往只对主键设置自动增长。如果字段被设置为自动增长，当删除了这个表中的某一行时，那么对应行的此字段的数值将无法再次被使用。

unique[key]此设置可省，如果显示设置，表示从字段级别设置当前字段为唯一键，key省，我们还可以通过表级别对当前字段进行唯一约束的定义。

[primary][key] 此设置可省，如果显示设置，表示从字段级别设置当前字段为主键，与UNIQUE [KEY]不同，UNIQUE [KEY]中的key可省，而 [PRIMARY] KEY中的 PRIMARY可省，KEY不可省，只写key就代表设置当前字段为primary key，注意，如果在字段级别设置当前字段为主键，则表示主键只包含当前字段一个字段，如果需要设置包含多个字段的主键，则必须在"表级别的约束定义"中设置主键。

comment ‘string’]设置字段的描述信息，此设置可省。
表级别约束定义

primary key(col1[,col2, …]) 用于定义主键，一个表中只能有一个主键，一个主键可以包含多个字段。

unique key (col1[,col2, …]) 用于定义唯一键，一个表中可以有多个唯一键。

foreign key 用于定义外键

check(expr) 用于定义检查性约束

当我们创建key时，即相当于创建了index。
索引定义

{index|key} 我们可以使用index或key关键字创建索引，注意，如果key写在此位置，与index相同，表示定义索引，而不是定义key。

{fulltext|spatial} 如果我们的数据存储引擎为myisam，那么它还有两个特殊的索引，全文索引和空间索引。innodb引擎不支持全文索引和空间索引，但是在某些分支的新版本中，貌似已经有支持空间索引的innodb出现了，我们不进行细究。

实例演示：
1）创建一个表，表中有两个字段，id 和 name，id 的数据类型为 int，name 的数据类型为 varchar，长度为60；

[test]>create table t1
    -> (
    -> id int(11),
    -> name varchar(60)
    -> );
Query OK, 0 rows affected (0.06 sec)

2）使用字段定义，对字段进行约束，此处将 id 字段设置为主键，并且限制 name 字段不能为空，而且对 name 字段添加了注释，这些都属于在字段定义级别对字段进行了约束或设定。
主键约束，非空约束，注意，在字段级别设置主键表示主键只包含当前字段，如果想要设置联合主键，必须在表级别定义主键。

[test]>create table t2
    -> (
    -> id int(11) key,
    -> name varchar(45) not null comment 'student name'
    -> );
Query OK, 0 rows affected (0.00 sec)

3）直接在字段定义时就指定主键，也可以在表级别的约束定义中定义约束，示例：

[test]>create table t3
    -> (
    -> id int(11),
    -> name varchar(45) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.25 sec)

4）联合主键，上述示例中，在创建表时只定义了单一主键，在创建表时，我们也可以在表级别定义联合主键。

[test]>create table t4
    -> (
    -> id int,
    -> name varchar(50) not null,
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.00 sec)

5）定义外键，我们可以在创建表时定义外键，如下示例表示t1表中的tid字段引用了t2表中的id字段作为外键。

[test]>create table t1
    -> (
    -> id int primary key,
    -> name varchar(100) not null,
    -> tid int,
    -> foreign key(tid) references t2(id)
    -> );

或者直接在创建数据表时定义索引。

注意，如下两个示例都表示在创建数据表时创建ind_name索引,此时key与index关键字都表示创建索引。

[test]>create table test3
    -> (
    -> id int(11),
    -> name varchar(50),
    -> primary key(id),
    -> index ind_name(name)
    -> );
Query OK, 0 rows affected (0.00 sec)
[test]>create table test4
    -> (
    -> id int(11),
    -> name varchar(50),
    -> primary key(id),
    -> key ind_name(name)
    -> );
Query OK, 0 rows affected (0.00 sec)

6）其他实例

[test]>create table students
    -> (
    -> id int primary key auto_increment,
    -> name varchar(40) not null,
    -> age tinyint unsigned,
    -> gender enum('f','m') default 'm',
    -> index(name)
    -> );
Query OK, 0 rows affected (0.04 sec)

除了上述创建一个新的空表以外，还能根据查询出的数据创建出一个新表，并且将查询的数据填充到创建的表中。

1）比如，使用如下方法，将 city 表中的 id,name,population 三个字段中的数据取出，并填充到创建的新表 t5 当中，但是请注意，通过这种方式将 t5 创建完成后，只能将对应的数据填充过去，但是表的字段中的约束以及表的索引则无法复制过去，如果需要对应的约束，需要手动指明。

1) 查询表结构
[world]>desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

2）创建
[world]>create table t5 select id,name,population from city;
Query OK, 4079 rows affected (0.03 sec)
Records: 4079  Duplicates: 0  Warnings: 0

3）验证
[world]>desc t5;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id         | int(11)  | NO   |     | 0       |       |
| name       | char(35) | NO   |     |         |       |
| population | int(11)  | NO   |     | 0       |       |
+------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

[world]>select * from t5 limit 5;
+----+----------------+------------+
| id | name           | population |
+----+----------------+------------+
|  1 | Kabul          |    1780000 |
|  2 | Qandahar       |     237500 |
|  3 | Herat          |     186800 |
|  4 | Mazar-e-Sharif |     127800 |
|  5 | Amsterdam      |     731200 |
+----+----------------+------------+
5 rows in set (0.00 sec)

2）当然，我们也可以将city表的数据全部查询出来，然后创建一张新表，将city表中的数据全部存入新表中，这样就相当于复制表，但是只是复制了city表中的数据，字段的约束以及表的索引会丢失。

[world]>create table t6 select * from city;
Query OK, 4079 rows affected (0.25 sec)
Records: 4079  Duplicates: 0  Warnings: 0

3）我们也可以只复制表结构，比如，我们创建一个新表，表结构与city表完全一致，也就是说，新表的所有字段都与students表一致，而且字段的约束也都相同，表中存在的索引也都相同。所以，单纯的完全的复制表结构，可以使用如下语句：

[world]>create table t7 like city;
Query OK, 0 rows affected (0.02 sec)

[world]>desc t7;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

上述描述与举例中，我们简单的描述了创建表、复制表、复制表结构的示例。

其实我们在创建表的时候，还可以加上表选项，表选项是可省的，表选项也可以有多个，注意，表选项的位置在(create_definition)的括号外面。

CREATE TABLE tbl_name [IF NOT EXISTS] (create_definition,…) [table_options]

最长用的表选项应该就是engine选项了，此选项用于指定创建的表使用哪种存储引擎。因为mysql是插件式存储引擎的数据库，所以，这表示存储引擎可以替换或指定，也就是说，每个表可以使用不同的存储引擎，但是一般不建议这样使用，这样会对以后的维护造成麻烦。当然，如果真的必须为表设置特定的引擎以使用对应的特殊功能，可以参考如下示例，如下示例中，在创建tt表时，指定了多个表选项，使用engine表选项指定了当前表使用InnoDB存储引擎，并且设置了当前表自动增长的字段从2开始增长，以及针对当前创建的表设定了对应的字符集。

[world]>create table tt
    -> (
    -> id int primary key atuo_increment,
    -> name varchar(55） not null,
    -> index ind_name(name)
    -> )
    -> engine=InnoDB atuo_increment=2 default charset=utf8;

表选项有很多，更多语法使用 help create table 命令参考帮助：

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

除了表选项，还有分割表的可选选项等，具体更多使用help create table命令参考帮助

三、删除表

语法格式

单表删除
> drop table table_name;
多表删除
> drop table if exists table_name1,table_name2

四、修改表

语法格式

ALTER  TABLE tbl_name [alter_specification [, alter_specification] ...]

修改表名

将t1改名为t2
> alter table t1 rename as t2

添加字段

为t2表添加age字段
> alter table t2 add column age int;
column 可省略，不指定被操作对象时，默认为操作列:
> alter table t2 add int;

为表添加字段，同时为添加的字段设定相应的约束。

> alter table t2 add age int not null default 0;
> alter table t2 add column age int not null default 0;

为表添加字段，同时指定新添加字段在表中的位置，如下语句表示将新添加的id字段设置为表中的第一个字段。

> alter table ttt add  id int first;

为表添加字段，同时指定新添加字段在表中的位置，如下语句表示将新添加的age字段添加到name字段的后面。

> alter table ttt add column age  int after name;

删除字段

从t2表删除stuname字段
> alter table tt drop stuname;

修改字段

此处只列出修改字段名称和修改字段数据类型的相关语句，修改字段约束和索引的语句单独归为一类总结。

重命名字段，修改字段名称

如下语句表示将testtable表中的name字段重命名为name1，name字段的数据类型为char（5），即使只是重命名，我们也需要在新名称后指定原字段的数据类型，否则会报错。
> alter table testtable change name name1 char(5);

修改字段类型

修改字段数据类型可以使用两种语法，modify与change两种，我们先来看看使用change这个语法怎么做。

使用change改变字段的数据类型，与使用change重命名字段的方法几乎一样，因为即使你不是想要重命名字段，也需要将字段名称重复写两次，比如将age字段从int类型改为char类型，当然，在有数据的情况下，这样改还不会出现什么问题，但是如果是把字符型改为整数类型，则会出现问题，所以，在修改字段之前多次确定，语句示例如下

> alter table testtable change  age age char(10);

或者使用modify来改变字段类型，modify不能用于修改字段名称，示例如下。

> alter table testtable modify age int;

注意：假如有一个name字段，数据类型为varchar(20), 同时，这张表上存在一个索引，这个索引使用了name字段的前10个字符作为索引，那么如果你想要将name字段的数据类型的长度缩短，则最短只能改为varchar(10)。

focus_lyh

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关系型数据库-MySQL：表管理

文章目录一、查看表二、创建表三、删除表四、修改表一、查看表查看当前数据库下的所有表> show tables;查看当前数据库下所有表的具体属性信息> show tables status\G查看当前库中某张表的状态，通过like匹配表名> show table status like 'table_name'\G;查看表结构> desc table_name;查看表被创建时的sql语句> show create table table_name;
复制链接

扫一扫