SQL大二年级上册期末总结.pdf

SQL大二上期末总结【新建数据库】c r e at e d at ab as e B il l in gS y so n p r im ar y(n am e B il l in gS y s,f il e n am e d:B il l in gS y s B il l in gs,m d f ,s iz e =3,f il e gr o w t h=l),f i l e gr o up f p _b s 一自定义文件组(n am e b s _d at a,f il e n am e=d:B il l in gS y s b s d at a,n d f,s iz e 5,f il e gr o w t h=1)l o go n(n am e B il l in gS y s _l o g,f il e n am e d:B il l in gS y s B il l in gS y s _l o g.,s iz e=l,m ax s iz e 20,f il e gr o w t h=109 6)【指定当前数据库】us e B il l in gS y sgo【移除文件】al t e r d at ab as e B il l in gS y sr e m o v e f il e b s _d at a【增加文件组】al t e r d at ab as e B il l in gS y sad d f il e gr o up f p _b s【增加数据文件】al t e r d at ab as e B il l in gS y sad d f il e(n am e b s _d at a,f il e n am e=，d:B i1l in gS y s b s _d at a.n d f,s iz e=5,f il e gr o w t h=l)t o f il e gr o up f p _b s【修改数据库】A l t e r d at ab as e 数据库名A d d f il e ,)【删除数据库】us e m as t e rd r o p B il l in gS y s【查询数据库相关参数】us e B il l in gS y se x e c s p he l p d b【查询数据库空间信息】us e B il l in gS y se x e c s p _s p ac e us e d【查询数据库选项信.息】us e B il l in gS y se x e c s p _d b o p t io n【自动收缩】al t e r d at ab as e B il l in gS y ss e t aut o s hr in k o n/o f f【手动收缩数据库】d b c c s hr in k d at ab as e(0,20)【分离数据库】us e m as t e rgos p _d e t ac h d b d b n am e=，B il l in gS y s【附加数据库】us e m as t e rgos p _at t ac h_d b d b n am e B il l in gS y s5,f il e n am e J d:B il l in gS y s B il l in gs.m d f，【分离数据库】Us e r m as t e rEX EC s p _d e t ac h_d b d b n am e=B il l in gS y s,【建表日建主外键】Cr e at e t ab I e c us t o m e r(CI D in t n o t n uI I p r im ar y k e y,R I D i n t c o n s t r a i n t f k _R I D f o r e i gn k e y r e f e r e n c e s r e I at i o n e r (R I D),CP s w c har (6)n ul l,)一为数据表R e l at io n e r添加两个新列，新列名称和数据类型分别为R id,c har(10),主键;Rsex,char（2）;altertable relationeraddcolumn Rid char（10）,Rsex char（2）【添加主键】altertable relationeraddconstraint pk _relationer primarykey（Rid）go【删除主键】alter table categorydrop constraint PK_category一修改Customer表中的名为Cname列的数据类型，将其原数据长度从改为;altertable Customeraltercolumn Cname varchar（20）go【添加外键约束（带级联删除）】alter table productadd constraint FK_product_category FOREIGN KEY（类别 ID）references category（类别 ID）on deletecascade【添加外键约束（不带级联删除）】alter table productadd constraint FK_product_category foreignkey（类别 ID）references category（类别 ID）【删除外键约束】alter table productdrop constraint FK_product_category【添加信息】insert into category values(9；酒 类 啤 酒,白 酒 等 )【修改信息】update category se t类别名=肉类，说明=所有的肉质品where类别ID=5【删除信息】delete from customer where 邮编=350007一删除客户表Customer所有数据deletefrom Customergo【查询表信息】select*from newRelationerselect Rid.Rname from Relationer一查询客户信息表信息，并将客户信息表中的客户类型值都加上“客户”两字显示select Cid,Cname,Ctype+客户from customer一查询联系人信息表信息，并将结果显示标题改为中文显示；select rid联系人编号,rname联系人姓名，raddr地址,rtelfrom Relationer一统计联系人的个数，并改变列标题显示selectCOUNT(rid)as 联系人个数from Relationer一查询明细表中通话时间最长的三个客户的通话记录selecttop 3 withtiesfrom Detailsorderby Dduration desc-检索联系人中来自广州天河区的联系人记录；select*fromRelationerwhere Raddr like%天河区对一统计年至年底期间注册的非流动客户的数据信息，结果显示客户编号、客户名称、注册日期和客户类型；select*from customerwhereyear(Cregistdate)between 2002 and 2005 and Ctype 流动一统计密码非空的客户的个数selectCOUNT(cid)客户个数from customerwhere Cpsd isnotnull一检索明细表中通话时间最长和通话时间最短的通话时长；_selectMA X(dduration)as 通话最长，MIN(dduration)通话最短from Details 统计最早注册和最后注册的大户和商业客户的注册时间和客户数；select ctype as 类型，MIN(cregistdate)as 最早注册，MA X(cregistdate)*最后注册,COUNT(ctype)as 客户数from customerwhere Ctype in(大户，商业)groupby Ctype一统计客户信息表中非流动客户，且客户数超过的最早注册、最后注册的注册日期及客户数：select ctype as 类型，MIN(cregistdate)as 最早注册、MA X(cregistdate)*最后注册,COUNT(ctype)as 客户数from customerwhere Ctype 流动groupby CtypehavingCOUNT(cname)1go一按开通年份和产品名称统计各类型产品在当年开通的个数selectYEA R(ebdate)as 开通年份,ename as 产品名称,COUNT(eno)产品数from EproductgroupbyYEA R(ebdate),Enameorderby 开通年份一汇总产品编号为的客户总的通话时长及通话明细select*from Detailswhere eno 1 38011599?computesum(dduration)一汇总不同编号产品的通话明细，并汇总每个编号产品的通话时长select eno,dduration from DetaiIsorderby ENocomputesum(dduration)by eno一检索开通了A DSL拨号的产品的客户的客户编号、姓名和客户类型selectdistinct c.cid 客户编号，cname 姓名，ctype 类型from customer c,Eproduct ewhere c.Cid e.Cid一检索开通了天气预报的客户信息,显示客户姓名、类型和对应产品号码开通天气预报服务的时间select asid from A dditiona 1 Servicewhere asitem 天气预报select cname,ctype,stime from eproduct e,customer c,A dditionalServicea,StartA dditionalService swhere e.cid=c.cid and e.eno=s.eno and s.asid=a.asid and asitenp天气预报一检索所有客户的客户编号、客户名称、产品名称和客户类型信息，包括无效的客户信息select c.cid,cname,eno,Ctypefrom customer c leftjoin Eproduct e on c.Cid-e.Cid根据单价查询客户购买的产品号码的消费金额select d.eno,dcost sum(dduration*euniprice)from eproduct e join details d on e.eno=d.enogroupby d.ENo一检索开通了天气预报的客户信息select*from customerwhere Cid in(select Cid from Eproduct where ENo in(select ENo from StartA dditionalService where A Sid=(select A Sid from A dditionalServicewhere A S item=天气预报)一查询购买了 A DSL拨号产品的客户信息select*from customer where Cid in(select Cid from Eproduct where Ename A DSL拨号)一查询开通了移动秘书的产品号码(用相关子查询实现)selectdistinct eno from StartA dditionalService swhereexists(select*from A dditionalService awhere a.A Sid=s.A Sid and A Sitem=，移动秘书)一将所有中国电信移动统一开通梦网短信，并将开通时间设置为系统当前时间inser。