数据库系统原理与设计实验教程实验1.docx
《数据库系统原理与设计实验教程实验1.docx》由会员分享，可在线阅读，更多相关《数据库系统原理与设计实验教程实验1.docx（18页珍藏版）》请在冰豆网上搜索。
数据库系统原理与设计实验教程实验1
实验一简单查询
在订单数据库中，完成如下的查询：
（1）查询所有业务部门的员工姓名、职称、薪水。
selectemployeeName,headShip,salary
fromemployee
wheredepartment='业务科'
（2）查询名字中含有“有限”的客户姓名和所在地。
selectCustomerName,address
fromCustomer
whereCustomerNamelike'%有限%'
（3）查询出姓“王”并且姓名的最后一个字为“成”的员工。
select*
fromemployee
whereemployeeNamelike'王%成'
没有结果
（4）查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。
selectemployeeName,department,headship,address,
sex=Casesex
when'M'then'男'
when'F'then'女'
end
fromemployee
whereaddresslike'%上海%'oraddresslike'%南昌%'andsex='F'
（5）在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。
selectorderNo
fromOrderDetail
groupbyorderNo
havingsum（quantity*price）>=10000
（6）选取订单金额最高的前10%的订单数据。
SELECTTOP10PERCENTorderNo
FROMOrderdetail
GROUPBYorderNo
ORDERBYsum（quantity*price）DESC
（7）查询出职务为“职员”或职务为“科长”的女员工的信息。
select*
fromemployee
where（headship='职员'orheadship='科长'）andsex='F'
（8）查找定单金额高于8000的所有客户编号。
1）查询总金额高于8000元的客户编号
selectCustomerNo
fromOrderMastera,Orderdetailb
wherea.orderNo=b.orderNo
groupbyCustomerNo
havingsum（quantity*price）>8000
2）查找定单金额高于8000的所有客户编号
selectCustomerNo
fromOrderMaster
whereorderNoin（selectorderNo
fromOrderDetail
groupbyorderNo
havingsum（quantity*price）>8000）
（9）选取编号界于“C”和“C”的客户编号、客户名称、客户地址。
selectCustomerNo,CustomerName,address
fromCustomer
whereCustomerNobetween'C'and'C'
（11）找出同一天进入公司服务的员工。
Selecta.employeeNo,a.employeeName,b.employeeNo,b.employeeName
fromEmployeea,Employeeasb
wherea.employeeNo!
=b.employeeNoanda.employeeName>b.employeeName
and（a.hireDate=b.hireDate）
（12）在订单主表中查询订单金额大于“E业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。
1）首先计算订单主表的订单金额
updateOrderMastersetorderSum=totalSum
fromOrderMastera,（selectorderNo,sum（quantity*price）totalSum
fromOrderDetail
groupbyorderNo）b
wherea.orderNO=b.orderNo
2）
SELECT*
FROMOrderMaster
WHEREorderSum>any
（SELECTorderSum
FROMOrderMaster
WHEREsalerNo='E'ANDorderDate=''）
（13）查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
SelectcustomerNo,orderNo,orderSum
fromOrderMaster
wherecustomerNoin
（selectcustomerNo
fromOrderMastera,OrderDetailb,Productc
wherea.orderNo=b.orderNoandb.productNo=c.productNoand
productName='52倍速光驱'）
andcustomerNoin（selectcustomerNo
fromOrderMastera,OrderDetailb,Productc
wherea.orderNo=b.orderNoandb.productNo=c.productNoand
productName='17寸显示器'）
（14）查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
Selectb.employeeName,b.sex,b.department,b.headShip
fromEmployeea,Employeeb
wherea.department=b.departmentanda.employeeName='陈诗杰'
（15）查询每种商品的商品编号、商品名称、订货数量和订货单价。
SELECTa.productNo,productName,quantity,price
FROMProducta,OrderDetailb
WHEREa.productNo=b.productNo
ORDERBYproductName,price
（16）查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
SELECTa.productNo,productName,quantity,price
FROMProducta,OrderDetailb
WHEREa.productNo=b.productNoANDprice>400
ORDERBYproductName
（17）分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。
左外连接命令：
SELECTa.productNo,productName,quantity,price
FROMProductaLEFTOUTERJOINOrderDetailb
ONa.productNo=b.productNo
WHEREprice>400
Selecta.productNo,a.productName,b.quantity,b.price
FromOrderDetailAsbleftJOINProductAsa
ON（a.productNo=b.productNo）
andprice>400
Orderbya.productNo
右外连接命令：
SELECTa.productNo,productName,quantity,price
FROMProductaRIGHTOUTERJOINOrderDetailb
ONa.productNo=b.productNo
WHEREprice>400
Selecta.productNo,a.productName,b.quantity,b.price
FromOrderDetailAsbRIGHTJOINProductAsa
ON（a.productNo=b.productNo）
andprice>400
Orderbya.productNo
全连接命令：
SELECTa.productNo,productName,quantity,price
FROMProductaFULLOUTERJOINOrderDetailb
ONa.productNo=b.productNo
WHEREprice>400
Selecta.productNo,a.productName,b.quantity,b.price
FromOrderDetailAsbfullJOINProductAsa
ON（a.productNo=b.productNo）
andprice>400
Orderbya.productNo
（18）分别使用左外连接、右外连接、完整外部连接查询客户编号、客户名称、订货金额和订单编号，并分析比较检索的结果。
SELECTa.customerNo,customerName,orderSum,orderNo
FROMCustomeraleftOUTERJOINOrderMasterb
ONa.customerNo=b.customerNo
SELECTa.customerNo,customerName,orderSum,orderNo
FROMCustomerarightOUTERJOINOrderMasterb
ONa.customerNo=b.customerNo
SELECTa.customerNo,customerName,orderSum,orderNo
FROMCustomerafullOUTERJOINOrderMasterb
ONa.customerNo=b.customerNo
从上述结果可知：
若表a和表b做外连接，且b表是外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。
（18）查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。
SELECTsalerNo,employeeName,
CASEsexWHEN'M'THEN'男'
WHEN'F'THEN'女'
END性别,
productName,quantity,price,orderSum,
convert（char（10）,orderDate,120）orderDate
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNo
ANDc.productNo=d.productNo
ORDERBYsalerNo
（19）查找在2008年3月中有销售记录的客户编号、名称和订单总额。
SELECTa.customerNo,customerName,orderSum
FROMOrderMastera,Customerb
WHEREyear（orderDate）=2008ANDmonth（orderDate）=3
ANDa.customerNo=b.customerNo
（20）使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。
SELECTa.customerNo,customerName,convert（char（10）,orderDate,120）orderDate,orderSum
FROMCustomeraLEFTOUTERJOINOrderMasterbONa.customerNo=b.customerNo
ORDERBYa.customerNo,orderSumdesc
（21）查找16MDRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。
SELECTemployeeName,CASEsex
WHEN'M'THEN'男'
WHEN'F'THEN'女'
ENDsex,
orderDate,quantity,quantity*pricetotal
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoAND
c.productNo=d.productNoANDproductName='16MDRAM'
（22）查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。
selecta.employeeNo,a.employeeName,a.sex,d.ProductName,
quantity,price,price*quantitytotal,orderDate
fromEmployeea,OrderMasterb,OrderDetailc,Productd
wherea.employeeNo=b.salerNoANDb.orderNo=c.orderNoANDc.ProductNo=d.ProductNo
（23）查询客户姓名为“客户丙”所购货物的“客户名称”、“定单金额”、“定货日期”和“电话号码”。
SELECTCustomerName,orderSum,orderDate,telephone
FROMCustomera,OrderMasterb
WHEREa.CustomerNo=b.CustomerNoANDa.CustomerName='客户丙'
（24）找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。
SELECTorderNo,orderSum
FROM（SELECTorderNo,orderSum
FROMOrderMastera,Employeeb
WHEREa.salerNo=b.employeeNoANDb.sex='M'）c
WHEREordersum>2000
或：
SELECTorderNo,orderSum
FROMOrderMastera,Employeeb
WHEREa.salerNo=b.employeeNoANDb.sex='M'andordersum>2000
（25）查询来自上海市的客户的姓名、电话、订单号及订单金额。
SELECTCustomerName,telephone,orderNo,orderSum
FROMCustomera,OrderMasterb
WHEREa.CustomerNo=b.CustomerNoANDa.addressLIKE'%上海%'
（26）实验问题
①连接操作类型有哪些？
分析外连接在现实应用中的意义。
答：
连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。
外连接的意义：
对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。
在现实中对于数据库的数据初始化很有用。
②查询表可以用在什么地方？
使用查询表要注意哪些地方？
答：
查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。
查询表是执行过程中产生的表，执行完后其表自动丢失。
子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。
③分析条件BETWEEN...AND、AND、OR等关键字的使用方法。
答：
BETWEEN...AND是范围查询，可用于查询属性值在某一个范围内的元组，NOTBETWEEN….AND可用于查询属性值不在某个范围内的元组，BETWEEN后是属性的下限值，AND后是属性的上限值。
AND、OR是逻辑查询，AND实现逻辑与运算，要注意的是在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。
用BETWEEN…..AND能实现的查询，也可以用AND来达到。
同样，用NOTBETWEEN…AND能实现的查询，也可以用OR来实现。
④请总结SQL语句中的单表查询语句的使用方法。
⒈查询所有列
SELECT*FROM[表名]
2.查询指定列
SELECT[字段列表]FROM[表名]
3.消除重复元组：
SELECTDistinct[字段列表]FROM[表名]
4.查询经过计算的列
SELECT【字段及字段表达式】FROM【表名】
5.指定查询结果的查询
SELECTTOPn[PERCENT]【字段列表】FROM【表名】
6.给属性取别名
①：
SELECT【字段名】1as''A''FROM【表名】
②：
SELECT"A"=字段名1FROM【表名】
③：
SELECT字段名1"A"FROM【表名】
7.选择查询
SELECT【字段名列表】FROM【表名】WHERE【条件表达式】
条件表达式运算符：
比较运算：
=,<,<=,>,>=,!
<,!
>,!
=,<>
逻辑查询：
or,AND,not
范围查询：
between…AND,notbetween…AND
集合查询：
IN,notIN
字符匹配查询：
ike,notLIKE
空值查询：
isnull,isnotnull
8.排序查询
SELECT【字段名1】，【字段名2】FROM【表名】
WHERE【条件表达式】
ORDERBY【字段表达式1】，【字段表达式2】[ASC][DESC]
9.分组查询
①SELECT字段名列表FROM表名[WHERE条件表达式]
GROUPBY字段名
②SELECT字段名列表FROM表名[WHERE条件表达式]
GROUPBY字段名HAVING筛选表达式
10.聚合查询
SELECTcount（[ALL|DISTINCT]{*|字段名列表}）FROM表名
[WHERE条件表达式]
SELECTsum|avg|max|min（[ALL|DISTINCT]<字段名列表>）
FROM表名
[WHERE条件表达式]
⑤分析哪几种情况需要使用自表连接。
当同表中的某个元组变量与表中的另外的元组变量相关时，可以使用自表连接，从而得到一些特殊的数据或者是说带特定条件的数据。
自表连接一般是用来判断并筛选单一表中的一些数据。
升级会员 