MySQL最终实验

学生成绩管理系统数据库

1.物理结构设计与实施

/*创建数据库*/
CREATE DATABASE EndTerm;
USE EndTerm;

/*创建数据表*/

/*x学生信息表*/
CREATE TABLE StudentInformation(
sno CHAR(8) PRIMARY KEY,
sname NVARCHAR(10) NOT NULL,
ssex CHAR(2) NULL);

/*教师信息表*/
CREATE TABLE TeacherInformation(
tno CHAR(7) PRIMARY KEY,
tname NVARCHAR(10) NOT NULL,
tsex CHAR(2) NULL,
tdepartment CHAR(12) NULL);

/*专业信息表*/
CREATE TABLE ProfessionInformation(
pid CHAR(2) PRIMARY KEY,
pname NVARCHAR(14) NULL);

/*专业信息表*/
CREATE TABLE ClassInformation(
clno CHAR(6) PRIMARY KEY,
cltno CHAR(7) NOT NULL,
clpid CHAR(2) NOT NULL,
FOREIGN KEY(cltno) REFERENCES TeacherInformation(tno),
FOREIGN KEY(clpid) REFERENCES ProfessionInformation(pid)
);

/*课程信息表*/
CREATE TABLE CourseInformation(
cid CHAR(9) PRIMARY KEY,
cname NVARCHAR(20) NOT NULL,
cterm TINYINT NULL,
cpid CHAR(2) NOT NULL,
cgrade CHAR(4) NOT NULL,
FOREIGN KEY(cpid) REFERENCES ProfessionInformation(pid));

/*用户信息表*/
CREATE TABLE UserInformation(
uid INT PRIMARY KEY,
uname CHAR(8) NOT NULL,
upassword CHAR(6) NOT NULL,
urolename CHAR(6) NOT NULL DEFAULT '学生',
CONSTRAINT CHK_Role CHECK (URoleName IN ('管理员', '任课教师', '班主任', '学生'))  
);

/*教师任课表*/
CREATE TABLE TeacherCourse (  
    TCID INT PRIMARY KEY,  
    TCClass CHAR(6) NOT NULL,  
    TCCID CHAR(9) NOT NULL,  
    TCTNo CHAR(7) NOT NULL,  
    FOREIGN KEY (TCClass) REFERENCES ClassInformation(CLNO),  
    FOREIGN KEY (TCCID) REFERENCES CourseInformation(CID),  
    FOREIGN KEY (TCTNo) REFERENCES TeacherInformation(TNo)  
);

/*学生信息表*/
CREATE TABLE StudentScore (  
    SSID INT PRIMARY KEY,  
    SSNo CHAR(8) NULL,  
    SSCID CHAR(9) NULL,  
    SSScore TINYINT NULL,  
    FOREIGN KEY (SSNo) REFERENCES StudentInformation(SNo),  
    FOREIGN KEY (SSCID) REFERENCES CourseInformation(CID)  
);

/*插入表格信息*/
INSERT INTO StudentInformation VALUES('19051102','龙宇','男');
INSERT INTO StudentInformation VALUES('19051104','张龙','男');
INSERT INTO StudentInformation VALUES('19051105','李枫娅','女');
INSERT INTO StudentInformation VALUES('19051108','叶海文','男');
INSERT INTO StudentInformation VALUES('19051114','王理坤','男');
INSERT INTO StudentInformation VALUES('19142420','秦浩','男');
INSERT INTO StudentInformation VALUES('19140427','崔艺琼','女');

INSERT INTO TeacherInformation VALUES('2','刘磊','男','大数据与人工智能学院');
INSERT INTO TeacherInformation VALUES('5','齐礼良','男','大数据与人工智能学院');
INSERT INTO TeacherInformation VALUES('8','张雪','女','大数据与人工智能学院');
INSERT INTO TeacherInformation VALUES('9','张翔','男','大数据与人工智能学院');
INSERT INTO TeacherInformation VALUES('10','尹光花','女','大数据与人工智能学院');


INSERT INTO ClassInformation VALUES('1','8','1');
INSERT INTO ClassInformation VALUES('2','5','1');
INSERT INTO ClassInformation VALUES('3','2','2');
INSERT INTO ClassInformation VALUES('4','10','3');

SELECT *FROM classinformation;

INSERT INTO ProfessionInformation  VALUES('1','计算机科学与技术');
INSERT INTO ProfessionInformation  VALUES('2','大数据与数据科学');
INSERT INTO ProfessionInformation  VALUES('3','物联网');
INSERT INTO ProfessionInformation  VALUES('4','人工智能');

INSERT INTO CourseInformation VALUES('11','操作系统',3,'1','191');
INSERT INTO CourseInformation VALUES('12','C++',3,'1','191');
INSERT INTO CourseInformation VALUES('13','数据库',4,'1','191');
INSERT INTO CourseInformation VALUES('14','JAVA程序设计',3,'1','191');

INSERT INTO UserInformation VALUES(4,'曹操','111','');
INSERT INTO UserInformation VALUES(7,'刘备','222','管理员');
INSERT INTO UserInformation VALUES(10,'孙权','772','班主任');
INSERT INTO UserInformation VALUES(11,'陆逊','211','');
INSERT INTO UserInformation VALUES(12,'陆逊','211','');
INSERT INTO UserInformation VALUES(13,'陆逊','211','');

INSERT INTO TeacherCourse VALUES(1,'1','12','5');
INSERT INTO TeacherCourse VALUES(2,'2','11','2');

INSERT INTO StudentScore VALUES(1,'19051102','11',88);
INSERT INTO StudentScore VALUES(2,'19051104','12',65);
INSERT INTO StudentScore VALUES(3,'19051108','13',87);

2.实现数据的更新操作（即插入数据），做一些查询操作和数据修改操作。

/*（1）	将CourseInformation表中的课程“数据库”的课程名更改为“数据库原理及应用”，开设学期改为2。*/
UPDATE CourseInformation
SET CName='数据库原理及应用',CTerm=2
WHERE CName='数据库';

SELECT *FROM CourseInformation;

/*（2）	查询StudentScore表中成绩高于80分的学生信息。*/
SELECT * FROM StudentScore WHERE SSScore > 80;

/*（3）	将计算机科学与技术专业删除，行则截图，如果不行请说明原因。*/
/*外键约束存在，无法删除*/
DELETE FROM ProfessionInformation WHERE  PName='计算机科学与技术';

/*（4）	查询学生总人数。*/
SELECT COUNT(*) FROM Studentinformation;

/*（5）	删除选修了12号课程的学生信息。 */
DELETE FROM studentscore WHERE sscid = 12;

SELECT *FROM  studentscore WHERE sscid = 12;

查询为空

3.利用视图管理知识（创建视图、更新视图）等基本操作。

/*（1）	建立女学生信息的视图，视图名为stu_nv。*/
CREATE VIEW stu_nv AS  
SELECT * FROM Studentinformation  
WHERE ssex = '女';

SELECT * FROM stu_nv;

/*（2）	将学生为女生的学生视图stu_nv中的学号为‘19051105’的学生姓名改为‘张倩’。*/
UPDATE stu_nv  
SET sname = '张倩'  
WHERE sno = '19051105';

SELECT * FROM stu_nv;

/*（3）删除视图stu_nv。*/
DROP VIEW stu_nv;