【MySQL和数据库】MySQL & database 第三章：DQL学习（主要是记录的查询）

NoBlackstone

已于 2022-06-20 10:03:52 修改

阅读量201

点赞数 1

分类专栏： MySQL与数据库文章标签：数据库 mysql sql

于 2021-10-28 23:19:24 首次发布

本文链接：https://blog.csdn.net/weixin_38872229/article/details/121025513

版权

MySQL与数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

MySQL和数据库系列文章目录

进阶1：基础查询

语法：

select 查询列表, 查询列表, ... from 表名;

特点：
- 查询列表可以是：表中的字段、常量值、表达式、函数；
- 查询的结果是一个虚拟的表格；
- 查询列表用 * ，表示查询所有字段；
- 为了防止字段名和关键字重复，可以使用着重号 查询列表 表示为字段名；
起别名：

语法：
```
select 查询列表 as 别名 from 表名;
```
as 可以省略不写，别名建议用单引号 ‘’ 或者双引号 “” 包括，防止与关键词重名；
- 便于理解；
- 如果查询的字段有重名的情况，使用别名可以区分开来；

去重
语法：

select distinct 查询列表 from 表名;

加号 + 的作用
仅为运算符作用；

select 1100 + 22;		两个操作数都为数值型，作加法运算；
select '123' + 23;		其中一方为字符型，会将其转换为数值型；
select 'sad' + 23;		若字符型无法转换，则转为数值 0；
select null + 123;		只要有一个 null，结果比为 null；

字符串连接： concat(str1, str2, str3)；
```
select concat(字段名1, str, 字段名2, ...) as 新字段名 from 表名;
```
查询字段名1，str，字段名2，… 的对应数据并连接起来，并归属到新字段名的列下；
空指 null 转换：ifnull(字段, 转换值)；
```
select ifnull(字段名, 转换值) from 表名；
```
该字段下的数据如果有 null 将替换为转换值，如果不是则不变；

进阶2：条件查询

作用：根据条件过滤原始表的数据，查询到想要的数据

语法：

select 查询列表 from 表名 where 筛选条件;

分类：
- 条件表达式
  大于 >，小于 <，等于 =，不等于 != 或 <>，大于或等于 >=，小于或等于 <=；
- 逻辑表达式
  逻辑与 and 或 &&，逻辑或 or 或 ||，逻辑非 not 或 !；
- 模糊查询
  like、between and、in、is null；

like 模糊查询

select 查询列表 from 表名 where 字段名 like 通配符和字符;

通配符：
- % 任意多个字符，包含 0 个字符；
- _ 任意单个字符；

如果查询模糊是 % 或 _，可以使用转义符 \ 或者通过关键字 escape 自定义转义符

select 查询列表 from 表名 where 字段名 like '_!_%' escape '!';
#这里说明 感叹号起到转义符作用；

between … and … 模糊查询
```
select 查询列表 from 表名 where 字段名 between 临界值1 and 临界值2;
```
- 作用相当于 $\leq 字段名 \leq 临界值2$ ;
in 模糊查询
```
select 查询列表 from 表名 where 字段名 in(某一项, 某一项, ... );
```
- 判断某字段的值是否属于 in 列表中的某一项；
  - 使用 in 相当于使用逻辑或 or，可以提高语句简洁度；
  - in 列表的值类型必须一致或可以进行隐式转换；

is null 或 is not null 模糊查询

select 查询列表 from 表名 where 字段名 is null;  #该字段中值为 null 的项；
select 查询列表 from 表名 where 字段名 is not null;  #该字段中值不为 null 的项；

= 或 <> 不能用于判断 null 值；
is null 和 is not null 是搭配使用的，用来判断 null 值；

<=> 安全等于
```
select 查询列表 from 表名 where 字段名 <=> 筛选条件;
```
- 作用相当于普通等于与 is null 的结合；
- 既可以判断 null 值，也可以判断其它普通值；

进阶3：排序查询

作用：按照一定顺序对查询得到的数据进行排序；
语法：
```
select 查询列表 from 表名 【where 筛选条件】 order by 排序列表 【asc | desc】;
```
- asc 表示为升序， desc 表示为降序；
- 不写关键字，默认为升序；
- 排序列表可以是，字段名、表达式、函数、别名；
- order by 子句一般是放在查询语句的最后面，limit 子句除外；
排序列表可以使用别名：
如果在查询列表中，出现了一个别名，则排序列表也可以使用该别名；
```
select 查询列表 as 别名 from 表名 order by 别名;
```

多字段排序
可以进行多个字段的排序，用英文逗号隔开，优先级从左到右降低；

select 查询列表 from 表名 order by 排序列表1 【asc | desc】, 排序列表2 【asc | desc】;

进阶4：常见函数

作用：隐藏了实现细节，提高代码的重用性；

调用：

select 函数名(实参列表) 【from 表名】;

分类：
- 单行函数
  如：concat、length、ifnull等；
- 分组函数
  又称为统计函数、聚合函数、组函数；
  功能：做统计使用

1、单行函数

1.1、字符函数

length() 获取字节个数

select length('john'); #返回结果为 4 个字节；

concat() 拼接字符串

select concat('ss', 'cc'); #返回结果为 sscc；

upper() 、 lower() 字母大小写转化

select upper('aBc'); #返回结果为 ABC；
select lower('aBc'); #返回结果为 abc；

substr、substring 子串截取

#输入索引值 pos，截取包括索引值 pos 往后的所有子串；
select substr('abcdefg', 2); #返回结果为 bcdefg；
#输入索引值 pos 和 字符个数 len，截取从索引值 pos 开始 len 个字符；
select substr('abcdefg', 2, 3); #返回结果为 bcd；
#sql中，索引值是从 1 开始计算；

instr() 返回子串第一次出现的索引，没有则返回 0
```
select instr('iambackam', 'am'); #返回结果为 2； 
```

trim() 去除字符串的前后指定字符

select trim('   aaa    '); #返回结果为 'aaa'，默认去除首尾空格符；
select trim('b' from 'bbbbababbb'); #返回结果为 'aba'，指定首尾去除 'b' 字符；

ltrim()、rtrim() 去除字符串的左、右空格符

select ltrim('   aba   '); #返回结果为 'aba   '；
select rtrim('   aba   '); #返回结果为 '   aba'；

lpad()、rpad() 用指定字符实现左、右填充指定长度

select lpad('abc', 2, '@'); #返回结果为 'ab'；
select lpad('abc', 5, '@'); #返回结果为 '@@abc'；
select rpad('abc', 2, '@'); #返回结果为 'ab'；
select rpad('abc', 5, '@'); #返回结果为 'abc@@'；

replace() 将字符串的子串替换为指定子串

select replace('abcbackcbaback', 'back', 'red'); #返回结果为 'abcredcbared'；

1.2、数学函数

round() 四舍五入

select round(1.4);  #返回结果为 1；
select round(1.5);  #返回结果为 2；
select round(-1.4);  #返回结果为 -1；
select round(-1.5);  #返回结果为 -2；

重载类型

select round(1.456, 2);  #返回结果为 1.26，即保留小数点后两位；

ceil() 向上取整，返回 >= 该参数的最小整数

select ceil(1.02);  #返回结果为 2；
select ceil(-1.02);  #返回结果为 -1；

floor() 向下取整，返回 <= 该参数的最大整数

select floor(1.1);  #返回结果为 1；
select floor(-1.1);  #返回结果为 -2；

truncate() 截断

select truncate(1.2999, 1);  #返回结果为 1.2，即截断1位小数点后面的数值；

mod() 取余

select mod(10, 3);  #返回结果为 1；

1.3、日期函数

now() 返回当前系统的日期和时间
```
select now();
```
curdate() 返回当前系统的日期，不包含时间
```
select curdate();
```
curtime() 返回当前系统的时间，不包含日期
```
select curtime();
```

获取部分时间信息，如年、月、日、周、小时、分钟、秒

select year(now());  #返回当前年份，这里的now()可以替换成表中的数据，下面同；
select month(now());  #返回当前月份；
select monthname(now());  #返回当前月份的单词；
select day(now());  #返回当天的日份；
select week(now());  #返回当前的周数；
select hour(now());  #返回当前的小时；
select minute(now());  #返回当前的分钟数；
select second(now());  #返回当前的秒数；

时间格式符

序号		格式符		功能
1		   %Y		4位的年份
2		   %y		2位的年份
3		   %m		月份（01、02、...）
4		   %c		月份（1、2、...）
5		   %d		日（01、02、...）
6		   %H		24小时
7		   %h		12小时
8		   %i		分钟（00、01、...、59）
9		   %s		秒（00、01、...、59）

str_to_date() 字符串转时间

select str_to_date('1993 2-11', '%Y %m-%d'); #返回 '1996-02-11'；
select * from 表名 where 字段名 = str_to_date('4 3@1996', '%m %d@%Y'); #返回该字段时间为 '1996-04-03' 的所有项；

date_format() 时间转字符串

select date_format(now(), '%Y年%c月%d日'); #返回 '2021年9月02日'；

1.4、其它函数

version() 查看当前 DBMS 的版本
```
select version();
```
database() 查看当前的数据库
```
select database();
```
user() 查看当前的用户
```
select user();
```

1.5、流程控制函数

if() 类似三元运算符的效果

select if(10 > 4, 'yes', 'no');  #返回 'yes'；

case 两种语法

类似 switch 语句

case 要判断的字段或表达式
when 常量1 then 显示值1或执行语句1
when 常量2 then 显示值2或执行语句2
...
else 显示值n或执行语句n
end
#如果是执行语句，后面还需要加上分号；

类似 if else 语句

case 
when 条件1 then 显示值1或执行语句1
when 条件2 then 显示值2或执行语句2
...
else 显示值n或执行语句n
end
#如果是执行语句，后面还需要加上分号；

2、分组函数

	sum 求和
	max 最大值
	min 最小值
	avg 平均值
	count 计数

	特点：
	1、以上五个分组函数都忽略null值，除了count(*)
	2、sum和avg一般用于处理数值型
		max、min、count可以处理任何数据类型
    3、都可以搭配distinct使用，用于统计去重后的结果
	4、count的参数可以支持：
		字段、*、常量值，一般放1

	   建议使用 count(*)

进阶5：分组查询

语法：

select 查询列表, 分组函数 
from 表名
【where 筛选条件（不要分组也能进行的筛选条件）】
group by 分组的字段
having 筛选条件（分组后才能进行的筛选条件）
【order by】
#查询列表中，必须包含分组的字段；

特点：
- 可以按单个字段分组；
- 和分组函数一同查询的字段最好是分组后的字段；
- 分组筛选：
```
			|   针对的表   |  |     位置      | |    关键字    | 
分组前筛选：	    原始表			group by的前面        where
分组后筛选：	  分组后的结果集	  group by的后面       having
```
- 可以按多个字段分组，字段之间用逗号隔开；
- 可以支持排序；
- having 后可以支持别名；

进阶6：多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

连接方式分类：
- 按年代分类：
  - SQL92 标准：仅仅支持内连接；
  - SQL99 标准：支持内连接、外连接（左外、右外）、交叉连接；
- 按功能分类：
  - 内连接：
    - 等值连接；
    - 非等值连接；
    - 自连接；
  - 外连接：
    - 左外连接；
    - 右外连接；
    - 全外连接；
  - 交叉连接

1、内连接

1.1、等值连接

语法：

select 查询字段 from 表1，表2 where 表1的字段A = 表2的字段A;
#选择两个表共有的字段，该字段下相等的项将作为一项处理；
#这里的表1、表2 可以取别名，若取别名后，原表名将失去作用；

特点：
- 多表等值连接的结果为多表的交集部分；
- n 表连接，至少需要 n - 1 个连接条件；
- 多表的顺序没有要求；
- 一般都需要为表取别名，减少字符量；
- 可以搭配前面介绍的所有子句使用，如排序、分组、筛选；

1.2、非等值连接

语法：

select 查询字段 from 表1，表2 where 非等值连接条件；
#不是用 等于号 '=' 作为连接条件的，一般地，都为非等值连接条件；

1.3、自连接

语法：

select 表别名1.查询字段A， 表别名2.查询字段B 
from 表名1 as 表别名1， 表名1 as 表别名2
where 【表别名1.字段C 与 表别名2.字段D 的筛选条件】;
#表自身的连接 称为 自连接；

1.4、SQL99语法

语法

select 查询列表
from 表1 别名1
连接关键字 join 表2 别名2
on 连接条件
where 筛选条件
group by 分组条件
order by 排序字段 asc/desc;

连接关键词
- 内连接：inner
- 外连接：
  - 左外：left 【outer】
  - 右外：right 【outer】
  - 全外：full 【outer】
- 交叉连接：cross
等值连接

#表1 与 表2 等值连接
select 查询列表 
from 表1 别名1
inner join 表2 别名2
on 别名1.连接字段1 = 别名2.连接字段2；

#表1 分别与 表2、表3 等值连接
select 查询列表
from 表1 别名1
inner join 表2 别名2 on 别名1.字段11 = 别名2.字段2
inner join 表3 别名3 on 别名1.字段12 = 别名3.字段3；

#inner 可以省略不写，join单独默认为 内连接；

其余内连接类似

2、外连接

应用场景：
用于查询一个表中有，另一个表中没有的记录
特点：
- 外连接的查询结果为主表中的所有记录，
  如果从表中有和它匹配的，则显示匹配的值，
  如果从表中没有和它匹配的，则显示NULL，
  外连接查询结果等于内连接结果加上主表中有而从表中没有的记录
- 左外连接， $left\ \ join$ 左边的是主表，
  右外连接， $right\ \ join$ 右边的是主表
- 实际上，左外和右外交换两个表的顺序，可以实现同样的效果；
- 全外连接 等于内连接结果加上表1中有但表2没有的加上表2中有但表1中没有的；

3、交叉连接

特点：
相当于对两个表进行 笛卡尔乘积；

进阶7：子查询

含义：
出现在其它语句中的 select 语句，称为子查询或内查询；
（外部的查询语句，称为主查询或外查询）
分类：
- 按结果集分类：
  - 标量子查询：结果集只有一行一列；
  - 列子查询：结果集只有一列多行；
  - 行子查询：结果集有一行多列以上；
  - 表子查询：结果集一般为多行多列；
- 按子查询的位置分类：
  - select（其它语句）后面：
    仅仅支持标量子查询；
  - where 或 having 后面：
    标量子查询（单行）；
    列子查询（多行）；
    行子查询（多列多行）；
  - exists 后面（相关子查询）：
    表子查询；

1、where 或 having 后面

子查询类型包含：
- 标量子查询（结果集为单行单）
- 列子查询（结果集为多行单列）
- 行子查询（结果集为单行多列或多行多列）
特点：
- 子查询放在小括号里面；
- 子查询一般放在条件的右侧；
- 标量子查询，一般搭配着单行操作符使用，
  如 >、<、>=、<=、=、<>；
- 列子查询，一般搭配着多行操作符使用，
  如 in、ang/some、all；
- 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果；
列子查询
- 返回多行；
- 使用多行比较符
```
操作符					含义
IN、NOT IN			等于列表中的 任意一个
ANY/SOME			 和子查询返回的 某一个值比较
ALL					 和子查询返回的 所有值比较
```
- ANY 和 ALL 的区别：
  - $A > A N Y (10, 15, 20)$ 相当于 $A > M I N (10, 15, 20)$ ；
  - $A > A L L (10, 15, 20)$ 相当于 $A > M A X (10, 15, 20)$ ；

行子查询

结果集为一行多列或多行多列；
具有局限性，不常用；

例子：查询员工编号最小并且工资最高的员工信息；

select *
from employees
where
	(employee_id, salary) = (
    	select min(employee_id), max(salary)
        from employees
    );

2、select 后面

子查询类型只有标量子查询
特殊的，在碰到子查询是 $c o u n t (*)$ 时，并非直接用该结果直接填充，而是根据筛选条件填充；
```
#查询每个部门的员工数
select
	de.*,
	(
    	select
        	count(*)
        from 
        	employees as em
        where
        	em.department_id = de.department_id
    )
from
	departments as de;
```

3、from 后面

将子查询结果充当为一张表，要求必须其别名

#查询每个部门的平均工资的工资等级
SELECT
	deavg.*,
	jg.`grade_level`
FROM
	(
	SELECT
		department_id,
		AVG(salary) AS avgs
	FROM
		employees
	GROUP BY
		department_id
	) AS deavg
	INNER JOIN job_grades AS jg
	ON deavg.avgs BETWEEN jg.`lowest_sal` AND jg.`highest_sal`

4、exists 后面（相关子查询）

语法：exists（完整的查询语句）
结果是 0 或 1

判断查询结果是否有值返回

#查询没有女神的男生名字
select
	boyName
from
	boys bo
where
	not exists (
    	select 
        	boyfriend_id
       	from
        	beauty be
        where
        	bo.id = be.boyfriend_id
    )

进阶8：分页查询

应用场景
当要显示的数据，一页显示不全，需要分页提交 sql 请求；
语法

select 查询列表
from 表
【连接类型 join 表2
on 连接条件】
【where 筛选条件】
【group by 分组字段
having 分组后的筛选条件】
【order by 排序字段 asc/desc】
limit offset， size；
#offset 要显示条目的起始索引（起始索引从 0 开始）
#size 要显示的条目个数

特点
- limit 语句放在查询语句的最后面；
- 且该语句也是最后执行；
- 要显示的页数为 page，每页的条目数为 size，
  公式： $o f f s e t = (p a g e - 1) * s i z e$ ；

进阶9：联合查询

作用：将多条查询语句的结果合并成一个结果；

语法：

查询语句1
union
查询语句2
union
....

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时；
特点：
- 要求多条查询语句的查询列数必须要一致；
- 要求多条查询语句的每一列的类型和顺序最好一致；
- union 关键字默认去重，使用 union all 可以包含重复项；

进阶10：DQL语言的执行顺序

select 查询列表                #Ⅶ、根据查询列表形成虚拟结果
                              #Ⅹ、显示最终虚拟结果
from 表1                      #Ⅰ、形成表1的虚拟表
连接类型 join 表2              #Ⅱ、形成表1和表2的笛卡尔乘积表
on 连接条件                    #Ⅲ、筛选后形成新的虚拟表
where 筛选条件                 #Ⅳ、对虚拟表进行筛选
group by 分组字段              #Ⅴ、按字段进行分组
having 分组后的筛选条件         #Ⅵ、对虚拟表进行筛选
order by 排序字段              #Ⅷ、对虚拟结果进行排序
limit 偏移量， 条目数；         #Ⅸ、对虚拟结果设置分页

NoBlackstone

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MySQL和数据库】MySQL & database 第三章：DQL学习（主要是记录的查询）

MySQL和数据库系列文章目录目录MySQL和数据库系列文章目录进阶1：基础查询进阶2：条件查询进阶3：排序查询进阶4：常见函数1、单行函数1.1、字符函数1.2、数学函数1.3、日期函数1.4、其它函数1.5、流程控制函数2、分组函数进阶5：分组查询进阶6：多表连接查询1、内连接1.1、等值连接1.2、非等值连接1.3、自连接1.4、SQL99语法2、外连接3、交叉连接进阶7：子查询1、where 或 having 后面2、select 后面3、from 后面4、exists 后面（相关子查询）进阶8
复制链接

扫一扫