数据库SQLServer实验三多表查询和子查询

一、实验目的

• 了解查询的概念和方法;
• 掌握查询分析器的使用方法;
• 掌握$SELECT$语句在单表查询中的应用;
• 掌握复杂查询的使用方法;
• 掌握多表连接的方法;
• 掌握$SELECT$语句在多表查询中的应用;
• 掌握子查询语句.

二、实验内容

(1).多表查询

• 数据库的各个表中存放着不同的数据,用户经常需要用多个表中的数据来组合提炼出所需要的信息,如果一个查询需要对多个表进行操作,就称为联表查询,联表查询的结果集或结果表称为表之间的连接.联表查询实际上是通过各各表之间共同列的关联来查询数据的,它是关系数据库查询最基本的特征.

• 按照下表所示,分别在数据库$test$中构造$student$、$course$和$student_course$三张表,并写入记录.

• $Student$表

列名称	类型	宽度	允许为空	缺省值	主键
学号	$Char$	$8$	否		是
学生姓名	$Nvarchar$	$8$	否
性别	$Char$	$2$	否
年龄	$Smallint$		否
班级号	$Char$	$6$	否
入学时间	$Smalldatetime$		否
家庭住址	$Nvarchar$	$40$	是

---

• $Student$表

列名称	类型	宽度	允许为空	缺省值	主键
课程号	$Char$	$10$	否		是
课程名称	$Nvarchar$	$20$	否
课程总学时	$Tinyint$		否
课程学分	$Tinyint$		否

---

• Student_course表

列名称	类型	宽度	允许为空	缺省值	主键
课程号	$Char$	$10$	否		是
学号	$Char$	$8$	否		是
成绩	$Tinyint$		否

create table Student
    (  学号       char(8)        not null  primary key,
       学生姓名   nvarchar(8)    not null ,
       性别       char(2)        not null ,
       年龄       smallint       not null ,
       班级号     char(6)        not null ,
       入学时间   smalldatetime  not null ,
       家庭住址   nvarchar(40)   )

create table Course
    (  课程号     char(10)       not null  primary key,
       课程名称   nvarchar(20)   not null ,
       课程总学时 tinyint                 ,
       课程学分   tinyint                 )

create table Student_Course
    (  课程号    char(10)       not null,
       学号      char(8)        not null,
       成绩      tinyint        not null
       primary key (课程号,学号),
       foreign key (课程号) references Course  (课程号),
       foreign key (学号)   references Student (学号),
    )
# 录入数据略

• 从$student$、$course$和$student_course$三张表中检索选修了课程的学生的学号、姓名、课程号、课程名及成绩.

select Student.学号,Student.学生姓名,Course.课程号,Course.课程名称,Student_Course.成绩
    from Student ,Course ,Student_Course
    where Student.学号=Student_Course.学号 and Course.课程号=Student_Course.课程号

• 如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值,就要在$SELECT$语句中与$GROUP$ $BY$子句一起使用集合函数.使用$GROUP$ $BY$子句进行数据检索可得到数据分类的汇总统计、平均值或其他统计信息.

(1) 使用不带$HAVING$的$GROUP$ $BY$子句,汇总出$student_course$表中的学生的学号及总成绩

select 学号,sum(成绩) 总成绩
    from Student_Course
    group by 学号

(2) 使用带$HAVING$的$GROUP$ $BY$子句,汇总出$student_course$表中总分大于$450$分的学生的学号及总成绩

select 学号,sum(成绩) 总成绩
    from Student_Course
    group by 学号
    having sum(成绩)>450

(2).子查询

1．使用$IN$或$NOT$ $IN$关键字.

• 使用$IN$关键字查询出‘$jk1$’ ‘$dx2$’ ‘$dx1$’班所有男生的学号、课程号及相应的成绩

select Student_Course.学号,课程号,成绩
    from Student_Course,Student
    where Student.学号=Student_Course.学号 and Student.班级号 in ('jk1','dx2','dx1') and Student.性别='男'

2. 使用$EXISTS$或$NOT$ $EXISTS$关键字.

• 使用$EXISTS$关键字查询出$‘jk1’$班选修‘数据结构’课程的学生的学号、姓名;使用$NOT EXISTS$查询出‘$jk1$’班没有选修‘数据结构’课程的学生的学号、姓名

select Student.学号,Student.学生姓名
    from Student
    where exists
    (    select * from Student_Course ,Course
        where Student_Course.学号=Student.学号 and Student_Course.课程号=Course.课程号 and Course.课程名称='数据结构' and Student.班级号='jk1'
    )

select Student.学号,Student.学生姓名
    from Student
    where Student.班级号='jk1' and not exists
    (    select * from Student_Course ,Course
        where Student_Course.学号=Student.学号 and Student_Course.课程号=Course.课程号 and Course.课程名称='数据结构' 
    )