SQL Test

发表于2020-06-16更新于2020-08-18字数统计571阅读时长6分阅读次数

TABLE Student

1
2
3
4
5
CREATE TABLE Student_2018091601004(
	StudentID char(13) PRIMARY KEY,
	StudentName varchar(10) NOT NULL,
	Sex char(2) NOT NULL
);

TABLE Teacher

1
2
3
4
5
CREATE TABLE Teacher_2018091601004(
	TeacherID char(7) PRIMARY KEY,
	TeacherName varchar(10) NOT NULL,
	Title varchar(10) NOT NULL CHECK(Title IN('教授','副教授','讲师'))
);

TABLE Course

1
2
3
4
5
6
7
8
9
10
CREATE TABLE Course_2018091601004(
	CourseID char(10) PRIMARY KEY,
	CourseName varchar(20) NOT NULL,
	TeacherID char(7) NOT NULL,
	Credit int NOT NULL CHECK(Credit IN(1,2,3,4)),
	CONSTRAINT TeacherID_FK FOREIGN KEY(TeacherID)
	REFERENCES Teacher_2018091601004(TeacherID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

TABLE Grade

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE Grade_2018091601004(
	StudentID char(13) NOT NULL,
	CourseID char(10) NOT NULL,
	Score int NOT NULL CHECK(Score>=0 and Score<=100),
	CONSTRAINT Grade_PK PRIMARY KEY(StudentID,CourseID),
	CONSTRAINT Student_FK FOREIGN KEY(StudentID)
	REFERENCES Student_2018091601004(StudentID)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	CONSTRAINT CourseID FOREIGN KEY(CourseID)
	REFERENCES Course_2018091601004(CourseID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

INSERT Student

1
2
3
INSERT INTO Student_2018091601004 VALUES('2018090015201','李同学','男');
INSERT INTO Student_2018091601004 VALUES('2018090015202','赵同学','女');
INSERT INTO Student_2018091601004 VALUES('2018091601004','林俊','男');

INSERT Teacher

1
2
3
INSERT INTO Teacher_2018091601004 VALUES('3202211','张老师','教授');
INSERT INTO Teacher_2018091601004 VALUES('3202212','刘老师','副教授');
INSERT INTO Teacher_2018091601004 VALUES('1601004','林俊','讲师');

INSERT Course

1
2
3
INSERT INTO Course_2018091601004 VALUES('C001','数据库原理','3202211',3);
INSERT INTO Course_2018091601004 VALUES('C002','软件工程原理','3202212',3);
INSERT INTO Course_2018091601004 VALUES('C003','林俊','3202212',2);

INSERT Grade

1
2
3
4
5
6
INSERT INTO Grade_2018091601004 VALUES('2018091601004','C001',90);
INSERT INTO Grade_2018091601004 VALUES('2018091601004','C002',80);
INSERT INTO Grade_2018091601004 VALUES('2018090015201','C001',92);
INSERT INTO Grade_2018091601004 VALUES('2018090015201','C002',45);
INSERT INTO Grade_2018091601004 VALUES('2018090015202','C001',56);
INSERT INTO Grade_2018091601004 VALUES('2018090015202','C002',35);

SELECT join on

1
2
3
4
5
SELECT A.StudentID,A.StudentName,B.Score,C.CourseName
FROM Student_2018091601004 as A join Grade_2018091601004 as B 
on A.StudentID=B.StudentID join Course_2018091601004 as C 
on B.CourseID=C.CourseID
WHERE A.StudentID='2018091601004';

CREATE VIEW

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW View_Grade_2018091601004 as
SELECT A.CourseID as 课程编号,
	   A.CourseName as 课程名称,
	   B.TeacherName as 任课教师,
	   count(C.StudentID) as 不及格人数
FROM Course_2018091601004 as A 
	join Teacher_2018091601004 as B on A.TeacherID=B.TeacherID
	join Grade_2018091601004 as C on A.CourseID=C.CourseID
WHERE C.Score<60
GROUP by A.CourseID,B.TeacherName
ORDER by A.CourseID;

SELECT* FROM  View_Grade_2018091601004;

CREATE ROLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE ROLE "Role_Student_2018091601004" WITH LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1;

CREATE ROLE "Role_Teacher_2018091601004" WITH LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1;	

CREATE ROLE "Role_TAdmin_2018091601004" WITH LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1;

GRANT ROLE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
GRANT SELECT,UPDATE ON Student_2018091601004 TO "Role_Student_2018091601004";
GRANT SELECT ON Teacher_2018091601004 TO "Role_Student_2018091601004";
GRANT SELECT ON Course_2018091601004 TO "Role_Student_2018091601004";
GRANT SELECT ON Grade_2018091601004 TO "Role_Student_2018091601004";

GRANT SELECT ON Student_2018091601004 TO "Role_Teacher_2018091601004";
GRANT SELECT,UPDATE ON Teacher_2018091601004 TO "Role_Teacher_2018091601004";
GRANT SELECT ON Course_2018091601004 TO "Role_Teacher_2018091601004";
GRANT SELECT,UPDATE,DELETE,INSERT ON Grade_2018091601004 TO "Role_Teacher_2018091601004";

GRANT SELECT ON Student_2018091601004 TO "Role_TAdmin_2018091601004";
GRANT SELECT ON Teacher_2018091601004 TO "Role_TAdmin_2018091601004";
GRANT SELECT,UPDATE,DELETE,INSERT ON Course_2018091601004 TO "Role_TAdmin_2018091601004";
GRANT SELECT ON Grade_2018091601004 TO "Role_TAdmin_2018091601004";