SQLServer设计和实现

数据库设计和实现笔记

一、回顾：

数据库基本概念

建库建表

完整性约束

函数

增删改查的SQL语句

聚合函数

表连接查询

二、数据库设计：

数据库设计：规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程。

为什么要规范设计？

如果需要保存和处理的信息只有十列八列，使用我们前面的知识就可以直观的进行表的设计，即使不同的人设计的结果不同，但一般也会在可承受的范围内，随着软件的工程化，如果需要处理的信息有两千列，该如何进行表的设计来存储和处理这些信息，仅仅凭经验或者直观感受恐怕难以实现，正如其他很多成熟的行业一样，这里面是有规矩的。按照规范设计的步骤进行设计，对操作它的编程代码来说，会更容易，更易读，易扩展。

设计数据库的步骤

1．需求分析：分析客户的业务和数据处理需求

2．概要设计：绘制E-R图，用于项目团队内部，设计人员和客户之间进行沟通，以确认信息的完整性。

3．详细设计：将E-R图转换为多张表

三、E-R图

实体-关系图：是数据库设计的图纸

1．实体：名词，对应表中的一行数据，图中表示为矩形

2．属性：实体的特性，图中表示为椭圆

3．关系：两个或者多个实体之间的联系，图中表示为菱形

4．映射基数：

一对一，一对多，多对一，多对多

Q:如何将E-R图转换为表？

Ans:

1.将各实体转换为对应的表，将各属性转换为各表的列

2.标识每个表的主键，没有主键的添加标识列

3.实现表之间的映射关系

四、数据规范化：

其实就是规范E-R图的过程。

不合理的设计会出现：

1、信息重复

2、更新异常

3、插入异常

4、删除异常

规范的设计：

1．第一范式

目标：确保每列的原子性，即每列都是不可再分的最小数据单元

如：地址可分为省和市

2．第二范式

满足第一范式，且除了主键外的其它列，都依赖于该主键，则满足第二范式如：

订单表(订单编号，产品编号，订购日期，价格。。。。。。)

其它列都和订单编号相关，而产品编号和订单编号无关，所以应该把产品编号删除。

3．第三范式

满足第二范式，且除了主键外，其它列都不传递依赖于主键，则满足第三范式

如：

订单表(订单编号，订购日期，顾客姓名，顾客编号。。。。。。)

顾客姓名和顾客编号相关，而顾客编号和订单编号相关，即传递依赖于订单编号，所以应该把顾客姓名列删除。

五、规范和性能的关系：

没有最好，只有最合适的。

并非越规范越好，也不是只考虑性能，一般在规范和性能间进行一定的取舍。

六、数据库的实现：

●T-SQL语句回顾

insert

delete

update

select

●使用SQL语句创建和删除数据库

数据库文件的组成：数据文件和日志文件

数据文件和日志文件的扩展名？

对这两个文件都可以设置文件初始大小，增长方式等。

创建数据库的语法：

create database 数据库名称on [primary]

([name=逻辑文件名，]

filename=物理文件名

[，size=大小]

[，maxsize=最大容量]

[，filegrowth=增长量])

[log on]

(

同上

)

GO

删除数据库：

一般创建数据库之前，应该先检测当前服务器中是否存在同名的数据库，如果存在则先删除后再创建数据库。

删除数据库：

drop database 数据库名称

如何检测呢？

SQL Server中数据库清单放在系统数据库master的sysdatabases表中，检测语句：

use master

go

if exists(select * from sysdatabases where name=?你要创建的数据库名称?) drop database你要创建的数据库名称

create database你要创建的数据库名称

on

()

()

go

●使用SQL语句创建和删除表

建表的步骤：

1.确定表中的列

2.确定每列的数据类型

3.添加约束

4.添加关系

创建表的语法：

create table 表名

(

列名数据类型列的特征，

列名数据类型列的特征，

。。。。。。

)

列的特征是指：是否为空，是否标识列，是否默认值，是否主键等。

删除表：先检测，有则删除，然后建立表

某个数据库中表的清单在该数据库里的系统表sysobjects中

use 数据库1

go

if exists(select * from sysobjects wher e name=?你要创建的表名?)

drop table 你要创建的表名

create table你要创建的表名

(

……

)

●使用SQL语句创建和删除约束

约束的目的：确保表中数据的完整型

常用的约束类型：

1.主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许

为空

2.唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现

一个空值。

3.检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关

年龄的约束

4.默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，

性别默认为“男”

5.外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引

用主表的那列

约束名的取名规则推荐采用：约束类型_约束字段

1.主键（Primary Key）约束：如PK_stuNo

2.唯一（Unique Key）约束：如UQ_stuID

3.默认（Default Key）约束：如DF_stuAddress

4.检查（Check Key）约束：如CK_stuAge

5.外键(Foreign Key)约束：如FK_stuNo

添加约束的语法：

ALTER TABLE 表名

ADD CONSTRAINT 约束名约束类型具体的约束说明

ALTER TABLE 表名

DROP CONSTRAINT 约束名

使用SQL语句创建登陆

第一步：创建登陆用户

EXEC sp_addlogin …zhangsan', …1234?

第二步：创建数据库用户

创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：

EXEC sp_grantdbaccess …登录帐户名?，?数据库用户名?

其中，“数据库用户“为可选参数，默认为登录帐户，

即数据库用户默认和登录帐户同名。

第三步：向数据库用户授权

授权的语法为：

GRANT 权限[ON 表名] TO 数据库用户

使用变量：

T-SQL中的变量分为局部变量和全局变量，局部变量和很多编程语言一样，先声明，再使用；而T-SQL的全局变量由系统定义和维护，我们只能直接使用，一般不自定义全局变量。

1.局部变量：

局部变量的命名：必须以@最为前缀

语法：

declare @变量名数据类型

局部变量的赋值语法：

set @变量名=值

select @变量名=值

注意：select赋值一般是用于从数据库表中查询数据，然后赋值给变量，select语句需要保证结果记录不多于一条，如果多于，则把最后一条记录

赋值。

2.全局变量：

●输出语句：

语法：

print 局部变量或者字符串

select 局部变量as 自定义列名

●逻辑控制语句：

1.IF-ELSE

2.WHILE

3.

●批处理语句：

将一条或者多条SQL语句编译成一个可执行单元称为执行计划，从而提高执行效率。

方法：在一条或多条语句最后加GO即可。

SQL Server规定：建库，建表，存储过程，视图必须在语句最后添加GO

八、高级查询：

●简单子查询

●IN和NOT in子查询

●EXISTS和NOT EXISTS子查询

●T-SQL的综合应用

九、事务、索引和视图：

●事务

begin transaction

事务的代码，一般多于1句

没有任何一句出错(@@error的累加结果为0)

提交commit transaction

否则

回滚rollback transaction

●索引：

create index ix_password on users(password)

●视图：

虚拟的表

create view vw_teacher as

select userid,username from users

十、存储过程：

●什么是存储过程：函数

●常用的系统存储过程

●用户定义的存储过程

十一、触发器

一﹕触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。

二﹕SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后﹐与该触发器相关的这两个表也被删除。

Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

三﹕Instead of 和After触发器

SQL Server2000提供了两种触发器﹕Instead of 和After 触发器。这两种触发器的差别在于他们被激活的同﹕

Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外﹐Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。

After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。

一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。

四﹕触发器的执行过程

如果一个Insert﹑update或者delete语句违反了约束﹐那幺After触发器不会执行﹐因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。

Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立﹐其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行﹐所以它可以对约束进行一些预处理。

五﹕使用T-SQL语句来创建触发器

基本语句如下﹕

create trigger trigger_name

on {table_name | view_name}

{for | After | Instead of }

[ insert, update,delete ]

as

sql_statement

示例：

create trigger insert_trigger

on users

for insert

as

update usercount set nums=nums+1