本文提供了所有的查询操作来显示出操作前后数据库中表的改变, 插入的数据保证了每一个题目有输出结果
学生选课数据库
-- 创建数据库
CREATE DATABASE XSXK学生选课相关表操作
学生表
-- 建表
CREATE TABLE student(
sno CHAR(10) PRIMARY KEY,
sname VARCHAR(8) NOT NULL,
ssex CHAR(2) CHECK(ssex IN('男','女')) DEFAULT('男') NOT NULL,
sage SMALLINT CHECK(sage > 18),
sdept VARCHAR(30),
stel VARCHAR(13)
)
-- 添加记录
INSERT INTO student
VALUES
('2024010001','张三','男',20,'数学与计算机科学学院','11111111111'),
('2024010002','李四','男',25,'马克思主义学院','22222222222'),
('2024010003','王五','男',29,'数学与计算机科学学院','33333333333'),
('2024010004','张三','女',23,'数学与计算机科学学院','44444444444'),
('2024010005','赵七','男',22,'材料科学与工程学院','55555555555'),
('2024010006','王华','女',20,'数学与计算机科学学院','66666666666'),
('2024010007','老八','男',19,'管理学院','77777777777'),
('2024010008','李萍','女',22,'外语学院','88888888888'),
('2024010009','十号','女',20,'数学与计算机科学学院','99999999999'),
('2024010010','十一号','男',19,'管理学院','00000000000'),
('2100001','十一号','男',19,'管理学院','123123123123')
-- 查询表
SELECT * FROM student
-- 删除所有信息
DELETE FROM student WHERE sno = sno课程表
-- 建表
CREATE TABLE Course(
cno CHAR(10) PRIMARY KEY,
cname VARCHAR(16) NOT NULL,
ccredit SMALLINT NOT NULL,
cpno VARCHAR(10)
)
-- 添加记录
INSERT INTO Course
VALUES
('01','数据库原理与应用',3,'10'),
('02','JAVA',4,'10'),
('03','数据结构',5,'9'),
('04','计算机组成原理',3,'9'),
('05','计算机网络',4,'9'),
('06','高等数学下',5,'07'),
('07','高等数学上',5,NULL),
('08','线性代数',3,NULL),
('09','离散数学',3,'10'),
('10','C语言',3,'06')
-- 查询表
SELECT * FROM Course
-- 删除所有信息
DELETE FROM Course WHERE cno = cno选课表
-- 建表
CREATE TABLE SC(
sno CHAR(10),
cno CHAR(10),
grade SMALLINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE,
FOREIGN KEY (cno) REFERENCES Course(cno) ON DELETE CASCADE
)
-- 添加记录
INSERT INTO SC
VALUES
('2024010001','01',65),
('2024010001','02',88),
('2024010001','03',89),
('2024010001','04',65),
('2024010001','05',77),
('2024010001','06',65),
('2024010001','07',66),
('2024010001','08',95),
('2024010001','09',68),
('2024010001','10',87),
('2024010002','01',80),
('2024010002','03',2),
('2024010002','09',85),
('2024010004','01',15),
('2024010004','06',91),
('2024010005','03',100),
('2024010005','06',95),
('2024010005','07',70),
('2024010006','01',88),
('2024010006','08',80),
('2024010007','02',NULL),
('2024010007','09',NULL),
('2024010008','02',80),
('2024010009','05',27),
('2024010010','10',89),
('2100001','05',74)
-- 查询表
SELECT * FROM SC
-- 删除所有信息
DELETE FROM SC WHERE sno = sno删除和修改示例
1.对于每个学生,求学生的选课门数和平均成绩,并把结果存入学生选课数据库中
-- 创建一个学生平均成绩表, 列包括学生学号, 学生选课门数, 学生平均成绩
CREATE TABLE avg_grade(
sno CHAR(10) PRIMARY KEY,
scnumber SMALLINT,
ag SMALLINT
)
-- 插入数据
INSERT INTO avg_grade
SELECT s.sno, COUNT(*), AVG(grade)
FROM student s
LEFT JOIN SC ON s.sno = SC.sno
LEFT JOIN Course c ON SC.cno = c.cno
GROUP BY s.sno
-- 查询结果
SELECT * FROM avg_grade2.将"数据库原理与应用"的学分修改为4
-- 修改
UPDATE Course SET ccredit = 4
WHERE cname='数据库原理与应用'3.将王华同学选修的数据库原理与应用课程的成绩增加五分
-- 修改
UPDATE SC
SET grade = grade + 5
WHERE sno IN(SELECT sno FROM student
WHERE sname='王华')
AND cno IN(SELECT cno FROM Course
WHERE cname='数据库原理与应用')
SELECT sno FROM student
WHERE sname='王华'
SELECT cno FROM Course
WHERE cname='数据库原理与应用'4.将管理学院学生选课成绩置为空
-- 修改
UPDATE SC SET grade = NULL
WHERE sno IN(SELECT sno FROM student
WHERE sdept='管理学院')
-- 查询管理学院学生选课及成绩
SELECT *
FROM SC
WHERE sno IN(SELECT sno FROM student
WHERE sdept='管理学院')5.删除选课成绩中低于40分的记录
-- 删除
DELETE FROM SC
WHERE grade < 40
-- 查询选课成绩中低于40分的记录
SELECT * FROM SC
WHERE grade < 406.删除所有选修"JAVA"的选课记录
-- 删除
DELETE FROM SC
WHERE cno IN(SELECT cno FROM Course
WHERE cname='JAVA')
-- 查询所有选修"JAVA"的选课记录
SELECT * FROM SC
WHERE cno IN(SELECT cno FROM Course
WHERE cname='JAVA')7.删除学生李萍所有的选课记录
-- 删除
DELETE FROM SC
WHERE sno IN(SELECT sno FROM student
WHERE sname='李萍')
-- 查询学生李萍所有的选课记录
SELECT * FROM SC
WHERE sno IN(SELECT sno FROM student
WHERE sname='李萍')8.删除学生学号为'2100001'的学生记录
-- 删除
DELETE FROM student
WHERE sno='2100001'
-- 查询学生学号为'2100001'的学生记录
SELECT * FROM student
WHERE sno='2100001'