2024/6/6

所学时间：2小时

代码行数：70

博客园数：1篇

所学知识：

 

create table student(
sno CHAR(8) NOT NULL PRIMARY KEY ,--学号
sname VARCHAR(20) ,--姓名
sex CHAR(2) ,--性别
sdept VARCHAR(20) --所在系
);

create table course(
cno CHAR(6) NOT NULL primary key,--课程号
cname varchar(20) --课程名
);

create table sc(
sno char(8) not null references student(sno),--学号
cno char(6) not null primary key(sno,cno) references course(cno),--课程号
grade int not null --成绩
);

insert into student values('20050101','王飞','男','计算机系');
insert into student values('20050102','李丽','女','信息系');

insert into course values('01','数据结构');
insert into course values('02','数据库原理');

insert into sc values('20050101','01',70);
insert into sc values('20050101','02',80);
insert into sc values('20050102','01',90);
insert into sc values('20050102','02',70);

CREATE TABLE average (
sno INT PRIMARY KEY,
average_grade FLOAT
);

INSERT INTO average(sno, average_grade)
SELECT sno, AVG(grade) AS average_grade FROM sc GROUP BY sno;

UPDATE sc SET grade = 85 WHERE sno = 20050102 AND cno = 02;

DELETE FROM sc WHERE sno = 20050102 AND cno = 02;

UPDATE sc SET grade = 0 WHERE cno = 02;

DELETE FROM sc WHERE sno = (SELECT sno FROM student WHERE sname = '李丽');

DELETE FROM sc; DELETE FROM student; DELETE FROM course;

DROP TABLE sc; DROP TABLE student; DROP TABLE course;

DROP DATABASE stumanage;