实验2: 数据表操作-数据查询
返回
实验2: 数据表操作-数据查询
一、实验名称和性质
所属课程 | C#数据库系统设计 |
实验名称 | 数据表操作-数据查询 |
实验学时 | 2 |
实验性质 | □验证 □综合 ✓□设计 |
必做/选做 | □必做 ✓□选做 |
二、实验目的
1. 加深对表间关系的理解。
2. 理解数据库中数据的查询方法和应用。
3. 学会各种查询的异同及相互之间的转换方法。
4. 理解数据库中数据的其他查询方法和应用。
三、实验的软硬件环境要求
硬件环境要求：
PC机（单机）
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）
四、知识准备
前期要求掌握的知识：
（一）SELECT 列名1 [ ,列名2 ]...
[ INTO 新表名 ]
FROM 表名1 [ ,表名2 ]...
[ WHERE 条件表达式 ]
[ GROUP BY 列名列表 ]
[ HAVING 条件表达式 ]
[ ORDER BY 列名列表 [ASC | DESC] ]
SELECT语句至少包含两个子句：SELECT和FROM
PUBs中的数据库表
Authors：
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
au_id | Id | 作者编号 | 否 | 是1 | 主键 |
au_lname | varchar(40) | 作者姓 | 否 |
|
|
au_fname | varchar(20) | 作者名 | 否 |
|
|
phone | char(12) | 电话 | 否 |
|
|
address | varchar(40) | 地址 | 是 |
|
|
city | varchar(20) | 所在城市 | 是 |
|
|
state | char(2) | 所在州 | 是 |
|
|
zip | char(5) | 邮编 | 是 | 是2 |
|
contract | Bit | 是否签约 | 否 |
|
|
============================
discounts
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
discounttype | varchar(40) | 折扣类型 | 否 |
|
|
stor_id | char(4) | 商店编号 | 是 |
| 外键 stores(stor_id) |
lowqty | Smallint | 数量下限 | 是 |
|
|
highqty | Smallint | 数量上限 | 是 |
|
|
discount | Float | 折扣 | 否 |
|
|
============================
Employee
属性名 | 数据类型 | 含义说明 | 可为空 | 默认值 | 检查 | 键/索引 |
emp_id | Empid | 职工编号 | 否 |
| 是1 | 主键 |
fname | varchar(20) | 职工名 | 否 |
|
|
|
minit | char(1) |
| 是 |
|
|
|
lname | varchar(30) | 职工姓 | 否 |
|
|
|
job_id | Smallint | 工作编号 | 否 | 1 |
| 外键 jobs(job_id) |
job_lvl | Tinyint |
| 否 | 10 |
|
|
pub_id | char(4) | 出版社编号 | 否 | '9952' |
| 外键publishers(pub_id) |
Hire_date | Datetime | 工作日期 | 否 | GETDATE( ) |
|
|
============================
Jobs
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
job_id | Smallint | 工作编号 | 否 |
| 主键 |
job_desc | varchar (50) | 工作描述 | 否 |
|
|
min_lvl | Tinyint |
| 否 | 是1 |
|
max_lvl | Tinyint |
| 否 | 是2 |
|
============================
pub_info
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
pub_id | char(4) | 出版社编号 | 否 |
| 主键，外键 publishers(pub_id) |
logo | Image | 标志图 | 是 |
|
|
pr_info | Text | 出版信息 | 是 |
|
|
============================
Publishers
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
pub_id | char(4) | 出版社编号 | 否 | 是1 | 主键 |
pub_name | varchar(40) | 出版社名称 | 是 |
|
|
city | varchar(20) | 所在城市 | 是 |
|
|
state | char(2) | 所在州 | 是 |
|
|
country | varchar(30) | 所在国家 | 是 |
|
|
============================
roysched
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
title_id | Tid | 书编号 | 否 |
| 外键 titles(title_id) |
lorange | Int | 低 | 是 |
|
|
hirange | Int | 高 | 是 |
|
|
royalty | Int | 版权 | 是 |
|
|
============================
Sales
属性名 | 数据类型 | 含义说明 | 可为空 | 键/索引 |
stor_id | char(4) | 商店编号 | 否 | 组合主键，聚集索引,外键 stores(stor_id) |
ord_num | varchar(20) | 订单编码 | 否 | 组合主键，聚集索引 |
ord_date | Datetime | 订购日期 | 否 |
|
qty | Smallint | 数量 | 否 |
|
payterms | varchar(12) | 付款方式 | 否 |
|
title_id | Tid | 书编号 | 否 | 组合主键，聚集索引,外键 titles(title_id) |
============================
titles
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
title_id | Tid | 书编号 | 否 |
| 主键 |
title | varchar(80) | 书名 | 否 |
|
|
type | char(12) | 类型 | 否 |
|
|
pub_id | char(4) | 出版社编号 | 是 |
| 外键 publishers (pub_id) |
price | Money | 价格 | 是 |
|
|
advance | Money | 预付款 | 是 |
|
|
royalty | Int | 版税 | 是 |
|
|
Ytd_sales | Int | 年销售量 | 是 |
|
|
notes | varchar(200) | 简介 | 是 |
|
|
pubdate | Datetime | 出版日期 | 是 |
|
|
============================
Stores
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
stor_id | char(4) | 商店编号 | 否 |
| 主键 |
stor_name | varchar(40) | 商店名称 | 是 |
|
|
stor_address | varchar(40) | 商店地址 | 是 |
|
|
city | varchar(20) | 所在城市 | 是 |
|
|
state | char(2) | 所在州 | 是 |
|
|
zip | char(5) | 邮编 | 是 |
|
|
============================
titleauthor
属性名 | 数据类型 | 含义说明 | 可为空 | 检查 | 键/索引 |
au_id | id | 作者编号 | 否 |
| 组合主键，聚集索引,外键 authors(au_id) |
title_id | tid | 书编号 | 否 |
| 组合主键，聚集索引,外键 titles(title_id) |
au_ord | tinyint |
| 是 |
|
|
royaltyper | int | 版权百分比 | 是 |
|
|
(二) 存储过程
使用T-SQL语句创建存储过程的语法为：
CREATE PROC[EDURE] 存储过程名
[{@参数1 数据类型}[ = 默认值] [OUTPUT],
…… ,
{@参数n 数据类型}[ = 默认值] [OUTPUT]
AS
SQL语句
五、实验内容
(一) Select
1. 查询所有作者的姓名、作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”，表明显示的信息是身份证信息
2. 查询在CA州的作者姓名和城市
3. 查询书名以T开头或者出版社号为0877，而且价格大于16的书的信息。
4. 查询销售量大于30的书名及销售数量
5. 查询订单数大于3的各商店编码及订单数量。
6. 查询在1994.1.1到1994.10.31间，每本书的销售总额
7. 查询所有作者的所在城市和州名，要求没有重复信息
8. 查询每本书的书名、作者及它的售书总量
9. 查询价格最高的书的书名、作者及价格
10. 查询每个店销售的各书的数量
11. 查询有销售记录的所有书信息，包括书的编号、书名、类型和价格
12. 显示所有的书名（无销售记录的书也包括在内）
13. 查询已销售书的信息（书号、书名、作者名、销售的商店名等）
14. 查询所有出版商业（business）书籍的出版社的名称
(二) 存储过程
编写存储过程proc_CourseBySname并调用它。
要求：输入学生姓名，查询该学生的选课信息，同时，返回该学生的选修课程数。