3.MySQL——数据查询语言的学习

---

前言

这一节继续介绍MySQL数据库，主要介绍了DQL（数据查询语言），欢迎小伙伴们一起交流。

---

一、基础查询

1. 语法

select 查询列表
from 表名；

2. 特点

• 查询列表可以是字段、常量值、表达式、函数、也可以是多个
• 查询结果是一个虚拟表

3. 示例

• 查询单个字段
  select 字段名 from 表名；

• 查询多个字段
  select 字段名，字段名 from 表名；

• 查询所有字段
  select * from 表名；
  注：
  ①在做查询时，需打开指定的库，在最上面先写命令 USE库名；
  ②着重号’ '可用来区分是关键字还是字段；
  ③执行就选中谁来进行实现命令。

• 查询常量
  注：
  MySQL中不区分字符和字符串

• 查询函数
  select 函数名（实参列表）；

• 查询表达式
  select 100%98；

• 起别名
  ①使用as
  好处：
  –便于理解
  –如果要查询的字段有重名的情况，使用别名可以区分开来
  ②使用空格
  注：
  要是别名中有特殊符号如空格、#等建议将别名加上双引号

• 去重
  select distinct 字段名 from 表名；

• +号
  作用：做加法运算
  select 数值+数值；直接运算
  select 字符+数值；先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
  select null+值；结果都为null

• 【补充】concat函数
  功能：拼接字符
  select concat (字符1，字符2，字符3，…)

• 【补充】ifnull函数
  功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原来的值
  select ifnull (commission_pct,0) from employees;

• 【补充】isnull函数
  功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0；

---

二、条件查询

1. 语法

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

2. 筛选条件的分类

• 简单条件运算符
  < > = <> != >= <= <=>安全等于

---

• 逻辑运算符
  && and
  || or
  ! not

---

• 模糊查询
• like

特点：
①一般搭配通配符使用，可以判断字符型或数值型
②通配符：%任意多个字符，包含0个字符；_任意单个字符

• between and

①使用between and 可以提高语句简洁度
②包含临界值
③两个临界值不要调换顺序

• in

含义：判断某字段的值是否属于 in 列表中的某一项
①使用 in 提高语句简洁度
②in 列表的值类型必须一致或兼容
③不适用通配符

• is null / is not null
  =或< >不能用于判断 null 值
  is null 或 is not null 可以判断 null 值

is null PK<=>
is null : 仅仅可以判断null值，可读性较高，建议使用
<=>：既可以判断null值，又可以判断普通的数值，可读性较低

---

三、排序查询

1. 语法

select 查询列表
from 表
where 筛选条件
order by 排序列表【asc / desc】

2. 特点

①asc代表的是升序，desc代表的是降序。如果不写，默认是升序
②order by 子句中支持单个字段、多个字段、函数、表达式、别名
③order by 子句一般是放在查询语句的最后面，limit 子句除外

---

四、常见函数

1、概述

• 功能：类似于 java 的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
• 好处：①隐藏了实现细节②提高代码的重用性
• 调用：select 函数名（实参列表）【from 表】；
• 特点：①叫什么（函数名）②干什么（函数功能）
• 分类：①单行函数：如concat 、length、ifnull等 ②分组函数：功能-做统计使用，又称为统计函数，聚合函数，组函数

2、单行函数

1. 字符函数

命令	功能
concat	连接
substr	截取子串
upper	变大写
lower	变小写
replace	替换
length	获取字节个数
trim	去前后空格
lpad	左填充
rpad	右填充
instr	获取子串第一次出现的索引

2. 数学函数

命令	功能
round	四舍五入
ceil	向上取整
floor	向下取整
truncate	截断
mod	取余
rand	获取随机数，返回0-1之间的小数

3. 日期函数

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

一些日期的格式符表示

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

4. 其他函数

命令	功能
version	当前数据库服务器的版本
database	当前打开的数据库
user	当前用户

5. 流程控制函数
   ①if(条件表达式，表达式1，表达式2）：如果条件表达式成立，返回表达式1，否则返回表达式2
   ②case情况1
   case 变量或表达式或字段
   when 常量1 then 值1
   when 常量2 then 值2
   …
   else 值n
   end
   ③case情况2
   case
   when 条件1 then 值1
   when 条件2 then 值2
   …
   else 值n
   end

---

3、分组函数

1. 分类

命令	功能
max	最大值
min	最小值
sun	和
avg	平均值
count	计算个数

2. 特点

①语法
select max(字段) from 表名；
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null
④都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段）from 表；
⑤count函数
count(字段）：统计该字段非空值的个数
count(*）：统计结果集的行数
count(1）：统计结果集的行数
效率上： MyISAM存储引擎，count(*)最高 InnoDB存储引擎，count(*)和count(1)效率>count(字段）
⑥和分组函数一同查询的字段，要求是group by 后出现的字段

---

五、分组查询

1. 语法

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
注意： 查询列表必须特殊，要求是分组函数和group by后出现的字段

2. 特点

1、分组查询中的筛选条件分为两类：

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

①分组函数做条件肯定是放在having子句中
②能用分组前筛选的，就优先考虑使用分组前筛选
2、group by子句支持单个字段分组，多个字段分组（多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数用得较少）
3、也可以添加排序（排序放在整个分组查询的最后）

---

六、连接查询

1. 含义

当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2，…；
笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件

2. 分类
   按年代分类：
   sql92:
   等值
   非等值
   自连接
   也支持一部分外连接（用于oracle、sqlserver、mysql不支持）
   sql99【推荐使用】
   内连接
   等值
   非等值
   自连接
   外连接
   左外
   右外
   全外（mysql不支持）
   交叉连接

3. SQL92语法

• 等值连接

语法：
select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by分组字段】
【having 分组后的筛选】
【order by排序字段】

特点：
①一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分

• 非等值连接

语法：
select 查询列表
from 表1 别名，表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by分组字段】
【having 分组后的筛选】
【order by排序字段】

• 自连接

语法：
select 查询列表
from 表 别名1，表 别名2
where 等值的连接条件
【and 筛选条件】
【group by分组字段】
【having 分组后的筛选】
【order by排序字段】

4. SQL99语法

• 内连接

语法：
select 查询列表
from 表1 别名
【inner】join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句；
特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

• 外连接
  应用场景：用于查询一个表中有，另一个表没有的记录

语法：
select 查询列表
from 表1 别名
left | right | full 【outer】join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句；
特点：
①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
②left join 左边的就是主表，right join 右边的就是主表，full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行

• 交叉连接

语法：
select 查询列表
from 表1 别名
cross join 表2 别名；
特点：
类似于笛卡尔乘积

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

---

七、子查询

1、含义

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

2、分类

• 按子查询出现的位置：
• select后面：仅仅支持标量子查询
• from后面：支持表子查询
• where或having后面：支持标量子查询、列子查询、行子查询
  特点：

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

支持类型
①标量子查询
②列子查询（多行子查询)
-返回多行
-使用多行比较操作符

操作符	含义
IN/NOT IN	等于列表中的任意一个
ANY/SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

③行子查询（结果集一行多列或多行多列）：用的少

• exists后面（相关子查询）：支持标量子查询、列子查询、行子查询、表子查询

---

• 按结果集的行列数不同：
• 标量子查询（结果集只有一行一列）
• 列子查询（结果集只有一列多行）
• 行子查询（结果集有一行多列）
• 表子查询（结果集一般为多行多列）

---

八、分页查询

1. 语法

select 查询列表
from 表
[ join type join 表2
on 连接条件
where筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段 ]
limit offset, size;

这里offset要显示条目的起始索引(起始索引从0开始）
size要显示的条目个数

2. 应用场景

当要显示的数据，一页显示不全，需要分页提交sql请求。

3. 特点

• limit语句放在查询语句的最后
• 公式
  要显示的页数page，每页的条目数size
  select 查询列表
  from 表
  limit （page-1) * size, size;

---

九、union联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果

1. 语法

查询语句1
union
查询语句2
union
…

2. 应用场景

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

3. 特点

• 要求多条查询语句的查询列数是一致的！
• 要求多条查询语句的查询的每一列的类型和顺序最好。
• union关键字默认去重，如果使用union all可以包含重复项

---

总结

以上就是今天要讲的内容，这节介绍了MySQL中的数据查询语言，下一节将继续介绍MysQL中的数据操作语言。