概念
说明
创建表时可以遵循的步骤
挑出事物,挑出你希望表描述的某样事物;
列出一份关于那样事物的信息列表,这些信息都是使用表的必要信息;
使用信息列表,把关于那项事物的综合信息拆分成小信息块,以便于组织。
原子性
当数据具有原子性,就表示它已经被分割至最小块,已经不能或不应该再被分割。规范化表的第一步,让数据具有原子性。
原子性数据的正式规则
具有原子性数据的列中不会有多个类型相同的值。
具有原子性数据的表中不会有多个存储同类数据的列。
规范化表的优点
没有重复数据,可以减小数据库的大小;
查询的数据较少,你的查询会更为快速。
1NF(第一范式)
每个数据行必须包含具有原子性的值;
每个数据行必须有独一无二的识别项,称主键。
主键
主键用于独一无二地识别出每一条记录。
主键不可以为NULL;插入新数据时必须要指定主键值;主键必须简洁;主键值不可以 被修改。
1NF
规则一:数据列只包含具有原子性的值;
规则二:没有重复的数据组;
组合主键
由两列以上组成的主键叫组合主键。组合主键就是由多个数据列构成的主键,组合各列后形成具有唯一性的键。
单独的某一列并不能作为主键,当多列组合时,确保了唯一性,可以作为组合主键。
速记符号:T.x -> T.y,
关系表T中,y列函数依赖于x列
部分函数依赖
非主键的列依赖组合主键的某一部分。
传递依赖
IF 改变任何非键列可能造成其它列的改变,即为传递依赖。
传递函数依赖
任何非键列与另一个非键列有关联。
2NF
规则一:先符合1NF
规则二:没有部分函数依赖性
任何具有人工主键且没有组合主键的表都符合2NF
3NF
规则一:符合2NF;
规则二:没有传递函数依赖性;
索引
主键和外键属于索引;考虑创建索引的对象必须在常用的列上。
模式(schema)
用于表达数据库内的结构,包括表和列,还有各种它们之间相互连接的方式。
外键
外键是表中的某一列,它引用到另一个表的主键。
外键可能与它引用的主键不同。
外键使用的主键成为父键;主键所在的表成为父表。
外键可以为NULL,表示父表中没有相符的主键;外键的值不需要唯一
外键不一定必须是父表的主键,但必须唯一
自引用外键
引用同一张表内另一列的键
约束(constraint)
创建在结构内的键被称为约束(constraint)(约束可以确保只能在子表中插入已经存在父表中的值)
除了主键约束,其它常见的几种约束有:UNIQUE,以及一种MySQL 不支持的约束:检查约束
引用完整性
插入外键列中的值必须已经存在父表的来源列中。例如:
CREATE TABLE interests
(
int _id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interset VARCHAR(50) NOT NULL,
contact_id INT NOT NULL,
CONSTRAINT my_contacts_contact_id -- 告诉我们键的来源表(my_contacts),键的名称(contact_id),还能说明它是一个外键
FOREIGN KEY(contact_id)
REFERENCES my_contacts(contact_id)
)
常见的数据模式
一对一
A表的一行最多只能与B表的某行关联,把一对一的数据留在主表更合适;
一对多
A表中的某一条数据,对应B表中的多行;B表中的某一条记录只能对应A表中的某一条记录
多对多
多对多的示例采用的woman和shoe的关系表述的,在处理两个表的时候,引出了junction table(连接表)。
列的别名(correlation name,相关名称)
别名的设定方式,在查询中首次出现表名的后面接AS(AS 可以省略) 并设定别名
交叉连接(笛卡尔积)
SELECT t.toy, b.boy FROM toys AS t CROSS JOIN boys AS b;
SELECT toys.toy,boys.boy FROM toys,boys; 上述语句的同意用法
INNER JOIN
SELECT somecolumns FROM table1 INNER JOIN table2 ON somecondition;
INNER JOIN 利用条件式里的比较运算符结合两张表;其中ON 可以替换成WHEN
相等连接、不等连接是INNER JOIN 后的比较运算为=和<>
自然连接(不使用“ON”子句的内连接)
SELECT boys.boy,toys.toy FROM boys NATURAL JOIN toys;
只有在连接的列在两张表中的名称都会相同时才会用到;NATURAL JOIN 利用相同列名的内联接
LEFT OUTER JOIN
会匹配左表中的每一行的及右表中符合条件行。
RIGHT OUTER JOIN
外连接和内连接的区别
外联接一定会提供数据行,无论该行能否在另一张表中找出匹配的行。在外联接的结果中NULL 表示右表中没有找到与左表相符的记录。
自联接
自联接能把单一的表当成两张具有完全相同的信息的表来进行查询
UNION,联合
可以把两张或多张表的查询结果合并至一个表中。UNION 只能接受一个ORDER BY 且必须位于语句的末端
UNION ALL
返回列的所有内容,而不是每个值的复制实例。
INTERSECT(交集) 和 EXCEPT(差集,返回出现在第一个查询,而不再第二个查询中的列)
子查询
当单一查询不够用的时候,请使用子查询。外部的查询称为包含查询或外层查询;内部的查询就是子查询。
作为欲选取列的子查询。
如果子查询可以独立运行且不会引起外层查询的任何结果,即称为非关联子查询。对应的一个概念:关联子查询
子查询可以搭配 IN,NOT IN,EXISTS,NOT EXISTS
检查约束(CHECK)
CREATE TABLE piggy_bank
(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
coin CHAR(1) CHECK (coin in ('P','N','D','Q'))
)
创建视图(虚拟表)
视图会像表一样出现在数据库中
CREATE VIEW web_designers AS SELECT xx
SELECT * FROMeb_designers; -- 查看创建的视图
视图对数据库的好处
把复杂查询简化成一个命令
即使一直改变数据库,也不会破坏依赖表的应用程序;
可以隐藏读者无需看到的信息
带有 CHECK OPTION 的视图
CREATE VIE wW pb_dimes AS SELECT * FROM piggy_bank WHERE coin ='D' WITH CHECK OPTION;
CHECK OPTION 检查每个进行 INSERT 或 DELETE 的查询,它根据视图中的WHERE 子句来判断这些查询是否可执行,IF 不符合WHERE 条件,插入操作被拒绝
可更新视图
可更新视图包括引用表里所有为NOT NULL 的列
DROP VIEW pb_dimes;
CHECK CONSTRAINT
当数据库的使用者不只一人时,CHECK CONSTRAINT 与视图均有助于维护控制权。
事务
事务是一群可以完成的一组工作的SQL语句。
在事务过程中,如果所有的步骤无法不受干扰的完成,则不该完成任何单一步骤。
构成一个事务的原则(ACID)
原子性。事务里的每一个步骤都必须完成,否则只能都玩不成。
一致性。事务完成后应该维护数据库的一致性。
隔离性。每次事务都会看到具有一致性的数据库,无论其它事务有什么行动;
持久性。事务完成后,数据库需要正确地存储数据并保护数据免受断电或其他威胁的伤害。
START TRANSACTION;
持续追踪后续所有SQL语句,直到你输入COMMIT 或 ROLLBACK
ROLLBACK 回滚,回到事务开始前的状态
引擎
存储引擎必须是BDB或InnoDB两种支持事务的引擎之一
ALTER TABLE your_table TYPE =InnoDB;