创建和维护表

数据库

设计表考虑因素

1. 主要作用是什么，包含哪些数据，数据又分别属于哪些实体对象，对象之间有存在什么样的关系
2. E-R模型：将所有要存入数据库的数据归类，整理成一个个的分类，这个分类被称为实体，而归与这个分类的数据就为实体的属性。
3. 什么样的列用什么样的数据类型
4. 允许为空和默认值。尽量少用允许队列为空，如果允许也要尽量靠后。 将用户一般不填写的资料单独放在详细资料表
5. 主键
6. 约束和规则。
7. 外键关系
8. 考虑是否使用索引

E-R模型

实体（entity）:用矩形表示，矩形框内写明实体名。如果是弱实体的话，在矩形外面再套实线矩形。
属性（attribute）：用椭圆形表示，并用无向边将其与相应的实体连接起来。如果是多值属性的话，在椭圆形外面再套实线椭圆。
联系（relationship）：用菱形表示，框内写明联系名，并用无向边分别与有关实体链接起来，同时在无向边旁标上联系的类型。

外键：用于建立和加强两个表数据之间链接的一列或多列。通过将保存表中主键值的一列或多列中的值添加到另一个表中，可创建两个表之间的链接。

联系

一对一关联：表示某种实体实例仅和另一个实体实例关联。人和身份证
一对多关联：另一个班级实体中可以包含多个学生实体，但是一个学生只能在一个班级中。 N：1
一个表的主键对应另一个表的非主键，主键的值是不能重复的，而非主键是可以重复的
多对多关联：两个主键都是一个类，而不是具体

关系规范化

满足第三范式必须满足第二范式，满足第二范式前必须满足第一范式。

第一范式：1NF 所有属性是不可分割的原子值

• 每行数据的一个属性就只能包含一个值，而这个值一旦分割的话则不再有他应有的意义。
• 多行数据中的同一属性包含的值数量必须一样多
• 多个属性的意义不能相同

不要在数据库中使用中文做表名和列名

第二范式：2NF，非主属性非部分依赖于主关键字

第三范式：3NF，要求一个数据库表中不包含已在其他表红包含的非关键字信息

表的基本特点和类型

表（table）：也称实体，是存储同一类数据的集合
列（field）：也称字段、域或者属性，它构成表的架构，具体表示为一条信息中的一个属性。
行（row）：也称元组（tuple），存储具体的一条数据
码（key）：也称主键，是一个独一无二的字符，代表当前这条数据的标识
外键：关系，代表一条信息与其他信息之间的关联。

表的类型

普通表：最基本、最重要的表
分区表：将数据水平划分为多个的单元的表，这些单元可以分布到数据库中的多个文件组中国，实现对单元中数据的并行访问。
临时表：不能永久保存的表。本地临时表和全局临时表。
系统表：存储有关sql server服务器的配置，数据库的设置，用户，架构等信息。

创建和修改表

创建普通表

create table 表名
( 列 数据类型 约束或者默认值
, 列 数据类型 约束或者默认值
, ...
)

系统会为null列的每一行分配一个额外的字节。

use student_manager
go
create table orders
( id int not null
, product varchar(50) not null
,number int not null
,price money not null
, stock as number*price persisted
)
go
insert into orders(id,product,number,price)
            values(2,'小明',12,2000)
insert into orders(id,product,number,price)
values(3,'小红',10,300)
go
select * from orders
go

创建临时表

临时表分为本地临时表和全局临时表，创建普通表唯一的区别在于多加了“#”号，本地临时表加一个“#”，如#student，全局临时表加两个##。
临时表一直存在直到断开链接。

创建分区表

分区：为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。

增加和删除列

增加列

use student_manager
go
alter table students
add
s_address varchar(60) null
,s_zip varchar(10)
exec sp_help students

删除列

use student_manager
go
alter table students
drop column
s_address,s_zip
exec sp_help students

修改列

sp_rename '表名.列名','新列名'

修改数据类型

alter table 表名
 alter colume 列名 新的数据类型

修改数据类型中，要清楚数据类型之家的转换是否兼容

创建和修改列标识符

sql server有两种方式可以创建标识符，第一种是identity，另外一种是uniqueidentifier

经常进行删除操作的表中存在标识符列，标识值之间可能会出现断缺，已删除的标识符不再重新使用。

删除表

drop table 表名

删除的表实体，删除全部数据和还保留表结构。
如果有外键约束，表删不掉

约束

三大完整性：

• 实体完整性：实体属性中的标识属性不能为空、不能重复，该约束通 过制定的主键实现，其约束有系统强制实施
• 参照完整性：实体中的外键可以为空，但不能为错。
• 用户定义完整性：设计数据库的时候用户定义了某一行不能为空

主键约束

通过定义primarykey约束来创建主键
创建有两种方式：一种是直接对字段加primarykey关键字；令外一种是通过额外加约束的方式
不能对text image类型加上primarykey属性。
约束的名称不能重复，一般部位约束指定命名的话，系统会自动生成。
指定联合主键的列最多有16个
单列指定primarykey关键字后，该列数据不能为空，不能重复。
联合主键

 create table test3
 ( id int identity(1,1)
 ,name varchar(30) not null
 constraint test_key primary key(id,name)
 )

外键约束

外键是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可以定义foreign key约束来创建外键。

NOT NULL约束

大部分情况不允许为空。

default约束

u_regdate datatime default(getdate())

定义的常量必须与该列的数据类型、精度等匹配
每个列只能定义一个default约束
default约束只能应用于insert语句
default约束不能与identity属性列重复定义在一个列上
default约束允许的系统函数包括system_user getdate 和current_user

check约束

用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。
第一种形式

constraint 约束名 check(logical_expression)

第二种形式

check(logical_expression)

unique约束

指定表中某一列或多个列不能有相同的两行或者两行以上的数据存在，
通过实现唯一性索引来强制实体完整性。

unique
unique(列，列...)
constraint 约束名 unique
constraint 约束名 unique(列，列...)

禁止与删除约束

只能禁止check约束和外键约束应用到表上，如唯一性和主键则不行。
已经存在的数据如果以后都会再改变，禁用是可以的，如果使用update更新的话必须满足约束。
一般不建议uzo，先检查数据，如果能够修改还是尽量修改数据。

--关闭约束检测
nocheck constraint 约束名
nocheck constraint all
--开启约束检测
check constraint 约束名
check constraint all

删除约束

alter table user
drop
    constraint sex,age
go