注:本人电脑安装的是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);