MySQL-04-DQL查询数据

4.1 DQL语言

DQL（Data Query Language）

  • 用于查询数据库中的数据，比如说SELECT语句
  • 属于数据库中最核心，最重要的语句，使用频率最高

SELECT语句

  • 完整语法

    SELECT (DISTINCT) 表达式 FROM 表名 
[left | right | inner join table_name2] -- 联合查询
[WHERE ...]		-- 指定结果需要满足的条件
[GROUP BY ...] 	-- 指定结果按照哪几个字段来分组
[HAVING] 		-- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] 	-- 指定查询记录按一个或多个条件排序
[LIMIT N,M] 	-- 指定从哪里开始查询和分页大小

4.2 查询指定的字段

1. 简单的查询

-- 查询所有学生信息
SELECT * FROM student;

-- 查询指定的学生信息（学号和姓名）
SELECT `StudentNo`, `StudentName` FROM student;

2. AS关键字

  • 作用

    • 给字段或者表取个别名
    • 将某次查询或计算的结果使用新名称代替

  • 示例

    -- 这里的AS用于取别名
SELECT `StudentNo` AS '学号', `StudentName` AS '姓名' FROM student AS s;

-- 使用AS，为CONCAT函数返回的结果取别名
SELECT CONCAT('姓名：',`StudentName`,'，学号：',`StudentNo`) AS '学生信息' FROM student;

    | | |

3. DISTINCT关键字

  • 作用：去重，将结果中重复的记录删除，只保留一条。通常和COUNT函数配合使用，用于统计数量

  • 示例

    SELECT  COUNT(DISTINCT `StudentNo`) AS '参加考试人数' FROM result;
/*
+--------------+
| 参加考试人数 |
+--------------+
|           18 |
+--------------+
*/

4. SELECT中的表达式

  • select中的表达式可为如下内容

    • 文本值，系统变量，NULL

    • 字段名

    • 函数，操作符，数学表达式

      。。。

  • 表达式的使用场景：

    • select后，作为要查询的内容
    • where子句中，作为查询的条件
    • order by，having等筛选条件中，用于对结果进行处理

4.3 where条件语句详解

  • 作用：用于检索表中符合条件的数据记录

  • 条件由一个或多个逻辑表达式组成，结果非真即假

  • 精确查询

    • 运算符
    操作符 语法 描述
    AND a AND b a且b
    OR a OR b a或b
    NOT NOT a 非a
    • 示例
    -- 查询所有学生的考试成绩
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM result；

-- 查询分数在90到100之间的学生学号和成绩
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM result WHERE `StudentResult` >= 90 AND `StudentResult`<=100；

SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM result WHERE `StudentResult` BETWEEN 90 AND 100；

-- 查询学号为1000号同学的成绩
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM result WHERE studentno=1000；

-- 查询除学号1000号同学以外的所有成绩
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM result
WHERE NOT studentno=1000

  • 模糊查询

    • 运算符

      操作符 语法 描述
      IS NULL a IS NULL a为空，真
      IS NOT NULL a IS NOT NULL a不为空，真
      BETWEEN a BETWEEN b AND c a在闭区间 [b,c]之间返回真
      LIKE a LIKE b a 按照 b 的格式进行匹配，返回匹配结果
      IN a IN b b是一个集合，返回a是否属于b

    • 示例

      -- LIKE关键字的匹配模式：'%' 表示0到任意数量的字符，'_' 表示1个字符
-- 查询所有姓刘的同学
SELECT `StudentNo`,`StudentName`,`Address` FROM student WHERE `StudentName` LIKE '刘%';

-- 查询姓刘的同学，且名字是两个字的同学信息
SELECT `StudentNo`,`StudentName`,`Address` FROM student WHERE `StudentName` LIKE '刘_';

-- 查询姓刘的同学，且名字是三个字的同学信息
SELECT `StudentNo`,`StudentName`,`Address` FROM student WHERE `StudentName` LIKE '刘__';

-- 查询名字中含有‘李’字的铜须
SELECT `StudentNo`,`StudentName`,`Address` FROM student WHERE `StudentName` LIKE '%李%';

-- 查询参见了科目1，科目2，科目3，科目4考试的同学
SELECT `StudentNo`,`SubjectNo`,`StudentResult` FROM result WHERE `SubjectNo` IN(1,2,3,4)

-- 查询没有填写出生日期的同学
SELECT `StudentNo`,`StudentName`,`BornDate` FROM student WHERE `BornDate` IS NULL

4.4 连表查询（join）

1. 三种join

  • SQL中的连接查询的概念图

img

PS：MySQL中没有 FULL JOIN

  • 语法

    SELECT column_name(s)
FROM table1 [INNER/LEFT/RIGHT] JOIN table2
ON table1.column_name=table2.column_name;

  • 三种join的对比

    JOIN方式 描述
    INNOR JOIN 查询两个表中的结果集中的交集
    LEFT JOIN 返回左表中符合条件的所有行，如果右表中没有符合条件的值，则用null进行填充
    RIGHT JOIN 返回右表中符合条件的所有行，如果左表中没有符合条件的值，则用null进行填充
    • 如何区分左右表？
      • 后来join上的表为右表，原来的表为左表。
      • 如果join了多个表，则每次join之后的表均为右表
        • 如果理解？在执行连接查询时，MySQL会根据连接的条件生成一张新表，如果有多个join进行连接，那么每次生成的新表即为左表

  • 演示数据

    注意图中框出来的红色数据，这两个数据在result表中是没有对应记录的，这样的话就可以很方便的看到 left join的区别

    同样的，这条数据在student表中是没有记录的，用来演示 right join

  • 示例

    -- inner join
SELECT stu.StudentNo,`StudentName`,`StudentResult`
FROM `student` AS stu INNER JOIN `result` AS res
ON stu.StudentNo = res.StudentNo;
/*
|      1017 | 赵宇航      |            66 |
|      1017 | 赵宇航      |            76 |
|      1017 | 赵宇航      |            95 |
|      1017 | 赵宇航      |            73 |
|      1017 | 赵宇航      |            82 |
|      1017 | 赵宇航      |            85 |
|      1017 | 赵宇航      |            68 |
|      1017 | 赵宇航      |            99 |
|      1017 | 赵宇航      |            76 |
+-----------+-------------+---------------+
在数据的最后可以看见没有出现张三和李四的身影
*/

-- left join
SELECT stu.StudentNo,`StudentName`,`StudentResult`
FROM `student` AS stu LEFT JOIN `result` AS res
ON stu.StudentNo = res.StudentNo;
/*
|      1017 | 赵宇航      |            76 |
|      1018 | 张三        |          NULL |
|      1019 | 李四        |          NULL |
+-----------+-------------+---------------+
在表格的数据中可以看到，由于result表中没有张三和李四的数据，所以使用NULL填充
*/

-- right join
SELECT stu.StudentNo,`StudentName`,`StudentResult`
FROM `student` AS stu RIGHT JOIN `result` AS res
ON stu.StudentNo = res.StudentNo;
/*
|      1017 | 赵宇航      |            68 |
|      1017 | 赵宇航      |            99 |
|      1017 | 赵宇航      |            76 |
|      NULL | NULL        |            32 |
+-----------+-------------+---------------+
这里可以看到，由于student表中没有id为1030同学的资料，所以使用了NULL对前两个列进行填充
*/

  • 总结：三种不同的join的主要区别在于使用NULL进行填充的字段不同。left join填充的是左表有而右表没有的数据，right join填充的是右表有而左表没有的数据，inner join不使用null进行填充数据，只返回两个表中都共有的数据

  • 如何使用各种join

    1. 先判断要从那些表中查找数据，也就是SELECT后面的字段名分别来自那些表
    2. 根据需求选择使用哪张表作为为主，这句话不好解释，一般来说，inner join是不需要考虑这个的。其他 join需要考虑一下，因为涉及到NULL填充字段的问题
    3. 判断各表之间的交叉点，也就是 on进行判断的条件点是什么
    4. 如果存在多张表，一张一张来，不要一口气吃成胖子

  • 练习

    -- 查询参加了考试的同学信息(学号，姓名，科目名，分数)
SELECT stu.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` 
FROM `student` AS stu
INNER JOIN `result` AS res ON res.`StudentNo`=stu.`StudentNo`
INNER JOIN `subject` AS sub ON sub.`SubjectNo`=res.`SubjectNo`;
/*
+-----------+-------------+--------------+---------------+
| StudentNo | StudentName | SubjectName  | StudentResult |
+-----------+-------------+--------------+---------------+
|      1000 | 周丹        | 高等数学-1   |            94 |
|      1001 | 周颖        | 高等数学-1   |            76 |
|      1002 | 杨文瑞      | 高等数学-1   |            61 |
|      1003 | 韩萌        | 高等数学-1   |            91 |
|      1004 | 刘丽侠      | 高等数学-1   |            84 |
|      1005 | 姜嘉航      | 高等数学-1   |            82 |
|      1006 | 郑嘉祥      | 高等数学-1   |            82 |
+-----------+-------------+--------------+---------------+
*/

-- 查询学院及所属的年纪(学号，姓名，年级名)
SELECT s.`StudentNo` AS '学号' ,`StudentName` AS '姓名',`GradeName` AS '年级名'
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`GradeId`=g.`GradeID`;
/*
+------+--------+--------+
| 学号 | 姓名   | 年级名 |
+------+--------+--------+
| 1000 | 周丹   | 大一   |
| 1001 | 周颖   | 大二   |
| 1002 | 杨文瑞 | 大一   |
| 1003 | 韩萌   | 大三   |
+------+--------+--------+
*/

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT `SubjectName` AS '科目名称' ,`GradeName` AS '年级名称'
FROM `subject` AS s
INNER JOIN `grade` AS g
WHERE s.`GradeID`=g.`GradeID`;
/*
+--------------+----------+
| 科目名称     | 年级名称 |
+--------------+----------+
| 高等数学-1   | 大一     |
| 高等数学-2   | 大二     |
| 高等数学-3   | 大三     |
| 高等数学-4   | 大四     |
+--------------+----------+
*/

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT stu.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` 
FROM `student` AS stu
INNER JOIN `result` AS res
ON res.`StudentNo`=stu.`StudentNo`
INNER JOIN `subject` AS sub
ON sub.`SubjectNo`=res.`SubjectNo`
WHERE sub.`SubjectName`='数据库结构-1';
/*
+-----------+-------------+--------------+---------------+
| StudentNo | StudentName | SubjectName  | StudentResult |
+-----------+-------------+--------------+---------------+
|      1000 | 周丹        | 数据库结构-1 |            94 |
|      1001 | 周颖        | 数据库结构-1 |            97 |
|      1002 | 杨文瑞      | 数据库结构-1 |            83 |
|      1003 | 韩萌        | 数据库结构-1 |            93 |
|      1004 | 刘丽侠      | 数据库结构-1 |            86 |
|      1005 | 姜嘉航      | 数据库结构-1 |            63 |
|      1006 | 郑嘉祥      | 数据库结构-1 |            64 |
|      1007 | 刘洋        | 数据库结构-1 |            90 |
|      1008 | 刘洋洋      | 数据库结构-1 |            99 |
+-----------+-------------+--------------+---------------+
*/

2. 自连接

  • 什么是自连接？

    • 自连接适用于这类情况，一个表中的行，存在某种关联关系。

  • 例如：

    -- tech表中存放的是如下信息，比如说有一个技术分类软件开发，软件开发包含web开发等内容，他们之间的关系通过pid和categoryid进行映射
CREATE TABLE `tech` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '所属主题分类id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '技术分类名称',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
-- 解释：信息技术的主题id是2，它的分类下，含有pid为2的其他技术，比如办公信息
INSERT INTO `tech` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

  • 我们要检索的信息要求如下，检索出每个分类下包含的所有技术名称，例如

    分类 技术
    信息技术 办公信息
    软件开发 web开发
    软件开发 数据库
    美术设计 ps技术

  • 实现

    SELECT father.`categoryName` AS '分类',son.`categoryName` AS '技术'
FROM `tech` AS father, `tech` AS son
WHERE son.`pid`=father.`categoryid`
/*
+----------+----------+
| 分类     | 技术     |
+----------+----------+
| 软件开发 | 数据库   |
| 软件开发 | web开发  |
| 美术设计 | ps技术   |
| 信息技术 | 办公信息 |
+----------+----------+
*/

  • 核心思想：

    • 所谓的自连接，就是自己和自己连接。要点就是把自己当成两张一模一样的表进行处理就好了。主要要使用别名嗷

4.5 分页和排序

1. 分页（LIMIT）

  • 语法

    LIMIT a,b
    • a是起始位置（从0开始），b时页面大小

2. 排序（ORDER BY）

  • 语法

    ORDER BY 字段名 DESC 	-- 降序
ORDER BY 字段名 ASC 	-- 升序

  • 注意，LIMIT和 ORDER BY的位置不能调换，LIMIT要在后

4.6 子查询

  • 什么是子查询？

    • where中嵌套的条件从具体的、明确的，变为了一个查询结果。即where后面的语句还是一个查询语句

  • 示例

    -- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 全部使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 使用子查询
SELECT s.`StudentNo`,`SubjectNo`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
WHERE r.`SubjectNo`=(
	SELECT `SubjectNo` FROM `subject`
	WHERE `SubjectName`='数据库结构-1'
)
ORDER BY `StudentResult` DESC;
/*
+-----------+-----------+---------------+
| StudentNo | SubjectNo | StudentResult |
+-----------+-----------+---------------+
|      1008 |        13 |            99 |
|      1013 |        13 |            98 |
|      1001 |        13 |            97 |
|      1000 |        13 |            94 |
|      1003 |        13 |            93 |
|      1009 |        13 |            91 |
*/

-- 查询 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 使用连接查询
SELECT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='高等数学-2' AND `StudentResult`>=80

-- 连接查询+子查询
SELECT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
WHERE `SubjectNo`=(
   SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='高等数学-2'
)
AND `StudentResult`>=80
-- 子查询嵌套子查询
SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN (
    SELECT `StudentNo` FROM `result` WHERE `StudentResult`>=80 AND `SubjectNo`=(
		SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='高等数学-2'
    )
)
/*
+-----------+-------------+
| StudentNo | StudentName |
+-----------+-------------+
|      1001 | 周颖        |
|      1002 | 杨文瑞      |
|      1005 | 姜嘉航      |
|      1010 | 赵杰        |
|      1014 | 牛恩来      |
|      1016 | 陈勉        |
|      1017 | 赵宇航      |
+-----------+-------------+
*/

-- 查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
-- 使用连接查询
SELECT s.`StudentNo`,`StudentName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='C语言-1'
ORDER BY `StudentResult`
LIMIT 0,5;

-- 连接查询+子查询
SELECT s.`StudentNo`,`StudentName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
WHERE r.`SubjectNo`=(
    SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='C语言-1'
)
ORDER BY `StudentResult` DESC
LIMIT 0,5;

-- 子查询嵌套子查询
-- 由于要求查询的所有信息，不在同一张表中，所以无法使用子查询嵌套子查询

/*
+-----------+-------------+---------------+
| StudentNo | StudentName | StudentResult |
+-----------+-------------+---------------+
|      1001 | 周颖        |            98 |
|      1000 | 周丹        |            97 |
|      1006 | 郑嘉祥      |            97 |
|      1005 | 姜嘉航      |            97 |
|      1017 | 赵宇航      |            96 |
+-----------+-------------+---------------+
*/

  • 注意

    • 使用子查询嵌套子查询时，需要满足的条件为，select后面的字段名，要求全部能在一张表中查出来

4.7 分组和过滤

  • 分组（GROUP BY）

    • GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
    • 注意
      • 如果要对分组之后的数据进行筛选，不能使用WHERE，要使用HAVING进行操作

  • 过滤（HAVING）

    • 使用HAVING的原因是，WHERE 关键字无法与聚合函数一起使用。

  • 语法

    SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

  • 示例

    -- 查询不同课程的平均分,最高分,最低分，对于平均分大于80分的进行筛选
-- 前提:根据不同的课程进行分组
SELECT `subjectname` AS '科目',AVG(`studentresult`) AS '平均分',MAX(`studentResult`) AS '最高分',MIN(`StudentResult`) AS '最低分'
FROM `result` AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80
ORDER BY 平均分 DESC
/*
+--------------+---------+---------+--------+
| 科目         | 平均分  | 最高分     | 最低分 |
+--------------+---------+---------+--------+
| C语言-1      | 84.6111 |      98 |     65 |
| C语言-2      | 84.0556 |      97 |     64 |
| 数据库结构-1 | 82.2778 |      99 |     62 |
| 数据库结构-4 | 81.7222 |      97 |     65 |
| JAVA第二学年 | 81.5556 |     100 |     60 |
| 数据库结构-3 | 81.5000 |      99 |     65 |
| C语言-4      | 81.0556 |      97 |     66 |
| 高等数学-3   | 80.3889 |      97 |     60 |
| 数据库结构-2 | 80.1667 |      99 |     64 |
+--------------+---------+---------+--------+
*/
posted @ 2020-10-20 15:32  PrimaBruceXu  阅读(69)  评论(0)  编辑  收藏  举报