mysql查询所有姓王的信息_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 \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个长度的结果。

==> limitstartIndex , 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 (

SELECTstudentNo

FROM result

WHERE subjectNo = (

SELECTsubjectNo

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

结果视图：