1、实验二 SQL 语言的基本操作实验目的和要求：掌握利用 SQL 语句完成各种查询操作的能力。重点掌握用 SELECT 语句进行各种查询； 掌握 INSERT 语句的用法。 实验内容：用 SQL 语句完成一下的要求：1.查询信息系（IS）的所有学生信息select * from student where sdept=is2.查询选修了“ 数学” 课的所有学生名单Select s.sno,sname From student s,course c,scWhere s.sno=sc.sno and o=o and cname=数学 3.查询至少选修了一门其直接先行课为 5 号课程的学生的姓名。Se
2、lect sname From student s, sc, course cWhere s.sno=sc.sno and o=o and pcno=54.查询全体学生的姓名和出生年份。select sname,year(now()-sage as 出生年份 from student5.查询所有姓王的学生。select * from student where sname like 王%6.查询选修了 3 号课程的学生姓名及成绩，并按成绩降序排序。Select sname,gradeFrom student s, scWhere s.sno=sc.sno and o=3Order by gra
3、de desc7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。Select *From studentOrder by sdept asc,age desc8.计算 2 号课程的平均成绩。Select avg(grade)From scWhere cno=29.查询选修了 2 号课程的学生的最高成绩。select max(grade) from sc where cno=210.求各个课程号及相应的选课人数。Select cno as 课程号,count(sno) as 人数From scGroup by cno11.查询至少选修了 3 门课程以上的学生学号
4、。select snofrom sc group by sno having count(*)212.查询“数据库 ”的间接先行课。Select ameFrom course c1,course c2,course c3Where c1.cpno=o and ame=数据库 and c2.cpno=o13.查询平均成绩最高的学生的学号和姓名。select top 1 sno,avg(grade)from scgroup by snoorder by avg(grade) desc14.查询数学成绩最高的学生的学号和姓名。select top 1 s.sno,sname,gradefrom st
5、udent s,course c, scwhere s.sno=sc.sno and o=o and cname=数学 order by grade desc15.查询出成绩最低学号最大的学生学号。select top 1 sc.sno,gradefrom scorder by grade asc,sno desc16.查询成绩高于学生平均成绩的记录。Select *From scWhere grade(select avg(grade)From sc )17.查询至少选修了 1 号课程和 3 号课程的学生学号。Select sc1.snoFrom sc sc1,sc sc2Where sc1
6、.sno=sc2.sno and o=1 and o=318.查询只选修了 1 号课程和 3 号课程的学生学号。select snofrom sc where cno=1 and sno in(select sno from sc where cno=3)and sno in(select sno from sc group by sno having count(cno)=2)19.查询没有选修 1 号课程的学生姓名。Select distinct s.snameFrom student s, scWhere s.sno=sc.sno and o!=120.查询选修了全部课程的学生姓名。Se
7、lect snameFrom student sWhere not exist (select *From course cWhere not exist (select *From scWhere s.sno=sc.sno and o=o)21.查询至少选修了 95002 所选修的全部课程的学生学号。Select sc1.snoFrom sc sc1Where not exist (select *From sc sc2Where sc2.sno=95002 andNot exist( select *From sc sc3Where o=o and sc1.sno=sc3.sno)22.查
8、询没有不及格课程的学生的学号和姓名。Select distinct sc.sno,s.snamefrom sc,student swhere sc.sno=s.sno and not exists (select *from sc sc2where sc.sno=sc2.sno and sc2.grade60)23.查询没有不及格学生的课程的课程号和课程名。Select distinct o,amefrom sc ,course cwhere o=o and not exists (select *from sc sc2where o=o and sc2.grade60)24.建立信息系学生视
9、图，并从视图中查询年龄最大的学生记录。goCreate view is_student(sno,sname,sage)as Select sno,sname,sageFrom s Where sdept=isSelect max(sage)From is_student1.用 SQL 语句定义表 student(sno,sname,ssex,sage，sdept)，并加入如下约束：主键：sno；sname 有唯一约束；sname,ssex,sage 都不允许空；create table student (sno char(10) not NULL unique,sname char(20) n
10、ot NULL unique,ssex char(2) not null,sage int not null,sdept char(20) not null,primary key (sno)2.用 SQL 语句定义表 course(cno,cname,cpno,credit)，并加入如下约束：主键：cno；cname 不允许空；create table course(cno char(10) not NULL unique,cname char(20) not NULL,cpno char(10),credit char(10),primary key (cno)3.用 SQL 语句定义表
11、sc(sno,cno,cj)，并加入如下约束：主键：sno,cno；为 sno 定义名为 lsno 的默认参照完整性；为 cno 定义名为 lcno 的默认参照完整性；create table sc(sno char(10) not NULL,cno char(10) not NULL,grade int,primary key (sno,cno),constraint lsno foreign key (sno) references student(sno),constraint lcno foreign key (cno) references course(cno);4.用 SQL 语
12、句向 student 表输入如下元组：(95001,李勇,男,20,CS); (95002,刘晨,女,21,IS);insert into studentvalues (95001,李勇,男,20,CS);另一组数据同上进行插入。用 SQL 语句向 course 表输入如下元组：(1,数据库,5,4);(2,数学,NULL,2);insert into coursevalues (1,数据库,5,4);另一组数据同上进行插入。用 SQL 语句向 sc 表输入如下元组：(95001,1,92);(95001,2,85); (95002,2,90);insert into scvalues (95
13、001,1,92);其它组数据同上进行插入。5.执行下列语句，并查看执行结果。如果不能正确执行给出错误原因。insert into student values(95001,张力,男,20,CS);不能执行，student 中 sno 属性为 unique，student 中已经有学号为 95001 的学生信息了，所以不能再插入相同学号的学生信息。insert into student values(95003,李勇,男,20,CS);不能执行，student 中 cname 属性为 unique，student 中已经有姓名为李勇的学生信息了，所以不能再插入相同姓名的学生信息。insert
14、into SC values(95004,1,92);不能执行，根据参照完整性，在 student 表中没有 95004 的信息，所以不能插入。delete from student where sno=95001;不能执行，因为在 sc 表中有 95001 的信息。update course set cno=3 where cno=2;不能执行，因为 sc 表中有 cno=2的信息。6.给 student 表的 ssex 列添加名为 fm 的约束，使其取值只能取男或女。alter table studentadd constraint fm check (ssex in (男,女)执行 in
15、sert into student values(95005,张力,f,20,CS)，查看执行结果。不能进行插入，因为，所输入的信息中性别必须是男或女。7.给 student 表的 sage 列添加约束，使其年龄不得超过 20 岁。查看约束是否能正确添加，并分析其原因。alter table studentadd constraint age check (sage 20)不能正确添加，ALTER TABLE 语句与 COLUMN CHECK 约束 age 冲突。该冲突发生于数据库 学生信息，表 student, column sage，因为表数据有 sage 20 的信息。8.删除约束 ls
16、no 和 lcno。alter table scdrop constraint lsno,lcno9.为 sc 表添加在列 sno 上的外键约束 lsno1，并定义为级联删除。执行 delete from student where sno=95001;查看执行结果。alter table scadd constraint lsno1 foreign key (sno) references student(sno)on delete cascade;由于是级联删除，所以除 student 表中学号为 95001 的学生的信息被删除外，其在 sc表中的信息也被删除了。10.为 sc 表添加在列 cno 上的外键约束 lcno1，并定义为级联修改。执行 update course set cno=3 where cno=2;查看执行结果。alter table scadd constraint lcno1 foreign key (cno) references course(cno)on update cascade;修改成功，且 course 表和 sc 表中 cno=2都被修改成了 cno=3。
