数据库系统(一)——数据查询

一、实验目的:

  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 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 可以


转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 2058751973@qq.com

×

喜欢就点赞,疼爱就打赏