SQL Server 笔记整理

远行的心

　　读书时遗失的笔记！现在偶尔的机会找到了！真的很不错的笔记！比较全面！在这里和大家共享！希望能帮到一些人！毕竟是在校时的产物！认知有限！如有错误还请帮忙指正！（Issac_czy@163.com）谢谢！
　　use master
　　go
　　if exists(select * from sysdatabases where name='student')
　　drop database student
　　--创建数据库
　　create database student
　　on
　　(
　　name='student',
　　filename='D:\DBExercise\sudent.mdf',

　　>　　filegrowth=10%,
　　maxsize=100
　　)
　　log on
　　(
　　name='student.ldf',
　　filename='D:\DBExercise\student.ldf',

　　>　　filegrowth=10%
　　)
　　go
　　--创建表
　　if exists(select * from sysobjects where name='stuInfo')
　　drop table stuInfo
　　if exists(select * from sysobjects where name='stuMarks')
　　drop table stuMarks
　　go
　　use student
　　go
　　create table stuInfo
　　(
　　stuName nvarchar(50) not null,
　　stuNo nvarchar(20) not null,
　　stuSex char(2) not null,
　　stuAge int not null,

　　stuSeat int>　　stuAddress nvarchar(100) not null
　　)
　　go
　　create table stuMarks
　　(
　　ExamNo nvarchar(20) not null,
　　stuNo nvarchar(20) not null,
　　writtenExam int not null,
　　labExam int not null
　　)
　　go
　　--向表中添加约束
　　alter table stuInfo
　　add constraint pk_stuNo primary key(stuNo)
　　alter table stuMarks
　　add constraint fk_stuNo foreign key(stuNo) references stuInfo(stuNo)
　　alter table stuInfo
　　add constraint df_stuAddress default('地址不详') for stuAddress
　　go
　　--向表中填充数据
　　insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
　　values('张秋离','s25301','男',19,'北京海淀')
　　insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
　　values('李斯文','s25303','女',20,'河南洛阳')
　　insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
　　values('李文才','s25302','男',17,default)
　　insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
　　values('欧阳俊雄','s25304','男',21,'新疆')
　　insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
　　values('梅嘲讽','s25318','女',18,default)
　　insert into stuMarks
　　values('s271811','s25303',93,59)
　　insert into stuMarks
　　values('s271813','s25302',63,91)
　　insert into stuMarks
　　values('s271816','s25301',90,83)
　　insert into stuMarks
　　values('s271817','s25318',63,53)
　　go
　　--检测表中数据
　　select * from stuInfo
　　select * from stuMarks
　　--本次考试的缺考人数
　　if exists(select * from sysobjects where name='newTable_1')
　　drop table newTable_1
　　select 应到人数=(select count(*) from stuInfo),
　　实到人数=(select count(*) from stuMarks),
　　缺考人数=((select count(*) from stuInfo)-(select count(*) from stuMarks)) into newTable_1
　　select * from newTable
　　--提取学员的成绩信息并保存结果，包括学员姓名，学号，笔试成绩和机试成绩，是否通过
　　if exists(select * from sysobjects where name='newTable_2')
　　drop table newTable_2
　　select stuName,stuInfo.stuNo,writtenExam,labExam,ispass=
　　case
　　when writtenExam>=60 and labExam>=60 then 1
　　else 0
　　end
　　into newTable_2 from stuInfo left join stuMarks on stuInfo.stuNo=stuMarks.stuNo
　　print('提分前的学员考试情况表')
　　select 学员姓名=stuName,学员编号=stuNo,
　　笔试成绩=case when writtenExam is null then '缺考'else convert(varchar(20),writtenExam) end,
　　机试成绩=case when labExam is null then '缺考' else convert(varchar(20),labExam) end,
　　是否通过=case when ispass=1 then '是' else '否' end
　　from newTable_2
　　--比较笔试平均分和机试平均分，较底者进行提分，但最高分不能超过97
　　declare @avg_write int ,@avg_lab int                --声明两个变量
　　select @avg_write=avg(writtenExam), @avg_lab=avg(labExam)from newTable_2 where writtenExam is not null and labExam is not null
　　--print('笔试平均分：'+convert(nvarchar(20),@avg_write))
　　--print('机试平均分：'+convert(nvarchar(20),@avg_lab))
　　if( @avg_write=97
　　break
　　end
　　else
　　while(1=1)
　　begin
　　update newTable_2 set labExam=labExam+2
　　if(select max(labExam) from newTable_2)>=97
　　break
　　end
　　update newTable_2 set ispass=case
　　when writtenExam>=60 and labExam>=60 then 1
　　else 0
　　end
　　print('提分后的学员成绩表')
　　select * from newTable_2
　　--提分后，统计学员的成绩和通过情况
　　select 学员姓名=stuName,学员编号=stuNo,
　　笔试成绩=case when writtenExam is null then '缺考'else convert(varchar(20),writtenExam) end,
　　机试成绩=case when labExam is null then '缺考' else convert(varchar(20),labExam) end,
　　是否通过=case when ispass=1 then '是' else '否' end
　　from newTable_2
　　set noCount on
　　--提分后统学员的通过率
　　declare @count int ,@pass int
　　select @count=count(*) from stuInfo
　　select @pass=count(*) from newTable_2 where ispass=1
　　select 总人数=@count,
　　通过人数=@pass,
　　通过率=(convert(nvarchar(20),(@pass*100)/@count))+'%'
　　----------事务---------
　　if exists(select * from sysobjects where name='bank')
　　drop table bank
　　create table bank
　　(
　　name nvarchar(20) not null,
　　money money not null
　　)
　　go
　　insert into bank
　　values('Tom',800)
　　insert into bank
　　values('Jim',10)
　　alter table bank
　　add constraint ck_money check(money>1)
　　select * from bank
　　--Tom要转帐元给jim，有上表可知Tom的钱压根就没有那么多的，如果用update将Tom 的
　　--减少，而jim的增加，则会出现的情况是：Tom 的没有减少Jim却增加了
　　--这样银行就亏损了，显然银行是不同意这样的事情发生的
　　--那么为了使更改前后两者的数据保持一直我们就采取事务来处理类似的问题
　　begin transaction
　　set nocount on        ====》设定不显示影响行数
　　declare @errorSum int
　　set @errorSum=0
　　update bank set money=money-1000 where name='Tom'
　　set @errorSum=@errorSum+@@error
　　--print(convert(nvarchar(20),@errorSum))
　　update bank set money=money+1000 where name='Jim'
　　set @errorSum=@errorSum+@@error
　　--print(convert(nvarchar(20),@errorSum))
　　--select * from bank  --此时虽说系统报错了，但jim的钱还是多了，而Tom的却没有变化
　　if @errorSum0
　　begin
　　print('交易有误！')
　　rollback transaction
　　end
　　else
　　begin
　　print('交易成功！')
　　commit transaction
　　end
　　go
　　print('转帐后的系统存根：')
　　select * from bank
　　go
　　-----------索引---------------
　　--对于上面的stuMarks表创建索引
　　if exists(select name from sysindexes where name='ix_writtenExam')
　　drop index stuMarks.ix_writtenExam
　　create nonclustered index ix_writtenExam
　　on stuMarks(writtenExam)
　　with fillfactor=30           ===》它表示创建索引时每个索引页的数据填充率
　　go
　　--应用索引
　　select * from stuMarks
　　(index=ix_writtenExam)
　　where writtenExam between 60 and 90
　　---------视图----------
　　--========注：对视图的修改会影响到原始数据==========
　　if exists(select * from sysobjects where name='view_student')
　　drop view view_student
　　go
　　create view view_student
　　as
　　select 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩=labExam,平均分=(writtenExam+labExam)/2
　　from stuInfo left join stuMarks on stuInfo.stuNo=stuMarks.stuNo
　　go
　　select * from view_student
　　go
　　--===================存储过程=================
　　--所有的系统存储过程都是以sp_开头的，存放在master数据库中的
　　--***************常用系统存储过程的应用**************
　　exec sp_databases       --列出当前系统中的存储过程
　　exec sp_renamedb 'oldname','newname'    --改变数据库名称
　　use student
　　go
　　exec sp_tables
　　exec sp_columns stuInfo
　　exec sp_help stuInfo      --查看stuInfo表的所有信息
　　exec sp_helpconstraint stuInfo     --查看表的约束
　　exec sp_helpindex stuMarks      --查看表的索引
　　exec sp_helptext 'view_writtenExam'   --查看视图的语句文本
　　exec sp_stored_procedures    --当前数据库中的存储过程列表
　　--系统存储过程xp_cmdshell可以实现DOS命令下的一些操作
　　--如果要创建一个数据库在D:\Issac中，但D盘中没有Issac文件夹，即可调用此存储过程实现创建
　　exec xp_cmdshell 'md D:\Issac',no_output     --no_output是输出返回信息，可选
　　--==========自定义存储过程============
　　--***********不带参数的存储过程*************
　　use student
　　go
　　if exists(select * from sysobjects where name='proc_getAvgOfWrite')
　　drop procedure proc_getAvgOfWrite
　　go
　　create proc proc_getAvgOfWrite
　　as
　　declare @avgWrite float
　　select @avgWrite=avg(writtenExam)from stuMarks
　　print(convert(nvarchar(20),@avgWrite))
　　go
　　exec proc_getAvgOfWrite
　　--***************带参数的存储过程****************
　　--*******输入参数********
　　use student
　　go
　　if exists(select * from sysobjects where name='proc_getNotPass')
　　drop procedure proc_getNotPass
　　go
　　create proc proc_getNotPass
　　@writtenPass=60 int ,   --输入参数笔试及格线
　　@labPass=60 int            --输入参数机试及格线
　　as
　　print('本次考试没有通过的学员：')
　　select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo inner join stuMarks on
　　stuInfo.stuNo=stuMarks.stuNo
　　where writtenExam