MySQL之DQL万字解析(涵盖所有查询!!!)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL之DQL万字解析(涵盖所有查询!!!)

今日金句

生活中有80%的痛苦是来自上班,但如果不上班,就会有100%的痛苦来自没钱



前言

  在上一篇博客中,我们详细介绍了MySQL的基础内容(MySQL简介与历史、安装与卸载、数据类型数据模型数据引擎以及账号管理和DDL操作数据库与表)。

  在本篇博客中,我们来介绍MySQL的核心内容(其实也是所有数据库的核心内容)——查询操作,也就是DQL(Data Query Language数据查询语言) 。

  上图是我们在自定义MVC进阶中所做的一个简单的页面,展示书籍数据。而上图中的是最基本的查询效果,那么数据库其实是很多的,不可能在将所有的数据在一页进行全部展示,而页面上会有分页展示的效果,如下:

  当然上图中的难度字段当我们点击也可以实现排序查询操作。从这个例子我们就可以看出,对于数据库的查询时灵活多变的,需要根据具体的需求来实现,而查询操作也是数据库最重要的操作。

  接下来我们先介绍查询的完整语法:

SELECT 
    字段列表
FROM 
    表名列表 
WHERE 
    条件列表
GROUP BY
    分组字段
HAVING
    分组后条件
ORDER BY
    排序字段
LIMIT
    分页限定

  为了给大家演示查询的语句,我们需要先准备表及一些数据:

-- 创建tb_stu表
CREATE TABLE tb_stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);
-- 添加数据
INSERT INTO tb_stu(id,NAME,age,sex,address,math,english,hire_date) 
VALUES 
(1,'李白',55,'男','杭州',66,78,'2000-09-01'),
(2,'王昭君',45,'女','深圳',98,87,'2001-09-01'),
(3,'刘备',55,'男','香港',56,77,'1999-09-02'),
(4,'孙尚香',20,'女','湖南',76,65,'1997-09-05'),
(5,'孙策',20,'男','湖南',86,NULL,'2001-09-01'),
(6,'大乔',57,'男','香港',99,99,'2002-09-01'),
(7,'小乔',22,'女','香港',99,99,'2002-09-02'),
(8,'周瑜',18,'男','南京',56,65,'1999-09-03');

一、基础查询

1.1 语法

  • 查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据
  • 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
  • 起别名
AS: AS 也可以省略

1.2 实例

  • 查询name、age两列
select name,age from tb_stu;

  • 查询所有列的数据,列名的列表可以使用*替代
select * from tb_stu;

  • 查询地址信息
select address from tb_stu;
  • 执行上面语句结果如下:

   从上面的结果我们可以看到有重复的数据,我们也可以使用 distinct 关键字去重重复数据。

  • 去除重复记录
select distinct address from tb_stu;

  • 查询姓名、数学成绩、英语成绩。并通过as给math和english起别名(as关键字可以省略
select name,math as 数学成绩,english as 英文成绩 from tb_stu;
select name,math 数学成绩,english 英文成绩 from tb_stu;

二、条件查询

2.1 语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;
  • 条件

  条件列表可以使用以下运算符

2.2 条件查询实例

  • 查询年龄大于等于20岁的学员信息
select * from tb_stu where age >= 20;
  • 查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
select * from tb_stu where age >= 20 &&  age <= 30;
select * from tb_stu where age >= 20 and  age <= 30;

上面语句中 && 和 and 都表示并且的意思,建议使用 and

也可以使用 between … and 来实现上面需求

select * from tb_stu where age BETWEEN 20 and 30;
  • 查询年龄不等于18岁的学员信息
select * from tb_stu where age != 18;
select * from tb_stu where age <> 18;

  • 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
select * from tb_stu where age = 18 or age = 20 or age = 22;
select * from tb_stu where age in (18,20 ,22);
  • 查询英语成绩为 null的学员信息
    null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not
select * from stu where english = null; -- 这个语句是不行的
select * from stu where english is null;
select * from stu where english is not null;

2.3 模糊查询实例

模糊查询使用like关键字,可以使用通配符进行占位:

(1)_ : 代表单个任意字符

(2)% : 代表任意个数字符

  • 查询姓’孙’的学员信息
select * from tb_stu where name like '孙%';
  • 查询第二个字是’白’的学员信息
select * from tb_stu where name like '_白%';
  • 查询名字中包含 ‘乔’ 的学员信息
select * from tb_stu where name like '%乔%';

三 、排序查询

3.1 语法

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

  上述语句中的排序方式有两种,分别是:

  • ASC : 升序排列 (默认值)
  • DESC : 降序排列

注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序

3.2 实例

  • 查询学生信息,按照年龄升序排列
select * from tb_stu order by age ;
  • 查询学生信息,按照数学成绩降序排列
select * from tb_stu order by math desc ;
  • 查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
select * from tb_stu order by math desc , english asc ;

四 、聚合函数

4.1 概念

   将一列数据作为一个整体,进行纵向计算。

  如何理解呢?假设有如下表

  现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。

4.2 聚合函数分类

函数名 功能
count(列名) 统计数量(一般选用不为null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值

4.3 聚合函数语法

SELECT 聚合函数名(列名) FROM 表;

注意:null 值不参与所有聚合函数运算

4.4 实例

  • 统计班级一共有多少个学生
select count(id) from tb_stu;
select count(english) from tb_stu;
  • 上面语句根据某个字段进行统计,如果该字段某一行的值为null的话,将不会被统计。所以可以用count(*) 来实现。* 表示所有字段数据,一行中也不可能所有的数据都为null,所以建议使用 count(*)
select count(*) from tb_stu;
  • 查询数学成绩的最高分
select max(math) from tb_stu;
  • 查询数学成绩的最低分
select min(math) from tb_stu;
  • 查询数学成绩的总分
select sum(math) from tb_stu;
  • 查询数学成绩的平均分
select avg(math) from tb_stu;
  • 查询英语成绩的最低分
select min(english) from tb_stu;

五、分组查询

5.1 语法

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义

5.2 实例

  • 查询男同学和女同学各自的数学平均分
select sex, avg(math) from tb_stu group by sex;

注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义

select name, sex, avg(math) from tb_stu group by sex;  -- 这里查询name字段就没有任何意义
  • 查询男同学和女同学各自的数学平均分,以及各自人数
select sex, avg(math),count(*) from tb_stu group by sex;
  • 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
select sex, avg(math),count(*) from tb_stu where math > 70 group by sex;
  • 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2个的
select sex, avg(math),count(*) from tb_stu where math > 70 group by sex having count(*)  > 2;

where 和 having 区别:

  • 执行时机不一样:where 是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
  • 可判断的条件不一样:where 不能对聚合函数进行判断,having 可以。

六、分页查询

  如下图所示,大家在很多网站都见过类似的效果,如京东、百度、淘宝等。分页查询是将数据一页一页的展示给用户看,用户也可以通过点击查看下一页的数据。

  接下来我们先说分页查询的语法。

6.1 语法

SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;

注意: 上述语句中的起始索引是从0开始

6.2 实例

  • 从0开始查询,查询3条数据
select * from tb_stu limit 0 , 3;
  • 每页显示3条数据,查询第1页数据
select * from tb_stu limit 0 , 3;
  • 每页显示3条数据,查询第2页数据
select * from tb_stu limit 3 , 3;
  • 每页显示3条数据,查询第3页数据
select * from tb_stu limit 6 , 3;

  从上面的练习推导出起始索引计算公式:

起始索引 = (当前页码 - 1) * 每页显示的条数

七、多表查询

  多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。

  现有一张员工表emp和一张部门表dept

# 创建部门表
  CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );
  # 创建员工表
  CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键,关联部门表(部门表的主键)
    );
  -- 添加部门数据
  INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
  -- 添加员工数据
  INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
  ('孙悟空','男',7200,'2013-02-24',1),
  ('猪八戒','男',3600,'2010-12-02',2),
  ('唐僧','男',9000,'2008-08-08',2),
  ('白骨精','女',5000,'2015-10-07',3),
  ('蜘蛛精','女',4500,'2011-03-14',1),
  ('小白龙','男',2500,'2011-02-14',null);

  执行下面的多表查询语句

select * from emp , dept;  -- 从emp和dept表中查询所有的字段数据

 

结果如下:

  从上面的结果我们看到有一些无效的数据,如 孙悟空 这个员工属于1号部门,但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据,

select * from emp , dept where emp.dep_id = dept.did;

  执行后结果如下:

  • 连接查询
  • 内连接查询 :相当于查询AB交集数据
  • 外连接查询
  • 左外连接查询 :相当于查询A表所有数据和交集部门数据
  • 右外连接查询 : 相当于查询B表所有数据和交集部分数据
  • 子查询

7.1 内连接查询

7.1.1 语法

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

内连接相当于查询 A B 交集数据

7.1.2 实例

  • 隐式内连接
SELECT
  *
FROM
  emp,
  dept
WHERE
  emp.dep_id = dept.did;
  • 执行上述语句结果如下:

  • 查询 emp的 name, gender,dept表的dname
SELECT
  emp. NAME,
  emp.gender,
  dept.dname
FROM
  emp,
  dept
WHERE
  emp.dep_id = dept.did;
  • 执行语句结果如下:

    上面语句中使用表名指定字段所属有点麻烦,sql也支持给表指别名,上述语句可以改进为
SELECT
  t1. NAME,
  t1.gender,
  t2.dname
FROM
  emp t1,
  dept t2
WHERE
  t1.dep_id = t2.did;
  • 显式内连接
select * from emp inner join dept on emp.dep_id = dept.did;
-- 上面语句中的inner可以省略,可以书写为如下语句
select * from emp  join dept on emp.dep_id = dept.did;
  • 执行结果如下:

7.2 外连接查询

7.2.1 语法

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

左外连接:相当于查询A表所有数据和交集部分数据

右外连接:相当于查询B表所有数据和交集部分数据

7.2.2 实例

  • 查询emp表所有数据和对应的部门信息(左外连接)
select * from emp left join dept on emp.dep_id = dept.did;
  • 执行语句结果如下:

    结果显示查询到了左表(emp)中所有的数据及两张表能关联的数据。
  • 查询dept表所有数据和对应的员工信息(右外连接)
select * from emp right join dept on emp.dep_id = dept.did;
  • 执行语句结果如下:

    结果显示查询到了右表(dept)中所有的数据及两张表能关联的数据。
    要查询出部门表中所有的数据,也可以通过左外连接实现,只需要将两个表的位置进行互换:
select * from dept left join emp on emp.dep_id = dept.did;
  • 执行语句结果如下:

7.3 子查询

7.3.1 概念

   查询中嵌套查询,称嵌套查询为子查询。

   什么是查询中嵌套查询呢?我们通过一个例子来看:

   需求:查询工资高于猪八戒的员工信息。

   来实现这个需求,我们就可以通过二步实现,第一步:先查询出来 猪八戒的工资

select salary from emp where name = '猪八戒'

   第二步:查询工资高于猪八戒的员工信息

select * from emp where salary > 3600;

   第二步中的3600可以通过第一步的sql查询出来,所以将3600用第一步的sql语句进行替换

select * from emp where salary > (select salary from emp where name = '猪八戒');

   这就是查询语句中嵌套查询语句。

  • 子查询根据查询结果不同,作用不同
  • 子查询语句结果是单行单列,子查询语句作为条件值,使用 = != > < 等进行条件判断
  • 子查询语句结果是多行单列,子查询语句作为条件值,使用 in 等关键字进行条件判断
  • 子查询语句结果是多行多列,子查询语句作为虚拟表

7.3.2 实例

  • 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息
-- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';
select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');
  • 查询入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息
-- 查询入职日期是 '2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11' ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;

好啦,本篇分享就到此为止!如果你看完本篇文章有所收获,请不要忘记点个赞,或者留下你的思考与问题,祝你变得更强!!!

目录
相关文章
|
5天前
|
SQL 关系型数据库 MySQL
MySQL查询语句优化的十个小技巧!
查询语句的优化是SQL效率优化的一个方式,可以通过优化sql语句来尽量使用已有的索引,避免全表扫描,从而提高查询效率。最近在对项目中的一些sql进行优化,总结整理了一些方法。
10 1
|
26天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
43 0
|
28天前
|
SQL 关系型数据库 MySQL
MySQL - 一文解析 SQL 的执行顺序
MySQL - 一文解析 SQL 的执行顺序
|
4天前
|
SQL 存储 关系型数据库
【MySQL 数据库】1、MySQL 的 DDL、DML、DQL 语句
【MySQL 数据库】1、MySQL 的 DDL、DML、DQL 语句
34 0
|
1月前
|
存储 SQL 关系型数据库
MySQL - 深入解析MySQL索引数据结构
MySQL - 深入解析MySQL索引数据结构
|
3天前
|
关系型数据库 MySQL
Mysql基础第二十天,组合查询
Mysql基础第二十天,组合查询
12 0
Mysql基础第二十天,组合查询
|
4天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
43 0
|
7天前
|
SQL 存储 关系型数据库
Mysql内核查询成本计算
Mysql内核查询成本计算
|
8天前
|
SQL 消息中间件 关系型数据库
Flink查询问题之每秒入库到mysql数量很少如何解决
Apache Flink是由Apache软件基金会开发的开源流处理框架,其核心是用Java和Scala编写的分布式流数据流引擎。本合集提供有关Apache Flink相关技术、使用技巧和最佳实践的资源。
|
9天前
|
关系型数据库 MySQL 分布式数据库
PolarDB for MySQL数据库外网连接解析失败的原因
【2月更文挑战第5天】PolarDB for MySQL数据库外网连接解析失败的原因
52 8

推荐镜像

更多