实验三 数据操纵

Student(stuno,stuname,stusex,sbirthday,dno,classno,tel,native,address)
Course(couno,couname,semester,credit,lecture,experiment)
Sc(stuno,couno,grade)
Dept(dno,dname,dchair,daddress,tel)

目的

掌握如何向表中添加、修改、删除数据。

内容

1. 向student表添加1条记录（‘101’，‘张三’ ，‘男’，2000-9-9，‘计算机学院’）

2. 删除student表中所有姓‘刘’且年龄大于20的学生记录

3. 将‘计算机学院’学生的成绩都增加10分

4. 将‘数据库’这门课的学生成绩置为0

5. 删除和‘张三’同龄的学生信息

6. INSERT
   INTO student(stuno, stuname, stusex, sbirthday, native)
   VALUES('101', '张三', '男', '2000-9-9', '计算机学院')
   

7. DELETE
   FROM student
   WHERE stuname LIKE '刘%' AND DATEDIFF(YEAR, sbirthday, GETDATE()) > 20
   

8. UPDATE sc
   SET grade = grade + 10
   WHERE stuno = 
   (
       SELECT stuno
       FROM student
       WHERE native = '计算机学院'
   )
   

9. UPDATE sc
   SET grade = 0
   WHERE couno = 
   (
       SELECT couno
       FROM couse
       WHERE couname = '数据库'
   )
   

10. DELETE
    FROM student
    WHERE DATEDIFF(YEAR, sbirthday, GETDATE()) = 
    (
        SELECT DATEDIFF(YEAR, sbirthday, GETDATE())
        FROM student
        WHERE stuname = '张三'
    ) AND stuname <> '张三'