【数据库原理】三级项目——数据库基本操作

一、项目名称

数据库基本操作

二、项目内容

1. 了解一种DBMS的功能和界面。
2. 使用图形化界面创建数据库。
3. 使用图形化界面创建课本70页习题6中的关系表。
4. 使用图形化界面向所建的关系表中插入数据。
5. 完成70页习题6第3-5小题的各项查询。
6. 查询每个城市供应的零件总数。
7. 查询使用零件数量最多的项目编号和零件数。

所用课本为王珊 萨师煊的《数据库系统概论 第五版》，如下图

三、实验报告

3.1 了解一种DBMS的功能和页面

在这里我使用的是下载MySQL 自带的DBMS——MySQL Workbench

Graph 1 MySQL Workbench软件的首页

Graph 2 新建数据库的链接（这里我之前已经创建了与端口3306的连接了）

Graph 3 填写连接相关的信息

注：这里我之前创了一个数据库连接，使用的是MySQL Sever默认端口3306，如果想用别的端口名可以更改MySQL Server的端口号，具体做法如下：你可以在MySQL Server配置文件中更改端口号。默认情况下，MySQL Server的配置文件通常是my.cnf（Unix/Linux）或my.ini（Windows）。您可以打开该文件，搜索并修改"port"（端口）的设置为一个未被占用的端口号，然后重启MySQL Server。完成后，在MySQL Workbench中创建新的数据库连接，并使用您指定的新端口号。

Graph 4 完成连接后的操作页面

注：该DBMS的功能栏已用红框框出并使用//给出了相关的使用注释

---

3.2 使用图形化界面创建数据库（创建模式）

Graph 5 右击模式列表空白处

Graph 6 在选项卡中填写该模式的相关信息

Graph 7 填写完成后点击Apply选项

Graph 8 一直Apply即可

Graph 9 创建selfpractise模式成功了

---

3.3 使用图形化界面创建课本70页习题6中的关系表。

设有一个 SPJ 数据库，包括 S、P、]及 SPJ4 个关系模式

• S(SNO,SNAMESTATUS,CITY)
• P(PNO.PNAME.COLORWEIGHT)
• J(JNOJNAME,CITY)
• SPJ(SNO,PNONO,QTY)

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

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

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

供应情况表 SPJ 由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(OTY)组成，表示某供应商供应某种零件给某工程项目的数量为 QTY。
今有若干数据如下

Graph 10 进入selfpractise模式右击Table后点击Create Table

Graph 11 填写该表的相关属性

Graph 12 S表的相关属性

Graph 13 创建S表的相关代码（系统自动生成的）

Graph 14 P表的相关属性

Graph 15 创建P表的相关代码（系统自动生成的）

Graph 16 J表的相关属性

Graph 17 创建J表的相关代码（系统自动生成的）

Graph 18 SPJ表的相关属性

Graph 19 创建SPJ表的相关代码（系统自动生成的）

---

3.4 使用图形化界面向所建的关系表中插入数据

Graph 20 进入插入数据页面

Graph 21 依次填写各项记录对应的属性值

Graph 22 J表填写情况

Graph 23 J表填写系统自动生成的代码

Graph 24 P表填写情况

Graph 25 P表填写系统自动生成的代码

Graph 26 S表填写情况

Graph 27 S表填写系统自动生成的代码

Graph 28 SPJ表填写情况

Graph 29 SPJ表填写系统自动生成的代码

注：修改了两处地方分别是各表的?NO类型和SPJ的key，以下是修改细节 1. 将SNO\PNO\JNO属性分别从INT类型调整为CHAR类型 2. 将SPJ表中SNO的PK属性给删除了

---

3.5 完成70页习题6第3-5小题的各项查询

3.5.1求供应工程J1零件为红色的供应商号码SNO

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `new_view` AS
    SELECT 
        `spj`.`SNO` AS `SNO`
    FROM
        `spj`
    WHERE
        ((`spj`.`JNO` = 'J1')
            AND `spj`.`PNO` IN (SELECT 
                `p`.`PNO`
            FROM
                `p`
            WHERE
                (`p`.`COLOR` = '红')))

Result:

3.5.2 求没有使用天津供应商生产的红色零件的工程号JNO

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `5-2` AS
    SELECT 
        `spj`.`JNO` AS `JNO`
    FROM
        `spj`
    WHERE
        (`spj`.`SNO` IN (SELECT 
                `s`.`SNO`
            FROM
                `s`
            WHERE
                (`s`.`CITY` <> '天津'))
            AND spj.PNO IN (SELECT 
                p.PNO
            FROM
                p
            WHERE
                (p.COLOR = '红')))

Result:

3.5.3 求至少用了供应商S1所提供的全部零件的工程号JNO

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `5-3` AS
    SELECT DISTINCT
        `spjz`.`JNO` AS `JNO`
    FROM
        `spj` `spjz`
    WHERE
        EXISTS( SELECT 
                `spjy`.`PNO`
            FROM
                `spj` `spjy`
            WHERE
                ((`spjy`.`JNO` = `spjz`.`JNO`)
                    AND EXISTS( SELECT 
                        `spjx`.`PNO`
                    FROM
                        `spj` `spjx`
                    WHERE
                        ((`spjx`.`SNO` = 'S1')
                            AND (`spjx`.`PNO` = `spjy`.`PNO`)))))

Result:

3.6 查询每个城市供应的零件总数

1. 先创建了一个j-spj的联合视图

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `j-spj` AS
    SELECT 
        `j`.`JNO` AS `JNO`,
        `j`.`CITY` AS `CITY`,
        `j`.`JNAME` AS `JNAME`,
        `spj`.`SNO` AS `SNO`,
        `spj`.`PNO` AS `PNO`
    FROM
        (`j`
        JOIN `spj`)
    WHERE
        (`j`.`JNO` = `spj`.`JNO`)

Result：

2. 随后使用查询操作进行分组

SELECT SUM(count) AS count, CITY
FROM (
    SELECT COUNT(PNO) AS count, CITY
    FROM selfpractise.`j-spj`
    GROUP BY JNO, CITY
) AS subquery
GROUP BY CITY;

Result：

---

3.7 查询使用零件数量最多的项目编号和零件数

1. 先创建项目编号和零件数的视图

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `jno-pnum` AS
    SELECT 
        `spj`.`JNO` AS `JNO`, COUNT(`spj`.`PNO`) AS `PNUM`
    FROM
        `spj`
GROUP BY `spj`.`JNO`

Result：

2. 随后对其零件数最大的一组进行查询

SELECT JNO, PNUM 
from selfpractise.`jno-pnum`
where PNUM = (select max(PNUM) from selfpractise.`jno-pnum`)

Result：