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

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

实验目的

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

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

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

实验工具 MySQL

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

实验流程

1. 建立数据库

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

image-20220416175415036

第4题

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

建表并添加数据

运行以下SQL语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
# 建立供应商表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

  • 零件表P：

    image-20220324233550612

  • 工程项目表J：

    image-20220324233528109

  • 供应情况表SPJ：

    image-20220324233643934

查询

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

    • SQL：

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

    • 结果：

      image-20220325102815380

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

    • SQL：

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

    • 结果：

      image-20220325102913718

  3. 求供应工程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

  4. 求没有使用天津供应商生产的红色零件的工程号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

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

    • SQL：

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      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

第5题

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

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

    • SQL：

      1
      SELECT DISTINCT SNAME,CITY FROM S;

    • 结果：

      image-20220325120743410

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

    • SQL：

      1
      SELECT DISTINCT PNAME,COLOR,WEIGHT FROM P;

    • 结果：

      image-20220325121417842

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

    • SQL：

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

    • 结果：

      image-20220325121452594

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

    • SQL：

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

    • 结果：

      image-20220325121520442

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

    • SQL：

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

    • 结果：

      image-20220325121546585

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

    • SQL：

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

    • 结果：

      image-20220325121633952

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

    • 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

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

    • SQL：

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

    • 结果：

      image-20220325121828186

  9. 由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

  10. 从供应商关系中删除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

      • 表S：

        image-20220325122921502

  11. 请将（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

      • 表SPJ：

        image-20220325123213720

第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

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

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

    • SQL：

      1
      SELECT DISTINCT PNO,QTY FROM SANJIAN;

    • 结果：

      image-20220325164915171

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

    • SQL：

      1
      SELECT * FROM SANJIAN WHERE SNO='S1';

    • 结果：

      image-20220325165039739

实验心得

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

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

image-20220325120605243

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

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

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