MYSQL数据库练习资料

附录A 学生成绩管理系统数据库

学生成绩管理系统数据库是比较简单的一个数据库。基于读者都对学校的学生管理方式十分了解，所以这个数据库作为上课时的实际演示例子。

A.1 学生表(Tbl_StudentInfo)

伦理名物理名属性主键外键

学号Stuno Char(5) 非空是

姓名stuname Varchar(20) 非空

生日Stubirth Date

性别Stusex char(1)

家庭住址StuAddr Varchar(200)

手机StuTel Varchar(11)

图A.1 学生表的表结构

学号姓名生日性别家庭住址手机05001 张三1988-12-12 0 江苏南京12345 05002 李四1987-06-05 1 上海12346 05003 王五1987-12-01 0 北京12347 05004 赵六1986-02-23 1 广东深圳12348 05005 张三1988-04-01 0 重庆12349 05006 孙七1988-07-03 1 湖北武汉

图A.2 学生表的表数据

A.2 课程表(Tbl_ClassInfo)

伦理名物理名属性主键外键

课程号classno Char(3) not null 是

课程名Classname Varchar(10) not null

图A.3 课程表的表结构

课程号课程名

001 计算机

002 日语

003 英语

图A.4 课程表的表数据

A.3 成绩表(Tbl_ScoreInfo)

伦理名物理名属性主键外键

学号Stuno Char(5) not null 是参照学生表课程号Classno Varchar(3) not null 是参照课程表考试成绩score Numeric(3,1)

图A.5 成绩表的表结构

学号课程号成绩

05001 001 95

05001 002 90

05001 003 88

05002 001 91

05002 002 93

05002 003 88

05003 001 95

05003 002 73

05003 003 58

05004 001 47

05004 003 61

05005 002 59

05005 003 47

图A.6 成绩表的表数据

附录B 网上书店管理系统数据库

网上书店管理系统数据库是比较复杂的一个数据库。基于网上销售图书的方式，作为课后习题。

B.1 客户表(Tbl_Customer)

伦理名物理名属性主键外键顾客编号Customer Int(4) 非空是

顾客姓名Customer_Name Varchar(10)

顾客地址Address Varchar(20)

所在城市City Varchar(20)

所在省State Varchar(20)

邮政编码Zip Varchar(6)

推荐会员Referred Int(4)

图B.1客户表的表结构

顾客编号顾客姓名顾客地址所在城市所在省邮政编码推荐会员1001 张三白下区南京江苏210000

1002 李四徐汇区上海上海110000

1003 王五朝阳区北京北京110000

1004 赵六北大街无锡江苏110000

1005 李三平江路苏州江苏110000

1006 陈三升州路南京江苏110000

1007 朱三三牌楼合肥安徽110000 1003 1008 梁三徐汇区上海上海110000

1009 宫三徐汇区上海上海110000 1003 1010 马三海淀区北京北京110000

1011 刘三崇文区北京北京110000

1012 齐三宣武区北京北京110000

1013 陆三鼓楼区南京江苏110000 1006 1014 陈三雨花区南京江苏110000

1015 钱三新桥村无锡江苏110000

1016 高三利农村无锡江苏110000 1010 1017 郝三杨浦区上海上海110000

1018 黄三三家庄合肥安徽110000

1019 黄四常青镇合肥安徽110000 1003 1020 梁四余老庄合肥安徽110000

图B.2 客户表的表数据

B.2 图书表(Tbl_Books)

伦理名物理名属性主键外键图书编号Isbn Varchar (10) 非空是

书名Title Varchar(300)

出版日期Pubdate Date

出版社ID Pubid Int(2)

成本Cost Numeric(5,2)

售价Retail Numeric (5,2)

种别Category Varchar (12)

图B.3图书表的表结构

图书编号书名出版日期出版社ID成本售价种别

2001-01-21 4 18.75 30.95 健康1059831198 一天中10分

钟的身体成分

0401140733 米奇的复仇2001-12-12 1 14.2 22 家庭生活4981341710 用牙签造车2002-08-18 2 37.8 59 儿童8843172113 数据库的实现1999-06-04 3 31 55 计算机3437212490 用蘑菇烹饪2000-02-28 4 12.5 19.95 烹饪3957136468 数据库法宝2001-11-30 3 47 75 计算机1915762492 电脑手册2001-01-21 3 21 25 计算机9959789321 电子商务的道

2002-03-02 2 37 54 计算机路

2000-07-17 5 48 89.95 家庭生活2491748320 教育孩子_轻

松方法

029******* 烹饪之路2000-09-11 4 19 28 烹饪8117949391 大熊和小宝贝2001-11-08 5 5.32 8.95 儿童

2002-11-11 4 17.85 29.95 自助0132149871 如何最快的制

作比萨

1999-05-09 1 15.4 31.95 商务9247381001 如何成为经理

人

2001-05-01 5 21.85 39.95 文学2147428890 最短小精悍的

诗

图B.4 图书表的表数据

B.3 订单表(Tbl_Orders)

伦理名物理名属性主键外键订单号Order Int (4) 非空是

顾客号Customer Int(4) 参照客户表订单日期Orderdate Date

发货日期Shipdate Date

发货地点Shipstreet Varchar (18)

发货城市Shipcity Varchar (15)

发货省Shipstate Varchar (2)

邮编Shipzip Varchar (6)

图B.5 订单表的表结构

订单号顾客号订单日期发货日期发货地点发货城市发货省邮编

1000 1005 2003-03

-31 2003-04

-02

奥体大街南京江苏210019

1001 1010 2003-03

-31 2003-04

-01

和会街南京江苏210001

1002 1011 2003-03

-31 2003-04

-01

南京路上海上海201100

1003 1001 2003-04

-01 2003-04

-01

南京路上海上海201100

1004 1020 2003-04

-01 2003-04

-05

欧风街无锡江苏214002

1005 1018 2003-04

-01 2003-04

-02

南大街常州江苏213000

1006 1003 2003-04

-01 2003-04

-02

洋人街重庆重庆400015

1007 1007 2003-04

-01 2003-04

-04

洋人街重庆重庆400015

1008 1004 2003-04

-02 2003-04

-03

欧风街无锡江苏214002

1009 1005 2004-04

-03 2004-04

-05

南大街常州江苏213000

1010 1019 2004-04

-03 2004-04

-04

和平街北京北京100000

1011 1010 2003-04

-03 2003-04

-05

长安街北京北京100000

1012 1017 2005-04

-03

北京东路南京江苏210001

1013 1014 2005-04

-03 2005-04

-04

德化街郑州河南450007

1014 1007 2003-04

-03 2003-04

-05

和平街北京北京100000

1015 1020 2005-03

-31

欧风街无锡江苏214002

1016 1003 2003-03

-31

长阳街苏州江苏215500

1017 1015 2005-03

-31 2005-04

-05

太合路合肥安徽230001

1018 1001 2003-03

-31

江苏路南京江苏210001

1019 1018 2003-03

-31

青阳路合肥安徽230001

1020 1008 2003-03

-31

梅山新村合肥安徽230001

图B.6 订单表的表数据

B.4 订单详细表(Tbl_Orderitems)

伦理名物理名属性主键外键订单号Order Int (4) 非空是参照订单表明细编号Item Int (2) 非空是

图书编号Isbn Varchar(10)

数量Quantity Int (3)

图B.7 订单详细表的表结构

订单号明细编号图书编号数量1000 1 3437212490 1

1001 1 9247381001 1

1001 2 2491748320 1

1002 1 8843172113 2

1003 1 8843172113 1

1003 2 1059831198 1

1003 3 3437212490 1

1004 1 2491748320 2

1005 1 2147428890 1

1006 1 9959789321 1

1007 1 3957136468 3

1007 2 9959789321 1

1007 3 8117949391 1

1007 4 8843172113 1

1008 1 3437212490 2

1009 1 3437212490 1

1009 2 0401140733 1

1010 1 8843172113 1

1011 1 2491748320 1

1012 1 8117949391 1

1012 2 1915762492 2

1012 3 2491748320 1

1012 4 0401140733 1

1013 1 8843172113 1

1014 1 0401140733 2

1015 1 3437212490 1

1016 1 2491748320 1

1017 1 8117949391 1

1018 1 3437212490 2

1018 2 8843172113 1

1019 1 0401140733 1

1020 1 3437212490 1

图B.8 订单详细表的表数据

B.5 作者表(Tbl_Author)

伦理名物理名属性主键外键

作者编号AuthorId Varchar (4) 非空是

姓名Name Varchar (20)

图B.9 作者表的表结构

作者编号姓名

S100 薛明

J100 陈洪

A100 张二

M100 刘虎

K100 陈康

P100 王立

A105 吴明

B100 洪海

P105 李强

W100 宫立

W105 龚佩

R100 张青

F100 钟汉

W110 韩青

图B.10 作者表的表数据

B.6 图书作者表(Tbl_BookAuthor)

伦理名物理名属性主键外键图书编号Isbn Varchar (10) 非空是

作者编号AuthorId Varchar (4) 非空是

图B.11 图书作者表的表结构

图书编号作者编号

1059831198 S100

1059831198 P100

0401140733 J100

4981341710 K100

8843172113 P105

8843172113 A100

8843172113 A105

3437212490 B100

3957136468 A100

1915762492 W100

1915762492 W105

9959789321 J100

2491748320 R100

2491748320 F100

2491748320 B100

029******* S100

8117949391 R100

0132149871 S100

9247381001 W100

2147428890 W105

图B.12 图书作者表的表数据

B.7 出版社表(Tbl_Publisher)

伦理名物理名属性主键外键出版社编号Pubid Int (10) 非空是

出版社名Name Varchar (23)

联系人Contact Varchar (15)

联系电话Phone Varchar (12)

图B.13 出版社表的表结构

出版社编号出版社名联系人联系电话

1 新华出版社陈明000-714-8321

2 扬子出版社张译010-410-0010

3 人民教育出版社元力800-555-1211

4 北京大学出版社郑爽800-555-9743

5 机械工业出版社陆华800-555-8284

图B.14 出版社表的表数据

B.8 促销表(Tbl_Promotion)

伦理名物理名属性主键外键礼物Gift Varchar (10)

最低售价Minretail Numeric (5,2)

最高售价Maxretail Numeric(5,2)

图B.15促销表的表结构

礼物最低售价最高售价

书签0 12

书题标签12.01 25

图书封面25.01 56

免费购物券56.01 999.99

图B.16促销表的表数据

第一章数据库概念

1. 理解附录B网上书店订单系统的数据库，并回答如下问题。

1.1 可以访问哪个表和字段在当月订单中确定客户已经购买了哪些书？

1.2通过哪些表可以确定哪些订单没有发货。

1.3通过哪些表及字段确定每月订单产生的总利润。

1.4订单表和明细表的订单编号是什么样的关系。

2. 参照教材附录C有关数据库的安装过程，把数据库安装到本机，从中学习安装数据库。

（考察：数据库安装预估：50分钟）

第二章表格及数据约束

1. 熟悉附录B中各个表及各字段含义，并且能说明此字段属性的设置原因。

（考察：表格和字段的理解预估：30分钟）

2. 创建附录B所示的客户表，图书表，订单表，订单详细表，作者表，图书作者表，出版社表及促销表。

3. 为客户表中增加客户电话字段(字段名：TEL_NO,属性：Varchar(15))。

4. 为客户表中将客户地址修改为可以存储50个中文汉字的属性。

5. 为客户表，图书表，订单表，订单详细表，作者表，图书作者表创建主键约束。

6. 为订单表的客户编号项目创建外键约束，为订单详细表的订单编号和图书编号创建外键约束；以及为教材附录A表的考试成绩表的课程号创建外键约束。

7. 为促销表的最低售价和最高售价指定检查约束，最高售价必须大于最低售价。

为订单详细表的数量指定检查约束，数量>0。

为图书表的成本和售价指定检查约束，售价必须大于成本。

（考察：检查约束预估：40分钟）

8. 为客户表的客户电话项目设置唯一约束。

第三章数据操作及事务处理

1. 参照教材附录B表插入数据。（保留住插入的sql文）

2. 参照教材附录B，修改朱三的客户信息，地址变为‘徐汇区’，城市变为‘上海’，省变为‘上海’，邮编变为‘110000’，推荐会员变为陈三。

3. 参照附录B，删除订单明细表中书名为《用蘑菇烹饪》的信息。

4. 修改订单1016 的发货地点为‘江苏路‘，城市‘南京’，省‘江苏’，邮编‘210001’。

5. 修改订单1006的图书为‘电子商务的道路‘，数量‘5’。

6. 修改书签礼物的最高售价为18，书题标签的最低售价改为18.01。

7. 修改人民教育出版社的联系电话为800-555-1398。

（考察：更新数据预估：5分钟）

第四章基本SQL

参看教材附录A表，完成以下习题：

1.查询所有没电话的学生信息。

2. 查询所有成绩优秀(大于90)和成绩不及格(低于60)的学生学号,和课程号。

3. 查询所有学生信息,按照生日从大到小排序。

4. 查询所有姓孙的学生信息。

参看教材附录B表，完成以下习题：

1. 查询儿童和烹饪种类的所有图书（两种方式查询）。

（考察：IN和OR查询的使用预估：10分钟）

2. 查询所有图书信息,按照出版社ID从小到大，出版日期从大到小排序。

（考察：排序的复杂使用预估：10分钟）

3. 查询由其他客户向其推荐书店的所有客户。

（考察：NULL的使用预估：10分钟）

4. 查询售价在20到50元之间的图书信息（用2种方式实现）。

（考察：BETWEEN和运算符的使用预估：15分钟）

5. 查询不属于计算机的图书信息（用2种方式实现）。

（考察：NOT IN和运算符的使用预估：10分钟）

6. 查询居住在上海和南京的顾客信息，并按照姓名升序排列（用2种方式实现）。

（考察：IN和OR以及排序的使用预估：15分钟）

7. 查询发货城市在北京和常州的订单信息，并按照发货日期升序排列（用2种方式实现）。

（考察：IN和OR以及排序的使用预估：15分钟）

8. 查询发货地点以“南”开头的的订单信息，并按照发货日期升序排列。

（考察：模糊查询以及排序的使用预估：15分钟）

9. 查询书名中含有‘_‘的图书信息，并按照出版日期升序排列。

（考察：模糊查询以及排序的使用预估：15分钟）

第五章内置函数

1. 查询所有课程的英文名称（分别用CASE和DECODE方法）。

（考察：CASE和DECODE方法预估：15分钟）

2. 查询所有学生的成绩（用等级表示：低于60表示不及格；60~70表示及格；70~80表示中等；80~100表示优秀）（用CASE方法）。

（考察：CASE方法预估：15分钟）

参照教材附录B表完成下面习题：

3. 根据所在城市查询顾客所在的省（分别用CASE和DECODE方法）。

（考察：CASE和DECODE方法预估：15分钟）

4. 创建所有客户的列表，如果一个客户不是由其他客户介绍来的，则显示字符‘NO REFERRED’。

（考察：CASE方法预估：10分钟）

5. 显示所有图书的毛利（百分数）的列表，毛利应该显示为一个没有小数位的整数（就是乘以100），后面带有百分号（例如：0.2793=28%）。

（考察：ROUND方法预估：15分钟）

6. 显示所有图书的成本的最小整数。

（考察：CEIL方法预估：5分钟）

7. 显示所有图书的成本和售价均截断小数点后的数值。

（考察：TRUNC方法预估：5分钟）

8. 求出所有图书的成本和售价的余数。

（考察：MOD方法预估：5分钟）

9. 显示所有图书的种别和书名，中间用'-’连接。

（考察：CONCAT方法预估：5分钟）

10. 显示所有顾客的姓名，地址，城市，中间用'/’连接，显示长度到80。

（考察：CONCAT和VARCHAR方法预估：10分钟）

11. 显示出版社名的开头4位以及联系人的姓。

（考察：SUBSTR方法以及字节数预估：10分钟）

12. 显示把顾客所在城市是上海的替换成英文。

（考察：REPLACE方法预估：10分钟）

13. 显示所有顾客的姓名，对于是直辖市的，需显示空白，否则显示城市名称。

（考察：NULLIF方法预估：10分钟）

14. 重复显示发货城市4遍。

（考察：REPEAT方法预估：5分钟）

15. 显示订单订单日期和发货日期的年。

（考察：YEAR方法预估：5分钟）

16. 显示订单日期和发货日期。

（考察：DATE方法预估：5分钟）

17. 显示当前系统时间。

（考察：TIME方法预估：5分钟）

18. 显示当前系统时间的秒。

（考察：SECOND方法预估：5分钟）

19. 显示订单日期和发货日期的月份。

（考察：MONTH方法预估：5分钟）

20. 显示当前系统时间的分。

（考察：MINUTE方法预估：5分钟）

21. 显示当前系统时间的小时。

（考察：HOUR方法预估：5分钟）

22. 显示书名从第2个汉字的书名以及图书编号的后四位。

（考察：SUBSTR方法预估：5分钟）

23. 显示书名中‘子’出现的位置。

（考察：POSSTR方法预估：5分钟）

参照附录B完成练习

1. 创建一个列表，显示每本书的书名及出版社办公室中你再次订购每本书时需要联系的人的姓名和电话。（传统和JOIN方法）。

（考察：一般关联查询预估：30分钟）

2. 确定哪些订单还没发货以及下达这些订单的客户的姓名，将结果按下达订单的日期排序（传统和JOIN方法）。

（考察：一般关联查询预估：30分钟）

3. 列出已经购买了计算机种类的所有人的客户号和姓名以及书名和订单号。

3.1 查询计算机种类的图书编号。

3.2 查询订单详细表中是图书编号是3.1得到编号的订单号。

3.3 查询订单中订单号是3.2得到编号的顾客编号。

3.4 根据3.3的顾客编号得到顾客的姓名。

（传统和JOIN方法）

（考察：一般关联查询预估：40分钟）

4. 确定李三已经购买了哪些书。

4.1 查询李三的顾客编号。

4.2 查询订单表中李三编号对应的订单号。

4.3 查询订单详细表中4.2的订单号对应的图书编号。

4.4 根据图书编号取得书名。

（传统方法）

（考察：一般关联查询预估：30分钟）

5. 确定销售给王五的每一本书的利润。将结果按订单日期排序。如果订购了多本书，那么将结果按利润的降序排列。（传统和JOIN方法）

（考察：一般关联查询预估：30分钟）

6. 哪一本书是由叫洪海的作者编写的？（传统和JOIN方法）

（考察：一般关联查询预估：30分钟）

7. 得到居住在南京并且订购了计算机图书的所有客户的列表。（传统和JOIN方法）

（考察：一般关联查询预估：30分钟）

8. 查询所有订单购买的图书书名。（JOIN方法）

（考察：外关联查询预估：30分钟）

9. 查询所有图书列表，及被哪些顾客购买以及该顾客的信息。（JOIN方法）

（考察：外关联查询预估：30分钟）

10. 查询所有作者编写了哪些图书。（JOIN方法）

（考察：外关联查询预估：30分钟）

11. 查询所有作者编写了哪些图书以及购买该图书的顾客信息。（JOIN方法）

（考察：外关联查询预估：30分钟）

12. 得到所有居住在南京的顾客以及他订购图书的信息。（JOIN方法）

（考察：外关联查询预估：30分钟）

13. 得到订单年在2003和2004年的订单信息以及顾客姓名。（UNION，UNION ALL，传统和与JOIN 方法）

（考察：UNION查询预估：40分钟）

1. 查询单科成绩最高的分数和课程名，按照课程名排序（多表关联,GroupBy子句）（传统和JOIN 方法）。

1.1 查询单科成绩最高的课程编号和成绩。

1.2 用1.1得到的课程号得出课程名。

（考察：多表关联查询预估：40分钟）

2. 查询单科成绩最低分数和课程名,按照课程名排序（多表关联,GroupBy子句）。（传统和JOIN方法）

（考察：多表关联查询预估：10分钟）

3. 查询平均分大于80分的学生的姓名，按照姓名排序。（多表关联 ,having 子句）（传统和JOIN 方法）

（考察：多表关联查询预估：40分钟）

4. 查询科目中,80分以上或者不及格的人的人数。按课程分组。

（考察：多表关联查询预估：20分钟）

5. 英语课的平均分数，最低，最高分数。

（考察：多表关联查询预估：20分钟）

6. 最低分比最高分低40分的课程名。

（考察：多表关联查询预估：20分钟）

7. 查询每门课最高分的人的学号和科目，成绩。

（考察：GroupBy查询预估：10分钟）

8. 日语课不及格的人数。

（考察：COUNT查询预估：10分钟）

9. 查询李四的考试总分数。

（考察：SUM查询预估：10分钟）

10，所有考试有过不及格的学生人数

（考察：COUNT查询预估：10分钟）

11. 所有的学生姓名，及他们的平均分数，总分数。

（考察：AVG，SUM,GROUP BY查询预估：10分钟）

12. 所有的图书书名，及平均成本，平均售价，最低成本，最高成本最低售价，最高售价，总成本，总售价，书的总数。

（考察：AVG，SUM,GROUP BY查询预估：20分钟）

13. 订单中所有的图书书名，及其销售的数量。

（考察：SUM,GROUP BY查询预估：20分钟）

14. 查询每个图书种类数量。

（考察：COUNT,GROUP BY查询预估：10分钟）

15. 查询每个出版社出版的图书数量。

（考察：COUNT,GROUP BY查询预估：10分钟）

1. 查询没参加过考试的学生的姓名,性别。（两种方式）

（考察：IN，EXISTS条件子查询预估：30分钟）

参看教程附录B表，完成下列习题：

2. 确定哪些书的零售价低于销售所有图书的平均零售价。

（考察：条件子查询预估：10分钟）

3. 确定哪些书的成本低于同一类中其他图书的平均成本。

（考察：虚拟表子查询预估：30分钟）

4. 确定哪些订单将发到与订单1014相同的城市。

（考察：IN，EXISTS条件子查询，虚拟表子查询预估：40分钟）

5. 确定哪些订单的总应付款项比订单1003更高。

（考察：虚拟表子查询预估：50分钟）

6. 列出发货延迟时间最长的客户所在城市。

（考察：虚拟表子查询预估：50分钟）

7. 确定哪些客户订购了销售最便宜的图书。

（考察：HAVING ,IN，EXISTS条件子查询预估：40分钟）

8. 确定多少个不同的客户订购了张二编著的图书。

（考察：虚拟表子查询, EXISTS条件子查询预估：30分钟）

9. 确定谁订购了陈洪编写的成本最高的图书。

（考察：HAVING ,IN，EXISTS条件子查询预估：30分钟）

10. 确定2003年3月到4月订购图书的顾客信息。

（考察：IN，EXISTS条件子查询预估：30分钟）

11. 确定销售量最高的图书信息。

（考察：虚拟表子查询预估：30分钟）

12. 确定订单中发往南京的顾客信息。

（考察：IN，EXISTS条件子查询，虚拟表子查询预估：30分钟）

13. 确定出版计算机类图书的出版社信息。

（考察：IN，EXISTS条件子查询，虚拟表子查询预估：30分钟）

14. 确定出售价在20至50元之间图书的出版社信息。

（考察：IN，EXISTS条件子查询，虚拟表子查询预估：30分钟）

15. 确定还没有发货出去的其顾客信息。

（考察：IN，EXISTS条件子查询，虚拟表子查询预估：30分钟）

第九章其他数据对象

1. 参照书上附录A的表，创建一个视图,反映每份订单的详细信息,包括书名,客户名。

（考察：视图预估：30分钟）(命名为ORDER_VI)

2.创建序列，从9开始的整数，各个值应该比生成的前一个值小

3.

允许的最小的可能值为-1，并且不应该允许它进行循环，

这个序列命名为MY_FIRST_SEQUENCE。

（考察：序列预估：30分钟）

3. 执行一个SELECT语句显示MY_FIRST_SEQUENCE的下一个值。

（考察：序列预估：5分钟）

4. 执行一个SELECT语句显示MY_FIRST_SEQUENCE的前一个值。

（考察：序列预估：5分钟）

5. 在顾客表上创建一个索引，以加速根据城市搜索客户的查询。确认索引已经存在，然后删除这个索引。

（考察：索引预估：30分钟）

6. 删除序列MY_FIRST_SEQUENCE。

（考察：序列预估：5分钟）