SQL Server高级编程

SQLserver高级编程
1、数据库设计
数据库设计的重要性：
减少冗余，提高性能、易维护

数据库设计的步骤：
1、收集信息、标识对象、标识属性、标识关系（一对一、一对多、多对一、多对多）

E-R图：
属性：定义实体的性质、实体的特征

实体：数据项（属性）的集合

关联：实体之间相互连接的方式

简单理解一下就可以了

数据库规范化：
第一范式（1NF）：
每列都应该是原子性的，五重复的域

第二范式（2NF）：
在第一范式的基础上属性完全依赖于主键

第三范式（3NF）：
第三范式要求各列与主键列直接相关

T-SQL语句创建和管理数据库和表：
T-SQL创建数据库：

if DB_ID('数据库名') is not null
drop database 数据库名
go
create database 数据库名
on
(
name='数据库名',
filename='物理数据库储存路径数据库文件'
)
1
2
3
4
5
6
7
8
9

案例:

if DB_ID('Student')is not null
drop databese Student
go
create databese Student
on
(
name='Student',
finema='E:\第二学期\SQL\stuDB\Student.mdf'
)
1
2
3
4
5
6
7
8
9

数据文件参数 描述
name 数据库逻辑名称
filename 数据库物理文件名
size 数据文件初始化大小，单位默认为M
maxsize 数据文件可增长到最大值，单位默认阿M，不指定即无限大
filegrowth 数据库每次增长率，可以是百分比，默认单位M，0不增长
T-SQL语句创建表：

if object_ID('表名')is not null
drop table 表名
go
create table 表名
(
字段1 数据类型 列的特性，
字段2 数据类型 列的特性
)
1
2
3
4
5
6
7
8

案例：

if object_ID('StuInfo')is not null
drop table StuInfo
go
create table StuInfo
(
StuId int identity(1,1) primary key,
StuName varchar(10) not null,
StuSex varchar(2) not null,
StuAge varchar(3) not null
)
1
2
3
4
5
6
7
8
9
10

T-SQL创建约束：
主键约束：（primary key constraint）：主键列数据唯一，并不为空，简称：PK

唯一约束：（unique constraint）：保证该列不允许除夕重复值，简称：UQ

检查约束：（check constraint）：限制列中允许的取值以及多个列直接的关系，简称：CK

默认约束：（default constraint）：设置某列的默认值，简称：DF

外键约束：（foreign key constraint）：用于在两个表之间建立关系，需要指定主从表，简称：FK

T-SQL添加约束的语法格式：
alter table 表名

add constraint 约束名 约束类型 具体的约束说明

T-SQL删除约束：
alter table 表名

drop constraint 约束名

案例：
复制代码
–添加主键约束（将StuNo设为主键）
alter table StuInfo add constraint PK_StuNO primary key (StuNo)
go
–添加默认约束（性别默认为男）
alter table StuInfo Add constraint DF_StuSex DEFAULT (‘男’)for StuSex
go
–添加检查约束（年龄必须为40之间）
alter table StuInfo Add constraint CK_StuAge check(StuAge>=18 and StuAge<=40)
go
–添加外键约束
alter table Exam Add constraint FK_StuNo FORELGN KEY (StuNo)references StuInfo(StuNo)
go
复制代码
（1）对表结构的操作
1、在表中添加一列

语法：alter table 表名

add 添加的列名 数据类型

例子：在Student表中添加列Hobbies,类型为varchar,宽度：20

    alter  table  Student  
1

add Hobbies varchar(20)
2、删除表中的一列

语法：alter table 表名

drop column 列名

例子：删除Student表中的Hobbies列

alter table Student

drop column Hobbies

3、修改表中列的数据类型

语法：alter table 表名

alter column 列名 修改后的数据类型 not null

例子：修改Student中的Sex列为char型，宽度为2

alter table Student

alter column Sex char(2) nou null

(2)添加约束
1、添加主键约束
语法：alter table 表名
add constraint 约束名 primary key(要设置主键的列名)
例子：
给Class表添加主键约束
if OBJECT_ID(‘PK_ClassId’) is not null
alter table Class
drop constraint PK_ClassId
go
alter table Class
add constraint PK_ClassId primary key(ClassId)

2、添加唯一约束
语法：alter table 表名
add constraint 约束名 unique(要添加唯一约束的列名)
例子：
给信息表stuInfo中的姓名添加唯一约束
if OBJECT_ID(‘UQ_StuName’) is not null
alter table StuInfo
drop constraint UQ_StuName
go
alter table StuInfo
add constraint UQ_StuName
unique(StuName)

3、添加默认约束
语法：alter table 表名
add constraint 约束名 Default(默认值) for 要添加默认值的列名
例子：
给stuInfo表中的Age列添加默认值为18
if OBJECT_ID(‘DF_Age’) is not null
alter table StuInfo
drop constraint DF_Age
go
alter table stuInfo
add constraint DF_Age Default(18) for Age

4、添加检查约束
语法：alter table 表名
drop constraint 约束名
check(列名>=0)
例子：
给笔试成绩添加一个约束，要求成绩必须在0-100之间
if OBJECT_ID(‘CK_WriteExam’) is not null
alter table Exam
drop constraint CK_WriteExam
go
alter table Exam
add constraint CK_WriteExam
check(WriteExam>=0 and WriteExam<=100)

5、外键约束
语法：alter table 表名1
add constraint 约束名
foreign key(外键约束名)
references 表名2(外键约束名)
例子：
给班级表与学员信息表创建关系（外键约束）
if OBJECT_ID(‘FK_Class_StuInfo’) is not null
alter table stuInfo
drop constraint Fk_Class_StuInfo
go
alter table stuInfo
add constraint Fk_Class_StuInfo
foreign key(ClassId)
references Class(ClassId)

–删除约束
Alter table 表名
Drop ConStraint 约束名

–删除表
Drop table 表名

(3)高级查询语法格式
–内连接
语法：
select 要查询的属性
from 表1 inner join 表2
on 表1.Id=表2.Id
where 要限制的条件（可以不要）

–左外连接
语法:
select 要查询的属性
from 表1 left outer join 表2
on 表1.id=表2.id

–右外连接
语法：
select 要查询的属性
from 表1 right outer join 表2
on 表1.id=表2.id

–全外连接
语法：
select 要查询的属性
from 表1 full outer join 表2
on 表1.id=表2.id

–交叉连接
语法：
select 要查询的属性
from 表1 Cross join 表2
where 条件

–自连接
select 要查询的属性
from 表1 , 表2
where 表1.id=表2.id

(4)高级查询实例
if DB_ID(‘GoodSystem’) is not null
drop database GoodSystem
go
create database GoodSystem on --创建一个商品数据库
(
name=‘GoodSystem’,
filename=‘E:\SQL\第二章上机任务\GoodSystem.mdf’
)
–打开数据库
use GoodSystem
–创建商品类型表GoodsType
if OBJECT_ID(‘GoodType’) is not null
drop table GoodType
go
create table GoodType
(
Tid int primary key,
Type varchar(20)
)
–创建商品信息表Goods
if OBJECT_ID(‘Goods’) is not null
drop table Goods
go
create table Goods
(
id int primary key,
Tid int ,
Name varchar(50),
Price money,
ProductionDate datetime,
Amount int
)
–给商品类型表GoodsType添加测试数据
insert GoodType select ‘1’,‘家电’ union
select ‘2’,‘电子’ union
select ‘3’,‘食品’ union
select ‘4’,‘生活用品’

–给商品信息表Goods添加测试数据
insert Goods select ‘1’,‘1’,‘冰箱’,‘3344’,‘2017-6-3’,‘100’ union
select ‘2’,‘1’,‘电视’,‘1777’,‘2016-10-4’,‘100’ union
select ‘3’,‘1’,‘微波炉’,‘333’,‘2017-2-26’,‘100’ union
select ‘4’,‘2’,‘手机’,‘4500’,‘2017-5-7’,‘100’ union
select ‘5’,‘2’,‘显示器’,‘1777’,‘2016-12-4’,‘100’ union
select ‘6’,‘2’,‘主机’,‘1500’,‘2017-3-9’,‘100’ union
select ‘7’,‘3’,‘老干妈’,‘9’,‘2017-7-6’,‘100’ union
select ‘8’,‘3’,‘爽口榨菜’,‘3.6’,‘2017-6-8’,‘100’

–查询商信息表中的商品名称，价钱，生产日期。
select Name 商品名称,Price 价钱,ProductionDate 生产日期
from Goods
–查询商品类型、商品名称、价钱、生产日期
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price 商品价钱,gs.ProductionDate 生产日期
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid
–查询生产日期为2017的商品类型、商品名称、价钱、生产日期
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price 价钱,gs.ProductionDate 生产日期
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid and year(gs.ProductionDate) like ‘2017%’
–查询商品类型为“电子”的商品类型、商品名称、价钱、生产日期，数据按价钱降序排列
select gt.Type 商品类型,gs.Name 商品名称,gs.Price 价钱 ,gs.ProductionDate 生产日期
from GoodType gt inner join GoodS gs
on gt.Tid=gs.Tid and gt.Type=‘电子’
order by Price desc

–统计每种商品类型的商品数量
select gt.Type 商品类型, COUNT(gs.Amount) 商品数量
from GoodType gt inner join Goods gs
on gt.Tid=gs.Tid
group by gt.Type

–查询所有商品类型对应的所有商品信息
select gt.Type 商品类型,gs.Name 商品名称 , gs.Price 商品价钱,gs.ProductionDate 生产日期
from GoodType gt left join Goods gs
on gt.Tid=gs.Tid

–查询价钱是333、1500、4500的商品信息
select Type 商品类型,Name 商品名称,Price 商品价钱,ProductionDate 生产日期
from GoodType gt ,Goods gs
where Price in(333,1500,4500)
and (gs.Tid=gt.Tid)

–查询没有对应商品信息的商品类型信息（类型编号，类型名称）
select Tid 编号,Type 类型 from GoodType
where Tid not in
(select Tid from Goods)

–查询所有商品的平均价钱
select AVG(Price) 平均价钱 from Goods

–查询价钱大于平均价钱的商品信息
select Type 商品类型,Name 商品名称,Price 商品价钱,ProductionDate 生产日期
from GoodType gt , Goods gs
where gs.Tid=gt.Tid
and Price>
(select AVG(Price) 平均价钱 from Goods)

–查询每种商品类型平均价钱超出总平均价钱的类型名称、平均价钱
select gt.Type 类型名称,AVG(Price) 价钱
from Goods gs,GoodType gt
where gs.Tid=gt.Tid
and
gs.Tid in
(select gs.Tid
from Goods
group by Tid
having AVG(Price)>
(select AVG(Price) 平均价钱 from Goods))
group by gt.Type
–查询所有商品总金额超20万的商品类型、名称、总金额
select Type 商品类型 ,Name 名称 ,PriceAmount 总金额
from GoodS gs , GoodType gt
where gs.Tid=gt.Tid
and PriceAmount>200000
–库存报警（低10）

–延迟到14:03:00 执行查询
waitfor time ‘14:03:00’
select*from Goods
–延迟两秒执行操作
waitfor delay ‘00:00:02’
select *from Goods

(5)变量函数
–常用的数学函数
–1、ABS 求绝对值 交易之前-交易之后
–2、POWER 求次方
select POWER(2,10),POWER(2.0000,0.5) --1kb 1024
select POWER(2.0000000,1.000000/3)
–3、求圆周率
select PI()
–4、Rount() --四舍五入函数
select ROUND(315.4567,2),Round(315.4567,0),Round(345.4567,-2)
–5、ceiling --取比原数大的整数
–6、floor --取比原数小的整数
select CEILING(3.00000000001),FLOOR(3.99999999)
–7、ASCII 返回一个字符的ASCII码值
select ASCII(‘A’)
–8、Rand() 返回一个0-1之间的随机数
–select nCHAR(214)+nCHAR(208)
select RAND()
select RAND(DATEPART(ss,GetDate())*2)–这样打变化频率小
go

–产生一个银行卡号，前8位为’6225 3800’ 后8位为随机数字，
–请用T-SQL编码完成
–select RAND()
declare @rand numeric(20,8)
select @rand=RAND()
print @rand
declare @s varchar(16)
set @s=SUBSTRING(STR(@rand,10,8),3,8)
print @s
set @s=‘62253800’+@s
print ‘你的新银行卡号为：’+@s

–日期函数
–1、getdate()：返回服务器上的当前时间
select GETDATE()
–2、datepart：返回一个日期的一部分值（整形）
–3、datename：返回一个日期的一部分值（字符串）
–返回一周的第几天（星期天是第一天）
select DATEPART(DW,GETDATE()),DATENAME(DW,GETDATE())
–返回一年的第几周
select DATEPART(WEEK,GETDATE()),DATENAME(WEEK,GETDATE())
–4、datediff 日期比较函数
–返回每个交易已经发生了多少天
select DATEDIFF(DD,transdate,GETDATE()) from TransInfo

–字符串函数
–1、LEN：返回一个字符串的字符数
select LEN(‘中国’),LEN(‘abc123!’)
select LEN(‘abc ‘),LEN(’ abc’)–数据类型为varchar类型，会自动消除没字符连接的空格

–2、dataLength：返回一个字符串的字节数
select dataLength(‘中国’),dataLength(‘abc123!’)
select RIGHT(‘abcdef’,3),LEFT(‘abcdef’,3)
–4、substring：字符串截取函数
select SUBSTRING(‘abcdef’,2,3)–和C#不一样，下标从一开始
–5、charIndex：字符串查找函数
select charIndex(‘a’,‘bcad’,1)
–6、upper：字母大写转换函数
–7、lower：字母小写转换函数
select UPPER(‘abc123’),LOWER(‘abCCC123中！’)
–8、space：产生空格函数
select len(‘abc’+SPACE(10) +‘123’) ,‘abc’+SPACE(10) +‘123’ --len是测定总长度
–9、replicate：字符串重复函数
select REPLICATE(‘abc’,3)
–10、replace:字符替换函数
select REPLACE(‘11111111’,‘1’,‘o’)–将1替换为o
select REPLACE(‘o0o0o0o0000oo’,‘0’,‘o’),‘00000000’–将0替换为o
select REPLACE( REPLACE(‘0o0oil0oillil10ol1’,‘l’,‘1’),‘0’,‘o’)
–11、stuff：字符替换函数
select STUFF(‘湖南武汉’,2,1,‘北’)
select STUFF(‘中国长沙’,3,0,‘湖南’)–输出中国湖南长沙
–12、ltrim和rtrim：去掉字符串左边或右边的全部空格
select len(ltrim(’ a bc’))
–13、str：将数值转换为字符串函数
select STR(12345.65,8,2)
–14、char：将一个ASCII码值转换为一个字符
select CHAR(97),ASCII(‘a’)

(6)数据库变量格式
use StuDB
selectfrom StuInfo
selectfrom Exam
–总学生人数 参考人数 及格人数 未及格人数 及格率
declare @total int,@sum int,@pass int
select @total=COUNT() from StuInfo --统计总人数
select @sum=COUNT() from Exam --统计参考人数
select @pass=COUNT(*) from Exam
where WriteExam>=60 and LadExam>=60 --统计及格人数

select @total 总人数,@sum 参考人数,@pass 及格人数 ,
@total-@pass 未及格人数,CONVERT(varchar(20),
ceiling( @pass1.0/@total10000)/100)+‘%’ 及格率

select*from StuInfo
–查看上一个错误的编号
select @@ERROR

selectfrom StuInfo
selectfrom Class
select @@IDENTITY
insert Class values(‘s149’)

–获取上一次SQL指令影响的命令行数
select @@ROWCOUNT

–判断删除是否成功
–1、直接删除
–2、先查询，然后再删除，再查询

–当前SQL服务器名称，当前服务名称
select @@SERVERNAME ,@@SERVICENAME

–显示当前打开的事务数
select @@TRANCOUNT

–显示当前服务器允许的最大连接数
select @@MAX_CONNECTIONS

–显示当前使用的语言
select @@LANGUAGE

print ‘当前服务器名称:’ +@@servername
print ‘当前服务名称:’ +@@servicename
print ‘错误编号：’ +convert(varchar(6), @@error)

–显示笔试平均成绩，再根据平均成绩显示相应信息
declare @avg float
select @avg=AVG(writeExam) from Exam
print ’ ------成绩信息如下：--------’
print’全校平均成绩：‘+convert(varchar(20),@avg)
if @avg>=70
begin
print’成绩优秀！’
–显示前三名的学员信息
select top 3 StuName,si.StuId,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam desc --笔试降序
end
else
begin
print’成绩比较差！’
–显示后三名的学员信息
select top 3 StuName,si.StuId,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam asc --笔试降序
end

–对全班学员进行提分，保证每位同学的笔试成绩全部通过
while(1=1) --永真循环
begin
declare @count int --保存未通过的人数
–统计为通过的人数
select @count=Count(*) from Exam where writeExam<60
if (@count=0)
begin
break --终止循环
end
–进行加分
update Exam set WriteExam=100 where writeExam>=98
update Exam set WriteExam=WriteExam+2 where WriteExam<98

end
print’----------加分后的学员成绩如下：--------------’
select*from Exam

–显示学员笔试成绩的等级制
–90以上：优秀，80-90：良好，70-80：中等，60-69 ：一般
–60以下：不及格
select ExamNo 考号,StuId 学号,WriteExam 笔试成绩 ,LadExam 机试成绩,
等级=
case
when writeExam>=90 then ‘优秀’
when writeExam>=80 then ‘良好’
when writeExam>=70 then ‘中等’
when writeExam>=60 then ‘一般’
else ‘不及格’
end
from Exam

–显示所有学员打的姓名，性别，年龄，笔试成绩，机试成绩
–没有成绩的学员显示缺考
select stuName,sex,Age,WriteExam=
case
when WriteExam IS null then ‘缺考’
else CONVERT(varchar(20),writeExam)
end,
LadExam=
case
when LadExam IS null then ‘缺考’
else CONVERT(varchar(20),ladexam)
end
from StuInfo si left join Exam e
on si.StuId=e.StuId

–未参加考试的学员成绩为0
select stuName ,sex ,ISNULL(age,18),
ISNULL(WriteExam,0),
ladexam=
case
when ladexam IS null then 0
else ladexam
end
from StuInfo si left join Exam e
on si.StuId=e.StuId

(7)索引、视图、事务

–创建索引
语法：
if exists(select*from sys.indexes
where name=‘IX_stuinfo_AgeName’)
drop index stuinfo.IX_stuinfo_AgeName
go
create nonclustered index 索引名
on 表名（按某列升序或降序）
例子：
create nonclustered index IX_stuinfo_AgeName
on stuinfo(age,stuname desc)
备注：列名后加 desc 是降序的意思，不加默认升序
备注2：nonclustered表示创建非聚集索引 还有如：unique表示创建唯一性索引，clustered 表示创建聚集索引

–使用索引
语法：
selectfrom 有该索引的表名
with (index=索引名)
例子：
使用索引IX_stuinfo_AgeName 查询学员信息
selectFrom StuInfo
with(index=IX_Stuinfo_AgeName)

–索引的优点和缺点
优点：
1、加快访问速度
2、加强行的唯一性
缺点：
1、带索引的表在数据库中需要更多的存储空间
2、更新数据的命令需要更长的处理时间，因为它们需要对索引进行更新

–创建视图
语法：
create view 视图名
as
select 列名 from 表1，表2
where 表1.id =表2.id order by 条件
例子：
创建一个视图：获取学员的姓名、性别、年龄、笔试成绩、机试成绩、并且按笔试成绩降序排序
if exists(select*from sys.views
where name=‘VW_Stu’)
drop view VW_Stu --有相同视图则删除原视图
go
create view VW_Stu
as
select top 100 stuName,Sex,Age,WriteExam,LadExam
from StuInfo si,Exam e
where si.StuId=e.StuId
order by WriteExam Desc
go
–视图的使用
select *from VW_Stu

–事务
事务的ACID属性
1、原子性
一个事务对数据库的所有操作，是一个不可分割的工作单元。这些操作要么全部执行，要么什么也不做。保证原子性是数据库系统本身的职责，由DBMS的事务管理子系统来实现。

2、一致性
一个事务独立执行的结果应保持数据库的一致性，即数据不会因为事务的执行而遭受破坏。确保单个事务的一致性是编写事务的应用程序员的职责。在系统运行时，由DBMS的完整性子系统执行任务。

3、隔离性
在多个事务并发执行时，系统应保证这些事务先后单独执行时的结果一样，此时称事务达到了隔离性的要求，也就是在多个并发事务执行时，保证执行结果是正确的，如同单用户环境一样。隔离性是由DBMS的并发控制子系统实现的。
4、持久性

语法：
–开启事务
begin transaction
declare @error int --定义变量，记录错误
set @error=0 --默认无错
Update bank set Blance=Blance+5000 where Bname=‘join’
set @error=@errror+@@ERROR
Update bank set Blance=Blance-5000 where Bname=‘jack’
set @error=@errror+@@ERROR
if(@error<>0) --如果错误号不为零，说明有操作出错
begin
raiserror(‘转账过程出错’,10,1)
rollback --回滚全部操作
end

else
begin
print ‘恭喜你，转账成功！’
commit --提交所有操作
end

实例：
–转账事务，转账900
begin tran
declare @err int=0 --声明一个变量，初值为0
update Bank set Cmoney=Cmoney-900 where Cname=‘张三’
set @err=@err+@@ERROR
update Bank set Cmoney=Cmoney+900 where Cname=‘李四’
set @err=@err+@@ERROR
if @err>0 --条件
begin
print’交易失败，事务回滚！’
rollback
end
else
begin
print’交易成功，事务提交’
commit tran
end

(8)存储过程
–执行dos命令的存储过程CMDShell
–在d盘根目录下创建一个文件夹
execute xp_cmdshell ‘md D:\DB’
–查看D盘下的所有信息
exec xp_cmdshell ‘dir d:’
–查看视图VW_Stu的源代码
exec sp_helptext ‘VW_Stu’
–查看一个表的索引
exec sp_helpindex ‘stuInfo’
–查看一个数据库中的存储过程
exec sp_stored_procedures
–进行数据库的逻辑名称改名（显示名称）
exec sp_renamedb BankDBs,BankDB–主文件名是不会变的
–查看当前数据库中的表和视图情况
exec sp_tables

–将网格显示改为文本格式显示

use StuDB
–创建一个存储过程
–显示机试和笔试的平均成绩，并且显示本次考试的成绩情况
–还要显示未通过的学员信息
if exists(select*from sys.procedures
where name=‘ScoreCountl’)
drop procedure ScoreCountl
go
create procedure ScoreCountl
as
declare @write decimal,@lab decimal
select @write=AVG(writeExam),@lab=AVG(LadExam)
from Exam
print ‘笔试成绩:’+convert(varchar(20),@write)
print ‘机试成绩:’+convert(varchar(20),@lab)
if @write>=70 and @lab>=70
print’本班考试成绩优秀！’
else
print’本班考试成绩一般！’

print’-------------------------------------------------’
print’----------参加本次考试没有通过的学员名单-----------’
select stuname 姓名,si.StuId 学号,WriteExam 笔试成绩,LadExam 机试成绩
from StuInfo si,Exam e
where si.StuId=e.StuId and ( WriteExam<60 or LadExam<60)

go
exec ScoreCountl

–自由调控及格线
if exists(select*from sys.procedures
where name=‘ScoreCount2’)
drop procedure ScoreCount2
go
create procedure ScoreCount2
@w decimal=60,–笔试及格线
@l decimal=60 --机试及格线，加了默认值60
as
declare @write decimal,@lab decimal
select @write=AVG(writeExam),@lab=AVG(LadExam)
from Exam
print ‘笔试成绩:’+convert(varchar(20),@write)
print ‘机试成绩:’+convert(varchar(20),@lab)
if @write>=70 and @lab>=70
print’本班考试成绩优秀！’
else
print’本班考试成绩一般！’

print’-------------------------------------------------’
print’----------本次笔试及格线：‘+convert(varchar(20),@w)
+’----------本次机试及格线：‘+convert(varchar(20),@l)
print’----------参加本次考试没有通过的学员名单-----------’
select stuname 姓名,si.StuId 学号,WriteExam 笔试成绩,LadExam 机试成绩
from StuInfo si,Exam e
where si.StuId=e.StuId and ( WriteExam<@w or LadExam<@l)
go
exec ScoreCount2 @l=40,@w=60 --分别为机试和笔试的及格线
exec ScoreCount2 --有默认值的情况下

use GoodSystem
–创建一个存储过程：查询生产日期为某年的
–商品名称，类型，生产日期，库存
if exists(select*from sys.procedures
where name=‘ScoreCount3’)
drop procedure ScoreCount3
go
create procedure ScoreCount3
@d decimal
as
select Name 商品名称,gt.Type 类型,ProductionDate 生产日期,Amount 库存
from GoodS gs,GoodType gt
where gs.Tid=gt.Tid and year(ProductionDate)=@d
–查询2017年的商品信息
go
exec ScoreCount3 2017

–创建一个存储过程：查询类型为‘食品’的商品信息
if exists(select*from sys.procedures
where name=‘ScoreCount4’)
drop procedure ScoreCount4
go
create procedure ScoreCount4
@s varchar(20)
as
select Name 商品名称,gt.Type 类型,ProductionDate 生产日期,Amount 库存
from GoodS gs,GoodType gt
where gs.Tid=gt.Tid and gt.Type=@s

go
exec ScoreCount4 ‘食品’

(9)带输出参数的存储过程
–创建一个存储过程：添加一个学员成绩信息
if OBJECT_ID(‘InsertExam’) is not null
drop proc InsertExam
go
create proc InsertExam
@ExamNo int,
@stuid varchar(50),
@writeExam decimal(10,2),
@ladExam decimal(10,2),
@n int output
as
insert Exam(ExamNo,StuId,WriteExam,LadExam)
values(@ExamNo,@stuid,@writeExam,@ladExam)
select @n=@@ROWCOUNT

go
select*from Exam
–调用添加存储过程
declare @n int
exec InsertExam ‘8’,‘1006’,70,80,@n output
if @n>0
print’添加成功’
else
print’添加失败’

use MySchool
select*from Teacher
–引用到C#案例MySchool_Proc
–添加教员
if OBJECT_ID(‘InsertTeacher’) is not null
drop proc InsertTeacher
go
create proc InsertTeacher
@LoginId varchar(50),
@LoginPwd varchar(50),
@TeacherName varchar(50),
@Sex varchar(50),
@UserStateId int,
@Birthday dateTime,
@n int output
as
insert Teacher(LoginId,LoginPwd,TeacherName,Sex,UserStateId,Birthday)
values(@LoginId,@LoginPwd,@TeacherName,@Sex,@UserStateId,@Birthday)
select @n=@@ROWCOUNT
go

–修改教员
if OBJECT_ID(‘UpdateTeacher’) is not null
drop proc UpdateTeacher
go
create proc UpdateTeacher
@LoginId varchar(50),
@LoginPwd varchar(50),
@TeacherName varchar(50),
@Sex varchar(50),
@UserStateId int,
@Birthday dateTime,
@n int output
as
update Teacher set LoginPwd=@LoginPwd, TeacherName=@TeacherName,
Sex=@Sex,UserStateId=@UserStateId,Birthday=@Birthday
where LoginId=@LoginId
select @n=@@ROWCOUNT
go
–删除教员
if OBJECT_ID(‘DeleteTeacher’) is not null
drop proc DeleteTeacher
go
create proc DeleteTeacher
@LoginId varchar(50),
@n int output
as
delete from Teacher where LoginId=@LoginId
select @n=@@ROWCOUNT
–自我理解
–添加、修改、删除存储过程都是为了应用到C#程序中可以更快的执行，且占系统运行内存不多。
–C#中的调用其实就是一个三层架构