SQL大二上期末总结.docx
- 文档编号:11344804
- 上传时间:2023-05-31
- 格式:DOCX
- 页数:52
- 大小:87.26KB
SQL大二上期末总结.docx
《SQL大二上期末总结.docx》由会员分享,可在线阅读,更多相关《SQL大二上期末总结.docx(52页珍藏版)》请在冰点文库上搜索。
SQL大二上期末总结
SQL大二上期末总结
【新建数据库】
createdatabaseBillingSys
onprimary
(name=BillingSys,
filename='d:
\BillingSys\Billings.mdf',
size=3,
filegrowth=1),
filegroupfp_bs--自定义文件组
(name=bs_data,
filename='d:
\BillingSys\bs_data.ndf',
size=5,
filegrowth=1)
logon
(name=BillingSys_log,
filename='d:
\BillingSys\BillingSys_log.ldf',
size=1,
maxsize=20,
filegrowth=10%
)
【指定当前数据库】
useBillingSys
go
【移除文件】
alterdatabaseBillingSys
removefilebs_data
【增加文件组】
alterdatabaseBillingSys
addfilegroupfp_bs
【增加数据文件】
alterdatabaseBillingSys
addfile(name=bs_data,
filename='d:
\BillingSys\bs_data.ndf',
size=5,
filegrowth=1)
tofilegroupfp_bs
【修改数据库】
Alterdatabase数据库名
{
Addfile…,
…
}
【删除数据库】
usemaster
dropBillingSys
【查询数据库相关参数】
useBillingSys
execsp_helpdb
【查询数据库空间信息】
useBillingSys
execsp_spaceused
【查询数据库选项信息】
useBillingSys
execsp_dboption
【自动收缩】
alterdatabaseBillingSys
setauto_shrinkon/off
【手动收缩数据库】
dbccshrinkdatabase(0,20)
【分离数据库】
usemaster
go
sp_detach_db@dbname='BillingSys'
【附加数据库】
usemaster
go
sp_attach_db@dbname='BillingSys',@filename='d:
\BillingSys\Billings.mdf'
【分离数据库】
Usermaster
EXECsp_detach_db@dbname=’BillingSys’
【建表时建主\外键】
Createtablecustomer
{
CIDintnotnullprimarykey,
RIDintconstraintfk_RIDforeignkeyreferencesrelationer(RID),
CPswchar(6)null,
}
--为数据表Relationer添加两个新列,新列名称和数据类型分别为Rid,char(10),主键;Rsex,char
(2);
altertablerelationer
addcolumnRidchar(10),Rsexchar
(2)
【添加主键】
altertablerelationer
addconstraintpk_relationerprimarykey(Rid)
go
【删除主键】
altertablecategory
dropconstraintPK_category
--修改Customer表中的名为Cname列的数据类型,将其原数据长度从改为;
altertableCustomer
altercolumnCnamevarchar(20)
go
【添加外键约束(带级联删除)】
altertableproduct
addconstraintFK_product_categoryFOREIGNKEY(类别ID)
referencescategory(类别ID)ondeletecascade
【添加外键约束(不带级联删除)】
altertableproduct
addconstraintFK_product_categoryforeignkey(类别ID)
referencescategory(类别ID)
【删除外键约束】
altertableproduct
dropconstraintFK_product_category
【添加信息】
insertintocategoryvalues(9,'酒类','啤酒,白酒等')
【修改信息】
updatecategoryset类别名='肉类',说明='所有的肉质品'where类别ID=5
【删除信息】
deletefromcustomerwhere邮编='350007'
--删除客户表Customer所有数据
deletefromCustomer
go
【查询表信息】
select*fromnewRelationer
selectRid,RnamefromRelationer
--查询客户信息表信息,并将客户信息表中的客户类型值都加上“客户”两字显示
selectCid,Cname,Ctype+'客户'fromcustomer
--查询联系人信息表信息,并将结果显示标题改为中文显示;
selectrid联系人编号,rname联系人姓名,raddr地址,rtel电话
fromRelationer
--统计联系人的个数,并改变列标题显示
selectCOUNT(rid)as联系人个数
fromRelationer
--查询明细表中通话时间最长的三个客户的通话记录
selecttop3withties*fromDetails
orderbyDdurationdesc
--检索联系人中来自广州天河区的联系人记录;
select*fromRelationerwhereRaddrlike'%天河区%'
--统计年至年底期间注册的非流动客户的数据信息,结果显示客户编号、客户名称、注册日期和客户类型;
select*fromcustomer
whereyear(Cregistdate)between2002and2005andCtype<>'流动'
--统计密码非空的客户的个数
selectCOUNT(cid)客户个数fromcustomerwhereCpsdisnotnull
--检索明细表中通话时间最长和通话时间最短的通话时长;
selectMAX(dduration)as通话最长,MIN(dduration)通话最短fromDetails
--统计最早注册和最后注册的大户和商业客户的注册时间和客户数;
selectctypeas类型,MIN(cregistdate)as最早注册,
MAX(cregistdate)'最后注册',COUNT(ctype)as客户数
fromcustomerwhereCtypein('大户','商业')
groupbyCtype
--统计客户信息表中非流动客户,且客户数超过的最早注册、最后注册的注册日期及客户数:
selectctypeas类型,MIN(cregistdate)as最早注册,
MAX(cregistdate)'最后注册',COUNT(ctype)as客户数
fromcustomerwhereCtype<>'流动'
groupbyCtypehavingCOUNT(cname)>1
go
--按开通年份和产品名称统计各类型产品在当年开通的个数
selectYEAR(ebdate)as开通年份,enameas产品名称,COUNT(eno)产品数
fromEproduct
groupbyYEAR(ebdate),Enameorderby开通年份
--汇总产品编号为的客户总的通话时长及通话明细
select*fromDetailswhereeno='38011599'computesum(dduration)
--汇总不同编号产品的通话明细,并汇总每个编号产品的通话时长
selecteno,ddurationfromDetailsorderbyENo
computesum(dduration)byeno
--检索开通了ADSL拨号的产品的客户的客户编号、姓名和客户类型
selectdistinctc.cid客户编号,cname姓名,ctype类型
fromcustomerc,Eproductewherec.Cid=e.Cid
--检索开通了天气预报的客户信息,显示客户姓名、类型和对应产品号码开通天气预报服务的时间
selectasidfromAdditionalServicewhereasitem='天气预报'
selectcname,ctype,stimefromeproducte,customerc,AdditionalServicea,StartAdditionalServices
wheree.cid=c.cidande.eno=s.enoands.asid=a.asidandasitem='天气预报'
--检索所有客户的客户编号、客户名称、产品名称和客户类型信息,包括无效的客户信息
selectc.cid,cname,eno,Ctype
fromcustomercleftjoinEproducteonc.Cid=e.Cid
--根据单价查询客户购买的产品号码的消费金额
selectd.eno,dcost=sum(dduration*euniprice)
fromeproductejoindetailsdone.eno=d.enogroupbyd.ENo
--检索开通了天气预报的客户信息
select*fromcustomer
whereCidin(
selectCidfromEproductwhereENoin(
selectENofromStartAdditionalServicewhereASid=(
selectASidfromAdditionalService
whereASitem='天气预报')))
--查询购买了ADSL拨号产品的客户信息
select*fromcustomerwhereCidin(
selectCidfromEproductwhereEname='ADSL拨号')
--查询开通了移动秘书的产品号码(用相关子查询实现)
selectdistinctenofromstartAdditionalServices
whereexists(select*fromAdditionalServicea
wherea.ASid=s.ASidandASitem='移动秘书')
--将所有中国电信移动电话统一开通梦网短信,并将开通时间设置为系统当前时间
insertintoStartAdditionalService
selectdistinctENo,(selectASidfromAdditionalServicewhereASitem='梦网短信'),GETDATE()fromEproduct
whereEname='中国电信移动电话'
--查询联系人联系地址或产品安装地址是广州市的相关信息。
selectrid,raddrfromRelationer
whereRaddrlike'广州市%'
union
selectcid,eaddrfromEproduct
whereEaddrlike'广州市%'
/*一批充值卡,卡的密码随机生成,现在出现这个问题:
卡里面的“O和”(哦和零)“l和”(英文字母l和一),用户反映说看不清楚,
公司决定,把存储在数据库中的密码中所有的“哦”都改成“零”,把所有的“l”都改成“”;*/
createtablecard
(
passwordvarchar(20)
)
insertintocard
select'009o45'union
select'007l7'union
select'oo8dlh'
select*fromcard
updatecard
setpassword=replace(replace(password,'o','0'),'l','1')
--函数说明:
replace(操作数,被替换字符,替换字符)
/*13-1、-2、-3、-10、-100、-108、-18、-11、-15、-1、-2
现在希望通过SQL语句进行排序,并且首先要按照前半部分的数字进行排序,
然后再按照后半部分的数字进行排需,输出要排成这样:
13-1、-2、-3、-10、-11、-15、-18、-100、-108、-1、-2
数据库表名:
SellRecord;字段名:
ListNumber;*/
数据准备如下:
createtablesellRecord
(
listNumberchar(10)
)
insertintosellRecord
select'13-1'union
select'13-2'union
select'13-3'union
select'13-10'union
select'13-100'union
select'13-108'union
select'13-18'union
select'13-11'union
select'13-15'union
select'14-1'union
select'14-2'
select*fromsellRecord
/*编写脚本如下:
(1)目标:
截取’-‘字符前半部分,后半部分
(2)查询排序*/
/*首要目标:
找寻'-'的位置
charindex(所找子串,被找子串[,起始位置])
len(字符串)--获取字符串的长度
第二过程:
截取前半部分
substring(字符串,起始点,长度)--截取字串
第三过程:
转换类型
convert(转换类型,操作数)*/
createtablesellRecord
(
listNumberchar(10)
)
insertintosellRecord
select'13-1'union
select'13-2'union
select'13-3'union
select'13-10'union
select'13-100'union
select'13-108'union
select'13-18'union
select'13-11'union
select'13-15'union
select'14-1'union
select'14-2'
insertintosellRecord
select'141-3'
select*fromdbo.sellRecord
select*fromsellRecord
orderby
convert(int,substring(listNumber,1,charindex('-',listNumber)-1)),
--首先截取前部分已-作为分界点,其次将截取的数字字符转换为int类型
convert(int,substring(listNumber,charindex('-',listNumber)+1,len(listNumber)-charindex('-',listNumber)))
--首先截取后部分,以-作为分界点,其次将截取的数字字符转换为int类型
1.现在请设计一张学生的考勤状态表。
由于该表访问频繁,数据库设计人员欲将其放置在文件组fg_kq中:
a)为现有数据库添加一个文件组fg_kq;
答:
useteachdb
go
alterdatabaseteachdb
addfilegroupfp_kp
useteachdb
go
--添加文件组
alterdatabaseteachdb
addfilegroupfg_kq
b)添加一个次数据文件kqdata,文件保存在考生目录文件夹的teachdb文件夹中;
答:
alterdatabaseteachdb
addfile(name=kqdata,
filename='D:
\sql\teachdb\teachdbkqdata.ndf',
size=5,
filegrowth=1)
tofilegroupfp_kp
--添加数据文件
alterdatabaseteachdb
addfile
(
name=kqData,
filename='D:
\sqlserver\teachdb\kqData.ndf'
)tofilegroupfg_kq
c)按如下表结构,建立数据表,并且创建到次数据文件中;
StucardInfo(学生考勤状态表)
字段名称
字段说明
备注
ID
记录编号
自动标识,主键
S_no
学号
引用学生表的学号
C_no
课程号
引用课程表的课程号
CardTime
刷卡时间
ClassTime
上课时间
Status
考勤状态
限制考勤状态取0,1,2,3:
备注:
已到(0)、请假
(1)、旷课
(2)、迟到(3)
答:
createtableStucardInfo
(
IDintidentity(1,1)primarykey,
s_nochar(13)notnull,
c_nochar(4)notnull,
CardTimedatetime,
ClassTimedatetime,
Statusint,
)
altertableStucardInfo
addconstraintFK_S_noforeignkey(s_no)referencesstudent(s_no)ondeletecascade
altertableStucardInfo
addconstraintFK_C_noforeignkey(c_no)referencescourse(c_no)ondeletecascade
--创建学生考勤表
createtablestucardinfo
(idintidentity,
snochar(13),
cnochar(4),
cardtimedatetime,
classtimedatetime,
sstatuschar
(1)check(sstatusin('0','1','2','3'))
constraintpk_stucardprimarykey(id),
constraintfk_stu_stucardforeignkey(sno)referencesstudent(s_no),
constraintfk_cour_stucardforeignkey(cno)referencescourse(c_no)
)onfg_kq
2.检索参加考试的学生学号,姓名,课程名称,成绩,按学生成绩由高到低排序。
将该检索结果保存成视图;
答:
selectdistincts.s_no学生学号,s.s_name学生姓名,e.score成绩
fromstudentsjoinchoiceeons.s_no=e.s_no
orderbye.scoredesc
go
--2.创建一视图,显示参加考试学生学号,姓名,课程名称,成绩,按学生成绩由高到低排序。
createviewvScore
as
selecttop100percent学号=s.s_no,姓名=s_name,课程名称=c_name,成绩=scorefromstudents,coursec1,choicec2
wheres.s_no=c2.s_noandc1.c_no=c2.c_no
orderbyc2.scoredesc
go
3.新学年教师基本工资提档,教授涨200元,副教授150,讲师100,助教80。
请相应更新教师表的教师基本工资(t_salary)。
答:
declare@t_salary
selectt_salary,t_duty=
case
when'教授'then@t_salary=@t_salary+200
when'副教授'then@t_salary=@t_salary+150
when'讲师'then@t_salary=@t_salary+100
when'助教'then@t_salary=@t_salary+80
end
fromteacher
--3.新学年教师基本工资提档,教授涨200元,副教授150,讲师100,助教80。
--请相应更新教师表的教师基本工资(t_salary)。
updateteacher
sett_salary=
caset_duty
when'教授'thent_salary+200
when'副教授'thent_salary+150
when'讲师'thent_salary+100
when'助教'thent_salary+80
end
4.删除张大维老师的教学记录。
答:
deletefromteachingwheret_noin(selectt_nofromteacherwheret_name='张大维')
--4.删除张大维老师的教学记录。
deletefromteachingwheret_no=(
selectt_nofromteacherwheret_name='张大维')
5.检索林楠老师的职称等级,显示教师名称+等级,如‘林楠老师是中级职称’
根据职称判定教师的职称等级,等级评定如下:
职称等级
教授高级职称
副教授副高级职称
讲师中级职称
助教初级职称
其他暂未评定职称等级
答:
selectt_name姓名,t_duty职称,等级=
case
whent_duty='教授'then'是高级职称'
whent_duty='副教授'then'是副高级职称'
whent_duty='讲师'then'是中级职称'
whent_duty='助教'then'是初级职称'
whent_duty='其他'then'暂未评定职称等级'
end
fromteacher
--5.检索林楠老师的职称等级,并输出显示教师名称+等级,如‘林楠老师是
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 大二 上期 总结