SQLpubs例子学习

1、实验 SQL Server 2000表的查询SQL Server 2000系统自带的 Pubs 示例数据库为例，以一个虚拟图书出版公司为模型。以下是它的E_R图模型。（1）该系统中数据库基本表如下：Authors：属性名数据类型含义说明可为空检查键/索引au_idId作者编号否是1主键au_lnamevarchar(40)作者姓否au_fnamevarchar(20)作者名否phonechar(12)电话否addressvarchar(40)地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)邮编是是2contractBit是否签约否1 au_id

2、 CHECK 约束定义为： (au_id LIKE '0-90-90-9-0-90-9-0-90-90-90-9')。2 zip CHECK 约束定义为： (zip LIKE '0-90-90-90-90-9')。discounts属性名数据类型含义说明可为空检查键/索引discounttypevarchar(40)折扣类型否stor_idchar(4)商店编号是外键 stores(stor_id)lowqtySmallint数量下限是highqtySmallint数量上限是discountFloat折扣否Employee属性名数据类型含义说明可为空默认值检查键

3、/索引emp_idEmpid职工编号否是1主键fnamevarchar(20)职工名否minitchar(1)是lnamevarchar(30)职工姓否job_idSmallint工作编号否1外键 jobs(job_id)job_lvlTinyint否10pub_idchar(4)出版社编号否'9952'外键publishers(pub_id)Hire_dateDatetime工作日期否GETDATE( )CHECK 约束定义为：(emp_id LIKE 'A-ZA-ZA-Z1-90-90-90-90-9FM') OR (emp_id LIKE 'A-Z

4、-A-Z1-90-90-90-90-9FM')。Jobs属性名数据类型含义说明可为空检查键/索引job_idSmallint工作编号否主键job_descvarchar (50)工作描述否min_lvlTinyint否是1max_lvlTinyint否是2(1) min_lvl CHECK 约束定义为 (min_lvl >= 10)。(2) max_lvl CHECK 约束定义为 (max_lvl <= 250)。pub_info属性名数据类型含义说明可为空检查键/索引pub_idchar(4)出版社编号否主键，外键 publishers(pub_id)logoImage标

5、志图是pr_infoText出版信息是Publishers属性名数据类型含义说明可为空检查键/索引pub_idchar(4)出版社编号否是1主键pub_namevarchar(40)出版社名称是cityvarchar(20)所在城市是statechar(2)所在州是countryvarchar(30)所在国家是1 pub_id CHECK 约束定义为 (pub_id = '1756' OR (pub_id = '1622' OR (pub_id = '0877' OR (pub_id = '0736' OR (pub_id = &

6、#39;1389') OR (pub_id LIKE '990-90-0')。roysched属性名数据类型含义说明可为空检查键/索引title_idTid书编号否外键 titles(title_id)lorangeInt低是hirangeInt高是royaltyInt版权是Sales属性名数据类型含义说明可为空键/索引stor_idchar(4)商店编号否组合主键，聚集索引,外键 stores(stor_id)ord_numvarchar(20)订单编码否组合主键，聚集索引ord_dateDatetime订购日期否qtySmallint数量否paytermsvarch

7、ar(12)付款方式否title_idTid书编号否组合主键，聚集索引,外键 titles(title_id)titles属性名数据类型含义说明可为空检查键/索引title_idTid书编号否主键titlevarchar(80)书名否typechar(12)类型否pub_idchar(4)出版社编号是外键 publishers (pub_id)priceMoney价格是advanceMoney预付款是royaltyInt版税是Ytd_salesInt年销售量是notesvarchar(200)简介是pubdateDatetime出版日期是Stores属性名数据类型含义说明可为空检查键/索引st

8、or_idchar(4)商店编号否主键stor_namevarchar(40)商店名称是stor_addressvarchar(40)商店地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)邮编是titleauthor属性名数据类型含义说明可为空检查键/索引au_idid作者编号否组合主键，聚集索引,外键 authors(au_id)title_idtid书编号否组合主键，聚集索引,外键 titles(title_id)au_ordtinyint是royaltyperint版权百分比是实 验一、目的要求：掌握表的查询，学会使用查询语句。二、内容步骤

9、：（上机练习题）打开每题输入完都先后执行 、。1: SELECT ALL * FROM authors2: SELECT title,price,pubdate FROM titles3：SELECT title_id,title,price*0.8 as NewPrice FROM titles 4 ：USE pubsSELECT DISTINCT type FROM titles5: USE pubsSELECT au_lname,au_fname,phone FROM authors where state='CA' 6：USE pubsSELECT title FROM

10、 titles where price<10 AND type= 'business'7 USE pubsSELECT ALL* FROM titles WHERE price BETWEEN 10 AND 20；8：USE pubsSELECT ALL* FROM titles WHERE price<10 or price>209：USE pubsSELECT ALL* FROM authors WHERE city='Oakland'OR city='Covelo'OR city='Palo Alto'10

11、：USE pubsSELECT ALL* FROM authors WHERE au_fname LIKE('_ _ _ _') ORDER BY au_lname 11 ：USE pubsSELECT ALL* FROM authors WHERE au_lname LIKE('S%') AND au_fname LIKE('%e%') 12 USE pubsSELECT ALL* FROM authors WHERE au_lname LIKE'AK%' 13: USE pubsSELECT MAX(price) as 最高价

12、格,MIN(price)最低价格,SUM(price)as 总价格,AVG(price)as 平均价格 FROM titles 14：USE pubsSELECT COUNT(DISTINCT(type) as 图书种类 FROM titles 15：USE pubsSELECT COUNT(*) AS 加利福亚洲作者人数 FROM authors where state='CA' 16：USE pubsSELECT state AS 州名,COUNT(au_id) AS 该州作者人数 FROM authors GROUP BY state 17：USE pubsSELECT

13、type AS 图书分类, AVG(price) AS 平均价格 FROM titles GROUP BY type 18：USE pubsSELECT title AS 书名,type AS 类型,price AS 单价 FROM titles ORDER BY type COMPUTE AVG(price) BY type 三、练习内容目的1：1. 加深对表间关系的理解。2. 理解数据库中数据的查询方法和应用。3. 学会各种查询的异同及相互之间的转换方法。内容1：1. 查询所有作者的作者号、姓名信息 SELECT au_id,au_lname,au_fname FROM authors;2

14、. 查询所有作者的姓名、作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”，表明显示的信息是身份证信息 SELECT '身份证号：' as 身份证号 ,au_id,au_lname,au_fname FROM authors;3. 查询在CA州的作者姓名和城市 SELECT au_lname,au_fname,city FROM authors WHERE state='CA'4. 查询出版日期在1990.12.31之间的书名和出版日期(查询1991年出版的书) SELECT title,pubdate FROM titles WHERE pubdat

15、e between '1/1/1992' and '12/31/2000'5. 查询每个出版社出版的书 SELECT title,pub_name FROM titles,Publishers WHERE titles.pub_id=Publishers.pub_id order by pub_name;6. 查询某店销售某书的数量 SELECT stor_name,sum(qty) FROM sales,stores WHERE sales.stor_id=stores.stor_id group by stor_name;7. 查询有销售记录的所有书信息，包

16、括书的编号、书名、类型和价格SELECT distinct sales.title_id,title,type,price FROM sales,titles WHERE sales.title_id=titles.title_id ;8. 显示所有的书名（无销售记录的书也包括在内） SELECT title FROM titles ;9. 查询已销售书的信息（书号、书名、作者等） SELECT DISTINCT sales.title_id, titles.title,authors.au_lname FROM sales,titles, titleauthor,authors WHERE

17、sales.title_id=titles.title_id and sales.title_id= titleauthor.title_id and titleauthor.ord=1 and titleauthor.au_id = authors.au_id;10. 查询所有出版商业（business）书籍的出版社的名称SELECT DISTINCT pub_name FROM titles,publishersWHERE titles.pub_id = publishers.pub_id and type='business'目的2：1. 理解数据库中数据的其他查询方法和

18、应用；2. 学会各种查询要求的实现。内容2：在实验1的基础上，练习查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，体会各种查询的执行过程，为简单综合应用打下良好的基础。1. 查询书名以T开头或者出版社号为0877，而且价格大于16的书的信息。SELECT *FROM titles WHERE (title LIKE 'T%' OR pub_id='0087') AND PRICE>16;2. 按照类型的升序和价格的降序（在类型相同时）显示书的信息（书名、作者、出版社、类型、价格） SELECT titles.title,

19、authors.au_lname,publishers.pub_name,titles.type,titles.price FROM titles, titleauthor,authors,publishersWHERE titles.title_id = titleauthor.title_id and titleauthor.au_id=authors.au_id and titles.pub_id =publishers.pub_id ORDER BY titles.type,titles.price DESC3. 查询销售量大于30的书名及销售数量SELECT DISTINCT titles.title,sales.qtyFROM titles, titleauthor,authors,salesWHERE titles.title_id = titleauthor.title_id and titleauthor.au_id=authors.au_id and titles.title_id = sales.title_id and sales.qty>304.