SQL 基础部分实验

一、实验目的

1. 熟悉 MySQL8.0 操作环境或者其它 RDBMS；理解关系数据库中的数据对象、数据操作以及完整性约束的概念；
2. 熟悉 SQL 语法，完成 SQL 创建数据对象、数据更新、数据查询等操作。
3. 理解数据库安全性和完整性概念，能够创建用户并权限，添加完整性约束。

二、实验内容

1、以图形化界面方式创建选修关系数据库（3.2 节）

包括 student, sc,以及 course 三张表，并操作向其插入数据、删除数据、修改数据，然后删除表。创建表时，要求定义其主码、外码约束，同时要求学生的年龄在 15 到 45岁之间。注：将自己的表保存到自己定义的数据库中。找到 MySQL 中数据文件的保存位置，自学数据文件备份或者其他数据备份方式。

创建代码：

CREATE DEFINER=`root`@`localhost` PROCEDURE `creat`()
BEGIN
CREATE TABLE SC(
	Sno CHAR(10),
	Cno CHAR(4),
	Grade INT,
	PRIMARY KEY (Sno, Cno),			-- 表级完整性约束， 定义主码与外码
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
CREATE TABLE Student (
	Sno CHAR(10) PRIMARY KEY,	-- 表的列级完整性约束
	Sname CHAR(20) UNIQUE,	-- Sname 取唯一值
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)	
);

CREATE TABLE Course(
	Cno Char(4)	PRIMARY KEY,
	Cname CHAR(40) NOT NULL,
	Cpno Char(4),
	Ccredit INT,
	FOREIGN KEY (Cpno) REFERENCES Course(Cno)	-- 表级完整性约束， 自身的先修后修之间的约束关系
);
END

插入代码：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert`()
BEGIN
SELECT * FROM Course;	
SELECT * FROM SC;
SELECT * FROM Student;	

INSERT INTO Course VALUES('2', '数学', null, 2);
INSERT INTO Course VALUES('6', '数据处理', null, 2);
INSERT INTO Course VALUES('4', '操作系统', '6', 3);
INSERT INTO Course VALUES('7', 'PASCAL语言', '6', 4);
INSERT INTO Course VALUES('5', '数据结构', '7', 4);
INSERT INTO Course VALUES('1', '数据库', '5', 4);
INSERT INTO Course VALUES('3', '信息系统', '1', 4);

INSERT INTO text1.SC(Sno,Cno,Grade) VALUES('201215121', '1', 92);
INSERT INTO text1.SC(Sno,Cno,Grade) VALUES('201215121', '2', 85);
INSERT INTO text1.SC(Sno,Cno,Grade) VALUES('201215121', '3', 88);
INSERT INTO text1.SC(Sno,Cno,Grade) VALUES('201215122', '2', 90);
INSERT INTO text1.SC(Sno,Cno,Grade) VALUES('201215122', '3', 80);

INSERT INTO text1.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215121', '李勇', '男',20, 'CS');
INSERT INTO text1.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215122', '刘晨','女',19,'CS');
INSERT INTO text1.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215123', '王敏','女',18,'MA');
INSERT INTO text1.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215125', '张立','男',19,'IS');

END

插入完成之后的三个表如下图所示：
SC：

Student：

Course：

修改数据：

在Student中增加了一列Scome

CREATE DEFINER=`root`@`localhost` PROCEDURE `alter`()
BEGIN
ALTER TABLE text1.Student ADD Scome DATE;
END

增加后的Student如图所示：

2、用 SQL 语言完成第五版书上 P130 页作业 4，5 和 9 题。

创建 SPJ、 S、P、J 四张表，最好放在一个 DB 中，便于备份数据文件。定义表3时需要定义相应的完整性约束（至少主码，有外码则定义外码，同时要求 S 表的 STATUS 只能取值为 10,20,30,40；J 表中的 Jname 取值唯一，SPJ 表中的 QTY 不为空。）

创建和插入数据：

CREATE DEFINER=`root`@`localhost` PROCEDURE `spj`()
BEGIN
-- 先定义 S 表
CREATE TABLE S(
	Sno CHAR(10) PRIMARY KEY, 
	Sname CHAR(10), 
	Sstatus int CHECK (Sstatus in (10,20,30,40)), 
	Scity CHAR(10)
);
INSERT INTO S(Sno, Sname, Sstatus, Scity) VALUES('S1', '精益', 20, '天津');
INSERT INTO S(Sno, Sname, Sstatus, Scity) VALUES('S2', '盛锡', 10, '北京');
INSERT INTO S(Sno, Sname, Sstatus, Scity) VALUES('S3', '东方红', 30, '北京');
INSERT INTO S(Sno, Sname, Sstatus, Scity) VALUES('S4', '丰泰盛', 20, '天津');
INSERT INTO S(Sno, Sname, Sstatus, Scity) VALUES('S5', '为民', 30, '上海');

-- 定义 P 表
CREATE TABLE P(
	Pno CHAR(10) PRIMARY KEY,
	Pname CHAR(10),
	Pcolor CHAR(10),
	Pweight INT
);
INSERT INTO P(Pno, Pname, Pcolor, Pweight) VALUES('P1', '螺母', '红', 12);
INSERT INTO P(Pno, Pname, Pcolor, Pweight) VALUES('P2', '螺栓', '绿', 17);
INSERT INTO P(Pno, Pname, Pcolor, Pweight) VALUES('P3', '螺丝刀', '蓝', 14);
INSERT INTO P(Pno, Pname, Pcolor, Pweight) VALUES('P4', '螺丝刀', '红', 14);
INSERT INTO P(Pno, Pname, Pcolor, Pweight) VALUES('P5', '凸轮', '蓝', 40);
INSERT INTO P(Pno, Pname, Pcolor, Pweight) VALUES('P6', '齿轮', '红', 30);
SELECT * FROM P;
-- 定义 J 表
CREATE TABLE J(
	Jno CHAR(10) PRIMARY KEY, 
	Jname CHAR(10) UNIQUE, 
	Jcity CHAR(10)
);
INSERT INTO J(Jno, Jname, Jcity) VALUES('J1', '三建','北京');
INSERT INTO J(Jno, Jname, Jcity) VALUES('J2', '一汽','长春');
INSERT INTO J(Jno, Jname, Jcity) VALUES('J3', '弹簧厂','天津');
INSERT INTO J(Jno, Jname, Jcity) VALUES('J4', '造船厂','天津');
INSERT INTO J(Jno, Jname, Jcity) VALUES('J5', '机车厂','唐山');
INSERT INTO J(Jno, Jname, Jcity) VALUES('J6', '无线电厂','常州');
INSERT INTO J(Jno, Jname, Jcity) VALUES('J7', '半导体厂','南京');
SELECT * FROM J;
-- 定义 SPJ 表
CREATE TABLE SPJ(
	Sno CHAR(10),
	Pno CHAR(10),
	Jno CHAR(10),
	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)
);
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);

SELECT * FROM S;


END

4个表建立好后如下图所示：
S表：

P表：

J表：

SPJ表：

书中第5题的相关操作

CREATE DEFINER=`root`@`localhost` PROCEDURE `spj-5`()
BEGIN
-- 找出所有供应商的姓名和所在城市
	SELECT SNAME,scity FROM s;
-- 找出所有零件的名称，颜色，重量
	SELECT PNAME,PCOLOR,PWEIGHT FROM P;
-- 找出使用供应商S1所供应零件的工件号码
	SELECT JNO FROM SPJ WHERE SNO = 'S1';
-- 找出工程项目J2使用的各种零件的名称及其数量
	SELECT p.Pname,SPJ.QTY FROM P,SPJ WHERE P.Pno=SPJ.Pno AND SPJ.Jno='J2';
-- 找出上海产的零件的工程名称
	SELECT JNAME FROM J,S,SPJ 
	WHERE J.Jno=SPJ.Jno AND SPJ.Jno=j.Jno AND SPJ.Sno=S.Sno AND S.Scity='上海';
-- 找出没有使用天津产的零件的工程号码
	SELECT JNO FROM J WHERE NOT EXISTS 
	(SELECT * FROM SPJ,S WHERE SPJ.Sno=S.Sno AND SPJ.Jno=J.Jno AND S.Scity='天津');
-- 把全部红色零件的颜色改为蓝色
	UPDATE p SET PCOLOR = '红';
-- 由S5供给j4的零件由P6改为由S3供应，请作必要的修改
	UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
-- 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录
	DELETE FROM SPJ WHERE SNO='S2';
	DELETE FROM S WHERE SNO='S2'; 
--  注意删除顺序，先从spj中删除供应商s2所供应的零件的记录，再从S表中删除S2
-- 请将(S2,J9,P4,200)插入供应情况关系
	INSERT INTO SPJ(SNO,JNO,PNO,QTY) VALUES (S2,J9,P4,200);

END

书中第9题的相关操作

CREATE DEFINER=`root`@`localhost` PROCEDURE `SPJ-9`()
BEGIN
-- 创建视图
CREATE VIEW V_SPJ AS SELECT SNO,PNO,QTY FROM SPJ WHERE JNO=(SELECT JNO FROM J WHERE JNAME='三建');
-- 查看
SELECT PNO,QTY FROM v_SPJ;
END