MySQL 函数的使用解析及SQL连接查询
一、函数的作用
1.隐藏了实现细节
2.提高了代码的重用性和间接性
调用(可以嵌套):
select 函数名(实参列表) 【from 表】;
select 函数() from dual; 标准语法,dual是一个通用表,默认的调用函数都走这个表,默认可以省略成select 函数();
1.函数部分基础使用
select version();
select database();
select user();
select @@port;
select @@datadir;
select @@socket;
二、函数的分类：
1.单行函数
如 concat length ifnull 等
2.分组函数（组函数）
功能：做统计使用，又称为统计函数、聚合函数
1.单行函数
1.）字符函数
# length 获取参数值的字节个数
select length('john'); 结果4
select length('张三丰hahaha'); 结果15（utf8字符集下中文为3个字节）
# concat 拼接字符串
mysql> select concat('a','_','b') 姓名;
+--------+
| 姓名 |
+--------+
| a_b |
+--------+
1 row in set (0.02 sec)
# upper lower 改变大小写
select upper('john') 结果
union all
select lower('JOHN');
+--------+
| 结果 |
+--------+
| JOHN |
| john |
+--------+
2 rows in set (0.00 sec)
# substr substring 字符截取
'注意:索引从1开始计数,截取的是字符长度而不是字节长度'
截取从指定索引处后的所有字符：
select substr('李莫愁爱上了陆展元',7) 输出;
+-----------+
| 输出 |
+-----------+
| 陆展元 |
+-----------+
1 row in set (0.01 sec)
截取从指定索引处后的指定个数字符：
select substr('李莫愁爱上了陆展元',1,3) 输出;
+-----------+
| 输出 |
+-----------+
| 李莫愁 |
+-----------+
1 row in set (0.00 sec)
# instr 返回子串第一次出现的索引位置，如果没有则返回0
select instr('abcXdefgx','x') as 输出;
+--------+
| 输出 |
+--------+
| 4 |
+--------+
"不区分大小写，支持中文字符查找。"
select instr('你好今日世界','今日') as 输出;
+--------+
| 输出 |
+--------+
| 3 |
+--------+
# trim 去前后空格或指定字符串（在中间的不会去除）
select trim(' ab c ') as 输出;
+--------+
| 输出 |
+--------+
| ab c |
+--------+
用'字符' from 方式指定字符
mysql> select trim('x' from 'xbcx ax xxbcxx xaxxx') as 输出;
+-------------------+
| 输出 |
+-------------------+
| bcx ax xxbcxx xa |
+-------------------+
select trim('x' from 'xxx ax xxbcxx xxxx') as 输出;
+--------------+
| 输出 |
+--------------+
| ax xxbcxx |
+--------------+
'注意只去前后。其中有任意字符隔开后就变成了中间部分的内容而不会被去除了。'
# lpad 用指定的字符实现左填充至指定的总长度（rpad右填充）
select lpad('aaa',5,'*') 输出;
+--------+
| 输出 |
+--------+
| **aaa |
+--------+
'如果长度不足则会截取长度。'
select lpad('aaaa',2,'*') 输出;
+--------+
| 输出 |
+--------+
| aa |
+--------+
# replace 替换
select replace('abcd_aaa_bbb','aaa','xxx') as output;
+--------------+
| output |
+--------------+
| abcd_xxx_bbb |
+--------------+
#替换是区分大小写的，需要注意。
2.）数学函数
#rand获取随机数,返回0-1之间的任意小数.
select rand();
+--------------------+
| rand() |
+--------------------+
| 0.3116143292855605 |
+--------------------+
# round 四舍五入
select round(-1.55) output; 结果-2
select round(-1.567,2) output; 结果-1.57
select round(1567,-2) output;
+--------+
| output |
+--------+
| 1600 |
+--------+
# ceil 向上取整，返回>=该参数的最小整数
select ceil(1.002); 结果2
select ceil(-1.002); 结果-1
# floor 向下取整，返回<=该参数的最大整数
select floor(-9.99); 结果-10
# truncate 截断
select truncate(1.6999,1); 结果1.6
select truncate(16999,-2);
+--------------------+
| truncate(16999,-2) |
+--------------------+
| 16900 |
+--------------------+
# mod 取余数
select mod(10,3); 结果1
select 10%3; 结果1
select mod(-10,-3); 结果-1
select mod(10,-3); 结果1
'实际计算公式为'：
mod(a,b) = a-a/b*b
3.）日期函数
# now返回当前系统时间
select now();
# curdate 返回当前系统日期，不包含时间。
select curdate();
+------------+
| curdate() |
+------------+
| 2020-08-12 |
+------------+
# curtime 返回当前系统时间，不含日期。
select curtime();
#但是值本身包含完整时间.例如:
mysql> select year(curtime());
+-----------------+
| year(curtime()) |
+-----------------+
| 2020 |
+-----------------+
# 获取指定的年月日时分秒
select year('1981-1-1') 年; 结果 1981
select month('1981-1-1') 月; 结果 1
select monthname('1981-1-1') 月; #结果 January
select day('1981-1-1');
select hour(now());
select minute(now());
select second(now());
# date_format 将日期转换为字符
select date_format(now(),'%Y年=%m月=%d日') output;
+-------------------+
| output |
+-------------------+
| 2020年=08月=12日 |
+-------------------+
使用场景：常用于日期格式的转换
select date_format(now(),'%m月/%d日 %Y年') output;
+---------------------+
| output |
+---------------------+
| 08月/12日 2020年 |
+---------------------+
# str_to_date 将字符通过格式转换成日期
查找日期为1992-04-03的所有信息
select * from 表 where 日期 = str_to_date('4-3 1992','%c-%d %y');
场景：一般情况下用于接受web端输入的日期信息（一般为str字符串类型）。
#注意：'%c-%d %y'不代表最后输出的日期格式，只是用对应的方法去解析日期。
附实例和时间函数表：
4.）其他函数
select version();
select database();
select user();
select password('字符') 返回一个字符对应的加密结果.
select md5('字符') 返回一个字符对应的md5加密结果.
select password('555');
+-------------------------------------------+
| password('555') |
+-------------------------------------------+
| *1D096071E42D900FF3B77C6AB23CBBBD6E4F3824 |
+-------------------------------------------+
5.）流程控制函数
# if 函数 判断输出
select if(10<5,'大','小') 比大小;
+-----------+
| 比大小 |
+-----------+
| 小 |
+-----------+
结果为是返回第一个值，结果为否返回第二个值
# case 多条件判断输出 使用1（等值判断）
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
....
else 要显示的值n或语句n;
end
# 注意case前要加,
举例1：
select 工资,部门编号,
case 部门编号
when 30 then 工资*1.5
when 40 then 工资*1.6
when 50 then 工资*1.7
else 工资
end as 新工资
from 表;
使用world.city表例2:
mysql> select id,name,
-> case id
-> when 1 then concat(name,'aa')
-> when 2 then concat(name,'bb')
-> else name
-> end as 新名字
-> from city limit 4;
+----+----------------+----------------+
| id | name | 新名字 |
+----+----------------+----------------+
| 1 | Kabul | Kabulaa |
| 2 | Qandahar | Qandaharbb |
| 3 | Herat | Herat |
| 4 | Mazar-e-Sharif | Mazar-e-Sharif |
+----+----------------+----------------+
4 rows in set (0.00 sec)
# case 多条件判断输出 使用2（区间判断）
语法：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
....
else 要显示的值n或语句n
end
使用world.city表举例2：
mysql> select id,name,
-> case
-> when population>600000 then 'A级'
-> when population>300000 then 'B级'
-> when population>100000 then 'C级'
-> else 'D级'
-> end as 城市级别
-> from city limit 10;
+----+---------------------+--------------+
| id | name | 城市级别 |
+----+---------------------+--------------+
| 1 | Kabul | A级 |
| 2 | Qandahar | C级 |
| 3 | Herat | C级 |
| 4 | Mazar-e-Sharif | C级 |
| 5 | Amsterdam | A级 |
| 6 | Utre | B级 |
| 7 | Haagddd | B级 |
| 8 | Utrecht | C级 |
| 9 | Eindhoven | C级 |
| 10 | Rotterdam | A级 |
+----+---------------------+--------------+
# case条件相当于ifelse,会自动分割区间.不会产生重复覆盖的情况.
2.分组函数
1.基础函数
#以下函数运算的时候是忽略null值
sum() 求和
avg() 求平均值
min() 求最小值
max() 求最大值
count() 求非空值的个数(不为null)
count(*) 统计总行数
count(1) 统计总行数 ()中可以加任意常量,意义为在表的列中加多了一列全都为该常量.然后统计该常量的个数,即为表的总行数.
datediff('2017-10-1','2017-11-5') 日期差距天数,(前者减去后者)
mysql> select datediff('2017-10-1','2017-11-5');
+-----------------------------------+
| datediff('2017-10-1','2017-11-5') |
+-----------------------------------+
| -35 |
+-----------------------------------+
#参数支持的类型
max min支持字符型和日期型
count 支持任意类型.
2.分组查询
#语法:
select 分组函数,列(要求出现在group by的后面)
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]
[having子句];
'where条件需在from后和group by前.'
'除了where子句,其他子句都支持使用别名.'
例:
# 1.简单分组查询
查询每个工种的最高工资
select max(工资),工种id
from 工资表
group by 工种id;
# 2.多条件分组查询
查询邮箱中包含a字符的每个部门的平均工资
select avg(工资),部门id
from 工资表
where 邮箱 like '%a%'
group by 部门id;
# 3.多重分组查询
查询每个部门每个工种的员工平均工资
select avg(工资),部门id,工种id
from 工资表
group by 工种id,部门id;
# 4.添加排序
查询每个部门每个工种每个员工的平均工资,并按平均工资排序.
select avg(工资) aaa,部门id,工种id
from 工资表
group by 工种id,部门id
order by aaa desc;
#4.查询哪个部门的员工个数大于2
select count(*),部门id
from 工资表
group by 部门id
having count(*)>2;
#having函数用于筛选以上select语句的结果.
三、连接查询(多表查询)
当查询的字段来源于多个表时,用到连接查询
笛卡尔乘积现象:表1 m行,表2 n行,查询表1和表2,结果=m*n行 (原因:没有有效的连接条件)
分类标准:
sql192标准 :仅支持内连接
sql199标准 (推荐):mysql支持内连接+外连接(左外右外)+交叉连接(不支持全外连接)
#区别:
sql199支持的功能更多,可读性更高,实现连接条件和筛选条件的分离.
#按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接
交叉连接
1.sql192标准
#1.等值连接
1) 多表等值连接的结果为多表的交集部分
2) n表连接,至少需要n-1个连接条件
3) 多表的顺序没有要求
4) 一般需要为表起别名
5) 可以搭配所有的子句使用,比如排序.分组.筛选
1例:查询女表中对应的男友名
select name女,name男
from 女表,男表
where 女表.男友id=男表.id
注意:"此查询结果与左外连接不同,如果女表查询结果有空值无对应的情况则会不显示."
#2.为表起别名
select * from 表名 as 别名(as可以省略)
例:
select a.id,b.id
from 表1 a,表2 b
where a.id=b.id;
!'如果为表起了别名,则查询的字段就不能使用原来的表名去查询了.'
例:
select 表1.id,表2.id
from 表1 a,表2 b
where a.id=b.id;
会执行报错,语句会先执行from后的表重命名然后再走select查询,导致表名不识别.
#3.三表连接查询
select name,部门name,city
from 表1 a,表2 b,表3 c
where a.部门name=b.部门名
and b.城市id=c.城市id
#4.非等值连接
例:查询员工的工资和工资级别
select name,工资,工资级别
from 工资表 a,工资等级表 b
where a.工资 between b.最低工资 and b.最高工资;
#5.自连接(内连接)
例如:查询员工名和上级领导的名字
+------+------+------+
| id | name | lid |
+------+------+------+
| 1 | fa | NULL |
| 2 | fb | 1 |
| 3 | fc | 1 |
+------+------+------+
在同一张表中两次或多次查询.
mysql> select id,name,lid from bbb where id=lid;
Empty set (0.00 sec)
'结果为空,不是我们想要的结果.'
实际情况:第一次查询得到fb领导为1,1再次查询对应到fa
所以需要用到别名:
select a.id,a.name,b.name lname
from bbb a,bbb b
where a.lid=b.id;
+------+------+-------+
| id | name | lname |
+------+------+-------+
| 2 | fb | fa |
| 3 | fc | fa |
+------+------+-------+
2.sql199语法
# 1.等值连接inner join (inner是可以省略不写)
# .两表连接
查询员工名,部门名
select name,部门名
from 员工表 a
inner join 部门表 b
on a.部门id=b.部门id
# .两表连接多子句
两表连接查询每个部门的员工个数>3的部门名和员工个数,并按个数降序
select count(*),部门名
from 员工表 a
inner join 部门表 b
on a.部门id=b.部门id
group by 部门名
having count(*)>3
order by count(*) desc;
# .三表连接使用2个inner join
三表连接查询员工名,部门名,工种名,并按部门名降序
select name,部门名,工种名
from 员工表 a
inner join 部门表 b on a.部门id=b.部门id
inner join 工种表 c on a.工种id=c.工种id
order by 部门名 desc;
# 2.非等值连接
例:查询员工的工资和工资级别的个数>20,并按工资级别降序
select name,工资,工资级别
from 工资表 a
join 工资等级表 b
on a.工资 between b.最低工资 and b.最高工资
group by 工资级别
having count(*)>20
order by 工资级别 desc;
# 3.内连接
例如:查询员工名和上级领导的名字
+------+------+------+
| id | name | lid |
+------+------+------+
| 1 | fa | NULL |
| 2 | fb | 1 |
| 3 | fc | 1 |
+------+------+------+
select a.id,a.name,b.name lname
from bbb a
inner join bbb b
on a.lid=b.id;
+------+------+-------+
| id | name | lname |
+------+------+-------+
| 2 | fb | fa |
| 3 | fc | fa |
+------+------+-------+
# 4.外连接
场景:常用于查询一个表中有另一个表中没有的数据.
'分主表和从表,查询结果为主表所有记录,以主表数据去从表中查找,没有结果的部分返回null'
1)left outer join左外连接,left左边的是主表
2)right outer join右外连接,right右边的是主表
例如:查询女表中所有男友的名字.
女表tf ,
+------+------+------+
| id | name | bf |
+------+------+------+
| 1 | fa | 2 |
| 2 | fb | 3 |
| 3 | fc | 4 |
| 4 | fd | 2 |
+------+------+------+
男表tm
+------+------+
| id | name |
+------+------+
| 1 | ma |
| 2 | mb |
| 3 | mc |
+------+------+
#左外连接
select tf.*,tm.name
from tf
left outer join tm
on tf.bf=tm.id
order by id;
+------+------+------+------+
| id | name | bf | name |
+------+------+------+------+
| 1 | fa | 2 | mb |
| 2 | fb | 3 | mc |
| 3 | fc | 4 | NULL |
| 4 | fd | 2 | mb |
+------+------+------+------+
#右外连接
select tf.*,tm.name
from tm
right outer join tf
on tf.bf=tm.id;
结果同上
#查询男表中所有的女友的名字.
select tm.*,tf.name gf
from tm
left outer join tf
on tf.bf=tm.id
order by id;
+------+------+------+
| id | name | gf |
+------+------+------+
| 1 | ma | NULL |
| 2 | mb | fd |
| ' 2 | mb | fa '|
| 3 | mc | fb |
+------+------+------+
'相同字段有多个返回值的时候会自动重复多行显示.'
# 全外连接(mysql不支持)full outer join
select tf.*,tm.*
from tf
full outer join tm
on tf.bf=tm.id
所得结果为以下两者的合集:
查询女表中所有男友的名字.
查询男表中所有女友的名字.
全外连接=内连接结果+表1有表2无+表2有表1无
# 交叉连接
select tf.*,tm.*
from tf
cross join tm;
结果为笛卡尔乘积
+------+------+------+------+------+
| id | name | bf | id | name |
+------+------+------+------+------+
| 1 | fa | 2 | 1 | ma |
| 1 | fa | 2 | 2 | mb |
| 1 | fa | 2 | 3 | mc |
| 2 | fb | 3 | 1 | ma |
| 2 | fb | 3 | 2 | mb |
| 2 | fb | 3 | 3 | mc |
| 3 | fc | 4 | 1 | ma |
| 3 | fc | 4 | 2 | mb |
| 3 | fc | 4 | 3 | mc |
| 4 | fd | 2 | 1 | ma |
| 4 | fd | 2 | 2 | mb |
| 4 | fd | 2 | 3 | mc |
+------+------+------+------+------+
3. useing的使用
1 using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件。
示例： select a.*, b.* from a left join b using(colA); 等同于： select a.*, b.* from a left join b on a.colA = b.colA;
2 多表查询就使用多个这样的join。
示例： select a.*, b.* from a left join b using(colA) left join c using(colB);
要求colA在a, b中均存在，colB在a, c表中均存在。
总之，using() 只是join中指定连接条件的简写，在简单的连接中常用。在列名称不同时或连接条件复杂时就无法用了，使用 a left join b on ... 是更常见的做法。
4. (@i:=@i+1)的使用
可以在生成查询结果表的时候生成一组递增的序列号。MySQL中没有这个伪列，但是有时候要用，可以用如下方法模拟生成一列自增序号。
（1）sql示例:select (@i:=@i+5) as rownum, surname, personal_name from student, (select @i:=100) as init;
解释: 上述sql中，红色值为自定义的初始序号，蓝色值为递增规则，上述sql运行结果如下
当然一般不会这么用，简单的从1开始递增就行
select (@i:=@i+1) as rownum, surname, personal_name from student, (select @i:=0) as init;
(2) 如果是多表联查，跟上述sql类似，连查完后使用(select @i:=0)定义一个初始化序列号,并且必须给这个序列号列使用别名as aa：
select (@i:=@i+1) as rownum, A.surname, CONCAT(A.surname,A.personal_name) uname
from student A
left join student B
on A.name = B.name,
(select @i:=0) as aa;
或者
select (@i:=@i+1) as rownum, A.surname,CONCAT(A.surname,A.personal_name) uname
from student A,(select @i:=0) as aa;
5. like(binary)和regexp的使用
- like对任意多字符匹配或任意单字符进行模糊匹配.
- regexp支持正则表达式的匹配方式，提供比 like 更多的匹配方式.
- binary区分大小写匹配.
select * from person where uname like '%sql%';
select * from person where uname like binary '%SQL%';
select * from person where uname regexp '.SQL*.';
