《SQL入门经典》-数据库设计

时间：2010-10-20 来源：zhangjunhd

《SQL入门经典》 Paul Wilton;John Colby
清华大学出版社
ISBN：7302128332

1.数据库设计的3个范式
1.1 第一范式
-定义所需要的数据项，因为它们将成为表中的列。将相关的数据项放置在一个表中。
-确保没有重复的数据项。
-确保存在一个主键。

1.2 第二范式
符合第一范式，并且要求主键中的任意列必须没有局部相关性。

例如存在一张表：
FilmId      int             PK
FilmName    varchar(100)
ActorId     int             PK
ActorName   varchar(100)
DateOfBirth date

这里存在局部相关，FilmName依赖于FilmId，而ActorName和DateOfBirth依赖于ActorId，所以应该拆分成3张表：
table1
FilmId      int             PK
FilmName    varchar(100)

table2
ActorId     int             PK
ActorName   varchar(100)
DateOfBirth date

table3：
FilmId      int             PK
ActorId     int             PK
表3记录影片和演员的关联。

1.3 第三范式
符合第二范式，并且要求非主键字段不存在传递相关性(即所有的非主键字段都依赖于主键，而不是通过其中某个非主键字段，传递依赖于主键)。

例如一张表：
MemberId    int             PK
FirstName   varchar(30)
LastName    varchar(30)
Zipcode     char(6)
Street      varchar(50)
City        varchar(50)
State       varchar(50)

这里Street,City,State3个字段存在传递相关性，其实它们依赖于Zipcode。所以应该拆分成2张表：
table1
MemberId    int             PK
FirstName   varchar(30)
LastName    varchar(30)

table2
Zipcode     char(6)         PK
Street      varchar(50)
City        varchar(50)
State       varchar(50)

消除传递相关性的好处是，数据重复量降低，不存在数据冗余的情况。坏处是增加了复杂性并降低了效率，数据库将花费更长的时间检索数据。

2.利用约束确保数据有效性
2.1 NOT NULL约束
NOT NULL可以确保列必须具有一个值，否则记录就不能插入到数据库中。

mysql> create table tmp
-> (
-> column1 int not null,
-> column2 varchar(20),
-> column3 varchar(12) not null
-> );
mysql> alter table tmp modify column2 varchar(20) not null;

2.2 UNIQUE约束
UNIQUE约束防止一个特定的列中的两个记录具有相同的值。

mysql> create table myuniquetable
-> (
-> column1 int,
-> column2 varchar(20) unique,
-> column3 varchar(30) unique
-> );
mysql> insert into myuniquetable values (1,'abc', 'xyz');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myuniquetable values (1,'efg', 'xyz');
ERROR 1062 (23000): Duplicate entry 'xyz' for key 'column3'

设定组合UNIQUE：

mysql> create table myuniquetable2
-> (
-> column1 int,
-> column2 varchar(20),
-> column3 varchar(20),
-> constraint myuniqueconstraint unique (column2,column3)
-> );
mysql> insert into myuniquetable2 values (1,'abc', 'xyz');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myuniquetable2 values (1,'efg', 'xyz');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myuniquetable2 values (1,'abc', 'uvw');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myuniquetable2 values (1,'abc', 'xyz');
ERROR 1062 (23000): Duplicate entry 'abc-xyz' for key 'myuniqueconstriant'

可以设置多个约束：

mysql> create table myuniquetable2
-> (
-> column1 int,
-> column2 varchar(20),
-> column3 varchar(20),
-> constraint myuniqueconstraint1 unique (column2,column3)，
-> constraint myuniqueconstraint2 unique (column1,column3)
-> );

更改和删除约束：

mysql> alter table myuniquetable2 add constraint myuniqueconstraint3 unique (column2,column3);
mysql> alter table myuniquetable2 drop index myuniqueconstraint3;

2.3 CHECK约束
CHECK约束检查输入的记录的值是否满足一个条件。如果条件为假，则该记录不被输入到表中。

mysql> create table nameage
-> (
-> name varchar(30),
-> age int check (age>=0)
-> );

注，在mysql中check约束不起作用。

2.4 主键约束
每个表都需要主键，主键提供了表之间的链接。主键 = UNIQUE + NOT NULL

mysql> create table booking
-> (
-> customerId int primary key,
-> bookingId int,
-> destination varchar(30)
-> );

2.5 外键约束
外键是访问另外一个表中的主键的列。主键和外键创建了不同表中数据的相互关系。

在mysql中，需要体现外键约束，必须使用InnoDB，而模式应该是MyISAM。需要更改引擎。

mysql> create table record
-> (
-> bookingId int primary key,
-> checkin date
-> );
mysql> alter table booking type =InnoDB;
mysql> alter table record type =InnoDB;
mysql> alter table booking add constraint booking_pk foreign key (bookingId) references record(bookingId);
mysql> insert into booking values (1,1,'beijing');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`filmClub`.`booking`, CONSTRAINT `booking_pk` FOREIGN KEY (`bookingId`) REFERENCES `record` (`bookingId`))

由于在record表中没有主键值为1的记录，所以在booking表中无法插入这条记录。这就是外键约束。

3.利用索引加速结果查询

添加与删除索引

CREATE INDEX index_name ON table_name column_names
ALTER TABLE table_name DROP INDEX index_name
mysql> use filmClub;
mysql> select FirstName,LastName from MemberDetails;
+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Katie | Smith |
| Sandra | Jakes |
| Steve | Gee |
| John | Jones |
| Jenny | Jones |
+-----------+----------+
mysql> create index member_name_index on MemberDetails(FirstName,LastName);

此时再进行查询，发现记录已经排序(默认升序)。

mysql> select FirstName,LastName from MemberDetails;
+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Jenny | Jones |
| John | Jones |
| Katie | Smith |
| Sandra | Jakes |
| Steve | Gee |
+-----------+----------+
mysql> alter table MemberDetails drop index member_name_index;

下面的语句创建一个唯一索引，它先根据姓的降序排序，再根据名排序。

mysql> create unique index member_name_index on MemberDetails (LastName desc, FirstName);
mysql> select FirstName,LastName from MemberDetails;
+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| Steve | Gee |
| Sandra | Jakes |
| Jenny | Jones |
| John | Jones |
| Katie | Smith |
+-----------+----------+

unique确保LastName + FirstName组合唯一。

示例数据库见 http://zhangjunhd.blog.51cto.com/113473/55150