上机实验三——基本表的建立和修改
三、实习内容：
1. 启动MSSQL Server服务，打开Enterprise Manager和Query Analyzer。
2. 在Query Analyzer中用CREATE TABLE命令在实验二创建的GradeManager数据
库中定义基本表：学生表（Student）、课程表（Course），利用Enterprise Manager的图形化功能建立班级表（Class）以及成绩表（Grade）。
create table Student
( Sno char(7) primary key ,
Sname varchar(20) not null ,
Ssex char(2) not null,
Sage Smallint ,
Clno char(5)not null);
create table Class
( Clno char(5) primary key,
Speciality varchar(20) not null,
Inyear char(4) not null,
Number integer ,
Monitor char(7) );
create table Course
(Cno char(1) primary key,
Cname varchar(20) not null,
Credit Smallint );
create table Grade
(Sno char(7) references student(sno),
Cno char(1) references course(cno),
Gmark numeric,
Primary key(sno,cno));
四、针对以上四个表，用SQL语言完成以下各项操作。
① 给学生表增加一属性Nation（民族），数据类型为Varchar（20）；
② 删除学生表中新增的属性Nation；
③ 向成绩表中插入记录（”2001110”，”3”，80）；
④ 修改学号为”2001110”的学生的成绩为70分；
⑤ 删除学号为”2001110”的学生的成绩记录；
⑥ 为学生表创建一个名为IX_Class的索引。
1.
ALTER TABLE Student
ADD Nation varchar(20);
2.
ALTER TABLE Student
DROP COLUMN Nation;
3.
Insert into grade(sno,cno,gmark) values(‘2001110’，’3’，80）
点评：该语句没有语法错误，但好多同学在执行时出现了问题，是对的。但同学们要知道为什么会出现这个问题。
4.
UPDATE Grade
SET Gmark=70
WHERE Sno='2001110';
5.
DELETE
FROM Grade
WHERE Sno='2001110';
6.
CREATE INDEX IX_Class
ON Student(Clno Asc);
7.
DROP INDEX Student.IX_Class;
五、思考题
在定义基本表语句时，NOT NULL参数的使用有何作用？
答：Not Null参数，可以保证在插入数据时，该属性列的取值不为空。
上机实验四——SELECT语句的使用(一)
三、实习内容：
完成以下各项操作的SQL语句：
① 找出所有被学生选修了的课程号；
select distinct cno from grade;
该语句实现的路径应该是Grade表， Course表中可能有某门课，一个学生都没有选修的。所以不能是查询Course表的Cno。
② 找出01311班女学生的个人信息；
select * from student
where Ssex = '女' and clno = '01311' ;
③ 找出01311班、01312班的学生姓名、性别、出生年份； select Sname,Ssex,2012- Sage as birth
from student
where clno = '01311' or clno = '01312';
OR：
select Sname,Ssex,2012- Sage as birth
from student
where clno in('01311', '01312');
OR:(在T-SQL中,超大纲了，呵呵)
Select sname,sex,year(getdate())-sage
From student Where clno in (‘01311’,’01312’);
④ 找出所有姓李的学生的个人信息；
select * from student
where Sname like '李%';
⑤ 找出学生李勇所在班级的学生人数；
Select count(*) from student
Where clno in (Select clno from student
Where sname=”李勇”);
Or: Select number from class
Where clno in (Select clno from student Where sname=”李勇”);
Or: Select number from class,student
Where sname=’李勇’ and class.clno=student.clno; ⑥ 找出课程名为操作系统的平均成绩、最高分、最低分；
Select avg(gmark),Max(gmark),Min(gmark)
From grade,course
Where cname=”操作系统” and grade.cno=course.cno; Or: Select avg(gmark),Max(gmark),Min(gmark) From grade
Where cno=
(Select cno from course where cname=”操作系统”); ⑦ 找出选修了课程的学生人数；
select count(distinct Sno)
from grade;
⑧ 找出选修了课程操作系统的学生人数。
Select count(sno) From grade
Where cno=
(Select cno from course Where cname=”操作系统” );
Or: Select count(sno) From grade,course
Where grade.cno=course.cno and cname=”操作系统”;
（9）找出2000级计算机软件班的成绩为空的学生姓名。
select Sname
from Student
where Clno in
(select Clno
from Class
where Speciality='计算机软件' and Inyear=2000)
and Sno in (select Sno from Grade where gmark is null);
四、思考题：
什么情况下需要使用关系的别名？别名的作用范围是什么？
答：一般几种两种情况下使用别名：1.关系名太长，为了简化；2.为了做自身连接查询。3.在同一个SQL语句中，为了对某个表进行2次扫描。
别名的作用范围是当前该SQL语句，离开当前SQL语句，别名就不在存在。
上机实验五——SELECT语句的使用(二)
三、实习内容：
完成以下各项操作的SQL语句：
① 找出与李勇在同一个班级的学生信息；
② 找出所有与李勇有相同选修课的学生信息；
③ 找出年龄介于学生李勇和25岁之间的学生信息；（已知李勇年龄小于25岁） ④找出选修了课程操作系统的学生学号和姓名
⑤找出没有选修1号课程的学生姓名
⑥ 找出选修了全部课程的学生姓名；
① 找出与李勇在同一个班级的学生信息；
Select * from student
Where clno=
(Select clno from student where sname=’李勇’);
② 找出所有与学生李勇有相同选修课程的学生信息
select * from Student
where Sno in
(select Sno from Grade
where Cno in
(select Cno from Grade
where Sno in
(select Sno from Student
where Sname='李勇')))
and Sname <>'李勇';
③ 找出年龄介于学生李勇和25岁之间的学生信息；
Select * from student
Where sage between
(Select sage from student Where sname=’李勇’)
And 25;
④ 找出选修了课程操作系统的学生学号和姓名；
Select sno,sname from student
Where sno in
( Select sno from grade Where cno = ( Select cno from course Where cname =’操作系统’));
⑤ 找出所有没有选修1号课程的学生姓名；
Select sname from student
Where not exist
(Select sno from grade
where cno=’1’ and sno=student.sno);
OR: Select sname from student
Where sno not in
(Select sno from grade Where cno=’1’);
⑥ 找出选修了全部课程的学生姓名
（提示：可找出这样的学生，没有一门课程是他不选修的。） 下面这个编程的思路有问题，是错误的哦：
SELECT Sname
FROM Student
WHERE Sno IN (SELECT Sno FROM Grade WHERE Cno=1) AND
Sno IN(SELECT Sno FROM Grade WHERE Cno=2) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=3) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=4) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=5) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=6) AND Sno IN(SELECT Sno FROM Grade WHERE Cno=7) 解一:
解二:
Select sname from student Where sno in (Select sno from grade Group by sno
Having count(*)=Select count(*) from course); Select sname from student Where not exists (Select * from course where not exists (Select * from grade Where sno=student.sno and cno=course.cno);
OR:
Select sname from student Where (Select count(cno) from grade
=(Select count(*) from course); Group by sno )
解法三：
Select Sname
From Student
WHERE not exists
(Select Cno
From Course
except
Select Cno
From Grade
WHERE Student.Sno=Grade.Sno); 类似表达的还有：
--1.
SELECT Sname FROM Student
WHERE (select count(cno) from course) =(SELECT count(sno) FROM Grade GROUP BY Sno)
--2.
SELECT Sname FROM Student
WHERE Sno=ANY
(SELECT Sno FROM Grade
GROUP BY Sno
HAVING(COUNT(*)=
(SELECT COUNT(*) FROM Course))) ORDER BY Sname;
（1）查询选修了3号课程的学生学号及成绩，并按成绩的降序排列 select Sno,Gmark from Grade
where Cno='3'
order by Gmark desc;
（2）查询全体学生信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列
select * from Student
order by Clno,Sage desc;
（3）求每个课程号及其相应的选课人数
select Cno,count(Sno) as 选课人数 from Grade
group by Cno;
（4）查询选修了3门以上课程的学生学号
select Sno,count(Cno) as 选课门数 from Grade
group by Sno
having count(Cno)>3;
select sno from Grade g
where (select COUNT(cno) from Grade
where Sno=g.Sno
)>3;
五、思考题：
1． 用UNION或UNION ALL将两个SELECT命令结合为一个时，结果有何不同？
2． 当既能用连接词查询又能用嵌套查询时，应该选择哪种查询较好？为什么？
3． 库函数能否直接使用在：SELECT选取目标、HAVING子句、WHERE子句、GROUP BY列名中？
上机实验六——SQL的存储操作
三、实习内容：
完成以下各项操作的SQL语句：
① 将01311班全体学生的成绩置零；
② 删除2001级计算机软件的全体学生的选课记录；
③ 学生李勇已退学，从数据库中删除有关他的记录；
④ 对每个班，求学生的平均年龄，并把结果存入数据库。
① 将01311班的全体学生的成绩置零；
update grade
set gmark=0
where sno in (select sno from student where clno=’01311’) ⑤ 删除01311班全体学生的选课记录；
delete
from grade
where sno in (select sno
from student
where clno = ‘01311’ ;
③ 学生李勇已退学，从数据库中删除有关他的记录。
注意：数据的一致性 删除表的次序：Grade / class /student delete from grade
where sno in
(select sno from student where sname ='李勇');
update class
set number = number -1
where clno =
delete from student
where sname ='李勇';
作业点评：有同学的语句如下：
delete from student a join Grade b on a.Sno=b.Sno join Course c on c.Cno=b.Cno where Sname='李勇';
注意：ANSI SQL的定义是一个Delete语句只能对一个表进行删除操作！
(select clno from student where sname ='李勇');
④对每个班，求学生的平均年龄，并把结果存入数据库；
解法一：建表、插入
create table avgage
(clno char(5),
avgage smallint);
insert into avgage select clno,avg(sage) from student grpup by clno; 解法二：建视图
create view v_avgage
as
select clno , avg(sage) as avg_age
from student
解法三：修改Class表后插入
alter table class add avgage smallint
go
update class
set avgage = (select age(sage) from student
group by clno
having student.clno = class.clno)
解法四：直接建表插入
select clno,avg(sage) as avg_age into avgage
from student
下面这种根据表中的数据进行编程的解法的思维是错误的，数据是随时都会变化的（虽然Case语句用的不错 ? ?）：
alter table Class add Cage smallint null
update Class set Cage=case
when Clno='00311' then (select AVG(Sage) from Student where Clno='00311') when Clno='00312' then (select AVG(Sage) from Student where Clno='00312') when Clno='01311' then (select AVG(Sage) from Student where Clno='01311') end
from Class
五、思考题：
DROP命令和DELETE命令的本质区别是什么？
19. 视图操作
create view Stu_00312_1 --1
as
select *
from Student
where Student.Sno in(
select Sno
from Grade
where Grade.Cno='1')and Student.Clno='00312'
create view Stu_00312_2 --2
as select *
from Student
where Student.Sno in(
select Sno
from Grade
where Grade.Cno='1'and Grade.Gmark<=60)and Student.Clno='00312'
create view Stu_year --3
as select Sno ,Sname,2005-Sage birthyear
from Student
select Sname --4
from Stu_year
where birthyear>1983
select Sno,Sname,2005-Sage --5
from Stu_01312_2
实验八 完整性约束
Create table student
(sno char(7) primary key,
sname varchar(20) not null,
ssex char(2) not null check(ssex in('男','女')) default ('男'), sage smallint check(sage<65 and sage>14),
clno char(5) not null)
---------------------------
alter table course add primary key(cno);
alter table course add constraint PK_course primary key(cno); alter table course add constraint chk_cred
check(credit in (1,2,3,4,5,6));
-----------------------------
alter table class add primary key(clno);
alter table class add check(number>1 and number<100);
alter table class add foreign key (monitor) references student(sno);
------------------
alter table student add foreign key(clno) references class(clno);
---------------------------------
alter table grade add primary key(sno,cno);
alter table grade add foreign key (cno) references course(cno); alter table grade add constraint chk_mark check(gmark>=0 and Gmark<=100);
点评：要注意外部码定义的时候，首先被参照关系（父表）必须已经定义了主码（或者唯一性约束定义），另外如果表中已经有数据的，表中已有数据不能违反参照完整性约束。
假如数据库中已有Student、Grade、Course和Class表，再添加以上约束： Student：
alter table Student
add Check(Ssex in('男','女'))
alter table Student
add default '男' for ssex
alter table Student
add check(Sage>14 and Sage<65)
alter table Class//先给Class表创建主键，否则Student不能建立与Class的外部约束
add primary key(clno)
alter table Student
add foreign key (clno) references Class(clno)
on update CASCADE
Course表
alter table Course add primary key(cno)
alter table Course add check (Credit in(1,2,3,4,5,6))
Class表
alter table Class add check(Number>1 and Number<100)
alter table Class add foreign key (Monitor) references Student(sno) Grade表
alter table Grade add foreign key(sno) references Student(sno) alter table Grade add foreign key(cno) references Course(cno) alter table Grade add check (Gmark>0 and Gmark<100)