[大数据]MySQL查询（二）

查询练习

? 练习10：查询score表中的最高分的学生学号和课程号

? new! : 函数、子查询

##10、 查询score表中的最高分的学生学号和课程号
##子查询和函数
mysql> SELECT sno,cno FROM score WHERE degree=(SELECT MAX(degree) FROM score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
查询过程解析：

#1、找到最高分
#SELECT MAX(degree) FROM score;
#2、找到最高分的 sno和 cno
#SELECT sno,cno FROM score WHERE degree=(SELECT MAX(degree) FROM score);


##limit	n,m	   只显示n-m行
mysql> SELECT degree FROM score ORDER BY degree desc LIMIT 1;
+--------+
| degree |
+--------+
|     92 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT degree FROM score ORDER BY degree LIMIT 1;
+--------+
| degree |
+--------+
|     68 |
+--------+
1 row in set (0.00 sec)

? 练习11：查询每门课的平均成绩

? new ! : 数据分组

• group子句可以包含任意数量的列，因此可以进行嵌套
• 建立分组时，所有分组的数据都在一起计算（包括所有嵌套分组）
• group子句出现在where子句之后，order by 子句之前

##11、查询每门课的平均成绩
##查询degree的平均成绩，约束条件就是where后面的cno = '3-105'
mysql> SELECT avg(degree) FROM score WHERE cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
+-------------+
1 row in set (0.00 sec)

##可以使用group by进行分组
mysql> SELECT cno,avg(degree) FROM score GROUP BY cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

? 练习12：查询score表中至少有2名学生选修的并以3开头的课程的平均分数

? new! : 模糊查询 like、分组过滤 having

• ? 分组过滤 HAVING：

  • where在数据分组前过滤，having在数据分组后过滤；where先过滤的行，不会再having内被过滤

• ? 模糊查询 LIKE：

  • 通配符 %：代表任意数量，任意字符。 例如查询包含ava：’%ava%’
  • 通配符 _：只能代表一个字符。 例如查询’1 0’-‘1 9’：‘1 _’
  • 通配符技巧：通配符搜索慢，尽量不要放在搜索模式的开始位置；不要过度使用（效率低）。

mysql> SELECT cno,AVG(degree) FROM score 	#	指定查询的数据表为 score，同时输出数据为 cno,avg(degree)
    -> GROUP BY  cno					  #    以cno为分组
    -> having count(cno)>=2 			   #   having和where含义一样，但是作用是分组。这句表示cno相同数量大于等于2的（至少有2同学）
    -> and cno like '3%';				   #   模糊查询  搜索cno字段中所有以3开头的 ；若'30%'，就是30开头的。%是一种通配符
##两个having 语句用 and连接后就是 至少有两个同学且课程号以3开头
+-------+-------------+
| cno   | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)

## 通过select
mysql> SELECT cno,AVG(degree),count(*) FROM score
    -> GROUP BY cno
    -> HAVING count(cno)>=2
    -> AND cno like '3%';
+-------+-------------+----------+
| cno   | AVG(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+
2 rows in set (0.00 sec)

? 练习13：查询分数大于70，小于90的sno列

## AND 和 运算符
mysql> SELECT * FROM score   WHERE degree>70 AND degree<90;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
7 rows in set (0.00 sec)

## between ... and
mysql> SELECT * FROM score WHere degree between 70 AND 90;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
7 rows in set (0.00 sec)

? 练习14：查询所有学生的 name、cno和degree 列

? new! : 联结表查询

? 联结是一种机制，用来在一条select语句中关联多个表，因此称为联结。

? 使用联结语法可以联合多个表查询返回一组输出。

##前两个数据来自student表 、 成绩来自于score表
mysql> SELECT sno,sname FROM student;
+-----+-----------+
| sno | sname     |
+-----+-----------+
| 101 | 曾华      |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 李军      |
| 105 | 王芳      |
| 106 | 陆君      |
| 107 | 王尼玛    |
| 108 | 张全蛋    |
| 109 | 赵铁柱    |
+-----+-----------+
9 rows in set (0.00 sec)

mysql> SELECT sno,cno,degree FROM score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.00 sec)



##多表查询
##	通过外键查询才能保证一致性
mysql> SELECT sname,cno,degree FROM student,score WHERE student.sno = score.sno; 
+-----------+-------+--------+
| sname     | cno   | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+
9 rows in set (0.00 sec)

? 练习15：查询所有学生的sno、cname和degree列

##多表查询

##查看cname所在表
mysql> SELECT * FROM course;
+-------+-----+-----------------+
| cno   | tno | cname           |
+-------+-----+-----------------+
| 3-105 | 825 | 计算机导论      |
| 3-245 | 804 | 操作系统        |
| 6-166 | 856 | 数字电路        |
| 9-888 | 831 | 高等数学        |
+-------+-----+-----------------+
4 rows in set (0.00 sec)


##查看degree所在表
mysql> SELECT * FROM score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.01 sec)


##由上表可得，这两个表有相同字段 cno
##设计多表查询
SELECT sno,cname,degree FROM score,course 
WHERE score.cno = course.cno;

? 练习16：查询所有学生的sname、cname和degree列

##分属于三个表
mysql> select sname,cname,degree from student,course,score 	
	WHERE student.sno=score.sno 	#学生表和成绩表中的学生号相等，锁定学生成绩
	AND course.cno=score.cno;		#学生成绩确定后，再根据课程号确定当前课程分数
+-----------+-----------------+--------+
| sname     | cname           | degree |
+-----------+-----------------+--------+
| 王丽      | 计算机导论      |     92 |
| 王丽      | 操作系统        |     86 |
| 王丽      | 数字电路        |     85 |
| 王芳      | 计算机导论      |     88 |
| 王芳      | 操作系统        |     75 |
| 王芳      | 数字电路        |     79 |
| 赵铁柱    | 计算机导论      |     76 |
| 赵铁柱    | 操作系统        |     68 |
| 赵铁柱    | 数字电路        |     81 |
+-----------+-----------------+--------+
9 rows in set (0.00 sec)

##验证正确
##注意多表有相同字段，要注意显式指定该字段所属表，避免出现二义性
mysql> select sname,cname,degree,student.sno,course.cno from student,course,score WHERE st
udent.sno=score.sno AND course.cno=score.cno;
+-----------+-----------------+--------+-----+-------+
| sname     | cname           | degree | sno | cno   |
+-----------+-----------------+--------+-----+-------+
| 王丽      | 计算机导论      |     92 | 103 | 3-105 |
| 王丽      | 操作系统        |     86 | 103 | 3-245 |
| 王丽      | 数字电路        |     85 | 103 | 6-166 |
| 王芳      | 计算机导论      |     88 | 105 | 3-105 |
| 王芳      | 操作系统        |     75 | 105 | 3-245 |
| 王芳      | 数字电路        |     79 | 105 | 6-166 |
| 赵铁柱    | 计算机导论      |     76 | 109 | 3-105 |
| 赵铁柱    | 操作系统        |     68 | 109 | 3-245 |
| 赵铁柱    | 数字电路        |     81 | 109 | 6-166 |
+-----------+-----------------+--------+-----+-------+
9 rows in set (0.00 sec)

? 练习17：查询 '95531’班 学生每门课程的平均分

mysql> SELECT cno,AVG(degree) FROM score		#在分数表中操作，但是需要依赖其他表过滤一部分数据
    -> WHERE sno IN(						  #需要特定班级的学生  用WHERE过滤一遍
    ->     SELECT sno FROM student WHERE class='95031'		#过滤出 95031班学生
    -> )
    -> GROUP BY cno;						  #计算平均值，我们检索就是按照cno和AVG，所以我们分组按照cno分组
+-------+-------------+
| cno   | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+
3 rows in set (0.01 sec)

? 练习18、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录

##思路
SELECT degree FROM score;	##所有同学成绩
SELECT degree FROM score WHERE sno='109' and cno='3-105';		##109号'3-105'课程成绩
##根据109的该课程成绩，在所有成绩中比较该课程成绩
mysql> SELECT degree FROM score
    -> WHERE cno='3-105' 	#所有同学里面3-105课程
    -> AND degree>(SELECT degree FROM score WHERE sno='109' and cno='3-105');	#成绩比109号同学大的
+--------+
| degree |
+--------+
|     92 |
|     88 |
+--------+
2 rows in set (0.00 sec)

? 练习19：查询成绩高于学号为‘109’、课程号为‘3-105’的成绩的所有记录

SELECT degree FROM score WHERE sno='109' AND cno='3-105';	##学号为109的同学，课程‘3-105’的成绩
mysql> SELECT * FROM score 
	WHERE degree>
	(
        SELECT degree FROM score 
        WHERE sno='109' 
        AND cno='3-105'
    );
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
6 rows in set (0.00 sec)

? 练习20：查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列

? new!: 函数year([字段值]) ，查询这个日期字段值的年份

SELECT year(sbirthday) FROM student WHERE sno IN('108','101');	##108号和101号出生年份

mysql> SELECT sno,sname,sbirthday FROM student 
    -> WHERE year(sbirthday) 
    -> IN(SELECT year(sbirthday) FROM student WHERE sno IN('108','101'));
+-----+-----------+---------------------+
| sno | sname     | sbirthday           |
+-----+-----------+---------------------+
| 101 | 曾华      | 1977-09-01 00:00:00 |
| 105 | 王芳      | 1975-02-10 00:00:00 |
| 108 | 张全蛋    | 1975-02-10 00:00:00 |
+-----+-----------+---------------------+