1、存储过程
概念： 我们将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，在服务器通过过程名来定义它们。
使用： 创建时就已经编译和优化，调用后相关信息就保存在内存中，下次使用时直接使用。
分类： 系统存储过程、用户自定义存储过程
优点：
（1）实现了模块化编程。
（2）存储过程具有对数据库立即访问的功能。
（3）使用存储过程可以加快程序的运行速度。
（4）使用存储过程可以减少网络流量。
（5）使用存储过程可以提高数据库的安全性。
系统存储过程：
a.存储过程保存在master数据库中，其前缀是SP_，它们为检索系统表的信息提供了方便快捷的方法。（？是否所有的存储过程都保存在master中）
b.本地存储过程名称前面没有前缀sp_
c.临时存储过程属于本地存储过程，在存储过程名称前加一个“#”，只能存在一个用户会话中，名称前加“##”其为全局存储过程，存在所有的用户会话中。
创建存储过程：
创建存储过程注意事项：（1）输入参数、输出参数
（2） sql语句
（3）返回给调用者的状态值
（4） 一个存储过程最大尺寸的为128 M
用T-sql 语句创建存储过程：
格式：create proc 存储过程名(
@name char(10) ,
@id int output
) as
Sql语句……
执行存储过程： exec 存储过程名
注意：有输出的存储过程运行时格式为，exexc 存储过程名 输入实参，输出实参output
存储过程的要点：
存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代
码存放在系统表syscomments中。
使用系统存储过程：
- sp_help name -- 参数name 为要查看的存储过程的名称
- sp_helptext name -- name （存~名）返回存储过程的源代码
- sp_stored_procedures –返回当前数据库的存储过程列表
- sp_rename a,b -- a为旧名，b为新名
删除存储过程： drop procedure name --name为存储过程名
2、 游标
- 为什么使用游标？
答：使用游标(光标)实现以逐行的形式集中处理数据，而无须将数据行的集合全部读到应用程序来处理。游标可在数据库服务器内部处理结果集。
- 游标分类
（1）根据处理特性：静态游标、动态游标、关键字游标(键集驱动游标 )。
（2）在结果集中移动方式：滚动游标（默认）和前向游标(FORWARD_ONLY)。
（3）结果集是否允许修改：只读游标和可写游标
- 创建游标步骤
a. 声明游标(declare)
b. 打开游标(open)
c. 存取游标(fetch)
d. 关闭游标(close)
e. 删除游标(deallocate)
- 创建游标格式
--声明游标
declare 游标名 cursor
for
--打开游标
open 游标名
--存取游标
fetch 游标
3、事务
- 什么是事务？
答：事务是用户定义的一个操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位(构成单一逻辑工作单元的操作集合)
- 事务的特点？（简称ACID）
答：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
- 事务分类？
答：
(1)显式事务：当明确键入 begin trans 和 commit 语句时，就会发生
（2）隐式事务：implicit_transactions on 语句，将隐性事务模式设置为打开
（3）自动提交事务：默认为此事务。
- 事务的工作方式？
答：
（1）创建（开始）事务： begin trans 事务名
（2）提交事务：commit 事务名
（3）回滚事务：rollback tran 事务名
（4）事务中设置保存点：save tran 保存点名（注意）保存点是指一个位置,此位置之前的事务语句不可回滚，即：有效的事务语句
- 事务例子1？（仅sql中）CHAP7
4、数据库的安全性
（1）概念？
答：是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。
（3）角色？
答：是向用户分配系统特权的主要方式。
（4）角色分类？
答：a.服务器角色：
Sysadmin：可以在 SQL Server 中执行任何活动。
Securityadmin：可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改 密码。
Serveradmin：可以设置服务器范围的配置选项，关闭服务器。
Setupadmin：可以管理链接服务器和启动过程。
Processadmin：可以管理在 SQL Server 中运行的进程。
Diskadmin：可以管理磁盘文件。
Dbcreator：可以创建、更改和除去数据库。
b.数据库角色{public 角色：其包含在每个数据库中，它不能被删除，每个用户都属于public角色
固定数据库角色：(每个数据库都拥有以下固定数据库角色)
db_owner：在数据库中有全部权限。
db_securityadmin：可以管理全部权限、对象所有权、角色和角色成员资格。
db_accessadmin：可以添加或删除用户 ID。
db_ddladmin：可以发出 ALL DDL，但不能发出 GRANT、REVOKE 或 DENY 语句。
db_backupoperator ：可以发出 DBCC、CHECKPOINT 和 BACKUP 语句。
db_datareader ：可以选择数据库内任何用户表中的所有数据。
db_denydatawriter ：不能更改数据库内任何用户表中的任何数据。
db_denydatareader：不能选择数据库内任何用户表中的任何数据。
5、范式
第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式是在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → → "的决定关系，则传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x → 非关键字段y
简言之数据库五大范式：
第一范式：对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.
（第一范式是通过把重复的组放到每个独立的表中，把这些表通过一对多关联联系起来这种方式来消除重复组的）
第二范式：第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.（第二范式处理冗余数据的删除问题。当某张表中的信息依赖于该表中其它的不是主键部分的列的时候，通常会违反第二范式）
第三范式：第三范式要求非主键列互不依赖.（第三范式规则查找以消除没有直接依赖于第一范式和第二范式形成的表的主键的属性。我们为没有与表的主键关联的所有信息建立了一张新表。每张新表保存了来自源表的信息和它们所依赖的主键）
第四范式：第四范式禁止主键列和非主键列一对多关系不受约束
第五范式：第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余。
6、连接
左外连接：左外连接的查询结果集包括指定左表中的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有找到相匹配的行，则结果集中的右表的相对应位置为Null
右外连接：右外连接的查询结果集包括指定右表中的所有行，而不仅仅是连接列所匹配的行，如果右表的某行在左表中没有找到相匹配的行，则结果集中的左表的相对应位置为Null
7、索引
索引是对数据库表中一个或多个列的值进行排序的结构
索引的好处：有助于更快的获取信息
缺 点：占用磁盘空间，并且降低添加、删除和更新的速度。
聚、非异同：在聚集索引中，表中各行的物理数据与键值的逻辑顺序相同。表能包含一个聚集索引；非聚集索引，表中的各行的物理顺序与键值的逻辑顺序不匹配
8、视图
回答什么是视图，视图有什么好处，视图有哪些注意事项，创建视图，删除视图，重命名视图的语法是分别什么？在MS企业管理器中列出pubs库所有用户表的关系，并且用表连接查出author所对应的pub信息显示au_fname,au_lname,pub_name三列，并创建视图。
视图是一个虚拟的表，提供一个或多个表中一系列的访问；他是从一个或多个表中派生出来的虚拟对象；
好 处：简单的重复使用复杂的SQL查询语句
注意事项：只能用户查询，不宜增删改；
语法：创建视图：CREATE VIEW view_name AS 查询语句
删除视图：drop view view_name
重命名视图：SP-RENAME old_view_name，new_view_name
回答什么是子查询。查看联机丛书了解子查询中都使用哪些关键字，分别是什么意思？解释in关键字和exists的特点和区别
子查询：在一个查询语句中使用另一个查询语句的结果
关键字 In：确定股、给定的值是否与子查询或链表中的值相匹配
Not in：通过not in关键字引入的子查询也返回一列0值或更多值
Exists：使用exists引入一个子查询时，就相当于进行一次存在测试，外部查询的where语句测 试子查询返回的行是否存在。子查询实际上不返回任何数据，它只返回turn或FALSE值；
9、什么是触发器，作用，优点
触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行
触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。
作用：主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。强化约束：触发器能够实现比check语句更为复杂的约束。跟踪变化：可以侦测数据库内的操作，从而不允许数据库中未经允许的指定更新和变化。级联运行：可以侦测到数据库内的操作，并自动地级联影响整个数据库的各项操作，该操作又导致该表上触发器被触发。存储过程的调用：为了响应数据库的更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用。
优点：触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。
SQL和T-SQL语句
1.按姓氏笔画排
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同
3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)
4.查看硬盘分区:
EXEC master..xp_fixeddrives
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
8.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
10：获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13：查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
Select * From TableName Order By CustomerName
1. 查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name'
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock
进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法:
set statistics time ...{on | off}
set statistics io ...{on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all ...{on | off}
set showplan_text ...{ on | off }
set statistics profile ...{ on | off }
13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，
以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。
修复完成后，请备份数据库。
repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。
这些修复可以很快完成，并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。
执行这些修复时不会有丢失数据的危险。
理解备份策略
备份策略是用户根据数据库运行的业务特点，制定的备份类型的组合。例如对一般的事务性数据库，使用“完整备份”加“差异备份”类型的组合，当然还要选择适当的“恢复模式”。下面提供了几种参考策略，主要包括“完全数据库备份策略”、“数据库和事务日志备份策略”、“差异备份策略”和“文件或文件组备份策略”。
1．完全数据库备份策略
完全数据库备份策略是定期执行数据库的“完整备份”、备份数据只依赖于“备份完整”。例如，定期修改数据的小型数据库，每天下午进行数据的少量修改，可以在每天18:00进行数据库的完整备份。完全数据库备份策略适用于以下情况。
► 如果数据库数据量小，总的备份时间是可以接受的。
► 如果数据库数据仅有很少的变化或数据库是只读的。
注意：如果使用“完整恢复模式”的数据库选项，用户应该定期清除事务日志。如果用户实现了完全数据库备份策略，数据库被配置使用完整、大容量日志模式，事务日志会被填充。当事务日志变满时，SQL Server 2005可能阻止数据库活动，直到事务日志被清空。如果用户设置数据库恢复模式为简单模式，则这样的问题将会减少。
2．数据库和事务日志备份策略
当数据库要求较严格的可恢复性，而由于时间和效率的原因，仅通过使用数据库的完整备份实现这样可恢复性并不可行时，可以考虑使用数据库加事务日志备份策略。即在数据库完整备份的基础上，增加事务日志备份，以记录全部数据库的活动。
当数据库实现数据库和事务日志备份策略时，用户应备份从最近的数据库完整备份开始，使用事务日志备份。数据库实现数据和事务日志备份策略一般用于经常进行修改操作的数据库上。
3．差异备份策略
差异备份策略包括执行常规的数据库“完整备份”加“差异备份”，并且可以在完整备份和差异备份中间执行事务日志备份。恢复数据库的过程则为，首先恢复数据库的“完整备份”，其次是最新一次的“差异备份”，最后执行最新一次“差异备份”以后的每一个“事务日志备份”。该策略在日常工作中被大量使用。差异备份策略一般用于以下备份需求的数据库。
► 数据库变化比较频繁。
► 备份数据库的时间尽可能短。
4．文件或文件组备份策略
文件或文件组备份策略主要包含备份单个文件或文件组的操作。通常这类策略用于备份读写文件组。备份文件和文件组期间，通常要备份事务日志，以保证数据库的可用性。这种策略虽然灵活，但是管理起来比较复杂，SQL Server 2005不能自动地维护文件关系的完整性。使用文件或文件组策略通常在数据库非常庞大，完整备份耗时太长的情况下使用。
6485
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
