MySQL的查询、索引和视图操作实验

一、实验目的和要求

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;

1. 查询没有选修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）通过这次实验，掌握了视图的创建、查询、修改以及删除等基本操作；了解了视图与基本表的区别，视图是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中；巩固了视图的基本概念及其作用。