MySQL据库表基本操作详细教程

共计 5186 个字符，预计需要花费 13 分钟才能阅读完成。

• 思前想后，最终还是把文章标题改成了数据库表基本操作，以前叫 SQL 语句大全，感觉用“大全”这个名词太大了，而文章中介绍的内容有限而且也不深。如果您在阅读的过程中发现有不完善的地方，欢迎补充说明。

一提笔就讲创建数据库，感觉好像是从半道上开始的。创建数据库前总得安装数据库吧，不管是 SQL Server、MySQL、Oracle。但是如果这样写下去，不知道这篇博客还能不能完成，那就默认是安装好数据库了吧，本文如下都是使用 mysql 数据库，首先是登入数据库操作（mysql -u root -p）。

1. 查看当前所存在的数据库：show databases;查询结果中的 mysql 数据库是必须的，用来描述用户的访问权限；information_schema 数据库提供了访问数据库元数据的方式，其中保存着关于 MySQL 服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等；performance_schema 用于收集数据库服务器性能参数。
2. 创建新的数据库：create database test_db;test_db 为要创建的数据库名称，该名称不能与现有的数据库重名。
3. 查看创建好的数据库定义：show create database test_db;

4. 删除数据库：drop database test_db;test_db 为要删除的数据库名称。

5. 查看数据库存储引擎：show engines;Support 列为 YES 表示引擎可用，DEFAULT 表示数据库当前默认的引擎。可以通过 my.ini 文件设置数据库的默认存储引擎。

在数据库中，数据表是最重要、最基本的操作对象，数据存储的基本单位。本节将介绍数据表的增、删、改、查操作，使读者能够熟练掌握数据表的基本操作，理解约束、默认和规则的含义。

• 创建数据表：数据表属于数据库，在创建数据表前，应该使用 use test_db; 指定在哪个数据库（test_db）下进行操作，如果没有指定数据库会抛出 No database selected 错误。创建数据表的语法规则如下：

create table < 表名 >(

字段名 数据类型 [列级别约束条件] [默认值],

…

[表级别约束条件]

);

1. 没有任何约束员工表的创建：create table user(id int(11), name varchar(20), sex tinyint, age tinyint, salary float);执行该语句后，会创建一个名称为 user 的数据表。
2. 使用主键约束创建员工表，主键是表中一列或多列的组合，主键约束要求主键列的数据唯一且不空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表间的关系，并且可以加快数据库查询的速度。主键分为两种：单字段主键和多字段联合主键。
   • 单字段主键：create table user1(id int(11) primary key, name varchar(20), sex tinyint, age tinyint, salary float);同：create table user1(id int(11), name varchar(20), sex tinyint, age tinyint, salary float, primary key(id));
   • 多字段主键：create table user1(name varchar(20), sex tinyint, age tinyint, salary float, primary key(name,age));
3. 使用外键约束：外键用来在两个表之间建立链接，可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性，一个表的外键可以为空，若不为空，则每个外键值必须等于另一个表中主键的某个值。外键可以不是本表的主键，但对应另一个表的主键，外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。
   • 创建外键规则：[constraint < 外键名 >] foreign key 字段名 1[, 字段名 2,…] references< 主表名 > 主键列 1[, 主键列 2,…]，外键名为定义的外键约束的名称，一个表中不能有相同名称的外键。如下以学生表、班级表、课程表、成绩表为例说明。
   • 创建班级表：create table tb_class(id int primary key, name varchar(20));
   • 创建课程表：create table tb_course(id int primary key, name varchar(20));
   • 创建学生表：create table tb_student(id int primary key, name varchar(20), classID int, constraint fk_class foreign key(classID) references tb_class(id));
   • 创建成绩表：create table tb_score(sID int, cID int, score float, primary key(sID,cID), constraint fk_student foreign key(sID) references tb_student(id), constraint fk_course foreign key(cID) references tb_course(id));
4. 使用非空约束：非空约束指字段的值不为空，对于指定非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。约束规则为：字段名 数据类型 not null，语句：create table user2(id int primary key, name varchar(20) not null);
5. 使用唯一性约束：唯一性约束要求该列唯一，允许为空，但只能出现一个空值，唯一性约束可以确保一列或者几列不出现重复值。约束规则：字段名 数据类型 unique，语句：create table user3(id int primary key, name varchar(22) unique);一个表中可以有多个字段声明为 unique，但只能有一个为 primary key，primary key 的列不允许为空，而 unique 可以存在一个 null。
6. 使用默认约束：指定某列的默认值。默认约束的语法规则：字段名 数据类型 default 默认值，默认约束语句：create table user4(id int primary key, name varchar(22), salary float default 3000.00);指定员工默认薪水为 3000 元。
7. 设置表的属性值自动增加，通过为表的主键添加 auto_increment 关键字来实现。默认 mysql 中 auto_increment 的初始值为 1，每增加一条新记录，字段值自动加 1，一个表只能有一个字段使用 auto_increment 约束，且该字段必须为主键的一部分。auto_increment 约束的字段可以是任何整数类型（tinyint、smallint、int、bigint 等）。语法规则：字段名 数据类型 auto_increment。sql 语句：create table user5(id int primary key auto_increment, name varchar(20), salary float);

  使用 sql 语句创建数据库表之后，可以查看表结构的定义，以确认表的定义是否正确，在 mysql 中可以使用 describe 和 show create table 语句，本节将对两个语句分别进行介绍。

• 查看表基本结构语句 describe
  • describe 表名; 或者 desc 表名;

Null：表示该列是否可以存储 null 值

key：表示该列是否已编制索引，PRI 表示该列是表主键的一部分，MUL 表示在列中某个给定值允许出现多次

Default: 如果该字段为 null，表示该没有默认值

Extra：表示可以获取的与给定列有关的附加信息，例如 auto_increment 等。

• 查看表详细结构语句 show create table
  • 语法格式：show create table 表名 \G;

修改表是指修改数据库中已经存在的数据表的结构。mysql 使用 alter table 语句修改表。常用的修改表操作有：修改表名、修改字段数据类型、或字段名、增加或删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表相关的操作进行说明。

• 修改表名

mysql 通过 alter table 语句来实现表名的修改，具体语法规则：alter table < 旧表名 > rename [to] < 新表名 >;其中 to 为可选参数。

• 修改字段的数据类型

把字段的数据类型转换成另一种数据类型，mysql 中的语法规则：alter table < 表名 > modify < 字段名 > < 数据类型 >;

• 修改字段名

mysql 中修改字段名的语法规则为：alter table < 表名 > change < 旧字段名 >< 新字段名 >< 新数据类型 >; 其中旧字段名指修改前的字段名，新数据类型为修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成原来一样即可，但数据类型不能为空。同时需要注意该语句不能修改主键的字段名。

• 添加字段

随着业务需求的变化，可能需要在已经存在的表中添加新的字段，一个完整的字段包括字段名、数据类型、完整性约束，添加字段的语法格式如下：

alter table < 表名 > add < 新字段名 > < 数据类型 > [约束条件][first|after 以存在字段名];

新字段名为需要添加的字段名称，first 为可选参数，其作用是将新添加的字段设置为表的第一个字段，after 为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面，如果没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

• 删除字段

删除字段是将数据表中的某个字段从表中删除，语法格式为：alter table < 表名 > drop < 字段名 >;

• 修改字段的排列位置

在数据表创建完成之后，字段在表中的排列顺序就已经确定了，但表的结构并不是完全不可变的，可以通过 alter table 来改变表中字段的相对位置，语法格式如下：

alter table< 表名 >modify< 字段 1 >< 数据类型 >first|after< 字段 2 >;

字段 1 指要修改位置的字段，数据类型指字段 1 的数据类型，first 为可选参数，指将字段 1 修改为表的第一个字段，after< 字段 2 > 指将字段 1 插入到字段 2 的后面。

• 删除表的外键约束

对于数据库中定义的外键，如果不在需要，可以将其删除。外键一旦删除，就会解除主表和从表之间的关联关系，外键删除语法为：

alter table < 表名 > drop foreign key < 外键约束名 >;

外键约束名是在定义表时 constraint 关键字后面的参数，详细见创建数据表章节。

• 添加表的外键约束

如上删除了 tb_score 表对 tb_course 表的外键关联，那么如下就是如何将外键约束添加回来，规则如下：

alter table < 从表 > add constraint fk_course foreign key (外键) references < 主表 >(关联主键);

  辛辛苦苦创建的数据表，仅仅一条删除数据表的语句，就能够将表的定义和表中所有的数据删除，因此在进行删除操作前，一定要慎重考虑。

• 删除没有关联的数据表

在 mysql 中，使用 drop table 可以一次删除一个或多个没有被其他表关联的数据表，语法为：drop table [if exists]表 1, 表 2,… 表 n;

•  删除被其他表关联的表

如果直接执行 drop table < 表名 > 或报错：Cannot delete or update a parent now：a foreign key constraint fails。因此在删除该表前，需要首先解除其他表与该表的关联关系，如 tb_course 与 tb_score 关联，然后就可以将 tb_course 表删除。

最后做一个小小的总结吧，①表删除操作一定要谨慎，因为 mysql 在执行删除操作时，不会有任何的确认信息，而且删除表操作是将表中的数据一并删除，因此在删除操作之前最好对表中的数据做备份。②数据库中并不是每一个表都存在主键，一般，多个表之间进行连接操作时，需要用到主键，因此并不需要为每个表建立主键，而且有些情况最好不要使用主键。③在 mysql 数据库中，带 auto_increment 约束的字段值是从 1 开始的，如果在表中插入第一条记录，同时指定 auto_increment 字段值为 8，那么以后插入的该字段值会从 9 开始往上增加。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-04/142502.htm