MYSQL数据库练习.docx
《MYSQL数据库练习.docx》由会员分享，可在线阅读，更多相关《MYSQL数据库练习.docx（28页珍藏版）》请在冰豆网上搜索。
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）notnull
是
课程名
Classname
Varchar（10）notnull
图A.3课程表的表结构
课程号
课程名
001
计算机
002
日语
003
英语
图A.4课程表的表数据
A.3成绩表（Tbl_ScoreInfo）
伦理名
物理名
属性
主键
外键
学号
Stuno
Char（5）notnull
是
参照学生表
课程号
Classno
Varchar（3）notnull
是
参照课程表
考试成绩
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
成本
售价
种别
1059831198
一天中10分钟的身体成分
2001-01-21
4
18.75
30.95
健康
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
计算机
2491748320
教育孩子_轻松方法
2000-07-17
5
48
89.95
家庭生活
029*******
烹饪之路
2000-09-11
4
19
28
烹饪
8117949391
大熊和小宝贝
2001-11-08
5
5.32
8.95
儿童
0132149871
如何最快的制作比萨
2002-11-11
4
17.85
29.95
自助
9247381001
如何成为经理人
1999-05-09
1
15.4
31.95
商务
2147428890
最短小精悍的诗
2001-05-01
5
21.85
39.95
文学
图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）
伦理名
物理名
属性
主键
外键
订单号
Order1
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）非空
是
出版社名
Name1
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种方式实现）。
（考察：
NOTIN和运算符的使用预估：
10分钟）
6.查询居住在上海和南京的顾客信息，并按照姓名升序排列（用2种方式实现）。
（考察：
IN和OR以及排序的使用预估：
15分钟）
7.查询发货城市在北京和常州的订单信息，并按照发货日期升序排列（用2种方式实现）。
（考察：
IN和OR以及排序的使用预估：
15分钟）
8.查询发货地点以“南”开头的的订单信息，并按照发货日期升序排列。
（考察：
模糊查询以及排序的使用预估：
15分钟）
9.查询书名中含有‘_‘的图书信息，并按照出版日期升序排列。
（考察：
模糊查询以及排序的使用预估：
15分钟）
第五章内置函数
1.查询所有课程的英文名称（分别用CASE和DECODE方法）。
（考察：
CASE和DECODE方法预估：
15分钟）
selectclassno,caseclassname
when'计算机'then'Computer'
when'日语'then'Japanese'
when'英语'then'English'
endclassname
fromTbl_classinfo;
2.查询所有学生的成绩（用等级表示：
低于60表示不及格；60~70表示及格；70~80表示中等；80~100表示优秀）（用CASE方法）。
（考察：
CASE方法预估：
15分钟）
selectStuno,Classno,case
whenscore<60then'不及格'
whenscorebetween60and69then'及格'
whenscorebetween70and89then'中等'
whenscorebetween80and100then'优秀'
endscore
fromTbl_scoreinfo;
参照教材附录B表完成下面习题：
3.根据所在城市查询顾客所在的省（分别用CASE和DECODE方法）。
（考察：
CASE和DECODE方法预估：
15分钟）
selectState,caseCity
when'上海'then'上海'
when'北京'then'北京'
when'合肥'then'安徽'
else'江苏'
endCity
fromTbl_Customer;
4.创建所有客户的列表，如果一个客户不是由其他客户介绍来的，则显示字符‘NOREFERRED’。
（考察：
CASE方法预估：
10分钟）
升级会员 