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

目录

一，创建表

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关键字。