T-sql程式設計

-TSQL語句庫(stuDB)-資訊表stuInfo-成績表stuMarks
--曾
insert into stuInfo(Name)
values('小A')
--刪
delete from stuInfo where name = '小A'--delete from 表名 where 條件
--改
update stuinfo set name = '大A' where name = '小A'--update 表名 set 欄位 = 更新值 where 條件
--查
select name from stuInfo where -----select 查詢欄位 from 表名 where 條件

/***************************建庫建表***********************************************************/
use master--設定當前是資料庫為master，以便sysdatabases訪問
if exists (select * from sysdatabases where name ='stuDB')--查詢資料庫中是否存在stuDB資料庫，如果存在刪除
drop database stuDB--刪除資料庫
go

----建庫(如果要建立多個資料檔案和日誌檔案只要在資料檔案的"()"後面加“,”就可以繼續建立第二個資料檔案了)
exec xp_cmdshell 'mkdir D:\Myproject'--利用系統儲存過程呼叫DOS命令建立目錄
create database stuDB
on

--資料檔案的具體描述
(
name = 'stuDB_data',
filename = 'D:\Myproject\stuDB_data.mdf',
size = 3mb,
maxsize = 10mb,
filegrowth = 10%
)
log on
--日誌檔案的具體描述
(
name = 'stuDB_log',
filename='D:\Myproject\stuDB_log.ldf',
size = 3mb,
maxsize = 5mb
)
go

 

----建表
use stuDB
go
if exists (select * from sysobjects where name = 'stuInfo')--查詢-刪除
drop table stuInfo
go
create table stuInfo--建表，學員資訊表
(
stuName    varchar(8)   not null,
stuNo    char(6)    not null,
stuAge    int     not null,
stuID    numeric(18,0), --18位數，小數位數為0
stuSeat    int     identity(1,1),--表示列，自動增長
stuAddress   text,
)
go
----建立學員成績表
if exists (select * from sysobjects where name = 'stuMarks')
drop table stuMarks
go
create table stuMarks
(
ExamNo    char(7)    not null,--考號
stuNO    char(6)    not null,--學號
writtenExam   int   ,--筆試
labExam    int   ,--機試
)

----建約束(主鍵/唯一/檢查/預設/外來鍵)
alter table stuInfo
add constraint PK_stuNo primary key(stuNo) --主鍵約束

alter table stuInfo
add constraint UO_stuID unique (stuID) --唯一約束,作為身份證號具有唯一性

alter table stuInfo
add constraint CK_stuAge check (stuAge between 18 and 25)--檢查約束,年齡在18-25之間

alter table stuInfo
add constraint DF_stuAddress default ('地址不詳') for stuAddress --預設約束

--新增外來鍵約束stuMark的stuNo引用stuInfo的主鍵stuNO
alter table stuMarks
add constraint FK_stuNO foreign key(stuNo) references stuInfo(stuNo)

alter table stumarks
add constraint PK_stuNo primary key(stuNo)--主鍵

-----刪除約束
/*
alter table stuInfo
drop constraint PK_stuNo
*/

----------------------------------------------------------------------------------------------------------
/*新增stuInfo測試資料*/
insert into stuInfo(stuName,stuNo,stuAge,stuID)
values('張飛,'s0070',18,320381199000000000)

insert into stuInfo(stuName,stuNo,stuAge,stuID)
values('李廣','s0071',18,320381199000000001)

insert into stuInfo(stuName,stuNo,stuAge,stuID,stuAddress)
values('byA','s0072',18,320381199000000002,'江蘇一區')
insert into stuInfo(stuName,stuNo,stuAge,stuID,stuAddress)
values('bB','s0073',18,320381199000000003,'江蘇二區')
insert into stuInfo(stuName,stuNo,stuAge,stuID,stuAddress)
values('byC','s0074',18,320381199000000004,'江蘇三區')

/*新增stuMarks測試資料*/

insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by00','s0070',67,89)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by01','s0071',77,90)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by02','s0072',40,55)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by03','s0073',59,92)
insert into stuMarks(ExamNo,stuNo,writtenExam,labExam)
values('by04','s0074',80,83)

 

/***************************T_SQL程式設計***********************************************************/

----區域性變數(宣告:declare @i int)(賦值:set @i = 3 OR select @i = stuSeat from stuInfo where stuname ='小A')()
declare @i int,@name varchar(6)
--set賦值
set @i = 3
set @name = '小A'
print @i
print @name
--select 賦值
select @i = stuSeat from stuInfo where stuname ='byC'
select @name = stuName from stuInfo where stuID = 320381199000000003
print @i
print @name

----全域性變數
@@identity--最後插入的標識列

 

-----邏輯語句 if else

declare @avgwritten float--宣告變數
select @avgwritten=avg(writtenExam) from stumarks--select賦值
print '本班筆試平均分是：   ' + convert(char(4),@avgwritten)

--邏輯語句判斷大於70 優秀 小於70 一般
if(@avgwritten > 70)
begin
   print '成績優秀'
end
else
begin
   print '成績一般'
end

-----while 迴圈
--統計機試沒有通過的學員，迴圈加分，如過有超過100的，迴圈結束
--(先判斷有幾人沒通過考試---1，然後通過迴圈判斷加分，然後在迴圈體中在判斷)
declare @n int
select @n=count(*) from stumarks where labExam < 60---1
while(@n>0)
begin
   update stumarks set labExam = labExam +1
   select @n=count(*) from stumarks where labExam < 60
end

----第二種方法()
declare @n int
while(1=1)--條件永遠成立
begin
   --update stumarks set labExam = labExam +1--迴圈加分
   select @n=count(*) from stumarks where labExam < 60--查詢不及格人數
   if(@n<1)--如果不及格人數小於1(全部通過)則跳出迴圈
    begin
     break
    end
   else
    begin
     update stumarks set labExam = labExam +1--迴圈加分
    end
end

-------case end 多分支語句

select '筆試成績'=writtenExam,'等級'=
case
when labexam between 90 and 100 then'A'
when labexam between 80 and 89 then'B'
when labexam between 70 and 79 then'C'
when labexam between 60 and 69 then'D'
else
'E'
end

,'機試成績'=labExam ,'等級'=
case
when labexam between 90 and 100 then'A'
when labexam between 80 and 89 then'B'
when labexam between 70 and 79 then'C'
when labexam between 60 and 69 then'D'
else
'E'
end

from stumarks

 

/***************************子查詢和多表連線***********************************************************/

------利用子查詢查詢出年齡比byA大的學員(首先查詢出byA學員的年齡，然後根據where條件篩選比byA大的學員)
select stuName from stuInfo where stuAge >(select stuAge from stuInfo where stuname = 'byA')

----利用多表連結和子查詢
--查詢筆試成績小於60分的學員姓名和成績
select stuName from stuinfo inner join stumarks on stuinfo.stuNo=stumarks.stuno where writtenExam < 60--多表連結，內連線

--in子查詢查詢出筆試小於60分學員的stuNO ，然後對應stuinfo表的stuNo
select stuName from stuinfo where stuNo in (select stuNo from stumarks where writtenExam < 60)

--not in
select stuName from stuinfo where stuNo not in (select stuNo from stumarks where writtenExam < 60)

 

 

 

 

/***************************事務-索引-檢視***********************************************************/

begin transaction --事務開始
rollback tran --撤銷事務
commit tran --提交事務

 

begin transaction --事務開始
declare @i int--定義變數，用於累計事務執行過程中的錯誤
set @i = 0 --初始值為0
update bank set money = money -300 where name = '張三'
set @i = @i +@@error
update bank set money = money +300 where name = '李四'
set @i = @i +@@error

if(@i>0)--根據是否有錯誤，確定事務是提交還是撤銷

begin
   print '轉賬失敗'
   rollback transaction--回滾事務
end
else
begin
   print '轉賬成功'
   commit transaction --提交事務,事務結束

end
select * from bank

 

 

 

----非聚集建立索引示例

if exists (select * from sysindexes where name='ix_stuName')
drop index stuinfo.ix_stuName--刪除索引

create nonclustered index ix_stuName
on stuInfo(stuName)
with fillfactor=75--填充因子75%
--指定索引搜尋
select * from stuinfo
with(index=IX_stuName)

------建立檢視
if exists(select * from sysobjects where name='view_BanZhuRen')
drop view view_stu--刪除檢視
go
create view view_stu
as
select stuName,writtenexam,labexam from stuInfo left outer join stumarks on stuinfo.stuno = stumarks.stuno--左外連線

select * from view_stu

 

 

 

/***************************a儲存過程***********************************************************/

-----------------------建立儲存過程------------------------
if exists (select * from sysobjects where name='proc_stu')
drop proc proc_stu
go
create proc proc_stu
@count int output ,--輸出引數
@writtenPass int=60 ,--輸入引數，筆試及格線，預設值為60分
@labPass int=60--輸入引數，機試及格線，預設值為60分

as
-------------T-SQL業務語句------------------
if((@writtenPass not between 0 and 100)or(@labPass not between 0 and 100))
   begin
    raiserror('及格線要在0到100之間',16,1)
    return
   end
print '筆試及格線'+convert(char(5),@writtenPass)
print '機試及格線'+convert(char(5),@labPass)
declare @avgWritten float,@avglab float
select @avgWritten=avg(writtenExam),@avglab=avg(labExam)
from stumarks
print '筆試平均分'+convert(char(5),@avgWritten)
print '機試平均分'+convert(char(5),@avgWritten)
--優秀
if(@avgWritten>70 and @avglab>70)
   print '優秀'
else
   print '一般'

--不及格學員資訊
select stuinfo.* ,writtenExam,labExam
FROM stuinfo inner join stumarks
on stuinfo.stuno=stumarks.stuno
where writtenExam

--不及格學員個數
select @count=count(*)
FROM stuinfo inner join stumarks
on stuinfo.stuno=stumarks.stuno
where writtenExam

go

--呼叫儲存過程----------
exec proc_stu--採用預設值
exec proc_stu 50,50--輸入分數線為50
exec proc_stu 50--筆試採用預設值60，機試及格線50
exec proc_stu @labPass=50--機試採用50分作為及格線，筆試預設值

--呼叫有返回值的儲存過程
declare @sum int
exec proc_stu @sum output ,60

if(@@error=0)

print '不及格人數'+convert(char(2),@sum)

else
print '因為及格線不正確，沒法確定資訊，請重新輸入 '

 

 

select * from stuInfo
select * from stuMarks

原文地址：http://www.cnblogs.com/zhangtao/archive/2009/11/06/1597114.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-618254/，如需轉載，請註明出處，否則將追究法律責任。