深入浅出SQL：4 聪明的表设计

声明：文章系《深入浅出SQL》一书的阅读笔记。

---

LIKE 搜索精确数据的能力不够。

设计表

1 挑出事物，挑出希望表描述的某样事物。
2 列出一份关于那样事物的信息列表，这些信息时使用表时的必要信息。
3 使用信息列表，把关于那样事物的综合信息拆成小块信息，以便组织表。

原子性数据

当数据具有原子性（atomic），表示它已经是最小块，不能再被分割（分割成创建有效率的表所需的最小片段）

原子性数据的正式规则：
规则一：具有原子性数据的列中，不会有多个类型相同的值。

food_name ingredients bread water, milk, egg, oil

规则二：具有原子性数据的表中，不会有多个存储同类数据的列。

teacher student1 student2

规范化表的优点：
1. 表中没有重复数据，可以减小数据库大小
2. 因为查询的数据较少，查询更快

1NF 第一范式

First Normal Form
1. 每个数据行必须包含具有原子性的值
2. 每个数据行必须有独一无二的识别项（主键 primary key）

主键规则

1. 主键用于独一无二的识别每条数据；
2. 主键不可以为NULL；
3. 插入新记录时必须指定主键值；
4. 主键必须简洁。
5. 主键值不可以被修改

规范旧表

步骤
1 SELECT 所有数据并存储在其他地方
2 创建一个新的规范表
3 把所有旧数据INSERT 到新表，并改变每一行来符合新表的结构。

SHOW CREATE TABLE my_contacts;

可返回可以创建表，但没有数据的CREATE TABLE语句。

CREATE TABLE `my_contacts` (  `last_name` varchar(30) DEFAULT NULL,    ...  `seeking` varchar(100) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=gbk

加上主键的CREATE TABLE

CREATE TABLE my_contacts (  contact_id INT NOT NULL,  last_name varchar(30) DEFAULT NULL,  ......  seeking varchar(100) DEFAULT NULL,  PRIMARY KEY(contact_id)) 

主键列自动递增AUTO_INCREMENT

CREATE TABLE my_contacts (  contact_id INT NOT NULL AUTO_INCREMENT,  last_name varchar(30) DEFAULT NULL,  ......  seeking varchar(100) DEFAULT NULL,  PRIMARY KEY(contact_id)) 

练习

创建表

CREATE TABLE your_table(id INT NOT NULL AUTO_INCREMENT,first_name VARCHAR(20),last_name VARCHAR(30),PRIMARY KEY(id));

插入

INSERT INTO your_table(id, first_name, last_name) VALUES(NULL, 'Marcia', 'Brady');主键值为NULLINSERT INTO your_table VALUES(2,'bobby','brady');可以插入特定的主键值INSERT INTO your_table(first_name, last_name) VALUES('cindy','brady');   插入除了主键列的值INSERT INTO your_table VALUES(99,'bobby','brady');99会覆盖主键列的值，再插入主键列值从100开始

给已有数据的表添加主键

添加主键

ALTER TABLE my_contacts    ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,    ADD PRIMARY KEY(contact_id);

好用的show指令

显示表的所有列以及数据类型。

show columns from my_contacts;或desc my_contacts;

查看索引

SHOW INDEX FROM my_contacts;

小结

数据原子性、规则、主键、第一范式、自增、查看表结构