SQL Server資料庫更新操作（create,update,delete,insert,drop）

名稱解釋（不區分大小寫）

• student：學生表，屬性分別為sno學號、sname學生姓名、age年齡、sex性別、dno學院編號、birthday生日
• sc選課資訊表：sno學號、cno課程號、grade成績
• dept學院資訊表：dno學院編號、dname學院名稱、dean學院負責人
• course課程資訊表：cno課程號、cname課程名稱、tname老師名稱、credit學分、room教室

下面陳述句可以直接復制到SQL Server運行

方法不唯一，有問題歡迎留言！代碼塊見文章末尾
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

SQL陳述句：

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