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%
)
--創(chuàng)建數(shù)據(jù)庫的一般格式
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('軟工系','環(huán)藝系','電子商務系')),
stuAddress varchar(20) not null
)
drop table student
select * from student
insert into student values ('孫業(yè)寶',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','軟工系','河北省合肥市')
--單一的輸入輸出參數(shù)的存儲過程,
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
--執(zhí)行該存儲過程
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
--執(zhí)行該存儲過程
declare @stuNo int ,@stuAvg_age int
exec searchstu '河北省邢臺市','M',@stuNo output,@stuAvg_age output
select @stuNo as 學生總數(shù),@stuavg_age as 平均年齡
--用戶自定義的函數(shù)(求立方體體積定義標題函數(shù)返回單一值)
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)
--用戶自定義的函數(shù)(內嵌表形式,返回一個表)
create function f_stuInfo(@studept varchar(20))
returns table
as
return
(
select * from student where studept=@studept
)
--調用該方法
select * from dbo.f_stuInfo('軟工系')
--用戶自定義的函數(shù)(多語句表值函數(shù),返回一個用戶想要顯的部分數(shù)據(jù)的表)
create function f_stuSexTye(@stuDept varchar(10))
returns @t_stuDetailInfo table(
stuName varchar(20),
stuAge int ,
stuSex varchar(4)
)
as
begin
insert into @t_stuDetailInfo
select Stuname,stuage,
Case stusex
when 'M' then '男'
when 'F' then '女'
end
from student
where stuDept=@studept
return
end
--調用該方法函數(shù)
select * from dbo.f_stuTye('軟工系')