数据库第二次上机实验报告

实验五表数据的维护

一、实验目的

熟练掌握使用 Transact-SQL 语言和通过 Navicat 输入表数据、修改表数据和删除表数据的操作。

二、实验内容

1、在数据库 world 中建立一个名为 newlanguage 的表，其结构与表 countrylanguage 完全一样（注意各字段的默认值、字符集、排序方式等），如图：

这里要注意，enum 类型的字段要设置可供选择的值。

这里其实建议使用建表语句（可以直接使用 SHOW CREATE TABLE countrylanguage; 来拷贝一下 countrylanguage 中的这个表，省去了一个一个设置字符集的麻烦，也解决了 enum 的问题）。

CREATE TABLE `newlanguage` (
  `CountryCode` char(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `Language` char(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`) USING BTREE,
  KEY `CountryCode` (`CountryCode`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

注：这里在拷贝建表语句时，不能把外键的设置带上。

2、在表 newlanguage 中插入中国的客家话，其名称为 CountryCode为CHN，language 为 Kejia，isOfficial 为 F，percentage 为 0.3。

INSERT INTO newlanguage (CountryCode, Language, IsOfficial, Percentage)
VALUES
('CHN', 'Kejia', 'F', 0.3);

注：这里实验指南本身漏掉了第 3 题。

4、试将表 countrylanguage 中的所有记录插入到表 newlanguage 中去。

INSERT INTO newlanguage SELECT * FROM countrylanguage;

5、将表 newlanguage 中 Language 为 “Kejia” 的语言的 Percentage 改为 0.4。

UPDATE newlanguage SET Percentage = 0.4
WHERE Language = 'Kejia';

6、将表 newlanguage 中语言的 Percentage 均减去 0.1。

UPDATE newlanguage SET Percentage = Percentage - 0.1;

修改前：

修改后：

7、删除表 newlanguage 中澳大利亚（CountrCode 为“AUS”）的英语记录。

DELETE FROM newlanguage
WHERE CountryCode = 'AUS' AND Language = 'English';

8、清空表 newlanguage 中的所有数据。

DELETE FROM newlanguage;

三、课后练习题

以下题目在数据库 world 中完成。

1、通过 Navicat，在 country、countrylanguage、和 city 三个表中各输入 10 条记录。

其他两张表的插入略。

2、将一条新的城市记录（Name：Beijing，CountryCode：AFG，District：Beijing，Population：21148000）插入表 city 中。

INSERT INTO city (Name, CountryCode, District, Population)
VALUES
('Beijing', 'AFG', 'Beijing', 21148000);

3、插入一条语言记录（“CHN”，“Minnan”，“F”，0.5）到表 countrylanguage。

INSERT INTO countrylanguage (CountryCode, Language, IsOfficial, Percentage)
VALUES
('CHN', 'Minnan', 'F', 0.5);

4、删除 CountryCode 为“ABW”的语言记录。

DELETE FROM countrylanguage
WHERE
CountryCode = 'ABW';

实验六视图管理

一、实验目的

掌握视图的定义与维护操作，加深对视图在关系数据库中作用的理解。

二、实验内容

在数据库 world 中完成以下操作：

1、建立亚洲国家的视图 asia_country，并要求进行修改和插入操作时仍需保证该视图只有亚洲国家，视图的属性名为 Code，Name，Continent，Population，GNP，GovernmentForm。

USE world;

CREATE VIEW asia_country
AS
SELECT Code, Name, Continent, Population, GNP, GovernmentForm
FROM country
WHERE Continent = 'Asia';

对于视图的使用，将其当做基本表即可，可用下面语句用来显示 asia_country 表中的所有亚洲国家：

SELECT * FROM asia_country;

或者直接在左侧目录【world】下的【视图】下多出的“asia_country”上点击右键，在弹出选项里选择“Open View”。

2、建立国家简称(country.Code)、城市名(city.Name)、语言名(countrylanguage.Language)的视图 cc_language。本视图由三个基本表的连接操作导出。

结果如图：

CREATE VIEW cc_language
AS
SELECT country.Code, city.`Name`, countrylanguage.`Language`
FROM country LEFT JOIN city
ON country.`Code` = city.CountryCode
LEFT JOIN countrylanguage
on country.`Code` = countrylanguage.CountryCode;

3、定义一个反映国家独立年份的视图 c_indepyear。

结果如图：

4、删除视图 c_indepyear。

DROP VIEW c_indepyear;

5、在亚洲国家视图 asia_country 中找出国民生产总值 GNP 大于 50000 的国家名称和 GNP。

SELECT `Name`, GNP
FROM asia_country
WHERE GNP > 50000;

6、在 asia_country 视图中人口多于一千万的国家名称、政体。

SELECT `Name`, GovernmentForm
FROM asia_country
WHERE Population > 10000000;

7、将亚洲国家视图 asia_country 中 Code 为“AFG”的政体改为“Republic”。

UPDATE asia_country
SET GovernmentForm = 'Republic'
WHERE Code = 'AFG';

8、向亚洲国家视图 asia_country 中插入一个新的国家记录，其中 Code 为“DMC”、Name 为“Demacia”，Cotinent 为“Asia”，Population 为“10000000”，GNP 为“50000”，GovernmentForm 为“Monarchy”。

INSERT INTO asia_country (Code, Name, Continent, Population, GNP, GovernmentForm)
VALUES
('DMC', 'Demacia', 'Asia', 10000000, 50000, 'Monarchy');

三、课后练习题

1、建立共和国政体的国家的视图。

CREATE VIEW republic_country
AS
SELECT Code, Name, Continent, Population, GNP, GovernmentForm
FROM country
WHERE GovernmentForm = 'Republic';

2、建立君主立宪制（Constitutional Monarchy）国家的视图，并要求进行修改和插入操作时仍须保证该视图只有君主立宪制国家。

CREATE VIEW const_monarchy_country
AS
SELECT Code, Name, Continent, Population, GNP, GovernmentForm
FROM country
WHERE GovernmentForm = 'Constitutional Monarchy'
WITH CASCADED CHECK OPTION;

3、建立共和国政体且国名生产总值在 10000 以上的国家视图。

CREATE VIEW gnp_republic_country
AS
SELECT Code, Name, Continent, Population, GNP, GovernmentForm
FROM country
WHERE GovernmentForm = 'Republic' AND GNP > 10000;

注：这里并没有使用原来的表的全部属性名。

实验七数据类型的使用

一、实验目的

熟练掌握使用 Navicat 和 Transact-SQL 语言两种方法创建、修改和删除表。掌握管理表的有关系统存储过程。

二、实验内容

1、了解并尝试使用MySQL的各数据类型，不作实验操作内容上的硬性要求。

1.1 日期和时间数据类型

1.2 整型

1.3 浮点型

1.4 字符串类型

1.5 其他类型

2、了解 MySQL 数据类型属性，并依次实际操作、验证。

2.1 auto_increment

2.2 binary

2.3 default

2.4 index

2.5 not null

2.6 null

2.7 primary key

2.8 unique

2.9 zerofill

3、如果要设计一个表用来存放商品的基本信息，包括商品编号、商品名称、品牌商标、型号、产地、生产厂商、生产日期、保质期、进货价格、销售价格和商品图片等。请设计出这个表的表结构，包括表名、列名、列的数据类型和长度等等。

使用 Navicat 建表：

建表语句：

CREATE TABLE `goodsinfo` (
  `number` char(10) NOT NULL,
  `name` char(20) NOT NULL,
  `trademark` char(20) NOT NULL,
  `model` char(20) NOT NULL,
  `madein` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `company` char(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `madedate` datetime NOT NULL,
  `shelflife` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `rawprice` float(10,2) NOT NULL,
  `saleprice` float(10,2) NOT NULL,
  `picture` char(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

4、将数据库 world 中 country 表的 capital 字段的数据类型改为 varchar(20)。

① Navicat—Design Table

② 查询编辑器，输入以下语句：

USE world;
alter table country modify column Capital varchar(20);

三、课后练习题

1、用 Navicat 和 SQL 语言两种方式将数据库 world 中 Indepyear 字段的数据类型改为 char(5)。

Navicat:

右键点击表，Design Table。

SQL 语句：

alter table country modify column IndepYear char(5);

2、在 MySQL 中建立一个表，有一列为 float(5, 3)。做以下试验： - ① 插入 12.345，成功则查询该表得到结果； - ② 插入 12.3456，成功则查询该表得到结果； - ③ 插入 123.456，成功则查询该表得到结果。

比较三次结果的差异，并分析原因。

建表：

CREATE TABLE `drafttable` (
  `id` int NOT NULL,
  `money` float(5,3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci;

插入数据：

INSERT INTO drafttable (id, money)
VALUES
(1, 12.345);

INSERT INTO drafttable (id, money)
VALUES
(2, 12.3456);

INSERT INTO drafttable (id, money)
VALUES
(3, 123.456);

分析：

第一次数据插入正常。第二次插入数据因为小数部分位数超过了限制，所以 MySQL 进行自动舍入。第三次插入数据因为小数部分和整数部分都超出了范围，所以 MySQL 报了一个 Out of range 的错误。

实验八表数据的高级查询

一、实验目的

本实验的目的是使学生熟练掌握 MySQL 查询分析器的使用方法，加深 SQL 语言查询语句的理解。熟练掌握数据查询中的分组、统计、计算和集合的操作方法。

二、实验内容

在数据库 world 中完成以下操作。

1、查询所有国家名称及相应的城市、语言。

SELECT co.Name CountryName, ci.Name CityName, cl.`Language`
FROM country co, city ci, countrylanguage cl
WHERE co.Code = ci.CountryCode AND co.`Code` = CL.CountryCode;

2、查询国家政体为共和国且国家人口在一千万以上的城市的名称和城市人口、所属国家。

SELECT ci.Name CityName, ci.Population, co.Name CountryName
FROM country co, city ci
WHERE
co.GovernmentForm = 'Republic'
AND
co.Population > 10000000
AND
co.Code = ci.CountryCode;

3、统计 country 表中共和国政体的国家数。

SELECT COUNT(*) FROM country
WHERE GovernmentForm = 'Republic';

4、统计 country 表中共和国政体国家的平均人口。

SELECT AVG(Population) FROM country
WHERE GovernmentForm = 'Republic';

按：

AVG 的使用：

AVG([distinct|all]x)

[功能]：统计数据表选中行x列的平均值。
[参数]：all表示对所有的值求平均值，distinct 只对不同的值求平均值，默认为 all。
如果有参数 distinct 或 all，需有空格与 x(列)隔开。

5、统计 countrylanguage 表中官方语言数。

SELECT COUNT(*) FROM countrylanguage
WHERE IsOfficial = 'T';

6、分组统计 country 表中各政体的国家个数。

SELECT GovernmentForm, COUNT(*) FROM country
GROUP BY GovernmentForm;

7、分组统计各大洲平均每国人口数。

SELECT Continent, AVG(Population) FROM country
GROUP BY Continent;

8、查询有超过两条城市记录的国家的名称。

SELECT co.Name, tmp.num FROM
country co, (SELECT CountryCode, COUNT(*) as num FROM city
GROUP BY CountryCode) tmp
WHERE
co.`Code` = tmp.CountryCode
AND tmp.num > 2;

9、查询非共和政体的国家的名称和政体。

SELECT Name, GovernmentForm
FROM country
WHERE
GovernmentForm != 'Republic';

10、查询独立年份未知的国家的缩写和名称。

SELECT Code, Name
FROM country
WHERE IndepYear is NULL;

11、查询欧洲国民生产总值 GNP 排名前 20 的国家名称及 GNP 值。

SELECT Name, GNP
FROM country
WHERE Continent = 'Europe'
ORDER BY GNP DESC
LIMIT 20;

12、查询平均每国人口数高于非洲的大洲名称及该平均数，以平均数的降序排列。

SELECT * FROM 
(SELECT Continent, AVG(Population) AS avgPopulation FROM countryGROUP BY Continent) AS tmp
WHERE avgPopulation > 
(
		SELECT avgPopulation FROM (SELECT Continent, AVG(Population) AS avgPopulation FROM country GROUP BY Continent) AS tmp 
		WHERE Continent = 'Africa'
);

13、查询 city 表中多于 3 个城市记录且缩写以 “A” 开头的国家的名称和城市平均人口，以平均人口的升序排列。

SELECT tmp2.Name, AVG(ci.Population) AS avgPopulation FROM
(SELECT co.Name, co.`Code`, tmp.num FROM
country co, (SELECT CountryCode, COUNT(*) as num FROM city
GROUP BY CountryCode) tmp
WHERE
co.`Code` = tmp.CountryCode
AND tmp.num > 3
AND co.`Code` REGEXP '^A') AS tmp2, city ci
WHERE tmp2.Code = ci.CountryCode
GROUP BY tmp2.`Name`
ORDER BY avgPopulation;

三、课后练习题

1、建立名为 SPJ 的数据库。它包括 S、P、J、SPJ 4 个关系模式：

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

供应商表 S 由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；
零件表 P 由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；
工程项目表 J 由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成；
供应情况表 SPJ 由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商供应某种零件给某工程项目的数量为QTY。

今有若干数据如下：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `j`
-- ----------------------------
DROP TABLE IF EXISTS `j`;
CREATE TABLE `j` (
  `JNO` char(10) NOT NULL,
  `JNAME` char(10) DEFAULT NULL,
  `CITY` char(10) DEFAULT NULL,
  PRIMARY KEY (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of j
-- ----------------------------
INSERT INTO `j` VALUES ('J1', '三建', '北京');
INSERT INTO `j` VALUES ('J2', '一汽', '长春');
INSERT INTO `j` VALUES ('J3', '弹簧厂', '天津');
INSERT INTO `j` VALUES ('J4', '造船厂', '天津');
INSERT INTO `j` VALUES ('J5', '机车厂', '唐山');
INSERT INTO `j` VALUES ('J6', '无线电厂', '常州');
INSERT INTO `j` VALUES ('J7', '半导体厂', '南京');

-- ----------------------------
-- Table structure for `p`
-- ----------------------------
DROP TABLE IF EXISTS `p`;
CREATE TABLE `p` (
  `PNO` char(10) NOT NULL,
  `PNAME` char(10) DEFAULT NULL,
  `COLOR` char(10) DEFAULT NULL,
  `WEIGHT` int(11) DEFAULT NULL,
  PRIMARY KEY (`PNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of p
-- ----------------------------
INSERT INTO `p` VALUES ('P1', '螺母', '红', '12');
INSERT INTO `p` VALUES ('P2', '螺栓', '绿', '17');
INSERT INTO `p` VALUES ('P3', '螺丝刀', '蓝', '14');
INSERT INTO `p` VALUES ('P4', '螺丝刀', '红', '14');
INSERT INTO `p` VALUES ('P5', '凸轮', '蓝', '40');
INSERT INTO `p` VALUES ('P6', '齿轮', '红', '30');

-- ----------------------------
-- Table structure for `s`
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `SNO` char(10) NOT NULL,
  `SNAME` char(10) DEFAULT NULL,
  `STATUS` char(10) DEFAULT NULL,
  `CITY` char(10) DEFAULT NULL,
  PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES ('S1', '精益', '20', '天津');
INSERT INTO `s` VALUES ('S2', '盛锡', '10', '北京');
INSERT INTO `s` VALUES ('S3', '东方红', '30', '北京');
INSERT INTO `s` VALUES ('S4', '丰泰盛', '20', '天津');
INSERT INTO `s` VALUES ('S5', '为民', '30', '上海');

-- ----------------------------
-- Table structure for `spj`
-- ----------------------------
DROP TABLE IF EXISTS `spj`;
CREATE TABLE `spj` (
  `SNO` char(10) NOT NULL,
  `PNO` char(10) NOT NULL,
  `JNO` char(10) NOT NULL,
  `QTY` int(11) NOT NULL,
  PRIMARY KEY (`SNO`,`PNO`,`JNO`),
  KEY `PNO` (`PNO`),
  KEY `JNO` (`JNO`),
  CONSTRAINT `spj_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s` (`SNO`),
  CONSTRAINT `spj_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p` (`PNO`),
  CONSTRAINT `spj_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j` (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of spj
-- ----------------------------
INSERT INTO `spj` VALUES ('S1', 'P1', 'J1', '200');
INSERT INTO `spj` VALUES ('S1', 'P1', 'J3', '100');
INSERT INTO `spj` VALUES ('S1', 'P1', 'J4', '700');
INSERT INTO `spj` VALUES ('S2', 'P3', 'J1', '100');
INSERT INTO `spj` VALUES ('S2', 'P3', 'J2', '400');
INSERT INTO `spj` VALUES ('S2', 'P3', 'J3', '200');
INSERT INTO `spj` VALUES ('S2', 'P3', 'J4', '500');
INSERT INTO `spj` VALUES ('S2', 'P3', 'J5', '400');
INSERT INTO `spj` VALUES ('S2', 'P5', 'J1', '400');
INSERT INTO `spj` VALUES ('S2', 'P5', 'J2', '100');
INSERT INTO `spj` VALUES ('S3', 'P1', 'J1', '200');
INSERT INTO `spj` VALUES ('S3', 'P3', 'J1', '200');
INSERT INTO `spj` VALUES ('S4', 'P5', 'J1', '100');
INSERT INTO `spj` VALUES ('S4', 'P6', 'J3', '300');
INSERT INTO `spj` VALUES ('S4', 'P6', 'J4', '200');
INSERT INTO `spj` VALUES ('S5', 'P2', 'J4', '100');
INSERT INTO `spj` VALUES ('S5', 'P3', 'J1', '200');
INSERT INTO `spj` VALUES ('S5', 'P6', 'J2', '200');
INSERT INTO `spj` VALUES ('S5', 'P6', 'J4', '500');

第 2～8 题在数据库 SPJ 中完成。

2、查询重量最轻的零件的零件代码。

SELECT PNO FROM p
WHERE WEIGHT = (SELECT MIN(WEIGHT) FROM p);

3、查询由供应商 S1 提供零件的工程项目名。

SELECT JNAME FROM
spj AS T1, j AS T2
WHERE T1.SNO = 'S1'
AND T1.JNO = T2.JNO;

4、查询同时为工程 J1 和 J2 提供零件的供应商代码。

SELECT SNO FROM spj WHERE JNO = 'J1' AND SNO IN (SELECT SNO FROM spj WHERE JNO = 'J2');

5、查询为位于天津的工程提供零件的供应商代码。

SELECT DISTINCT SNO FROM spj
WHERE JNO IN
(SELECT JNO FROM j
WHERE CITY = '天津');

6、查询同时为位于天津或北京的工程提供红色零件的供应商代码。

SELECT DISTINCT SNO FROM spj
WHERE JNO IN
(SELECT JNO FROM j
WHERE CITY = '天津')
AND SNO IN
(SELECT DISTINCT SNO FROM spj
WHERE JNO IN
(SELECT JNO FROM j
WHERE CITY = '北京'))
AND PNO IN
(SELECT PNO FROM p
WHERE COLOR = '红');

7、查询供应商和工程所在城市相同的供应商能提供的零件代码。

SELECT PNO
FROM spj
INNER JOIN J
ON J.JNO = spj.JNO
INNER JOIN S
ON S.SNO = spj.SNO
WHERE S.CITY = J.CITY
GROUP BY spj.PNO;

8、查询上海供应商不提供任何零件的工程代码。

SELECT spj.JNO
FROM spj
INNER JOIN S
ON S.SNO = spj.SNO
GROUP BY spj.JNO
HAVING COUNT(CASE WHEN S.CITY ='上海' THEN '1' END) = 0;

数据库

#数据库

数据库第二次上机实验报告

http://fanyfull.github.io/2021/11/24/数据库第二次上机实验报告/

作者

Fany Full

发布于

2021年11月24日

许可协议

华科软院计算机组成原理实验整理上一篇

matplotlib 绘制更加清晰（分辨率更高）的图像下一篇

数据库第二次上机实验报告

实验五 表数据的维护

一、实验目的

二、实验内容

三、课后练习题

实验六 视图管理

一、实验目的

二、实验内容

三、课后练习题

实验七 数据类型的使用

一、实验目的

二、实验内容

三、课后练习题

实验八 表数据的高级查询

一、实验目的

三、课后练习题

实验五表数据的维护

实验六视图管理

实验七数据类型的使用

实验八表数据的高级查询