SQL相关知识复习，比较难的几个查询

create database book3
go
use book3
go
/*学生基本信息表*/
create table StuInfo
(
StuID int primary key, --学号
StuName varchar(10),--姓名
StuSex bit --性别，1为男，0为女
)
go
/*分数表*/
create table StuMarks
(
ExamNO int, --准考证号
StuID int references StuInfo(StuID), --学号
Score int --成绩
)
go
insert into StuInfo values(1,'唐僧',1)
insert into StuInfo values(2,'孙悟空',1)
insert into StuInfo values(3,'猪八戒',1)
insert into stuinfo values(4,'沙僧',1)


insert into StuMarks values(1,1,88)
insert into StuMarks values(2,1,92)
insert into StuMarks values(3,2,53)
insert into StuMarks values(4,2,60)
insert into StuMarks values(5,2,99)
insert into StuMarks values(6,3,82)
select * from stumarks


go




--.请同学们看书完成以下查询功能：（能做多少是多少）




--1.查询全班所有人的平均分（结果只有1行1列）
select avg(score) as'全班平均分' from stumarks


--2查询全班每个人的平均分（结果只有1列）
--方法.1
select *,(
select avg(score) from  stumarks where stuinfo.stuid=StuMarks.stuid)as '平均分'
from stuinfo
--方法.2
select stuinfo.*,newtable.平均分 from stuinfo,(
select stuid,avg(score)as '平均分' from stumarks group by stuid)as newtable
where stuinfo.stuid=newtable.stuid
--方法3
select *,(select avg(score) from  stumarks where stuinfo.stuid=StuMarks.stuid )as 平均分 from stuinfo


--3查询孙悟空的平均成绩（结果只有1行1列）
select avg(score)as'孙悟空的平均成绩'from stumarks 
where stumarks.stuid=(select stuid from stuinfo where stuname='孙悟空')
--4查询全班每一位同学的学号、姓名、性别、准考证号和平均成绩
--方法1
select stumarks.examno,newtable2.* from stumarks,
(select * ,(select avg(score)from stumarks where stuinfo.stuid=stumarks.stuid)as'平均分'
from stuinfo)as newtable2
where stumarks.stuid=newtable2.stuid
--方法2
select distinct stuinfo.*,stumarks.examno,table1.平均分 from stuinfo,stumarks,
(select stuid,avg(score)as '平均分' from stumarks group by stuid)
 as table1 where stuinfo.stuid=table1.stuid and stuinfo.stuid=stumarks.stuid
--方法3(此方法显示出了沙僧的信息)
select stumarks.examno,newtable2.* from stumarks right outer join 
 (select * ,(select avg(score)from stumarks where stuinfo.stuid=stumarks.stuid)as'平均分'
from stuinfo)as newtable2 on newtable2.stuid=stumarks.stuid
--5查询孙悟空的学号、姓名、性别、准考证号和平均成绩
select stuinfo.*,stumarks.examno,table2.平均分 from stuinfo,stumarks,
(select stuid ,avg(score)as '平均分' from stumarks group by stuid 
having stumarks.stuid=(select stuid from stuinfo where stuname='孙悟空'))as table2 
where stuinfo.stuid=table2.stuid and stuinfo.stuid=stumarks.stuid


--6查询平均成绩比孙悟空的平均成绩低的那些学生的学号、姓名、性别、和平均成绩
--方法1(此方法显示出了沙僧的信息)
select * from (select * ,
平均分=(select avg(score) from  stumarks where stuinfo.stuid=StuMarks.stuid)
from stuinfo )as newtable 
where newtable.平均分<
(select avg(score)from stumarks 
where stumarks.stuid=(select stuid from stuinfo where stuname='孙悟空'))
or newtable.平均分 is null


--方法2
select stuinfo.*,table2.平均分 from stuinfo,
(select * from (select stuid,avg(score)as '平均分' from stumarks group by stuid)as newtable
 where newtable.平均分<
(select avg(score)from stumarks where stumarks.stuid=(select stuid from stuinfo where stuname='孙悟空'))
)as table2 where stuinfo.stuid=table2.stuid 




--7查询比全班所有人的平均分高的那些同学的学号、姓名、和平均成绩（只要有1次考试的结果比全班的平均分高，这样的学生都应该查询出来）
select * from (select * ,
平均分=(select avg(score) from  stumarks where stuinfo.stuid=StuMarks.stuid)
from stuinfo )as newtable 
where newtable.平均分>
(select avg(score) as'全班平均分' from stumarks)


/*以上是从两个表中查询，有一点复杂
 下面是更简单的查询方法，先将两表合
 并成一个新表，再从新表中查询。合并
 的代码如下，具体查询请同学自己完成*/


select a.*,b.examno,b.score from stuinfo a left join stumarks b on a.stuid=b.stuid
identity
use master
go
if exists(select * from sysdatabases where name='bankdb')
drop database bankdb
go
exec xp_cmdshell 'md d:\bankproject',no_output


create database bankdb
go
use bankdb
go
--帐户信息表
create table accountinfo
(
custid int primary key identity(1,1),
custname varchar(20),
idcard varchar(18),
telephone varchar(13),
address varchar(50)default('地址不详')
)
go
--信用卡信息表
create table cardinfo
(
cardid varchar(19)primary key,
cardpassword varchar(6)default('888888'),
custid int references accountinfo(custid),
savetype varchar(10),
opendate datetime default(getdate()),
openmoney money check(openmoney>=1),
leftmoney money check(leftmoney>=1),
isloss varchar(2)default('否')
)
go
--交易信息表
create table transinfo
(
cardid varchar(19),
transtype varchar(4),
transmoney money,
transdate datetime default(getdate())
)
go


insert into accountinfo values('孙悟空','42322001502110017','027-88888888','花果山')
insert into accountinfo values('唐僧','42322001902140019','027-88888888','大唐')
insert into accountinfo values('沙僧','42322001572144719','027-88888888','通天河')
select * from accountinfo
go
insert into cardinfo values('1027 3526 1536 1135',default,1,'定期',default,500,500,'否')
insert into cardinfo values('1029 3326 1536 1235',default,2,'活期',default,1500,1500,'否')
insert into cardinfo values('1324 3626 7532 1935',default,3,'活期',default,4500,4500,'否')
select * from cardinfo
go
--孙悟空改密码
update cardinfo set cardpassword='611234'where cardid='1027 3526 1536 1135'
insert transinfo values('1027 3526 1536 1135','支取',200,default)
insert transinfo values('1324 3626 7532 1935','存入',1200,default)
--更改帐户表里的余额
update cardinfo set leftmoney=leftmoney-200 where cardid='1027 3526 1536 1135'
update cardinfo set leftmoney=leftmoney+1200 where cardid='1324 3626 7532 1935'
go
--唐僧挂失
update cardinfo set isloss='是'where cardid='1029 3326 1536 1235'
go
--查询交易金额最大的信息
declare @transmoney money
select @transmoney=max(transmoney) from transinfo
select * from cardinfo where cardid in(select cardid from transinfo 
where transmoney=@transmoney )


--查询交易金额
declare @toale money --定义变量存放交易总金额
declare @zhiqu money　--定义变量存放支出金额
declare @cunru money　--定义变量存放存入金额
select @toale=sum(transmoney)from transinfo
select @zhiqu=transmoney from transinfo where transtype='支取'
select @cunru=transmoney from transinfo where transtype='存入'
go
--打印数据
print'----------------'
print'总交易金额：'+convert(varchar(10),@toale)
print'----------------'
print'支取金额：'+convert(varchar(10),@zhiqu)
print'----------------'
print'存入金额：'+convert(varchar(10),@cunru)
print'----------------'
go
--给交易信息表的列加非聚集索引
create nonclustered index IX_cardid
on transinfo(cardid)
go
--drop index transinfo.IX_cardid
--select * from with(index=IX_cardid) where cardid='1324 3626 7532 1935'
select * from transinfo where cardid='1324 3626 7532 1935'
go
--查询挂失的帐户信息
select * from accountinfo where custid in(select custid from cardinfo where isloss='是' )
go
--创建帐户信息视图
create view view_accountinfo
as
select a.custid as'帐户编号',a.custname as'帐户姓名',a.idcard as'身份证号码',a.telephone as'客户电话',a.address as'客户地址',b.cardid as'信用卡编号' ,b.savetype as'储蓄类型',b.opendate as'开户日期',b.openmoney as'开户金额',b.isloss as'是否挂失'
 from accountinfo a,cardinfo b where a.custid=b.custid
go
--drop view view_accountinfo
select * from view_accountinfo
--创建交易信息视图
create view view_transinfo
as
select a.cardid as'卡号',a.transtype as'交易类型',a.transmoney as'交易金额',a.transdate as'交易时间' from transinfo a
go
select * from view_transinfo
go
--使用触发器
--drop trigger tri_transinfo_insert
create trigger tri_transinfo_insert 
on transinfo
for insert
as
declare @temptranstype varchar(10)--定义临时变量存放交易类型
declare @temptrnasmoney money　--定义临时变量存放交易金额
declare @tempcardid varchar(19)--定义临时变量存放卡号
declare @templeftmoney money　--定义临时变量存放客户的余额


select @templeftmoney=lefrmoney from cardinfo where cardid=@tempcardid
if(@temptranstype='支取')
begin
if(@templeftmoney-@temptranmoney<1)
begin
raiserror('交易金额已经超过余额，交易失败！',17,1)
return
end
else
begin
select @tempcardid=cardid,@temptranstype=transtype,@temptranmoney=transmoney from inserted
update cardinfo set leftmoney=leftmoney-@temptranmoney where cardid=@tempcardid
end
end
else
begin
select cardid=@tempcardid,transtype=@temptranstype,transmoney=@temptranmoney from inserted
update cardinfo set leftmoney=leftmoney+@temptranmoney where cardid=@tempcardid
    end
select @templeftmoney=leftmoney from cardinfo where cardid=@tempcardid--重新查询余额并赋值
print'交易成功！'
print'卡号:'+convert(varchar(19),@tempcardid)+'余额额:'+convert(varchar(10),@templeftmoney)
go
--测试触发器
insert into transinfo(cardid,transtype,transmoney)values('1324 3626 7532 1935','支取',500)
go
select * from transinfo
select* from cardinfo


--使用存储过程备份数据
create proc proc_backup_transinfo
@backupdate datetime　--定义备份的时间　输入参数
as
declare @myerror int　--定义变量存放Ｔ－ＳＱＬ语句执行的错误代码
set @myerror=0
print'开始备份.............'
if exists(select * from sysovjects where name='back_transinfo')
begin
begin transaction
inset into back_transinfo select * from transinfo where.....
else
begin transaction
select * into back_transinfo from transinfo where...
--测试备份存储过程
declare @backdate datetime
set @backdate=getdate()
exec proc_backup_transinfo @backdate
go
--产生随机卡号的存储过程
create proc proc_getcardid
@cardid varchar(19) output　--产生的编号作为输入参数
as 
declare @temprand numeric(18,16)--定义临时变量存放产生的随机数
declare @tempstr varchar(18)　　--将随机数转化为字符存放的变量


set @temprand=rand(datepart(ms,getdate())*10000)--利用随机函数产生随机数
set @tempstr=convert(varchar(18),@temprand)
set @cardid=substring(@tempstr,3,4)+' '+substring(@tempstr,7,4)+' '+
substring(@tempstr,11,4)+' '+substring(@tempstr,15,4)
go
--测试该存储过程
declare @mycardid varchar(19)


exec proc_getcardid @mycardid output
print'产生的随机卡号为：'+@mycardid
go
--开户的存储过程
create proc proc_openaccount
@custname varchar(20),--开户姓名
@idcard varchar(18),--开户身份证号
@telephone varchar(13)--电话号码
@openmoney money=1,--开户金额
@savetype varchar(10)='活期',--开户类型
@address varchar(50)='地址不详'--地址
as
declare @mycardid varchar(19)--定义存放随机卡号的变量
declare @mycustid int　--定义存放帐户编号的变量
exec proc_getcardid @mycardid output--使用产生随机卡号的存储过程


while(1=1) 　　　　　　--使用循环判断卡号是否存在
begin
if exists(select cardid from cardinfo)--如果产生的随机卡号已经存在，则继续产生下一个
exec proc_getcardid @mycardid output
else
break
end


--................................插入相关的信息
print'尊敬的客户：'+@custname+'开户成功，卡号为：'+@mycardid
go
print'产生的随机卡号为：'+@mycardid
go
--测试该存储过程
exec proc_openaccount '白骨精','245687556977812345','12478964568'
exec proc_openaccount '嫦娥公主','745687476912812335','14796653141',@address='月亮'
go
--销户存储过程
drop proc proc_dropaccount
create proc proc_dropaccount
@cardid varchar(19)
as
declare @tempcustid int--定义变量存放该卡号对应的帐户编号
declare @templeftmoney money--定义变量存放该客户的余额


select @tempcustid=custid from cardinfo where cardid='1029 3326 1536 1235'
select @templeftmoney=leftmoney from cardinfo where cardid='1029 3326 1536 1235'
print'该帐户的余额为：'+convert(varchar(10),@templeftmoney)+'正在取出...';
delete from cardinfo where custid=@tempcustid        --进行删除信息的操作
delete from accountinfo where custid=@tempcustid
print'删除成功'
--测试该存储过程删除唐僧的帐户信息
declare @cardid varchar(19)
select @cardid=cardid from cardinfo where custid in(select custid from accountinfo where custname='唐僧')
exec proc_dropaccount @cardid
--查看结果
select * from accountinfo
select * from cardinfo