《数据库原理.doc》由会员分享,可在线阅读,更多相关《数据库原理.doc(6页珍藏版)》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、如有侵权,请联系网站删除,仅供学习与交流数据库原理【精品文档】第 6 页实验三 单表查询1 实验目的与要求(1) 掌握SQL查询语句的基本概念。(2) 掌握SQL Server查询语句的基本语法。(3) 熟练使用SQL的SELECT语句对单表进行查询。(4) 熟练掌握并运用SQL Server所提供的函数。(5) 熟练使用SQL语句进行单表聚合操作。2 实验内容在订单数据库OrderDB中,完成如下的查询:(1) 查询员工的姓名、职务和薪水。(2) 查询名字中含有“有限”的客户名称和所在地。(3) 查询出姓“张”并且姓名的最后一个字为“梅”的员工。(4) 查询住址中含有“上海”或“南昌”的女员
2、工,并显示其姓名、所属部门、职务、住址、出生日期和性别,其中如果出生日期为空,显示“不详”,否则按格式“yyyy-mm-dd”显示,性别用“男”和“女”显示。(5) 查询出职务为“职员”或职务为“科长”的女员工的信息。(6) 选取编号不在C20050001C20050004之间的客户编号、客户名称、客户地址。(7) 在表OrderMaster中挑出销售金额大于等于5000元的订单。先统计订单主表中的订单金额,使用命令:(8) 在订单主表中选取订单金额最高的前10%的订单数据。(9) 计算出一共销售了几种商品。(10) 计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额,
3、并且依据销售金额由大到小排序输出。(11) 按客户编号统计每个客户2008年2月的订单总金额。(12) 统计至少销售了10件以上的商品编号和销售数量。(13) 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。(14) 实验问题: 给出SQL语句实现分组聚集操作的执行过程。 WHERE和HAVING子句都是用于指定查询条件的,请给出你对这两个子句的理解,用实例说明。 在分组聚集操作中,为什么在查询列中,除了集聚函数运算,其它表达式必须包含在GROUP BY子句中。 分析条件BETWEEN . AND、AND、OR等关键字的使用方法。 请总结SQL语句中的单表查询语句的使用方法
4、。(1) 查询员工的姓名、职务和薪水。select employeeName48, headship48,salary48from Employee48(2) 查询名字中含有“有限”的客户名称和所在地。select customerName48,address48from Customer48where customerName48 like %有限%(3) 查询出姓“张”并且姓名的最后一个字为“梅”的员工。select employeeName48from Employee48where employeeName48 like %张_梅%(4) 查询住址中含有“上海”或“南昌”的女员工,并显
5、示其姓名、所属部门、职务、住址、出生日期和性别,其中如果出生日期为空,显示“不详”,否则按格式“yyyy-mm-dd”显示,性别用“男”和“女”显示。select employeeName48,department48, headship48,address48,birthday48,CASE sex48WHEN M THEN 男WHEN F THEN 女END sex48,isnull(convert(char(10),birthday48,120),不详)birthday48from Employee48where address48 like %上海%or address48 like
6、%南昌%(5) 查询出职务为“职员”或职务为“科长”的女员工的信息。select *from employee48where (headship48=科长 or headship48=职员) and sex48=F(6) 选取编号不在C20050001C20050004之间的客户编号、客户名称、客户地址。select customerNo48,customerName48,address48from customer48where customerNo48 not in(C20050001,C20050004)(7) 在表OrderMaster中挑出销售金额大于等于5000元的订单。更新订单脚
7、本update OrderMaster48 set orderSum48=sum2from OrderMaster48 a,(select orderNo48,sum(quantity48*price48) sum2from OrderDetail48group by orderNo48) bwhere a.orderNo48=b.orderNo48挑出订单select *from OrderMaster48where orderSum48=5000(8) 在订单主表中选取订单金额最高的前10%的订单数据。select top 10 percent *from OrderMaster48ord
8、er by orderSum48 desc(9) 计算出一共销售了几种商品。select count(distinct productNo48) account48from OrderDetail48(10) 计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额,并且依据销售金额由大到小排序输出。select productNo48,sum(quantity48) quantity48,avg(price48) avgprice48,sum(quantity48*price48) 总销售金额from OrderDetail48group by productNo48ord
9、er by 总销售金额desc(11) 按客户编号统计每个客户2008年2月的订单总金额。select b.customerNo48,customerName48,sum(orderSum48) ordersumfrom customer48 a ,ordermaster48 bwhere a.customerNo48=b.customerNo48and year(orderDate48)=2008and month(orderDate48)=2group by b.customerNo48,customerName48(12) 统计至少销售了10件以上的商品编号和销售数量。select pr
10、oductNo48,sum(quantity48) total_quantityfrom OrderDetail48group by productNo48having sum(quantity48)=10(13) 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。select count(employeeName48) aoount48,avg(salary48) avgsalary48from employee48where department48=业务科 and (year(birthday48)=1973 or year(birthday48)=1967)(14)
11、实验问题: 给出SQL语句实现分组聚集操作的执行过程。答:1:首先执行FROM语句,查找到各表数据2:其次执行WHERE语句,对数据进行筛选以及表与表的连接3:然后执行GROUP BY语句,将数据划分组4:使用聚集函数进行计算5:使用HAVING语句进行筛选分组 WHERE和HAVING子句都是用于指定查询条件的,请给出你对这两个子句的理解,用实例说明。答:WHERE子句:作用于整个查询对象,对元组进行过滤,不可以和函数一起用。HAVING子句:仅作用于分组,对分组进行过滤。必须与GROUP BY配合使用。举例:WHERE:(1) 查询所有业务部门的员工姓名、职称、薪水。SELECT empl
12、oyeeName48,headShip48,salary48FROM employee48WHERE department48=业务科HAVING:查询最高分在80以上的每个同学的平均分和最高分 SELECT studentNo,avg(score) 平均分,max(score) 最高分 FROM Score GROUP BY studentNo HAVING max(score)=80 在分组聚集操作中,为什么在查询列中,除了集聚函数运算,其它表达式必须包含在GROUP BY子句中。答:如果查询列除集聚函数外的表达式不包含在GROUP BY子句中,则聚合没有意义,如在订单数据库中,查询每个客
13、户的客户编号、订单编号和订单总额SELECT customerNo48,orderNo48,sum(orderSum)FROM OrderMaster48GROUP BY customerNo48只按客户号分组的话,语义变得不明确,因为一个客户可能有多张订单,按客户号分组但是订单总额不知道分配到那张订单去,所以,除了集聚函数运算,其他表达式必须包含在GROUP BY 子句中。 分析条件BETWEEN . AND、AND、OR等关键字的使用方法。答:BETWEEN . AND是介于两个值之间的时候的查询条件;AND 连接两个条件,同时成立;OR 连接两个条件,一方成立即可。 请总结SQL语句中的
14、单表查询语句的使用方法。查询所有列SELECT * FROM 表名 2.查询指定列SELECT 字段列表 FROM 表名 3. 消除重复元组:SELECT Distinct 字段列表 FROM 表名 4.查询经过计算的列SELECT 【字段及字段表达式】 FROM 【表名】5. 指定查询结果的查询SELECT TOP n PERCENT 【字段列表】 FROM 【表名】6.给属性取别名:SELECT 【字段名】1 as ''A'' FROM 【表名】:SELECT A=字段名1 FROM 【表名】:SELECT 字段名1 A FROM 【表名】7.选择查
15、询SELECT 【字段名列表】 FROM 【表名】 WHERE 【条件表达式】条件表达式运算符:比较运算:=,=,!,!=,逻辑查询:or,AND,not范围查询:between AND,not between AND集合查询:IN,not IN字符匹配查询:ike,not LIKE空值查询:is null,is not null8.排序查询SELECT 【字段名1】,【字段名2】 FROM 【表名】WHERE 【条件表达式】ORDER BY 【字段表达式1】,【字段表达式2】 ASCDESC 9.分组查询SELECT 字段名列表 FROM 表名 WHERE 条件表达式 GROUP BY 字段名 SELECT 字段名列表 FROM 表名 WHERE条件表达式 GROUP BY 字段名 HAVING 筛选表达式10.聚合查询SELECT count(ALL| DISTINCT*|字段名列表) FROM 表名 WHERE 条件表达式 SELECT sum| avg| max| min (ALL| DISTINCT)FROM 表名 WHERE 条件表达式 分析哪几种情况需要使用自表连接。当同表中的某个元组变量与表中的另外的元组变量相关时,可以使用自表连接,从而得到一些特殊的数据或者是说带特定条件的数据。自表连接一般是用来判断并筛选单一表中的一些数据。