SQLServer 实验一 单表查询

在订单数据库中，完成如下的查询：

（1）查询员工的姓名、职务和薪水。

select employeeName,headShip,salary

from Employee

（2）查询名字中含有“有限”的客户名称和所在地。

select CustomerName,address

from Customer

where CustomerName like '%有限%'

（3）查询出姓“张”并且姓名的最后一个字为“梅”的员工。

select *

from employee

where employeeName like '张%梅'

（4）查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职称、住址、出生日期和性别，其中如果出生日期为空，显示“不详”，否则按格式“yyyy-mm-dd”显示，性别用“男”和“女”显示。

select employeeName,department,headship,address,

isnull(convert(char(10),birthday,120),'不详') birthday,

sex= Case sex

                      when 'M'then '男'

                      when 'F'then '女'

                   end

from   employee

where  address like'%上海%' or address like '%南昌%' and sex='F'

（5）查询出职务为“职员”或职务为“科长”的女员工的信息。

select *

from employee

where (headship='职员' or headship='科长') and sex='F'

（6）选取编号不在C20050001～C20050004之间的客户编号、客户名称、客户地址。

select CustomerNo,CustomerName,address

from Customer

where CustomerNo not between 'C20050001' and 'C20050004'

(7) 在表OrderMaster中挑出销售金额大于等于5000元的订单。

先统计订单主表中的订单金额，使用命令：

update OrderMaster set orderSum=sum2

from OrderMaster a,(select orderNo,sum(quantity*price) sum2

                       from OrderDetail

                       group by orderNo) b

where a.orderNo=b.orderNo

再使用命令：

select *

from OrderMaster

where orderSum>=5000

(8) 在订单主表中选取订单金额最高的前10%的订单数据。

select top 10 percent orderNo

from OrderMaster

order by orderSum

(9) 计算出一共销售了几种商品。

select count(distinct productNo)

from OrderDetail

(10) 计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额，并且依据销售金额由大到小排序输出。

select productNo,sum(quantity) 销售数量,

         sum(quantity*price)/sum(quantity) 平均销售单价,

         sum(quantity*price) 总销售金额

from OrderDetail

group by productNo

order by sum(quantity*price) desc

(11) 按客户编号统计每个客户2008年2月的订单总金额。

select customerNo,sum(orderSum) 订单总金额     

from OrderMaster

where year(orderDate)=2008 and month(orderDate)=2

group by customerNo

(12) 统计至少销售了10件以上的商品编号和销售数量。

select productNo,sum(quantity)

from OrderDetail

group by productNo

having sum(quantity)>10

(13) 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。

select count(*) 人数,avg(salary) 平均工资

from Employee

where department='业务科' and

           (year(birthday)=1973 or year(birthday)=1967)

(14) 实验问题：

① 给出SQL语句实现分组聚集操作的执行过程。

 答：1，首先执行FROM语句，查找到各表数据

2，其次执行WHERE语句，对数据进行筛选以及表与表的连接

3，然后执行GROUP BY语句，将数据划分组

4，使用聚集函数进行计算

5，使用HAVING语句进行筛选分组

② WHERE和HAVING子句都是用于指定查询条件的，请给出你对这两个子句的理解，用实例说明。

答：WHERE子句：作用于整个查询对象，对元组进行过滤，不可以和函数一起用。

HAVING子句：仅作用于分组，对分组进行过滤。必须与GROUP BY配合使用。

举例：

WHERE：(1) 查询所有业务部门的员工姓名、职称、薪水。

select employeeName,headShip,salary

from employee

where department='业务科'

HAVING：查询最高分在80以上的每个同学的平均分和最高分

select studentNo,avg(score) 平均分,max(score) 最高分

from score

group by studentNo

having max(score)>=80

③ 在分组聚集操作中，为什么在查询列中，除了集聚函数运算，其它表达式必须包含在GROUP BY子句中。

答：如果查询列除集聚函数外的表达式不包含在GROUP BY子句中，则聚合没有意义，如在订单数据库中，查询每个客户的客户编号、订单编号和订单总额

select customerNo,orderNo,sum(orderSum)

from OrderMaster

group by customerNo

该语句其语义不明确，因为一个客户有多张订单，按客户分组求和，意味着求该客户的总订单金额，而在查询列中还包含了订单编号，这个总金额应该放在那张订单中？系统无法确定。

④ 分析条件BETWEEN ... AND、AND、OR等关键字的使用方法。

答：BETWEEN ... AND是范围查询，可用于查询属性值在某一个范围内的元组，NOT BETWEEN ….AND 可用于查询属性值不在某个范围内的元组，BETWEEN后是属性的下限值，AND后是属性的上限值。

AND、OR是逻辑查询，AND实现逻辑与运算，要注意的是在逻辑运算中，不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。

用BETWEEN…..AND能实现的查询，也可以用AND来达到。同样，用NOT BETWEEN…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 null

8.排序查询

SELECT 【字段名1】，【字段名2】   FROM 【表名】 

WHERE  【条件表达式】 

ORDER  BY  【字段表达式1】，【字段表达式2】 [ASC][DESC]

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 条件表达式]