MySQL实验四数据库的查询_MySQL数据库查询（实验四）

MySQL数据库查询

准备工作：脚本文件xkgl.sql下载：xkgl脚本.sql

1、执行脚本xkgl.sql (创建xkgl库、表及插入数据)，观察有无错误，如有记录错误信息，并解决。

(1) 执行脚本

执行代码：

source+xkgl.sql文件所放的位置；(要用反斜杠)；

(2)检查创建表的情况

代码：

show tables;

检查结果截图：

(3)检查七张表的数据记录数

代码：

select c1.class_count,c2.course_count,d.department_count,

g.grade_count,s1.schedule_count,s2.student_count,

t.teacher_count from

(select count(*) class_count from class) c1,

(select count(*) course_count from course) c2,

(select count(*) department_count from department) d,

(select count(*) grade_count from grade) g,

(select count(*) schedule_count from schedule) s1,

(select count(*) student_count from student) s2,

(select count(*) teacher_count from teacher) t;

检查结果截图：

2、在xkgl数据库中进行如下的单表查询

(1)查询teacher表中所有教师的姓名和年龄：

代码：

select Teachername,year(now())-year(Brith) as age from teacher;

查询结果截图：

(2)查询所有系的信息：

代码：

select * from department;

查询结果截图：

(3)查询学分值大于等于4的课程的名称：

代码：

select * from course where credit>=4;

查询结果截图：

(4)查询Cs010901班的女生信息：

代码：

select * from student where Sex='女' and ClassID='Cs010901';

检查结果截图：

(5)查询学生姓名中第2个字为“丽”的学生信息：

代码：

select * from student where StudentName like '_丽%';

检查结果截图：

(6)查询选修了Dp010001号课程的学生中成绩位于4到8名的学生学号和成绩：

代码：

select StudentID,Grade from grade where CourseID='Dp010001' order by Grade desc limit 3,5;

检查结果截图：

(7)查询schedule表中学年和课程号的组合，去掉重复：

代码：

select distinct SchoolYear,CourseID from schedule;

查询结果截图：

(8)查询年龄大于40岁的教授和副教授的姓名和性别：

代码：

select Teachername,Sex from teacher where Profession='教授' or Profession = '副教授' and year(now())-year(Brith)>40;

查询结果截图：

(9)查询course表中前5行数据：

代码：

select * from course limit 0,5;

查询结果截图：

3、在xkgl数据库中进行如下的分类汇总。

(1)查询course表中的最大学分和最小学分的课程：

代码：

select max(credit)最高分,min(credit)最低分 from course;

查询结果截图：

(2)查询不同职称的教师人数：

代码：

select Profession,count(Profession) 人数 from teacher group by Profession;

查询结果截图：

(3)查询grade表中选修了3门以上课程的学生学号：

代码：

select StudentID,count(CourseID) 选修课程数 from grade group by StudentID having count(CourseID)>=3;

查询结果截图：

(4)统计各个班的男生和女生人数：

代码：

select ClassID,count(Sex='男') 男生人数,count(Sex='女') 女生人数 from student group by ClassID;

查询结果截图：

4、在xkgl数据库中进行如下的连接查询。

(1)查询课程名及该课程的得分情况：

代码：

select CourseName,StudentID,Grade from grade join course on grade.CourseID=course.CourseID;

查询结果截图：

(2)查询教师姓名及其所教授的课程名：

代码：

select distinct teacher.Teachername,course.CourseName from teacher inner join schedule on teacher.TeacherID=schedule.TeacherID inner join course on schedule.CourseID=course.CourseID;

查询结果截图：

(3)查询比‘刘芳’老师年龄小的教师信息：

代码：

select t2.* from teacher t1,teacher t2 where t1.Brith

查询结果截图：

(4)查询全部教师的授课情况,包括没有授课的老师：

代码：

select t.Teachername,CourseID,ClassID from schedule sc right join teacher t on sc.TeacherID=t.TeacherID;

查询结果截图：

(5)查询既选修了Dp010001又选修了Dp010004号课程的学生的学号、姓名：

代码：

select s.StudentID,s.StudentName from student s,schedule sc1,schedule sc2 where s.ClassID=sc1.ClassID and s.ClassID=sc2.ClassID and sc1.CourseID='Dp010001' and sc2.CourseID='Dp010004';

查询结果截图：

5、在xkgl数据库中进行如下的嵌套查询。

(1)查询“计算机系”的全部学生信息：

代码：

select student.* from student join class on student.ClassID=class.ClassID join department on department.DepartmentID=class.DepartmentID and department.DepartmentName='计算机系';

查询结果截图：

(2)查询Dp010001课程中最低分的学生信息：

代码：

select s.* from student s join grade g on s.StudentID=g.StudentID where g.CourseID='Dp010001' order by g.Grade asc limit 0,1;

检查结果截图：

(3)查询Dp010001课程中成绩未达到该门课程平均分的选课信息：

代码：

select * from grade g where g.Grade

查询结果截图：

(4)查询Cs010901班比Cs010902班年龄都大的学生信息：

代码：

select * from student where birth < all(select birth from student where ClassID='Cs010902') and ClassID='Cs010901';

查询结果截图：

(5)查询选修了Dp010001课程而没有选修Dp010002号课程的学生学号：

代码：

select StudentID from grade where CourseID='Dp010001' and studentid not in (select studentid from grade where courseid='Dp010002');

查询结果截图：

(6)查询“大学英语”成绩不及格的学生人数是多少？

代码：

select count(*) 不及格人数 from grade,course where grade.CourseID=course.CourseID and CourseName='大学英语(一)' and grade.Grade<60;

查询结果截图：

(7)查询选修了Dp010001号课程的学生的学号和成绩

代码：

select StudentID,Grade from grade where CourseID='Dp010001';

检查结果截图：

(8)查询结果按成绩降序排序，成绩相同按学号升序排序。

代码：

select * from grade order by Grade desc,StudentID asc;

检查结果截图：

(9)查询course表中的最大学分和最小学分的课程名。(用order by子句)

代码：

select (select coursename from course order by credit desc limit 1) as 最高学分课程名 ,(select coursename from course order by credit asc limit 1) as 最低学分课程名 from course limit 1;

检查结果截图：

(11)统计各个班的学生人数。

代码：

select s.ClassID,(select count(student.StudentID) from student where student.ClassID=s.ClassID) as 人数 from class s;

检查结果截图：

(12)查询不同职称不同性别教师人数。

代码：

select distinct t.Profession,(select count(TeacherID) from teacher where Profession=t.Profession and Sex='男') as 男教师人数,(select count(TeacherID) from teacher where Profession=t.Profession and Sex='女') as 女教师人数 from teacher t;

检查结果截图：

(13)查询和‘刘芳’老师同职称的教师信息。

代码：

select t1.* from teacher t1,teacher t2 where t1.Profession=t2.Profession and t2.Teachername='刘芳';

检查结果截图：

(14)合并显示学生人数及选课学生人数。

代码：

select '学生人数',count(s.StudentID) 人数 from student s

UNION

select '选修人数',count(b.StudentID) 人数 from (select StudentID from grade g group by StudentID)

检查结果截图：

(15)合并显示教师中的男性教师和有教授职称的教师。

代码：

select count(t.TeacherID) from teacher t where Sex='男'

union

select count(te.TeacherID) from teacher te where Profession='教授';

检查结果截图：

(16)计算机系的平均成绩为多少？

代码：

select avg(g.Grade) 计算机系平均分 from grade g,student s,class c1,department d where d.DepartmentID=c1.DepartmentID and s.StudentID=g.StudentID and c1.ClassID=s.ClassID and d.DepartmentName='计算机系';

检查结果截图：

(17)查询 “计算机系”的班级信息。

代码：

select c.* from class c where c.DepartmentID=(select d.DepartmentID from department d where d.departmentname='计 算机系');

检查结果截图：

(18)用EXISTS方式查询选修了Dp010001的学生学号和姓名

代码：

select s.StudentID,s.StudentName from student s where exists(select * from grade g where g.courseid='Dp010001' and g.studentid=s.studentid);

检查结果截图：

(19)查询计算机系选修了5门以上课程的学生的学号和姓名。

代码：

select s.StudentID,s.StudentName from department d join class c on d.DepartmentID=c.DepartmentID join student s on s.ClassID=c.ClassID join grade g on g.StudentID=s.StudentID where d.DepartmentName='计算机系' group by s.StudentID having count(g.CourseID)>5;

检查结果截图：

【分析与思考】

(1) LIKE的通配符有哪些？分别代表什么含义？

通配符

功能

%

匹配零个或多个字符组成的任意字符串

_(下划线)

匹配任意一个字符

(2)知道学生的出生日期，如何求出其年龄？

答：代码：year(now())-year(brith);

(3) IS能用“=”来代替吗？如何周全地考虑“空数据”的情况？

答：不可以的，is是用来判断null的，比如 remark is null 或者remark is not null，=是用来直接比较值的；= 用在2种情况下：一是判断值是否相等 where id=1；二是用于赋值set id=1；用 is null去判断是否真的是空；

(4)关键字ALL和DISTINCT有什么不同的含义？关键字ALL是否可以省略不写？

答：ALL表示所有的字段，有重复的2条，2 条都显示DISTINCT表示如果有重复的，只取一条；All关键字是可以省略，但是省略all关键字在select子句和union子句会产生很不一样的效果

(5)聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？

答：聚集函数 可用于 select和having by中，where 是在 聚集函数计算前 筛选数据，having by 是在聚集函数计算后筛选数据；

(6) WHERE子句与HAVING子句有何不同？

答：1、作用对象不同：where子句作用于表(或视图)中得行,而having子句作用于形成的组；where子句限定查找的行，having子句限定查找的组；

2、执行顺序不同：若查询语句中同时有where子句和Having子句，执行时先去掉不满足where条件的行，然后分组，分组后再去掉不满足having条件的组；

3、where自居不能直接使用聚合函数，having子句条件中可以包含聚合函数

(7) count(*)、count(列名)、count(distinct 列名)三者的区别是什么？通过一个实例说明。

答：count(*)：是统计所有的个数不管是否重复，是否为空值；

count(列名)：不统计为空值的行；

count(distinct 列名)：统计不重复的行数为多少；

比如：

Num1

1

2

null

2

Count(*)=4；

Count(Num1)=3；

Count(distinct Num1)=3；

(8)内连接与外连接有什么区别？

答：内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。

外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。

右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。

全外连接：左外连接 union 右外连接。

(9) “=”与IN在什么情况下作用相同？

答：当in的候选值只有一个的时候；

如有什么问题欢迎下方留言讨论！！