sql常用函数

                                                                                单行函数

字符函数：

• length 获取参数值的字节个数 

select length("john")

• concat 拼接字符串

select concat( last_name,"_",first_name ) as 姓名 form employees

• upper , lower 将参数值变大写，小写

select upper("john")

• substr, substring 字符串截取（索引从1开始，长度为字符长度）

//输出 陆展元 截取从第7个到尾部  

select substr('李莫愁爱上了陆展元',7)     

//输出 李莫愁 截取从第1个开始的3个字符

select substr('李莫愁爱上了陆展元',1,3)   

• instr 返回子串在字符串中的起始索引，如果找不到返回0

//输出 7

select instr('李莫愁爱上了陆展元','陆展元')

• trim 去除前后的空格或指定字符

//输出 李  莫  愁

select trim('   李  莫  愁  ') 

//输出 李aaa莫aaa愁

select trim('a', 'aaaaaaa李aaa莫aaa愁aaaaaaaa')

• lpad 在起始位置填充指定字符直到整个字符串达到指定长度

//输出 *******李莫愁   

select lpad('李莫愁',10,'*')

 

//字符串最后输出长度一定是指定的长度，如果不足则用指定字符左填充，如果超过，则从开始处删减字符直到长度等于指定长度   

select lpad('李莫愁',2,'*')   //输出 莫愁

• rpad 在末尾位置填充指定字符直到整个字符串达到指定长度

• replace 替换

//输出 小龙女爱上了陆展元   

select replace('李莫愁爱上了陆展元','李莫愁','小龙女')

 

---

数学函数：

• round 四舍五入

//输出 2

select round(1.65)

//输出 1.66  小数点后保留2位

select round(1.657，2)

• ceil 向上取整，返回>=该参数的最小整数

• floor 向下取整，返回<=该参数的最大整数

//输出 -1

select ceil(-1.65)

//输出 -2

select floor(-1.65)

• truncate 截断

//输出 1.6 从小数点后1位截断

select truncate(1.65，1)

• mod 取余

//输出 1 相当于10%-3

select mod(10，-3)

 

---

日期函数：

• now 返回当前系统日期+时间

• curdate 返回当前系统日期，不包含时间

• curtime 返回当前时间，不包含日期

//可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;

SELECT YEAR('1998-1-1') 年;

SELECT MONTH(NOW()) 月;

SELECT MONTHNAME(NOW()) 月; //输出月份英文

• str_to_date 将字符通过指定的格式转换成日期

// 1998-03-02

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') ;

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

 

• date_format 将日期转换成字符

// 20年10月08日

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

 

---

其他函数：

• SELECT VERSION()   查看版本号

• SELECT DATABASE() 查看当前的库

• SELECT USER()  查看 当前用户

 

---

流程控制函数：

• if： if else 的效果

// 小

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

//

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') as 备注

FROM employees;

• case ： switch case 的效果

/*

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1;

when 常量2 then 要显示的值2或语句2;

...

else 要显示的值n或语句n;

end

*/

 

/*

案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍

部门号=40，显示的工资为1.2倍

部门号=50，显示的工资为1.3倍

其他部门，显示的工资为原工资

*/

 

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

when 条件1 then 要显示的值1或语句1

when 条件2 then 要显示的值2或语句2

。。。

else 要显示的值n或语句n

end

*/

 

/*

#案例：查询员工的工资的情况

如果工资>20000,显示A级别

如果工资>15000,显示B级别

如果工资>10000，显示C级别

否则，显示D级别

*/

 

SELECT salary,

CASE

WHEN salary>20000 THEN 'A'

WHEN salary>15000 THEN 'B'

WHEN salary>10000 THEN 'C'

ELSE 'D'

END AS 工资级别

FROM employees;

 

 

---

 

 

分组函数

• sum 求和     忽略nul值

• avg 平均值   忽略nul值

• max 最大值  忽略nul值

• min 最小值  忽略nul值

• count 计算个数  忽略nul值

可以搭配distinct实现去重求值

//输出 表中所有行的salary列的和

select sum(salary) from employees

//输出 表中所有行的salary列非空的有几个，若全部不为null，则为表的行数

select count(salary) from employees

//输出 表中salary列不相同的且非空的有几个

select count(distinct salary) from employees

//输出 表有多少行

select count(1) from employees

select count(*) from employees

计算表行数，效率比较：

myisam存储引擎下   count(*) 的效率高

innodb存储引擎下    count(*)和count(1)效率差不多，比count(字段)要高

因而统计行数时，使用count(*)统计较好

 

 

分组查询

• group by

 

/*

语法：

select 查询列表

from 表

【where 筛选条件】

group by 分组的字段

【order by 排序的字段】;

 

#案例1：查询每个工种的员工平均工资

SELECT AVG(salary),job_id

FROM employees

GROUP BY job_id;