SQLServer设计和实现.doc

数据库设计和实现笔记一、回顾：数据库基本概念建库建表完整性约束函数增删改查的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 第三范式满足第二范式，且除了主键外，其它列都不传递依赖于主键，则满足第三范式如：订单表(订单编号，订购日期，顾客姓名，顾客编号。)顾客姓名和顾客编号相关，而顾客编号和订单编号相关，即传递依赖于订单编号，所以应该把顾客姓名列删除。五、规范和性能的关系：没有最好，只有最合适的。并非越规范越好，也不是只考虑性能，一般在规范和性能间进行一定的取舍。六、数据库的实现：l T-SQL语句回顾insertdeleteupdateselectl 使用SQL语句创建和删除数据库数据库文件的组成：数据文件和日志文件数据文件和日志文件的扩展名？对这两个文件都可以设置文件初始大小，增长方式等。创建数据库的语法：create database 数据库名称 on primary(name=逻辑文件名，filename=物理文件名，size=大小，maxsize=最大容量，filegrowth=增长量)log on(同上)GO删除数据库：一般创建数据库之前，应该先检测当前服务器中是否存在同名的数据库，如果存在则先删除后再创建数据库。删除数据库：drop database 数据库名称如何检测呢？SQL Server中数据库清单放在系统数据库master的sysdatabases表中，检测语句：use mastergoif exists(select * from sysdatabases where name=你要创建的数据库名称)drop database你要创建的数据库名称create database你要创建的数据库名称on()()gol 使用SQL语句创建和删除表建表的步骤：1. 确定表中的列2. 确定每列的数据类型3. 添加约束4. 添加关系创建表的语法：create table 表名(列名 数据类型 列的特征，列名 数据类型 列的特征，。)列的特征是指：是否为空，是否标识列，是否默认值，是否主键等。删除表：先检测，有则删除，然后建立表某个数据库中表的清单在该数据库里的系统表sysobjects中use 数据库1goif exists(select * from sysobjects where name=你要创建的表名)drop table 你要创建的表名create table你要创建的表名()l 使用SQL语句创建和删除约束约束的目的：确保表中数据的完整型常用的约束类型：1. 主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空2. 唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。3. 检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束4. 默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”5. 外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列约束名的取名规则推荐采用：约束类型_约束字段1. 主键（Primary Key）约束：如 PK_stuNo2. 唯一（Unique Key）约束：如 UQ_stuID3. 默认（Default Key）约束：如 DF_stuAddress4. 检查（Check Key）约束：如 CK_stuAge5. 外键(Foreign Key)约束：如 FK_stuNo 添加约束的语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明示例：ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)ALTER TABLE stuInfo ADD CONSTRAINT UQ_stuID UNIQUE (stuID)ALTER TABLE stuInfo ADD CONSTRAINT DF_stuAddress DEFAULT (地址不详) FOR stuAddressALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO删除约束的语法：ALTER TABLE 表名 DROP CONSTRAINT 约束名 l 使用SQL语句创建登陆第一步：创建登陆用户EXEC sp_addlogin zhangsan, 1234第二步：创建数据库用户创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：EXEC sp_grantdbaccess 登录帐户名，数据库用户名 其中，“数据库用户“为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。第三步：向数据库用户授权 授权的语法为：GRANT 权限 ON 表名 TO 数据库用户示例：USE stuDBGO/*-为zhangsanDBUser分配对表stuInfo的select, insert, update权限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-为S26301DBUser分配建表的权限-*/GRANT create table TO S26301DBUser七、T-SQL编程l 使用变量：T-SQL中的变量分为局部变量和全局变量，局部变量和很多编程语言一样，先声明，再使用；而T-SQL的全局变量由系统定义和维护，我们只能直接使用，一般不自定义全局变量。1. 局部变量：局部变量的命名：必须以最为前缀语法：declare 变量名 数据类型局部变量的赋值语法：set 变量名=值select 变量名=值注意：select赋值一般是用于从数据库表中查询数据，然后赋值给变量，select语句需要保证结果记录不多于一条，如果多于，则把最后一条记录赋值。2. 全局变量：SQL Server中的全局变量都以为前缀。变量含义ERROR最后一个T-SQL错误的错误号IDENTITY最后一次插入的标识值LANGUAGE当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目ROWCOUNT受上一个SQL语句影响的行数SERVERNAME本地服务器的名称TRANSCOUNT当前连接打开的事务数VERSIONSQL Server的版本信息l 输出语句：语法：print 局部变量或者字符串select 局部变量 as 自定义列名l 逻辑控制语句：1. IF-ELSE语法：if(条件)语句else语句或者if(条件)begin语句endelsebegin语句end2. WHILE语法：while(条件)begin语句breakend3. CASE语法：casewhen 条件1 then 结果1when 条件2 then 结果2。else 结果endl 批处理语句：将一条或者多条SQL语句编译成一个可执行单元称为执行计划，从而提高执行效率。方法：在一条或多条语句最后加GO即可。SQL Server规定：建库，建表，存储过程，视图必须在语句最后添加GO八、高级查询：l 简单子查询l IN和NOT in子查询l EXISTS和NOT EXISTS子查询l T-SQL的综合应用九、事务、索引和视图：l 事务begin transaction事务的代码，一般多于1句没有任何一句出错(error的累加结果为0)提交 commit transaction否则回滚 rollback transaction示例declare error intset error=0begin transaction-从张三卡里转出 updateset error = error + error-给李四卡里加 updateset error = error + errorif(error0)beginrollback transactionendelsebegincommit transactionendl 索引：create index ix_password on users(password)l 视图：虚拟的表create view vw_teacher asselect userid,username from users十、存储过程：l 什么是存储过程：函数l 常用的系统存储过程l 用户定义的存储过程十一、触发器一 触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。 二 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触发器。四触发器的执行过程如果一个Insertupdate或者delete语句违反了约束那幺After触发器不会执行因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。 Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立其它任何操作还没有发