《数据库原理与应用》课程实验报告二 数据库的简单查询和连接查询

一、实验目的

1.掌握简单查询、连接查询的语法格式，熟练掌握数据查询中的分组、排序，以及外连接、自连接等。

2.掌握SQL Server Management Studio的使用方法，并能够根据出错警示，熟练修正查询语句。

二、实验过程及分析

1.实验内容

1.简单查询操作。包括选择条件表达，简单表达式输出、别名应用、模糊查询、数据排序、数据分组等。

2.连接查询操作。包括等值连接、自然连接、求笛卡儿积、自连接、外连接。

2.实验过程

实验一：

建表插入数据

学生选课数据库中表结构如下：

学生（学号，姓名，年龄，性别，所在系）；

课程（课程号，课程名，学分，先行课）；

选课（学号，课程号，成绩）；

在学生选课数据库中实现下面的数据查询操作。

--创建学生表create table student(s_num int PRIMARY KEY,s_name varchar(20)not null,s_age int check(s_age between 10 and 35),s_sex varchar(10) check(s_sex in('男', '女')),s_unit varchar(20))drop table student select * from student--插入数据insert into student values (1815001,'张三',18,'男','数学系'),(1815002,'李白',30,'女','计算机系'),(1815003,'后裔',11,'男','计算机系'),(1815004,'翠花',25,'女','数学系'),(1815005,'狗蛋',21,'男','机械系');--创建课程表create table course(c_num varchar(20) PRIMARY KEY,c_name varchar(20) not null,c_credit int check(c_credit in (1,2,3,4,5,6)),front varchar(20) )--插入数据drop table courseinsert into course values ('C1','C语言',2,' '),('C2','程序设计电路',2,'c1'),('C3','数字电路',3,'C1'),('C4','计算机组成',3,'C3'),('C5','计算机网络',4,'C4'),('C6','操作系统',3,'C5');select * from course--创建选课表create table selectCourse(s_num int,c_num varchar(20),score numeric(3,1) ,primary key (s_num,c_num),foreign key ( s_num) references student(s_num),foreign key (c_num) references course(c_num),)drop table selectCourse--插入数据insert into selectCourse(s_num, c_num, score ) values(1815001,'C1',70),(1815001,'C2',80),(1815001,'C3',88),(1815001,'C4',78),(1815001,'C5',88),(1815001,'C6',98),(1815005,'C3',88),(1815002,'C1',80),(1815003,'C1',88),(1815004,'C1',98)insert into selectCourse(s_num, c_num ) values(1815005,'C1')数据查询--1)求数学系学生的学号和姓名select s_num as '学号' ,s_name as '姓名' from student where s_unit='数学系';--2)求选修了课程的学生学号select distinct s_num as '学生学号' from selectCourse where c_num is not null--3)求选修C1课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列select s_num as '学号' , score as '成绩' from selectCourse where c_num ='C1' and score is not null order by score--4)求选修课程C1成绩在80~90之间的学生学号和成绩,并将成绩乘以0.8输出select s_num as '学号', score*0.8 as '成绩' from selectCourse where c_num ='C1' and ( score between 80 and 90) order by score--5)求数学或计算机系姓张的学生的信息select * from student where s_name like '张%' and s_num in (select s_num from student where s_unit='计算机系' or s_unit ='数学系')--6)查询每个学生的选课门数，并输出选修5门以上的学生学号。select s_num as '学号' from selectCourse group by s_num having count(c_num)>5;--7)求缺少了成绩的学生的学号和课程号select s_num as '学号' ,c_num as '课程号' from selectCourse where score is null--8)查询各个系的学生人数select s_unit as '系' ,count(s_unit) as '人数 ' from student group by s_unit--9)求学生的学号、姓名、选修的课程名及成绩select student.s_num as '学号' ,student.s_name as '姓名' ,selectCourse.c_num as '课程名',selectCourse.score as '成绩' from student join selectCourse on student.s_num= selectCourse.s_num --10)求选修课程C1成绩在80~90之间的学生学号、姓名及成绩select student.s_num as '学号' ,student.s_name as '姓名' ,selectCourse.score as 'C1成绩' from student join selectCourse on student.s_num= selectCourse.s_num where (selectCourse.score between 80 and 90)--11)求选修了学分是3的课程的学生学号。select student.s_num as '学号' from selectCourse join student on student.s_num= selectCourse.s_numjoin course on course.c_num=selectCourse.c_numwhere course.c_credit=3--12)查询每一门课的间接先行课（即先行课的先行课）SELECT k1.c_num as '课程号',k1.c_name as '课程名',k2.front '间接先行课'FROM Course as k1 ,Course as k2where k1.front= k2.c_numSELECT k2.frontFROM Course k2

提高实验：

建表插入数据

（1）建立职工部门数据库，数据库涉及有职工表、部门表，并向表中输入数据。

职工表

职工号 姓名 性别 年龄 所在部门

1010 李勇 男 20 11

1011 刘晨 女 19 12

1012 王敏 女 22 12

1014 张立 男 21 13

部门表

部门号 部门名称 电话

11 生产科 566

12 计划科 578

13 一车间 467

14 科研所

--创建职工部门create table employee(职工号 int PRIMARY KEY,姓名 varchar(20)not null,性别 varchar(10) check(性别 in('男', '女')),年龄 int check(年龄 between 10 and 35), 所在部门 varchar(20))select * from employee--插入相关数据insert into employee values(1010,'李勇','男',20,11),(1011,'刘晨','女',19,12),(1012,'王敏','女',22,12),(1014,'张立','男',21,13)--创建部门表create table department(部门号 varchar(20),部门名称 varchar(20)not null,电话 varchar(11) )--插入相关数据insert into department values(11,'生产科',566)insert into department values(12,'计划科',578)insert into department values(13,'一车间',467)insert into department (部门号,部门名称) values(14,'科研所')数据查询--1)查询每个职工的信息，及所对应的部门电话。select 职工号,姓名,性别,年龄,所在部门,电话 from employee K1 join department as k2 on k1.所在部门=k2.部门号--2)查询每个部门的名称、电话，及部门职工的姓名、年龄。select 所在部门,电话,姓名,年龄 from employee join department on employee.所在部门=department.部门号--3)针对职工表查询每个部门的职工人数。select 所在部门 , count(所在部门) as 职工人数 from employee group by 所在部门--4)查询年龄小于20的职工号、所在部门select 职工号,所在部门 from employee where 年龄<20--5)查询年龄小于20的职工号、所在部门名称select 职工号,部门名称 from employee join department on employee.所在部门=department.部门号 where 年龄<20

3.实验结果

实验一：

提高实验：

4.实验分析

①分析实验结果；

通过sql语句得到了的结果

②说明实验过程中遇到的问题及怎么解决。

查询每一门课的间接先行课（即先行课的先行课）。我不能顺利的进行查询

解决

这个操作属于表与自身进行连接，那么zd我们需要将课程表取两个别名去区分它们。

```sqlSELECT k1.c_num as '课程号',k1.c_name as '课程名',k2.front '间接先行课'FROM Course as k1 ,Course as k2where k1.front= k2.c_numSELECT k2.frontFROM Course k2