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

个人博客地址

《数据库系统概论》课程之实验二：交互式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：

  

• 零件表P：

  

• 工程项目表J：

  

• 供应情况表SPJ：

  

查询

1. 求供应工程J1零件的供应商号码SNO:

   • SQL：

     SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1';
     

   • 结果：

     

2. 求供应工程J1零件P1的供应商号码SNO:

   • SQL：

     SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1';
     

   • 结果：

     

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

   • SQL：

     SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1' AND PNO IN 
     (SELECT DISTINCT PNO FROM P WHERE COLOR='红');
     

   • 结果：

     

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

   • SQL：

     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);
     

   • 结果：

     

5. 求至少用了供应商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)); -- 确定工程
     

   • 结果：

     

第5题

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

1. 找出所有供应商的姓名和所在城市：

   • SQL：

     SELECT DISTINCT SNAME,CITY FROM S;
     

   • 结果：

     

2. 找出所有零件的名称、颜色、重量；

   • SQL：

     SELECT DISTINCT PNAME,COLOR,WEIGHT FROM P;
     

   • 结果：

     

3. 找出使用供应商S1所供应零件的工程号码：

   • SQL：

     SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';
     

   • 结果：

     

4. 找出工程项目J2使用的各种零件的名称及其数量：

   • SQL：

     SELECT DISTINCT PNAME,QTY FROM P,SPJ WHERE 
     SPJ.JNO='J2' AND P.PNO=SPJ.PNO;
     

   • 结果：

     

5. 找出上海厂商供应的所有零件号码：

   • SQL：

     SELECT DISTINCT PNO FROM S,SPJ WHERE
     S.CITY='上海' AND S.SNO=SPJ.SNO;
     

   • 结果：

     

6. 找出使用上海产的零件的工程名称：

   • SQL：

     SELECT DISTINCT JNO FROM S,SPJ WHERE
     S.CITY='上海' AND S.SNO=SPJ.SNO;
     

   • 结果：

     

7. 找出没有使用天津产的零件的工程号码：

   • SQL：

     SELECT JNO FROM J WHERE JNO NOT IN
     (SELECT JNO FROM S,SPJ WHERE
     	S.CITY='天津' AND S.SNO=SPJ.SNO);
     

   • 结果：

     

8. 把全部红色零件的颜色改成蓝色：

   • SQL：

     UPDATE P SET COLOR='蓝' WHERE COLOR='红';
     SELECT * FROM P; -- 用于显示结果
     

   • 结果：

     

9. 由S5供给J4的零件P6改为由S3供应，请作必要的修改：

   • SQL：

     UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
     SELECT * FROM SPJ; -- 用于显示结果
     

   • 结果：

     

10. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录：

    因为表SPJ的外键SNO是表S的主键，故要先从表SPJ中删除S2的记录，在从表S中删除S2的记录

    • SQL：

      DELETE FROM SPJ WHERE SNO='S2';
      DELETE FROM S WHERE SNO='S2';
      

    • 结果：

      • 表SPJ：

        

      • 表S：

        

11. 请将（S2，J6，P4，200）插入供应情况关系：

    因为表SPJ的外键SNO是表S的主键，故在表SPJ插入关于S2的记录前，要先在表S中还原S2的信息

    • SQL：

      INSERT INTO S VALUES('S2','盛锡','10','北京');
      INSERT INTO SPJ VALUES('S2','P6','J6',200);
      

    • 结果：

      • 表S：

        

      • 表SPJ：

        

第9题

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

• SQL:

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

• 在“视图”中查看结果：

  

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

1. 找出三建工程项目使用的各种零件代码及其数量：

   • SQL：

     SELECT DISTINCT PNO,QTY FROM SANJIAN;
     

   • 结果：

     

2. 找出供应商S1的供应情况。

   • SQL：

     SELECT * FROM SANJIAN WHERE SNO='S1';
     

   • 结果：

     

实验心得

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

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

• 二是使用INSERT语句插入、UPDATE语句更新数据时注意字段在表中顺序。

• 三是要注意数据库完整性，包括：

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