【数据库系统设计】SQL语言实验

一、实验目的和要求

(1) 掌握SQL语言,能熟练运用SQL语言进行数据定义和数据操纵
(2) 加深对关系数据模型的数据结构和约束的理解

二、实验环境(实验设备)

硬件:微机
软件:建议MySQL 5.6

三、实验原理及内容

实验原理基于第二、三章的相关内容。

题目:设有一个 SPJ 数据库,包括 S、P、J 及 SPJ 4 个关系模式:

  • S (SNO,SNAME,STATUS,CITY) ;
  • P (PNO,PNAME,COLOR,WEIGHT) ;
  • J (JNO,JNAME,CITY) ;
  • SPJ (SNO,PNO,JINO,QTY) ;

应商表 S 由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成。

零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某工程项目的数量为QTY。

在这里插入图片描述

实验内容如下:
1.用 SQL 的 DDL 语句创建 SPJ 数据库及所包含的四个基表。数据库命名为 SPJ+学号(SPJ210101),表名命名为S+学号,等;分别写出创建数据库以及4个基表模式的完整语句,要求定义每个模式的主码以及相应的参照完整性约束,其中有供应记录的零件基本信息不允许删除;当更新供应商信息时,相应的供应记录也跟随更新。截图输出,截图包含SQL语句以及对应的结果。

登录 MySQL ,开始做实验,WIN+R运行,输入cmd,调出命令行。

mysql -u root -p
Enter password: 1234

在这里插入图片描述

1、创建 SPJ 数据库及 S、P、J、SPJ 表

创建 SPJ 数据库:

CREATE DATABASE SPJ210224;

在这里插入图片描述
进入数据库,并且查看一下目前的表,刚建完数据库肯定没有表。

USE spj210224; #进入刚创建的数据库
SHOW TABLES; #查看数据库的所有表,现在还没创建

在这里插入图片描述

创建四个基表 S、P、J、SPJ

创建 S 表:

CREATE TABLE s210224 (
	SNO CHAR(20) NOT NULL,
	SNAME CHAR(20),
	STATUS INT(20),
	CITY CHAR(20),
	PRIMARY KEY (SNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 数据库引擎与字符集可不设置

在这里插入图片描述

创建 P 表:

CREATE TABLE p210224 (
	PNO CHAR(20) NOT NULL,
	PNAME CHAR(20),
	COLOR CHAR(20),
	WEIGHT INT(20),
	PRIMARY KEY (PNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 数据库引擎与字符集可不设置

在这里插入图片描述
创建 J 表:

CREATE TABLE j210224 (
	JNO CHAR(20) NOT NULL,
	JNAME CHAR(20),
	CITY CHAR(20),
	PRIMARY KEY (JNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 数据库引擎与字符集可不设置

在这里插入图片描述

创建 SPJ 表:

CREATE TABLE spj210224 (
	SNO CHAR(20) NOT NULL,
	PNO CHAR(20) NOT NULL,
	JNO CHAR(20) NOT NULL,
	QTY INT(20) NOT NULL,
	FOREIGN KEY (SNO) REFERENCES s210224(SNO) 
	ON DELETE CASCADE 
	ON UPDATE CASCADE,
	FOREIGN KEY (PNO) REFERENCES p210224(PNO) 
	ON DELETE NO ACTION
	ON UPDATE NO ACTION,
	FOREIGN KEY (JNO) REFERENCES j210224 (JNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 数据库引擎与字符集可不设置

在这里插入图片描述

2、用SQL语句完成增删改操作以及指定查询

(1)向四个基表中插入数据

只需写出向每个表插入第一条记录的SQL语句
截图输出,截图包含SQL语句以及对应的结果。以下同。

向 S 表中插入数据:

INSERT INTO S210224(SNO,SNAME,STATUS,CITY)
VALUES ('S1', '精益', 20, '天津');

在这里插入图片描述

向 P 表中插入数据:

INSERT INTO P210224(PNO,PNAME,COLOR,WEIGHT)
VALUES ('P1', '螺母', '红', 12);

在这里插入图片描述

向 J 表中插入数据:

INSERT INTO J210224(JNO,JNAME,CITY)
VALUES ('J1', '三建', '北京');

在这里插入图片描述
向 SPJ 表中插入数据:

INSERT INTO SPJ210224(SNO,PNO,JNO,QTY)
VALUES ('S1', 'P1', 'J1', 200);

在这里插入图片描述

!!!衔接!!!

要求是只需要提供插入1条语句的SQL语句和截图。这里为了后面查询的方便,提供完整数据的插入语句。请将所有210224改为自己的学号,然后复制运行即可。

S 表:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `s210224`;
CREATE TABLE `s210224` (
  `SNO` char(20) NOT NULL,
  `SNAME` char(20) DEFAULT NULL,
  `STATUS` int(20) DEFAULT NULL,
  `CITY` char(20) DEFAULT NULL,
  PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `s210224` VALUES ('S1', '精益', '20', '天津');
INSERT INTO `s210224` VALUES ('S2', '盛锡', '10', '北京');
INSERT INTO `s210224` VALUES ('S3', '东方红', '30', '北京');
INSERT INTO `s210224` VALUES ('S4', '丰泰盛', '20', '天津');
INSERT INTO `s210224` VALUES ('S5', '为民', '30', '上海');

P 表:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `p210224`;
CREATE TABLE `p210224` (
  `PNO` char(20) NOT NULL,
  `PNAME` char(20) DEFAULT NULL,
  `COLOR` char(20) DEFAULT NULL,
  `WEIGHT` int(20) DEFAULT NULL,
  PRIMARY KEY (`PNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `p210224` VALUES ('P1', '螺母', '红', '12');
INSERT INTO `p210224` VALUES ('P2', '螺母', '绿', '17');
INSERT INTO `p210224` VALUES ('P3', '螺丝刀', '蓝', '14');
INSERT INTO `p210224` VALUES ('P4', '螺丝刀', '红', '14');
INSERT INTO `p210224` VALUES ('P5', '凸轮', '蓝', '40');
INSERT INTO `p210224` VALUES ('P6', '齿轮', '红', '30');

J 表:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `j210224`;
CREATE TABLE `j210224` (
  `JNO` char(20) NOT NULL,
  `JNAME` char(20) DEFAULT NULL,
  `CITY` char(20) DEFAULT NULL,
  PRIMARY KEY (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `j210224` VALUES ('J1', '三建', '北京');
INSERT INTO `j210224` VALUES ('J2', '一汽', '长春');
INSERT INTO `j210224` VALUES ('J3', '弹簧厂', '天津');
INSERT INTO `j210224` VALUES ('J4', '造船厂', '天津');
INSERT INTO `j210224` VALUES ('J5', '机车厂', '唐山');
INSERT INTO `j210224` VALUES ('J6', '无线电厂', '常州');
INSERT INTO `j210224` VALUES ('J7', '半导体厂', '南京');

SPJ 表:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `spj210224`;
CREATE TABLE `spj210224` (
  `SNO` char(20) NOT NULL,
  `PNO` char(20) NOT NULL,
  `JNO` char(20) NOT NULL,
  `QTY` int(20) NOT NULL,
  KEY `SNO` (`SNO`),
  KEY `PNO` (`PNO`),
  KEY `JNO` (`JNO`),
  CONSTRAINT `spj210224_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s210224` (`SNO`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `spj210224_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p210224` (`PNO`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `spj210224_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j210224` (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J3', '100');
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J4', '700');
INSERT INTO `spj210224` VALUES ('S1', 'P2', 'J2', '100');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J1', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J2', '200');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J4', '500');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J5', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P5', 'J5', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P5', 'J2', '100');
INSERT INTO `spj210224` VALUES ('S3', 'P1', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S3', 'P3', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S4', 'P5', 'J1', '100');
INSERT INTO `spj210224` VALUES ('S4', 'P6', 'J3', '300');
INSERT INTO `spj210224` VALUES ('S4', 'P6', 'J4', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P2', 'J4', '100');
INSERT INTO `spj210224` VALUES ('S5', 'P3', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P6', 'J2', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P6', 'J4', '500');

(2)找出所有供应商的姓名和所在城市

SELECT SNAME,CITY
FROM s210224;

在这里插入图片描述

(3)找出所有零件的名称、颜色、重量

SELECT PNAME,COLOR,WEIGHT
FROM p210224;

在这里插入图片描述

(4)找出使用供应商S1所供应零件的工程号码

SELECT JNO
FROM spj210224
WHERE SNO IN(
	SELECT SNO
	FROM s210224
	WHERE SNO='S1');

在这里插入图片描述

(5)找出工程项目J2使用的各种零件的名称及其数量

SELECT PNAME,QTY
FROM p210224 first,spj210224 second
WHERE first.PNO=second.PNO
AND JNO='J2';

在这里插入图片描述

(6)找出上海厂商供应的所有零件号码

SELECT PNO
FROM spj210224
WHERE SNO IN(
	SELECT SNO
	FROM s210224
	WHERE CITY='上海'
);

在这里插入图片描述

(7)找出使用上海产的零件的工程名称

SELECT JNAME#使用上海产的零件的工程名称
FROM j210224
WHERE JNO IN(
	SELECT JNO#零件产地是上海的工程
	FROM spj210224
	WHERE SNO IN( 
		SELECT SNO #零件产地是上海
		FROM s210224
		WHERE CITY='上海'
	)
);

在这里插入图片描述

(8)找出没有使用天津产的零件的工程号码

SELECT DISTINCT JNO #零件产地不是天津的工程号码
FROM spj210224
WHERE SNO IN(
	SELECT SNO #零件产地不是天津
	FROM s210224
	WHERE CITY != '天津'
);

在这里插入图片描述

(9)把全部红色零件的颜色改成蓝色

UPDATE p210224
SET COLOR='蓝'
WHERE COLOR='红';

在这里插入图片描述

(10)由S5供给J4的零件P6改为由S3供应,请作必要的修改

UPDATE spj210224
SET SNO='S3'
WHERE SNO='S5'AND JNO='J4' AND PNO='P6';

在这里插入图片描述

(11)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录

#由于建表时是设置的ON DELETE CASCADE ON UPDATE CASCADE
#所以删除与更新都是级联
DELETE FROM s210224
WHERE Sno= 'S2';

在这里插入图片描述

(12)请将(S2,P4,J6,200)插入供应情况关系

#上一问中已经删除了S2
# 为了保证正常运行,我重新添加了一遍完整的SQL再执行以下语句
INSERT INTO spj210224(SNO,PNO,JNO,QTY)
VALUES('S2','J6','P4',200);

在这里插入图片描述

(13)请为三建工程项目建立一个供应情况的视图

包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询:

  1. 找出三建工程项目使用的各种零件代码及其数量;
  2. 找出供应商S1的供应情况。

建立视图:

CREATE VIEW IS_SJ
AS 
SELECT SNO,PNO,QTY 
FROM spj210224
WHERE JNO =(
	SELECT JNO
	FROM j210224
	WHERE j210224.JNAME='三建'
);

在这里插入图片描述

(14)找出三建工程项目使用的各种零件代码及其数量
SELECT PNO,sum(QTY)
FROM IS_SJ 
GROUP BY PNO;

在这里插入图片描述

(15)找出供应商S1的供应情况
SELECT DISTINCT * 
FROM IS_SJ 
WHERE SNO='S1';

在这里插入图片描述

(16)按供应商代码统计每位供应商供应某种零件的供应总量

要求:

  1. 仅显示供应总量 >= 500的信息
  2. 显示 SNO、PNO 和供应总量
  3. 显示时,查询结果按供应总量降序排列,供应总量相同按 SNO 升序、PNO 降序排列
SELECT SNO, PNO, SUM(QTY) AS All_Qty
FROM spj210224
GROUP BY SNO,PNO
HAVING All_Qty >= 500
ORDER BY All_Qty DESC, SNO ASC, PNO DESC;

在这里插入图片描述

  • 24
    点赞
  • 131
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
数据库系统设计 实验报告 1.实验目的 掌握数据库设计的基本方法和步骤,熟悉数据库设计各个阶段所要完成的任务和实施 方法。通过该实验更加清楚地了解数据库设计的过程。 2.实验原理和步骤 利用数据库设计的4个阶段:需求分析(分析用户要求)、概念设计(信息分析和定义) 、逻辑设计(设计实现)和物理设计(物理数据库设计)。需求分析采用自顶向下,概念模 型设计时采用自底向上的方法,即自顶向下地进行需求分析,然后再自底向上地设计概 念结构,和自底向上的设计方法。 3.实验内容 根据周围的实际情况,自选一个小型的数据库应用项目,并深入到应用项目中调研, 进行分析和设计。例如可选择人事管理系统、工资管理系统、教材管理系统和小型超市 商品管理系统和图书管理系统等。要求写出数据库设计报告。 在数据库设计报告中包括以下内容: (1)系统需求分析报告 (2)概念模型的设计(E-R图) (3)关系数据模型的设计 (4)对关系表的主键、外键及被参照表进行说明,对关系中数据的约束条件进行说明。 (5)用SQL语言建立该数据库。(包括数据库数据表的创建以及数据完整性的实施) (选作) 实验指导 1. 小型超市商品管理系统数据库设计 我们这边就以小型超市商品管理系统为例,介绍数据库设计。 对于小型超市商品管理系统,要求:能对小型超市商场的商品进货、销售、库存等环节 进行管理。主要有: (1)能记录每一笔进货,查询商品的进货记录。 (2)能记录每一笔售货,查询商品的销售情况和销售记录。 (3)能记录顾客的购买信息。 (4)能查询某个厂商或供应商的信息。 E-R图如下: 转换为关系模式: 供应商(供应商编号,供应商名称,供应商地址,电话,联系人) 商品(商品号,供应商编号,名称,库存,进价,售价) 顾客(顾客号,购货时间) 售货(商品号,顾客号,销售数量,销售总价) 2. 图书馆日常事务管理系统数据库设计 (1)图书馆日常事务管理系统功能简析 图书信息维护:主要完成图书信息登记、修改、删除等操作。 读者信息维护:主要完成读者信息的添加、修改和删除等操作。 工作人员信息维护:主要完成工作人员信息的添加、修改和删除等操作。 图书类别的管理:主要完成图书类别的添加、修改和删除等操作 图书借还管理:主要完成读者图书借还信息的记录。 (2)图书馆日常事务管理系统中的实体和属性的设计 读者(借书证号,姓名,性别,出生日期,借书量,单位,电话,E-mail) 图书(图书编号,图书名称,作者,出版社,定价,购进日期,购入数,复本数,库存 数) 工作人员(工号,姓名,性别,出生日期,联系电话,E-mail) 图书类别(类别号,图书类别) 其中,每本图书都有惟一的一个图书类别,每个图书类别有多本图书;每个读者可以借 阅多本图书;工作人员负责读者的借、还工作。 (3)设计该系统数据库的E-R图 (4)将设计好的E-R图转换为关系模式 读者(借书证号,姓名,性别,出生日期,借书量,单位,电话,E-mail) 图书(图书编号,图书名称,作者,出版社,定价,购进日期,购入数,复本数,库存 数) 工作人员(工号,姓名,性别,出生日期,联系电话,E-mail) 图书类别(类别号,图书类别) 图书借阅(图书编号,借书证号,借出日期,归还日期) 图书借还(图书编号,工号,借还,数量,借书日期,还书日期) ----------------------- 数据库系统设计实验报告全文共4页,当前为第1页。 数据库系统设计实验报告全文共4页,当前为第2页。 数据库系统设计实验报告全文共4页,当前为第3页。 数据库系统设计实验报告全文共4页,当前为第4页。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

萌宅鹿同学

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值