SQL的语言分类&&数据查询语言 select 的学习（二） - zhang

#进阶4：常见函数

 

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）

二、单行函数
1、字符函数
concat:连接

SELECT CONCAT(last_name,"_",first_name) 姓名 FROM employees;
substr:截取子串

注意：索引从1开始

#截取从指定索引处后面所有字符SELECT SUBSTR("李莫愁爱上了陆展元",7) out_put;

#截取从指定索引处指定字符长度的字符

SELECT SUBSTR("李莫愁爱上了陆展元",1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",LOWER(SUBSTR(last_name,2))) out_put
FROM employees;

upper:变大写

SELECT UPPER("john");
lower：变小写

SELECT LOWER("joHn");
replace：替换

SELECT REPLACE("周芷若周芷若周芷若周芷若张无忌爱上了周芷若","周芷若","赵敏") AS out_put;
length：获取字节长度

SELECT LENGTH("john");
SELECT LENGTH("张三丰hahaha");

trim:去前后空格

SELECT LENGTH(TRIM(" 张翠山 ")) AS out_put;
SELECT TRIM("aa" FROM "aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") AS out_put;

lpad：左填充

SELECT LPAD("殷素素",10,"*") AS out_put;
rpad：右填充

SELECT RPAD("殷素素",12,"ab") AS out_put;
instr:获取子串第一次出现的索引

SELECT INSTR("杨不殷六侠悔爱上了殷六侠","殷八侠") AS out_put;
2、数学函数
ceil:向上取整，,返回>=该参数的最小整数

SELECT CEIL(-1.02);
round：四舍五入

SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

mod:取余数

mod(-10,-3)
#floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99);
truncate:截断

SELECT TRUNCATE(1.69999,1);
rand:获取随机数，返回0-1之间的小数

日期函数

now（）：返回当前日期+时间
year（date）:返回年
month（date）：返回月
day（date）:返回日
date_format:将日期转换成字符

SELECT last_name,DATE_FORMAT(hiredate,"%m月/%d日 %y年") 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

curdate（）:返回当前日期
str_to_date:将字符转换成日期

SELECT STR_TO_DATE("1998-3-2","%Y-%c-%d") AS out_put;

curtime（）：返回当前时间
hour（date）:小时
minute（date）:分钟
second（date）：秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月

4、其他函数
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password("字符")：返回该字符的密码形式
md5("字符"):返回该字符的md5加密形式

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

流程控制函数

①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

SELECT IF(10<5,"大","小");

SELECT last_name,commission_pct,IF(commission_pct IS NULL,"没奖金，呵呵","有奖金，嘻嘻") 备注   FROM employees;

②case情况1  用于等于的情况  java中 switch case 的效果
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

③case情况2   用于等于或者 大于  小于的情况  相当于 if  else if  else  if 的情况
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end

三、分组函数
1、分类
max 最大值

SELECT MAX(salary) FROM employees;
min 最小值

SELECT MIN(salary) FROM employees;
sum 和

SELECT SUM(salary) FROM employees;
avg 平均值

SELECT AVG(salary) FROM employees;
count 计算个数

SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

特点

①语法
select max(字段) from 表名;

②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型

③以上分组函数都忽略null

SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
④都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段)：统计该字段非空值的个数
count(*):统计结果集的行数
案例：查询每个部门的员工个数
1 xx 10
2 dd 20
3 mm 20
4 aa 40
5 hh 40

count(1):统计结果集的行数

效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)

⑥ 和分组函数一同查询的字段，要求是group by后出现的字段

 

SELECT DATEDIFF("1995-2-7","1995-2-6"); # 相差天数DATEDIFF

#3.查询部门编号为90的员工个数

SELECT COUNT(*) FROM employees WHERE department_id = 90;