Mysql基础篇之DQL语言

1. 基础查询

特点

① 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在

② 要 查询的东西 可以是常量值、表达式、字段、函数


示例：

• AS ：起别名,可以省略
  
  
• DISTINCT：去重 ， 放在 字段前面
  
  
• CONCAT(str1,str2,...) ：MySQL中用于字符串拼接
  
  
• IFNULL(expr1,expr2) ：判断某 字段 或 表达式 的值 是否为null

• 如果expr1的值不为null，返回expr1的值
• 如果为null，返回expr2的值

• ISNULL(expr) ：判断 expr 的值是否为null

• 如果expr的值不为null，返回0
• 如果为null，返回1

• NULLIF(expr1,expr2) ：判断某 字段 或 表达式 的值 是否为null

• 如果 expr1=expr2 成立，那么返回值为null，否则返回值为expr1的值

• IF(expr1,expr2,expr3) ：判断某 字段 或 表达式 的值 是否为null

• 如果expr1的值为true，则返回expr2的值，否则返回expr3的值

语法格式

SELECT 要查询的东西【FROM 表名】;

闲言碎语

• SQL 语言大小写不敏感
  
• SQL可以写在一行或者多行
  
• 关键字不能被缩写也不能分行
  
• 各子句一般要分行写
  
• 使用缩进 提高语句的可读性

2. 条件查询

语法格式

select 要查询的字段|表达式|常量值|函数 from 表 where 条件;

条件表达式

示例：

age > 18

条件运算符(示例)：

> < >= <= = != <>

逻辑表达式

示例：

age > 18 && age < 60

条件运算符(示例)：

and（&&）:两个条件如果同时成立，结果为true，否则为false

or(||)：两个条件只要有一个成立，结果为true，否则为false

not(!)：如果条件成立，则not后为false，否则为true

模糊查询

示例：

like 【 % 任意0个或多个字符; _表示任意单个字符 】

between ... and ... 【注意：包含临界值，等同于>=和<=】

in【列表中类型必须一致】

is null 、is not null 【可以判断null值】

<=> 【表示 安全等于 ，判断值是否相等，既可以和数值搭配，也可以和null搭配】

ESCAPE用法：

案例： 查询员工名称中 第二个字符为_ 的员工名字；【注意 _ 为 通配字符，所以需要转义，如果是普通字母就不需要了】

方式一：可以使用 \转义 字符， 如：name like "_\_%"

方式二： ESCAPE 任意 字符，如：name like "_$_%" ESCAPE '$'

【ESCAPE '$'只是强调说明一下$在这里被当作是转义字符使用，并不一定非要使用$,也可以使用a b c......只不过使用$可读性强 些】

is null 和<=>的区别

is null ：仅仅判断null值，可读性较高
<=> ：既可以判断null值，又可以判断普通的数值，但是可读性较差，很少用

3. 排序查询

默认asc升序排序


语法格式

select 要查询的东西

from 表

where条件

order by排序的字段|表达式|函数|别名 【asc|desc】

多个字段排序中间用英文逗号隔开，不能用and连接

4. 常见函数

MySql中索引是从1开始的

单行函数

1. 字符函数

示例

concat 拼接字符串

substr 截取子串

upper 转换成大写

lower 转换成小写

trim 去前后指定的空格和字符

ltrim 去左边空格

rtrim 去右边空格

replace 替换

lpad 左填充

rpad 右填充

instr 返回子串第一次出现的索引

length 获取字节个数

示例

2. 数学函数

示例

round 四舍五入

rand 随机数

floor 向下取整：返回小于等于该参数的最小整数

ceil 向上取整：返回大于等于该参数的最小整数

mod 取余：

• mod(a,b) 运算过程为 a-a/b*b 运算结果的符号和 a 保持一致
  
  
• 例如：mod(-10,3) 运算过程:
  
  (-10)-(-10)/3*3 = (-10)-(-3)*3 = (-10)-(-9)= -1

truncate 截断

示例

3. 日期函数

示例

now 当前系统日期+时间

DATEDIFF(expr1,expr2) ：可以统计两日期之间相差的天数

curdate 当前系统日期

curtime 当前系统时间

str_to_date 将字符转换成日期

date_format 将日期转换成字符

示例

SELECT
	NOW() `now`, -- 当前系统日期+时间
	CURDATE() `curdate`, -- 当前系统日期 
	CURTIME() `curtime`, -- 当前系统时间
	DATEDIFF('2024-12-15', '2024-12-10') `DATEDIFF`,
	-- 统计两日期之间相差的天数 
	STR_TO_DATE('8-11-2024', '%m-%d-%Y') `strToDate`,
	-- 将字符转换成日期
	DATE_FORMAT( '2024/08/12', '%Y年%m月%d日' ) dateToStr
  -- 将日期转换成字符
FROM
	DUAL;

4. 流程控制函数

百度百科

1. IF(expr1,expr2,expr3) 函数处理双分支： if else 的效果
   

函数用法说明： 如果 expr1 是 TRUE (expr1 <> 0 and expr1 <> NULL) ，则 IF() 的返回值为 expr2 ; 否则返回值则为 expr3 。 IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定

2. CASE WHEN THEN 函数
   
   

case 要判断的字段或表达式

when 常量1 then 要显示的值1

when 常量2 then 要显示的值2

...

else 要显示的值n

end

或

case 要判断的字段或表达式

when 条件1 then 要显示的值1

when 条件2 then 要显示的值2

...

else 要显示的值n

end

5. 其他函数

示例：主要是一些数据库系统相关的函数

SELECT VERSION(); 查看MySQL版本号

SELECT DATABASE(); 查看当前库

SELECT USER(); 查看当前连接用户

分组函数

分组函数 用作统计使用，又称聚合函数或统计函数或组函数


分类

sum ：求和

avg ：平均值

max ：最大值

min ：最小值

count ：计数

特点：

• 以上五个分组函数都忽略 null值，除了count(*)
  
  
• sum和avg一般用于处理数值型max、min、count可以处理任何数据类型
  
  
• 都可以搭配distinct使用，用于统计去重后的结果
  
  
• count的参数可以支持： 字段、*、常量值，一般放1,建议使用 count(*)
  
• 和分组函数一同查询的字段要求是group by 后的字段

5. 分组查询

分组函数

分组函数 用作统计使用，又称聚合函数或统计函数或组函数


分类

sum ：求和

avg ：平均值

max ：最大值

min ：最小值

count ：计数

语法格式

语法格式

select 分组函数, 列(必须出现在group by 后面的列)

from 表

where 筛选条件

group by 分组的列表

order by 子句

查询的字段要求是分组函数和group by后出现的字段

特点

特点：

1. group by 支持单个字段分组、多个字段分组（逗号隔开，无顺序要求）、表达式或函数（用 较少）
   
   
   
2. 和分组函数一起查询的字段必须是group by后出现的字段
   
   
   
3. 可以支持排序（排序放在整个分组函数的最后）
   
   
   
4. having后可以支持别名，只是 不常用
   
   
   
5. 分组筛选 ，筛选条件位置（参考下述对比图表）
   

① 能用 分组前 筛选的，优先考虑 分组前 筛选
② 分组函数做条件 肯定在having子句中

特点 5 说明	针对的表	位置	关键字
分组前筛选	原始表	group by的 前 面	where
分组后筛选	分组后的结果集	group by的 后 面	having

6. 多表连接查询

分类

按年代分

• SQL92标准：【MySQL中 仅仅支持 内连接】
  
  
• SQL99标准【推荐使用】：【MySQL中仅仅支内连接+外连接（左外和右外，MySQL中 不支持全外链接 ） +交叉连接】

按功能分

内连接：

• 等值连接
• 非等值连接
• 自连接

外连接：

• 左外连接
• 右外连接
• 全外连接

交叉连接

SQL 七种连接示意图

SQL92语法

【MySQL中 仅仅支持 内连接】

等值连接

语法

select 查询列表

from 表1 别名,表2 别名

where 表1.key = 表2.key

【and 筛选条件】

【group by 分组字段】

【having 分组后的筛选】

【order by 排序字段】

注意事项

1. 等值连接的结果 = 多个表的交集
2. n表连接，至少需要n-1个连接条件
3. 多个表不分主次，无顺序要求
4. 一般为表起别名，提高阅读性和性能

非等值连接

语法和 等值连接 相同，where后变为非等值连接条件


示例：

自连接

和等值连接一样


示例：

SQL99语法

简述

通过join关键字实现连接

【MySQL中 仅仅支持 内连接+外连接（左外和右外 ，不支持全外连接） +交叉连接】

含义：1999年推出的sql语法

支持：

• 内连接（★）： inner
  
• 外连接

左外(★) ： left 【outer】

右外(★) ：right 【outer】

全外 ：full【outer】

• 交叉连接：cross

语法格式

语法

select 字段，...

from 表1

【inner|left outer|right outer|cross】join 表2 on 连接条件

【inner|left outer|right outer|cross】join 表3 on 连接条件

【where 筛选 条件】

【group by 分组 字段】

【having 分组 后 的 筛选 条件】

【order by 排序 的字段或表达式】

内连接

语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件;

分类：

• 等值连接
• 非等值连接
• 自连接

特点：

①添加排序、分组、筛选

②inner可以省略

③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

④inner join连接和sql92语法中的 等值连接 效果是一样的，都是查询多表的交集

示例：

• 等值连接
  
  
  
  
• 非等值连接
  
  

• 自连接
  
  

外连接

应用场景：

用于查询一个表中有，另一个表没有的记录

特点：

1、外连接的查询结果为主表中的所有记录；
      如果从表中有和它匹配的，则显示匹配的值
      如果从表中没有和它匹配的，则显示null

外连接查询结果=内连接结果+主表中有而从表没有的记录

2、左外连接，left join 左边的是主表
      右外连接，right join 右边的是主表

3、左外和右外交换两个表的顺序，可以实现同样的效果

4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

• MySQL不支持全外连接
• 可以参考上述 SQL 七种连接示意图 理解

示例：

• 左外连接
  
  
• 右外连接
  
• 全外连接
  百度百科
  

交叉连接

SQL92和SQL99对比

功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

7. 子查询

特点、分类

特点：

1. 子查询都放在小括号内
   
   
2. 子查询可以放在from后面、select后面、where或having后面、exists后面，但一般放在条件的右侧
   
   
3. 子查询优先于主查询执行，主查询使用了子查询 的执行结果
   
   
4. 子查询根据查询结果的行数不同分为以下两类
   

• ① 单行子查询

• 结果集 只有一行
• 一般搭配单行操作符使用：> < = <> >= <=
• 非法使用子查询 的情况：

• a、子查询的结果为一组值
• b、子查询的结果为空

• ② 多行子查询

• 结果集 有多行
• 一般搭配多行操作符使用：any/some、all、in、not in

• in: 属于子查询结果中的任意一个就行
• any和all往往可以用其他查询 如： min 和 max 代替
  

示例

示例：

标量子查询 (单行子查询)

标量子查询(单行子查询)

列子查询 (多行子查询)

列子查询(多行子查询)

行子查询 [ 结果集一行多列或多行多列（用的较少）]

**行子查询 [ 结果集一行多列或多行多列（用的较少）]**了解即可

子查询位置示例

EXISTS 用法

where 后使用 带有 exists 或 not exists的子查询作为条件时，子查询不返回任何记录的数据，而是返回逻辑值true或false

注意： 执行顺序是先执行外查询再执行内查询 ，根据外查询的每条执行结果执行内查询

1. exists ： 子查询有结果 ，返回 true，保留外查询 的这条数据；否则返回false，不保留外查询 的这条数据
2. not exists ：子查询无结果 ，返回 true，保留外查询 的这条数据；否则返回false，不保留外查询 的这条数据
   
   

因为子查询只是返回逻辑值true或false，所以子查询中的select 后跟什么字段不重要，可以是任何内容，但是为了执行效率，建议写 1

8. 分页查询limit

语法

语法

select 字段|表达式 ,...

from 表

【where 筛选 条件】

【group by 分组 字段】

【having 分组 后 的 筛选 条件】

【order by 排序 的字段或表达式】

limit 【起始的条目索引】，条目数;

特点：

1. 起始条目索引 从 0 开始
   

2. limit子句放在 查询语句的最后
   

3. 规律公式：
   

select * from 表 limit （page-1）*size, size

• size 每页显示条目数
• page 要显示的页数
  
  
  举例理解：
  
  假定：limit A，B(size)

            B(size) = 10
  
  page           A
  1               0
2               10
3               20
4              30
...
B(size)       (page-1）*size

示例：

• limit A, B

理解： 跳过 A 条数据，取 B 条数据

9. 联合查询

语法

select 字段|常量|表达式|函数 【from 表】 【where 条件】

union 【all】

select 字段|常量|表达式|函数 【from 表】 【where 条件】

union 【all】

...

select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：

1. 多条查询语句的查询的列数必须是一致的
   
2. 多条查询语句每一列的类型和顺序最好一致
   
3. union代表去重，union all代表不去重
   

应用场景：

要查询的结果来自于多个表，且它们之间没有直接的连接关系，但查询结果一致时

10. 执行顺序

MySQL书写顺序	执行顺序
select 查询列表	7
from 表1	1
join 表2	2
on 连接条件	3
where 筛选条件	4
group by分组列表	5
having 分组后的筛选	6
order by排序列表	8
limit 偏移数,展示条目数	9