MYSQL数据库练习.docx

1、MYSQL数据库练习附录A 学生成绩管理系统数据库 学生成绩管理系统数据库是比较简单的一个数据库。基于读者都对学校的学生管理方式十分了解，所以这个数据库作为上课时的实际演示例子。A.1 学生表(Tbl_StudentInfo)伦理名物理名属性主键外键学号StunoChar(5) 非空是姓名stunameVarchar(20) 非空生日StubirthDate性别Stusexchar(1)家庭住址StuAddrVarchar(200) 手机StuTelVarchar(11)图 A.1 学生表的表结构学号姓名生日性别家庭住址手机05001张三1988-12-120江苏南京1234505002李四1

2、987-06-051上海1234605003王五1987-12-010北京1234705004赵六1986-02-231广东深圳1234805005张三1988-04-010重庆1234905006孙七1988-07-031湖北武汉图A.2 学生表的表数据A.2 课程表(Tbl_ClassInfo)伦理名物理名属性主键外键课程号classnoChar(3) not null是课程名ClassnameVarchar(10) not null图 A.3 课程表的表结构课程号课程名001计算机002日语003英语图A.4 课程表的表数据A.3 成绩表(Tbl_ScoreInfo)伦理名物理名属性主键

3、外键学号StunoChar(5) not null是参照学生表课程号ClassnoVarchar(3) not null是参照课程表考试成绩scoreNumeric(3,1)图 A.5 成绩表的表结构学号课程号成绩0500100195050010029005001003880500200191050020029305002003880500300195050030027305003003580500400147050040036105005002590500500347图A.6 成绩表的表数据附录B 网上书店管理系统数据库网上书店管理系统数据库是比较复杂的一个数据库。基于网上销售图书的方式，作

4、为课后习题。B.1 客户表(Tbl_Customer)伦理名物理名属性主键外键顾客编号CustomerInt(4) 非空是顾客姓名Customer_NameVarchar(10)顾客地址AddressVarchar(20)所在城市CityVarchar(20)所在省StateVarchar(20)邮政编码ZipVarchar(6)推荐会员ReferredInt(4)图 B.1客户表的表结构顾客编号顾客姓名顾客地址所在城市所在省邮政编码推荐会员1001张三白下区南京江苏2100001002李四徐汇区上海上海1100001003王五朝阳区北京北京1100001004赵六北大街无锡江苏1100001

5、005李三平江路苏州江苏1100001006陈三升州路南京江苏1100001007朱三三牌楼合肥安徽11000010031008梁三徐汇区上海上海1100001009宫三徐汇区上海上海11000010031010马三海淀区北京北京1100001011刘三崇文区北京北京1100001012齐三宣武区北京北京1100001013陆三鼓楼区南京江苏11000010061014陈三雨花区南京江苏1100001015钱三新桥村无锡江苏1100001016高三利农村无锡江苏11000010101017郝三杨浦区上海上海1100001018黄三三家庄合肥安徽1100001019黄四常青镇合肥安徽110000

6、10031020梁四余老庄合肥安徽110000图B.2 客户表的表数据B.2 图书表(Tbl_Books)伦理名物理名属性主键外键图书编号IsbnVarchar (10) 非空是书名TitleVarchar(300)出版日期PubdateDate出版社IDPubidInt(2)成本CostNumeric(5,2)售价RetailNumeric (5,2)种别CategoryVarchar (12)图 B.3图书表的表结构图书编号书名出版日期出版社ID成本售价种别1059831198一天中10分钟的身体成分2001-01-21418.7530.95健康0401140733米奇的复仇2001-12

7、-12114.222家庭生活4981341710用牙签造车2002-08-18237.859儿童8843172113数据库的实现1999-06-0433155计算机3437212490用蘑菇烹饪2000-02-28412.519.95烹饪3957136468数据库法宝2001-11-3034775计算机1915762492电脑手册2001-01-2132125计算机9959789321电子商务的道路2002-03-0223754计算机2491748320教育孩子_轻松方法2000-07-1754889.95家庭生活029*烹饪之路2000-09-1141928烹饪8117949391大熊和小宝

8、贝2001-11-0855.328.95儿童0132149871如何最快的制作比萨2002-11-11417.8529.95自助9247381001如何成为经理人1999-05-09115.431.95商务2147428890最短小精悍的诗2001-05-01521.8539.95文学图B.4 图书表的表数据B.3 订单表(Tbl_Orders)伦理名物理名属性主键外键订单号OrderInt (4) 非空是顾客号CustomerInt(4)参照客户表订单日期OrderdateDate发货日期ShipdateDate发货地点ShipstreetVarchar (18)发货城市ShipcityVa

9、rchar (15)发货省ShipstateVarchar (2)邮编ShipzipVarchar (6)图B.5 订单表的表结构订单号顾客号订单日期发货日期发货地点发货城市发货省邮编100010052003-03-312003-04-02奥体大街南京江苏210019100110102003-03-312003-04-01和会街南京江苏210001100210112003-03-312003-04-01南京路上海上海201100100310012003-04-012003-04-01南京路上海上海201100100410202003-04-012003-04-05欧风街无锡江苏21400210

10、0510182003-04-012003-04-02南大街常州江苏213000100610032003-04-012003-04-02洋人街重庆重庆400015100710072003-04-012003-04-04洋人街重庆重庆400015100810042003-04-022003-04-03欧风街无锡江苏214002100910052004-04-032004-04-05南大街常州江苏213000101010192004-04-032004-04-04和平街北京北京100000101110102003-04-032003-04-05长安街北京北京100000101210172005-04

11、-03北京东路南京江苏210001101310142005-04-032005-04-04德化街郑州河南450007101410072003-04-032003-04-05和平街北京北京100000101510202005-03-31欧风街无锡江苏214002101610032003-03-31长阳街苏州江苏215500101710152005-03-312005-04-05太合路合肥安徽230001101810012003-03-31江苏路南京江苏210001101910182003-03-31青阳路合肥安徽230001102010082003-03-31梅山新村合肥安徽230001图B.6

12、 订单表的表数据B.4 订单详细表(Tbl_Orderitems)伦理名物理名属性主键外键订单号OrderInt (4) 非空是参照订单表明细编号ItemInt (2) 非空是图书编号IsbnVarchar(10)数量QuantityInt (3)图B.7 订单详细表的表结构订单号明细编号图书编号数量10001343721249011001192473810011100122491748320110021884317211321003188431721131100321059831198110033343721249011004124917483202100512147428890110061

13、995978932111007139571364683100729959789321110073811794939111007488431721131100813437212490210091343721249011009204011407331101018843172113110111249174832011012181179493911101221915762492210123249174832011012404011407331101318843172113110141040114073321015134372124901101612491748320110171811794939111

14、018134372124902101828843172113110191040114073311020134372124901图B.8 订单详细表的表数据B.5 作者表(Tbl_Author)伦理名物理名属性主键外键作者编号AuthorIdVarchar (4) 非空是姓名NameVarchar (20)图B.9 作者表的表结构作者编号姓名S100薛明J100陈洪A100张二M100刘虎K100陈康P100王立A105吴明B100洪海P105李强W100宫立W105龚佩R100张青F100钟汉W110韩青图B.10 作者表的表数据B.6 图书作者表(Tbl_BookAuthor)伦理名物理名属

15、性主键外键图书编号IsbnVarchar (10) 非空是作者编号AuthorIdVarchar (4) 非空是图B.11 图书作者表的表结构图书编号作者编号1059831198S1001059831198P1000401140733J1004981341710K1008843172113P1058843172113A1008843172113A1053437212490B1003957136468A1001915762492W1001915762492W1059959789321J1002491748320R1002491748320F1002491748320B100029*S100811

16、7949391R1000132149871S1009247381001W1002147428890W105图B.12 图书作者表的表数据B.7 出版社表(Tbl_Publisher)伦理名物理名属性主键外键出版社编号PubidInt (10) 非空是出版社名NameVarchar (23)联系人ContactVarchar (15)联系电话PhoneVarchar (12)图B.13 出版社表的表结构出版社编号出版社名联系人联系电话1新华出版社陈明000-714-83212扬子出版社张译010-410-00103人民教育出版社元力800-555-12114北京大学出版社郑爽800-555-97

17、435机械工业出版社陆华800-555-8284图B.14 出版社表的表数据B.8 促销表(Tbl_Promotion)伦理名物理名属性主键外键礼物GiftVarchar (10) 最低售价MinretailNumeric (5,2) 最高售价MaxretailNumeric(5,2) 图B.15促销表的表结构礼物最低售价最高售价书签012书题标签12.0125图书封面25.0156免费购物券56.01999.99图B.16促销表的表数据第一章 数据库概念 1. 理解附录B网上书店订单系统的数据库，并回答如下问题。 1.1 可以访问哪个表和字段在当月订单中确定客户已经购买了哪些书？1.2通过哪

18、些表可以确定哪些订单没有发货。1.3通过哪些表及字段确定每月订单产生的总利润。1.4订单表和明细表的订单编号是什么样的关系。2. 参照教材附录C有关数据库的安装过程，把数据库安装到本机，从中学习安装数据库。 （考察：数据库安装 预估：50分钟）第二章 表格及数据约束 1. 熟悉附录B中各个表及各字段含义，并且能说明此字段属性的设置原因。 （考察：表格和字段的理解 预估：30分钟） 2. 创建附录B所示的客户表，图书表，订单表，订单详细表，作者表，图书作者表，出版社表及促销表。3. 为客户表中增加客户电话字段(字段名：TEL_NO,属性：Varchar(15)。 4. 为客户表中将客户地址修改为

19、可以存储50个中文汉字的属性。 5. 为客户表，图书表，订单表，订单详细表，作者表，图书作者表创建主键约束。 6. 为订单表的客户编号项目创建外键约束，为订单详细表的订单编号和图书编号创建外键约束；以及为教材附录A表的考试成绩表的课程号创建外键约束。7. 为促销表的最低售价和最高售价指定检查约束，最高售价必须大于最低售价。 为订单详细表的数量指定检查约束，数量0。 为图书表的成本和售价指定检查约束，售价必须大于成本。 （考察：检查约束 预估：40分钟） 8. 为客户表的客户电话项目设置唯一约束。第三章 数据操作及事务处理 1. 参照教材附录B表插入数据。（保留住插入的sql文） 2. 参照教材

20、附录B，修改朱三的客户信息，地址变为徐汇区，城市变为上海，省变为上海，邮编变为110000，推荐会员变为陈三。3. 参照附录B，删除订单明细表中书名为用蘑菇烹饪的信息。 4. 修改订单1016 的发货地点为江苏路，城市南京，省江苏，邮编210001。 5. 修改订单1006的图书为电子商务的道路，数量5。 6. 修改书签礼物的最高售价为18，书题标签的最低售价改为18.01。 7. 修改人民教育出版社的联系电话为800-555-1398。 （考察：更新数据 预估：5分钟）第四章 基本SQL 参看教材附录A表，完成以下习题： 1.查询所有没电话的学生信息。 2. 查询所有成绩优秀(大于90)和成

21、绩不及格(低于60)的学生学号,和课程号。 3. 查询所有学生信息,按照生日从大到小排序。 4. 查询所有姓孙的学生信息。 参看教材附录B表，完成以下习题： 1. 查询儿童和烹饪种类的所有图书（两种方式查询）。 （考察：IN和OR查询的使用 预估：10分钟） 2. 查询所有图书信息,按照出版社ID从小到大，出版日期从大到小排序。 （考察：排序的复杂使用 预估：10分钟） 3. 查询由其他客户向其推荐书店的所有客户。 （考察：NULL的使用 预估：10分钟） 4. 查询售价在20到50元之间的图书信息（用2种方式实现）。 （考察：BETWEEN和运算符的使用 预估：15分钟） 5. 查询不属于计

22、算机的图书信息（用2种方式实现）。 （考察：NOT IN和运算符的使用 预估：10分钟） 6. 查询居住在上海和南京的顾客信息，并按照姓名升序排列（用2种方式实现）。 （考察：IN和OR以及排序的使用 预估：15分钟） 7. 查询发货城市在北京和常州的订单信息，并按照发货日期升序排列（用2种方式实现）。 （考察：IN和OR以及排序的使用 预估：15分钟） 8. 查询发货地点以“南”开头的的订单信息，并按照发货日期升序排列。 （考察：模糊查询以及排序的使用 预估：15分钟） 9. 查询书名中含有_的图书信息，并按照出版日期升序排列。 （考察：模糊查询以及排序的使用 预估：15分钟）第五章 内置函

23、数 1. 查询所有课程的英文名称（分别用CASE和DECODE方法）。 （考察：CASE和DECODE方法 预估：15分钟） 2. 查询所有学生的成绩（用等级表示：低于60表示不及格；6070表示及格；7080表示中等；80100表示优秀）（用CASE方法）。 （考察：CASE方法 预估：15分钟） 参照教材附录B表完成下面习题： 3. 根据所在城市查询顾客所在的省（分别用CASE和DECODE方法）。 （考察：CASE和DECODE方法 预估：15分钟） 4. 创建所有客户的列表，如果一个客户不是由其他客户介绍来的，则显示字符NO REFERRED。 （考察：CASE方法 预估：10分钟）

24、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. 显示把顾客所在城市是上海的替换成英文。 （考