实验目的:
1.基本的插入、更新、删除SQL语句
2.带子查询的更新、删除及将查询结果添加到表中
实验要求:
1.掌握插入、更新、删除的SQL语句
2.掌握带条件的数据的更新及将查询结果添加到表中
实验内容:
在实验一的基础上,练习在表(视图)上进行数据的插入、更新、删除
1.插入数据
将以下数据插入到对应的表中,注意:维护完整性约束
Department
DepID | DName | Location | Leade |
---|---|---|---|
1 | 计算机系 | 麓山南路184号 | 804 |
2 | 应化系 | 求实路24号 | 854 |
3 | 数学系 | 北京路35号 | 822 |
4 | 电子工程系 | 836 |
Teacher
TNo | TName | Sex | Prof | Moblile | DepID |
---|---|---|---|---|---|
804 | 李诚 | 男 | 副教授 | 13712884353 | 1 |
856 | 张旭 | 男 | 讲师 | 13578453321 | 4 |
825 | 王萍 | 女 | 助教 | 1 | |
831 | 刘冰 | 女 | 助教 | 13686751234 | 4 |
822 | 周刚 | 男 | 教授 | 13512484756 | 3 |
827 | 张旺才 | 男 | 副教授 | 3 | |
854 | 沈长生 | 男 | 教授 | 2 | |
836 | 刘明 | 女 | 副教授 | 13813536608 | 4 |
Course
Cno | CName | Grade | Term | DepID | Tno |
---|---|---|---|---|---|
3-105 | 计算机导论 | 3 | 1 | 1 | 825 |
3-245 | 操作系统 | 4 | 4 | 2 | 804 |
6-166 | 数字电路 | 2 | 3 | 1 | 856 |
9-888 | 高等数学 | 4 | 1 | 3 | 831 |
Student
SNo | SName | Sex | BirthDate | Class |
---|---|---|---|---|
108 | 曾华 | 男 | 1982-09-01 | 95033 |
105 | 匡明 | 男 | 1982-10-02 | 95031 |
107 | 王丽 | 女 | 1981-01-23 | 95033 |
101 | 李军 | 男 | 1983-02-20 | 95033 |
109 | 王芳 | 女 | 1982-02-10 | 95031 |
103 | 陆君 | 男 | 1980-06-03 | 95031 |
SC
SNo | CNo | SDate | Score |
---|---|---|---|
103 | 3-245 | 2004-05-01 | 86 |
105 | 3-245 | 2004-06-11 | 75 |
109 | 3-245 | 2004-06-23 | 68 |
103 | 3-105 | 2002-10-11 | 92 |
105 | 3-105 | 2002-10-14 | 88 |
109 | 3-105 | 2002-10-15 | 76 |
101 | 3-105 | 2002-10-20 | 64 |
107 | 3-105 | 2002-10-17 | 88 |
108 | 3-105 | 2002-10-18 | 78 |
101 | 6-166 | 2003-05-18 | 85 |
107 | 6-166 | 2003-06-21 | 79 |
108 | 6-166 | 2003-06-08 | 81 |
2.更新数据
将刘冰的职称改为讲师
将王丽的计算机导论的考试成绩改为90分
3.删除数据
删除101号学生所有的选课记录
删除选修了李诚老师所授课程的所有成绩
4.插入子查询结果
通过一个子查询得到学生的学号、姓名、平均成绩,将这个结果插入到新建表SAVG(Sno,Sname,AVGrade)中。
相关代码
use JiaoXue
insert into Department values
(1,'计算机系','麓山南路184号','804'),
(2,'应化系','求实路24号','854'),
(3,'数学系','北京路35号','822'),
(4,'电子工程系',NULL,'836');
insert into Teacher values
('804','李诚','男','副高','13712884353',1),
('856','张旭','男','讲师','13578453321',4),
('825','王萍','女','助教',NULL,1),
('831','刘冰','女','助教','13686751234',4),
('822','周刚','男','正高','13512484756',3),
('827','张旺才','男','副高',NULL,3),
('854','沈长生','男','正高',NULL,2),
('836','刘明','女','副高','13813536608',4);
insert into Course values
('3-105','计算机导论',3,3,3,'825'),
('3-245','操作系统',4,4,2,'804'),
('6-166','数字电路',2,3,1,'856'),
('9-888','高等数学',4,1,3,'831');
insert into Student values('108','曾华','男','1982-09-01',95033),
('105','匡明','男','1982-10-02',95031),
('107','王丽','女','1981-01-23',95033),
('101','李军','男','1983-02-20',95033),
('109','王芳','女','1982-02-10',95031),
('103','陆君','男','1980-06-03',95031);
insert into SC values('103','3-245','2004-05-01',86),
('105','3-245','2004-06-11',75),
('109','3-245','2004-06-23',68),
('105','3-105','2002-10-14',88),
('109','3-105','2002-10-15',76),
('101','3-105','2002-10-20',64),
('107','3-105','2002-10-17',88),
('108','3-105','2002-10-18',78),
('101','6-166','2003-05-18',85),
('107','6-166','2003-06-21',79),
('108','6-166','2003-06-08',81),
('108','9-888','2003-05-27',90);
--更新数据
--将刘冰的职称改为讲师
update Teacher set prof='讲师' where TName='刘冰'
--将王丽的计算机导论的考试成绩改为90分
update SC set score=90
where SC.Sno in (select s.sno from Student s where S.SName='王丽')
AND SC.CNo in(select c.CNo from Course c where c.CName='计算机导论')
go
--3.删除数据
--删除101号学生所有的选课记录
delete from sc where sno='101'
--删除选修了李诚老师所授课程的所有成绩
update sc set Score =null where sc.cno in(select c.cno from course c, teacher t where c.tno=t.tno and t.tname='李诚')
--4.插入子查询结果
--创建savg表,记录学生的平均分
use JiaoXue
create table savg(sno char(10) primary key,
sname nchar(4) not null unique,
gavg decimal(4,1));
--将查询结果插入到savg表中
insert into savg(sno,sname,gavg)
select sc.sno,sname,AVG(score)
from student s,sc
where s.sno=sc.sno
group by sc.sno,sname--sname在这里并不起分组作用,只是避免语法错
select * from savg
delete from savg
--或者给中间查询结果其别名,让其在参与运算实现如下
insert into savg(sno,sname,gavg)
select sc1.sno,s.sname,sc1.gavg
from student s,
(select sno,AVG(score) gavg
from sc
group by sno) sc1
where sc1.sno=s.sno