北大青鸟S2sql数据库设计和高级查询总结
第一章 数据库的设计良好的数据库设计1、节省数据库的存储空间2、能够保证数据的完整性3、方便进行数据库应用系统的开发糟糕的数据库设计1、效率低下2、更新和检索数据时会出现许多问题设计数据库的步骤1、需
第一章 数据库的设计
良好的数据库设计
1、节省数据库的存储空间
2、能够保证数据的完整性
3、方便进行数据库应用系统的开发
糟糕的数据库设计
1、效率低下
2、更新和检索数据时会出现许多问题
设计数据库的步骤
1、需求分析阶段 分析客户的业务和数据处理需求
⑴ 收集信息
⑵ 标识对象(实体)
⑶ 标识每个对象需要存储的详细信息
⑷ 标识对象之间的关系
2、概要设计阶段 绘制数据库的E-R 模型图(实体关系图)
3、详细设计阶段 将E-R 图转换为多张表, 进行逻辑设计, 并应用数据库设计的三大规范进行设计
4、代码编写阶段
映射基数
1一对一
2一对多
3多对一
4多对多
实体关系图
矩形表示实体集
椭圆形表示属性
菱形表示关系集
直线用来连接
设计数据库问题
1 信息重复
2 更新异常
3 插入异常(无法表示某些信息)
4 删除异常(丢失有用的信息)
数据库设计的三大规范理论
一 第一范式的目标是确保 每列的原子性
二 第二范式的目标是确保 表中的每列都和主键相关
三 第三范式的目标是确保 每列都和主键直接相关, 而不是间接相关(不能传递依赖)
,规范化和性能的关系
在必要的情况下 允许适当的数据冗余
第二章 数据库的实现
一 建库
ues master
go
if 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 (条件)
语句
[break]
,3、
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 语句视为一个事务. 如果成功执行, 则自动提交. 如果错误, 则自动回滚.
在这里用的了全局变量@@error
declare @errorsum int
set @errorsum =0
语 句
set @errorsum =@errorsum @@error
三 什么是索引
索引, 它是sql server编排数据的内部方法, 相当于字典中的目录
索引页, 数据库中存储索引的数据页
通过索引可以大大提高数据库的检索速度, 改善数据库性能
加快查询的速度(通过平衡二叉树)
增删改 速度慢, 所需要的空间大
索引可分为3类
1 唯一索引: 唯一索引不允许两行具有相同的索引值.
创建了唯一约束, 将自动创建唯一索引, 为了最佳性能, 建议使用主键的约束
2 主键索引: 在数据库关系图中为表定义一个主键将自动创建主键索引, 主键索引是唯一索引的特殊类型.
3 聚集索引: 在聚集索引中, 表中各行的物理顺序与健值的逻辑(索引) 顺序相同.
非聚集索引: 表中各行的物理顺序与键值的逻辑顺序不匹配.
在sql server 中, 一个表只能 创建一个聚集索引, 但可以有多个非聚集索引, 设置某列为主键, 该列就默认为聚集索引
四 建立索引
create [unique][clustered][nonclustered] 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 proc[edure] 存储过程名
[
@参数1 数据类型 [默认值] [output]
@参数2 数据类型 [默认值] [output]
]
as
sql 语句
2 创建步带参数的存储过程
1 输入参数
可以在调用时向存储过程传递参数，此类参数可以用来在存储过程中传入值 2 输出参数(引用传递)
如果希望返回值，则可以使用输出参数，输出参数后有'output' 标记，执行存储过程后， 将把返回值存放在输出参数中，可供其它T-SQL 语句读取访问
3 创建带输出参数的存储过程
如果希望调用存储过程后，返回一个或多个值，这时需要使用输出(output)参数。
使用输出参数创建存储过程时，在参数后面需要跟随'output' 关键字，调用时也需要在变量后跟随'output' 关键字
四 处理错误信息
如果存储过程变得越来越复杂，则需要在存储过程中加入错误检查语句
raiserror ({msg_id|msg_str}{,severity,state}[with option[,...n]])
例 raiserror('及格线错误，请指定0-100之间的分数，统计中断退出',16,1)
msg_id 在sysmessages 系统表中指定的用户定义错误信息
msg_str 用户定义的特定信息，最长255个字符
severity 与特定信息相关联，表示用户定义的严重性级别。用户可使用的级别为0-18级(一般下为16)
19-25级是为sysadmin 固定角色的成员预留的，并需要指定with log选项 20-25
,为致命错误
state 表示错误的状态，是1-127的值
option 指示是否将错误记录到服务器错误日至中