数据库实验1.数据库的定义与维护

题目来数据库实验

语言:mysql

编辑器:navicat

 建表语句如下：

CREATE TABLE student (
    sno CHAR(10) PRIMARY KEY,  
    sname CHAR(8),             
    ssex CHAR(2) DEFAULT '男', 
    sage SMALLINT NOT NULL CHECK (sage > 18),  
    sdept CHAR(30) NOT NULL,  
    stel CHAR(13) NOT NULL     
);
CREATE INDEX sname_asc ON student(sname); -- 在学生表的姓名列上创建升序索引


create table Course(
cno char(10) primary key,
cname char(16),
ccredit smallint,
cpno char(10) not null
)
create index cno_ccredit on student(cno,ccredit DESC);
alter table Course add column ctea char(30)

create table SC(
sno char(10),
cno char(10),
grade smallint;
primary key (sno,cno);
foreign key(sno) references student(sno),
foreign key(cno) references Course(cno)
)



insert into student(sno,sname,ssex,sage,sdept,stel) values
('S001', '张三', '男', 20, '计算机系', '13800138001'),
('S002', '李萍', '女', 21, '数学系', '13900139002'),
('S003', '王华', '男', 22, '物理系', '13700137003'),
('S004', '赵六', '男', 19, '化学系', '13600136004'),
('S005', '孙七', '女', 20, '管理系', '13500135005'),
('S006', '周八', '男', 21, '计算机系', '13400134006'),
('S007', '吴九', '女', 23, '数学系', '13300133007'),
('S008', '郑十', '男', 22, '物理系', '13200132008'),
('S009', '陈十一', '女', 20, '化学系', '13100131009'),
('S010', '卫十二', '男', 21, '生物系', '13000130010');
insert into Course (cno, cname, ccredit, cpno, ctea) values
('C001', '数据库原理', 3, 'C002', '张老师'),
('C002', '数据结构', 4, 'C008', '李老师'),
('C003', '操作系统', 4, 'C002', '王老师'),
('C004', '计算机网络', 3, 'C001', '赵老师'),
('C005', '软件工程', 4, 'C003', '孙老师'),
('C006', '算法设计与分析', 3, 'C002', '周老师'),
('C007', '人工智能', 3, 'C006', '吴老师'),
('C008', '离散数学', 3, 'C008', '郑老师'),
('C009', '编译原理', 4, 'C008', '陈老师'),
('C010', '计算机组成原理', 3, 'C003', '卫老师');
insert into sc (sno,cno,grade) values
('S001','C001','86'),
('S002','C002','96'),
('S003','C003','85'),
('S004','C004','46'),
('S005','C005','64'),
('S006','C006','84'),
('S007','C007','89'),
('S008','C008','89'),
('S009','C009','86'),
('S010','C010','46');

1）向学生选课数据库中的每个基本表中录入若干条记录，要求记录满足基本表定义的约

束条件。（学生、课程和选课表中各录入10条以上记录）。

2）通过QueryAnalyzer实现对学生选课数据库的数据增加、数据删除和数据修改操作。

（1）对于每个学生，求学生的选课门数和平均成绩，并把结果存入学生选课数据库中。

（2）将数据库原理与应用课程的学分修改为4。

（3）将“王华”学生选修的数据库原理及应用课程的成绩增加5分。

（4）将管理学院全体学生的成绩置为空。

（5）删除选课表中成绩低于40分的记录。

（6）删除所有选修课程“JAVA”的选课记录。

（7）删除学生李萍的所有选课记录。

（8）删除学号为2100001的学生记录，并讨论该删除操作所受到的约束。

-- 给student新建列来存储选课门数和平均成绩  

alter table student add COLUMN course_count int,
add COLUMN avg_grade FLOAT;

-- 错了错了
UPDATE student SET (course_count,avg_grade)=(
select count(sc.sno) as 选课门数 ,AVG(sc.grade) as 平均成绩
from sc 
where student.sno = sc.sno
GROUP BY sc.sno
)
-- 文心一言 (不懂)
UPDATE student s  
JOIN (  
    SELECT sno, COUNT(*) AS course_count, AVG(grade) AS avg_grade  
    FROM sc  
    GROUP BY sno  
) sc_summary ON s.sno = sc_summary.sno  
SET s.course_count = sc_summary.course_count,  
    s.avg_grade = sc_summary.avg_grade;

-- 创建新表来存储选课门数和平均成绩  
CREATE TABLE student_course_summary (  
    sno char(10) PRIMARY KEY, 
    course_count SMALLINT,  
    avg_grade FLOAT  
);  
  
-- 插入选课门数和平均成绩到新表中  
INSERT INTO student_course_summary(sno, course_count, avg_grade)(
SELECT   
    student.sno as 学号,  
    COUNT(sc.sno) AS 选课门数, 
    AVG(sc.grade) AS 平均成绩  
FROM   
    student
JOIN   
    sc ON student.sno = sc.sno  
GROUP BY   
    student.sno
);
		
-- 将数据库原理与应用课程的学分修改为 4。 
UPDATE course SET ccredit = 4 
where cname = '数据库原理';
-- 将“王华”学生选修的数据库原理及应用课程的成绩增加 5 分。 
UPDATE SC set grade = grade + 5
where sc.cno = (SELECT cno FROM course where cname = '数据库原理')
AND sc.sno = (SELECT sno from student where sname = '王华');
-- 将管理学院全体学生的成绩置为空
UPDATE sc set grade = null
where sc.sno = (select sno from student where sdept = '管理系');

DELETE  from sc where grade < 40;

DELETE from sc where cno = (SELECT cno from course where cname = 'JAVA');

DELETE from sc where sno = (SELECT sno from student where sname = '李萍');


DELETE from sc where sno = 'S001';
DELETE from student where sno = 'S001';