SQL SERVER 2005练习集2续--读书笔记

 

=====第17章==索引=========================================================================
use school
go
--为学生表创建索引
/*create index idx_student_id
on  student(id)
go
select * from student
where id=2006005
go*/
--创建聚集索引
语法：
  create clusted  index index_name
  on  {table_name|view_name}(column[ASC|DESC][,...n])
index_name:索引的名称。
table_name|view_name：要创建的索引的表或视图。
column：索引所基于的一列或多列。
ASC|DESC：索引是以哪种顺序来创建，ASC代表升序，DESC是降序。
--在 result表id列上创建聚集索引
/*create clustered index idx_result_id
on result(student_id asc)
go
--在 result表subject列上创建聚集索引
create clustered index idx_result_subject
on result(subject asc)
go*/
/*可以看出，result只能创建一个聚集的索引，表中的物理顺序与索引中的物理顺序都已固定，当再次创建一个
 聚集索引就会报错。*/
--非聚索引
--创建
/*create nonclustered index idx_result_subject
on result(subject asc)
go
create nonclustered index idx_result_grade
on result(grade asc)
go
--查看
exec sp_helpindex  result
go
--唯一性索引
--创建
create unique  nonclustered index idx_student_cardno
on student(cardno asc)
on students  --将索引存储在students文件组上
go
--再创建
create unique nonclustered index idx_student_teacher_id
on student(teacher_id asc)
on students
go
--创建了唯一性索引的列上的值必须是唯一的
--查询
select * from student
go
--输入一条新的学生信息(如果cardno上存在之前创建的唯一性约束，将其删除)
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006004,'Lily','女',23,2,1,103,2001,'尚未毕业','天津',220200030304304341)
go
--修改
update student
set cardno=220200030304304341
where id=2006004
--主键索引
--删除在result上创建的索引
drop index result.idx_result_id
go
drop index result.idx_result_subject
go
drop index result.idx_result_grade
go
--将表的列定义为非空
alter table result
alter column student_id  int  not null
go
alter  table result
alter column subject char(10) not null
go
--开始创建主键约束
alter table result
add constraint pk_result_student_id_subject primary key(student_id,subject)
go
--查看
exec sp_helpindex result
go*/
/*索引管理*/
--禁用索引
/*alter index idx_student_cardno
on student
disable
go
--exec  sp_helpindex student
--go
--启用
alter index idx_student_cardno
on student
rebuild
go*/
--索引碎片
******************索引碎片***************************
语法：
dbcc  showcontig
[(
{'table_name'|table_id|'view_name'|view_id}
[,'index_name'|index_id]
)]
[with
{
[,[all_indexes]]
[,[tableresults]]
[,[fast]]
[,[all_levels]]
[no_infomsgs]
}
]

'table_name'|table_id|'view_name'|view_id：为要检查碎片信息的表或视图。
'index_name'|index_id：要检查其碎片信息的索引。
fast：指定是否要对索引执行快速扫描和输出最少信息。
all_indexes：显示表和视图的所有索引的结果，即使指定了也如此。
tableresults：将结果显示含有附加信息的行。
all_levels：主要椒为保持向后兼容性而保留。
no_infomsgs：取消严重级别从0~10的所有信息性消息。

如果碎片太多可以用下面的方法来减少：
删除然后重新创建聚集索引：创建聚集索引将重新组织，使数据页面填满。
对索引的页级按逻辑顺序重新排序：使用ALTER INDEX...REORGANIZE，对索引的页逻辑顺序重新排序。
联机重新生成索引：使用REBUILD和ALTER INDEX。
*********************************************
--索引碎片
--dbcc showcontig (student)
--go
--全文索引
*****************文索引******************************
CREATE FULLTEXT  CATALOG catalog_name
[ON FILEGROUP filegroup]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name]
<catalog_option>::=
ACCENT_SENSITIVITY={ON|OFF}

catalog_name：新目录的名称。
ON FILEGROUP 'filegroup'：包含新目录的SQL SERVER文件组的名称。
IN PATH 'rootpath'：该目录的根目录。
********************************************
use  school
go
--在当前数据库中启用全文索引，如果不启用这个属性，则不能创建全文目录
/*exec sp_fulltext_database enable
go
create fulltext catalog  school_catalog
on filegroup students
go
--创建全文索引
--在student上添加address列，全文索引存储大的信息上
alter table student
add address varchar(200)
go
--为表中的数据输入信息
update student
set address='北京市西城区，西直门内大街134号楼705室'
where id=2006001
go
update student
set address='北京市西城区，西直门内大街134号楼708室'
where id=2006002
go
update student
set address='北京市海淀区，西直门内大街135号楼102室'
where id=2006003
go
--使用全文索引
select id as '学生编号',
[name] as '学生姓名',
address  as '学生地址'
from student
where freetext(address,'西城区')
--无法对 表或索引视图'student' 使用 CONTAINS 或 FREETEXT 谓词，因为它未编制全文索引。
go*/
--慎重使用/删除索引
　通过上面的学习，可以知道如何创建、使用索引，但是不能轻易地创建。
1、对数据量小的表不要创建索引，这样非但不会提高，反而会降低查询反问的速度；
2、创建索引的表列，经常要被反问，不能在不经常访问的表上创建索引；
3、如果经常访问的数据量超过总数的20%，则不需要创建索引。
4、不要过多的创建非聚集索引，这样会影响访问的速度。
5、要经常对索引进行重建。
6、在大批量数据导入或数据修改的时候，要先将索引禁用，之后再启用。
7、如果数据量过大(GB以上)，这个时候就没有必要创建了。

--删除索引
DROP INDEX index_name
ON <object>
<object>::=
{
[database_name.[schema_name].|schema_name.]
table_or_view_name
}

index_name是索引的名称，database_name就是数据库的名称，schema_name就是表的所有者，
table_or_view_name代表的是创建索引的表或者试图。
=====第18章==事务与锁================================================
***********************
事务分类：
自动提交事务：每条单独的语句都是一个事务。
显示事务：每个事务均以BEGIN RRANSACTION语句开始，以COMMIT或ROLLBACK结束。
隐性事务：在前一个事务完成时，新事务启动，但仍以COMMIT或ROLLBACK语句显示完成。
批处理级事务：只能应用于多个活动结果集(MARS)，在MARS会话中，启动的TRANSACT-SQL显示或隐性变　　　为批处理级事务。当批处理完成时，没有提交或回滚的事务将自动由SQL SERVER进行。

并发性问题：
丢失更新：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失问题。
未提交的依赖关系(脏读)：当第二个选择其他事务正在更新的行时，会发生未提交的依赖关系问题。
不一致的分析(不可重复读)：当第二个事务多次访问同一行，而且每次读取不同的数据时，会发生不一　　　　致的分析问题。
幻读：当对某行执行插入或删除操作，而该行属于某个事务正在读取的范围时，会发生幻读问题。
***********************
use school
go
/*新建查询1*/
--查看
/*select * from student
go
--修改
/*步骤1*/
begin transaction t1  --使用事务
update student
set name='Long'
where id=2006003
go
select * from student
go*/
/*步骤3*/
rollback
go
select *from student
go
/*新建查询2*/
use school
go
/*步骤2*/
/*begin transaction t2
update student
set name='Tank'
where id=2006003
go
select * from student
go*/
/*步骤4*/
rollback
go
select * from student
go
/*use school
go
insert into student
(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,address)
values
(2006008,'李治','男',25,1,1,104,2004,'2006年毕业','天津',220200030304304051,null)
go
update student
set age=age+10
go
delete from student
go

begin transaction t1
update student
set [name]='Tank'
where id=2006008

delete from student
where id=2006006
commit transaction t1  
*/
--并发性问题
--不使用事务造成的数据不一致性
--创建两个存储用户储蓄的账户
use  demo
go
/*create table  bank1
(
 deposit_id  int, --储蓄账户编号　
 [name] char(20),--储蓄账户的名称
 deposit  money  --储蓄额
)
go
create table  bank2
(
 deposit_id  int, --储蓄账户编号　
 [name] char(20),--储蓄账户的名称
 deposit  money  --储蓄额
)
go
--录入信息
--bank1
insert into bank1
(deposit_id,name,deposit)
values
(1001,'王云',10000)
go
insert into bank1
(deposit_id,name,deposit)
values
(1002,'李治',20000)
go
--bank2
insert into bank2
(deposit_id,name,deposit)
values
(1003,'张冶',9000)
go
insert into bank2
(deposit_id,name,deposit)
values
(1004,'李婷',30000)
go
--转账存储过程
create procedure transfer_bank1 @deposit_id int,@deposit money
as
update bank1
set deposit=deposit+@deposit
where deposit_id=@deposit_id
go
create procedure transfer_bank2 @deposit_id int,@deposit money
as
update bank2
set deposit=deposit+@deposit
where deposit_id=@deposit_id
go
--查询
select * from bank1
go
select * from bank2
go
--使用
execute transfer_bank1 1001,-5000
go
execute transfer_bank2 1003,5000
go
select * from bank1
go
select * from bank2
go
--使用waitfor delay模拟转移延迟
exec transfer_bank1 1001,-5000
go
waitfor delay '00:08'  --代表延迟5秒执行下面的语句
exec transfer_bank2 1003,5000
go
/*打开SQL SERVER CONFIGURATION MANAGER管理器，选择SQL SERVER2005服务，停止SQL SERVER(MSSQL SEVE),
　重新启动SQL SERVER(MSSQL SEVE，再执行下面的代码*/
select * from bank1
go
select * from bank2
go*/
/*发现BANK1中的账户已经把5000元转出去了，而BANK2中没有收到，这就是在没有维护事务一致性造成的结果*/
--通过事务维护原子性
--使用显示事务来将转账标识
/*begin transaction t1
exec transfer_bank1 1002,-10000
go
waitfor delay '00:08'
exec transfer_bank2 1004,10000
commit transaction t1
select * from bank1
go
select * from bank2
go*/
/*可以看见数据没有丢失,实现了通过显示事务来维护数据的一致性*/
use school
go
--查看锁的信息
--在第一个查询分析界面更改学生和老师信息
--第一个查询界面
/*步骤1*/
/*begin transaction t1
update student
set name='long' where id=2006008
update teachers
set name='刘缨' where id=102*/
/*步骤3*/
exec sp_lock
go
--第二个查询界面
/*步骤2*/
use school
go
begin transaction t2
update teachers
set name='LITING' where id=102
update student
set name='zhangming' where id=2006008
======第19章==SQL Server2005的安全机制==================================================
/*
--创建windows身份的SQL Server登录
use master
go
create login [pc2009062911VDJ/study] from windows  --pc2009062911VDJ是电脑的名字
go
--授予他访问数据库的权限
use school
go
create user study
for login [pc2009062911VDJ/study]
go*/
--创建SQL SERVER账户登录
use master
go
create login study1
with
password='study1',
default_database=school
go
/*重新登录显示:登录失败，错误：4064*/
--创建数据库用户
use school
go
create user study1
for login  study1
go
--重新登录，登录成功
--创建架构
use school
go
create schema reachers  --架构名称
authorization dbo　--所有者为dbo
go

use school
go
--drop user study1  --删除用户
--go
--drop login study1 --删除登录账户
--go
--删除架构
--drop schema reachers 
--go
--guest用户
--创建
use master
go
create login teachers
with
password='teachers',
default_database=master
go
--启用guest用户
use school
go
grant connect to guest
go
--创建账户
use school
go
/*create login teacher
with
password='teacher',
default_database=school
go
create user teacher
for login teacher
go*/
--用teacher用户身份登录SQL SERVER2005
--查询
select * from student
go
消息：拒绝了对对象 'student' (数据库 'School'，架构 'dbo')的 SELECT 权限。
--修改
update student
set age=30
where id=2006003
go
消息：拒绝了对对象 'student' (数据库 'School'，架构 'dbo')的 UPDATE 权限。
use school
go
--赋予teacher修改表权限
grant update on dbo.student
to teacher
go
--语句权限
use school
go
/*create table books
(
 book_id int,
 book_name char(20)
)
go
--创建架构
create schema students--架构名称
authorization teacher--所有者
go
--查询
select * from student
go
create table students.books
(
 book_id int,
 book_name char(20)
)
go
create table students.student
(
 id int,
 [name] char(20)
)
go
insert into students.student
(id,name)
values
(10001,'志强')
--查询
select * from student
go
select * from students.student
go
select * from dbo.student
go*/
--赋予语句权限
--创建登录与数据库用户
/*create login teacher5
with  password='teacher5',
default_database=school
go
use school
go
create user teacher5
for login teacher5
with default_schema=students
go
--赋予teacher5用户CREATE TABLE的权限
grant create table to teacher5
go*/
/*通过以上操作，登录名为“teacher5”的账户，就能够在数据库school中创建在students架构下的表*/
--删除权限
use school
go
/*revoke select on dbo.student
from teacher
go
--在teacher登录数据库下，查询dbo.student表
select * from  dbo.student
go*/
--将用户添加到系统角色中
--使用SP_ADDROLEMEMBER系统存储过程
/*use school
go
exec sp_addrolemember 'db_owner','teacher5'
go
--用户自定义角色
--创建TEACHERS角色
use school
go
exec sp_addrole teachers
go
--赋予teachers权限
--创建表权限
grant create table to teachers
go
--创建视图权限
grant create view to teachers
go
--将teacher5添加到teachers角色中
exec sp_addrolemember 'teachers','teacher5'
go
--创建应用程序角色
use school
go
create application role school_application
with password='hello',
default_schema=students
go*/
--赋予应用程序角色创建存储过程的权限
grant select on dbo.student to school_application
go
--用TEACHER5角色登录数据库
--查询表
use school
go
select * from dbo.student
go
--激活应用程序
use school
go
exec sp_setapprole 'school_application','hello'
go
--DENY——否决优先权
--赋予角色访问表权限
use school
go
/*grant select on dbo.student
to teachers
go
--取消TEACHER5访问表的权限
revoke select on dbo.student
from teacher5
go
--使用DENY
deny select on dbo.student
to teacher5
go*/
--权限深入
--以TEACHER5角色登录SQL
--创建的架构students下的视图
use school
go
/*create view students.v_student
as
select * from dbo.student
go*/
--查询
select * from students.v_student
go
/*消息：拒绝了对对象 'student' (数据库 'School'，架构 'dbo')的 SELECT 权限。
　通过实验，用户teacher5由于属于角色teachers，而teachers拥有创建视图的权限，虽然视图组成的SQL语句，
　是不能被teacher5执行的，但teacher5从角色中拥有了创建视图的权限，视图students.v_student还是创建成功了。*/
=====第20章==SQL Server2005备份与恢复=====================================================
--实现数据库school的临时备份
/*use master
go
backup  database  school
to disk='e:/school_back/school.bak'
/*消息:不允许对文件或文件组 "sysft_school_catalog" 进行备份，因为它没有联机。可以使用
  FILEGROUP 或 FILE 子句执行 BACKUP，以限制在选项中只包括联机数据。*/
go
--exec  sp_helpdb  school
--删除数据库school
drop  database school
go
--恢复数据库
use master
go
restore database  school
from disk='e:school_back/school.bak'
go
--创建备份设备
use master
go
exec sp_addumpdevice  'disk','demo_bak_device',
  'e:/demo_bak_device/demo.bak'
go
--备份数据库DEMO
use master
go
backup database demo
to demo_bak_device  
--消息:无法打开备份设备 'demo_bak_device(e:/demo_bak_device/demo.bak)'。出现操作系统错误 3(系统找不到指定的路径。)。
go
--删除数据库school
use  master
go
drop  database school
go
--恢复数据库
use master
go
restore database demo
from demo_bak_device
go*/
--在school中添加一条信息
/*use  school
go
insert into student
(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,address)
values
(2006020,'志刚','男',24,2,2,102,2002,'已经毕业','北京',22020003030430407,'北京市清华西路103号')
go*/
--查询
/*use school
go
select * from student
go
--创建备份设备
use master
go
exec sp_addumpdevice  'disk','school_bak_device',
  'e:/school_bak_device/school.bak'
go
--全备份
use master
go
backup database school
to school_bak_device
go*/
--删除
use school
go
delete from student
where id=2006020
go
--差异备份
use master
go
backup  database school
to with differential
go
--删除
use master
go
drop database school
go
/*差异备份恢复*/
--查看
restore headeronly
from school_bak_device
go
--全备份恢复
use master
go
restore database school
from school_bak_device
with file=2,norecovery
go
--访问数据库
use school
go
select * from school.dbo.student
go
--差异恢复
use master
go
restore database school
from school_bak_device
with file=3,recovery
go
--查询
use school
go
select * from student
go
--日志备份与恢复
--为teachers添加信息
use school
go
insert into teachers
(id,name,gender,age,subject,teaching_age,position,salary)
values
(106,'韩非','男',40,'数学',20,'数学组组长',5000)
go
--查询
use school
go
select * from teachers
go
--对数据库school进行全备份
use master
go
backup database school
to school_bak_device
go
--在表teachers添加一条新的记录
use school
go
insert into teachers
(id,name,gender,age,subject,teaching_age,position,salary)
values
(110,'开波','男',30,'数学',5,'初级教师',2000)
go
--进行差异备份
use master
go
backup database school
to school_bak_device
with differential
go
--在表teachers添加一条新的记录
use school
go
insert into teachers
(id,name,gender,age,subject,teaching_age,position,salary)
values
(111,'王刚','男',35,'物理',10,'中级教师',3000)
go
--进行事务日志备份
use master
go
backup log school
to school_bak_device
go
--删除表teachers
use school
go
drop table teachers
go
--查看备份设备中的结果集
restore  headeronly from school_bak_device
go
--使用备份设备进行全备份恢复
use master
go
restore database school
from school_bak_device
with file=4,norecovery
go
--使用备份设备进行差异备份恢复
use master
go
restore database school
from school_bak_device
with file=5,norecovery
go
--使用备份设备进行日志备份恢复
use  master
go
restore log school
from school_bak_device
with file=6,recovery
go
--查询
use school
go
select * from teachers
go
/*数据库还原模型*/
--对数据库msdb进行全备份
use master
go
backup database msdb
to school_bak_device
go
--对数据库msdb进行日志备份
use master
go
backup log msdb
to school_bak_device
go
--消息：当恢复模式为SIMPLE时，不允许使用BACKUP LOG或用ALTER DATABASE更改恢复模式
--消息：BACKUP LOG正在异常终止
/*通过以上操作，数据库MSDB不运行进行日志备份，而出现的错误是由于数据库的恢复模式为SIMPLE，
　这就是数据库恢复模型的影响．*/

--查看数据库school的还原属性
select databasepropertyex('school','recovery') as 'school数据库还原模型'
go
select databasepropertyex('master','recovery') as 'master数据库还原模型'
go
--修改数据库还原模型
/*use master
go
alter database school
set recovery simple
go
--查看
select databasepropertyex('school','recovery') as 'school数据库还原模型'
go*/