当前位置：首页 > article >正文

MySQL数据库（3）—— 表操作

article 2025/2/24 12:38:39

一，创建表

1.1 创建表的SQL

1.2 演示

二，查看表

三，修改表

四，删除表

常用的表操作会涉及到两种SWL语句

DDL（Data Definition Language）数据定义语言：建表、改表、删表等，本篇博客讲的就是这个
DML（Data Manipulation Language）数据库操作语言：比如插入记录、删除记录、修改记录等

一，创建表

1.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];

field表示列名，datatype表示列的数据类型。

CHARSET：指定表所采用的编码格式，如果没有指定则以所在数据库的编码格式为准。

COLLATE：指定表所采用的校验规则，如果没有指定则以所在数据库的校验规则为准。

ENGINE：指定表所采用的存储引擎。

COMMENT：对指定列添加注释信息。

使用 show engines 可以查看当前MySQL能支持的存储引擎：

可以看到只有InnoDB这个引擎后面的三个都是YES选项，所以我们目前MySQL默认使用的就是这个存储引擎

1.2 演示

我们还是用我们前面的helloworld库来演示

我们先创建两个表，SQL如下：

mysql> create table if not exists user1(
    -> id int,
    -> name varchar(20) comment '用户名',
    -> password char(20) comment '密码',
    -> birthday date comment '用户的生日'
    -> )character set utf8 collate utf8_general_ci engine MyIsam;

mysql> create table if not exists user2(
    -> name varchar(20) comment '用户名',
    -> password char(20) comment '密码',
    -> birthday date comment '用户的生日'
    -> )character set utf8 collate=utf8_general_ci engine=InnoDB;

建表完成后，在Linux目录 /var/lib/mysql 的对应库目录下可以看到如下内容：

注意，采用不同的存储引擎，建表后产生的文件不一样：

采用InnoDB存储引擎建表，会产生对应的xxx.frm（表结构）和 xxx.ibd（表数据+表索引）文件
采用MyISAM存储引擎建表，会产生对应的 xxx.frm（表结构）、xxx.MYD（表数据）和 xxx.NYI（表索引）文件

二，查看表

查看表结构的SQL为 desc 表名; 如下：

关于第一列的解释：

Field：表示该字段名称。
Type：表示该字段的数据类型。
Null：表示该字段是否允许为空。
Key：表示索引类型，比如主键索引为PRI（后面讲）。
Default：表示该字段的默认值。
Extra：表示该字段的额外信息说明。

如果要查看创建表时的更具体地细节，可以使用下面SQL查看：（和查看库的那个很像）

show create table 表名

三，修改表

①相关SQL

ALTER TABLE table_name ADD 新增列名 新增列的属性;
ALTER TABLE table_name MODIFY 列名 修改后的列属性;
ALTER TABLE table_name DROP 列名;
ALTER TABLE table_name RENAME [TO] 新表名;
ALTER TABLE table_name CHANGE 列名 新列名 新列属性;

我们仍然以helloworld库的student表做演示：

②新增列

我们可以在上面的表中新增一列class，表示该学生所在的班级：

这时我们再插入一条数据：

③修改列类型

讲student表的id列的int类型也换成varchar类型，如下：

注意：修改列类型后会舍弃掉原来的comment字段，如果修改后要保留，需要重新设定comment字段

④修改列名

这个简单，加入我们要将列的class修改为“班级”：

⑤修改表名

将表名student修改为“学生表”：

⑥删除列

将学生表的班级列删除：

删除后，该列对应的信息也会一并删除

四，删除表

删除表的SQL如下：

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

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