create database MyDb on ( name=mainDb, filename='c:/MyDb/mainDb.mdf', size=10, maxsize=100, filegrowth=4 ), ( name=secondDb, filename='C:/MyDb/secondDb.ndf', size=15, maxsize=28, filegrowth=2 ) log on ( name=log_Db, filename='C:/MyDb/log_Db', size=20, filegrowth=10% ) --創建數據庫的一般格式 use mydb create table student ( stuId int primary key identity (1,1), stuName varchar (20) not null, stuAge int not null check(stuAge between 20 and 50), stuSex varchar(4) not null check(stusex in('F','M')), stuDept varchar(20) check( stuDept in('軟工系','環藝系','電子商務系')), stuAddress varchar(20) not null ) drop table student select * from student insert into student values ('孫業寶',22,'M','軟工系','河北省邢臺市') insert into student values ('孫婷',20,'F','電子商務系','河北省邢臺市') insert into student values ('孟幾',22,'F','電子商務系','河北省邢臺市') insert into student values ('小五',22,'M','軟工系','河北省革要市') insert into student values ('王丹丹',22,'M','軟工系','河北省阜陽市') insert into student values ('陳海波',22,'M','軟工系','河北省合肥市') --單一的輸入輸出參數的存儲過程, create proc Myproc @Dept varchar(20),@count int output As if not exists(select * from student where Studept=@dept) print '沒有指定類型的學生存在??！' else select @count=Count(*) from student where studept=@dept drop proc myproc --執行該存儲過程 declare @result int Exec myproc '軟工系',@result output print @result --多輸入輸出的存儲過程. create proc Searchstu @area varchar(20),@Sex varchar(2),@count int output,@avg_age int output as select @count=count(*),@avg_age=Avg(stuage) from student where stuaddress=@area and stusex=@sex --執行該存儲過程 declare @stuNo int ,@stuAvg_age int exec searchstu '河北省邢臺市','M',@stuNo output,@stuAvg_age output select @stuNo as 學生總數,@stuavg_age as 平均年齡 --用戶自定義的函數(求立方體體積定義標題函數返回單一值) create function dbo.CubicVolume (@CubeLength int,@CubeHenght int,@CubeWidth int) Returns int as begin return (@CubeLength*@CubeHenght*@CubeWidth) end drop function CubicVolume --調用該方法 select dbo.CubicVolume(10,10,10) --用戶自定義的函數(內嵌表形式,返回一個表) create function f_stuInfo(@studept varchar(20)) returns table as return ( select * from student where studept=@studept ) --調用該方法 select * from dbo.f_stuInfo('軟工系') --用戶自定義的函數(多語句表值函數,返回一個用戶想要顯的部分數據的表) create function f_stuSexTye(@stuDept varchar(10)) returns @t_stuDetailInfo table( stuName varchar(20), stuAge int , stuSex varchar(4) )
