《数据库原理与应用》课程实验报告二 数据库的简单查询和连接查询
一、实验目的
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