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%
)
-- database-creating 1 A format
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(' Soft science ',' Ring arts department ',' Department of electronic commerce ')),
stuAddress varchar(20) not null
)
drop table student
select * from student
insert into student values (' Sun Yebao ',22,'M',' Soft science ',' Xingtai city, hebei province ')
insert into student values (' Sun Ting ',20,'F',' Department of electronic commerce ',' Xingtai city, hebei province ')
insert into student values (' Meng a few ',22,'F',' Department of electronic commerce ',' Xingtai city, hebei province ')
insert into student values (' small 5',22,'M',' Soft science ',' Gexiang city, hebei province ')
insert into student values (' Diana ',22,'M',' Soft science ',' Fuyang city, hebei province ')
insert into student values (' Chen haibo ',22,'M',' Soft science ',' Hefei city, hebei province ')
-- single 1 The input and output parameters of the stored procedure ,
create proc Myproc
@Dept varchar(20),@count int output
As
if not exists(select * from student where Studept=@dept)
print ' No specified type of student exists!! '
else
select @count=Count(*) from student where studept=@dept
drop proc myproc
-- Execute the stored procedure
declare @result int
Exec myproc ' Soft science ',@result output
print @result
-- A stored procedure with multiple inputs and outputs .
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
-- Execute the stored procedure
declare @stuNo int ,@stuAvg_age int
exec searchstu ' Xingtai city, hebei province ','M',@stuNo output,@stuAvg_age output
select @stuNo as Total number of students ,@stuavg_age as The average age of
-- User-defined functions ( Find the volume of the cube and define the title function to return a single 1 value )
create function dbo.CubicVolume
(@CubeLength int,@CubeHenght int,@CubeWidth int)
Returns int
as
begin
return (@CubeLength*@CubeHenght*@CubeWidth)
end
drop function CubicVolume
-- Call this method
select dbo.CubicVolume(10,10,10)
-- User-defined functions ( Inline table form , return 1 A table )
create function f_stuInfo(@studept varchar(20))
returns table
as
return
(
select * from student where studept=@studept
)
-- Call this method
select * from dbo.f_stuInfo(' Soft science ')
-- User-defined functions ( Multiple statement table valued functions , return 1 Table of partial data that the user wants to display )
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 ' male '
when 'F' then ' female '
end
from student
where stuDept=@studept
return
end
-- The method function is called
select * from dbo.f_stuTye(' Soft science ')