【MySQL】数据查询语言DQL

数据查询语言DQL(Data Query language)

对象：表数据

关键词：select

1、基础查询

1.1 语法

SELECT 查询列表 FROM 表名;

1.2 特点

1. 查询列表可以是字段、常量、函数和表达式。
2. 查询结果为「虚拟表」。

1.3 示例

1、查询单个字段：SELECT 字段名 FROM 表名;

2、查询多个字段：SELECT 字段名,字段名 FROM 表名;

3、查询所有字段：SELECT * FROM 表名;

4、查询常量：SELECT 常量值;

注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要。

5、查询函数：SELECT 函数名(实参列表); 6、查询表达式：SELECT 100/25;

7、起别名：SELECT 字段名 AS “别名” FROM 表名;

注意：别名可以使用单引号、双引号引起来，当只有一个单词时，可以省略引号，当有多个单词且有空格或特殊符号时，不能省略，AS可以省略。

8、去重复：SELECT DISTINCT 字段名 FROM 表名;

9、做加法

• SELECT 数值+数值; 直接运算
• SELECT 字符+数值; 首先先将字符转换为整数，如果转换成功，则继续运算，如果转换失败，则默认为0，然后继续运算
• SELECT NULL+数值; NULL和任何数值参与运算结果都是NULL

10、【补充】ifnull函数

功能：判断某字段或表达式是否为 null，如果为 null，返回指定的值，否则返回原本的值

SELECT IFNULL(字段名, 指定值) FROM 表名;

11、【补充】isnull函数

功能：判断某字段或表达式是否为 null，如果是 null，则返回 1，否则返回 0

SELECT ISNULL(字段名) FROM 表名;

2、条件查询

2.1 语法

SELECT 查询列表 FROM 表名 WHERE 筛选条件;

2.2 分类

条件运算符：>、>=、<、<=、=、<=>、!=、<>

逻辑运算符：and、or、not

模糊运算符：

1. like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义
2. between and
3. not between and
4. in
5. is null
6. is not null

注意：

1. = 只能判断普通类型的数值，而 <=> 不仅可以判断普通类型的数值还可以判断 NULL。
2. != 和 <> 都是判断不等于的意思，但是 MySQL 推荐使用 <>
3. in 列表的值类型必须一致或兼容，in 列表中不支持通配符 % 和 _。
4. =、!= 不能用来判断 NULL、而 <=>、is null 、is not null 可以用来判断 NULL，但注意 <=> 也可以判断普通类型的数值

3、排序查询

3.1 语法

SELECT 
  查询列表 
FROM
  表 
【WHERE 筛选条件】
ORDER BY 排序列表 【asc | desc】 ;

3.2 注意

1. 排序列表可以是单个字段、多个字段、别名、函数、表达式
2. asc 代表升序，desc 代表降序，如果不写，默认是 asc
3. order by 的位置一般放在查询语句的最后（除 limit 语句之外）

4、单行函数

4.1 语法

SELECT 函数名(实参列表) 【FROM 表】;

4.2 分类

• 字符函数

concat：连接字符
substr：截取子串
replace：替换字符
upper：变大写
lower：变小写
lpad：左填充
rpad：右填充
length：获取字节长度
trim：去除前后空格
instr：获取子串第一次出现的索引

注意：MySQL中的索引是从1开始的

• 数学函数

round：四舍五入
ceil：向上取整
floor：向下取整
mod：取模运算（a-a/b*b）
truncate：保留小数的位数，不进行四舍五入
rand：获取随机数，返回 0-1 之间的小数

• 日期函数

now：返回当前日期+时间
curdate：返回当前日期
curtime：返回当前时间
year：返回年
month：返回月
day：返回日
hour：小时
minute：分钟
second：秒
monthname：以英文形式返回月
datediff：返回两个日期相差的天数
date_format：将日期转换成字符
str_to_date：将字符转换成日期

• 格式符：

%Y：四位的年份
%y：二位的年份
%m：二位的月份（01，02，...，12）
%c：一位的月份（1，2，...，12）
%d：日（01，02，...，31）
%H：小时（24小时制）
%h：小时（12小时制）
%i：分钟（00，01，02，...，59）
%s：秒（00，01，02，...，59）  

• 控制函数

if：判断函数

case：分支函数

1、IF(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

2、case的格式一：
    CASE 变量或字段或表达式 
      WHEN 常量1 THEN 值1 
      WHEN 常量2 THEN 值2
      ...
      ELSE 值n 
    END ;
    
3、case的格式二：
    CASE
      WHEN 条件1 THEN 值1 
      WHEN 条件2 THEN 值2
      ...
      ELSE 值n 
    END

• 其它函数

version：当前数据库的版本
database：当前打开的数据库
user：当前登录的用户
password(‘字符’)：返回该字符的密码形式
md5(‘字符’)：返回该字符的md5加密形式

5、分组函数

5.1 语法

SELECT 函数名(实参列表) 【FROM 表】;

5.2 分类

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

1. sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2. 以上分组函数都忽略null值
3. 可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;
4. 一般使用count(*)用作统计行数
5. 和分组函数一同查询的字段要求是group by后的字段

6、分组查询

6.1 语法

SELECT 
  查询列表 
FROM
  表 
【where 筛选条件】 
GROUP BY 分组的字段 
【having 分组后的筛选】
【order BY 排序的字段】 ;

6.2 特点

1、和分组函数一同查询的字段必须是group by后出现的字段

2、筛选分为两类：分组前筛选和分组后筛选

3、分组可以按单个字段也可以按多个字段

4、分组可以搭配着排序使用

6.3 总结

分组，个人理解是多个不同的数据都有一个共同的标识，按照这一个标识对这些数据进行分组。

7、连接查询

7.1 含义

连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

7.2 注意

笛卡尔乘积现象：表 1 有 m 行，表 2 有 n 行，结果 = m*n 行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

7.3 分类

按年代分类：

• sql92 标准：支持内连接

• sql99 标准：支持内连接、部分外连接（左外、右外）、交叉连接

按功能分类：

• 内连接（返回两张表都满足条件的部分）

  • 等值连接
  • 非等值连接（在两表某值范围内进行范围查询）
  • 自连接（自己和自己连接，得到一些他特殊数据）

• 外连接

  • 左外连接（左表以及右表符合条件部分）
  • 右外连接（右表以及左表符合条件部分）
  • 全外连接（全外连接是在结果中除了显示满足连接的条件的行外，还显示了 join 两侧表中所有满足检索条件的行）
  • 交叉连接（笛卡尔积，左表每一行与右表全表组合）

7.4 区别

1. 功能方面：sql99 支持的较多
2. 可读性：sql99 实现了连接条件和筛选条件的分离，因此可读性较高

7.5 总结

sql92 功能较少，不支持外连接；sql99 引入了 JOIN 进行关联查询，功能强大，支持外连接，可读性高，常用 sql99。

8、sql99标准

8.1 语法

SELECT 
  查询列表 
FROM 表1 别名1 
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】 ;

8.2 连接类型

内连接：inner

外连接：

• 左外连接：left 【outer】（左边的是主表）

• 右外连接：right 【outer】（右边的是主表）

• 全外连接：full 【outer】（两边都是主表，但是MySQL不支持全外连接、Oracle支持）

• 交叉连接：cross（交叉连接其实是用sql99语法实现笛卡尔乘积）

8.3 总结

推荐博客：Sql 中内连接、外连接、全连接、交叉连接的区别

9、子查询

9.1 含义

嵌套在其它语句内部的 select 语句称为子查询或内查询，外面的语句可以是 insert、delete、update、select 等，一般 select 作为外面语句较多，外面如果为 select 语句，则此语句称为外查询或主查询。

9.2 分类

按出现的位置划分

• select后面：标量子查询

• from后面：表子查询

• where或having后面

  • 标量子查询
  • 列子查询
  • 行子查询

• exists后面

  • 标量子查询
  • 列子查询
  • 行子查询
  • 表子查询

按结果集行列数划分

• 标量子查询（单行子查询）：结果集为一行一列
• 列子查询（多行子查询）：结果集为多行一列
• 行子查询：结果集为多行多列
• 表子查询：结果集为多行多列

9.3 特点

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
4. 标量子查询，一般搭配着单行操作符使用：>、>=、<、<=、!=、<>、=、<=>
5. 列子查询，一般搭配着多行操作符使用：in、not in、any、some、all、exits

9.4 总结

子查询的效率并不高，每次进行子查询时需要先创建临时表，使用完之后，会销毁这些临时表，所以会对 SQL 执行的效率造成影响。

注意：这张临时表是数据库（MySQL自动生成）

可以使用连接查询 JOIN 代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

多表联查性能优化，优化的本质就是 join on 和 where 的执行顺序。

数据库在通过连接两张或多张表来返回记录时，都会生成一张 中间的临时表，然后再将这张临时表返回给用户

on 和 where 条件的区别如下：

1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录
2. where 条件是在临时表生成好后，再对临时表进行过滤的条件（这时已经和 left join 没关系了），条件不为真的就全部过滤掉

参考博客：子查询和关联查的区别

10、分页查询

10.1 语法

SELECT 
  查询列表 
FROM
  表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【WHERE 分组前的筛选】
【GROUP BY 分组字段】 
【HAVING 分组后的筛选 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT 【offset, 】size ;

10.2 特点

1. limit 语句放在查询语句的最后
2. offset 代表起始索引，起始索引从 0 开始，size 代表条目个数
3. 分页语句：select 查询列表 from 表 limit (page-1)*size , size;

10.3 总结

分页的条件一般都放在 SQL 语句最后边。

11、联合查询

11.1 语法

查询语句1
union 【all】
查询语句2
union 【all】
...

11.2 特点

1. 要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致时，可以使用联合查询
2. 要求多条查询语句的查询列数是一致的
3. 要求多条查询语句的查询的每一列的类型和顺序最好一致
4. union 关键字默认去重，如果使用 union all 可以包含重复项

11.3 演示

#查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

参考文章：轻松的小希