一、实验目的和要求
1、掌握select语句的基本语法;
2、掌握子查询、连接查询的表示;
3、掌握select语句的GROUP BY、ORDER BY、LIMIT的作用和使用方法。
4、熟悉视图的概念和作用;
5、掌握索引和视图的创建方法;
6、掌握如何查询和修改视图。
二、实验内容和原理
1、select语句的基本使用:
(1)查询student表中每个学生的所有数据;(2)查询course和sc表的所有记录;
(3)查询年龄在20~21岁之间的学生的姓名及年龄;(4)统计学生总人数;
(5)查询信息系(IS)学生的姓名和性别;(6)查询所有姓“黄”的学生的信息。
2、子查询的使用:
(1)查询与“罗靖”在同一个系的学生
(2)查询其他系中比CS系所有学生年龄都小的学生的姓名和年龄。
3、连接查询的使用:
(1)查询选修了3号课程且成绩在85分以上的学生的学号、姓名。
(2)查询所有学生的选课情况。(3)查询没有选修2号课程的学生学号、姓名。
4、GROUP BY、ORDER BY和LIMIT子句的使用:
(1)查找student中男生和女生的人数;
(2)查找选修了2号课程的学生的学号及其成绩,查询结果按成绩降序排列;
(3)返回student表中的前3为同学的信息。
(4)查询信息工程系的学生与年龄不大于18岁的学生的交集。
(5)查询选修了1号课程但没有选修2号课程的学生的学号。
5、创建视图:
(1)创建zhz数据库上的视图student_view,视图包含学号、姓名、系;
(2)创建zhz数据库上的视图sc_view,视图包含sc表的全部列。
6、查询视图:
(1)从视图sc_view中查看选修了3号课程的学生的学号;
(2)从视图student_view中查询姓名为“李立婷”的学生所在的系。
7、更新视图:
(1)向视图sc_view中插入一行数据:3116003045,5,87;
(2)将视图student_view中学号=3116003043的学生的系改为MA;
(3)删除视图student_view中学号=3116003042的学生的信息。
8、删除视图:
(1)删除视图sc_view;
9、在界面工具中操作视图
三、实验环境
硬件:PC机
软件:Windows操作系统、 MySQL Server 6.0 和Navicat for MySQL 9.0
四、算法描述及实验步骤
表的建立
建立 student 表:
CREATE TABLE student
(Sno varchar(10) PRIMARY KEY,
Sname varchar(20) UNIQUE,Ssex varchar(2),
Sage SMALLINT,
Sdept varchar(20)
)
建立 course 表:
CREATE TABLE course
(Cno varchar(4) PRIMARY KEY,
Cname varchar(40),
Cpno varchar(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES course(Cno)
)
建立 sc 表:
CREATE TABLE sc
(Sno varchar(10),
Cno varchar(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES student(Sno)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Cno) REFERENCES course(Cno)
ON DELETE NO ACTION ON UPDATE CASCADE
)
向表中填充数据
向student表输入数据:
insert
into student(Sno,Sname,Ssex,Sage,Sdept)
values('3116003042','罗靖','男','20','CS');
insert
into student(Sno,Sname,Ssex,Sage,Sdept)
values('3116003043','吴健','男','22','IS');
insert
into student(Sno,Sname,Ssex,Sage,Sdept)
values('3116003044','郑智化','男','20','MA');
insert
into student(Sno,Sname,Ssex,Sage,Sdept)
values('3116003047','黄强伟','男','21','MA');
insert
into student(Sno,Sname,Ssex,Sage,Sdept)
values('3116003045','李立婷','男','20','IS');
向course表输入数据:
insert
into sc
values('3116003042','1','92');
insert
into sc
values('3116003042','2','92');
insert
into sc
values('3116003042','3','88');
insert
into sc
values('3116003043','2','90');
insert
into sc
values('3116003043','3','80');
向sc表输入数据:
insert
into sc
values('3116003042','1','92'),('3116003042','2','92');
insert
into sc
values('3116003042','3','88');
insert
into sc
values('3116003043','2','90');
insert
into sc
values('3116003043','3','80');
insert
into sc
values('3116003044','2','89');
1、select语句的基本使用:
(1)查询student表中每个学生的所有数据;-
select*
from student;
(2)查询course和sc表的所有记录;
select*
from course;
select*
from sc;
(3)查询年龄在20~21岁之间的学生的姓名及年龄;
select Sname,Sage
from student
where Sage<=21 and Sage>=20;
(4)统计学生总人数;
select count(*)
from student;
(5)查询信息系(IS)学生的姓名和性别:
select Sname,Ssex
from student
where Sdept='IS';
(6)查询所有姓“黄”的学生的信息:
select *
from student
where Sname like'黄%';
2、子查询的使用:
(1)查询与“罗靖”在同一个系的学生:
select Sname
from student
where Sdept in(select Sdept
from student
where Sname='罗靖');
(2)查询其他系中比CS系所有学生年龄都小的学生的姓名和年龄。
select Sname,Sage
from student
where Sage<any(select Sage
from student
where Sdept='CS')
and Sdept<>'CS';
3、连接查询的使用:
(1)查询选修了3号课程且成绩在85分以上的学生的学号、姓名:
select Sno,Sname
from student
where exists
(select *
from sc
where Sno=student.Sno and Cno='3' and grade>85);
(2)查询所有学生的选课情况。
select student.Sno,Sname,Cname
from student,sc,course
where student.Sno=sc.Sno and sc.Cno=course.Cno;
- 查询没有选修2号课程的学生学号、姓名。
select student.Sname,Sno
from student
where Sno not in (select Sno from sc where Cno=2);
4、GROUP BY、ORDER BY和LIMIT子句的使用:
(1)查找student中男生和女生的人数;
select Ssex,count(Ssex)
from student
group by Ssex;
(2)查找选修了2号课程的学生的学号及其成绩,查询结果按成绩降序排列;
select student.Sno,grade
from student,sc
where student.Sno=sc.Sno
order by grade desc;
(3)返回student表中的前3为同学的信息。
select *
from student
limit 3;
(4)查询信息工程系的学生与年龄不大于18岁的学生的交集。
select*
from student
where Sdept='IS' and Sage<=18;
(5)查询选修了1号课程但没有选修2号课程的学生的学号。
select a.Sno from sc a
where a.Sno not in (select Sno from sc where cno=1) and a.Cno=2;
5、创建视图:
(1)创建zhz数据库上的视图student_view,视图包含学号、姓名、系;
create view student_view
as
select Sno,Sname,Sdept
from student
with check option;
(2)创建zhz数据库上的视图sc_view,视图包含sc表的全部列。
create view sc_view
as
select*
from sc
with check option;
6、查询视图:
(1)从视图sc_view中查看选修了3号课程的学生的学号;
select Sno
from sc_view
where Cno='3';
(2)从视图student_view中查询姓名为“李立婷”的学生所在的系。
select Sdept
from student_view
where Sname='李立婷';
7、更新视图:
(1)向视图sc_view中插入一行数据:3116003045,5,87;
insert
into sc_view
values ('3116003045','5','87');
(2)将视图student_view中学号=3116003043的学生的系改为MA;
update student_view
set Sdept='MA'
where Sno='3116003043';
(3)删除视图student_view中学号=3116003042的学生的信息。
delete
from student_view
where Sno='3116003042';
或delete
from student
where Sno='3116003042';
无法删除,因为与sc有外键约束
8、删除视图:
(1)删除视图sc_view;
删除视图中的信息:delete
from sc_view;
删除视图:drop view sc_view;
9、在界面工具中操作视图
五、体会与总结
1)通过这次实验,掌握了select语句的基本使用方法,对数据库中的数据进行查询方法有了进一步的了解,巩固了子查询和连接查询的使用以及GROUP BY、ORDER BY和LIMIT子句的使用,为以后做数据库数据的查询奠定基础。
2)通过这次实验,掌握了视图的创建、查询、修改以及删除等基本操作;了解了视图与基本表的区别,视图是一个虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中;巩固了视图的基本概念及其作用。