MySQL基础(4)——数据表操作

数据库范式

数据库范式就是在设计关系型数据库时，遵循的规范，这样就能设计出比较合理的关系型数据库。
关系型数据库有6中范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF)，一般来说数据库满足第三范式就可以了。

• 第一范式(1NF)——数据库表的每一列都是不可再分的原子数据项，也就是说数据库的字段是单一属性的。由于MySQL数据库的字段都是单一属性的，所以第一范式在我们创建数据库的时候就已经满足了
• 第二范式(2NF)——非码属性必须完全依赖于候选码。也就是说数据库中的每一个实例或者记录可以被唯一区分。如果没有对应可以区分的属性，也可以设置id来进行区分。
• 第三范式(3NF)——任何非主属性不依赖其他非主属性。也就是一个表中包含一个实体属性，如果包含了其他的实体属性，应该设置相应的表，该表中只保留其实体的主属性。

其他的范式就不再赘述了，有需要了解的可以百度一下。

数据表

数据表是由行和列构成的包含了特定实体类型数据的二维网络，是数据库的重要组成部分之一。

创建数据表

那么来创建数据表吧，首先来看一下数据表创建语句：

CREATE TABLE [IF NOT EXISTS] tb_name(
    字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT],
    ...
    [PRIMARY KEY(主键字段名)]
    [UNIQUE KEY [唯一键名称]字段名称]
    [[CONSTRAINT 外键名称]FOREIGN KEY(本表字段名) REFERENCES 外键表名(外键表字段) [ON DELETE|UPDATE [CASCADE|SET NULL|RESTRICT]...]]
   )[ENGINE=引擎名称 CHARSET=编码方式 AUTO_INCREMENT=自增长起始值];

看到上面的可能有些乱，那就写一个最简单的创建语句：

CREATE TABLE test1(
id TINYINT AUTO_INCREMENT KEY,
name VARCHAR(20),
sex ENUM("男","女","保密"),
age TINYINT  
);

如果你输入这条语句出现了如下错误：

ERROR 1291 (HY000): Column 'sex' has duplicated value '?' in ENUM

说明你的数据库编码没有改过来。有两中解决方法：

1. 将你的数据库编码改成“UTF8”

ALTER {DATABASE|SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name

2. 创建数据表的时候指定编码方式

CREATE TABLE test1(
id TINYINT AUTO_INCREMENT KEY,
name VARCHAR(20),
sex ENUM("男","女","保密"),
age TINYINT  
)CHARSET="UTF8";

好了，这就创建了一个简单的数据表，一个数据表创建语句可以分为三个部分：

1. CREATE TABLE [IF NOT EXISTS] tb_name 这是创建表语句，[IF NOT EXISTS]是防止数据表已经创建而导致创建失败造成的错误。
2. ()括号里面的内容是创建表的结构，可以声明字段以及建立索引，主键等类容
3. 括号后面的内容，可以指定表创建是所使用的存储引擎，数据表所使用的编码以及数据表自增字段的起始值等。

()里的内容可以创建数据库字段，在创建数据库字段的时候，可以添加完整性约束条件，完整性约束条件有以下几种：

• PRIMARY KEY 主键
• AUTO_INCREMENT 自增长
• FOREIGN KEY 外键
• NOT NULL 非空
• UNIQUE KEY 唯一
• DEFAULT 默认值

PRIMARY KEY(主键)

在创建的时候可以省略PRIMARY，该字段就定义为主键。每一个表应该有且只有一个主键来标识每条记录。主键不能为空，一般常用id来作为数据库的主键，并且设置为自增长。

id TINYINT [PRIMARY] KEY,

有时候在创建表的时候忘记设置主键了，可以通过ALTER来添加主键。

ALTER TABLE tb_name ADD CONSTRAINT [主键名称] PRIMARY KEY 字段名称

这里CONSTRAINT是给创建的主键取一个名字，PRIMARY KEY后面接的是要定义的主键字段名称，也可以是复合主键，如(id,name)复合主键就是当两个都相同的时候才算重复。

那如果需要删除主键呢？也有方法

ALTER TABLE tb_name DROP PRIMARY KEY;

由于主键是唯一的，直接DROP PRIMARY KEY就可以知道要删除的是哪个键。

AUTO_INCREMENT(自增长)

设置为AUTO_INCREMENT的字段，每次添加内容的时候会自动加1，一般的话使用在主键id上。为什么每次添加的时候器值会自动加1呢？因为数据库会存储AUTO_INCREMENT的值，每次添加的时候将该值赋给自增长的字段，然后更新AUTO_INCREMENT的值。当然也可手动去设置AUTO_INCREMENT的值。

ALTER TABLE tb_name AUTO_INCREMENT=num; 

FOREIGN KEY(外键)

外键一般是用于多表之间的关联，通常代表1对多的关系。设置的时候可以在设置字段之后进行设置，设置外键的话，需要所使用的存储引擎支持外键操作，可以将存储引擎设置为innodb，MySQL默认的存储引擎也是innodb。

ALTER TABLE tb_name ENGINE="innodb";

外键设置的语句如下：

CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 表名(指向字段) [ON DELETE|UPDATE SET NULL|CASCADE]

当两个表之间进行了外键关联之后，就有了外键约束，外键约束有四种：

• CASCADE级联，父表删除或更新行，子表跟着删除或更新相应的记录。
• SET NULL 设置为空，父表删除或更新行，子表将对应的数据设置为NULL
• RESTRICT 拒绝操作，当子表有对应的数据时，将拒绝父表进行删除或更新行
• NO ACTION 和RESTRICT一样，在MySQL中是默认操作。

那需要怎么设置呢？

1. 在设置外键后面添加ON字段即可，ON 操作 操作类型，简单的来一个示例：

CONSTRAINT 's_to_c' FOREIGN KEY (cid) REFERENCES course(id) ON DELETE SET NULL ON UPDATE CASCADE

这里将本表的cid和course表的id进行外键连接，然后delete操作的时候设置为NULL，更新操作的时候为级联。当course表删除了某个课程之后，本表就会将设置了相应课程对应的cid设置为null，当course表更新了某个课程的id之后，那么本表就会将设置了相应课程的cid更新。

2. 通过ALTER进行设置

ALTER TABLE tb_name ADD [CONSTRAINT 外键名称] FOREIGN KEY (外键字段) REFERENCES f_tb(fkey_id);

如果要撤销外键依赖只需要如下操作：

ALTER TABLE db_name DROP FOREIGN KEY 外键名称;

NOT NULL

设置了NOT NULL的字段是不允许设置为NULL值的，当然PRIMARY KEY本身就是不能为NULL的。当该字段设置为NULL的时候，会出现错误。

UNIQUE KEY

字段设置UNIQUE KEY的时候可以省略KEY，即只写UNIQUE，MySQL也是认可的，当字段设置为UNIQUE时，器字段不能重复，当然NULL值不算重复。UNIQUE KEY有三种设置方法：

1. 直接在字段上设置

card VARCHAR(18) UNIQUE

2. 设置完字段后，再设置字段为UNIQUE KEY

CONSTRAINT 'uni_card' UNIQUE (card)

3. 使用ALTER方法进行设置

ALTER TABLE tb_name ADD CONSTRAINT uni_name UNIQUE (uni_key)

最后当然需要讲解一下，撤销唯一索引

ALTER TABLE tb_name DROP INDEX 索引名称;

DEFAULT

DEFAULT是默认值，也就是如果用户不设置值的时候，该字段会自动填充默认值。
设置默认值很简单，只需要在字段后面接

DEFAULT="default_value"

即可，当然忘记设置了也没有问题，因为有ALTER操作

ALTER TABLE tb_name ALTER 字段名称 SET DEFAULT 默认值

撤销默认值的操作正好相反

ALTER TABLE tb_name ALTER 字段名称 DROP DEFAULT;

注意

---

如果没用CONSTRINT设置名称的话，对应的UINQUE，FROEIGN KEY的名称默认为字段名称。

---

总结

---

设置字段的完整性约束如PRIMARY KEY，UINQUE KEY，PRIMARY KEY, DEFAULT时可以通过ALTER来进行设置。
并且前三个是通过ADD|DROP来添加完整性约束，后者是通过ALTER...SET|DROP来进行。

ALTER TABLE tb_name ADD [CONSTRAINT 约束名称] {PRIMARY KEY (字段名称)|UNIQUE KEY(字段名称)|PRIMARY KEY(字段名称) [REFERENCES f_tb(fkey)]}
ALTER TABLE tb_name DROP {PRIMARY KEY |UNIQUE KEY|PRIMARY KEY 约束名称}

默认值

ALTER TABLE tb_name ALTER 字段名称 SET DEFAULT "默认值";
ALTER TABLE tb_name ALTER 字段名称 DROP DEFAULT;

---

查看数据表

1. 查看数据库下的表

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
+----------------+
1 row in set (0.01 sec)

2. 查看数据表的表结构

mysql> DESC test1;
+-------+----------------------------+------+-----+---------+----------------+
| Field | Type                       | Null | Key | Default | Extra          |
+-------+----------------------------+------+-----+---------+----------------+
| id    | tinyint(4)                 | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)                | YES  | UNI | NULL    |                |
| sex   | enum('男','女','保密')      | YES  |     | NULL    |                |
| age   | tinyint(4)                 | YES  |     | NULL    |                |
+-------+----------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

当然还有两种方法也可以查看数据表的表结构，当然上面的方法比较简洁。

DESCIBE tb_name;
SHOW COLUMNS FROM tb_name;

3. 查看数据表创建语句

mysql> SHOW CREATE TABLE test1;
+-------+-----------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` enum('男','女','保密') DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8     |
+-------+----------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到创建表的详细语句，包括有些默认的设置也显示出来了，比较详细。

修改数据表

在创建数据库中已经讲解了一些修改数据库完整性约束的方法，下面主要讲解一下，修改数据表的表名和字段名

1. 修改数据表名
   修改数据表名有两种方法，一种是使用ALTER...RENAME方法，另一种是RENAME...TO方法。

ALTER TABLE tb_name RENAME [TO|AS] new_name;
RENAME tb_name TO new_name;

2. 修改数据表字段名

# 添加字段名
# FIRST是添加到数据表字段的第一个位置
# AFTER是添加到数据库中的某个字段后
# 默认是添加在数据表字段末尾)
ALTER TABLE tb_name ADD 字段名称 字段类型 [完整性约束] [FIRST|AFTER 字段名称];

# 修改字段名
# 这里需要将完整性约束添加完整，不然会丢失相应的信息
ALTER TABLE tb_name MODIFY 字段名称 字段类型 [完整性约束] [FIRST|AFTER 字段名称];
ALTER TABLE tb_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束] [FIRST|AFTER 字段名称];

# 删除字段名
ALTER TABLE tb_name DROP 字段名称;

删除数据表

删除数据表和创建数据表是一个相反的过程，定义语句如下：

DROP TABLE [IF EXISTS] tb_name;

数据表的操作大致就这些了。写的不好请指出！