数据查询

一、实验目的

1. 掌握使用SQL的SELECT语句进行基本查询的方法。
2. 掌握使用SELECT语句进行条件查询的方法。
3. 掌握SELECT语句的GROUP BY、ORDER BY以及UNION子句的作用和使用方法。
4. 掌握嵌套查询的方法。
5. 掌握连接查询的操作方法。
   二、实验内容
6. SQL的SELECT语句进行基本查询的方法。
7. SELECT语句进行条件查询的方法。
8. SELECT语句的GROUP BY、ORDER BY以及UNION子句的作用和使用方法。
9. SELECT嵌套查询的方法。
10. SELECT连接查询的操作方法。
    三、实验步骤
11. 在studentsdb数据库中使用SELECT语句进行基本查询。
    （1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。
    SELECT 学号,姓名,出生日期
    from student_info
    

（2）查询student_info表学号为 0002的学生的姓名和家庭住址。
SELECT 姓名,家庭住址
from student_info
WHERE 学号=0002

（3）查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。
SELECT 姓名,出生日期
from student_info
WHERE 出生日期>=‘1995-01-01’ AND 性别=‘女’

2. 使用select语句进行条件查询。
   （1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。
   SELECT 学号,课程编号,分数
   from grade
   WHERE 分数 BETWEEN 70 AND 80
   

（2）在grade表中查询课程编号为0002的学生的平均成绩。
SELECT AVG(分数) AS 平均成绩
From grade
WHERE 课程编号=‘0002’

（3）在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。
SELECT count() 人数
from grade
WHERE 课程编号=‘0003’
SELECT count() 人数
from grade
WHERE 课程编号=‘0003’ AND 分数>0

（4）查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。
SELECT 姓名,出生日期
from student_info
ORDER BY 出生日期 desc

（5）查询所有姓名“张”的学生的学号和姓名。
SELECT 学号,姓名
from student_info
WHERE 姓名 LIKE ‘张%’

3.对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序。
SELECT 学号,姓名,性别,出生日期,家庭住址
from student_info
ORDER BY 性别 asc,学号 desc

4.使用GROUP BY子句查询grade表中各个学生的平均成绩。
SELECT 学号,AVG(分数) AS 平均成绩
from grade
GROUP BY 学号

5.使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。
SELECT 学号,姓名
from student_info
WHERE 姓名=‘刘%’ UNION
SELECT 学号,姓名
from student_info
WHERE 姓名=‘张%’

6. 嵌套查询
   （1）在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
   SELECT 姓名,出生日期
   from student_info
   WHERE 性别=(SELECT 性别 FROM student_info WHERE 姓名=‘刘东阳’)
   

（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。
SELECT a.学号,姓名,性别,课程编号
from student_info a, grade b
WHERE a.学号=b.学号 AND 课程编号 IN (‘0002’,‘0005’)

（3）使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。
SELECT 学号,课程编号,分数
from grade
WHERE 学号=‘0001’ AND 分数>ANY(SELECT 分数 FROM grade WHERE 学号=‘0002’)

（4）使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
SELECT 学号,课程编号,分数
from grade
WHERE 学号=‘0001’ AND 分数>ALL(SELECT 分数 FROM grade WHERE 学号=‘0002’)

7. 连接查询
   （1）查询分数在80-90范围内的学生的学号、姓名、分数。
   SELECT s.学号,s.姓名,分数
   from student_info s, grade g
   WHERE s.学号=g.学号 AND g.分数 BETWEEN 80 AND 90
   

（2）使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。
SELECT s.学号,s.姓名,课程名称,分数
From student_info s
INNER JOIN grade g ON g.学号=s.学号
I NNER JOIN curriculum c ON g.课程编号=c.课程编号
WHERE c.课程名称=‘数据库原理及应用’

（3）查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。
SELECT s.学号,s.姓名,课程编号,分数
from student_info s
LEFT JOIN(SELECT a.学号,课程编号,分数 FROM grade a,
(SELECT 学号,MAX(分数)最高分 FROM grade g GROUP BY 学号)b
WHERE a.学号=b.学号 AND a.分数=b.最高分)c ON s.学号=c.学号

（4）使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
SELECT s.学号,s.姓名,SUM(分数)
from student_info s
LEFT JOIN grade g ON s.学号=g.学号
LEFT JOIN curriculum c ON g.课程编号=c.课程编号
GROUP BY s.学号,s.姓名

（5）为grade表添加数据行：学号为0004、课程编号为0006、分数为76。使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。
INSERT INTO GRADE VALUES(‘0004’,‘0006’,76);
SELECT GRADE.课程编号,课程名称,COUNT(学号) AS 选修人数 FROM CURRICULUM
RIGHT OUTER JOIN GRADE ON GRADE.课程编号=CURRICULUM.课程编号
GROUP BY 课程编号;

四、实验思考

1. 查询所有没有选修课程的学生的学号、姓名。
   SELECT 学号,姓名
   FROM student_info
   WHERE(学号 NOT IN (SELECT 学号 FROM grade))
   

2.查询选修课程的人数。
SELECT COUNT(DISTINCT 学号)
FROM student_info;

3.查询选课人数大于等于3人的课程编号、课程名称、人数。
SELECT 课程编号,课程名称,COUNT(课程编号)AS 人数
FROM curriculum
GROUP BY 课程编号,课程名称
HAVING COUNT(*)>=3;

4. 在查询的FROM子句中实现表与表之间的连接有哪几种方式？对应的关键字分别是什么？
   3种
   内连接 inner outer join
   左连接 left outer join
   右连接 right outer join
   5、LIKE的通配符有哪些？分别代表什么含义？
   %任意多个字符
   _单个字符
   6、知道学生的出生日期，如何求出其年龄？
   YEAR（CURDDATE）-YEAR（sbirthday）
   7、IS能用“=”来代替吗？
   IS不能用“=”来代替。
   8、聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？
   聚集函数可作为列标识符出现在SELECT子句的目标列、HAVING子句的条件中或ORDER BY子句中。
   9、WHERE子句与HAVING子句有何不同？
   WHERE条件与HAVING条件的区别在于作用对象的不同。HAVING条件作用于结果组，选择满足条件的结果组；而WHERE条件作用于被查询的表，从中选择满足条件的记录。
   10、指定一个较短的别名有什么好处？
   让更长的表名看起来更加简介，或者让显示时名字变成自定义的名称，易懂。
   11、 内连接与外连接有什么区别？
   内连接：进行连接的两个表对应的相匹配的字段完全相同的连接。
   左外连接：两个表进行左连接时会返回左边表中的所有的行和右边表中与之相匹配的列值没有相匹配的用空值代替。
   右外连接：两个表进行右连接时会返回右边表中的所有的行和左边表中与之相匹配的列值没有相匹配的用空值代替。
   12、“=”与IN在什么情况下作用相同？
   IN里面的数据只有一个时。
   13、子查询一般分为几种？
   子查询一般分两种：嵌套子查询和相关子查询。
   14、 相关子查询的执行过程是什么？
   A.子查询为外部查询的每一个元祖（行）执行一次，外部查询将子查询引用列的值传给子查询；
   B.如果子查询的任何行与其匹配，外部查询则取此次行放入结果；
   C.再回到A，直到处理完外部表的每一行。

五、实验总结
1、收获
在单表查询中，查询只涉及一个表的查询；在连接查询中，查询同时涉及两以上的表，包含等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。
2、存在的问题
在单表查询中，在去掉表的重复行时，未用指定的DISTINCT关键字。
在连接查询中，未分清表与表的联系。
忘记如何插入；使用左右链接时总出现错误