2021-02-22 SQL server实践记录---2数据库表创建与设计

坚持学习，持续学习

相关概念和范式

1.函数依赖
定义：给定一个X一定能查到Y，就是Y依赖于X，写成X→Y。
理解：身份证号（X）一定跟查到一个姓名（Y）。姓名依赖于身份证号，身份证号→姓名。
（1）完全函数依赖
定义：在一张表中，若X→Y，且对于X的任何一个真子集（假如属性组X包含超过一个属性）X’，都有X’→Y不成立，那么我们称Y对于X完全函数依赖。
理解：X包含属性（a, b），并且Y依赖于X（X→Y），但是Y不依赖于a, 并且Y不依赖于b, 那么Y对于X完全函数依赖。（X中去掉任何一个，X和Y的函数依赖关系不复存在）
（2）部分函数依赖
定义：假如Y函数依赖于X，但同时Y并不完全函数依赖于X，那就称Y部分函数依赖于X。
理解：满足函数依赖，但是不满足完全函数依赖的一种折中依赖关系，比如下面这种情况
X’包括属性（a, b），Y完全函数依赖于X’。又X包含属性（X’, c），即X（a, b, c），并且Y不依赖于c，那么Y部分函数依赖于X，因为X的真子集（a, b）→Y，但是Y不依赖于（a, c），Y也不依赖于（b, c），即Y和X不是完全函数依赖的关系。
（3）传递函数依赖
定义：加入Z函数依赖于Y，且Y函数依赖于X，且Y不包含于X，X不函数依赖于Y，那么我们称Z传递依赖于X。
理解：X→Y，Y→Z，此时还不够，还要对X,Y进行额外条件的约束，Y不包含于X（A包含于B的意思是A是B的子集或A=B。换言之Y不是X的子集并且Y不是X），且X不函数依赖于Y。

2.码
（1）码
概念：能唯一标识实体的属性，他是整个实体集的性质，而不是单个实体的性质。
理解：实体E（学号，身份证号，姓名，性别，年龄）中能唯一标识这个实体E的字段有“身份证号”，或者在这个学校中，“学号”也可以，所以在这个环境下，身份证号或者学号都叫做码。就是这一个字段可以找到唯一确定这一行。
（2）超码
概念：一个或多个属性的集合，这些属性可以让我们在一个实体集中唯一地标识一个实体。如果K是一个超码，那么K的任意超集也是超码。
理解：也即包含码字段的集合都是超码。实体E（学号，身份证号，姓名，性别，年龄），超码包括但不限于（身份证号，学号），（学号，姓名）等。
（3）候选码
概念：若关系中的一个属性或者属性组的值能够唯一地标识一个元组，且它的真子集不能唯一的标识一个元组，则称这个属性或者属性组做候选码。
理解：是超码，是最小的超码。超码的范围太广候选码是最小的超码，它的任意真子集都不能成为超码。如上，（学号）、（身份证号）、（姓名，性别，年龄）
（4）主码
概念：被数据库设计者选中的，用来在统一死体积重区分不同实体的候选码。
理解：候选码中选一个我们理解方便的。
主属性：所有候选码中所有属性的并集中的任意一个属性都是主属性，不为空（可理解为主键）———学号、身份证号
非主属性：不包含候选码中的属性———姓名、性别、年龄
注：码都可以唯一确定这一条记录

3.范式可防止数据冗余
（1）第一范式（1NF）
所有的域都应该是原子性，就是说数据库表的每一列都是不可分可的原子数据项，不能是集合，数组，记录等非原子数据项。
（2）第二范式（2NF）
在1NF基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
不允许存在那种非主属性部分依赖于主码的情况。这有时候会将一张表拆分为两张表。（主码只有一个字段）
主码有多个时，注意主码属性之间的排列组合与非主属性之间全部要有函数依赖关系
（3）第三范式（3NF）
在2NF的基础上，任何非主属性不依赖于其它非主属性（在2NF的基础上消除传递依赖），举例
[学号，姓名，所在系，系地址]
主属性：学号 学号→（姓名，所在系，系地址）
非主属性：姓名，所在系，系地址
存在的问题：学号→系地址存在传递依赖关系，学号→所在系，所在系→系地址。（姓名可能重名）
拆分：[学号，姓名，所在系]、[系名，系地址]
非主属性有多个注意非主属性之间是否存在函数依赖关系
（4）巴斯-科德范式（BCNF）
定义：在3NF的基础上，任何非主属性不能对主键子集依赖（在3NF的基础上消除对主码子集的依赖）
举例
仓库（仓库名，管理员，物品名，数量）
1）每个仓库只能有一名管理员，一名管理员只能在一个仓库中工作；
2）一个仓库汇总可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应的数量。
审题
1）对应关系：
每个仓库只能有一名管理员 仓库名→管理员
一名管理员只能在一个仓库中工作 管理员→仓库名
一个仓库汇总可以存放多种物品，一种物品也可以存放在不同的仓库中。每种物品在每个仓库中都有对应的数量。(仓库名,物品名)→(管理员,数量) 或者可以说 (管理员,物品名)→(仓库名,数量)
2）候选码：
（仓库名，物品名）或者（管理员，物品名）
3）主属性：仓库名，管理员，物品名（注意之前提到过主属性不能为空）
4）非主属性：数量
存在问题
1）想增加一个仓库，不存放物品，不指派管理员
不行，因为主属性不能为空
2）想清空一个仓库，要删除所有与这个仓库相关的物品存放记录
不行，因为这个仓库与管理员信息也被删除了
3）如果某个仓库更改管理员
将会更改这个表中的每一条记录
结论
1）满足第三范式
2）不属于BCNF范式

• 主属性仓库名，对候选键（管理员，物品名）存在部分函数依赖
• 主属性管理员，对候选键（仓库名，物品名）存在部分函数依赖

3）拆分
[仓库名，管理员]
[仓库名，物品名，数量]
存在相互依赖关系时就要防止BCNF范式，这里仓库名和管理员相互依赖，A→B，B→A

数据库表的创建，修改，删除

1.创建普通的数据库表

use Myfirstdb
Create Table cangku
(
	CangKuBH	int primary key,		//primary key主键，不可空，不可重复。可设置自动编号identity(1,1) primary key
	CangKuHao	varchar(50) unique,		//unique 唯一索引，可空，不可重复
	ChengShi	varchar(50) not null,	//not null 不可为空。(default '北京')设置默认值为“北京”
	MianJi		int check(MianJi >= 300 and MianJi < = 1800)	//约束条件（运算符有<、>、=、!=、<=、>=、and，or，not）
)

identity(1,1) primary key不是数据类型，是一个等差数列，要求字段必须是整型。
又如下

Create Table gongzi
(
	zhigongbh	int identity(1,1) primary key,
	zhigonghao	varchar(50) unique,
	cangkuhao	varchar(50) reference cangku(cangkuhao),
	jibengz		int check(jibengz >= 3000 and jibengz <= 5000),
	jiabangz	int,
	jiangjin	int,
	koushui		int,
	shifagz		as(jibengz + jiabangz + jiangjin - koushui)
)

1）这里shifagz（实发工资）是通过计算得到的。
2）上面创建的两张表，cangku和gongzi，通过“cangkuhao”进行关联，代码如下：
cangkuhao varchar(50) reference cangku(cnagkuhao),
这表示，gongzi表中的cangkuhao必须依赖于cangku表中的cangkuhao（这里随便举的例子，可能并没有现实意义）。在创建表时，要先创建cangku表才能创建gongzi表；插入数据时，也要先向cangku表中插入；并且gongzi表中的cangkuhao必须能够在cangku表中存在。
2.数据库表的修改
1）改表名

exec sp_rename 'cangku','CangKuNew'

2)增加新字段

alter table CangKuNew
add qita varchar(50) default '***仓库'		//有一个默认值

3）改字段，长度从50变成100

alter table gongzi
alter column qita varchar(100)

4）删除字段

alter table CangKuNew
drop column qita

这样原本是可以直接删除这个字段的，但是由于这个字段有一个约束条件“default”，因此会提示（有约束条件DF_CangKuNew_qita_AAAAA）无法删除（没有约束条件就可以直接删除），想要删除这个字段，先删除这个约束。

alter table CangKuNew
drop constraint DF_CangKuNew_qita_AAAAA		//随便取的名字

删除约束条件后再执行删除字段的代码，就可以成功删除这个字段。
5）删除数据库文件

alter database Myfirstdb					//获得这个数据库的权限
remove file Mydatabase1_new_log

3.数据库表的删除

use Myfirstdb
drop table CangKuNew
//drop table CangKu1,CangKu2				//删除多张表

数据库表的数据操作

CangKu(cangkubh, cangkuhao, chengshi, mianji)
insert

insert into cangku(cangkubh, cahngkuhao, mianji) values (1, 'wh1', 580)		//城市字段为null
insert into cangku(cangkubh, cangkuhao, chengshi, mianji) values (2, 'wh2', null, 580)
insert into cangku values (3, 'wh3', null, 580)

如若有约束条件，在插入数据时候应当遵守约束条件。
update

update cangku set mianji = mianji + 1
update cangku set mainji = mainji + 10 where chengshi = '北京' or chengshi = '上海'

delete

delete from cangku where mianji < 600		//删除满足条件的记录
delete from cangku
truncate table cangku						//这两句都是删除数据库表中所有的记录