数据库 实验5

use jxgl;
-- 1. 插入单个元组的insert语句
--insert into Table_Name [( 属性 . . . )]values ( . . .)
---a--5.1
insert into Student values ('98041','大哥',25,'男','CS');
---b--5.2
insert into Student(Sno,Sname) values ('98042','二哥');
------5.3 给CS系的学生开设5号课程 建立选课信息
--包含select子查询的insert
insert into SC 
 select sno,cno,null
from Student,Course
where Sdept='CS' and cno='5';

-------5.4 
insert into Student
 select cast( cast(sno as integer) +1  as char(5) ),sname+'2',sage,ssex,sdept 
from Student where Sname='赵三';
---   cast .. as ...类型 转换   integer  char(5)
------update
--  update Table_Name set Column_Name = <  > 
---from   --where 条件查询确定集合

--5.5 将学生98003的年龄改为23岁
update student set sage=23 where sno='98003';
--5.6 将student表的前3位学生的年龄均+1
update student set Student.sage=student.sage+1
from (
select top 3 *
from student order by sno)as stu3
where stu3.sno=student.sno;
--top 3 前3个
--5.7 将98001学生选修3号课程的成绩改为该课的平均成绩
update sc
set grade=( 
select avg(grade) from sc where cno='3'
)
where sno='98001' and cno='3';

--5.8  学生王林在2号课中作弊 该成绩为0
update sc set grade =0
where sno in (select sno from student where sname='王林')     and  cno='2';

--没有where表示对所有的行进行修改

----delete
---delete [from]  Table_Name [where <>];
--5.9 删除计算机系所有学生的选课记录
select * from SC;
delete from sc
where 'CS'=(
select sdept from student
where student.sno=sc.sno
);
select * from sc;
---5.10 使用delete 语句删除sc中的所有数据
delete from sc;
 
INSERT INTO SC VALUES('98001','1',87);
INSERT INTO SC VALUES('98001','2',67);
INSERT INTO SC VALUES('98001','3',90);
INSERT INTO SC VALUES('98002','2',95);
INSERT INTO SC VALUES('98002','3',88);
--备份
Create Table TStudent
( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),
Sname VARCHAR(20),
Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),
Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),
Sdept CHAR(2));
Create Table TCourse 
( Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),
Cname VARCHAR(20),
Cpno CHAR(2),
Ccredit SMALLINT);
Create Table TSC
( Sno CHAR(5) NOT NULL CONSTRAINT S_FF FOREIGN KEY REFERENCES TStudent(Sno),
Cno CHAR(2) NOT NULL,
Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Cno) REFERENCES TCourse(Cno));
 
 
 

--实验5 习题
--1 
insert into student values('98011','张静',27,'女','CS');
insert into student(sno,sname,sage) values('98012','李四',16);
--2备份student到ts中 清空ts表
Create Table ts
( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),
Sname VARCHAR(20),
Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),
Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),
Sdept CHAR(2));
delete from ts;
insert into ts select * from student;

--3 给IS系的学生开设7号课程 建立所有的相应的选课记录 成绩暂定为60分
insert into sc 
 select sno,7,60
from student
where sdept='IS' ;
--4把年龄小于等于16的女生记录保存到表ts中
delete from ts;
insert into ts 
select * from Student
where sage<=16;
--5在表student中 检索每门课程均不及格的学生,姓名，年龄，性别及所在系的信息，
--并把检索到得信息存入到ts中
delete from ts;;
select * from student,sc
where student.sno=sc.sno 
group by sc.sno