MySQL学习笔记2

MySQL学习笔记2

#进阶4：常见函数
/**
功能：类似于java的方法；将一组逻辑语句封装在方法体重，对外暴露方法名
好处1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) from 表;
特点：
	1.函数名
	2.函数功能
分类：
	1.单行函数
	如concat、length、ifnull等
	2.分组函数
	功能：做统计使用

*/


#一、字符函数
#length获取参数指定字节个数
SELECT LENGTH('哈哈')

#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) FROM employees;
#3.upper 、lower 大小写
#案例：将姓变大写，名变小写，然后拼接
SELECT 
  CONCAT(UPPER(last_name),' ',LOWER(first_name))
 FROM employees;
 
 #4.substr 、substring【截取字符串】
 #注意索引从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,LENGTH(last_name)-1)))
 FROM employees;
 
 #instr 返回子串第一次出现的缩影，如果找不到返回0
 
 SELECT INSTR('杨不悔爱上了11','11');
 
 #6.trim去除首尾字符
 SELECT TRIM(' 1                ' ) AS out_put;
 SELECT TRIM('a' FROM 'aaaaaaaaaaaa1aaaaaaaaa1aaaaaaaaaaa' ) AS out_put;
 
 #7.lpad 用指定的字符实现左填充指定长度
 SELECT LPAD('殷素素',10,'*')  AS out_put
 #8.rpad 用指定的字符实现右填充指定长度
 SELECT RPAD('殷素素',10,'*')  AS out_put
 
 #9.replace 替换
SELECT REPLACE ('张无忌爱上了周芷若周芷若','周芷若','赵敏') AS out_put;
 
 
#二、数学函数
#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);#小数点后保留两位


#ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(1.52);
#floor 向下取整
SELECT FLOOR(9.99);
#truncate 截断小数点后截断一位
SELECT TRUNCATE(1.00,1);

#MOD取余=10%3
SELECT MOD(10,3);

#三 日期函数
#弄完 返回当前系统日期+时间
SELECT NOW();

#CURDATE 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间、不包含日期
SELECT CURTIME();

#可以获取指定的部分，年月日，小时分钟秒 按照英文名使用
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

#str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1997-07-25','%Y-%c-%d');

#案例查询入职日期为1992--4--3的入职员工的信息
SELECT * FROM employees
WHERE hiredate=STR_TO_DATE('1992-4-3','%Y-%c-%d');

#date_format:将日期转换为字符

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日%H时%i分%s秒');

#案例查询有奖金的员工名和入职日期(xx月/xx日)
SELECT 
	CONCAT(UPPER(last_name),LOWER(first_name)),DATE_FORMAT(hiredate,'%Y年%m月%d日')
FROM employees
WHERE commission_pct IS NOT NULL;
 
#四、其他函数
SELECT VERSION()
SELECT DATABASE()
SELECT	USER()

#五、流程控制函数
#1.if 函数 ：if else的效果
SELECT IF(10>5,'da','xiao');
SELECT last_name,commission_pct,DATE_FORMAT(hiredate,'%Y-%m-%d'),
 IF(commission_pct IS NULL,'没奖金，呵呵','有奖金嘻嘻')
 FROM employees;
 
 
 #2.CASE函数的使用一：switch case的效果
 /*
 case 要判断的字段或者表达式
 when 常量1 then 要显示的值1或语句1;
 when 常量2 then 要显示的值1或语句1;
 ...
 else 要显示的值n或语句n:
 end
 */

#案例：查询员工的工资，要求
/*
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为原工资
其他部门，显示的工资为原工资


*/


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;


#3.case 函数的使用二；类似于多重if
/*
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值1或语句2
when 条件3 then 要显示的值1或语句3
when 条件4 then 要显示的值1或语句4
...
else 要显示的值n或语句n
end
*/

#案例 ：查询员工的工资的情况
如果工资大于20000 A
如果工资大于15000 B
如果工资大于10000 C
否则显示，d级别

SELECT salary,CONCAT(last_name,first_name),DATE_FORMAT(hiredate,'%y%m%d'),
CASE
WHEN salary>20000 THEN  'A'
WHEN salary>15000 THEN  'B'
WHEN salary>10000 THEN  'C'
ELSE  'd'
END

FROM employees;

 

#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数
特点
1、sum、avg、只用于数值处理
   max，密码，count可以处理任何类型
2、以上分组函数忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的介绍
一般使用count(*)统计行数

5、和分组韩式一同查询的字段要求是group by后的字段
*/
#1.简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary)FROM employees;

SELECT SUM(salary),ROUND(AVG(salary),2),MIN(salary),MAX(salary),COUNT(salary) FROM employees;

#2、参数支持哪些类型

SELECT SUM(last_name),AVG(last_name) FROM employees;#不支持字符型，仅支持数值型

#3、是否忽略null

#4、和distinct 搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;


#5、count函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;#统计行数
SELECT COUNT(1) FROM employees;#好比在原数据库中加了一列1，有多少个1就有多少行

/*效率：
myisam 存储引擎下，count(*)的效率高
innodb 存储引擎下，count(*),count(1)效率差不多，比count(字段)效率高*/


#6、和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;

#查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）用到一个函数datediff
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference
 FROM employees;

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