实验二 sql sever数据操纵子语言

实验目的：

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