【MySQL】表的操作

2023-12-15 10:45:41

MySQL表的操作

1、创建表

创建表的SQL语法如下:

CREATE TABLE [IF NOT EXISTS] table_name(
	field1 datatype1 [COMMENT '注释信息'],
	field2 datatype2 [COMMENT '注释信息'],
	field3 datatype3  [COMMENT '注释信息']
)[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];

说明一下:

  • SQL中大写的表示关键字,[ ]中代表的是可选项。
  • field表示列名,datatype表示列的类型。
  • CHARSET用于指定表所采用的编码格式,如果没有指定则以所在数据库的编码格式为准。
  • COLLATE用于指定表所采用的校验规则,如果没有指定则以所在数据库的校验规则为准。
  • ENGINE用于指定表所采用的存储引擎。
  • COMMENT用于对指定列添加注释信息。

使用show enginesSQL可以查看当前MySQL支持的存储引擎。如下:

show engines;

在这里插入图片描述

可以看到我这里当前的MySQL默认使用的是InnoDB存储引擎,当我建表时没有指定使用哪种存储引擎,那么就会默认使用InnoDB存储引擎。

2、创建表案例

在创建表之前我们要先有一个数据库,并且使用这个数据库,这里我们创建一个db1的数据库并使用。

create database db1;
show databases;
use db1;

在这里插入图片描述

下面我们就来创建一个表:

create table user( 
id int, name varchar(20) comment '用户名',
password char(32) comment '密码',
birthday date comment '生日'
) charset=utf8 engine=MyISAM;

在这里插入图片描述

表创建完毕后在数据库的数据存储路径/var/lib/mysql下的db1目录中,就会对应增加三个文件。如下:

在这里插入图片描述

说明一下:

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

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

show tables;

在这里插入图片描述

4、查看表结构

desc 表名;

在这里插入图片描述

  • Field表示该字段的名字。
  • Type表示该字段的类型。
  • Null表示该字段是否允许为空。
  • Key表示索引类型,比如主键索引为PRI。
  • Default表示该字段的默认值。
  • Extra表示该字段的额外信息说明。

5、查看创建表时的相关细节

show create table 表名 \G

在这里插入图片描述

6、修改表

修改表的SQL语法如下:

ALTER TABLE table_name RENAME [TO] 新表名;

ALTER TABLE table_name ADD 新增列名 新增列的属性;

ALTER TABLE table_name MODIFY 列名 修改后的列属性;

ALTER TABLE table_name DROP 列名;

ALTER TABLE table_name CHANGE 列名 新列名 新列属性;
  • 修改表名
show tables;
alter table user rename to employee;
show tables;

在这里插入图片描述


  • 新增列
    修改表的过程中可能会影响到表中的数据,为了演示这个过程,我们在修改表之前先在emploee表中插入两条数据,然后使用select * from 表名显示表里面的内容。如下:
desc employee;
insert into employee value ('1', '张三', '12345', '1998-12-1');
insert into employee value ('1', '李四', '12345', '1997-8-3');
select * from employee;

在这里插入图片描述

在employee表中新增一列用于保存用户的照片路径。如下:

alter table employee add image_path varchar(128) comment '这是员工的照片' after birthday;
select * from employee;

在这里插入图片描述

说明一下:

  • 新增列SQL中的after表示将该列新增到哪一列之后,如果想要将新增的列放到第一列,可以将after及其之后的SQL换成not null first

  • 可以看到在新增列之前插入表中的两条记录对应的image_path值为NULL,因此新增列后可能还需要对原来插入的记录进行修改。


  • 修改列类型

在修改之前我们可以先看一看表的结构相关信息:

在这里插入图片描述

将employee表中name列的类型由varchar(20)改成varchar(60)。如下:

alter table employee modify name varchar(60);

在这里插入图片描述

在修改之后我们可以再看一看表的结构相关信息:

在这里插入图片描述

可以看出modify不是简单的修改,而是覆盖式的修改,所以如果我们还要保留注释的话就要再修改时加上comment


  • 删除列

例如将employee表中的password列删除。如下:

desc employee;
alter table employee drop password;
desc employee;

在这里插入图片描述

注意: 删除列后,该列对应的所有数据都没有了。

在这里插入图片描述


  • 修改列名

将employee表中image_path列的列名改成path。如下:

alter table employee change image_path path varchar(128);

在这里插入图片描述
在这里插入图片描述

注意

  • 修改列名也是覆盖式修改。
  • 修改列名时后面要加上变量类型。

7、删除表

删除表的SQL如下:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

说明一下:

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

将employee表删除。如下:

drop table employee;
show tables;

在这里插入图片描述

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