《数据库系统概论》课程之实验二：交互式SQL

实验目的

1．熟悉数据库的交互式SQL工具。

2．熟悉通过SQL对数据库进行操作。

3．完成作业的上机练习。

实验工具 MySQL

利用MySQL及其图形化界面工具Navicat来熟悉SQL。

实验流程

1. 建立数据库

在MySQL中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。

第4题

用SQL语句建立第2章习题6中的4个表；针对建立的4个表用SQL语言完成第2章习题6中的查询。

建表并添加数据

运行以下SQL语句：

# 建立供应商表S
CREATE TABLE S 
(
	SNO VARCHAR(5) PRIMARY KEY,
	SNAME VARCHAR(10),
	STATUS VARCHAR(5),
	CITY VARCHAR(10)
);

# 向表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','上海');

# 建立零件表P
CREATE TABLE P
(
	PNO VARCHAR(5) PRIMARY KEY,
	PNAME VARCHAR(10),
	COLOR VARCHAR(5),
	WEIGHT INT
);

# 向表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);

# 建立工程项目表J
CREATE TABLE J
(
	JNO VARCHAR(5) PRIMARY KEY,
	JNAME VARCHAR(10),
	CITY VARCHAR(10)
);

# 向表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','半导体厂','南京');

# 建立供应情况表SPJ
CREATE TABLE SPJ
(
	SNO CHAR(5),
	PNO CHAR(5),
	JNO CHAR(5),
	QTY INT,
	PRIMARY KEY(SNO,PNO,JNO),
	FOREIGN KEY(SNO) REFERENCES S(SNO),
	FOREIGN KEY(PNO) REFERENCES P(PNO),
	FOREIGN KEY(JNO) REFERENCES J(JNO)
);

# 向表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('S1','P2','J2',100);
INSERT INTO SPJ VALUES('S2','P3','J1',400);
INSERT INTO SPJ VALUES('S2','P3','J2',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);

结果

供应商表S：

![image-20220324233303964](《数据库系统概论》课程之实验二交互式SQL/image-20220324233303964.png)
零件表P：

![image-20220324233550612](《数据库系统概论》课程之实验二交互式SQL/image-20220324233550612.png)
工程项目表J：

![image-20220324233528109](《数据库系统概论》课程之实验二交互式SQL/image-20220324233528109.png)
供应情况表SPJ：

![image-20220324233643934](《数据库系统概论》课程之实验二交互式SQL/image-20220324233643934.png)

查询

求供应工程J1零件的供应商号码SNO:
- SQL：
  1
  SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1';
- 结果：
  
  ![image-20220325102815380](《数据库系统概论》课程之实验二交互式SQL/image-20220325102815380.png)
求供应工程J1零件P1的供应商号码SNO:
- SQL：
  1
  SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1';
- 结果：
  
  ![image-20220325102913718](《数据库系统概论》课程之实验二交互式SQL/image-20220325102913718.png)
求供应工程J1零件为红色的供应商号码SNO：
- SQL：
  1
  2
  SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1' AND PNO IN (SELECT DISTINCT PNO FROM P WHERE COLOR='红');
- 结果：
  
  ![image-20220325103030728](《数据库系统概论》课程之实验二交互式SQL/image-20220325103030728.png)
求没有使用天津供应商生产的红色零件的工程号JNO：
- SQL：
  1
  2
  3
  4
  5
  6
  SELECT DISTINCT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM SPJ,P,S WHERE S.CITY='天津' AND P.COLOR='红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO);
- 结果：
  
  ![image-20220325103213694](《数据库系统概论》课程之实验二交互式SQL/image-20220325103213694.png)

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

SQL：

SELECT DISTINCT JNO
FROM SPJ SPJX
WHERE NOT EXISTS -- 不存在情况:
	(SELECT *
	 FROM SPJ SPJY
	 WHERE SNO='S1'  
	 AND NOT EXISTS -- S1提供的零件没有被该工程使用 
		(SELECT *									
		 FROM SPJ SPJZ
		 WHERE SPJZ.PNO=SPJY.PNO    -- 确定S1的零件名
         	AND SNO='S1'			-- 确定零件是S1提供的
			AND SPJZ.JNO=SPJX.JNO)); -- 确定工程

结果：

![image-20220325111114717](《数据库系统概论》课程之实验二交互式SQL/image-20220325111114717.png)

第5题

针对习题4中的4个表试用SQL语言完成以下各项操作：

找出所有供应商的姓名和所在城市：
- SQL：
  1
  SELECT DISTINCT SNAME,CITY FROM S;
- 结果：
  
  ![image-20220325120743410](《数据库系统概论》课程之实验二交互式SQL/image-20220325120743410.png)
找出所有零件的名称、颜色、重量；
- SQL：
  1
  SELECT DISTINCT PNAME,COLOR,WEIGHT FROM P;
- 结果：
  
  ![image-20220325121417842](《数据库系统概论》课程之实验二交互式SQL/image-20220325121417842.png)
找出使用供应商S1所供应零件的工程号码：
- SQL：
  1
  SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';
- 结果：
  
  ![image-20220325121452594](《数据库系统概论》课程之实验二交互式SQL/image-20220325121452594.png)
找出工程项目J2使用的各种零件的名称及其数量：
- SQL：
  1
  2
  SELECT DISTINCT PNAME,QTY FROM P,SPJ WHERE SPJ.JNO='J2' AND P.PNO=SPJ.PNO;
- 结果：
  
  ![image-20220325121520442](《数据库系统概论》课程之实验二交互式SQL/image-20220325121520442.png)
找出上海厂商供应的所有零件号码：
- SQL：
  1
  2
  SELECT DISTINCT PNO FROM S,SPJ WHERE S.CITY='上海' AND S.SNO=SPJ.SNO;
- 结果：
  
  ![image-20220325121546585](《数据库系统概论》课程之实验二交互式SQL/image-20220325121546585.png)
找出使用上海产的零件的工程名称：
- SQL：
  1
  2
  SELECT DISTINCT JNO FROM S,SPJ WHERE S.CITY='上海' AND S.SNO=SPJ.SNO;
- 结果：
  
  ![image-20220325121633952](《数据库系统概论》课程之实验二交互式SQL/image-20220325121633952.png)
找出没有使用天津产的零件的工程号码：
- SQL：
  1
  2
  3
  SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM S,SPJ WHERE S.CITY='天津' AND S.SNO=SPJ.SNO);
- 结果：
  
  ![image-20220325121707581](《数据库系统概论》课程之实验二交互式SQL/image-20220325121707581.png)
把全部红色零件的颜色改成蓝色：
- SQL：
  1
  2
  UPDATE P SET COLOR='蓝' WHERE COLOR='红'; SELECT * FROM P; -- 用于显示结果
- 结果：
  
  ![image-20220325121828186](《数据库系统概论》课程之实验二交互式SQL/image-20220325121828186.png)
由S5供给J4的零件P6改为由S3供应，请作必要的修改：
- SQL：
  1
  2
  UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6'; SELECT * FROM SPJ; -- 用于显示结果
- 结果：
  
  ![image-20220325121855651](《数据库系统概论》课程之实验二交互式SQL/image-20220325121855651.png)
从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录：

因为表SPJ的外键SNO是表S的主键，故要先从表SPJ中删除S2的记录，在从表S中删除S2的记录
- SQL：
  1
  2
  DELETE FROM SPJ WHERE SNO='S2'; DELETE FROM S WHERE SNO='S2';
- 结果：
  - 表SPJ：
    
    ![image-20220325122904368](《数据库系统概论》课程之实验二交互式SQL/image-20220325122904368.png)
  - 表S：
    
    ![image-20220325122921502](《数据库系统概论》课程之实验二交互式SQL/image-20220325122921502.png)
请将（S2，J6，P4，200）插入供应情况关系：

因为表SPJ的外键SNO是表S的主键，故在表SPJ插入关于S2的记录前，要先在表S中还原S2的信息
- SQL：
  1
  2
  INSERT INTO S VALUES('S2','盛锡','10','北京'); INSERT INTO SPJ VALUES('S2','P6','J6',200);
- 结果：
  - 表S：
    
    ![image-20220325123114711](《数据库系统概论》课程之实验二交互式SQL/image-20220325123114711.png)
  - 表SPJ：
    
    ![image-20220325123213720](《数据库系统概论》课程之实验二交互式SQL/image-20220325123213720.png)

第9题

请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）：

SQL:

1
2
3

CREATE VIEW SANJIAN AS
SELECT SNO,PNO,QTY FROM SPJ,J
WHERE SPJ.JNO=J.JNO AND J.JNAME='三建'

在“视图”中查看结果：

![image-20220325164742884](《数据库系统概论》课程之实验二交互式SQL/image-20220325164742884.png)

针对该试图完成下列查询：

找出三建工程项目使用的各种零件代码及其数量：
- SQL：
  1
  SELECT DISTINCT PNO,QTY FROM SANJIAN;
- 结果：
  
  ![image-20220325164915171](《数据库系统概论》课程之实验二交互式SQL/image-20220325164915171.png)
找出供应商S1的供应情况。
- SQL：
  1
  SELECT * FROM SANJIAN WHERE SNO='S1';
- 结果：
  
  ![image-20220325165039739](《数据库系统概论》课程之实验二交互式SQL/image-20220325165039739.png)

实验心得

通过这次实验，我学会通过SQL语句对基本表、索引和视图进行创建、修改及删除，并完成各类的查询数据、更新数据操作。在整个实验过程中，主要有三个注意点：

一是IN或NOT IN前需要指定字段，否则会产生SQL语法错误，例子如下：

![image-20220325120605243](《数据库系统概论》课程之实验二交互式SQL/image-20220325120605243.png)

二是使用INSERT语句插入、UPDATE语句更新数据时注意字段在表中顺序。
三是要注意数据库完整性，包括：
- 实体完整性，要求每个关系（表）有且仅有一个主键，每一个主键值必须唯一，而且不允许为“空”（NULL）或重复。
- 域完整性，指数据库表中的列必须满足某种特定的数据类型或约束。
- 参照完整性，属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一，就会影响数据的完整性。故删除一个表的主键时，若它是另一个表的外键，要先把在另一个表中把这个外键删除。

数据库系统课程实验

数据库系统实验

本博客所有文章除特别声明外，均为博客作者本人编写整理，转载请联系作者！