背景知识： 一、插入：对表进行插入操作，insert into table_name（属性列）values（插入值），一定要注意属性列和插入值的顺序要一样 二、语法记忆：update、delete、insert都不改变表的结构，所以只需要加上table_name就可以，而drop、create,alter都改变了表的结构，所以需要加上table，例如drop table table_name

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

select * from sc select * from student select * from course

–插入新的学生李一和李二 insert into student(sno,sname,sex,dno,BIRTHDAY) values(‘20069011’,‘李一’,‘男’,‘0001’,‘1985-01-02’) select * from student where sname = ‘李一’ insert into student(sno,sname,sex,dno,BIRTHDAY) values(‘20069012’,‘李二’,‘女’,‘0002’,‘1986-01-02’) select * from student where sname = ‘李二’

–创建新表 CREATE TABLE chengjiao ( SNO char (8) not null unique, SNAME char(10), SEX char(2), DNO char(8), AGE smallint, BIRTHDAY datetime )

–插入新学生张三、王二、张三 INSERT INTO student(SNO,SNAME,AGE,DNO) VALUES (‘20067027’,‘张三’,20,‘0002’) INSERT INTO chengjiao(SNO,SNAME,AGE,DNO) VALUES (‘20067011’,‘王二’,23,‘0003’) INSERT INTO chengjiao(SNO,SNAME,AGE,DNO) VALUES (‘20067021’,‘张三’,19,‘0003’) select * from student where sno = ‘20067027’ union select * from student where sno = ‘20067011’ union select * from student where sno = ‘20067021’

–将成教表 chengjiao 中的所有学生一次性添加到学生表 student 中。 insert into student(sno,sname,sex,dno,age,birthday) (select sno,sname,sex,dno,age,birthday from chengjiao) select * from chengjiao

–依据学生的生日，计算出该学生的年龄 update student set age = (year(getdate()) - year(birthday))

–将所有安排在 A209 的课程调整到 D109 update course set room = ‘D109’ where room = ‘A209’

–将选课表中的‘线性代数’课程的成绩减去 4 分 update sc set grade = grade-4 where cno in (select cno from course where cname = ‘线性代数’)

–从排课表中删除‘杨丽’老师的所有排课纪录 delete from course where tname = ‘杨丽’

–删除学院编号为空的学生记录及选课记录，判断空不要使用=null，因为出来的结果未必只有一个，=只适用于一个值的情况 delete from sc where sno in (select sno from student where dno is null)

–删除表 ’excelxuanke’ drop table excelxuanke

–（1）在选课表中插入一个新的选课记录，学号为 20002059，授课班号为 244501，成绩 80分。 insert into sc(sno,cno,grade) values(‘20002059’,‘244501’,80)

–（2）从选课表中删除选修‘线性代数’的选修纪录 delete from sc where cno in (select cno from course where cname = ‘线性代数’)

–（3）将机电学院的女生一次性添加到成教表中 insert into chengjiao(sno,sname,sex,dno,age,birthday) (select sno,sname,sex,dno,age,birthday from student where dno=‘0001’)

–（4）将所有学生的高等数学成绩加５分 update sc set grade = grade + 5 where cno in (select cno from course where cname = ‘高等数学’)

–（5）将学号尾数为‘4’的同学成绩加 2 update sc set grade = grade + 2 where sno like ‘%4’

–（6）删除电科系所有学生的选课记录 delete from sc where sno in (select * from student where dno = ‘0001’)

–（7）将学号为“20002059”的学生姓名改为“王菲” update student set sname = ‘王菲’ where sno = ‘20002059’

–（8）删除成绩为空的选课记录 delete from sc where grade is null