北工大-数据库实验(共49页).docx

《北工大-数据库实验(共49页).docx》由会员分享，可在线阅读，更多相关《北工大-数据库实验(共49页).docx（49页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上数据库原理上机报告教师评语：成绩：指导教师签字评阅日期：年月日1.选题背景通过创建销售管理系统，实现对销售商品的销售情况，从中查看商品的销售情况。同时，从中能够查看出商品销售热度以及对商品的管理。也可通过销售管理系统营业员的销售总计来查看营业员的业绩。同时，方便用户查看商品信息，商品销售信息，营业员的销售业绩等等。总体说来，就是为用户实现一个方便的，简单的，可视化的商品销售管理平台。在一个销售管理系统中应该包含以下几种情况：进货时入库登记，以便日后查看和统计进货情况；销售时把相应数量的商品从现存商品库中减去，并记录于另外的一个销售库中，以便日后查看和统计销售信息；还要

2、能够随时查看各种信息表，如营业员信息、商品信息、销售信息。除了以上一些基本的需求外，实际中公司还有用来精心决策和管理分析的数据。如希望查看各个时间段（如今日、本月、本年度等）的进货和销售情况，以便对以后的运作进行一个准确的预测和正确的决断。为了调动员工的积极性，员工的薪水应该和他的销售情况挂钩的，因此还需要能够查看各员工的销售情况，以便对他们的业绩进行考核和评比。本系统正是针对一般销售公司的都有的以上需求而设计的，可以有效管理数据。主要功能有厂商进货，交易管理，包括进货商品登记管理、销售登记管理、营业员信息管理；销售统计管理，其中包括有关进货的各种统计数据表，如进货商品统计，商品信息登记等；员

3、工销售业绩的考核，可以很方便的地查看各员工的销售情况，以便对各员工的业绩进行考核比较；客户订单详情，包括所购买的商品单价数量和总价等，能清晰直观的看出销售情况。2.问题描述2.1实体集销售管理系统的后台数据管理系统中应含有如下实体集：1. 厂家“厂家”实体集含有的属性有：厂家名称，厂家编号，负责人，和商品出厂价。其中厂家编号是它的主键。2. 商品“商品”实体集含有的属性有：商品出售价格，规格，商品名，商品编号，保质期，和备注。其中商品编号是它的主键。3. 销售经理“销售经理”实体集含有的属性有：经理编号，性别，姓名。其中经理编号是它的主键。4. 营业员“营业员”实体集含有的属性有：营业员姓名，

4、编号，性别，基本工资。其中营业员编号是它的主键。5. 客户“客户”实体集含有的属性有：客户姓名，和身份证号。其中身份证号是它的主键。6. 订单“订单”是一个弱实体集，一个确定的客户和订单中的流水单号可以确定一个订单。其中含有的属性有：结算时间，流水单号，和完成订单的营业员编号。其中客户的身份证号和流水单号共同组成它的主键。7. 购买商品“购买商品”实体集包含的属性有：购买商品的总价，单价，数量和商品编号，其中商品编号是它的主键。8. 产地“农夫山泉”是“商品”的子集。除“商品”所含属性外，其还包含“海外”，“国内”这两个额外属性。2.2联系销售管理系统的后台数据管理系统中，含有如下联系：1.

5、厂家和商品间的联系f_p一个厂家可以生产多种商品，但一种商品只能由一个厂家生产。它们的联系是“多对一”的。2. 商品和销售经理的联系p_m一种商品可以被唯一的销售经理分配，一个管理员可以分配多种商品。它们的联系是“多对一”的。3. 销售经理和营业员的联系m_a一个销售经理能管理很多营业员，但一个营业员只能被一个管理员管理。它们的联系是“多对一”的。4. 营业员和客户的联系a_c一个营业员可以负责多位客户，一个客户也可以找多个营业员办理不同的业务。它们的联系是“多对多”的。5. 客户和订单的联系一个客户能有很多订单，但一个订单只属于一个客户。它们的联系是“一对多”的。6. 订单和购买商品的联系b

6、_bp一个订单能包含很多商品，但一个购买商品只能存在一个订单中。它们的联系是“多对一”的。3.数据查询与更新需求3.1数据查询对于销售商品的销售员，可以按照不同得查找方法排序他们的销售业绩。按所有订单中某个特定商品的销售总量降序输出销售员的编号并附带其销售量，若对此商品的总销量相同，则按完成此产品销售的完成度排名。（如张三被分配销售300瓶农夫山泉，成功售出200瓶；李四被分配销售250瓶农夫山泉，也成功售出200瓶，则李四排名在张三之上。）除此种查询方式外，也可按照所有订单的销售总额降序输出销售员编号，若销售总额相同，则按照销售员所拥有的客户数量降序输出销售员的编号。3.2数据更新厂家可以添

7、加或删除所供给的商品，并且决定提供商品的数量和价格。销售经理可以将不同的商品分发给不同的销售员进行销售。这些操作都会带来数据的更新。4.ER图根据前三节所述，用于销售管理系统后台数据的数据库的ER模型如图4.1所示。图4.1 ER图5.关系模式依据4.1中ER模型建立关系模式：厂家（厂家编号，厂家的其他属性）商品（商品编号，商品的其他属性）销售经理（经理编号，销售经理的其他属性）营业员（营业员编号，营业员的其他属性）客户（客户身份证号，客户的其他属性）订单（客户身份证号，订单流水号，其他属性）（此处应设置多值主键）购买商品（商品编号，购买商品的属性）产地（商品编号，商品的其他属性，产地的属性）

8、（此处应设置外键）厂家-商品（厂家编号，商品编号）商品-销售经理（商品编号，经理编号）销售经理-营业员（经理编号，营业员编号）营业员-客户（营业员编号，客户身份证号）订单-购买商品（订单流水号，购买商品编号）6.数据库基本表如图所示，数据库test中共有14个基本表，分别是工厂(factory)、营业员(assistants)、订单(booking)、购买商品(buyproducts)、客户(clients)、销售经理(manager)、产地(place)、商品(products)、营业员-客户(a_c)、订单-购买商品(b_bp)、工厂-商品(f_p)、销售经理-营业员(m_a)、商品-销售

9、经理(p_m)。6.1厂家(factory)6.1.1数据类型6.1.2表中数据6.2商品(products)6.2.1数据类型6.2.2表中数据6.3销售经理(manager)6.3.1数据类型6.3.2表中数据6.4营业员(assistant)6.4.1数据类型6.4.2表中数据6.5客户(clients)6.5.1数据类型6.5.2 表中数据6.6订单(booking)6.6.1数据类型6.6.2表中数据6.7购买商品(buyproducts)6.7.1数据类型6.7.2表中数据6.8产地(place)6.8.1数据类型6.8.2表中数据6.9厂家-商品(f_p)6.9.1数据类型6.9

10、.2表中数据6.10商品-销售经理(p_m)6.10.1数据类型6.10.2表中数据6.11销售经理-营业员(m_a)6.11.1数据类型6.11.2表中数据6.12营业员-客户(a_c)6.12.1数据类型6.12.2表中数据6.13订单-购买商品(b_bp)6.13.1数据类型6.13.2表中数据7.实验过程7.1实验1：数据库的创建与删除如图7.1所示，使用show databases 命令显示当前数据库服务器上所有的数据库；使用CREATE DATABASE database name命令创建数据库，如果已有同名数据库存在，则不能成功创建。使用DROP DATABASE databas

11、e name 命令删除数据库。图7.1 创建数据库的创建与删除7.1.1思考题1. 数据库文件有哪些增长方式？按百分比（按数据库文件的总长度的百分之几增长）或.按固定长度2.日志文件的作用是什么？日志文件（log文件）记录系统（程序）运行的状态。可以通过log文件查询历史操作，在发生“灾难性”问题时可以通过log文件找回信息，发现问题。7.2实验2：基本表的创建与删除7.2.1基本表的创建如图7.2所示，使用CREATE TABLE命令创建基本表，创建过程中可对表中各项数据的特点进行设置。以图中为例，表中factory-id列被设置为主键，并且为其添加了自动增加的属性。使用SHOW TABLE

12、S查看当前数据库中所有基本表，使用DESC table name 命令查看基本表中各项数据的属性。7.2 基本表的创建7.2.2基本表的设置如图7.3所示，使用：ALTER TABLE factory AUTO_INCREMENT = 1000图7.3 基本表的设置命令设置表中factory-id自动从1000开始增长；7.2.3基本表的修改使用：ALTER TABLE factory MODIFY factory-name VARCHAR(80);命令修改表中factory-name列类型为VARCHAR(80)；图7.4 基本表修改7.2.4基本表的删除如图7.4所示，使用DROP TAB

13、LE table name命令删除基本表，删除前后使用SHOW TABLES查看数据库中基本表列表的变化。图7.5 基本表的删除7.2.5思考题1.什么叫做外键？公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。2.外键的作用是什么？保持数据一致性，完整性。7.3实验3：数据的增、删、改7.3.1数据的增加如图7.5所示，使用INSERTINTO命令向基本表中增加数据，命令格式如下：INSERT INTO table_name VALUES (v1, v2, .)INSERT INTO table_name (column1, column2, .) VALUES (

14、v1, v2, .)图7.6使用INSERT INTO命令增加数据7.3.2数据的删除使用：DELETE FROM tablename WHERE columnname = value;命令删除基本表中数据，如图7.7所示。图7.7 数据的删除使用：TRUNCATE tablename命令删除表中所有数据，如图7.8所示。图7.8 删除表中全部数据7.3.3数据的更新使用：UPDATE table nameSET column1 = value1,column2 = value2,WHERE key_value = some_value;命令对表中原始数据进行更新，如图7.9所示。图7.9 数

15、据的更新7.3.4思考题1.MySQL Server提供了哪些类型的完整性约束？五种完整性约束，分别是：（1） NOT NULL：非空约束非空约约束就是限制必须为某个列提供值。空值（NULL）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。（2）UNIQUE：唯一约束唯一约束使用UNIQUE关键字来定义。唯一约束的值必须是唯一的，且不能为空（NULL）。在MySQL中，唯一约束与主键之间存在以下两点区别。1.一个表只能创建一个主键，但可以定义多个唯一约束。2.定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。（3）

16、PRIMARY KEY：主键约束主键可以是表中的某一列，也可以是表中的多个列所构成的一个组合；其中，由多个列组合而成的主键也称为复合主键。在MySQL中，主键列必须遵守以下规则。1.每一个表只能定义一个主键。2.唯一性原则。主键的值，也称键值，必须能够唯一表示表中的每一条记录，且不能为NULL。3.最小化规则。复合主键不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后，如果剩下的列构成的主键仍能满足唯一性原则，那么这个复合主键是不正确的。4.一个列名在复合主键的列表中只能出现一次。（4）FOREIGN KEY：外键约束指定该行记录从属于主表中的一条记录，主要用于参照完整性（5）CH

17、ECK：检查约束检查约束用来指定某列的可取值的范围，它通过限制输入到列中的值来强制域的完整性。2.DELETE语句与DROP TABLE语句有何不同？DELETE 是删除表里部分数据，不会删除表，即使删除全部数据原表仍存在，而 DROP TABLE 操作将表完全删除。7.4实验4：数据的检索7.4.1单表查询查询factory表中所有数据：查询factory表中factory-price列数据:查询factory表中factory-price列数据，并将查询到的结果乘以10转换成refactory-price数据输出:查询factory表中所有不同的factory-manager数据:查询bo

18、oking表中settling-time在2019年12月01日至2019年12月03日之间的数据：查询assistants表中所有assistant为“男”并且assistant-salary大于3000的数据：查询clients表中所有client-name包含“李”的数据，并输出client-id数据：7.4.2聚类分析统计每个manager管理assistant的数量：选择销售经理管理的营业员数量大于2的数据输出：7.4.3排序选择p_m中manager-id为3000，按manager-id进行聚类分析，对结果分别按非递减和非递增的顺序排序:7.4.4多表查询将factory,pro

19、duct和f_p进行连接，查看每个厂家出售商品的价格、规格、名称、数量，同时显示厂家名称，并按厂家id和商品id对结果进行排序:将厂家进行自连接，查询其中在销售价格超过250且由销售商名字中带n的全部记录:将工厂id为1000生产的商品和工厂id为1004生产的商品合并：查询由订单流水为6002中的用户id：在价格大于300的商品中，查询商品数量不是最多的全部记录：价格小于400的商品数量比价格大于3000的商品数量多的记录：使用EXISTS子句查询经理3000管理的所有商品的编号：7.4.5思考题1.什么是空值？为什么空值不用等号判定？数据库中，空值表示值未知。空值不同于空白或零值。没有两个

20、相等的空值。比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。2.聚合函数可以出现在什么子句中？什么情况使用 HAVING？聚合函数对一组值执行计算并返回单一的值，经常与 SELECT 语句的 GROUP BY 子句一同使用。HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。3.连接条件一定是对应属性相等吗？不一定，很多运算都可以，例如判断大小等。4.所有的查询都可以使用多表连接和子查询两种方法吗？当一个查询语句嵌套在另一个查询的查询条件之中时，称为子

21、查询。子查询总是写在圆括号中，可以用在使用表达式的任何地方。如：嵌套在Select 、Insert 、Update 或Delete语句或其他子查询中的查询。连接查询是通过主外键让多个表数据对应成一个表数据，而子查询是查到的数据利用这个数据再查别的。子查询和多表查询一般可以相互转换，但不是所有查询情况都可以使用。7.5实验5：视图的创建与删除创建/修改/删除视图：7.5.1思考题1.视图与基本表有什么不同？1.视图是已经编译好的sql语句,而表不是。2.视图没有实际的物理记录,而表有。3.表是内容,视图是窗口。4.表只用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时四对它进行修

22、改,但视图只能有创建的语句来修改。5.表是内模式,视图是外模式。7.6实验6：索引的创建与删除7.6.1思考题1.索引在数据库中的作用是什么？1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 2、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 4、在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。2.索引有哪几种类型？普通索引、唯一索引、主键索引、聚集索引。8.总结提高通过此次数据库实验，我通过

23、独立设计情景，建立关系和ER模型，能对数据库的相关知识能了解的更为透彻，将理论知识实际运用到实验设计中。在做实验的过程中，我学会了在本地配置mysql，在完成设计的过程中学会使用mysql命令行和mysql workbench进行表的创建、数据的添加和检索等等。SQL语言简单，只是在数据输入上略有麻烦，但当建好一个完整的数据库时，我便可以对其进行任意检索，非常令人有成就感。通过此次上机实验，我对现有的知识有了更全面的认知，构建了较为完整的知识体系，对之前学习过程中的问题也通过实验慢慢掌握。同时掌握了一门新的语言，对这方面的操作也有了一定的锻炼，收益良多。最后非常感谢杜老师的精心指导，指出了我在

24、ER图上的错误，并且耐心给我修改意见，使我的数据库建的更加完善。9. 代码附录show databases;CREATE DATABASE test;DROP DATABASE test;show databases;CREATE DATABASE test;show databases;use test;show tables;DROP TABLE factory;show tables;CREATE TABLE factory(factory-id INT NOT NULL AUTO_INCREMENT,factory-price INT NOT NULL,factory-name VAR

25、CHAR(100) NOT NULL,factory-manager VARCHAR(100) NOT NULL,PRIMARY KEY (factory-id);ALTER TABLE factory MODIFY factory-name VARCHAR(80);show tables;desc factory;CREATE TABLE products(products-id INT NOT NULL AUTO_INCREMENT,products-price INT NOT NULL,products-name VARCHAR(100) NOT NULL,products-standa

26、rd VARCHAR(100) NOT NULL,products-number INT(100) NOT NULL,PRIMARY KEY (products-id);show tables;desc products;CREATE TABLE manager(manager-id INT NOT NULL AUTO_INCREMENT,manager-gender VARCHAR(10) NOT NULL,manager-name VARCHAR(10) NOT NULL,PRIMARY KEY (manager-id);show tables;desc manager;CREATE TA

27、BLE assistants(assistant-id INT NOT NULL AUTO_INCREMENT,assistant-gender VARCHAR(10) NOT NULL,assistant-name VARCHAR(10) NOT NULL,assistant-salary INT(10) NOT NULL,PRIMARY KEY (assistant-id);desc assistants;CREATE TABLE clients(client-id INT NOT NULL AUTO_INCREMENT,client-name VARCHAR(10) NOT NULL,P

28、RIMARY KEY (client-id);desc clients;DROP TABLE booking;show tables;desc booking;CREATE TABLE booking(settling-time DATETIME NOT NULL,order-flow INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (order-flow),client-id INT NOT NULL,KEY idx_fk_client-id (client-id),CONSTRAINT fk_booking_clients FOREIGN KEY (clie

29、nt-id) REFERENCES clients (client-id) on delete restrict ON UPDATE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8;desc booking;CREATE TABLE buyproducts(number INT NOT NULL,buyproduct-id INT NOT NULL AUTO_INCREMENT,single-price INT(10) NOT NULL,total-price INT(100) NOT NULL,PRIMARY KEY (buyproduct-id);sh

30、ow tables;desc buyproducts;DROP TABLE place;show tables;CREATE TABLE place(foerign VARCHAR(100) NOT NULL,local VARCHAR(100) NOT NULL,products-id INT NOT NULL,KEY idx_fk_products-id (products-id),CONSTRAINT fk_place_products FOREIGN KEY (products-id) REFERENCES products (products-id) on delete restri

31、ct ON UPDATE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8;show tables;desc place;CREATE TABLE f_p(products-id INT NOT NULL,factory-id INT NOT NULL,PRIMARY KEY (products-id,factory-id);desc f_p;CREATE TABLE p_m(products-id INT NOT NULL,manager-id INT NOT NULL,PRIMARY KEY (products-id,manager-id);desc p

32、_m;CREATE TABLE m_a(assistant-id INT NOT NULL,manager-id INT NOT NULL,PRIMARY KEY (assistant-id,manager-id);desc m_a;CREATE TABLE a_c(assistant-id INT NOT NULL,client-id INT NOT NULL,PRIMARY KEY (assistant-id,client-id);desc a_c;CREATE TABLE b_bp(order-flow INT NOT NULL,buyproduct-id INT NOT NULL,PR

33、IMARY KEY (order-flow, buyproduct-id);desc b_bp;show tables;desc factory;ALTER TABLE factory AUTO_INCREMENT=1000;INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (2, 农夫山泉股份有限公司, 李明);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (3, 农夫山泉股份有限公司, 李明)

34、;INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (280, 膳魔师家庭制品有限公司, 李红);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (250, 膳魔师家庭制品有限公司, 李红);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (340, 膳魔师家庭制品有限公司, 李红);INSERT I

35、NTO factory (factory-price, factory-name, factory-manager) VALUES (4888, 美国苹果公司, Nancy);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (5020, 美国苹果公司, Nancy);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (2216, 美国苹果公司, Nancy);INSERT INTO factory

36、(factory-price, factory-name, factory-manager) VALUES (340, 美国苹果公司, Nancy);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (5, 西友株式会社, 秋木美音);INSERT INTO factory (factory-price, factory-name, factory-manager) VALUES (32, 西友株式会社, 秋木美音);INSERT INTO factory (factory-price, fact

37、ory-name, factory-manager) VALUES (250, 克里斯汀迪奥, Linda);select * from factory;desc products;ALTER TABLE products AUTO_INCREMENT=2000;INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (3, 农夫山泉, 550ml/瓶,500);INSERT INTO products (products-price, products-nam

38、e, products-standard,products-number) VALUES (5, 东方树叶, 550ml/瓶,600);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (350, 膳魔师水杯, 500ml/瓶,50);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (320, 膳魔师儿童水杯, 50

39、0ml/瓶,30);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (580, 膳魔师煮饭杯, 500ml/瓶,20);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (5499, iPhone11, 64GB,100);INSERT INTO products (products-price, products-

40、name, products-standard,products-number) VALUES (6311, iPad Pro, 64GB,80);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (3468, Apple Watch, 16GB,70);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (700, A

41、pple Pencil, for ipad Pro,70);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (10, 凝胶墨水笔, ,2000);INSERT INTO products (products-price, products-name, products-standard,products-number) VALUES (10, 无印良品洗发露, ,1500);INSERT INTO products (products-price, pr

42、oducts-name, products-standard,products-number) VALUES (330, Dior999, 3.5g,4000);select * from products;ALTER TABLE manager AUTO_INCREMENT=3000;INSERT INTO manager (manager-gender, manager-name) VALUES (女, 王昭君);INSERT INTO manager (manager-gender, manager-name) VALUES (男, 马超);INSERT INTO manager (ma

43、nager-gender, manager-name) VALUES (女, 孙尚香);select * from manager;DELETE FROM manager WHERE manager-id = 3001;select * from manager;ALTER TABLE assistants AUTO_INCREMENT=4000;INSERT INTO assistants (assistant-gender, assistant-name, assistant-salary) VALUES (男, 张飞, 3000);INSERT INTO assistants (assi

44、stant-gender, assistant-name, assistant-salary) VALUES (男, 赵云, 5000);INSERT INTO assistants (assistant-gender, assistant-name, assistant-salary) VALUES (男, 关羽, 4000);INSERT INTO assistants (assistant-gender, assistant-name, assistant-salary) VALUES (女, 西施, 1000);INSERT INTO assistants (assistant-gender, assistant-name, assistant-salary) VALUES (女, 花木兰, 7000);select * from assistants;DELETE FROM assistants WHERE assistant-id =