MySQL（4）— 数据查询

四、数据查询（DQL）

4-1、查询指定字段

select 完整 语法：

select [distinct] 字段名1 [as] [别名],字段名2 [as] [别名]
from 表1
[ left | right | inner join 表2] on 两表有关联的连接点 -- 连表查询
[where ... ] 		-- 选择条件
[group by 字段名 ]   -- 根据 字段名进行分组
[having ... ] 	    -- 对分组后的结果列，再进行筛选
[order by 字段名 asc|desc ]  -- 对查询结果根据 字段名 排序，默认asc升序
[limit startIndex,pageSize ] -- 对查询结果分页，默认从 0 开始。

别名 [as] ，拼接函数 concat( a,b )，分页查询 limit a,b

-- 起别名  select 字段 [as] 别名 from 表
SELECT studentNo 学号, studentName 学生姓名 FROM student

-- 函数 concat(a,b) 拼接
SELECT CONCAT('姓名：',studentName) FROM student

-- 分页查询 limit a,b [a,a+(b-1)]
SELECT CONCAT('姓名：',studentName) FROM student LIMIT 1,5 ![](E:\Program Files (x86)\Typora_Notes\MYSQL-学习笔记.assets\1587904975972.png)

查询姓名，拼接为 ('姓名'：xx ) 格式 ，结果列取别名为 '我取的别名'，且显示 4-（4+8-1）之间的数据，

去重 distinct

select distinct `studentNo` from result

从考试表中，查询参加了考试（至少一门）的学生学号

\Typora_Notes\MYSQL-学习笔记.assets\1587905171591.png)

数据库的列（表达式）

-- 查询系统版本
SELECT VERSION()

-- 计算
SELECT 100*3 AS 计算结果 

-- 查询自增的步长
SELECT @@auto_increment_increment

数据库中的表达式：函数，计算表达式，系统变量，文本值，表的列，null...

select 表达式 from 表

4-2、where条件子句

逻辑判断

运算符	含义	描述
and , &&	与	一假则假
or , ||	或	一真即真
not , !	非	真假取反

-- 查询任意有一门成绩90及以上的学号和学生姓名
SELECT DISTINCT s.studentNo 学号,studentName 姓名 
FROM student s,result WHERE studentResult >= 90

-- 查询课程编号1，或者2中成绩在70-85 之间的学号和学生姓名
SELECT DISTINCT s.studentNo 学号,studentName 姓名
FROM student s,result WHERE studentResult BETWEEN 70 AND 85

-- 查询除了1号学生之外的学生成绩
SELECT * FROM result WHERE NOT studentNo = 1

模糊查询

运算符	语法	描述
like	name like '王%' （姓王的）	模糊查询
in	id in (1,2,3) （id 在 1,2,3 之间）	在集合中
is null ，is not null	phone is not null（手机号不为空）	是否为空
between .. and ..	score between 80 and 100 (分数在区间 [80,100] 之间的 )	是否在区间

-- like 模糊查询, % : 任一字符,  _ : 单个字符
-- 姓关谷，且后面只有2个字的学生信息
SELECT * FROM student WHERE studentName LIKE '关谷__'

-- 查询名字里，带有'海'的学生信息
SELECT * FROM student WHERE studentName LIKE '%海%'

-- 查询学号在 1,2,3 之间的学生信息
SELECT * FROM student WHERE studentNo IN (1,2,3)

-- 查询生日为null的学生信息
SELECT * FROM student WHERE bornDate IS NULL

4-3、连表查询

join ... on ...

连接查询语法	含义
inner A jion B on a.xx = b.xx	查询A,B两表公共的信息
left [outer] A join B on a.xx = b.xx	查询A,B两表公共的信息，包括A中和B没关联的信息
right [outer] A join B on a.xx = b.xx	查询A,B两表公共的信息，包括B中和A没关联的信息

思路：

/*
	1 分析需求，查询的字段来自哪几张表?
	2 确定使用哪种连接查询？（xxx join）
	3 确定 连接点（on） 和 判断的条件（where）
*/

举例如下：

内联：查询 A B 表公共的信息 ==> inner A join B on

-- 查询学生id，学生姓名和所在年级
SELECT studentNo,studentName,grade_name
FROM student s INNER JOIN grade g
ON s.`gradeId` = g.`grade_id`

结果视图：

左连接：可在 A B 表连接查询显示信息时，查出 A 表里所有的内容，即包括和 B 表没有关联的信息。

==> left A join B on

左连接示例1： 查询所有学生的，以及他在的年级，包括年级为null的学生！

-- 查询所有学生的，以及他在的年级，包括年级为null的学生！
--（左外连接:可查出左表里，和右表没有关联的信息。 例如此例可以查出 没有年级的学生）
SELECT DISTINCT s.* ,g.grade_name FROM
student s LEFT OUTER JOIN grade g
ON s.gradeId = g.grade_id

结果视图：

左连接示例2：查询没有分配年级的学生的

-- 查询没有分配年级的学生的
SELECT DISTINCT s.* ,g.grade_name FROM
student s LEFT JOIN grade g
ON s.gradeId = g.grade_id
WHERE s.gradeId IS NULL

结果视图：

右连接：可在 A B 表连接查询并显示信息时，查出 B 表里所有的内容，即包括和 A 表没有关联的信息。

==> rightA join B on

-- 查询所有学生的，以及显示他在的年级，包括显示没有学生的年级！
--（右外连接:可查出右表里，和左表没有关联的信息。例如此例可以 显示出没有学生的年级）
SELECT DISTINCT s.* ,g.grade_name FROM
student s RIGHT OUTER JOIN grade g
ON s.gradeId = g.grade_id

结果视图：

三表查询练习1 : 查询参加了考试的学生的学号，姓名，课程名，课程成绩，并且按照学号升序排列

思路：先查询两张表，然后再慢慢增加！

-- 查询参加了考试的学生的学号，姓名，课程名，课程成绩，并且按照学号升序排列
SELECT stu.studentNo,stu.studentName,subjectName,studentResult
FROM (student stu RIGHT JOIN result r 
ON stu.studentNo = r.studentNo)
LEFT JOIN SUBJECT sub 
ON sub.subjectNo = r.subjectNo
ORDER BY studentNo ASC 

三表查询练习2 : 查询参加了 高数4 的同学信息：学号，学生姓名，科目名，分数

-- 查询参加了 高数4 的同学信息：学号，学生姓名，科目名，分数
-- 思路：先内联查询参加了考试的学生，再查询 考试科目为数据结构的
SELECT stu.studentNo '学号' , studentName , '姓名', 
	subjectName '科目名' ,studentResult '分数'
FROM (student stu INNER JOIN result r
ON stu.studentNo = r.studentNo) INNER JOIN SUBJECT  sub 
ON sub.subjectNo = r.subjectNo
WHERE subjectName LIKE '%高等数学%4%'

自连接：把一张表拆分成两张表查询。

思路：

就好比是一张员工表，组长是员工，组员也是员工，但是组员的上级id = 组长的员工id。

所以这表其实本来可以细分为底层员工表，和管理者表。

练习：将category表，继续细分类别

SELECT a.categoryname '父栏目',b.categoryname '子栏目'	
FROM `category` a , `category` b 	-- 或者写为  FROM `category` a inner join`category` b
WHERE b.`pid` = a.`categoryid`   	--			  on b.`pid` = a.`categoryid` 
-- 还意外得出个结论: 逗号分隔其实就是隐式的内连接！

4-4、分页及排序查询

排序：根据字段名进行升降序排列。

==> order 字段名 by [ ASC / DESC ] 默认是ASC升序排列

分页：显示开始索引位置startIndex ，到往后pageSize个长度的结果。

==> limit startIndex , pageSize (不写开始索引位置，默认是从0开始)

练习：查询 java 第一学年，成绩前5的学生，且分数大于85，的学生信息（学号，姓名，课程名称，成绩）

-- 查询 java 第一学年，成绩前5的学生，且分数大于85，的学生信息（学号，姓名，课程名称，成绩）
SELECT stu.studentNo,studentName,subjectName,studentResult
FROM (student stu INNER JOIN result r 
ON stu.studentNo = r.studentNo) INNER JOIN SUBJECT sub 
ON r.subjectNo = sub.subjectNo
WHERE subjectName = 'Java第一学年' AND   studentResult >= 90
ORDER BY studentResult DESC
LIMIT 5

结果视图：

4-5、子查询和嵌套查询

练习：查询 '高等数学-1'，前五名同学的成绩信息，（学号，姓名，课程名，成绩）

连表查询 方式实现：

-- 连表查询：查询高等数学-1，前五名同学的成绩信息，（学号，姓名，课程名，成绩）
 SELECT s.`studentNo` , s.`studentName` , `subjectName`,`studentResult`
 FROM (student AS s INNER JOIN result AS r 
 ON s.studentNo = r.studentNo) INNER JOIN `subject` AS sub 
 ON r.subjectNo = sub.subjectNo
 WHERE sub.`subjectName` = '高等数学-1'
 ORDER BY studentResult DESC
 LIMIT 5

子查询 方式实现：

 -- 子查询：查询高等数学-1，前五名同学的信息，（学号，姓名，课程名，成绩）
 SELECT DISTINCT s.`studentNo` , s.`studentName`, sub.`subjectName` , r.`studentResult`
 FROM student s ,result r,`subject` sub
 WHERE s.`studentNo` IN (
	SELECT	studentNo
	FROM  result
	WHERE subjectNo = (
		SELECT	subjectNo
		FROM `subject` 
		WHERE subjectName = '高等数学-1'
	)
	ORDER BY studentResult DESC
 )
ORDER BY studentResult DESC
LIMIT 5

结果视图：

注意点：子查询里不能用外面的表别名！

4-6、分组查询

group by 字段名

having 过滤条件

注意：此处的 having 是对分组后的，属性进行筛选，相当于复试。

而where 是对还没分组的所有属性进行筛选，相当于海选，注意 where 不能使用聚合函数。

-- 查询所有科目的 最低分，最高分，平均成绩（大于90分）
SELECT subjectName 课程名, MIN(studentResult) 最低分 , MAX(studentResult) 最高分 , AVG(studentResult)平均分
FROM SUBJECT s INNER JOIN result r
ON s.`subjectNo` = r.`subjectNo`
GROUP BY s.subjectNo
HAVING 平均分 >= 80

结果视图：