实验四 数据更新与视图

实验四 数据更新与视图

一、插入一条选课记录（ ‘201215123’,'1 '）。

INSERT
INTO sc
VALUES ('201215123','1',NULL);

二、将一个新学生元组（学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中。

INSERT 
INTO student(sno,sname,ssex,sdept,sage)
VALUES ('201215128','陈冬','男','IS','18');

三、删除学号为201215128的学生记录。

DELETE
FROM student
WHERE sno='201215128';

四、将所有学生的年龄增加1岁。

UPDATE student
SET sage=sage+1;

五、删除计算机科学系所有学生的选课记录。

DELETE 
FROM sc
WHERE sno in
(
SELECT sno
FROM student
WHERE sdept = 'CS'
);

六、将计算机科学系(CS)全体学生的成绩置零。

UPDATE sc
SET grade = 0
WHERE sno in
(
SELECT sno
FROM student
WHERE sdept='CS'
);

七、将学生201215121的年龄改为22岁。

UPDATE student
SET sage = 22
WHERE sno ='201215121';

八、对每一个系，求学生的平均年龄，并把结果存入数据库中的新表dept_age(Sdept,Avg_age)中。

-- 建立表格 ，做题时不需要写
CREATE TABLE `dept_age`(
`Sdept` CHAR(20),
`Avg_age` SMALLINT
);

INSERT 
INTO dept_age(sdept,Avg_age)
SELECT sdept,AVG(sage)
FROM student
GROUP BY sdept;

九、建立信息系（IS）学生的视图（is_student），要求保留学号，姓名，年龄。

CREATE VIEW is_student
AS
SELECT sno,sname,sage
FROM student
WHERE sdept='IS';

十、建立信息系（IS）选修了1号课程的学生的视图（is_s包括学号Sno、姓名Sname、成绩Grade）。

CREATE VIEW is_s(Sno,Sname,Grade)
AS
SELECT student.sno,Sname,Grade
FROM sc,student
WHERE sdept='IS' AND
			student.sno=sc.sno AND
			sc.cno='1';

十一、将学生的学号及平均成绩定义为一个视图（s_g），学号用sno表示，平均成绩用gavg表示。

CREATE VIEW s_g(sno,gavg)
AS
SELECT sno,AVG(grade)
FROM sc
GROUP BY sno;

十二、找出选修1号课程的不及格的学生的学号。（不包含缺考）

SELECT sno
FROM sc
WHERE grade<60 AND cno='1';
-- 因为缺考没有成绩，所以为NULL，这里直接写<60 就可以

十三、选出选修1号课程的不及格的学生以及缺考的学生的学号。

-- 方法一 结果错误
SELECT sno
FROM sc
WHERE grade<60 AND cno='1'
UNION
SELECT sno
FROM sc
WHERE grade IS NULL AND cno='1';
-- 方法二
SELECT sno
FROM sc
WHERE cno='1' AND (grade <60 OR grade IS NULL);