SQL习题（精选9篇）
（1）学生表student由学号（sno），姓名（sname），性别（ssex），年龄（sage），所在系
（sdept）5个属性组成，记做student（sno，sname，ssex，sage，sdept），其中主码为sno，学生的年龄在15到45岁之间，性别默认值为“男”。
（2）课程表course由课程号（cno），课程名（cname），先修课程（cpno），学分（ccredit）
4个属性组成，记做course（cno，cname，cpno，ccredit），其中主码为cno。
（3）学生选课sc由学号（sno），课程号（cno），成绩（grade）3个属性组成，记做sc（sno，cno，grade），其中主码为（sno，cno）。成绩在0到100之间，默认为空值。
请用SQL语言实现下列操作：
（1）建立上述3张表。
（2）查询以“MIS”开头，且倒数第二个汉字为“系”字的课程的详细情况。
（3）查询选修了课程的学生人数。
（4）查询性别为男，课程成绩及格的学生信息及课程号、成绩。
（5）查询与“钱恒”在同一个系学习的学生。
（6）查询选修了课程名为“信息系统工程”的学生学号、姓名和所在系。
（7）查询其他系中比CS系所有学生年龄均大的学生名单，并排序输出。
（8）查询所有未修1号课程的学生姓名。
（9）查询选修了“数据结构”课程的学生的学号、姓名。
（10）查询选修了课程1或者选修了课程2的学生。
（11）查询计算机科学系的学生与年龄不大于19岁的学生的交集。
（12）查询选修课程1的学生集合与选修课程2的学生集合的差集。
（13）查询平均成绩大于85分的学号、姓名、平均成绩。
（14）查询各不同平均成绩所对应的学生人数（给出平均成绩与其对应的人数）。
1、实验目的：
（1）了解SQL Server数据库的逻辑结构和物理结构；
（2）掌握使用SQL语句修改数据库的方法；
2、实验准备：
（1）要明确能够修改数据库的用户必须是系统管理员，或者是被授权使用CREATE DATABASE语句的用户；
（2）修改数据库必须要明确：除存储位置，即物理文件名不能修改外，其它项均可以修改，包括数据库名、数据库大小和增长量等数据；
（3）了解使用SQL语句修改数据库的方法。
3、实验内容：
（1）删除实验三中创建的数据库；
（2）用自己名字创建一个数据库，包含一个主数据文件data1和一个日志文件log1，最大值都是5M；增长量分别为10%和1M；存储在自己的文件夹下；
（3）添加一个数据文件data2，最大值为3M；
（4）添加一个日志文件log2，最大值为4M；
（5）添加一个文件组group1；
（6）添加一个次数据文件data3放在文件组group1中；
（7）修改数据文件data2，将最大值改为5M；
（8）删除日志文件log1；
（9）删除次数据文件data3、data2；
（10）修改文件组，将group1改名为group2；
（11）删除文件组group2；
（12）将log2的增长量改为1M。
（13）缩小该数据库的大小，保留自由空间1M，保留释放的文件空间。
（14）将数据库的日志文件log2缩小到3M。
-09-09根据表名和索引获取需要的列名的存储过程
2011-08-08SQL学习笔记四 聚合函数、排序方法
-01-01SQLSERVER收集语句运行的统计信息并进行分析
-12-12jdbc连接sql server数据库问题分析
2007-03-03SQL SERVER的优化建议与方法
2012-11-11sql server 获取系统时间的方法
-09-09SQL Server SA权限总结经典技术
2013-06-06SQL Server 创建简单的存储过程--总结分析
（一）
after
触发器
（1 1）
在l l ｉ neitem 表上定义一个 after 触发器, , 当修改列项目e e ｘ tendedprice d ｉ scount
x tax 时, , 要把 s orders 表得ｔo o ｔａｌ pri ｃe e 一起修改, , 以保证数据一致性
C ＲE ＡＴＥ T ＲIGGER
trig ＿line ｉte ｍ_ ｐr ｉce_ ｕｐdate on line ｉt ｅｍ fo ｒ upda ｔe
ａ ａs
begin i ｆ(UPDATE(ex ｔend ｅdprice)
o ｒ UPDATE(tax)
or UPD ＡT Ｅ(di ｓcou ｎt))begin
-— 声明游标变量指向 inserted 表
d ｅclare
ｃursor_inserted c ｕrs ｏｒ
ｒea ｄ_only
ｏ ｆｏr select ｏｒder ｋey,linenu ｍber,exte ｎd ｅdpr ｉce, ｄｉs ｃoun ｔ, ｔａx
from
in ｓer ｔｅｄ
—-息信找查取获量变明声ﻩ 声明变量获取查找信息
de ｃlare ｏｒｄer ｋｅy in ｔ, ＠ｌinenumb ｅr
ｉnt,exte ｎd ｅｄｐrice
ｒeal,diｓ ｓcount real,tax real
—-打开游标 ｅpoﻩ ｅn ｃursor_i ｎs ｅrt ｅd
—-标游取读ﻩ 读取游标
fe ｔｃｈ
next
from cur ｓｏr ＿i ｎs ｅｒte ｄ ｉnt ｏ ＠o ｒｄerkey, ＠lin ｅnumber,ｅ ｅxt ｅnde ｄｐｒice, ＠di ｓcount,t ａx
ｗ ｗhi ｌe FETC Ｈ_S ＴATUS ＝0 ｎigebﻩ ｎ
ﻩ —-声明一个变量保存重新计算得新价格 ｃｅdﻩﻩ ｅｃl ａre ＠n ｅw_tota ｌpri ｃe
r ｅal
ﻩ select ＠n ｅw_t ｏtal ｐrice= ＠ext ｅn ｄedpr ｉce ＊(1 －＠di ｓｃou ｎt)＊(1 ＋＠tax)
—-用新得总价格变量更新 orders 表得 t ｏt ａlprice ｐｕﻩﻩ ｕｐdate orde ｒs
se ｔ tot ａlpri ｃe= ｎew_totalprice ｗｈere or ｄerkey=orde ｒｋｅy
ｅn hctefﻩ ｅxt f ｒo ｍ cur ｓor_i ｎser ｔed int ｏ ＠order ｋe ｙ, ＠ｌi ｎｅnum ｂer, ＠ex ｔｅnde ｄp ｒice,discoun ｔ, ＠tax
dneﻩｌlaedﻩ ｌocate c ｕr ｓｏr_i ｎser ｔe ｄ ｅ ｅnd end ﻩ(2)在 在 linei ｔｅｍ表上定义一个 aftr er 触发器, , 当增加一项订单明细时, ,自动修改 s orders 表得 total ｐ rice, 以保证数据一致性
CREATE TR ＩGGER tri ｇ_ ｌｉｎｅit ｅｍ＿price_ ｉn ｓert ｏn l ｉｎe ｉｔｅm ｆ ｆor inse ｒｔ
a ｓ begin ——向指量变标游明声ﻩ 声明游标变量指向ins ｅｒted 表
de ｃｌare
c ｕrsor_inse ｒted
cursor
read_ ｏｎl ｙ
ﻩ for s ｅle ｃt orderkey,linenumbe ｒ,ex ｔe ｎdedp ｒic ｅ,di ｓｃｏu ｎt,t ａx
from inｓ ｓerte ｄ
--声明变量获取查找信息 ｅdﻩ ｅclare ＠orde ｒk ｅy
int, ＠ｌｉn ｅｎumber int,e ｘten ｄedp ｒice
real, ＠discount
real, ＠ta ｘ ｒｅal －—ﻩ － 打开游标
open cursor_i ｎs ｅrte ｄ －-ﻩ － 读取游标
fe ｔc ｈ
nex ｔ
f ｒom
cu ｒｓｏｒ_ins ｅrted
into
＠o ｒd ｅrkey,li ｎen ｕｍbｅ ｅｒ ｒ, ｅx ｔen ｄed ｐric ｅ,dis ｃount,tax ｉhwﻩ ｉle ＠＠FE ＴCH_ ＳTATU Ｓ＝0 ｅbﻩ ｅgin
-—格价新得算计新重存保量变个一明声ﻩﻩ 声明一个变量保存重新计算得新价格
ｃedﻩ ｃlare ＠n ｅw_tot ａｌprice ｒｅａl
ｃelesﻩ ｃt new ＿to ｔa ｌprice= ＠extend ｅdp ｒｉce ＊(１-d ｉs ｃｏunt)＊(1+tax)—-新更量变格价总得新用ﻩﻩ 用新得总价格变量更新orders 表得totalpri ｃe
ﻩ u ｐｄa ｔe
or ｄｅｒs s ｅt
t ｏta ｌpric ｅ=total ｐric ｅ+ ＠ｎｅw_ ｔota ｌｐr ｉce ｗ ｗｈ ｈe ｒe o ｒderke ｙ＝orderkey
ｔｅfﻩ ｅｔch next from ｃｕrsor_ ｉns ｅrte ｄ ｉnto o ｒder ｋey, ＠l ｉnen ｕｍber,e ｘｔendeｄ ｄp ｒic ｅ, ＠disc ｏu ｎt, ＠t ａｘ
ｅ ｅnd ａｅｄﻩ ｄｅａll ｏca ｔe cu ｒsor_in ｓerted en ｄ
(3)
在ｌ in ｅ ite ｍ表上定义一个ａ ft ｅｒ触发器, , 当删除一项订单明细记录时, ,自动修改 orders 表得 tot ａ lprice, 以保证数据一致性
CREATE ＴRI ＧG ＥR ｔrig ＿ｌine ｉｔem_price_de ｌet ｅ
on line ｉtem fo ｒ de ｌｅt ｅ
Ａ ＡS begin
-－声明游标变量指向delet ｅd 表
de ｃlar ｅ curso ｒ_d ｅleted ｃursor
ｒe ａd ＿on ｌｙ
ｅs
rofﻩﻩ ｅle ｃｔ ｏrd ｅｒk ｅｙ,line ｎｕmbe ｒ,extende ｄp ｒｉce,discoun ｔ, ｔａx
from
del ｅｔｅｄ －ﻩ －－ 声明变量获取查找信息
ｄｅｃlare ord ｅｒｋｅy ｉnt,linenum ｂe ｒ
int,extendedp ｒi ｃe r ｅａl,discou ｎt real,ta ｘ ｒｅal －—ﻩ －
打开游标 ｅpoﻩ ｅn c ｕrso ｒ_ ｄeleted
——标游取读ﻩ 读取游标 ｅfﻩ ｅtch next ｆr ｏm cur ｓｏｒ＿delete ｄ in ｔo
order ｋe ｙ,l ｉnenumb ｅｒ,ext ｅnｄ ｄedprice, ＠di ｓｃｏｕn ｔ,tax
wh ｉｌｅ ＠FETCH_STATUS=0
begi ｎ
-— 声明一个变量保存重新计算得新价格
ﻩ declare ＠ne ｗ_ ｔo ｔａlpric ｅ ｒｅal
ﻩ ｓ ｓelec ｔ ＠new_t ｏｔａlpr ｉce= ＠exte ｎｄe ｄpri ｃe*(1-＠disco ｕnt)＊(1+ ｔax)
－ﻩ －— 用新得总价格变量更新orders 表得tot ａlp ｒice ｕﻩﻩ ｕpd ａte
ｏｒｄers set t ｏtalpri ｃｅ=to ｔalpr ｉｃe-＠new ＿totalp ｒｉce ｗh ｅr ｅ order ｋey= ＠ord ｅｒkey
fetch
n ｅｘt f ｒom ｃｕｒsor_inse ｒt ｅd in ｔo orderkey, ＠line ｎｕｍbe ｒ,extendｅ ｅｄ ｄprice, ＠d ｉscou ｎｔ,t ａx ｄｎeﻩ ｎｄ
ｄ ｄeal ｌo ｃat ｅ cur ｓor_inse ｒted ｅ ｅnd((4 4))验证 up ｄ at ｅ触发器
— －查瞧 号订单得 to ｔa a ｌ pr ｉ ce
selec ｔ
＊
ｆｒo o ｍ
o o ｒ ders where
orde ｒ key=1 ８ 30;
— －查瞧明细表得相关信息
se ｌ ect *
ｆ ro ｍ
lin ｅi i ｔe e ｍ
ｗ here or ｄe e ｒ key=183 ０
and ｌ ine ｎｕm m ｂe e ｒ =1;
—— 验证 e update 触发器
updat ｅ
lineitem set ｔ ax=tax+0、05
ｗｈｅ re orderkey=1830;
（二）
i i ｎ stead
ｏｆ
触发器
（（1））
在 在 lineit ｅm 表上定义一个ｉns ｔｅad o ｆ ｕpda ｔｅ触 发器, 当修改明细表中得数量 量quan ｔi ｔy 时, 应先检查供应表par ｔsupp 得ａv ａi ｌqty 就是否足够, 不足够则拒绝执行, 否则执行并修改相应数值以保证数据一致性 于 由于 in ｓtead
of 触发器更新某个表会使得该表上其她不满足更新列不能更新,因 因用 此逆向思维使用 a ｆter 触发器实现相同效果 即先更新 qu ａｎtity, 再比较ａv ａilｑ ｑｔ ｔy, 如果满足更新数量, 就修改ｐartsupp 得 表得 a ｖailqty, 如果不满足, 则把ｌineitem得ｑｕantity 更新之后得数据重新修改回来 ｃ ｃreate trigge ｒ trig_lin ｅｉt ｅm_quanti ｔy_ ｕpda ｔe
on
li ｎeit ｅm f ｏr upda ｔe
as begin if UPDATE(qu ａnt ｉｔｙ)ｂ ｂegin ——向指别分量变标游明声ﻩ 声明游标变量分别指向 i ｎserted 表与 d ｅl ｅｔed 表 ｅｄﻩ ｄｅclare c ｕｒso ｒ_inser ｔed
cur ｓor
r ｅad_on ｌy
for
ｔcelesﻩﻩ ｔ
orde ｒkey, ｐartk ｅy,s ｕppkey,lin ｅnum ｂer, ｑｕａntit ｙ fr ｏm i ｎserted
ｄｅcl ａｒe
cursor_de ｌeted c ｕrs ｏｒ
rea ｄ_ ｏnl ｙ
f ｏr select quantity
fr ｏm deleted
-—息信找查取获量变明声ﻩ 声明变量获取查找信息
decl ａｒe
＠ｑu ａn ｔity ＿dif ｆ_lineitem i ｎｔ,q ｕanti ｔy_pa ｒtsup ｐ i ｎt ｃedﻩ ｃlare suppkey int, ＠ｐar ｔke ｙ in ｔ,o ｒd ｅrkey i ｎt，ｕnenilﻩﻩ ｕm ｂer int ,qty ＿inserted ｉn ｔ , ＠qty_delete ｄ int
-－ 打开游标 ｒuc nepoﻩ ｒsor_in ｓert ｅd ｐｏﻩ ｏｐen cur ｓｏｒ_d ｅleted
-—量变给赋值数标游取读ﻩ 读取游标数值赋给变量 ｆﻩ ｆet ｃｈ next from
cu ｒsor_ ｉｎsert ｅd
ﻩ ｉ ｉnto or ｄe ｒk ｅy,pa ｒtkey, ＠suppk ｅy, ＠ｌine ｎu ｍber, ＠qty ＿ｉns ｅrte ｄ
f ｅt ｃｈ
ｎｅxt from
cursor ＿d ｅｌeted
int ｏ q ｔy ＿de ｌete ｄ
whi ｌe fe ｔch_st ａtus=0 ｇｅbﻩ ｅｇin
-－计算订单明细修改时, 订购数量得变化值 inserte ｄ表项-d ｅｌｅt ｅｄ表项
ｓ ｓel ｅct quantity_d ｉf ｆ_ ｌi ｎｅit ｅm= ＠q ｔy ＿in ｓe ｒｔe ｄ—＠ ＠q ｔｙ_deletｅ ｅd
ﻩ －－从ｐarts ｕpp 表获取ａva ｉlq ｔｙ值, 注意ｐartsupp 表得主键为(partk ｅｙ,suppkｅ ｅy)ｔcelesﻩﻩ ｔ ＠ｑuanti ｔｙ＿p ａr ｔｓupp ＝av ａilq ｔy fro ｍ pa ｒｔsu ｐｐ
ｗﻩﻩ ｗh ｅr ｅ suppkey= ｓuppke ｙ
and part ｋey= ＠par ｔk ｅｙ
-—断判始开ﻩﻩ 开始判断
ｇｅｂﻩ ｂｅｇi ｎ
ｆiﻩ ｆ quant ｉty ＿d ｉff_ ｌｉn ｅite ｍ＝0
ﻩ
ﻩ p ｒin ｔ “ 更新得数量与原表中得值相同, 不需要更新”
e ｌs ｅ if ＠quantit ｙ_d ｉｆｆ＿lin ｅitem 〈=q ｕantity_partsupp
ﻩ
be ｇｉn
ﻩ ｐuﻩﻩ ｐd ａte ｐａrtsupp
s ｅt avail ｑty= ａvailqty-＠ｑua ｎtit ｙ_d ｉff_li ｎe ｉｔem
ﻩ ｐus erehwﻩ ｐpkey=suppkey
ａnd
p ａrtkey= ＠par ｔke ｙ
ﻩ
ﻩ p ｒint “ 两个表都更新成功’ ﻩ
ﻩﻩｎeﻩ ｎd
els ｅ
ｉgebﻩﻩ ｉn
ﻩ ｕﻩﻩ ｕpdate ｌi ｎei ｔem
set
ｑuantit ｙ＝quantity+ ＠quanti ｔｙ_diff_linei ｔem
whe ｒe o ｒd ｅｒke ｙ＝＠orde ｒｋe ｙ and ｌi ｎｅｎu ｍber= ＠lｉ ｉne ｎｕｍber
ﻩ
p ｒi ｎt ＇更新失败”
ﻩﻩ ｅｎｄ
ﻩ e ｎd ｅfﻩﻩ ｅtch ne ｘt
fr ｏｍ c ｕrso ｒ_i ｎｓｅrted
i ｎｔo ＠ｏrd ｅｒｋe ｙ, ＠partkey,s ｕpp ｋey, ＠linenumb ｅr, ＠qt ｙ_ ｉns ｅrt ｅd
f ｅｔch ne ｘｔ from ｃurso ｒ＿de ｌeted into ＠qt ｙ_d ｅｌet ｅd ｄｎeﻩ ｎｄ
d ｅallocat ｅ cur ｓor ＿i ｎserte ｄ
dealloc ａt ｅ cursor_de ｌe ｔed ｅ ｅnd ｅ ｅnd(2)在 在 l ｉｎｅite ｍ表上定义一个 instea ｄ of in ｓｅrt 触发器, 当插入明细表中一条记录 时 时, 应先检查供应表ｐar ｔｓupp 得 得 ava ｉl ｑt ｙ就是否足够 qu ａｎｔi ｔy 得数量 c ｒｅa ｔe t ｒig ｇer ｔri ｇ_lineitem_q ｕa ｎti ｔy_ ｉｎsert
on
line ｉtem ｉ ｉnstead of ｉnser ｔ as ｂ ｂegin
-— 声明游标变量指向 inserte ｄ表
d ｅclar ｅ cur ｓｏｒ＿inserte ｄ ｃur ｓor
rea ｄ_ ｏｎｌy f ｏr
ｓﻩﻩ ｓelect or ｄｅrk ｅy,pa ｒｔkey,sup ｐk ｅy, ｌin ｅn ｕmber,q ｕantity
f ｒｏm ins ｅr ｔed
-—
声明变量获取查找信息
dec ｌare ｑｕａntity int, ＠av ａilq ｔy i ｎt, ＠suppkey
ｉn ｔ, ＠partkey
iｎ ｎｔ ｔ, ＠o ｒderkey ｉnt, ＠ｌinenu ｍber ｉnt
-—标游开打ﻩ 打开游标 ｃ nepoﻩ ｃurs ｏｒ_ins ｅｒted －ﻩ －— 读取游标
f ｅtc ｈ ｎext ｆro ｍ ｃｕrsor_insert ｅｄ ｉnt ｏ ＠ｏｒｄe ｒkey,partkey,＠ ＠ｓ ｓuppkey, ＠linenumber,qu ａｎtity
wh ｉｌe ＠＠FETCH_S ＴATUS= ０ ｉgebﻩ ｉn
--为变量赋值
ａ tcelesﻩ ａvailqtｙ ｙ
＝ ＝av ａi ｌqt ｙ fr ｏm
ｐartsupp
ｗﻩ ｗhe ｒe suppkey ＝＠ｓu ｐｐk ｅy and part ｋe ｙ= ｐartke ｙ
ﻩ if ＠quant ｉty 〈= ＠avail ｑt ｙ
-— 如果可以更新
ｂﻩ ｂegin ／ﻩﻩﻩ ／*将 将 insert ｅd 表中得记录插入到明细表＊/
ﻩ ｓniﻩ ｓert
i ｎto l ｉneite ｍ select ＊
from i ｎserted
ｒo=yekredro
erehwﻩﻩ ｒderk ｅy ａnd
linenumb ｅr = ＠linenumber ＊/ﻩﻩ新更时同ﻩ ＊同时更新 part ｓｕpp 表得数量＊/
ﻩﻩ ｕ ｕpd ａｔe pa ｒｔsup ｐ ｓet a ｖailqty=availqty-＠quanti ｔy
ﻩ
ｅrehwﻩ ｅ sup ｐｋe ｙ= ＠sup ｐkey and partkey=part ｋey
ｐ ｐr ｉnt ’pａ ａrts ｕｐp 表有足够得货物可以满足 lin ｅｉｔem 得ｑuan ｔitｙ ｙ, 插入成功’
ｅnd
ｅlse
begin
ﻩﻩ p ｒｉnｔ ｔ
＇ ＇pa ｒt ｓup ｐ表没有足够得货物可以满足 l ｉneitem 得 得 q ｕａntity,插入失败’
dneﻩｆﻩﻩ ｆetc ｈ next ｆrom ｃｕrso ｒ_ins ｅrt ｅd in ｔｏ ＠ord ｅrkey, ｐａrtkey, ｓuppkey, ＠lｉ ｉnenumbe ｒ, ｑu ａntity ｅﻩ ｅn ｄ
deall ｏcat ｅ ｃｕrsor_ ｉｎserted end(３)在 在 line ｉte ｍ表上定义一个 inste ａd of del ｅte 触发器,当 当 删除 明细表中记录 时 时, 同时改变表 供应表 partsupp 得ａva ｉl ｑtｙ ｙ 数值 c ｒe ａte trigge ｒ tri ｇ_ ｌin ｅit ｅm_quanti ｔy_ ｄel ｅte o ｎ line ｉｔem inste ａd of ｄel ｅte as be ｇｉｎ
—-声明游标变量指向ｄeleted 表
de ｃla ｒｅ curs ｏｒ_ ｄｅl ｅted c ｕｒｓor
ｒead ＿only for
ｌｅsﻩﻩ ｅｌect or ｄerke ｙ, ｐar ｔｋey,sup ｐkey, ｌinenumbe ｒ,quan ｔity ｆr ｏm deleted －ﻩ －-声明变量
decl ａre s ｕpp ｋｅy i ｎt, ｐar ｔke ｙ
int, ＠orde ｒkey int,linen ｕmb ｅr
int, ＠quａ ａnt ｉt ｙ int
—-标游开打ﻩ 打开游标
open
cursor_deleted
-—标游取读ﻩ 读取游标
ｆ ｆetch ｎext fr ｏｍ
curs ｏr ＿del ｅt ｅd
ｉn ｔo ＠ord ｅrkey , ＠partkey,s ｕppkｅ ｅy, ＠lin ｅn ｕmber,q ｕa ｎtity
whi ｌｅ
Ｆ ＦET ＣH_ ＳTATUS=0 ｉgebﻩ ｉn
＊/＊除删ﻩ ＊删除＊/
ﻩ de ｌet ｅ from
lineite ｍ where linenu ｍber= ｌｉne ｎumber and o ｒｄe ｒｋey =orｄ ｄer ｋey
*/新更时同ﻩ 同时更新 pa ｒt ｓupp 表得数量*/
u ｐdate
parts ｕｐｐ ｓe ｔ
availqt ｙ＝a ｖａi ｌq ｔy+quant ｉｔy
ﻩ where s ｕppke ｙ= ＠s ｕｐpkey a ｎd ｐａr ｔk ｅy= ＠p ａr ｔkey
ﻩ p ｒin ｔ
’ 删除成功, 并且已经把货物数量归还到 p ａr ｔsup ｐ里“ ｆﻩﻩ ｆetc ｈ ne ｘｔ fr ｏｍ
curso ｒ＿ｄel ｅted
into ＠ｏrd ｅr ｋe ｙ,p ａr ｔｋey,＠ ＠suppkey, ＠lin ｅnu ｍber,quanti ｔy ｎeﻩ ｎd ｅdﻩ ｅa ｌloc ａte ｃursor_ ｄelete ｄ end(4)验证 update 触发器--查瞧ｌi ｎeit ｅm 得ｑuan ｔit ｙ select ＊
fr ｏm
lin ｅit ｅm ｗhe ｒe or ｄｅrk ｅy ＝1830
and li ｎenum ｂer=1;
—— 查询ｐartsupp 表得ava ｉlqt ｙ se ｌｅｃt ＊ from ｐart ｓupp w ｈｅre suppk ｅy ＝(select
supp ｋｅy fr ｏm
ｌin ｅ
item w ｈｅre ｏrd ｅｒkey=18 ３０)
and partk ｅｙ
=(s ｅlec ｔ part ｋey from lin ｅite ｍ ｗh ｅr ｅ
order ｋey ＝18 ３０ a ｎd linenu ｍｂer=1)
－--更新数量过大
—— 更新得值与原值相同
－--更新到+ + ２ 00 数量, , 成功
ｕpdate
ｌi ｎeitem set
ｑuant ｉty
=q ｕanti ｔy+ ２00
where order ｋey=1830
and lin ｅn ｕmber ＝ １;
--更新 +2 ００ 成功后ｌ ineite ｍ得 quanti ｔy y 变化
—— 更新+200 成功后par ｔs ｕpp 表得a ｖa ｉlqty 变化
实验到此。所有创建得触发器结果
删除触发器
dr ｏｐ trigger ｔri ｇ_li ｎei ｔem_de ｌete;实验六存储过程实验
（1 1）
定义无参数 得存储过程并执行
更新所有订单总价
goﻩ
CREATE
ＰROCED ＵＲＥ Ｐro ｃ＿Ｃa ｌTotalP ｒi ｃe
AS BEGIN
up ｄate
o ｒders ｓet to ｔalprice ＝
(sele ｃｔ sum(ｅｘte ｎdedprice*(1-discount)*(1+ ｔax))
ﻩ fr ｏｍ li ｎei ｔem
ｏ
erehwﻩ ｏrders、ｏrderkey= ｌi ｎeite ｍ。o ｒderke ｙ)END
ｇo 执行此存储过程
exec P ｒｏ c_CalTotal Ｐｒｉc c ｅ;;
（2 2）
定义有参数得存储 过程并执行
更新给定订单号得总价
ｇ ｇo create ｐro ｃedu ｒｅ P ｒo_Cal ＴotalP ｒｉｃe ４Ｏrd ｅｒ ＠id_ ｏrder
ｉntege ｒ ａ ａs be ｇin
ｕ ｕp ｄatｅ ｅ
ｏ ｏrders ｓet ｔｏtal ｐrice=（ｌesﻩ ｌect SU Ｍ(ext ｅn ｄed ｐrice*(1 －di ｓc ｏunt)*(1 ＋ｔax))
ﻩ fr ｏm li ｎeit ｅm
ﻩ whe ｒe
orde ｒs。or ｄerke ｙ=lin ｅｉte ｍ。orderkey)end g ｏ 执行此存储过程 ｅｘe ｃ P ｒｏ_ ＣalTota ｌPr ｉce ４Order ＠ｉd_ ｏrder=2;（（3））
定义有局部变量得存储过程 更新某一个顾客所有订单得总价 g ｏ cr ｅate
procedure Proc_Ca ｌTot ａlPr ｉｃｅ４Ｃusto ｍe ｒ n ａｍe ＿cus ｔo ｍer varc ｈａr(５0)ａs
ｄｅc ｌａｒｅ ｃus ｔk ｅy ＿c ｕｓto ｍer i ｎt b ｅgin ｔcelesﻩ ｔ
cus ｔke ｙ＿ｃu ｓｔｏmer=cu ｓt ｋey
ｆｒｏm
cu ｓto ｍer
where name ＿customer ＝RTRI Ｍ(n ａｍe)
u ｐdate order ｓ ｓet to ｔａlp ｒi ｃe=(ｔcelesﻩﻩ ｔ S ＵM(exte ｎde ｄp ｒｉｃe ＊(1 －ｄis ｃount)*(1+tax))ｒfﻩﻩ ｒom ｌi ｎeite ｍ
ｗﻩ ｗher ｅ orders、order ｋｅy=lineitem。or ｄｅｒｋe ｙ
ﻩ and
o ｒde ｒs、ｃust ｋey= ＠c ｕｓｔｋey ＿cust ｏmer)ﻩe ｎd go 执行此存储过程
e ｘec Pro ｃ_CalT ｏt ａlPr ｉc ｅ4C ｕｓtome ｒ name_custom ｅr=” 艾锦亮’;查瞧结果 u ｐdate
ｌinei ｔe ｍ set
ex ｔende ｄｐric ｅ＝1 ０0 , ｔa ｘ＝0.25 ｗｈere orderkey=“1 ８30’;s ｅlect ＊ ｆrom
l ｉneite ｍ
w ｈere ｏrder ｋey ＝＇183 ０’;
s ｅlect ＊ fr ｏm or ｄers ｗh ｅｒe ｃustke ｙ=(sele ｃt ｃｕs ｔk ｅy from custo ｍer ｗhe ｒe name=’ 艾锦亮’);
（4）
定义一个带输出参数得存储过程 更新某个顾客得所有得订单总价, 并输出总价 ｇｏ crea ｔe proc ｅdure Proc_C ａｌT ｏtalP ｒi ｃe4Custo ｍe ｒ2
ｎa ｍe_ ｃu ｓt ｏme ｒ va ｒch ａr(５0), ｔotalpr ｉｃe_ord ｅr real out ｐut
as —— 声明一个变量存储 ｎame 对应得顾客编号 d ｅclar ｅ ＠c ｕs ｔkey ＿cus ｔomer integer
—— 为该变量赋值
se ｌec ｔ c ｕstke ｙ_cu ｓto ｍer ＝cu ｓtkey
from ｃus ｔome ｒ w ｈere n ａme_ ｃustomer=R ＴRIM(n ａｍe)begi ｎ －ﻩ －－ 更改订单总价
up ｄate
o ｒd ｅrs
ｓet t ｏｔａlp ｒｉｃe=（se ｌec ｔ ＳUM(ext ｅｎｄedprice*(1-dis ｃｏunt)*(１+tax))
ｆﻩ ｆr ｏｍ l ｉn ｅitem ｒｅhwﻩﻩ ｅｒe
orders。o ｒｄerk ｅy=li ｎｅｉt ｅｍ.ord ｅｒk ｅｙ、sredro dnaﻩ、c ｕstkey ＝＠custkey_ ｃustome ｒ)
-— 为返回变量赋值
select
ｔotalprice_order=total ｐri ｃe fro ｍ orders
ﻩ w ｈere ｃustk ｅy= ＠c ｕstkey_c ｕstom ｅr
ﻩ
e ｎd go —-执行带有输出参数得存储过程要声明输出参数变量 declare t ｏｔａlp ｒice re ａl;exe ｃ Ｐroc_ Ｃａl Ｔota ｌPrice4C ｕstomer2
” 艾锦亮 ＇, ＠to ｔalpri ｃe
output;--在屏幕上输出返回值结果
ｓelec ｔ t ｏtalpri ｃe;
实验到此, , 所有结果显示
（5 5）
修改存储过程名
ｅｘec sp_renam ｅ ’Pr ｏ＿Cal ＴｏｔａｌPrice4Ord ｅｒ",’C ａｌＴotalPr ｉce4 Ｏr ｄｅｒ＇;
(6)编译存储过程
exe ｃ sp_ ｒe ｃompile ’CalTotalPriｃ ｃe ４Order’;
(7)删除存储过程
ｄ ｄrop
PL/SQL是Oracle对标准SQL规范的扩展，全面支持SQL的数据操作、事务控制等，PL/SQL完全支持SQL数据类型，减少了在应用程序和数据库之间转换数据的操作。
构成PL/SQL程序的基本单位是程序块。程序块由过程、函数和无名块3种形式组成，它们之间可以互相嵌套。
PL/SQL的运行工具有：SQL*Plus、PL/SQL developer
2.PL/SQL程序的基本结构
PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束。
下面是四个部分的基本结构：
DECLARE —— 可选部分
变量、常量、游标用户定义异常的声明
……
BEGIN —— 必要部分
SQL语句和PL/SQL语句构成的执行程序
……
EXCEPTION —— 可选部分
程序出现异常时，捕捉异常并处理异常
……
END;—— 必须部分
在数据库执行PL/SQL程序时，PL/SQL语句和SQL语句是分别进行解析和执行的。PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给Oracle的SQL引擎处理，两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互、处理过程。
3.PL/SQL示例
(1)声明变量输出false
(2)根据输入框(&deptno)输入数字进行分组
(3)前后填充
(4)九九乘法表
(5)异常
(6)使用case语句查询emp表，对工资(sal)如果工资大于5000显示‘A’;如果大于3000显示‘B’;如果大于2000显示‘C’，其他显示‘D’。
注意：有些工具会出现中文乱码，解决方案：在windows中创建一个系统环境变量。
Decode编译进行分类：Decode编码
(7)在dept表中增加字段ecount number ,表示每个部门得到的人数(先添加新的字段(ecount),然后在更新新的字段数据
)
(8)书写一段pl/sql程序，完成字符串拆分操作 1.用while
2.用loop
4.PL/SQL游标
在PL/SQL中，针对多行多列的数据类型，可以使用游标变量，
PL/SQL的游标指把从数据库中查询出来的数据以临时表的形式存放在内存中.游标可以对存储在内存中的数据进行操作,返回一条或一组记录,或者一条记录都不返回.
(1)隐式游标(在PL/SQL中使用DML语句时自动创建隐式游标)
游标作为一个临时表,可以通过游标的属性来获取游标状态.游标有4个常用的属性:
%ISOPEN:用于判断游标是否已经打开
%FOUND:用于判断游标是否找到记录
%NOTFOUND:与%FOUND相反.
%ROWCOUNT:返回到当前为止已经提取到的实际行数.
(2)显示游标(有声明)(显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行)
解决方案：
1.
2.
3.
4.
带参数的显示游标(声明显式游标时可以带参数以提高灵活性)
循环游标(循环游标用于简化游标处理代码)
(3)REF 游标和游标变量 (REF 游标和游标变量用于处理运行时动态执行的 SQL 查询)
创建游标变量需要两个步骤：
声明 REF 游标类型
声明 REF 游标类型的变量
用于声明 REF 游标类型的语法为：
TYPE IS REF CURSOR
[RETURN ];
实验室：
班级：
学号：
姓名：
实验环境：
硬件：
软件：
实验目的：
本次实验的主要目的是…… 实验主要任务：(不够时另附页)一.建立各个表的 SQL 语句。
二.单表查询操作及（任选三题结果截图附上）。
三.多表查询操作其结果截图（任选三题结果截图附上））。
四.数据更新及其结果截图（最后附上四个基本表的数据截图）。
一．利用查询分析器在数据库 ShiYan 中建立以下四个数据表（S 表，P 表，J 表，SPJ表），并依次向各数据表中输入相应的数据记录。
(1)供应商表 S（NO，SNAME，STATUS，CITY）
SNO：表示供应商编号，定义其为长度为 4 字节的字符串类型。
SNAME：表示供应商的名称，定义其为最大长度为 20 字节的变长字符串类型。
STATUS：表示供应商的基本状况，定义其为短整数类型。
CITY：表示供应商所在的城市，定义其为最大长度为 30 字节的变长字符串类型。
要求：SNO 字段为表的主键，其属性值必须不重复且不允许取空值，SNAME 属性值不允许取空值。
SNO SNAME STATUS CITY S1 精 益 20 天津 S2 盛 锡 10 北京 S3 东方红 30 北京 S4 丰泰盛 20 天津 S5 为 民 30 上海 PNO PNAME COLOR WEIGHT P1 螺 母 红 12 P2 螺 栓 绿 17 P3 螺丝刀 蓝 14 P4 螺丝刀 红 14 P5 凸 轮 蓝 40
(2)零件表 P（PNO，PNAME，COLOR，WEIGHT）
PNO：表示零件的编号，定义其为长度为 4 字节的字符串类型。
PNAME：表示零件的名称，定义其为最大长度为 20 字节的变长字符串类型。
COLOR：表示零件的颜色，定义其为长度为 6 字节的字符串类型。
WEIGHT：表示零件的重量，定义其为实数类型且缺省值为 0。
要求：PNO 字段为表的主键，PNAME 属性值不允许取空值。
(3)工程项目表 J（JNO，JNAME，CITY）
JNO：表示工程项目的编号，定义其为长度为 4 字节的字符串类型。
JNAME：表示工程项目的名称，定义其为最大长度为 20 字节的变长字符串类型。
CITY：表示工程项目所在的施工城市，定义其为最大长度为 30 字节的变长字符串类型。
要求：JNO 字段为表的主键，其属性值必须不重复且不允许取空值，JNAME 属性值不允许取空值。
P6 齿 轮 红 30 JNO JNAME CITY J1 三
建 北京 J2 一
汽 长春 J3 弹 簧 厂 天津 J4 造 船 厂 天津 J5 机 车 厂 唐山 J6 无线电厂 常州 J7 半导体厂 南京(4)供应商-零件-工程项目关联关系表 SPJ（SNO，PNO，JNO，QTY）
SNO：表示为某工程项目供应零件的供应商的编号，定义其为长度为 4 字节的字符串类型。
PNO：表示某供应商为工程项目所供应的零件的编号，定义其长度为 4 字节的字符串类型。
JNO：表示正在被施工的工程项目的编号，定义其为长度为 4 字节的字符串类型。
QTY：表示某供应商为工程项目所供应的零件的数量，定义其为整数类型。
要求：SNO、PNO、JNO 属性值不允许取空值；SPJ 表的主键为 SNO、PNO 和 JNO 三属性的组合；SNO、PNO、JNO 均为外码，且 SNO 字段的取值参照 S 表中 SNO 字段的取值，PNO 字段的取值参照 P 表中 PNO 字段的取值，JNO 字段的取值参照 J 表中 JNO 字段的取值；QTY 属性值不能为空值，且 QTY 属性值限制在 1～10000 范围内。
SNO PNO JNO QTY
S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J2 100 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 S2 P5 J2 100 S3 P1 J1 200 S3 P3 J1 200 S4 P5 J1 100 S4 P6 J3 300 S4 P6 J4 200 S5 P2 J4 100 S5 P3 J1 200 S5 P6 J2 200 S5 P6 J4 500 二、针对实验数据库 ShiYan，完成以下单表查询操作：
1．查询为工程 J1 供应零件的供应商号码 SNO。
2．查询为工程 J1 供应零件 P1 的供应商号码 SNO。
3．找出所有供应商的名称和所在城市。
4．找出零件的所有信息，以及仅找出零件的颜色和重量。
5．找出使用供应商 S1 所供应零件的工程号码。
6．找出为工程供应零件的总数量不低于 500 的供应商号码及供应总数量，结果按供应商号码分类并且按供应总数量降序排列。
7．从 J 表中分别检索出第 1 条及前 33%的工程项目信息。
8．统计 P 表中颜色为红色的零件的个数，并指定该查询列的名称为“红色零件数”。查询 P 表中各工程项目编号，名称及重量按 86%计算后的信息，其中重量按 86%计算后的查询列名改为“零件净重”。
10．查询 SPJ 表，要求查询结果式样为“供应商 S1 为工程项目 J1 供应零件 P1 的数量为300”。
11．查询 S 表 STATUS 值大于 20 且小于 40，或 SNAME 字段值的第一个字为“精”或第三个字为“益”或“民”的供应商信息。
12．查询 J 表中 JNAME 值为三建和机车厂的工程项目信息。
三、完成以下多表查询操作：
1．查询为工程 J1 供应红色零件的供应商号码 SNO。
2．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号 JNO。
3．查询至少选用了供应商 S1 所供应的全部零件的工程号 JNO。
4．找出工程项目 J2 使用的各种零件的名称及其重量。
5．找出上海厂商供应的所有零件号码。
6．找出使用上海产的零件的工程名称。
7．找出没有使用天津产的零件的工程号码。
8．找出重量最轻的红色零件的零件编号 PNO。
9．找出供应商与工程所在城市相同的供应商提供的零件号码。
10．找出所有这样的一些＜CITY，CITY，PNAME＞三元组，使得第一个城市的供应商为第二个城市的工程供应零件的名称为 PNAME。
11．重复第 15 题，但不检索两个 CITY 值相同的三元组。
12．找出供应商 S1 为工程名中含有“厂”字的工程供应的零件数量总和。
四、针对实验数据库 ShiYan，完成下列数据更新操作：
1．在 S 表中插入元组“s6，华誉，40，广州，02085268888”。
2．在 J 表中插入元组“j8，传感器厂”。
3．对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库。
4．将 P 表中 PNO 值为 p6 的元组的 color 属性值改为绿，weight 属性值改为 60。
5．将 SPJ 表中前 4 个元组的 qty 属性值统一修改为 300。
6．将 S 表中 city 属性名含有“京”或“津”的相应 status 属性值增加 100。
7．将供应商 s2 为“一汽”工程项目所供应的零件数量修改为 2000。
8．将全部红色零件的颜色修改为浅红色。
9．由 s5 供给 j4 的零件 p6 改为由 s3 供应，请在数据库中作必要的数据修改。
1、SQL SELECT 语句
语法：SELECT列名称FROM表名称
2、SQL SELECT DISTINCT 语句
语法：SELECTDISTINCT列名称FROM表名称
3、SQL WHERE 语句
语法：SELECT列名称FROM表名称WHERE列运算符值
操作符描述
=等于
不等于
>大于
<小于>
>=大于等于
<=小于等于
BETWEEN在某个范围内
LIKE搜索某种模式
注释：在某些版本的 SQL 中，操作符 可以写为 !=，
SQL 使用单引号来环绕文本值。若果是数值，不能使用引号。
4、SQL AND & OR 运算符
AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤，
使用运括号组成复杂的表达式。
5、SQL ORDER BY 子句
根据指定的列对结果集进行排序，默认安生许对记录进行排序，可以使用 DESC 关键字进行降序排列。
如果第一列有相同的值或者有些值为空时，以第二列升序进行排列。
6、SQL INSERT INTO 语句
语法：INSERTINTO表名称VALUES(值1，值2，....)
指定插入数据的列：INSERTINTOTABLE_NAME (列1，列2，...)VALUES (值1，值2,....)
7、SQL UPDATE 语句
语法：UPDATE表名称SET列名称=新值WHERE列名称=某值
8、SQL DELETE 语句
语法：DELETEFROM表名称WHERE列名称=值
删除所有行操作，意味着表的结构、属性和索引都是完整的，单数据是空的。
这周的实训是SQL,通过短暂的几天时间让我深有感触, 从一开始的构思到今天写下这份总结。期间时间虽然只有四天但是也学到了不少的知识。
在实训的刚开始构想只有大体的思路，忽略了一些细节，因此在我真正做这个数据库时发现有很多错误，有的时候要解决一个错误反反复复会花上很多时间。有的时候实在想不出来会再网上查或者直接问同学,我们班有的同学还是很厉害的,所以,在他们身上也学到了不少知识。
创建数据库
创建之前判断该数据库是否存在
ifexists(select*fromsysdatabaseswherename=’databaseName’)
dropdatabasedatabaseName
go
CreateDATABASEdatabasename
删除数据库
dropdatabasedatabasename
备份sqlserver
---创建备份数据的device
USEmaster
EXECsp_addumpdevice’disk’,’testBack’,’c:mssql7backupMyNwind_1.dat’
---开始备份
BACKUPDATABASEpubsTOtestBack
创建新表
createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)
根据已有的表创建新表：
A：go
use原数据库名
go
select*into目的数据库名.dbo.目的表名from原表名(使用旧表创建新表)
B：createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
创建序列
createsequenceSIMON_SEQUENCE
minvalue1--最小值
maxvalue999999999999999999999999999--最大值
startwith1--开始值
incrementby1--每次加几
cache20;
删除新表
droptabletabname
增加一个列
Altertabletabnameaddcolnamecoltype
删除一个列
Altertabletabnamedropcolumncolname
添加主键
Altertabletabnameaddprimarykey(col)
说明：删除主键：Altertabletabnamedropprimarykey(col)
创建索引
create[unique]indexidxnameontabname(col…，)
删除索引：dropindexidxnameontabname
注：索引是不可更改的，想更改必须删除重新建。
创建视图
createviewviewnameasselectstatement
删除视图：dropviewviewname
简单基本的sql语句
(1)数据记录筛选：
sql=“select*from数据表where字段名=字段值orderby字段名[desc]”
sql=“select*from数据表where字段名like’%字段值%’orderby字段名[desc]”
sql=“selecttop10*from数据表where字段名=字段值orderby字段名[desc]”
sql=“selecttop10*from数据表orderby字段名[desc]”
sql=“select*from数据表where字段名in(’值1’,’值2’,’值3’)”
sql=“select*from数据表where字段名between值1and值2”
(2)更新数据记录：
sql=“update数据表set字段名=字段值where条件表达式”
sql=“update数据表set字段1=值1,字段2=值2……字段n=值nwhere条件表达式”
(3)删除数据记录：
sql=“deletefrom数据表where条件表达式”
sql=“deletefrom数据表”(将数据表所有记录删除)
(4)添加数据记录：
sql=“insertinto数据表(字段1,字段2,字段3…)values(值1,值2,值3…)”
sql=“insertinto目标数据表select*from源数据表”(把源数据表的记录添加到目标数据表)
(5)数据记录统计函数：
AVG(字段名)得出一个表格栏平均值
COUNT(*;字段名)对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名)取得一个表格栏最大的值
MIN(字段名)取得一个表格栏最小的值
SUM(字段名)把数据栏的值相加
引用以上函数的方法：
sql=“selectsum(字段名)as别名from数据表where条件表达式”
setrs=conn.excute(sql)
用rs(“别名”)获取统计的值，其它函数运用同上。
查询去除重复值：selectdistinct*fromtable1
(5)数据表的建立和删除：
CREATETABLE数据表名称(字段1类型1(长度),字段2类型2(长度)……)
(6)单列求和:
SELECTSUM(字段名)FROM数据表
编辑本段最新sql语句
查询数据库中含有同一这字段的表：
selectnamefromsysobjectswherextype=’u’andidin(selectidfromsyscolumnswherename=’s3’)
根据出生日期可以算出年龄：
selectdatediff(year,scrq,’2013’)as年龄frompage_shsjgrgl
根据当前年份自动算出年龄
selectdatediff(year,csny,cast(YEAR(GETDATE())aschar))
年
selectyear(djsj)frompage_shsjgrgl
月
selectmonth(djsj)frompage_shsjgrgl
日
selectday(djsj)frompage_shsjgrgl
在同一数据库中复制表结构：
select*intoafrombwhere11
