MySQL表的操作

发布时间:2023年12月17日

目录

前言

创建表

创建表时指定不同的存储引擎会对表造成的影响

查看表的结构

显示创建语句

修改表

删除表?


前言

表操作至少会涉及如下两类SQL语句:

  • DDL(Data Definition Language)数据定义语言:比如建表、删表、该表、新增列、删除列等。
  • DML(Data Manipulation Language)数据操作语言:比如插入记录、删除记录、修改记录等。

注意:本篇文章讲的是表的DDL操作,也就是操作表结构的SQL语句。

创建表

创建表的SQL如下:

create [temporary] table [if not exists] 表名称(
	属性名称1 属性名称1的类型 [comment '注释信息'],
	属性名称2 属性名称2的类型 [comment '注释信息'],
	.....
)[charset=字符集名称] [collate=校验规则名称] [engine=存储引擎名称];

说明一下:

  • [ ]中的内容代表的是可选项,即可以省略不写。
  • 在创建表的SQL语句中加上temporary关键字时,MySQL服务端进程mysqld将创建出一个临时表,该表会在你与服务器的会话终止时自动消失。temporary表的名字可以与某个已有的永久表相同,当有temporary表存在时,对应的永久表会隐藏起来(即无法访问)。
  • SQL中大写的表示关键字,[ ]中代表的是可选项。
  • charset用于指定表所采用的字符集,如果没有指定则以表所在数据库DB的默认字符集为准(即以表所在的数据库DB中的配置文件db.opt中设置的字符集为准)
  • collate用于指定表所采用的校验规则,如果没有指定则以表所在的数据库DB的默认校验规则为准(即以表所在的数据库DB中的配置文件db.opt中设置的校验规则为准)
  • comment用于对指定列添加注释信息,在建表时增设的每个属性列肯定都有作用,比如增加属性列age int时,可以在其后面加上comment '该属性列表示年龄',这样一来,往后用户忘了一个表中的某个属性列是用来干嘛的时候,就可以通过SQL语句【 show create table 表名称; 】来查看曾经创建该表时是如何编写的SQL语句,以及查看comment信息。
  • engine用于指定表所采用的存储引擎。

如下图1所示,使用SQL语句show engines可以查看MySQL支持的所有存储引擎,然后如下图1的红框处所示,可以看到笔者当前的MySQL默认的存储引擎就是InnoDB,所以如果建表时在建表的SQL语句中没有指定使用哪种存储引擎,那么就会使用MySQL默认的存储引擎InnoDB。??

说一下,为什么MySQL默认的存储引擎是InnoDB呢?如下图2的红框处所示,默认的存储引擎就是MySQL配置文件(即MySQL客户端进程mysql和MySQL服务端进程mysqld共同的配置文件/etc/my.cnf)中提前配置的存储引擎,所以因为提前配置好的存储引擎是InnoDB,所以MySQL默认的存储引擎就是InnoDB了。

  • 图1如下。
  • 图2如下。

创建表时指定不同的存储引擎会对表造成的影响

首先回顾一下,在<<MySQL数据库的基础概念>>一文中我们说过,本质上MySQL中的一个数据库DB就是Linux系统下/var/lib/mysql路径上的一个目录文件,MySQL中的表就是Linux系统下/var/lib/mysql路径上的一个非目录普通文件。

然后如下图左半部分所示,use进入数据库test1后,在该数据库中创建一个表table1,并在建表时指定采用MyISAM存储引擎,做完这些后,如下图右半部分所示,在MySQL用于存储所有数据的路径/var/lib/mysql上的test1目录(即test1数据库)中,就会增加3个名为table1的文件(说一下,db.opt是最初在创建数据库test1时就会自动生成的用于记录数据库test1的默认字符集和默认校验规则的配置文件)。换言之,在建表时指定采用MyISAM存储引擎时,在MySQL中创建1个表就对应在Linux系统中创建3个普通文件。

然后如下图左半部分所示,use进入数据库test1后,在该数据库中创建一个表table2,并在建表时指定采用InnoDB存储引擎,做完这些后,如下图右半部分所示,在MySQL用于存储所有数据的路径/var/lib/mysql上的test1目录(即test1数据库)中,就会增加2个名为table2的文件(说一下,db.opt是最初在创建数据库test1时就会自动生成的用于记录数据库test1的默认字符集和默认校验规则的配置文件)。换言之,在建表时指定采用InnoDB存储引擎时,在MySQL中创建1个表就对应在Linux系统中创建2个普通文件。

所以综上可以发现:

  • 采用不同的存储引擎,创建表时所产生的文件数量以及文件类型不一样。
  • 比如采用InnoDB存储引擎建表,会产生对应的xxx.frm(表结构)和xxx.ibd(表数据+表索引)文件。
  • 再比如采用MyISAM存储引擎建表,会产生对应的xxx.frm(表结构)、xxx.MYD(表数据)和xxx.MYI(表索引)文件。

查看表的结构

如下图所示,使用SQL语句【desc 表的名称】即可查看表的结构。

说一下:?

  • Field表示该属性字段的名字。
  • Type表示该属性字段的类型。
  • Null表示该属性字段是否允许为空,比如是否允许id属性上的值为NULL(注意,在MySQL中NULL才表示空,如上图的Key一列上是一片空白,这个不表示空,而表示' '
  • Key在以后讲解索引的文章中再说明。
  • Default表示该字段的默认值。
  • Extra表示该字段的额外信息说明。

显示创建语句

如下图所示,使用SQL语句【 show create table 表的名称;?】即可查看对应表的创建语句。说一下,该条语句并不是用于创建新的表的,而是用于查看一个已经存在的表的创建语句、用于查看曾经在创建该表时写的SQL语句是怎样的。

可以看到,使用当前这条SQL语句还可以看到曾经在创建一个表时写的comment注释。

然后要知道的是,如下图所示,SQL语句【 show create table 表的名称 \G】也可以起到和上面一样的作用。

问题:为什么在已经有了SQL语句【 show create table 表的名称;?】的情况下,还要有SQL语句【 show create table 表的名称 \G】呢?

答案:因为前者在显示的时候是以一个表格显示的,如果shell界面不够大,则表格就会显示得很奇怪,这时就可以使用后者(注意使用后者时,在\G后面是不用加分号 ; 的),因为后者不会以表格的形式显示,所以即使shell界面不大,也能很完整简洁的显示一个已经存在的表的创建语句。

修改表

alter table 想要修改的表的名称 add 想要新增的属性列的名称 新增属性列的类型;

alter table 想要修改的表的名称 modify 想要修改的属性列的名称 修改后的属性列的类型;

alter table 想要修改的表的名称 drop 想要删除的属性列的名称;

alter table 想要修改的表的名称 change 想要修改的属性列的名称 属性列的新名称 属性列的新类型;

alter table 想要修改的表的名称 rename 表的新名称;

修改表的SQL语句如上所示,说明一下:

  • alter table add用于给表中增加一个属性。
  • alter table modify用于修改表中的某个属性的属性类型(无法修改属性的属性名)
  • alter table drop用于删除表中的一个属性。
  • alter table change用于修改表中的某个属性的属性名和属性类型(因为可以修改属性的属性名,所以本条SQL语句相当于alter table modify的升级版)。注意只能同时修改属性的属性名和属性类型,不能只修改其中的一个。
  • alter table rename用于修改表的名称。

alter table add的实操。(用于给表中增加一个属性)

注意修改表可能会影响到表中的原数据,我们在进行alter table add的实操时顺便证明一下这一点。

如何证明这一点呢?为了证明这一点,我们在修改表之前先在table1表中插入两条数据。如下:

如下图所示,然后通过alter table add在table1表中新增一个属性用于保存用户的照片存储路径,完成后,通过desc查看表table1的结构,可以发现的确新增成功了。

注意:?SQL语句alter table add中的after表示将该属性列新增到哪一列之后,如果想要将新增的列放到第一列,可以将从after开始的后面的所有SQL语句换成not null first。

如下图所示,这时再插入一条用户记录后,再查看表中信息,可以看到在新增属性photo_path之前就已经插入到表中的两条记录对应的photo_path值为NULL,所以新增属性后可能还需要对原来插入的记录进行修改。这就证明了修改表可能会影响到表中的原数据,所以一个表的结构一但创建出来了,我们最好不要再修改表的结构。

alter table modify的实操。(用于修改表中的某个属性的属性类型)

如下图所示,该SQL语句即可将table1表中的password属性的类型由char(30)改成char(50)。

注意,如果需要在修改属性的属性类型后仍然保留comment字段(即如果想在未来调用show create table `table1`时能看见表table1的创建语句中还携带comment字段),就需要在使用SQL语句alter table modify修改属性的属性类型时如下图红框处所示重新指定comment字段。

alter table drop的实操。(用于删除表中的一个属性)

如下图红框处所示,该SQL语句即可将table1表中的photo_path属性删除。注意删除列后,该photo_path属性列对应的所有数据都没有了。

注意一直删除table1表中的属性时,如果删到只有最后一个属性时,此时是无法再通过alter table drop语句删除最后一个属性的,如果想要把最后一个属性删除,则需要调用删除整个表的drop table语句。

alter table change的实操。(用于修改表中的某个属性的属性名和属性类型。注意只能同时修改属性的属性名和属性类型,不能只修改其中的一个)

如下图所示,该SQL语句即可将table1表中的password属性的属性名从password改成passwd。

注意,如果需要在修改属性的属性名和属性类型后仍然保留comment字段(即如果想在未来调用show create table `table1`时能看见表table1的创建语句中还携带comment字段),就需要在使用SQL语句alter table change时如下图红框处所示重新指定comment字段。

alter table rename的实操。(用于修改表的名称)

如下图所示,该SQL语句即可将table1表的名字从table1改成table2。注意,如下图红框处所示,MySQL中的表的名字被改变后,在Linux系统下该表对应的几个普通文件的名字也会跟着被改变。

删除表?

删除表的SQL如下:

drop [temporary] table [if exists] 表的名称;

说明一下:

  • 在创建表的SQL语句中加上temporary关键字时,MySQL服务端进程mysqld将创建出一个临时表,该表会在你与服务器的会话终止时自动消失。temporary表的名字可以与某个已有的永久表相同,当有temporary表存在时,对应的永久表会隐藏起来(即无法访问)。为了避免重新连接后(temporary表已经不存在),在未做检测的情况下调用drop误删了对应的永久表,因此在使用drop删除临时表时需要带上temporary关键字。

将table1表删除。如下:

文章来源:https://blog.csdn.net/work_hard423/article/details/135044491
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。