数据库更新语句操作实例

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’