【数据库系统设计】SQL语言实验 | 您所在的位置:网站首页 › 创建数据库SQL完整语句 › 【数据库系统设计】SQL语言实验 |
SQL语言
一、实验目的和要求二、实验环境(实验设备)三、实验原理及内容1、创建 SPJ 数据库及 S、P、J、SPJ 表2、用SQL语句完成增删改操作以及指定查询(1)向四个基表中插入数据!!!!!!!!!衔接!!!!!!!!!(2)找出所有供应商的姓名和所在城市(3)找出所有零件的名称、颜色、重量(4)找出使用供应商S1所供应零件的工程号码(5)找出工程项目J2使用的各种零件的名称及其数量(6)找出上海厂商供应的所有零件号码(7)找出使用上海产的零件的工程名称(8)找出没有使用天津产的零件的工程号码(9)把全部红色零件的颜色改成蓝色(10)由S5供给J4的零件P6改为由S3供应,请作必要的修改(11)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录(12)请将(S2,P4,J6,200)插入供应情况关系(13)请为三建工程项目建立一个供应情况的视图(14)找出三建工程项目使用的各种零件代码及其数量(15)找出供应商S1的供应情况
(16)按供应商代码统计每位供应商供应某种零件的供应总量
一、实验目的和要求
(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创建 SPJ 数据库: CREATE DATABASE SPJ210224;
创建四个基表 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; # 数据库引擎与字符集可不设置
创建 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; # 数据库引擎与字符集可不设置只需写出向每个表插入第一条记录的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', '三建', '北京');
要求是只需要提供插入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;包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询: 找出三建工程项目使用的各种零件代码及其数量;找出供应商S1的供应情况。建立视图: CREATE VIEW IS_SJ AS SELECT SNO,PNO,QTY FROM spj210224 WHERE JNO =( SELECT JNO FROM j210224 WHERE j210224.JNAME='三建' );要求: 仅显示供应总量 >= 500的信息显示 SNO、PNO 和供应总量显示时,查询结果按供应总量降序排列,供应总量相同按 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; |
CopyRight 2018-2019 实验室设备网 版权所有 |