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

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

select employeeName 员工姓名,headShip 职务,salary 薪水

from Employee

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

select customerName 客户名称,address 所在地

from customer

where customerName like '%有限%'

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

select employeeNo 编号,sex 性别,birthday 出生日期,address 地址,telephone 联系电话,employeeName 员工姓名,headShip 职务,salary 薪水

from Employee

where employeeName like '张%梅'

（4） 查询住址中含有“上海”或“南昌”的女员工，并显示其姓名、所属部门、职务、

住址、出生日期和性别。如果出生日期为空，则显示“不祥”，否则按格式“yyyy—mm—dd”显示，性别用“男”和“女”显示。

select employeeName 员工姓名,department 所属部门,headShip 职务,address 地址,

case sex

when 'F' then '女'

when 'M' then '男'

end 性别,

isnull(convert(char(10),birthday,120),'不详') 出生日期

from Employee

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

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

select employeeNo 员工编号,employeeName 员工姓名,

case sex

when 'F' then '女'

when 'M' then '男'

end 性别,birthday 出生日期,address 地址,telephone 联系电话,hireDate 雇佣时间,department 所属部门,headShip 职务,salary 薪水

from Employee

where sex like 'F' and headShip like '职员' or sex like 'F' and headShip like '科长'

（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 *

from OrderMaster

order by orderSum desc

（9）计算一共销售了几种商品。

select count(*)种类

from ( select distinct ProductNo

from OrderDetail)a

（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）按客户编号统计每个客户20xx年2月的订单总金额。

select a.customerNo 客户编号,orderDate 订单日期,sum(orderSum) 总金额 from OrderMaster a,Customer b

where a.customerNo=b.customerNo and year(orderDate)='2008' and month(orderDate)='2'

group by a.customerNo,orderDate

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

select productNo 商品编号,sum(quantity) 销售数量

from OrderDetail

group by productNo

having sum(quantity)>=10

order by sum(quantity) desc

（13）统计在业务科工作且在19xx年或19xx年出生的员工人数和平均工资。 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 *

FROM employee

WHERE department='财务科'

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

SELECT studentNo,avg(score) ,max(score)

FROM Score

GROUP BY studentNo

HAVING max(score)>=80 （按studentNo分好了组，having在分组内部过滤）

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

答：如果查询列除集聚函数外的表达式不包含在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 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 条件表达式]

第二篇：实验一 数据库和表的操作

实验一 数据库和表的操作

一、数据库实验

【实验目的】

掌握数据库的创建、数据库的分离、数据库文件备份、附加数据库和数据库删除。

【实验内容】

1.创建数据库

⑴ 使用向导创建数据库：数据库名称为jxsk（教学数据库）；并查看数据库属性。

⑵ 使用企业管理器Enterprise Manager创建数据库：数据库名称为jxsk（教学数据库）；并查看数据库属性；修改数据库参数：把数据库jxsk文件增长参数设置为2MB，文件最大大小参数设置为10MB。

⑶ 使用Transact-SQL指定参数创建数据库；查看数据库属性。要求如下：

① 创建数据库：

数据库名称为：testbase1；

数据文件名：testbase1_dat.mdf，存储在E：\练习数据库；

事务日志文件名：testbase1_log.ldf，存储在E：\练习数据库。

② 创建数据库：

数据库名称为：testbase2

数据文件：

主文件组PRIMARY包括文件：prim_sub1_dat、prim_sub2_dat；

文件组Grouptest1包括文件：group1_sub1_dat、group1_sub2_dat；

文件组Grouptest2包括文件：group2_sub1_dat、group2_sub2_dat；

所有数据文件都存储在E:\练习数据库；大小都是5MB。

事务日志文件名：testbase2_log.ldf，存储在E:\练习数据库；大小10MB。

2.分离数据库

掌握使用企业管理器分离数据库；掌握使用系统存储过程分离数据库。

3.备份数据库

掌握数据库文件备份。

4.附加数据库

掌握使用企业管理器附加数据库；掌握使用系统存储过程附加数据库。

分别使用企业管理器和系统存储过程将数据库testbase1附加到SQL Server服务器中。数据库“testbase1”的数据文件和事务日志文件现存放在如下目录中：

e:\练习数据库\testbase1_data.mdf

e:\练习数据库\testbase1_log.ldf

5.删除数据库

掌握使用企业管理器删除数据库；掌握使用系统存储过程删除数据库。

二、数据库表实验

【实验目的】

掌握数据库表创建的方法、掌握数据库表结构修改的方法、掌握数据库表删除的方法。

【实验内容】

1.创建数据库表

⑴使用企业管理器Enterprise Manager创建数据库表：S,C,SC，设置主键和外键约束； ⑵使用使用Transact-SQL创建数据库表：S,C,SC,设置主键和外键约束。

2.修改数据库表

⑴ 向学生表S中增加一列学籍，NATIVE CHAR(40) NOT NULL。

⑵ 将学籍列修改为：NATIVE CHAR(16) NULL。

⑶ 删除学籍列。

3.删除数据库表

⑴使用企业管理器Enterprise Manager删除数据库SC；

⑵使用使用Transact-SQL删除数据库SC。

4.数据库表记录操作

⑴ 记录录入

① 使用企业管理器Enterprise Manager录入记录；

② 使用使用Transact-SQL录入记录。

S(S3，钱多，男，22，自动化) ??

C(C4，英语，80) ??

SC(S3,C4,90) ??

⑵ 记录修改

① 使用企业管理器Enterprise Manager修改记录；

② 使用使用Transact-SQL修改记录。

Ⅰ将学生钱多的系名改为计算机；

Ⅱ将SC中课程c4的成绩提高10分；

Ⅲ将所有课程全部增加10课时。

⑶ 记录删除

① 使用企业管理器Enterprise Manager删除记录；

② 使用使用Transact-SQL删除记录。

Ⅰ将s4选修的c2成绩记录删除；

Ⅱ将c4课程删除；

Ⅲ将学生钱多删除。

⑷ 记录复制

使用使用Transact-SQL完成表记录的复制。

① 复制生成S表的备份test1;

② 复制生成S表中男生的数据记录备份test2,包含学号、姓名、性别和所在系。

实验一 数据库和表的操作

一、数据库实验

1．创建数据库

⑶ 使用Transact-SQL指定参数创建数据库。

① 在查询窗口中输入下列T-SQL 语句：

CREATE DATABASE testbase1

ON

( NAME=testbase1_data,

FILENAME='e:\练习数据库\testbase1_data.mdf')

LOG ON

( NAME=testbase1_log,

FILENAME='e:\ 练习数据库\testbase1_log.ldf')

GO

② 在查询窗口中输入下列T-SQL 语句：

USE master

GO

CREATE DATABASE testbase2

ON

PRIMARY

(NAME=prim_sub_dat1,

FILENAME=‘E：练习数据库\prim_sub1_dat.mdf’, SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=20% ),

(NAME=prim_sub_dat2,

FILENAME=‘E：\练习数据库\prim_sub2_dat.ndf’, SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=20% ),

FILEGROUP Grouptest1

(NAME=group1_sub1,

FILENAME=‘E：\练习数据库\group1_sub1_dat.ndf’, SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=5MB ),

(NAME=group1_sub2,

FILENAME=‘E：\练习数据库\group1_sub2_dat.ndf’, SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=5MB ),

FILEGROUP Grouptest2

(NAME=group2_sub1,

FILENAME=‘E：\练习数据库\group2_sub1_dat.ndf’,

SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=15% ),

(NAME=group2_sub2,

FILENAME=‘E：\练习数据库\group2_sub2_dat.ndf’, SIZE=5MB,

MAXSIZE=50MB,

FILEGROWTH=15% )

LOG ON

(NAME=testbase2_log,

FILENAME=‘E：\练习数据库\testbase2_log_file.ldf’, SIZE=5MB,

MAXSIZE=25MB,

FILEGROWTH=5MB )

GO2. 分离数据库

exec sp_detach_db testbase1,true

3. 备份数据库

分离数据库后，可按一般备份过程实施备份，其文件目录如下： e:\ 练习数据库\testbase1_data.mdf

e:\ 练习数据库\testbase1_log.ldf

4. 附加数据库

EXEC sp_attach_single_file_db @dbname=’testbase1’， @physname=’e：\练习数据库\testbase1_data.mdf ’

5. 删除数据库

DROP DATABASE testbase1