MySQL数据表操作详解-优快云博客

本文详细介绍了MySQL数据表的操作，包括创建数据表时的主键、外键、非空、唯一性和默认约束，以及查看表结构、修改表、删除表等。强调了主键和外键在数据完整性中的作用，提供了修改字段类型、名称、排列位置和存储引擎的方法，并讨论了删除表时的注意事项，尤其是涉及外键约束的情况。

1.01 创建数据表

创建表的语法形式
创建数据表的语法为CREATE TABLE

CREATE  TABLE <表名>
      (
	列名1  数据类型 [列级别约束条件] [默认值],
	列名2  数据类型 [列级别约束条件] [默认值],
	……
	[表级别约束条件]
       );

使用主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key constraint）要求主键列的数据唯一，并且不允许为空。

单字段主键
```
字段名 数据类型 PRIMARY KEY [默认值]
```
多字段联合主键
```
 PRIMARY KEY [字段1, 字段2,. . ., 字段n]
```
使用外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
```
  [CONSTRAINT <外键名>] FOREIGN KEY 列名1 [ ,列名2,…] 
  REFERENCES <主表名> 主键列1 [ ,主键列2,…]
```
注：同一个字段可同时作为主键和外键
使用非空约束

非空约束（Not Null constraint）指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时，没有指定值，数据库系统会报错。

字段名数据类型 not null
使用唯一性约束

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值(MySQL允许出现多个空值)。唯一约束可以确保一列或者几列不出现重复值。

字段名数据类型 UNIQUE
使用默认约束

默认约束（Default Constraint）指定某列的默认值。

字段名数据类型 DEFAULT 默认值
设置表的属性值自动增加

在数据库应用中，经常希望在每次插入新记录时，系统就会自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。

字段名数据类型 AUTO_INCREMENT

注：当主键设为AUTO_INCREMENT后不能设置默认值；
```
 当两个以上的列共同作为主键即联合主键时，最多只能有一个列可以设位AUTO_INCREMENT，
且自增列应放置于PRIMARY KEY(主键列1[,注解列2...])中的第一个位置
```
[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE products_description(
	products_id int(11) NOT NULL,
	language_id int(11) AUTO_INCREMENT NOT NULL,
	products_name varchar(64) NOT NULL default '',
	products_description  text,
	PRIMARY KEY(language_id,products_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表

CREATE TABLE emp (
	  id int(11) PRIMARY KEY  AUTO_INCREMENT,
	  name varchar(255) DEFAULT NULL,
	  birthday date DEFAULT NULL,
	  did int(11) DEFAULT NULL,
	  age int(11) DEFAULT NULL,
	  gender char(2) DEFAULT NULL,
	  sal double DEFAULT NULL,
	  CONSTRAINT fk_did FOREIGN KEY (did) REFERENCES dept (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

1.02 查看数据表结构

查看表基本结构语言DESCRIBE

 该语句可以查看表的字段信息，其中包括：字段名、字段数据类型、是否为主键、
是否有默认值等。

DESCRIBE <表名>/DESC <表名>

在这里插入图片描述

查看表详细结构语言SHOW CREATE TABLE

语句可以用来显示数据表的创建语句。
```
SHOW CREATE TABLE <表名\G>
```

1.03 修改数据表

修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的。
```
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
```
修改字段的数据类型

修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。
```
 ALTER TABLE <表名> MODIFY <字段名>  <数据类型>
```

修改字段名

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

ALTER TABLE <表名> 
CHANGE <旧字段名> <新字段名> <新数据类型>;

在这里插入图片描述

添加字段

在已经存在的表添加新的字段。

ALTER TABLE <表名> 
ADD [column] <新字段名> <数据类型> 
[约束条件] [FIRST | AFTER 已存在字段名];

在这里插入图片描述

删除字段

删除字段是将数据表中的某个字段从表中移除。
```
ALTER TABLE <表名> DROP <字段名>;
```

修改字段的排列位置
ALTER TABLE来改变表中字段的相对位置。

ALTER TABLE <表名> 
MODIFY <字段1> <数据类型>  FIRST|AFTER <字段2>;

更改表的存储引擎

在MySQL中，存储引擎是指MySQL数据库中表的存储类型。可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。

ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。
```
 ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
```

1.04 删除数据表

删除没有被关联的表

MySQL中，使用DROP TABLE可以一次删除一个或多个没有被其它表关联的数据表。
```
DROP TABLE [IF EXISTS]表1, 表2, . . . 表n;
```
删除被其他表关联的主表

在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除，将破坏表的参照完整性。如果必须要删除，可以先删除与之关联的子表，再删除父表。但是这样同时删除了两个表中的数据。
但有的情况下可能要保留子表，这时如要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表(也就是说将外键约束删除ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>)。

1.05 综合案例——数据表的基本操作