北大青鸟S2sql数据库设计和高级查询总结.doc

第一章 数据库的设计良好的数据库设计1、节省数据库的存储空间2、能够保证数据的完整性3、方便进行数据库应用系统的开发糟糕的数据库设计1、效率低下2、更新和检索数据时会出现许多问题设计数据库的步骤1、需求分析阶段 分析客户的业务和数据处理需求 收集信息 标识对象(实体) 标识每个对象需要存储的详细信息 标识对象之间的关系2、概要设计阶段 绘制数据库的E-R模型图(实体关系图)3、详细设计阶段 将E-R图转换为多张表,进行逻辑设计,并应用数据库设计的三大规范进行设计4、代码编写阶段映射基数1一对一2一对多3多对一4多对多实体关系图矩形表示实体集椭圆形表示属性菱形表示关系集直线用来连接设计数据库问题1 信息重复2 更新异常3 插入异常(无法表示某些信息)4 删除异常(丢失有用的信息)数据库设计的三大规范理论一 第一范式的目标是确保 每列的原子性二 第二范式的目标是确保 表中的每列都和主键相关三 第三范式的目标是确保 每列都和主键直接相关,而不是间接相关(不能传递依赖)规范化和性能的关系在必要的情况下 允许适当的数据冗余第二章 数据库的实现一 建库ues master goif exists (select * from sysdatabases where name =数据库名字)drop database 数据库名 -判断这个数据库名字是否存在,如果存在 删除create database 数据库名on primary( name=, filename=, size =, 建立数据库的主文件 如果需要建立次要文件(ndf) 用逗号隔开 maxsize =, filegrowth =)log on ( name =, filename=, size =, 建立日志文件 如果建立多个日志文件 在后面用逗号隔开 maxsize =, filegrowth =)go - 批处理 二 建表ues 数据库名字go if exists (select * from sysobjects where name =表名)drop table 表名 -判断是否有这个名字的表create table 表名( 字段名 数据类型 列的特征如 stuid int identity(1,1) not null -identity 表示是否为自动增长)go三 加约束1 主键约束(primary key constraint) -constraint 约束 alter table 表名 add constraint 约束名(PK_*) primary key(约束的字段)2 唯一约束(unique constraint) alter table 表名 add constraint 约束名(uq_*) unique(约束的字段)3 检查约束(check constraint) alter table 表名 add constraint 约束名(ck_*) check(约束的条件)4 默认约束(default constraint) alter table 表名 add constraint 约束名(df_*) default(默认值) for 约束的字段5 外建约束(foreign key constraint) alter table 表名 add constraint 约束名(fk_*) foreign key(约束的字段) references 主表的表名(主表的字段)总结create database 建库create table 建表add constraint 加约束drop database 删库drop table 删表drop constraint 删约束 use master select * from sysdatabases 判断是否有库名select * from sysobjects 判断是否有表名完整性1 实体完整性(保证数据是唯一的如主键唯一键标示列)2 引用完整性(保证两表数据一致如外键)3 域完整性(保证数据的准确性如检查约束默认约束非空)三层安全模型1 登陆帐号- 决定登陆服务 windows 身份验证 exec sp_grantlogin windows域名域帐户 sql 身份验证 exec sp_addlogin 帐户名,密码2 数据库用户-访问数据库 use 数据库名 go exec sp_grantdbaccess 登陆帐户,(数据库用户) 如果不写数据库用户默认为登陆帐号名3 权限 -在数据库里的操作 use 数据库名 go grant 权限(增,删,改,查,建表(create table) on 表名 to 用户名 第三章 T-SQL编成使用变量一局部变量 declare 变量名 类型 赋值: 1、 set 变量名 = 值 2、 select 变量名 =值二全局变量 error 最后一个T-SQL错误的错误号 identity 最后一次插入的标示值 language 当前使用的语言的名称 max_connections 可以创建的同时连接的最大数目 rowcount 受上一个sql语句影响的行数 servername 本地服务器的名称 servicename 该计算机上的sql服务的名称 timeticks 当前计算机上每刻度的微秒数 transcount 当前连接打开的事物数 version sql server 的版本信息输出语句 1、print 局部变量或字符串 2、select 局部变量 as 自定义列名(查询语句的特殊应用) 逻辑控制语句1、if(条件) begin - 开头 语句 end -结束else -为可选 begin - 开头 语句 end -结束2、while (条件) 语句 break3、case when 条件1 then 结果1 when 条件2 then 结果2 else 其他结果end 批处理语句 以一条命令的方式来处理一组命令的过程称为批处理批处理的好处就是能够简化数据库的管理第四章 高级查询一 简单子查询select * from 表1 where 字段1 (子查询) 它等于一个等值内连接将子查询和比较运算符联合使用，必须保证子查询返回的值不能多与一个二 in 和 not in 子查询in 后面的子查询可以返回多条记录select * from 表1 where 字段1 in(not in)(子查询)三 exists not exists子查询 if exists (子查询) 语句 如果子查询的结果非空，则exists(子查询) 将返回真(true) ,否则返回假(false)第五章 事务、索引和试图一 事务：是一个整体，要么都成功，要么都失败事务时作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性1 原子性(atomicity) 事务是一个完整的操作。事务的各元素是不可分得2 一致性(consistency) 当事务完成时，数据必须处于一致状态3 隔离性(isolation) 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的， 它不应以任何方式依赖于或影响其它事务4 持久性(durability) 事务完成之后，它对系统的影响是永久的简称 ACID二 如何创建事务开始事务：begin transaction提交事务：commit transaction回滚(撤销)事务： rollback transaction事务的分类有3种1 显示事务: 用 begin transaction 明确指定事务的开始2 隐式事务: 通过设置 set implicit_transaction on 语句,将隐式事务模式设置为打开3 自动提交事务: 这是sql server 的默认模式 它将每条单独的T-SQL语句视为一个事务. 如果成功执行,则自动提交.如果错误,则自动回滚.在这里用的了全局变量errordeclare errorsum int set errorsum =0 语 句 set errorsum =errorsum+error三 什么是索引索引,它是sql server编排数据的内部方法,相当于字典中的目录索引页,数据库中存储索引的数据页 通过索引可以大大提高数据库的检索速度,改善数据库性能 加快查询的速度(通过平衡二叉树) 增删改 速度慢,所需要的空间大索引可分为3类1 唯一索引: 唯一索引不允许两行具有相同的索引值. 创建了唯一约束,将自动创建唯一索引,为了最佳性能,建议使用主键的约束2 主键索引: 在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型.3 聚集索引: 在聚集索引中,表中各行的物理顺序与健值的逻辑(索引)顺序相同. 非聚集索引: 表中各行的物理顺序与键值的逻辑顺序不匹配.在sql server中,一个表只能 创建一个聚集索引,但可以有多个非聚集索引,设置某列为主键,该列就默认为聚集索引四 建立索引create uniqueclusterednonclustered index ix_(name)on 表名 (字段名)with fillfactor =30 1 unique 指定唯一索引 可选2 clustered nonclustered 指定是聚集索引还是非聚集索引,可选3 fillfactor 表示填充因子,指定0-100的值,该值指示索引页填满的空间所占的比例 (百分比越小,所留得空白空间越大)什么情况下可以建立索引1 该列用于频繁搜索2 该列用于对数据进行排序 请不要对下面的列创建索引1 列中仅包含几个不同的值2 表中仅包含几行.五 什么是试图试图使另一种查看数据库中一个或多个表中的数据的方法.它是一种虚拟表试图通常用来进行以下三种操作1 筛选表中的行2 防止未经许可的用户访问敏感数据3 将多个物理数据表抽象为一个逻辑数据表 (降低数据库的复杂程度)好处1对最终用户的好处 结果更容易理解 获得数据更容易2对开发人员的好处 限制数据检索更容易 维护应用程序更方便六 如何创建试图create view view_(name)as (select 语句) 第六章 存储过程一 什么是存储过程 存储过程(procedure)类似于java语言中的方法，它是sql语句和控制流语句的预编译集合。 存储过程的优点 1 允许模块化程序设计 2 允许更快地执行 3 减少网络流量 4 可作为安全机制使用 存储过程分为以下两类 1 系统存储过程 2 用户自定义的存储过程 3 扩展存储过程(系统定义好的，以xp_开头)二 常用的系统存储过程 所有的系统存储过程的名称都以sp_开头 sp_databases (列出服务器上的所有数据库) sp_helpdb (报告有关指定数据库或所有数据库的信息) sp_renamedb (更改数据库的名称) sp_tables (返回当前环境下可查询的对象的列表) sp_columns (返回某个表列的信息) sp_help (查看某个表的所有信息) sp_helpconstraint (查看某个表的约束) sp_stored_procedures (列出当前环境中的所有存储过程) sp_password (添加或修改登陆帐号的密码) sp_helptext (显示默认值、未加密的存储过程、用户定义的存储过程、触发器或试图的实际文本) 扩展参数 exec xp_cmdshell dos命令 no_output三 用户定义的存储过程1 创建不带参数的存储过程 create procedure 存储过程名 参数1 数据类型 默认值 output 参数2 数据类型 默认值 output as sql 语句2 创建步带参数的存储过程 1 输入参数 可以在调用时向存储过程传递参数，此类参数可以用来在存储过程中传入值 2 输出参数(引用传递) 如果希望返回值，则可以使用输出参数，输出参数后有output标记，执行存储过程后， 将把返回值存放在输出参数中，可供其它T-SQL语句读取访问3 创建带输出参数的存储过程 如果希望调用存储过程后，返回一个或多个值，这时需要使用输出(output)参数。 使用输出参数创建存储过程时，在参数后面需要跟随output关键字，调用时也需要在变量后跟随output关键字四 处理错误信息 如果存储过程变得越来越复杂，则需要在存储过程中加入错误检查语句 raiserror (msg_id|msg_str,severity,statewith op