实验3-SQL的连表、子查询

实验3SQL的连表与子查询一、实验目的学习掌握使用SQL的SELECT语句对表进行查询操作。二、实验准备1.环境操作系统：Windows7或Windows200XServer数据库管理系统：SQLServer20082.准备工作⑴本次实验使用超市的商品销售关系数据库CRM。从ftp服务器上该数据库拷贝到D：盘的自己建立存储数据库文件的文件夹下。在SQLServer2008中利用数据库附加功能将数据库附加到系统中。(2)复习SELECT语句的基本语法格式，学习使用SELECT语句的连表查询、子查询和集函数等查询方法。三、实验内容使用SQL的SELECT语句完成如下操作：1.连表查询2.子查询四、实验要求使用SELECT语句在关系数据库CRM中完成下面的操作：1.连表检索①查询会员马驰所购买的商品号、商品名、单价、数量与金额。SELECTMGQ.Commodityno,Commodityname,sales.Price,num,SUMFROMmemberJOINMGQONmember.Memberno=MGQ.MembernoJOINSalesONMGQ.Commodityno=Sales.CommoditynoWHEREMembername='马驰'②查询会员中所有比马驰年龄大的会员号，会员名，年龄和马驰的年龄。SELECTX.Memberno,X.Membername,X.AGE,Y.AGEFROMMemberX,MemberYWHEREX.AGEY.AGEANDY.Membername='马驰';③查询所有会员的会员号，会员名和所购买商品的商品号、商品名、单价、数量与金额，以及商品的供货商名与电话。SELECTmember.Memberno,MGQ.Commodityno,sales.Commodityname,sales.Price,MGQ.num,SUM,Supname,supplier.TelephoneFROMmemberJOINMGQONmember.Memberno=MGQ.MembernoJOINSalesONMGQ.Commodityno=Sales.CommoditynoJOINInventoryONSales.Commodityno=Inventory.CommoditynoJOINSupplierONInventory.Supno=Supplier.Supno2.子查询与嵌套查询①查询购买商品名为“海信电视”的会员号、会员名和单价、数量与金额，结果按金额降序排序。SELECTMember.Memberno,Membername,sales.Price,num,SUMFROMmemberJOINMGQONmember.Memberno=MGQ.MembernoJOINSalesONMGQ.Commodityno=Sales.CommoditynoWHERESales.CommoditynameIN(SELECTCommoditynameFROMsalesWHERECommodityname='海信电视')ORDERBYSUMDESC②查询会员中所有比会员马驰年龄大的会员号，会员名，年龄。SELECTX.memberno,X.membername,X.AgeFROMMemberXJOINMemberYONX.AGEY.AgeWHEREY.Membername='马驰'②查询会员中同时购买商品号为01和02的会员号和姓名。SELECTmember.Memberno,membernameFROMMemberJOINMGQONMember.Memberno=MGQ.MembernoWHEREMember.Memberno='01'ANDMGQ.MembernoIN(SELECTMembernoFROMMemberWHEREMemberno='02')③查询会员中没有购买商品号为01的会员号和会员名。SELECTdistinctmember.Memberno,membernameFROMMemberJOINMGQONMember.Memberno=MGQ.MembernoWHEREmember.Membernonotin(SELECTMembernoFROMMemberWHEREMemberno='01')④查询会员中购买的所有商品单价都是大于等于2000元、购买数量大于等于2的会员号和会员名。SELECTmember.Memberno,membernameFROMMemberJOINMGQONMember.Memberno=MGQ.MembernoWHEREMGQ.Membernonotin(SELECTPriceFROMMGQWHEREprice2000)(SELECTNumFROMMGQWHERENum2)⑤查询无人购买商品的商品号和商品名（即全部商品集合与有人购买的商品集合的差集）。SELECTDISTINCTMGQ.Commodityno,sales.CommoditynameFROMMGQJOINSalesONMGQ.Commodityno=Sales.CommoditynoWHEREMGQ.CommoditynoNOTIN(SELECTCommoditynoFROMMGQWHEREMGQ.Commodityno=Sales.Commodityno)⑥查询会员中所购买商品中包含了会员为0201所购买全部商品的会员号和会员名。SELECTDISTINCTMember.Memberno,Member.MembernameFROMMemberWHERENOTEXISTS(SELECT*FROMMGQaSXWHEREX.Memberno='0201'ANDNOTEXISTS(SELECT*FROMMGQASYWHEREY.Memberno=Member.MembernoANDY.Memberno=X.Memberno))五、实验报告实验报告严格按照《实验报告书》模板来写。内容如下：一、实验目的二、实验环境三、查询操作的SQL语句四、总结与结果分析附录1超市的商品销售关系数据库CRM中的5个关系模式1)商品库存表：Inventory(Commodityno,Commodityname,Price,Num,Address,Supno)，主键：Commodityno其中：Commodityno,Commodityname,Price,Num,Address,Supno中文含义分别为商品号,商品名,单价,库存数量,产地,供货商号2)供货商表：Supplier(Supno,Supname,Address,Email,Telephone,Person)，主键：Supno其中：Supno,Supname,Address,Email,Telephone,Personh中文含义分别为供货商号,供货商名,通信地址,Email,电话,供货商负责人。3)销售记录表：Sales(Date，Salesorder，Commodityno，Commodityname,Salesnum，Price，Sum)，主键：{Date，Salesorder}其中：Date，Salesorder，Commodityno，Commodityname,Salesnum，Price，Sum中文含义分别为日期，销售顺序号,商品号,商品名,销售数量,单价,金额。4)会员购买商品单价数量表:M-G-Quantity(Memberno，Commodityno，Price，Num)，主键：{Memberno，Commodityno}其中：Memberno，Commodityno，Price，Num中文含义分别为会员号,商品号,单价,数量。5)会员表:Member(Memberno，Membername，Sex，Age，Email，Telephone，Class)，主键：Memberno其中：Memberno，Membername，Sex，Age，Email，Telephone，Class中文含义分别为会员号，会员名，性别，年龄，EMAIL，电话，等级。附录2相关的SQL语句：1.SQL的SELECT语句的语法格式SELECT[ALL|DISTINCT][TOPexpression[PERCENT][WITHTIES]]目标列表达式[,目标列表达式[,…]][INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression[HAVINGsearch_condition]][ORDERBYorder_expression[ASC|DESC]][COMPUTE{{AVG|COUNT|MAX|MIN|SUM}(expression)}[,...n][BYexpression[,...n]]]2.连表查询⑴JOIN连接语法格式SELECT……FROMtable_source[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOINtable_sourceONjoined_condition[[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOINtable_sourceONjoined_condition,n][WHEREsearch_condition]其中FROM子句要指明进行连接的表和连接条件。INNER表示为内连接(默认值),表示只有满足连接条件的记录才能出现在查询结果中；OUTER表示为外连接，外连接又分为LEFT(左)、RIGHT(右)和FULL(全)连接：LEFT[OUTER]指定在结果集中包含左表中所有满足和不满足联接条件的行，将左表不满足联接条件的行的右表的输出列值设为NULL。RIGHT[OUTER]指定在结果集中包含右表中所有不满足联接条件的行，将右表不满足联接条件的行的左表的输出列值设为NULL。FULL[OUTER]指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为NULL。这是对通常由INNERJOIN返回的所有行的补充。ON连接条件格式：[表名1.]列名1比较运算符[表名2.]列名2WHERE子句中要指明查询条件。⑵WHERE连接语法格式SELECT……FORM表名列表WHERE连接条件AND[查询条件]其中FROM子句要指明进行连接的表名列表；(例如，表1,表2[，…n])WHERE子句中要指明连接的列名及其连接条件。连接条件：[表名1.]列名1比较运算符[表名2.]列名2[AND…n]