Copy codeThe Code is as follows: 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%
)
-- General format for creating a database
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 ')),
StuAddress varchar (20) not null
)
Drop table student
Select * from student
Insert into student values ('Sun yebao', 22, 'M', 'soft Engineering Department ', 'xingtai City, Hebei Province ')
Insert into student values ('sunting', 20, 'F', 'e-commerce department ', 'xingtai City, Hebei Province ')
Insert into student values ('mongos', 22, 'F', 'e-commerce department ', 'xingtai City, Hebei Province ')
Insert into student values ')
Insert into student values ('wang dand', 22, 'M', 'soft Engineering Department ', 'fuyang City, Hebei Province ')
Insert into student values ('chen Haibo ', 22, 'M', 'soft Engineering Department', 'hefei City, Hebei Province ')
-- Stored procedure for a single input/output parameter,
Create proc Myproc
@ Dept varchar (20), @ count int output
As
If not exists (select * from student where Studept = @ dept)
Print 'no student of the specified type exists !! '
Else
Select @ count = Count (*) from student where studept = @ dept
Drop proc myproc
-- Execute the Stored Procedure
Declare @ result int
Exec myproc 'software', @ result output
Print @ result
-- Stored procedure of 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 average age
-- User-Defined Function (cube volume definition title function returns a single 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 (a table is returned in the form of an embedded 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 ('softwary ')
-- User-Defined Function (Multi-statement Table value function, returns a table of some 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
-- Call this method Function
Select * from dbo. f_stuTye ('softwary ')
