DQL-Data Query Language 数据查询语言
一.基础查询
语法:
select 查询列表 from 表名;
特点:
①查询列表可以是:表中的字段 常量值 表达式 函数,也可以是多个进行组合
②查询的结果是一个虚拟的表格
示例:
1.查询单个字段
select 字段名 from 表名;
2.查询多个字段
select 字段名1,字段名2,... 字段名n from 表名;
3.查询所有字段
select * from 表名;
4.查询常量
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5.查询函数
select 函数名(参数列表);
6.查询表达式
select 100/12;
7.起别名
①AS关键字
②直接使用空格
8.去重
select distinct 字段名 from 表名;
9.+(加号)
作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值; 先试图将字符转成数值,如果能成功转换,则继续运算,否则转换成0,再做运算
10.concat函数
功能:拼接字符
语法:
select concat(字符1,字符2,字符3,...);
11.ifnull函数
功能:判断某字段是否为null,如果为null,返回指定的值
语法:
select ifnull(某字段,指定值) from 表名;
12.isnull函数
功能:判断某字段或表达式是否为null,如果为null返回1,如果不为null返回0
二.条件查询
语法:
select 查询列表 from 表名 where 筛选条件;
筛选条件的分类
1.简单条件运算符
<> = > < != >= <= <=>(安全等于)
2.逻辑运算符(推荐使用后者)
&& and
|| or
! not
3.模糊查询
(1)like: 一般搭配通配符使用,用于字符型判断
通配符: %
匹配任意多个字符 _
匹配任意单个字符
(2)between and:包含上下两个临界值 临界值不可以颠倒顺序 从小到大
(3)in:可以规定多个值 是等于的含义
(4)is null /is not null:用于判断null值
is null 和<=>(安全等于)的对比
普通的数值类型 | null值 | 可读性 | |
---|---|---|---|
is null | 不支持 | 支持 | 较高 |
<=> | 支持 | 支持 | 较差 |
三.排序查询
- 语法
select 查询列表
from 表
where 筛选条件
order by 筛选列表[asc|desc];
- 特点
①asc:升序 不写默认 desc:降序
②排序列表支持:单个字段 多个字段 函数 表达式 别名
③order by 的位置一般是放在查询语句的最后(limit 语句除外)
四.常见的函数
调用方式 :
select 函数名(实参列表);
1.单行函数
(1)字符函数
concat:连接
substr:截取字符串
upper:变大写
lower:变小写
replace:替换
length:获取字节长度
trim:去前后空格
lpad:左填充
rpad:右填充
instr:获取子串第一次出现的索引
2.数学函数
ceil:向上取整
floor:向下取整
round:四舍五入
mod:取模
truncate:截断
rand:获取随机数,返回0~1之间的小数
3.日期函数
now:返回当前日期+时间
year:返回年
month:返回月
day:返回日
date_format:将日期转换成为字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime:返回当前时间
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月
4.其他函数
version:当前数据库服务器的版本
database:当前打开的数据库
user:当前用户
password('字符'):返回该字符的密码形式 MySQL8.0已弃用
md5('字符'):返回该字符的MD5加密形式
5.流程控制函数
(1)if
if(条件表达式,表达式一,表达式二):如果条件表达式成立,返回表达式一,否则返回表达式二
(2)case 情况1(实现等值判断):
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
(3)case 情况2(实现区间判断):
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
2.分组函数
分类:
max:最大值
min:最小值
sum:和
avg:平均值
count:计算个数
特点:
(1)语法:
select max(字段) from 表名;
(2)支持的类型
sum和avg一般用于处理数值型
max min count可以处理任何数据类型
(3)以上分组函数都可以忽略null值
(4)以上分组函数都可以搭配distinct使用,实现去重统计
select sum(distinct 字段) from 表;
(5)count 函数
count(字段):统计该字段的非空值
count(*):统计结果集的行数
count(1):统计结果集的行数
五.分组查询
1.语法
select 分组函数,分组后的字段 ①
from 表 ②
【where 筛选条件】 ③
group by 分组的字段 ④
【having 分组后的筛选】 ⑤
【order by 排序列表】 ⑥
语句执行顺序:②→③→④→⑤→①→⑥
mysql中order by group by having 支持别名 oracle中只有 order by 支持别名
2. 特点
使用关键字 | 筛选的表 | 位置 | |
---|---|---|---|
分组前筛选 | where | 原始表 | group by的前面 |
分组后筛选 | having | 分组后的结果 | group by的后面 |
六.连接查询
1.含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,...;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
2. 分类
(1) 按年代分类:
sql92:
等值、非等值、自连接、也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99(推荐使用):
内连接 => 等值 、 非等值 、 自连接
外连接 => 左外 、 右外、 全外(mysql不支持)
交叉连接
3.语法
SQL92语法
(1)等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
(2)非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
(3)自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
SQL99语法
1.内连接
语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
等值连接、非等值连接、自连接
2.外连接
使用场景:
用于查询一个表中有,另一个表中没有的记录
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
②left join左边的就是主表,right join 右边的就是主表,full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
分类:
左外、 右外、 全外(mysql不支持)
3.交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积
七.子查询
1.含义
嵌套在其他语句内部的select语句称为子查询
外面的语句也可以是insert update delete select等,一般select作为外面语句较多
外面如果是select语句,则可以称为外查询或者主查询
2.分类
(1)按出现位置
select后面
只支持标量子查询
from后面
主要是表子查询
注意:将子查询结果充当一张表,要求必须起别名
where后面或having后面
特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用
> < >= <= = <>
④列子查询,一般搭配着多行操作符使用
in/not in any/some all
⑤子查询的执行优于主查询执行,主查询的条件用到了子查询的结果
支持类型:标量子查询、列子查询、行子查询
exists后面(相关子查询)
语法:
exists(完整的查询语句) 结果:1或0
支持类型:标量子查询、列子查询、行子查询、表子查询
(2)按结果集的行列
标量子查询(单行子查询):结果为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列
八.分页查询
1.应用场景
当要查询的条目太多,一页显示不全
2.语法
select 查询列表 ①
from 表1 ②
【join type join 表2 ③
on 连接条件 ④
where 筛选条件 ⑤
group by 分组字段 ⑥
having 分组后的筛选 ⑦
order by 排序后的字段】 ⑧
limit 【offset,】size; ⑨
语句执行顺序:②→③→④→⑤→⑥→⑦→①→⑧→⑨
注意:
offset代表的是起始的条目索引,默认从0开始
size代表的是显示的条目数
limit 子句不论是语法还是执行顺序上都放在最后
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
九.联合查询
含义: 将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...
应用场景:
要查询的结果来自多个表,且多个表没有直接的连接关系,但查询信息一致时
特点:
①要求多个查询语句的查询列表是一致的
②要求多条查询语句的查询的每一列的类型和顺序最好一致
③union关键字默认去重,如果使用union, all 可以包含重复项