一、常用函数
1、语法
select 函数名(实参列表)from 表名;
2、分类
2.1 单行函数
(1)字符函数
1.length :获取参数值的字节个数。 SELECT LENGTH('john');
2.concat :拼接字符串。上节有例子。
3.upper :大写。 SELECT UPPER('john');
4.lower :小写。
5.substr :截取字符串。SELECT SUBSTR('今年我一定能顺利看华晨宇演唱会',10);
,输出结果是华晨宇演唱会。mysql中的索引从1开始。SELECT SUBSTR('今年我一定能顺利看华晨宇演唱会',10,3);
,输出结果是华晨宇。
6.instr :返回子串的第一次出现的索引(找不到返回0)。SELECT INSTR('今年我一定能顺利看华晨宇演唱会','华晨宇演唱会');
输出结果是10。
7.trim :去除指定字符。SELECT TRIM('啊' FROM '啊啊啊华晨宇啊啊啊啊');
输出结果是华晨宇。
8.lpad :左填充。SELECT LPAD( '华晨宇',10,'*');
输出结果是*******华晨宇。若SELECT TRIM( '华晨宇',2,'*');
输出结果是华晨。
9.rpad :右填充。SELECT RPAD( '华晨宇',10,'*');
输出结果是华晨宇*******。
10.replace :替换。SELECT REPLACE('今年我一定能顺利看华晨宇演唱会','我','华晨宇');
输出结果是今年华晨宇一定能顺利看华晨宇演唱会。
(2)数学函数
1.round :四舍五入。SELECT ROUND(1.65);
,结果是2。SELECT ROUND(1.657,2);
,结果是1.66。
2.ceil :向上取整,返回>=该参数的最小整数。SELECT CEIL(1.65);
,结果是2。SELECT CEIL(1.02);
,结果是2。SELECT CEIL(-1.02);
,结果是-1。
3.floor :向下取整,返回<=该参数的最小整数。SELECT FLOOR(-9.99);
,结果是-10。
4.truncate :截断。SELECT TRUNCATE(1.65,1);
,结果是1.6。
5.mod :取余,等同于%。SELECT MOD(10,3);
,结果是1。SELECT MOD(-10,-3);
,结果是-1(a%b=a-a/b*b)。
(3)日期函数
1.now :返回当前系统日期时间。SELECT NOW();
,结果是2021-01-18 22:08:27。
2.curdate :返回当前系统日期。
3.curtime :返回当前系统时间。
4.year :获取年份。SELECT YEAR(NOW());
,结果是2021。SELECT YEAR('1990-2-7');
,结果是1990。除此之外还有,month,day等。SELECT MONTHNAME(NOW());
,结果是January。
5.str_to_date :将日期格式的字符转换成指定格式的日期。SELECT STR_TO_DATE('27-02-1990','%m-%d-%Y');
,结果是1990-02-27。
6.date_format :将日期转换成字符。SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');
,结果是2018年06月06日。
(4)其他函数
1.version :查看数据库版本。
2.database :查看当前数据库。
3.user :查看当前用户。
(5)流程控制函数
1.if :SELECT IF(10>5,'大','小');
,结果是大。
2.case 结构 :
第一种:
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;(值的时候不用加分号)
when 常量2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end
第二种:
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end
2.2 分组函数
(又称统计函数,聚合函数、组函数)做统计使用。
(1)语法:
1.sum :求和。
2.avg :求均值。
3.max :求最大值。
4.min :求最小值。
5.count :计算个数。
(2)注意:
① sum、avg 一般用于处理数值类型,max,min,count可以处理任何类型。
② 以上分组函数都忽略了null值(null值不参与运算)。
③和distinct搭配使用,去重后再计算。
④ 和分组函数一同查询的字段要求是group by(下节内容)后的字段。
(3)count函数详细计算:
一般会这样使用:
SELECT COUNT(*) FROM employees;
表示统计employees表中的总行数(除去值为null的行)。
SELECT COUNT(1) FROM employees;
表示添加了一列1,统计employees表中为1的总行数(也就是统计employees表中的总行数,与上述语句同样功能)。
2.3 分组查询
(1)语法:
select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
【】内容不是必须条件
(2)注意:
查询列表必须特殊,要求是分组函数和group by后出现的字段。
(3)举例:
① 简单的分组查询
② 添加筛选条件
③ 添加复杂的筛选条件
案例1:
不能直接得出结果,要分以上两个步骤。此时在第二步中,若使用where count(*)>2
是错误的,因为where用在group by后且where后面不能直接加分组函数,此时就引出另一个关键词having。
案例2:
(4)group by后支持的其他字句:
1、表达式或函数
案例:
len_name是别名
group by和having后使用别名:
2、多个字段
案例:
group by后字段顺序可以颠倒。
3、添加排序
(5)总结:
1.分类
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by字句的前面 | where |
分组后筛选 | 分组后的结果集 | group by字句的后面 | having |
2.分组函数做条件肯定是放在having字句中;能用分组前筛选的,优先考虑使用分组前筛选。
3.group by字句支持单个字段分组,多个字段分组(多个字段分组之间用逗号隔开没有顺序要求),也可以添加排序(排序放到整个分组查询的最后)。
2.4 连接查询
(1)定义:
又称多表查询,当查询的字段来自多个表时,就会用到连接查询。
笛卡尔乘积现象:表1有m行,表2有n行,结果有m x n 行。发生原因:没有有效的连接条件。
(2)举例:
如上面两个表,查询表1对应表2的项,没有有效条件时,出现笛卡尔乘积现象导致得到以下结果:
加入有效连接条件时:
得到的结果如下:
(3)连接查询分类:
1.按年代分类:
sql92标准:仅支持内连接
sql99标准:支持内连接、外连接(左外和右外)、交叉连接
2.按功能分类:
内连接:等值连接、非等值连接、自连接
外连接:左外连接、右外连接、全外连接
交叉连接
2.4.1 sql92标准
2.4.1.1 等值连接
1、案例:
2、为表起别名:
当不同的表中出现了相同的字段名,此时查询时需要加表名来区分。
如上图,job_id在两个表中都出现了,此时需要添加表名来限定:
但是,当需要多次表名来限定,且表名比较复杂时,代码就会比较复杂,此时就用到了为表起别名(表名后加as 别名即可,同字段起别名相同,as可以省略)。
注意:当起别名后,不能再用原来的表名限定要查询的字段。
3、加筛选条件
4、加分组
5、加排序
6、实现三表连接
7、总结
① 多表等值连接的结果为多表的交集部分;
② n表连接,至少需要n-1个连接条件;
③ 多表的顺序没有要求;
④ 一般需要为表起别名;
⑤ 可以搭配前面介绍的所有字句使用,比如排序、分组、筛选等。
2.4.1.2 非等值连接
如上表,用左边表的salary来匹配右边表的lowest_sal和highest_sal,符合区间就得到对应的等级。
同理,也可以加排序、分组、筛选等字句。
2.4.1.3 自连接
一张表,自己和自己连接。
如上图,第二个员工,要查他的领导,首先可以查到他的领导编号100,再查员工编号是100的员工,则此员工就是要查的员工的领导。即一张表查了2次。
2.4.2 sql99标准
1、语法
连接类型
内连接:inner
外连接:左外:left【outet】、右外:rigth【outet】、全外:full【outet】
交叉连接:cross
2.4.2.1 内连接——等值连接
总结:
2.4.2.2 内连接——非等值连接
2.4.2.3 内连接——自连接
2.4.2.4 外连接
应用场景:用于查询一个表中有,另一个表中没有的记录。
若用beauty表匹配boys表,则beauty表为主表,boys表为从表,从主表的第一项开始匹配从表,匹配成功则得到对应项,否则得到null。结果如下图:
主从表的区分:
左外右外交换两个表的顺序,可以实现同样的效果。
举例:
左外连接
右外连接:
2.4.2.5 全外连接
这里不支持,所以只看下语法即可。
若支持,则结果应该是:
两个表的交集,以及表1未匹配成功表2的null对应的部分(外连接)再加表2剩下的部分。
即:
2.4.2.6 交叉连接
就是笛卡尔乘积的结果:
2.4.2.7 总结连接查询
2.5 子查询
(1)定义:
出现在其他语句中的select语句,称为子查询或内查询。
内部嵌套其他select语句的查询语句,称为外查询或主查询。
(2)分类:
1、按子查询出现的位置:
select后面(仅支持标量子查询)
from后面(支持表子查询)
where或having后面(支持标量子查询、列子查询、行子查询)
exists后面(支持表子查询)
2、按结果集的行列数:
标量子查询(也称单行子查询,结果集只有一行一列)
列子查询(也称多行子查询,结果集只有一列多行)
行子查询(也称多列子查询,结果集有一行多列)
表子查询(结果集一般为多行多列)
(3)具体使用:
2.5.1 where或having后面
特点:
① 子查询放在小括号内。
② 子查询一般放在条件右侧。
③ 标量子查询,一般搭配着单行操作符使用(>、<、>=、<=、=、<>等)。列子查询,一般搭配着多行操作符使用(in、any/some、all等)。
④ 子查询执行优先于主查询。
2.5.1.1 标量子查询
2.放两个子查询的情况:
3.having后:
4.非法使用标量子查询的情况
单行操作符后有多个结果:select salary得到多个结果,> 是单行操作符。
子查询不存在结果:没有部门号=250,子查询没有结果,主查询也就没有结果。
2.5.1.2 列子查询
1.操作符:
2.案例
把①选中部分放入②中any后即可(同时添加and后面内容如下图)。
还有另一种写法:
2.5.1.3 行子查询
结果为一行多列或多行多列,后者较少用。
要求查询的条件用相同的运算符,如本案例中用了=。
用之前的方法也可以做,分成三步:
2.5.2 select后面
2.5.2.1 标量子查询
2.
2.5.3 from后面
1.查询每个部门的平均工资的工资等级
2.5.4 exists后面(又称相关子查询)
1、语法
exists(完整的查询语句)
结果:1或0
2、案例
①
用in也可以做:
②
2.6 分页查询
(1)定义:
当要查询的数据一页显示不全的时候,需要分页提交sql请求。
(2)语法:
【】内容表示可选。
当offset=0的时候,可以省略。
(3)具体使用:
2.7 联合查询
(1)定义:
当要查询的内容来自多个表,且多个表没有直接的连接关系,但查询的内容一致时用到联合查询。
关键字:union 联合,合并:将多条查询语句的结果合并成一个结果。
(2)语法:
(3)特点:
(1)要求多条查询语句的查询列数是一致的。
(2)多条查询语句的查询的每一列的类型和顺序最好一致。
(3)union默认去重,若不想去重,可以在union后面加all。