前言
这一节继续介绍MySQL数据库,主要介绍了DQL(数据查询语言),欢迎小伙伴们一起交流。
一、基础查询
- 语法
select 查询列表
from 表名;
- 特点
- 查询列表可以是字段、常量值、表达式、函数、也可以是多个
- 查询结果是一个虚拟表
- 示例
-
查询单个字段
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;
二、条件查询
- 语法
select 查询列表
from 表名
where 筛选条件
- 筛选条件的分类
- 简单条件运算符
< > = <> != >= <= <=>安全等于
- 逻辑运算符
&& 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值,又可以判断普通的数值,可读性较低
三、排序查询
- 语法
select 查询列表
from 表
where 筛选条件
order by 排序列表【asc / desc】
- 特点
①asc代表的是升序,desc代表的是降序。如果不写,默认是升序
②order by 子句中支持单个字段、多个字段、函数、表达式、别名
③order by 子句一般是放在查询语句的最后面,limit 子句除外
四、常见函数
1、概述
- 功能:类似于 java 的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
- 好处:①隐藏了实现细节②提高代码的重用性
- 调用:select 函数名(实参列表)【from 表】;
- 特点:①叫什么(函数名)②干什么(函数功能)
- 分类:①单行函数:如concat 、length、ifnull等 ②分组函数:功能-做统计使用,又称为统计函数,聚合函数,组函数
2、单行函数
- 字符函数
命令 | 功能 |
---|---|
concat | 连接 |
substr | 截取子串 |
upper | 变大写 |
lower | 变小写 |
replace | 替换 |
length | 获取字节个数 |
trim | 去前后空格 |
lpad | 左填充 |
rpad | 右填充 |
instr | 获取子串第一次出现的索引 |
- 数学函数
命令 | 功能 |
---|---|
round | 四舍五入 |
ceil | 向上取整 |
floor | 向下取整 |
truncate | 截断 |
mod | 取余 |
rand | 获取随机数,返回0-1之间的小数 |
- 日期函数
命令 | 功能 |
---|---|
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) |
- 其他函数
命令 | 功能 |
---|---|
version | 当前数据库服务器的版本 |
database | 当前打开的数据库 |
user | 当前用户 |
- 流程控制函数
①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、分组函数
- 分类
命令 | 功能 |
---|---|
max | 最大值 |
min | 最小值 |
sun | 和 |
avg | 平均值 |
count | 计算个数 |
- 特点
①语法
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 后出现的字段
五、分组查询
- 语法
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
注意: 查询列表必须特殊,要求是分组函数和group by后出现的字段
- 特点
1、分组查询中的筛选条件分为两类:
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by 子句的前面 | where |
分组后筛选 | 分组后的结果集 | group by子句的后面 | having |
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的,就优先考虑使用分组前筛选
2、group by子句支持单个字段分组,多个字段分组(多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数用得较少)
3、也可以添加排序(排序放在整个分组查询的最后)
六、连接查询
- 含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,…;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
-
分类
按年代分类:
sql92:
等值
非等值
自连接
也支持一部分外连接(用于oracle、sqlserver、mysql不支持)
sql99【推荐使用】
内连接
等值
非等值
自连接
外连接
左外
右外
全外(mysql不支持)
交叉连接 -
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排序字段】
- 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后面(相关子查询):支持标量子查询、列子查询、行子查询、表子查询
- 按结果集的行列数不同:
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
八、分页查询
- 语法
select 查询列表
from 表
[ join type join 表2
on 连接条件
where筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段 ]
limit offset, size;
这里offset要显示条目的起始索引(起始索引从0开始)
size要显示的条目个数
- 应用场景
当要显示的数据,一页显示不全,需要分页提交sql请求。
- 特点
- limit语句放在查询语句的最后
- 公式
要显示的页数page,每页的条目数size
select 查询列表
from 表
limit (page-1) * size, size;
九、union联合查询
union 联合 合并:将多条查询语句的结果合并成一个结果
- 语法
查询语句1
union
查询语句2
union
…
- 应用场景
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时。
- 特点
- 要求多条查询语句的查询列数是一致的!
- 要求多条查询语句的查询的每一列的类型和顺序最好。
- union关键字默认去重,如果使用union all可以包含重复项
总结
以上就是今天要讲的内容,这节介绍了MySQL中的数据查询语言,下一节将继续介绍MysQL中的数据操作语言。