数据库系统（一）——数据查询

数据库系统（⼀）——数据查询⽂章⽬录
⼀、实验⽬的：
1. 熟练掌握SQL定义数据表和索引的⽅法；
2. 能够使⽤SQL完成数据的单表查询、多表查询和嵌套查询操作。

⼆、实验内容：
1. 根据指定场景创建数据库；
2. 根据具体的查询应⽤需求写出相应的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 o = o;
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 o='2' and SC.Grade>90;
4.3 、嵌套查询操作：
四、总结：
HAVING 是跟GROUP BY 连在⼀起⽤的，放在GROUP BY 后⾯，此时的作⽤相当于WHERE。

WHERE 后⾯的条件中不能有聚集函数，⽐如SUM(),AVG()等，⽽HAVING 可以。