mysql/mariadb知识点总结(9):表管理语句

  • A+
所属分类:mysql  数据库

mysql/mariadb知识点总结(9):表管理语句

这篇文章总结了mysql中常用的表管理语句。

在本博客中,"mysql"是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。

mysql文章列表直达链接:mysql知识点总结

 

查看表

如果想要列出当前数据库上的所有表,可以使用如下语句。

mysql/mariadb知识点总结(9):表管理语句

 

如果想要查看当前库中所有表的具体属性信息,可以使用如下语句。

使用上述命令查看的表信息包括每张表的名称,所使用的存储引擎,版本,字符集,描述信息,创建时间等信息。

mysql/mariadb知识点总结(9):表管理语句

 
 

如果只想查看当前库的某张表的状态,可以使用like匹配表名称

mysql/mariadb知识点总结(9):表管理语句

或者使用通配符,查看表名类似的表的信息

mysql/mariadb知识点总结(9):表管理语句

 

查看表结构的对应语句如下:

示例:查看students表的字段,类型,表结构。

mysql/mariadb知识点总结(9):表管理语句

 

如果想要查看某张表被创建时对应的sql语句,可以使用如下语法。

比如想要查看创建students表时使用的sql语句是什么,可以使用如下命令:

mysql/mariadb知识点总结(9):表管理语句

 
 

创建表

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

创建一个新表的基本语法如下:

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

现在我们对上述语法进行解释,因为解释较多,可以先从“创建表示例”部分开始看起,看完实例后再回过头来对照概念进行查看,更加容易理解。

 

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

我们可以把上述语法分为两个部分查看。

前半部分为create table [IF NOT EXISTS] tbl_name ,很容易理解,create table是关键字,表示创建表,tbl_name为表名, [IF NOT EXISTS]为可选的,表示如果表不存在时才创建。

后半部分为(create_definition,...),首先要注意的是,create_definition需要被括号括起来。

 

那么我们来看看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']设置字段的描述信息,此设置可省。

 

表级别约束定义(key定义):

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

UNIQUE KEY (col1[,col2, ....]) 用于定义唯一键,一个表中可以有多个唯一键。

FOREIGN KEY  用于定义外键

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

当我们创建key时,即相当于创建了index。

索引定义(index定义):

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

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

 

创建表的语法说了这么多,都没有举例子,反而不容易说明白,此处我们从最简单的示例开始,对照概念进行理解。

 

创建表 示例

如下示例表示创建一个表,表中有两个字段,id与name,id的数据类型为int,name的数据类型为varchar,长度为60.

红线标注的每一行,都能看做是一个"create_definition"而且下图中的create_definition都属于字段定义。

mysql/mariadb知识点总结(9):表管理语句

 

如下示例表示使用字段定义,对字段进行约束,此处将id字段设置为了主键,并且限制name字段不能为空,而且对name字段添加了注释,这些都属于在字段定义级别对字段进行了约束或设定。

主键约束,非空约束,注意,在字段级别设置主键表示主键只包含当前字段,如果想要设置联合主键,必须在表级别定义主键。

mysql/mariadb知识点总结(9):表管理语句

 

除了像上一个示例中那样,能直接在字段定义时就指定主键 ,也可以在表级别的约束定义中定义约束,示例如下

mysql/mariadb知识点总结(9):表管理语句

 

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

mysql/mariadb知识点总结(9):表管理语句

 

定义外键,我们可以在创建表时定义外键,如下示例表示test1表中的tid字段引用了test2表中的id字段作为外键。

mysql/mariadb知识点总结(9):表管理语句

 

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

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

mysql/mariadb知识点总结(9):表管理语句

 

mysql/mariadb知识点总结(9):表管理语句

 

其他示例

mysql/mariadb知识点总结(9):表管理语句

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

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

mysql/mariadb知识点总结(9):表管理语句

 

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

mysql/mariadb知识点总结(9):表管理语句

 

我们也可以只复制表结构,比如,我们创建一个新表,表结构与students表完全一致,也就是说,新表的所有字段都与students表一致,而且字段的约束也都相同,表中存在的索引也都相同。

所以,单纯的完全的复制表结构,可以使用如下语句。

mysql/mariadb知识点总结(9):表管理语句

 

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

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

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

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

mysql/mariadb知识点总结(9):表管理语句

 

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

mysql/mariadb知识点总结(9):表管理语句

  

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

 
 

   

删除表

删除表比较简单,但是非常危险,如果不是确定要删而且必须要删,请勿随意删除,数据无价,请勿手贱,此处没有回收站。

删除表的语句如下,可以删除指定的单张表,也可以一次删除多张表,表之间用逗号隔开。

mysql/mariadb知识点总结(9):表管理语句

   

   

修改表

如果在表中还没有任何数据时,我们可以相对轻松的修改表结构,但是如果表中已经存在了很多数据,那么我们应该充分的考虑修改表结构以后给我们带来的麻烦,所以,在设计表的时候,就应该具有一定的前瞻性,以免以后带来更多不必要的麻烦,此处我们只是练习改怎么修改表,当然,也不一定是修改表结构,也有可能是修改表的一些属性。

 

当我们修改表时,往往是添加、删除、修改表中的字段、索引、约束,但是此处,我们将不会列出修改索引、约束的相关操作,会将它们单独归类到一类操作中进行总结。

 

修改表的基本语法如下

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

 

此处列出修改表的常用语句

 

修改表名、重命名表

将表test1改名为test2

 

添加字段

为表添加字段,只添加字段,不指定字段的任何其他属性,如下语句表示为ttt表添加age字段。

 

修改表的字段时,column可省,不指定被操作对象时,默认为操作列。省略column后如下。

 

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

 

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

 

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

 

删除字段

删除包含数据的字段时需要三思,珍爱数据,请勿手欠。

如下语句表示从tt表总删除stuname字段。

 

修改字段

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

 

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

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

 

修改字段类型

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

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

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

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

weinxin
我的微信公众号
关注"实用运维笔记"微信公众号,当博客中有新文章时,可第一时间得知哦~
朱双印

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:5   其中:访客  3   博主  2

    • avatar peterzgy 0

      CREATE TABLE tbl_name [IF NOT EXISTS] (create_definition……)语法写错了,if not exists 必须是在table后面,应该是CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition…)

      • avatar 小小白 1

        删库到跑路从零教学系列 :cool:

        • avatar 初学者 1

          博主你好!
          查看当前库中所有表的属性
          show table status\G;后面是不是不需要分号啊?