数据库系统（一）——数据查询
文章目录
一、实验目的：
-
熟练掌握SQL定义数据表和索引的方法；
-
能够使用SQL完成数据的单表查询、多表查询和嵌套查询操作。
二、实验内容：
-
根据指定场景创建数据库；
-
根据具体的查询应用需求写出相应的SQL查询语句，并得到正确的查询结果。
1、熟悉基于单表的SQL的数据查询功能；
2、掌握基于单表的GROUP BY子句、HAVING子句、ORDER BY子句的用法；
3、掌握基于单表的（NOT）IN等谓词的用法，掌握集合函数的用法；
4、掌握多表连接查询的适用情况和语句构造方法；
三、实验过程：
1、创建学生课程数据库 （stu_course）：
create database stu_course;
2、学生表、课程表和学生选课表的创建和修改：
2.1、创建数据表：
create table Student(
Sno char(9)primary key,
Sname char(20)unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
create table Course (
Cno char(4)primary key,
Cname char(4) not null,
Cpno char(4),
Ccredit smallint ,
foreign key(Cpno) references Course(Cno)
);
create table SC(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
2.2、修改数据表：
在 Student 表增加入学时间列，数据类型为日期型：
alter table Student add S_entrance Date;
将年龄的数据类型 由 字符型（假设原来是字符型）改为 整数型：
alter table Student alter column Sage int;
增加课程名称必须取唯一的约束条件：
alter table Course add unique(Cname);
删除 Student表：
drop table Student;
3、插入数据：
INSERT INTO student VALUES ('201215121', '李勇', '男', 20, 'CS');
INSERT INTO student VALUES ('201215122', '刘晨', '女', 19, 'CS');
INSERT INTO student VALUES ('201215123', '王敏', '女', 18, 'MA');
INSERT INTO student VALUES ('201215125', '张立', '男', 19, 'IS');
INSERT INTO course VALUES ('1', '数据库', '5', 4);
INSERT INTO course VALUES ('2', '数学', NULL, 2);
INSERT INTO course VALUES ('3', '信息系统', '1', 4);
INSERT INTO course VALUES ('4', '操作系统', '6', 3);
INSERT INTO course VALUES ('5', '数据结构', '7', 4);
INSERT INTO course VALUES ('6', '数据处理', NULL, 2);
INSERT INTO course VALUES ('7', 'PASCAL语言', '6', 4);
INSERT INTO sc VALUES ('201215121', '1', 92);
INSERT INTO sc VALUES ('201215121', '2', 85);
INSERT INTO sc VALUES ('201215121', '3', 88);
INSERT INTO sc VALUES ('201215122', '2', 90);
INSERT INTO sc VALUES ('201215122', '3', 80);
4、各种类型的查询操作：
4.1 、单表查询操作：
1、查询指定列：
查询所有学生的的学号与姓名：
select Sno,Sname from Student;
2、查询全部列：
查询所有学生的信息：
select * from Student;
3、查询经过计算的值：
查询全体学生的姓名和出生年份：
select Sname 2021-Sage from Student;
4、消除出重复的元组：
select Distinct Sno from Student;
5、查询满足条件的元组：
查询计算机科学系的所有学生：
select Sname from Student where sdept='CS';
查询年龄小于20的学生：
select * from student where Sage <20;
查询年龄在20~23岁之间的学生姓名，系别和年龄：
select sname,sdept,sage from student where sage between 20 and 23;
查询年龄不在20~23岁之间的学生姓名，系别和年龄：
select sname,sdept,sage from student where sage not between 20 and 23;
6、确定集合（IN）：
查询 计算机科学系（CS），数学系（MA）和信息系（IS）学生的姓名和性别。
select Sname,Ssex from student where Sdept in('CS','MA','IS');
查询不是计算机科学系（CS），数学系（MA）和信息系（IS）学生的姓名和性别。
select Sname,Ssex from student where Sdept not in('CS','MA','IS');
7、字符匹配（模糊查询）：
like ：字符串匹配
% :代表任意长度字符
_ :代表单个字符
查询学号为 201215121 的学生的信息：
select * from where Sno like '201215121';
查询所有姓刘的学生姓名，学号和性别：
select Sname,Sno,Ssex from student where Sname like'刘%'
查询不姓刘的学生姓名，学号和性别：
select Sname,Sno,Ssex from student where Sname not like'刘%'
查询名字中第二个字为 "阳"的学生的姓名，学号：
select Sname,Sno from student where Sname like'_阳%';
8、多重条件查询（and ，or）：
查询计算机系的年龄20岁以下的学生的学生姓名：
select Sname from student where Sdept='CS' and Sage < '20';
9、order by：
查询选了3号课程的学生的学号和成绩，按成绩的降序排列（默认升序）：
select Sno,Grade from SC where Cno='3' order by Grade DESC;
10、聚集函数：
count(*) 统计元组个数
count([distinct|all] <列名>) 统计一列中值的个数
Sum([distinct|all] <列名>) 求一列总和
Max([distinct|all] <列名>) 求一列中的最大值
Min([distinct|all] <列名>) 求一列中的最小值
Avg([distinct|all] <列名>) 求一列的平均值
查询学生总人数：
select count(*) from student;
查询选修了课程的总人数：
select count(distinct Sno) from student;
查询学生 201215012 选修课程的总分数：
select sum(Ccredit) from SC,Course where Sno='201215121' and SC.Cno = Course.Cno;
11、分组（group by）：
求各个课程号及相应的选课人数：
select Cno,count(*) from SC group by Cno;
如果分组后还要求按一定的条件对这些组进行筛选，可以使用 having指定筛选条件。
查询选修了一门以上课程的学生学号：
select Sno from SC group by Sno having count(*)>1;
注意：where 子句中是不能用聚集函数的！需要用 having 代替
4.2 、多表连接查询操作：
一次从两个及以上的表 查数据称为连接查询。
查询每个学生及其选修课程的情况：
select student.*,SC.* from student,SC where student.Sno=SC.Sno;
查询选修2号课程且成绩在90分以上的所有学生的学号和姓名：
select student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Cno='2' and SC.Grade>90;
4.3 、嵌套查询操作：
四、总结：
-
HAVING 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当于WHERE。
-
WHERE 后面的条件中不能有聚集函数，比如SUM(),AVG()等，而HAVING 可以
