--'xp_cmdshell', 1的时候xp_cmdshell才能使用
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE
dbcc addextendedproc("xp_cmdshell","xplog70.dll")
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
EXEC xp_cmdshell "dir d:/sql2005/"
系统存储过程 说明
sp_databases 列出服务器上的所有数据库。
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程。
sp_password 添加或修改登录帐户的密码。
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或
@@ERROR 最后一个T-SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SQL Server的版本信息
--4、 查询选修了全部课程的学生姓名；（提示：查询这样的学生，没有一门课程是他不选修的）；
set statistics time on ;
select sno,sname
from student
where sno in(select sno
from sc
group by sno
having count(cno)>=(select count(cno)
from course))
set statistics time on;
use student
select * from student
where not exists(
select * from course
where not exists( -- 没有一门课是不选的
select * from sc --返回结果为真
where sc.cno=course.cno
and sc.sno=student.sno
)
)
----5、 查询至少选修了 学生‘0011’选修的全部课程的学生号码
select sno from student as b
where not exists(
select cno from sc as a
where not exists( -- 没有一门课是不选的
select * from sc as c --返回结果为真
where c.sno=b.sno and c.cno=a.cno
) and sno='003'
) and b.sno<>'003'
--5、 查询比 学生‘0011’选修课多的的学生号码
set statistics time on
select sno,sname
from student
where sno in(
select sno
from sc
group by sno
having count(cno)>=(select count(cno)
from sc
where sno='003'))
-------------------
--1、 在超市的日常管理中，有时会搞不清某个上商品编号的准确的商品名称是什么，
--于是就希望给出一个商品编号值，能查出对应商品的商品名称。
use market
go
create procedure Msr_Plus_id
@plu_id int =1
as
select *from plus where plu_id=@plu_id
exec Msr_Plus_id 4
--2、 创建存储过程，输入商品的部分名字就可查出该类商品的销售价格总和；
use market
go
create procedure Msr_SUM_name
@plu_name varchar(100)
as
select *from plus where plu_name like '%'+@plu_name+'%'
exec Msr_SUM_name '糖'
--3、 创建一个带有输入参数的基于插入操作的存储过程，
--用于在商品信息中插入一条新的商品信息，商品信息由变量形式给出；
use market
go
create procedure Msr_input_data
@plu_id int,
@plu_name varchar(100),
@plu_depid int,
@plu_unitprce money,
@plu_merid int,
@plu_number varchar(100),
@plu_prce money
as
insert into plus (plu_id,plu_name,plu_depid,plu_unitprce,plu_merid,plu_number,plu_prce) values (@plu_id,@plu_name,@plu_depid,@plu_unitprce,@plu_merid,@plu_number,@plu_prce)
--4、 创建一个带有输入参数和输出参数的存储过程，
--输入参数用于指定查询的商品编号信息，输出参数用于保存指定商品编号的商品名称、
--部门编号、供应商编号、供应商名称等信息；
use market
go
create procedure Msr_id_out
@plu_id int,
@plu_name varchar(100) output ,
@plu_depid int output ,
@plu_merid int output,
@merid_name varchar(100) output
as
select @plu_name=plu_name,@plu_depid=plu_depid,@plu_merid=plu_merid ,@merid_name=mer_name
FROM plus,merchant
where mer_id=plu_merid and plu_id=@plu_id
declare @plu_name varchar(100)
declare @plu_depid int
declare @merid_name varchar(100)
declare @plu_merid int
exec Msr_id_out 6,@plu_name output,@plu_depid output,@plu_merid output,@merid_name output
print @plu_name
print @plu_depid
print @plu_merid
print @merid_name
--5、 创建一个带有输入参数的基于更新操作的存储过程，
--用于在商品信息表中将指定商品名称的销售价格介于5—6元之间的商品都提高到7元，
--商品名称由输入参数指定；
use market
go
create procedure Msr_value_name
@plu_name varchar(100)
as
update plus
set plu_prce=case
when plu_prce between 5 and 6 then 7
else plu_prce
end
where plu_name like '%'+@plu_name+'%'
exec Msr_value_name '糖精'
exec Msr_SUM_name '糖精'
update plus
set plu_price=case
when plu_price between 5 and 6 then 7
else plu_price
end
-------------------------
if exists(select * from sys.databases where name='xue')
drop database xue
go
create database xue
on
(
name='xue.mdf',
filename='d:/date/xue.mdf',
size=3,
maxsize=10
),
(
name='xue1.ndf',
filename='d:/date/xue1.ndf',
size=3,
maxsize=10
)
log on
(
name='xue_log.ldf',
filename='d:/date/xue_log.ldf',
size=3,
maxsize=10
)
--创建 文件
alter database xue
add file
(
name='xue2',
filename='d:/date/xue2.ndf',
size=3,
maxsize=10
)
use xue
alter database xue
remove file xue2 --移除文件
alter database xue
modify name=xue0 --更给数据库名称
go
create table stu
( sno int identity(1,1) primary key,
sname varchar(20) not null,
ssex char(2) check(ssex='nan'or ssex='nv'),
data datetime default(getdate())
)
--查询 王军同学 的同桌
declare @stu varchar(50)
select @stu=sid from student where sname='王军'
select sname from student where sid=@stu+1 or sid=@stu-1
declare @gra int
select @gra=avg(grade) from sc
print '平均成绩为：'+convert(varchar(10),@gra)
if(@gra>70)
begin
print '成绩不错 '
select top 3* from sc order by grade desc
end
else
begin
print 'buhao'
select top 3* from sc order by grade
end
@@identity
select SchName,dname
from school,diqumanage
where diqumanage.dcode=school.Dcode and school.Dcode='005'
select Sname ,Ssex,Sgrade
from student
where
exists(select Sschoolnum, SchName,dname
from school,diqumanage
where diqumanage.dcode=school.Dcode
and school.Sschoolnum='004'
and school.Sschoolnum=student.Sschoolnum)
=============
if exists(select * from sys.databases where name='lili')
drop database lili
go
create database lilu
on
(
name='lili',
filename='d:/date/lili.mdf',
size=3,
maxsize=9,
filegrowth=10%
)
log on
(name='li',
filename='d:/date/lili.ldf',
size=3,
maxsize=9,
filegrowth=10%
)
===========================
--4、 编写SQL语句，查询至少有2个电话的用户名、电话号码的个数；
select UserName,count(*)as '电话个数'
from UserInfo
group by UserName
having count(*)>=2
--5、 编写SQL语句，查询通话时间少于1分钟的通话ID；
select TelFrom
from TelRecord
group by TelFrom
having sum(convert(int,substring(convert(varchar(50),(ETime-STime),108),7,2)))<60
and sum(convert(int,substring(convert(varchar(50),(ETime-STime),108),4,2)))=0
--6、 编写SQL语句，
--找出通话记录表中存在的电话号码而在用户信息表中没有的电话号码，
--然后把这些电话号码插入到用户信息表中，新插入的数据用户名为“未知”、
--地址为“未知”；
--7、 编写存储过程，按输入的月份、主叫号码统计通话记录、通话总时间；
create procedure Cunchu_LX_yue_tel
@month varchar(50)=01,
@tel varchar(50)
as
declare @mis int
declare @hous int
declare @second int
select @hous=sum(convert(int,substring(convert(varchar(50),ETime-STime,108),1,2))),
@mis=sum(convert(int,substring(convert(varchar(50),ETime-STime,108),4,2))),
@second=sum(convert(int,substring(convert(varchar(50),ETime-STime,108),7,2)))
from TelRecord
group by TelFrom
while (1=1)
begin
if(@second>60)
begin
set @second=@second-60
set @mis=@mis+1
end
else
break
end
while (1=1)
begin
if(@mis>60)
begin
set @mis=@mis-60
set @hous=@hous+1
end
else
break
end
select TelFrom,TelDest,(convert(varchar(50),ETime-STime,108))
from TelRecord
where TelFrom=@tel and substring(convert(varchar(50),STime,104),4,2)=@month
select distinct (TelFrom) , (str(@hous,4)+':'+str(@mis,2)+':'+str(@second,2))as '通话总时间'
from TelRecord
where TelFrom=@tel
exec Cunchu_LX_yue_tel '02','98290000'
--8、 对于通话记录表中存在大数据量的情况，你有哪些查询的优化建议？
---本次考试的缺考情况 --
use student
select count(*) '应到人数',(select count(distinct(sno)) from sc ) '实到人数',(select count(*) from student where sno not in(select sno from sc))'缺考人数' from student
select student.sno ,sname,cno, grade=case
when grade between 60 and 100 then '通过考试'
else '未通过'
end
from sc ,student
where student.sno=sc.sno
union
select student.sno,sname,cno,grade=case
when grade is null then '缺考'
else grade
end
from student left join sc on student.sno=sc.sno where grade is null
----统计通过率
declare @aa int
declare @bb int
select @aa=count(distinct(sno)) from sc where grade>'60'
select @bb=count(*) from student
select (@bb) as '总人数',(@aa)as '通过人数',(convert(varchar(50),@aa*100/@bb)+'%')as'通过率' from student where sno='002'
---- 循环加分
while (select avg(convert(int,grade)) from sc)<70
begin
update sc
set grade =grade+1
where grade<97 and grade<>'缺考'
end
-------------不允许改变tranInfo表的（时间段）
if exists(select * from Sys.objects where name='update_not_bank'and type='tr')
drop trigger update_not_bank
go
create trigger update_not_bank
on customerInfo
for update
as
if(update(customerDatetime))
begin
raiserror('安全警告: 不允许修改时间!!!',16,1)
print '语句回滚。。。'
rollback transaction
end
--建立触发器
use bankdb
if exists(select * from Sys.objects where name='trigg_Cq_Zc' and type='tr')
drop trigger trigg_Cq_Zc
go
create trigger trigg_Cq_Zc
on customerInfo
for insert
as
declare @Num varchar(50)
declare @Type nchar(2)
declare @Money money
select @Num=customerNumber,@Type=customerType,@Money=customerMoney
from inserted
if(@Type='支取')
begin
update bank
set currentMoney=currentMoney-@Money
where customerNumber=@Num
end
else
begin
update bank
set currentMoney=currentMoney+@Money
where customerNumber=@Num
end
--建立触发器
-- 添加商品时（商品的库存不足时），先判断该种商品的销售情况，如果日销售数量等于0 ，则不再进货；
use market
if exists(select * from Sys.objects where name='trigg_Depot_sale_detail' and type='tr')
drop trigger trigg_Depot_sale_detail
go
create trigger trigg_Depot_sale_detail
on depot
for insert
as
declare @depot_id int
declare @plu_qutt int
select @depot_id=depot_id
from inserted
select @plu_qutt=plu_qutt
from sale_detail
where plu_id=@depot_id
if(@plu_qutt=0)
begin
print '此商品销售情况太差,还是先别进货'
rollback transaction
end
---、在merchant表中，当插入的新行中“供货厂商”的值不是“北京绿园公司”时，就撤销该插入操作，并返回一个错误信息；
use market
if exists(select * from Sys.objects where name='trigg_sale_detail' and type='tr')
drop trigger trigg_sale_detail
go
create trigger trigg_sale_detail
on merchant
for insert
as
declare @mer_name varchar(100)
select @mer_name= mer_name
from inserted
if(@mer_name<>'北京绿园公司')
begin
print '次供应商姓名不是。。北京绿园公司'
rollback transaction
end
--删除交易信息时的触发器
use bankdb
if exists(select * from Sys.objects where name='trigg_delete_date' and type='tr')
drop trigger trigg_delete_date
go
create trigger trigg_delete_date
on customerInfo
for delete
as
insert into pbcustomerInfo
select *
from deleted
use bankdb
if exists(select * from Sys.objects where name='trigg_delete1_date' and type='tr')
drop trigger trigg_delete1_date
go
create trigger trigg_delete1_date
on customerInfo
for delete
as
select *
into pbcustomerInfo
from deleted
--对不起，你的支取或存入金额太大 。交易被取消！
use bankdb
if exists(select * from Sys.objects where name='up_bank'and type='tr')
drop trigger up_bank
go
create trigger up_bank
on bank
for update
as
declare @money1 money
declare @money2 money
declare @Num varchar(50)
select @money1=currentMoney,@Num=customerNumber
from deleted
select @money2=currentMoney,@Num=customerNumber
from inserted
if(Abs(@money2-@money1))>2000
begin
RAISERROR ('对不起，你的支取或存入金额太大 。交易被取消！',16,1)
rollback transaction
end
-------------不允许改变tranInfo表的（时间段）
if exists(select * from Sys.objects where name='update_not_bank'and type='tr')
drop trigger update_not_bank
go
create trigger update_not_bank
on customerInfo
for update
as
if(update(customerDatetime))
begin
raiserror('安全警告: 不允许修改时间!!!',16,1)
print '语句回滚。。。'
rollback transaction
end
---执行语句
update customerInfo
set customerDatetime='2009-11-27 11:18:43'
where customerDatetime='2009-11-27 11:18:50'
------SQL争霸赛.doc
--创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，
--如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，
--直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？
use market
go
create procedure proc_ProWage_CC
as
declare @a int
declare @c int
set @c=0
select @a=count(*)
from ProWage
while ((select count(*)
from ProWage
where Wage<2000)*100/(select count(*)
from ProWage))>50
BEGIN
set @c=@c+1
update ProWage
set Wage=Wage+100
end
print @c*100*@a
select *
from ProWage
exec proc_ProWage_CC
--
create proc cx_avg_sc
as
declare @aa varchar(50)
declare @bb int
select @bb=avg(grade) from sc
set @aa=case
when @bb between 80 and 100 then '优秀'
when @bb between 60 and 79 then '良好'
else '差'
end
print ' '
print'平均成绩：'+convert(varchar(20), @bb)
print'-------------------------------'
print'本班考试成绩：'+@aa
print '本班考试没有通过的学生有：'
print'-------------------------------'
select student.sno,sname,grade
from sc,student
where sc.sno=student.sno and
grade<60
go
exec cx_avg_sc
------------ 存储过成 练习
use student
go
create procedure CanShuLX_sc_student
@Sname varchar(50) output ,
@Cname varchar(50) ,
@Grade int =60
as
select @Sname=count(*) from sc
group by cno,grade
having grade<@Grade and cno=(select cno from course where cname=@Cname)
declare @qq varchar(50)
exec CanShuLX_sc_student @qq output,@Cname=js
print '不及格的学生人数：'+@qq
----
-----1. 创建一个存储过程,
---用它来查找学号为指定值的学生的出生年份,
---在存储过程中设置一个输出参数用于返回找到的出生年份.
go
create procedure CunchuLX_student_sno
@Sno varchar(20)='001'
as
select sshengcheng from student where sno=@Sno
exec CunchuLX_student_sno '001'
--2. 创建一个存储过程,用它来查找出生年份介于某两个年份之间的学生人数,
--在存储过程中设置一个输出参数用于返回查到的人数。
--如果在调用该存储过程时没有给出查询的具体的年份，
--则该存储过程返回1980到2000之间的学生人数。
go
create procedure CunChuLX_student_age
@Rshu int output,
@Qage int =1980,
@Hage int =2000
as
select @Rshu=count(*)
from student
where convert(int,2009-sage) between @Qage and @Hage
--@1
declare @aa int
exec CunChuLX_student_age @aa output
print '人数：'+str(@aa,4)
--@2
declare @aa int
exec CunChuLX_student_age @aa output,1989,2000
print '人数：'+str(@aa,4)
---3. 创建一个存储过程,用于查询指定课程号的课程
--是否被学生选修，如果有学生选修，
--则在该过程中设置一个输出参数用于
--返回选修该课程的学生人数，
--然后让该过程返值1，
--如果没有被任何一个学生选修,则该过程返回值2。
IF exists (select* from sys.objects where name='CunChu_lx_cno_course' and type='p' )
drop proc CunChu_lx_cno_course
go
create procedure CunChu_lx_cno_course
@out int output,
@in varchar(10)=1
as
select @out=count(*)from sc
where cno=@in
if(@out=0)
begin
set @out='2'
end
else
begin
set @out='1'
end
--@1练习
declare @out int
exec CunChu_lx_cno_course @out output
print '(1)代表有人选课，(2)代表无人选课('+str(@out,2)+')'
--@2练习
declare @out int
exec CunChu_lx_cno_course @out output,7
print '(1)代表有人选课，(2)代表无人选课('+str(@out,2)+')'
--用自然连接结合分组
select sc.cno,cname,avg(grade) as 平均成绩
from sc inner join course on sc.cno=course.cno
group by sc.cno,cname
--建立‘计算机’系学生的视图js_stu，并要求进行修改和插入操作时仍需要保证该视图只有计算机系的学生；
create view js_stu
as
SELECT sno as '学号' ,sname as '姓名',ssdept as '系名'
from student
where ssdept='计算机系'
with check option;
select *from js_stu
--、建立计算机系选修了1号课程的学生的视图js_js（包括学号、姓名、成绩），
--并要求对视图文本保密；
create view js_js
with encryption
as
SELECT dbo.sc.sno AS 学号, dbo.student.sname AS 姓名, dbo.sc.grade AS 成绩
FROM dbo.student INNER JOIN
dbo.sc ON dbo.student.sno = dbo.sc.sno
where ssdept='计算机系'and cno=1
--
--建立计算机系选修了1号课程且成绩在90分以上的学生姓名；
create view cj_dy
as
SELECT dbo.student.sname AS 姓名, dbo.sc.grade AS 成绩
FROM dbo.student INNER JOIN
dbo.sc ON dbo.student.sno = dbo.sc.sno
where cno=1 and grade>90
--定义一个反映学生出生年份的视图；
create view stu_shengcheng
as
SELECT sname as '姓名',sshengcheng as '出生年月'
from student
--定义每个学生及其平均成绩的视图；
create view stu_sc_avg
as
select sname as '姓名',avg(grade) '平均成绩'
from student,sc
where student.sno=sc.sno
group by sname
--考虑 查询每个学生成绩大于其平均成绩的课程名，显示学号和课程名；
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
create view cj_dayu_avgcj_cname
with encryption
as
select sno '学号',cname '课程名',grade'成绩'
from course,sc
where sc.cno=course.cno and grade in(
select grade
from sc as a
where grade>=(select avg(grade) '平均成绩'
from sc as b
group by sno
having a.sno=b.sno
))
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
-- 计算机系的学生 大于20岁的
create view dept_dy_20
as
select sno,sname
from student
where ssdept='计算机系'and sage>20
复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表复制表
use bankdb
select *
into pbcustomerInfo
from customerInfo
where 1<>1
-------------------------------------------
--实现各个课程的课程号、课程名称及平均成绩
--li
--2009-9-24
-------------------------------------------
--用交叉连接实现
set statistics time on;
use student
select cname,课程号,平均成绩
from course,
(select cno as 课程号,avg(grade) as 平均成绩
from sc
group by cno)as b
where 课程号=cno
select cname,sc.cno,avg(grade)as 平均成绩
from course,sc
where course.cno=sc.cno
group by cname,sc.cno
--用自然连接结合分组
select sc.cno,cname,avg(grade) as 平均成绩
from sc inner join course on sc.cno=course.cno
group by sc.cno,cname
--建立‘计算机’系学生的视图js_stu，并要求进行修改和插入操作时仍需要保证该视图只有计算机系的学生；
create view js_stu
as
SELECT sno as '学号' ,sname as '姓名',ssdept as '系名'
from student
where ssdept='计算机系'
with check option;
select *from js_stu
--、建立计算机系选修了1号课程的学生的视图js_js（包括学号、姓名、成绩），
--并要求对视图文本保密；
create view js_js
with encryption
as
SELECT dbo.sc.sno AS 学号, dbo.student.sname AS 姓名, dbo.sc.grade AS 成绩
FROM dbo.student INNER JOIN
dbo.sc ON dbo.student.sno = dbo.sc.sno
where ssdept='计算机系'and cno=1
--
--建立计算机系选修了1号课程且成绩在90分以上的学生姓名；
create view cj_dy
as
SELECT dbo.student.sname AS 姓名, dbo.sc.grade AS 成绩
FROM dbo.student INNER JOIN
dbo.sc ON dbo.student.sno = dbo.sc.sno
where cno=1 and grade>90
--定义一个反映学生出生年份的视图；
create view stu_shengcheng
as
SELECT sname as '姓名',sshengcheng as '出生年月'
from student
--定义每个学生及其平均成绩的视图；
create view stu_sc_avg
as
select sname as '姓名',avg(grade) '平均成绩'
from student,sc
where student.sno=sc.sno
group by sname
--考虑 查询每个学生成绩大于其平均成绩的课程名，显示学号和课程名；
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
create view cj_dayu_avgcj_cname
with encryption
as
select sno '学号',cname '课程名',grade'成绩'
from course,sc
where sc.cno=course.cno and grade in(
select grade
from sc as a
where grade>=(select avg(grade) '平均成绩'
from sc as b
group by sno
having a.sno=b.sno
))
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
--重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点重点
-- 计算机系的学生 大于20岁的
create view dept_dy_20
as
select sno,sname
from student
where ssdept='计算机系'and sage>20
ALTER trigger [dbo].[bank_XG_ZD]
on [dbo].[transInfo]
for insert
as
declare @cardid nvarchar(20)
declare @transtype nvarchar(20)
declare @transmoney money
select @cardid=cardID,@transtype=transType,@transmoney=transMoney
from inserted
if(@transtype='支出')
update [Table]
set currentMoney=currentMoney-@transmoney
where cordid=@cardid
else
update [Table]
set currentMoney=currentMoney+@transmoney
where cordid=@cardid
/*2、 用户取款或存款有相应的提示，并自动修改帐户余额；
条件限制：
用户每天的总取款金额不能超过20000，超过则不进行交易，并有相应的提示
*/
use bank
if exists(select * from Sys.objects where name='Trigger_Qu_20000' and type='tr')
drop trigger Trigger_Qu_20000
go
create trigger Trigger_Qu_20000
on transInfo
for insert
as
declare @b money
declare @Num varchar(50)
declare @Type nchar(2)
declare @Money money
select @Num=cardID,@Type=transType,@Money=transMoney
from inserted
select @b=sum(transMoney)
from transInfo
where cardID=@Num and convert(char(50),transDate,101)=convert(char(50),getdate(),101)
if(@Type='支出')
begin
if(@b>=20001)
begin
print '你今天取得金钱太多，一超过20000'
rollback transaction
end
else
begin
update [Table]
set currentMoney=currentMoney-@Money
where cordid=@Num
end
end
else
update [Table]
set currentMoney=currentMoney+@Money
where cordid=@Num
insert into transInfo (cardID,transType,transMoney)
values('2334 5566 1234 6788','支出',20000)
/*
3、 用户可以进行销户（相当于删除账户），
销户时可取出卡中的所有余额（提示：您的帐户还剩余xx元，将全部取出）；
并删除相应的交易记录；销户成功后提示相应的信息；
*/
use bank
if exists(select * from Sys.objects where name='Trigger_delete_zhanghu' and type='tr')
drop trigger Trigger_delete_zhanghu
go
create trigger Trigger_delete_zhanghu
on [Table]
for delete
as
declare @Num varchar(50)
declare @Money money
select @Num=cordid,@Money=currentMoney
from deleted
print '你的账户余额'+convert(varchar(50),@Money)+'将要取出！'
print '删除账户成功!'
delete [Table]
where cordid='2334 5566 1234 6788'
/*
4、 用户每次取款不能超过2500，且只能取100面值；
*/
use bank
if exists(select * from Sys.objects where name='Trigger_100_BS' and type='tr')
drop trigger Trigger_100_BS
go
create trigger Trigger_100_BS
on [Table]
for update
as
declare @Money money
declare @Money1 money
select @Money=currentMoney
from deleted
select @Money1=currentMoney
from inserted
if(@Money-@Money1)>2500
begin
print '你一次支取得金钱太多！已过2500'
rollback transaction
end
else
begin
if (@Money-@Money1)>0 and (@Money-@Money1)%100!=0
begin
print '你输入的是不是100的倍数！'
rollback transaction
end
end
insert into transInfo (cardID,transType,transMoney)
values('2334 5566 1234 6788','支出',2666)
declare @Type money
set @Type=324
print @Type%100
/*
5、 交易信息表中的交易信息可以删除，但是必须把删除的数据做好备份；
*/
use bank
if exists(select * from Sys.objects where name='Trigger_delete_Bakeup' and type='tr')
drop trigger Trigger_delete_Bakeup
go
create trigger Trigger_delete_Bakeup
on transInfo
for delete
as
insert into Bakeup
select * from deleted
delete from transInfo
where cardID='2334 5566 1234 6788'
/*
6、 交易日期不允许修改；
*/
use bank
go
if exists(select * from Sys.objects where name='Trigger_update_transDate' and type='tr')
drop trigger Trigger_update_transDate
go
create trigger Trigger_update_transDate
on transInfo
for update
as
if(update(transDate))
begin
raiserror('安全警告: 不允许修改时间!!!',16,1)
print '语句回滚。。。'
rollback transaction
end
/*
7、 创建存储过程，产生随机卡号 ； 产生随机卡号起始值表达式：
+当前系统时间的月份*100000当前系统时间的秒*1000 + 当前系统时间的毫秒
5+rand()*5
*/
use bank
go
if exists(select * from Sys.objects where name='proc_KH_sj' and type='p')
drop procedure proc_KH_sj
go
create procedure proc_KH_sj
@Pwd nchar(20),
@Name nvarchar(20),
@Tel char(16),
@Money money
as
declare @bb numeric(15,8)
set @bb=rand(datepart(mm,getdate())*1000000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
insert into [Table](cordid,customerName,customerPwd,currentMoney,currenttel)
values('1004 6545 '+substring(convert(varchar(20),@bb),3,4)+' '+substring(convert(varchar(20),@bb),7,4),@Name,@Pwd,@Money,@Tel)
/*
8、 测试产生的随机卡号，并将其插入到 帐户信息表中，也就是用户进行注册；
*/
七八提 要建存储(存储过程调用存储过程)
/*
9、 输入卡号、密码、交易类型、交易金额；
要求：进行卡号和密码的验证，如果正确才能进行相应的交易，并且要将该交易存
入到交易信息中，并对帐户余额进行相应的修改；
*/
use bank
go
if exists(select * from Sys.objects where name='proc_BJ_Mm' and type='p')
drop procedure proc_BJ_Mm
go
create procedure proc_BJ_Mm
@Pwd nchar(20),
@Num nvarchar(20),
@Type nchar(2),
@money money
as
declare @Pwd1 nchar(20)
select @Pwd1=customerPwd
from [Table]
where cordid=@Num
if(@Pwd=@Pwd1)
begin
print '支取或存入成功!'
insert into transInfo(cardID,transType,transMoney)
values(@Num,@Type,@money)
end
else
begin
print '你输入的密码错误!'
end
exec proc_BJ_Mm @Pwd='123',@Num='2324 7567 7865 4543',@Type='支出',@money=2500
/*
转账的实现：输入转账卡号、接收卡号、转账金额；
要求：验证转账和接收帐户是否存在，且要保证同一张卡不能进行转账；卡号验证
成功后，检查转账用户的余额是否满足转账需求；都验证成功后进行转账，并把该
交易保存到交易信息中（转出卡号的交易类型为支取，接收卡号的交易类型为存
入），提示成功信息；否则回滚，提示错误信息；
*/
use bank
go
if exists(select * from Sys.objects where name='Proc_Use_Transfer' and type='p')
drop procedure Proc_Use_Transfer
go
create procedure Proc_Use_Transfer
@InputID varchar(50),
@upID varchar(50),
@TranSferMoney money
as
if exists(select * from [Table] where cordid=@InputID)
begin
if exists(select * from [Table] where cordid=@upID)
begin
if (@InputId<>@upID)
begin
insert into transInfo(cardID,transType,transMoney)
values(@InputID,'支出',@TranSferMoney)
insert into transInfo(cardID,transType,transMoney)
values(@upID,'存入',@TranSferMoney)
end
else
print '你输入如账号相同！'
end
else
print '你输入的转入账号不存在！'
end
else
print '你输入转出账号不存在！'
--验证
exec Proc_Use_Transfer '8766 3443 4552 7536','6577 7887 5667 3455',2500
--11、在账户信息表中添加字段cardAvailable int --用户信用卡的当前状态，
--即是否被挂失，默认值为1，即不挂失；若为0，则挂失；
--12、实现账号挂失功能，输入账号进行挂失；
create procedure proc_guashi
@ID nvarchar (20)
as
if exists(select * from where cordid=@ID)
begin
update [Table]
set cardAvailable=0
where cordid=@ID
end
else
begin
print '用户不存在。。。'
end
--13、申请取消帐号挂失，恢复帐号功能；
create procedure proc_cancelguashi
@ID nvarchar (20)
as
if exists(select * from where cordid=@ID and cardAvailable=0)
begin
update [Table]
set cardAvailable=1
where cordid=@ID
end
else
begin
print '用户不存在或没有挂失。。。'
end
--14、查询本月交易金额最高的卡号；
select distinct(cardID)
from transInfo
where transMoney=(
select max(transMoney)
from transInfo
group by datepart(mm,transDate)
having datepart(mm,transDate)=datepart(mm,getdate())) and datepart(mm,transDate)=datepart(mm,getdate())
--15、查询挂失账号的客户信息；
select *
from [Table]
where cardAvailable=0
-- 转账作业
use bankdb
declare @sum int
set @sum=0
begin TRANSACTION
update bank set currentMoney=currentMoney-800
where customerName='李四'
set @sum=@sum+@@error
update bank set currentMoney=currentMoney+800
where customerName='张三'
set @sum=@sum+@@error
if (@sum<>0)
begin
print 'no'
rollback transaction
end
else
begin
print 'ok'
commit transaction
end
select * from bank
select * from student
where sid=convert(int,@@IDENTITY)
insert into student (sno,sname,ssex,ssdept,stel,sshengcheng,sage)
values('008','王立','女','计算机系','586993','1988-11-01',23)
select * from 表名 where 自动增长的列号=@@IDENTITY
-- CASE-END练习
select sno
,(case when avg(grade)<60 then '不及格'
when avg(grade)between 60 and 69 then '差'
when AVG(grade)between 70and 79 then '中'
when AVG(grade)> 80 then '优'
end)
as '成绩'
from sc
group by sno
/*本次考试成绩较差，假定要提分，确保每人笔试都通过。
提分规则很简单，先每人都加2分，看是否都通过，
如果没有全部通过，每人再加2分，再看是否都通过，
如此反复提分，直到所有人都通过为止 。*/
while (select count(grade) from sc where grade<60)>0
begin
update sc
set grade=grade+2
where grade<99
end
/*则根据如下规则对成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。
90分以上： 不加分
80－89分： 加1分
70－79分： 加2分
60－69分： 加3分
60分以下： 加5分
*/
while (select avg(grade) from sc )<85
begin
update sc
set grade=
case
when grade between 80 and 89 then grade+1
when grade between 70 and 79 then grade+2
when grade between 60 and 69 then grade+3
when grade between 0 and 60 then grade+5
else grade
end
end
select sno, grade from sc
---------------------------------------------------
--添加约束
alter table bank
add constraint CK_bankcountmany
check (currentMoney>=1)
-- 转账作业
use bankdb
declare @sum int
set @sum=0
begin TRANSACTION
update bank set currentMoney=currentMoney-800
where customerName='李四'
set @sum=@sum+@@error
update bank set currentMoney=currentMoney+800
where customerName='张三'
set @sum=@sum+@@error
if (@sum<>0)
begin
print 'no'
rollback transaction
end
else
begin
print 'ok'
commit transaction
end
select * from bank
-- rand可以放入种子 如下
declare @bb numeric(15,8)
set @bb=rand(datepart(mm,getdate())*1000000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
print @bbs
declare @bb numeric(15,8)
set @bb=rand(datepart(mm,getdate())*1000000+datepart(ss,getdate())*1000+datepart(ms,getdate()))
print substring(convert(varchar(20),@bb),3,4)+' '+substring(convert(varchar(20),@bb),7,4)
use student
alter table sc
add constraint fk_sc_student foreign key(sno) references student(sno)
on update cascade
on delete cascade
use student
alter table sc
add constraint fk_sc_course foreign key(cno) references course(cno)
on update cascade
on delete cascade
--在student 表的sname列创建非聚集索引
use student
create nonclustered
index IX_sc_l--名称
on student(sname)--
with fillfactor=50--填充因子最多 50个
/*1、用一条SQL语句 查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
*/
declare @t table (Sname varchar(20) ,KeCheng varchar(20) , FenShu int)
insert into @t
select '张三','语文',81 union all
select '张三','数学',75 union all
select '李四','语文',76 union all
select '李四','数学',90 union all
select '王五','语文',81 union all
select '王五','数学',100 union all
select '王五','英语',90
select distinct(Sname)
from @t
where Sname not in(select Sname from @t where FenShu<80)
/*
2、学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息：
*/
go
declare @t table ( Id int,Sno varchar(50), Sname varchar(20) ,KeChengHao varchar(20) , FenShu int)
insert into @t
select 1,'2005001','张三','0001',69 union all
select 2,'2005002','李四','0001',89 union all
select 3,'2005001','张三','0001',69
delete from @t
where Id not in(select min(ID) from @t group by Sno,Sname)
select * from @t
/*
3、一个叫department的表，里面只有一个字段name,一共有4条纪录，
分别是a,b,c,d,对应四个球对，
现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合：
*/
go
declare @t table( [name] varchar(20))
insert into @t
select 'a' union all
select 'b' union all
select 'c' union all
select 'd'
select a.[name] ,b.[name]
from @t a ,@t b
where a.[name]<b.[name]
/*4、面试题：怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
*/
declare @t table([year] varchar(20),[month] varchar(20),amount float)
insert into @t
select '1991','1',1.1 union all
select '1991','2',1.2 union all
select '1991','3',1.3 union all
select '1991','4',1.4 union all
select '1992','1',2.1 union all
select '1992','2',2.2 union all
select '1992','3',2.3 union all
select '1992','4',2.4
select [year],
(select amount from @t as b where [month]='1'and b.[year]=a.[year] ) 'm1' ,
(select amount from @t as b where [month]='2'and b.[year]=a.[year] ) 'm1',
(select amount from @t as b where [month]='3'and b.[year]=a.[year] ) 'm1',
(select amount from @t as b where [month]='4'and b.[year]=a.[year] ) 'm1'
from @t as a
group by [year]
/*
5、两张关联表，删除主表中已经在副表中没有的信息（主表 a, 从表b）:
*/
declare @t table(Num int,[name] varchar(20))
insert into @t
select 2,'张三' union all
select 3,'多少' union all
select 4,'里斯' union all
select 5,'的是'
declare @ta table(Num int)
insert into @ta
select 1 union all
select 2 union all
select 7 union all
select 4
delete from @ta
where Num not in(select Num from @t)
select * from @ta
/*
日程安排提前五分钟提醒（假如存在日程安排表）
*/
/*
7、有两个表A和B，均有key和value两个字段，
如果B的key在A中也有，就把B的value换为A中对应的value,
这道题的SQL语句怎么写？
*/
use student
create table a([Key] int,[value] varchar(20))
insert into a
select 2,'万物' union all
select 3,'多少' union all
select 4,'里斯' union all
select 5,'的是'
create table b([Key] int,[value] varchar(20))
insert into b
select 1,'张三' union all
select 2,'热土' union all
select 7,'发的' union all
select 4,'的是'
update b
set b.value=(select a.value
from a
where a.key=b.key)
where b.key in( select b.key
from b,a
where b.key=a.key)
/*
1、原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
*/
declare @t table (Sname varchar(20) ,KeCheng varchar(20) , FenShu int)
insert into @t
select '张三','语文',81 union all
select '张三','数学',30 union all
select '李四','语文',76 union all
select '李四','数学',90 union all
select '王五','语文',45 union all
select '王五','数学',132 union all
select '王五','英语',90
select Sname ,KeCheng,FenShu,mark=case
when FenShu between 0 and 60 then 'fail'
when FenShu between 60 and 100 then 'pass'
else '数值不是分数！'
end
from @t
/*写出此查询语句
2、复制表(只复制结构,源表名：a 新表名：b)
*/
declare @t table([Key] int,[value] varchar(20))
insert into @t
select 2,'张三' union all
select 3,'多少' union all
select 4,'里斯' union all
select 5,'的是'
select *
into [Table]
from @t
where 1<>1
select *from [Table]
--定义一个游标，用于访问学生库中年龄大于18的第一个学生信息。
use bank
go
declare cur_stu cursor
for select * from [Table] where currentMoney>=18
open cur_stu
fetch next
from cur_stu
close cur_stu
deallocate cur_stu
--取学生信息表中的第一条或最后一条数据(first/last)
declare cur_stu scroll cursor --scroll （用first/last 他们 必修写）
for select * from [Table] where currentMoney>=18
open cur_stu
fetch first
from cur_stu
close cur_stu
deallocate cur_stu
--更新游标数据
declare cur_update cursor
for select * from [Table] where currentMoney>=18
open cur_update
fetch next from cur_update
begin
update [Table]
set currentMoney=currentMoney+2
where current of ur_update --（current of） 重要 游标当前所在的行
end
close cur_update
deallocate cur_update
--声明一个只读游标，用以查询计算机系学生信息
declare computer cursor
for select * from 学生表 where 所在系=‘计算机’
for read only --只读
open computer
fetch next from computer
while (@@fetch_Status=0) --如果游标读取成功
begin
fetch next from computer
end
close computer
deallocate computer
--分别取学生信息表中倒数第二行、正数第二行或零行数据
declare tt cursor scroll
for select * from 学生表
open tt
fetch absolute –2/2/0 from tt --（-2/2/0）可选
close tt
deallocate tt
--查询信息表中第一个姓王的同学姓名，并将其放到变量中显示出来
declare @stuname varchar(40)
declare stu_cursor cursor scroll
for select stuname from student
where stuname like ‘王%’
open stu_cursor
fetch next from stu_cursor into @stuname
select @stuname as 姓名
close stu_cursor
deallocate stu_cursor
---查询选修了课程1或选修了课程2 的学生姓名；（两种方法：集合查询和连接查询）
use student
select student.sno,sname '学生姓名',sc.cno
from student,sc
where student.sno=sc.sno and cno in (1,2)
--2. 查询选修了课程名为‘数据库’的学生学号和姓名（用两种方法实现：子查询和连接查询）
select student.sno,sname '学生姓名'
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno and cname='shujuku'
select sno,sname '学生姓名'
from student
where sno in(select sno
from sc
where cno=( select cno
from course
where cname='shujuku'
)
)
--3. 找出每个学生超过他选修课程平均成绩的课程号；
select sno ,avg(grade) '平均成绩'
from sc
group by sno
select a.sno,a.cno,grade
from sc as a
where grade>=(
select avg(grade) '平均成绩'
from sc as b
group by sno
having b.sno=a.sno
)
--4、查询参与选课的学生姓名；（两种方法）
select distinct (student.sno),sname
from student,sc
where student.sno=sc.sno
select sno,sname,ssex
from student
where sno in( select sno
from sc
)
---1、 公司经理需要查询所有库存数量小于50件的商品的供货厂商名称、厂商联系人、联系电话、商品编号和商品库存数量等信息，以便订货；
select mer_id,mer_managre,mer_tel,depot_id,depot_stocks
from merchant,depot
where depot_stocks<50 and depot_merid=mer_id
--2、 公司经理需要查询还没有参与销售的商品信心（只要没有参与销售的信息）；
select *
from plus
where plu_id not in (select plu_id
from sale_detail
)
--3、 公司经理需要查询商品日销售明细表中日销售数量低于商品平均是销售数量的商品信息；
select *
from sale_detail
where plu_qutt<(select avg(plu_qutt)
from sale_detail)
select avg(plu_qutt)
from sale_detail
--4、 查询商品库存表中库存数量低于平均库存量的商品信息；
select *
from depot
where depot_stocks<(select avg (depot_stocks)
from depot)
select avg (depot_stocks)
from depot
--5、 公司经理需要查询未在2009-10-15销售出的商品的编号，商品名称，商品所属的部门编号，商品的进货价格和商品的销售价格；
select plus.plu_id ,plus.plu_name,depot_id,depot_name,plu_prce,plu_unitprce
from depot,plus,sale_detail
where depot_id=plu_depid and plus.plu_id=sale_detail.plu_id and convert(varchar(50),sale_detail.plu_date,111)!='2009/10/15'
--5(1)要查询未在2009-10-15销售出的商品的编号
select *
from sale_detail
where convert(varchar(50),plu_date,111)!='2009/10/15'
--1、 查询其他系中比计算机系某一学生年龄小的学生姓名和年龄（用两种方法实现）；
use student
select sno ,sname
from student
where sage <any(select sage
from sc,student
where sc.sno=student.sno and cno =(select cno
from course
where cname='shujuku')
)
and sno<>all( select sno
from sc
where cno =(select cno
from course
where cname='shujuku'))
--2、 查询其他系中比计算机系所有学生年龄小的学生姓名及年龄（用两种方法实现）；
use student
select sno ,sname
from student
where sage <all(select sage
from sc,student
where sc.sno=student.sno and cno =(select cno
from course
where cname='java')
)
and sno<>all( select sno
from sc
where cno =(select cno
from course
where cname='java'))
--3、 查询没有选修1号课程的学生姓名；
select sno, sname
from student
where sno <>all(select sno
from sc
where cno='1')
--4、 查询选修了全部课程的学生姓名；（提示：查询这样的学生，没有一门课程是他不选修的）；
set statistics time on ;
select sno,sname
from student
where sno in(select sno
from sc
group by sno
having count(cno)>=(select count(cno)
from course))
set statistics time on;
use student
select * from student
where not exists(
select * from course
where not exists( -- 没有一门课是不选的
select * from sc --返回结果为真
where sc.cno=course.cno
and sc.sno=student.sno
)
)
----5、 查询至少选修了 学生‘0011’选修的全部课程的学生号码
select sno from student as b
where not exists(
select cno from sc as a
where not exists( -- 没有一门课是不选的
select * from sc as c --返回结果为真
where c.sno=b.sno and c.cno=a.cno
) and sno='003'
) and b.sno<>'003'
--5、 查询比 学生‘0011’选修课多的的学生号码
set statistics time on
select sno,sname
from student
where sno in(
select sno
from sc
group by sno
having count(cno)>=(select count(cno)
from sc
where sno='003'))
--6、 查询出每门课都大于80分的学生姓名；
select distinct(sname)
from student,sc
where student.sno not in (select sno
from sc
where grade< 50
) and student.sno=sc.sno
--7、 删除除了自动编号不同,其他都相同的学生冗余信息；
delete from student
where sno not in( select min(sno)
from student
group by sname,ssex)
--实现各个课程的课程号、课程名称及平均成绩
--用交叉连接实现
use student
select cname,课程号,平均成绩
from course,
(select cno as 课程号,avg(grade) as 平均成绩
from sc
group by cno)as b
where 课程号=cno
--------------------------
use studentgl
select student.id,student.sname,school.SchName,ssex,Zyname,sgrade,stell from student ,school,YxZy where student.Sschoolnum=school.Sschoolnum and student.Zynum=YxZy.Zynum
select ID, UName from [User] where UDuty_Num='3'
-----------
select SchName,dname from school,diqumanage where diqumanage.dcode=school.Dcode
select Sname ,Ssex,Sgrade from student,school where student.Sschoolnum=school.Sschoolnum
-----------第八章 （三）
exec sp_databases
exec sp_helpdb
use student
exec sp_tables
扫一扫
872
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
