数据库系统概论——交互式SQL

注：本人电脑安装的是MySQL5.7，还有一个编辑器Beekeeper-Studio。网络上其他平台或文章有相应的安装教程，都比较简单，在此不做赘述。

例题：

1. 用SQL语句：创建数据库、创建表、将数据添加到表中

首先创建数据库，我们将其命名为 SECONDTRY：

CREATE DATABASE SECONDTRY;

 接着创建 S, P, J 以及 SPJ 四个表：

CREATE TABLE S
(Sno CHAR(10) UNIQUE,
Sname CHAR(10),
Status INT,
City CHAR(10)
);

CREATE TABLE P
(Pno CHAR(10) UNIQUE,
Pname CHAR(10),
Color CHAR(5),
Weight INT
);

CREATE TABLE J
(Jno CHAR(10) UNIQUE,
Jname CHAR(10),
City CHAR(10)
);

CREATE TABLE SPJ
(Sno CHAR(10),
Pno CHAR(10),
Jno CHAR(10),
QTY INT
);

 

最后将数据添加到表中：

INSERT INTO S
VALUES('S1','精益',20,'天津');

INSERT INTO S
VALUES('S2','盛锡',10,'北京');

INSERT INTO S
VALUES('S3','东方红',20,'北京');

INSERT INTO S
VALUES('S4','丰泰盛',20,'天津');

INSERT INTO S
VALUES('S5','为民',20,'上海');

 

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

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

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('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. 执行SQL语句

(1) 找出所有供应商的姓名和所在城市；

SELECT Sname, City FROM S;

 

(2) 找出所有零件的名称，颜色，重量；

SELECT Pname, Color, Weight FROM P;

 

(3) 找出使用供应商S1所供应零件的工程号码；

SELECT Jno FROM SPJ WHERE Sno = 'S1';

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

SELECT Pname, QTY FROM P, SPJ WHERE SPJ.Pno = P.Pno AND Jno = 'J2';

 

(5) 找出上海厂商供应的所有零件号码；

SELECT Pno FROM S, SPJ WHERE SPJ.Sno = S.Sno AND City = '上海';

(6) 找出上海产的零件的工程名称；

SELECT Jname FROM J, S, SPJ WHERE J.Jno = SPJ.Jno AND SPJ.Sno = S.Sno AND S.City = '上海';

 

(7) 找出没有使用天津产的零件的工程号码；

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

 

(8) 把全部红色零件颜色改成蓝色；

UPDATE P SET Color = '蓝' WHERE Color = '红';

 

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

UPDATE SPJ SET Sno = 'S3' WHERE Pno = 'P6' AND Jno = 'J4' AND Sno = 'S5';

 

(10) 从供应商关系中删除S2的记录，并从供应情况中删除相应的记录；

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

 

(11) 请将 (S2, J6, P4, 200) 插入供应关系情况；

INSERT INTO SPJ VALUES('S2','P4','J6',200);