MySQL学习笔记(二)——DQL语言

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	不支持	支持	较高
<=>	支持	支持	较差

---

三.排序查询

1. 语法

select 查询列表
from 表
where 筛选条件
order by 筛选列表[asc|desc];

2. 特点

①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 可以包含重复项