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
数据库 实验5
最新推荐文章于 2024-02-19 13:11:51 发布