数据库作业3

发表于 | 分类于 ， ， |
字数统计: | 阅读时长 ≈

数据库上机3

还有待改进，下一版请稍等，有问题可以联系本人

上机实验7

代码如下：

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
# 上机实验7.1
-- 16.1
CREATE VIEW Stu_01311_1
  AS SELECT * FROM student WHERE clno = "01311" AND Sno IN
	  (SELECT sno from grade WHERE cno = '1');
			
-- 16.2
CREATE VIEW Stu_01311_2
  AS SELECT * FROM student WHERE clno = "01311" AND Sno IN
	  (SELECT sno from grade WHERE Gmark < 60.0 AND cno = '1');
		
-- 16.3
CREATE VIEW Stu_year
  AS SELECT s.Sno AS '学生学号', s.Sname AS '姓名', (DATE_FORMAT(NOW(), '%Y') - s.Sage) AS '出生年份' FROM student s;

-- 16.4
SELECT 姓名 AS '学生姓名' FROM stu_year WHERE 出生年份 > 199

-- 16.5
SELECT sno AS '学号', Sname AS '姓名', (DATE_FORMAT(NOW(), '%Y') - Sage) AS '出生年份' FROM stu_01311_2;

# 上机实验7.2
CREATE VIEW Class_grade
 AS SELECT S.Clno AS '班级' , cno AS '课程名', AVG(Gmark) as '平均成绩' 
 FROM student S 
      right JOIN grade G on S.sno = G.sno GROUP BY Clno , Cno;
			
UPDATE Class_grade
SET 平均成绩 = 90.0
WHERE 班级 = '03310'; //该表无法update

# 上机实验7.4
-- (1)
CREATE VIEW Course_grade
 AS SELECT cno AS '课程' , AVG(Gmark) as '平均成绩' FROM grade GROUP BY cno;
 
SELECT S.sno AS '学号', S.sname AS '姓名', G.cno AS '课程名', G.gmark - CC.平均成绩
AS '与平均分差值' 
from grade G LEFT JOIN student S ON G.sno = S.sno
             LEFT JOIN course_grade CC ON CC.课程 = G.Cno;


-- (2)
CREATE VIEW Course_classNum
AS SELECT clno,count(Sno) AS Number FROM student GROUP BY Clno;

SELECT Cl.Clno, Cl.Speciality, Cl.Number AS 'Class表人数', CLn.Number AS '实际人数' FROM class Cl 
LEFT JOIN Course_ClassNum Cln on cl.Clno = Cln.Clno;

上机实验8

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
# 上机实验8.2
-- 创建学生表
CREATE TABLE Student(
	 Sno char(7) not null PRIMARY KEY,
	 Sname varchar(20) not null DEFAULT('男') CHECK(Sname IN('男', '女')),
	 Ssex char(2) not null CHECK ( Ssex > 14 AND Ssex < 65 ),
	 Sage SMALLINT CHECK(Sage>14 AND Sage<65),
	 Clno Char(5)not null REFERENCES Class(Clno) ON UPDATE CASCADE ON DELETE RESTRICT
);


-- 创建课程表
CREATE TABLE Course(
		Cno char(1) NOT NULL PRIMARY KEY,
		Cname varchar(20) not null,
		Credit Smallint CHECK ( Credit <= 6 )
);

-- 创建班级表

CREATE TABLE Class(
		Clno Char(5) not null PRIMARY KEY,
		Speciality VARCHAR(20) not null,
		Inyear char(4) not null,
		Number INTEGER CHECK(Number > 1 AND Number < 100),
		Monitor Char(7) REFERENCES Student(Sno) ON UPDATE CASCADE ON DELETE RESTRICT
);


-- 创建成绩表
CREATE table Grade(
	Sno Char(7) not null,
	Cno char(1) not null,
	Gmark Numeric(4,1) CHECK(Gmark > 0 AND Gmark < 100),
	PRIMARY KEY(Sno, Cno),
	FOREIGN KEY (Sno) REFERENCES Student (Sno) ON UPDATE CASCADE,
	FOREIGN KEY (Cno) REFERENCES course (Cno) ON UPDATE CASCADE
);


-- 实验内容
# 触发器为选做

上机实验9

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
# 上机实验9
-- 14.1
GRANT SELECT 
  ON student
	TO ZhangYong
	WITH GRANT OPTION;
GRANT SELECT 
  ON grade
	TO ZhangYong
	WITH GRANT OPTION;
	
-- 14.2
Grant INSERT, SELECT
  ON Student
	TO ZhangSan
	WITH GRANT OPTION;
	
-- 14.3
Grant INSERT, DELETE
  ON Student
	TO Lisi;
	
-- 14.4
Grant ALL PRIVILEGES
  ON Student
	TO LiYong
	WITH GRANT OPTION;
	
-- 14.5
REVOKE ALL PRIVILEGES
 on Student
 FROM ZhangYong;
 
REVOKE ALL PRIVILEGES
 on Course
 FROM ZhangYong;
 
-- 14.6
REVOKE ALL PRIVILEGES
  ON Student
	FROM ZhangSan;