MySQL函数

MySQL函数分为两种： 单行函数：做处理 ，分组函数：做统计

1.单行函数

字节数

SELECT LENGTH('1234哈哈') AS len -- 字节数

查看客户端字符集

SHOW VARIABLES LIKE '%char%'

字符串连接 大写 小写

SELECT CONCAT('a','b','c') 

SELECT UPPER('ASddfDSU')

SELECT LOWER('ASddfDSU')

索引从1开始，从某一位开始的截取 字符长度

SELECT SUBSTR('helloworld', 6)

从某一索引开始，截取某段长度 字符长度

SELECT SUBSTR('helloworld',1,3)

SELECT CONCAT(  UPPER( SUBSTR(last_name, 1, 1) )    ) FROM employees

返回起始索引 如果找不到返回0

SELECT INSTR('helloworld','or')

去前后空格

SELECT TRIM('  vdfsv  scs  ')

去掉首尾的o

SELECT TRIM('o' FROM 'ooooooooooooooooooheoooolloooooooooooooooooooooooooooooooo')

指定字符左填充到指定长度，如果超过，右边的被截断

SELECT LPAD('hello',10,'*')
SELECT LPAD('hellohellohtllohello',10,'*')

指定字符右填充到指定长度，如果超过，右边的被截断

SELECT RPAD('hello',10,'*')
SELECT RPAD('hellohellohtllohello',10,'*')

替换

SELECT REPLACE('王老八夜里打酱油和酱油','酱油','酒')

数学函数

ROUND(x) 四舍五入

SELECT ROUND(1.65)

ROUND(x, y) 小数保留

SELECT ROUND(1.6545, 2)
SELECT ROUND(1.4578, 2)

上取整，返回大于等于参数的最小整数

SELECT CEIL(1.025)
SELECT CEIL(1.00)
SELECT CEIL(-1.025)
SELECT CEIL(-1.00)

下取整，返回小于等于参数的最大整数

SELECT FLOOR(1.021)
SELECT FLOOR(9.6)
SELECT FLOOR(-9.6)

截断

SELECT TRUNCATE(1.65,1)

%:余数

SELECT MOD(-11,3)
SELECT MOD(11,3)

日期时间函数

SELECT NOW()

SELECT CURRENT_TIME()
SELECT YEAR(NOW())
SELECT YEAR('1996-08-14')

SELECT MONTH(NOW())
SELECT MONTHNAME(NOW())

字符通过指定格式转换成日期

SELECT STR_TO_DATE('14-8-1996','%d-%m-%Y')
SELECT STR_TO_DATE('14-8-96','%d-%m-%y')

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

SELECT DATE_FORMAT(NOW(),'%y-%m-%d')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')
SELECT DATE_FORMAT(NOW(),'%c-%d %Y')

SELECT last_name, DATE_FORMAT(hiredate,'%y年/%m月 %d日') AS hiredate FROM employees

流程控制函数

SELECT IF(1=1,1,0)
SELECT IF(1=41,1,0)

SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, '有奖金', '没奖金') FROM employees 
ORDER BY commission_pct DESC

/*case结构1 类似switch 适合判断等值运算*/
SELECT
	last_name,
	department_id,
	salary,
CASE
		department_id 
		WHEN 30 THEN
		salary * 1.2 
		WHEN 40 THEN
		salary * 1.5 ELSE salary 
	END AS new_salary 
FROM
	employees

/*case结构1 类似多重if 适合判断范围运算*/

SELECT
	last_name,
	department_id,
	salary,
	CASE 
		WHEN salary > 20000 THEN 'A'
		WHEN salary > 15000 THEN 'B'
		WHEN salary > 10000 THEN 'C'
	ELSE
		'D'
END AS 工资级别 
FROM employees;

2.分组函数

分组函数用作统计，又叫组函数，聚合函数，统计函数

求和，忽略空值

SELECT SUM(salary) FROM employees;

平均，忽略空值

SELECT AVG(salary) FROM employees;

最大最小值，忽略空值

SELECT MIN(salary) FROM employees
SELECT MAX(salary) FROM employees

SELECT MAX(last_name), MIN(last_name) FROM employees
SELECT MAX(hiredate), MIN(hiredate) FROM employees

count函数
计算非空的值的个数，myisam下 COUNT()效率最高，INNODB下 COUNT() COUNT(1) 效率差不多，COUNT(字段)效率最低。

SELECT COUNT(employees.commission_pct) FROM employees
SELECT COUNT(employees.employee_id) FROM employees

可以用来查询总行数，某一列一个字段有值就统计上

SELECT COUNT(*) FROM employees

加上常量值，相当于表中添加一列，可以用来查询总行数

SELECT COUNT(1) FROM employees

DISTINCT 去重后统计

SELECT count(salary) FROM employees
SELECT count(DISTINCT salary) FROM employees

分组查询

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

每个部门的平均工资

SELECT  AVG(salary) , department_id FROM employees GROUP BY department_id

工种最高工资

SELECT MAX(salary) salary, job_id FROM employees GROUP BY job_id

每个领导下有奖金的员工的最高工资（分组前的筛选）

SELECT MAX(salary), manager_id FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id

哪个部门的员工数量大于2（分组后的筛选：HAVING）

SELECT COUNT(*) as count , department_id FROM employees GROUP BY department_id
HAVING count > 2

每个工种有奖金的员工最高工资大于12000的工种编号和最高工资

SELECT MAX(salary) as max, job_id FROM employees 
WHERE  commission_pct is not null 
GROUP BY job_id
HAVING max > 12000

领导编号大于102 的员工最低工资大于5000的 领导

SELECT min(salary) as min, manager_id FROM employees 
WHERE manager_id > 102
GROUP BY manager_id
HAVING min > 5000

按表达式筛选

按员工姓名长度分组，查员工个数大于5的姓名长度有几个

SELECT count(*) as count, LENGTH(last_name) len from employees 
GROUP BY len
HAVING count > 5

多个字段分组

每个部门每个工种的平均工资

SELECT AVG(salary) as salary, job_id, department_id FROM employees
GROUP BY job_id, department_id
ORDER BY salary DESC

每个部门每个工种的平均工资中大于10000的

SELECT AVG(salary) as salary, job_id, department_id FROM employees
GROUP BY job_id, department_id
HAVING salary > 10000
ORDER BY salary DESC

查各工种平均最大最小和总和

select 
	sum(salary) as sum,
	min(salary) as min,
	max(salary) as max,
	avg(salary) as avg,
	job_id 
FROM employees
	GROUP BY job_id
	ORDER BY job_id DESC

3.自定义函数

1. 函数和存储过程的区别

   函数只能有一个返回，而且必须有返回，存储过程适合批量插入，更新，函数适合处理数据后，得到一个结果，适合查询。

2. 创建

CREATE FUNCTION 名(参数列表) RETURNS 返回类型
BEGIN
  函数体
	(必须有return 语句)
	(函数体只有一句话，可以省略BEGIN END)
END

delimiter $
CREATE FUNCTION fun1() RETURNS INT
BEGIN
	DECLARE count INT DEFAULT 0;
	
	SELECT COUNT(*) INTO count 
	FROM myemployees.employees;
	
	RETURN count;
END $

delimiter $

CREATE FUNCTION getPriceFromId(empId INT) RETURNS DOUBLE
BEGIN
	DECLARE price DOUBLE DEFAULT 0.0;
	SELECT employees.price INTO price FROM employees WHERE employee_id = empId;
	RETURN price;
END $

3. 调用

SELECT fun1();

4. 查看函数

SHOW CREATE FUNCTION getPriceFromId

5. 删除函数

DROP FUNCTION getPriceFromId

6. 循环结构

• WHILE :

标签:WHILE 条件 DO
	循环体
END WHILE 标签;

• LOOP:

标签: LOOP
	循环体;

	IF 退出条件 THEN
		LEAVE 标签; 
	END IF; 
	
END LOOP 标签;

• REPEAT:

标签:REPEAT
	循环体
UNTIL 结束条件 END REPEAT 标签;

7. 循环控制语句：

• ITERATE(继续，结束本次继续下次)

• LEAVE (break,跳出所在循环)