数据库原理.doc
如有侵权，请联系网站删除，仅供学习与交流数据库原理【精品文档】第 6 页实验三 单表查询1 实验目的与要求(1) 掌握SQL查询语句的基本概念。(2) 掌握SQL Server查询语句的基本语法。(3) 熟练使用SQL的SELECT语句对单表进行查询。(4) 熟练掌握并运用SQL Server所提供的函数。(5) 熟练使用SQL语句进行单表聚合操作。2 实验内容在订单数据库OrderDB中，完成如下的查询：(1) 查询员工的姓名、职务和薪水。(2) 查询名字中含有“有限”的客户名称和所在地。(3) 查询出姓“张”并且姓名的最后一个字为“梅”的员工。(4) 查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职务、住址、出生日期和性别，其中如果出生日期为空，显示“不详”，否则按格式“yyyy-mm-dd”显示，性别用“男”和“女”显示。(5) 查询出职务为“职员”或职务为“科长”的女员工的信息。(6) 选取编号不在C20050001C20050004之间的客户编号、客户名称、客户地址。(7) 在表OrderMaster中挑出销售金额大于等于5000元的订单。先统计订单主表中的订单金额，使用命令：(8) 在订单主表中选取订单金额最高的前10%的订单数据。(9) 计算出一共销售了几种商品。(10) 计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额，并且依据销售金额由大到小排序输出。(11) 按客户编号统计每个客户2008年2月的订单总金额。(12) 统计至少销售了10件以上的商品编号和销售数量。(13) 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。(14) 实验问题： 给出SQL语句实现分组聚集操作的执行过程。 WHERE和HAVING子句都是用于指定查询条件的，请给出你对这两个子句的理解，用实例说明。 在分组聚集操作中，为什么在查询列中，除了集聚函数运算，其它表达式必须包含在GROUP BY子句中。 分析条件BETWEEN . AND、AND、OR等关键字的使用方法。 请总结SQL语句中的单表查询语句的使用方法。(1) 查询员工的姓名、职务和薪水。select employeeName48, headship48,salary48from Employee48(2) 查询名字中含有“有限”的客户名称和所在地。select customerName48,address48from Customer48where customerName48 like '%有限%'(3) 查询出姓“张”并且姓名的最后一个字为“梅”的员工。select employeeName48from Employee48where employeeName48 like '%张_梅%'(4) 查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职务、住址、出生日期和性别，其中如果出生日期为空，显示“不详”，否则按格式“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 '%南昌%'(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元的订单。更新订单脚本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 OrderMaster48order 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 productNo48order by 总销售金额desc(11) 按客户编号统计每个客户2008年2月的订单总金额。select b.customerNo48,customerName48,sum(orderSum48) ordersumfrom customer48 a ,ordermaster48 bwhere a.customerNo48=b.customerNo48and year(orderDate48)='2008'and month(orderDate48)='2'group by b.customerNo48,customerName48(12) 统计至少销售了10件以上的商品编号和销售数量。select productNo48,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) 实验问题： 给出SQL语句实现分组聚集操作的执行过程。答：1：首先执行FROM语句，查找到各表数据2：其次执行WHERE语句，对数据进行筛选以及表与表的连接3：然后执行GROUP BY语句，将数据划分组4：使用聚集函数进行计算5：使用HAVING语句进行筛选分组 WHERE和HAVING子句都是用于指定查询条件的，请给出你对这两个子句的理解，用实例说明。答：WHERE子句：作用于整个查询对象，对元组进行过滤，不可以和函数一起用。HAVING子句：仅作用于分组，对分组进行过滤。必须与GROUP BY配合使用。举例：WHERE：(1) 查询所有业务部门的员工姓名、职称、薪水。SELECT employeeName48,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子句中，则聚合没有意义，如在订单数据库中，查询每个客户的客户编号、订单编号和订单总额SELECT customerNo48,orderNo48,sum(orderSum)FROM OrderMaster48GROUP BY customerNo48只按客户号分组的话，语义变得不明确，因为一个客户可能有多张订单，按客户号分组但是订单总额不知道分配到那张订单去，所以，除了集聚函数运算，其他表达式必须包含在GROUP BY 子句中。 分析条件BETWEEN . AND、AND、OR等关键字的使用方法。答：BETWEEN . AND是介于两个值之间的时候的查询条件；AND 连接两个条件，同时成立；OR 连接两个条件，一方成立即可。 请总结SQL语句中的单表查询语句的使用方法。查询所有列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.选择查询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 条件表达式 分析哪几种情况需要使用自表连接。当同表中的某个元组变量与表中的另外的元组变量相关时，可以使用自表连接，从而得到一些特殊的数据或者是说带特定条件的数据。自表连接一般是用来判断并筛选单一表中的一些数据。
