mysql学习-MySQL数据表的基本操作

1.MySQL创建数据表（CREATE TABLE语句）

基本语法
在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

其中，[表定义选项]的格式为：

<列名1> <类型1> [,…] <列名n> <类型n>

CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。

<表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。

<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。

在指定的数据库中创建表
数据表属于数据库，在创建数据表之前，应使用语句“USE<数据库>”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。

查看表结构
在 MySQL 中，使用 SQL 语句创建好数据表之后，可以查看结构的定义，以确认表的定义是否正确。在 MySQL 中，查看表结构可以使用 DESCRIBE 和 SHOW CREATE TABLE 语句。

DESCRIBE/DESC 语句可以查看表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法规则如下：

DESCRIBE <表名>;
--  或简写成：
desc <表名>;

其中，各个字段的含义如下：
Null：表示该列是否可以存储 NULL 值。
Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，值是多少。
Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句，语法格式如下：

SHOW CREATE TABLE <表名>\G；

提示：使用 SHOW CREATE TABLE 语句不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码。如果不加“\G”参数，显示的结果可能非常混乱，加上“\G”参数之后，可使显示的结果更加直观，易于查看。
在Navicat上使用\G，会报错。

2.MySQL ALTER TABLE：修改数据表

其语法格式如下：
alter table <表名> [修改选项]

[修改选项] 的语法格式如下：
{ add column <列名> <类型>
| change column <旧列名> <新列名> <新列类型>
| alter column <列名> { set default <默认值> | drop default }
| modify column <列名> <类型>
| drop column <列名>
| rename to <新表名>
| character set <字符集名>
| collate <校对规则名> }

修改表名
mysql 通过 alter table 语句来实现表名的修改，语法规则如下：

alter table <旧表名> rename [to] <新表名>；

提示：修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。

修改表字符集
mysql 通过 alter table 语句来实现表字符集的修改，语法规则如下：

alter table 表名 [default] character set <字符集名> [default] collate <校对规则名>;

其中，default 为可选参数，使用与否均不影响结果。

3.MySQL修改/删除字段

3.1 修改字段名称
MySQL 中修改表字段名的语法规则如下：

alter table <表名> change <旧字段名> <新字段名> <新数据类型>；

其中：
旧字段名：指修改前的字段名；
新字段名：指修改后的字段名；
新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

change 也可以只修改数据类型，实现和 modify 同样的效果，方法是将 sql 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。

提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

3.1 修改字段数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 mysql 中修改字段数据类型的语法规则如下：

alter table <表名> modify <字段名> <数据类型>

其中：
表名：指要修改数据类型的字段所在表的名称；
字段名：指需要修改的字段；
数据类型：指修改后字段的新数据类型。

3.3 删除字段
删除字段是将数据表中的某个字段从表中移除，语法格式如下：

alter table <表名> drop <字段名>；

其中，“字段名”指需要从表中删除的字段的名称。

4. MySQL删除数据表（DORP TABLE语句）

在 MySQL 数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。
在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

基本语法
使用 drop table 语句可以删除一个或多个数据表，语法格式如下：
drop table [if exists] 表名1 [ ,表名2, 表名3 …]

对语法格式的说明如下：
表名1, 表名2, 表名3 …表示要被删除的数据表的名称。drop table 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
if exists 用于在删除数据表之前判断该表是否存在。如果不加 if exists，当数据表不存在时 mysql 将提示错误，中断 sql 语句的执行；加上 if exists 后，当数据表不存在时 sql 语句可以顺利执行，但是会发出警告（warning）。

两点注意：
用户必须拥有执行 drop table 命令的权限，否则数据表不会被删除。
表被删除时，用户在该表上的权限不会自动删除。
删除表的实例

5. MySQL删除被其它表关联的主表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

删除父表有以下两种方法：
先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

下面介绍了如何取消关联表的外键约束并删除主表，也就是上面所说的删除父表的第二种方法。

CREATE TABLE tb_emp4
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR (50)
);

CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp4_emp5 FOREIGN KEY (deptId) REFERENCES tb_emp4(id)
);

下面解除子表 tb_emp5 的外键约束，SQL语句和运行结果如下：

alter table tb_emp5 drop foreign key fk_emp4_emp5;

语句成功执行后，会取消表 tb_emp4 和表 tb_emp5 之间的关联关系。

解除关联关系后，可以使用 DROP TABLE 语句直接删除父表 tb_emp4，SQL 语句如下：

DROP TABLE tb_emp4;

6. MySQL查看表结构命令

创建完数据表之后，经常需要查看表结构（表信息）。在 mysql 中，可以使用 describe 和 show create table 命令来查看数据表的结构。
describe：以表格的形式展示表结构
describe/desc 语句会以表格的形式来展示表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法格式如下：

describe <表名>;

或简写成：

desc <表名>;

其中，各个字段的含义如下：
Null：表示该列是否可以存储 NULL 值。
Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，值是多少。
Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句，语法格式如下：

SHOW CREATE TABLE <表名>\G；

show create table：以sql语句的形式展示表结构
show create table 命令会以 sql 语句的形式来展示表信息。和 describe 相比，show create table 展示的内容更加丰富，它可以查看表的存储引擎和字符编码；另外，你还可以通过\g或者\g参数来控制展示格式。

show create table 的语法格式如下：

show create table <表名>;

在 show create table 语句的结尾处（分号前面）添加\g或者\g参数可以改变展示形式。

提示：使用 SHOW CREATE TABLE 语句不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码。如果不加“\G”参数，显示的结果可能非常混乱，加上“\G”参数之后，可使显示的结果更加直观，易于查看。
在Navicat上使用\G，会报错。

7. MySQL数据表添加字段（三种方式）

MySQL 数据表是由行和列构成的，通常把表的“列”称为字段（Field），把表的“行”称为记录（Record）。随着业务的变化，可能需要在已有的表中添加新的字段。

MySQL 允许在开头、中间和结尾处添加字段

在末尾添加字段
一个完整的字段包括字段名、数据类型和约束条件。mysql 添加字段的语法格式如下：

alter table <表名> add <新字段名><数据类型>[约束条件];

对语法格式的说明如下：
<表名> 为数据表的名字；
<新字段名> 为所要添加的字段的名字；
<数据类型> 为所要添加的字段能存储数据的数据类型；
[约束条件] 是可选的，用来对添加的字段进行约束。

这种语法格式默认在表的最后位置（最后一列的后面）添加新字段。
注意：本节我们只添加新的字段，不关注它的约束条件。

在开头添加字段
mysql 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 first 关键字，语法格式如下：

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

first 关键字一般放在语句的末尾

在中间位置添加字段

mysql 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 after 关键字，语法格式如下：

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

after 的作用是将新字段添加到某个已有字段后面。

注意，只能在某个已有字段的后面添加新字段，不能在它的前面添加新字段。