SQL语句--mysql常用函数（流程函数、字符串函数、数学函数、日期函数、聚合函数、分组函数、加密函数）

#案例：表字段说明
-- 注释：员工编号，员工姓名，领导姓名，领导编号，入职时间，工资，奖金，部门编号
CREATE TABLE `employee` (
  `empid` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `job` varchar(30) DEFAULT NULL,
  `leaderid` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  `wage` decimal(10,2) DEFAULT NULL,
  `prize` decimal(10,2) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 添加数据
INSERT INTO `employee` VALUES 
('2069', 'JALEN', 'CLERK', '7902', '2009-12-17 00:00:00', '18000.00', null, '20'),
('3099', 'WANRE', 'SALESMAN', '7698', '2010-02-20 00:00:00', '18000.00', '300.00', '30'),
('3021', 'FIKEN', 'SALESMAN', '7698', '2010-02-22 00:00:00', '16500.00', '500.00', '30'),
('3066', 'JONES', 'MANAGER', '7839', '2011-04-02 00:00:00', '16000.00', null, '20'),
('3054', 'RANKEE', 'SALESMAN', '7698', '2012-09-28 00:00:00', '16500.00', '1400.00', '30'),
('3098', 'BLAKE', 'MANAGER', '7839', '2013-05-01 00:00:00', '16000.00', null, '30'),
('1082', 'CALAN', 'MANAGER', '7839', '2014-06-09 00:00:00', '16000.00', null, '10'),
('2088', 'SCOTT', 'ANALYST', '7566', '2015-04-19 00:00:00', '16000.00', null, '20'),
('3039', 'DIVE', 'PRESIDENT', null, '2016-11-17 00:00:00', '15000.00', null, '10'),
('3044', 'TURNER', 'SALESMAN', '7698', '2016-09-08 00:00:00', '15000.00', '0.00', '30'),
('2076', 'JULI', 'CLERK', '7788', '2017-05-23 00:00:00', '11000.00', null, '20'),
('3000', 'JAMES', 'CLERK', '7698', '2017-12-03 00:00:00', '9500.00', null, '30'),
('2002', 'FAXI', 'ANALYST', '7566', '2017-12-03 00:00:00', '9000.00', null, '20'),
('1034', 'MOKA', 'CLERK', '7782', '2018-01-23 00:00:00', '8800.00', null, '10');


#常见函数-------------------
-- 1.1流程函数
	
	-- ifnull: 判断值是否为null,指定值填充
	select  IFNULL(comm,0) +100 from emp;  #查询表emp的comm值，如果为null，赋值0给他，然后comm字段整体加100
	select * ,IFNULL(comm,0) +100 as comm1 from emp where comm=0 or comm is null;  #等于0或null的 +100,但是其他值的没有输出
	
	-- if(···,···,···) :分支 ; 
	-- if(···,···,if(···,···,···)) :嵌套
	select *, if(comm is null or comm =0,IFNULL(comm,0) +100,comm) as comm1 from emp;#等于0或null的 +100,并输出全部
	select *, if(comm is null,IFNULL(comm,0)+100,if(comm=0,comm+100,comm)) as comm1 from emp;
	select *, if(sal>=4000,"高级",if(sal>=2000,"中级","初级")) from emp;
	
	-- case: 多分支
	select *,(case when sal>=4000 then "高级" when sal>=2000 then "中级" else "初级" end) d from emp; #常用
	select *,(case sal when sal>=4000 then "高级" when sal>=2000 then "中级" else "初级" end) d from emp; #了解
	select *,(case deptno when 10 then "部门1" when 20 then "部门2" when 30 then "部门3" else "部门4" end) d from emp; #了解
	
	-- nullif(n1,n2) #如果n1=n2返回null，否则返回n1
	select  nullif(1,1) ;
	
-- 1.2字符串函数
	
	select ascii("a"); #a的ascii为97
	select ascii(0);   #0的ascii为48
	
	select length("456"); #返回byte字节长度--->3
	select length(12.33); #---->5
	select length(12);  #---->2
	select length("我");  #---->3
	select length(comm) from emp;
	
	select char_length(12); #返回字符长度---->2
	select char_length("我"); #---->1
	
	select character_length("aa"); #---->2
	select character_length("我");#--->1
	
	select concat("a",111); #字符串拼接函数---->'a111'
	select concat("b",null,111); #---->null
	select concat("b",ifnull(null,""),111); #---->'b111'
	select concat(42.35);---->'42.35'
	select concat(ifnull(comm,"")) from emp;
	
	select concat_ws("--","158","2687",3309); #指定连接符号的字符串拼接函数--->158--2687--3309
	select concat_ws("~",deptno,empno) from emp;

	select upper("asdvf"); #字母转化为大写---->'ASDVF'
	select upper(ename) from emp;
	
	select lower("ASDC"); #字母转化为小写 ---->'asdc'
	select lower(ename) from emp;
	
	select instr("abcdddd","cd"); #查找某子字符串在字符串中首次出现的位置，返回0表示无 --->3
	select ename,instr(ename,"a") from emp;
	
	select locate("ca","bcadddd");  #查找某子字符串在字符串中首次出现的位置，返回0表示无 --->2
	select ename,locate("a",ename) from emp;
	
	select locate("a","qwabcddadd",4);#从指定位置查找，前面有也不返回--->8
	select locate("a","cdaswqggg",4) #----->0
	select ename,locate("a",ename,2) from emp;
	
	select reverse("abcd");#将字符串倒序-->'dcba'
	
	select left("abcd",2);#从左边截取指定长度的字符串--->'ab'
	select empno,left(empno,2) from emp;
	
	select right("abcd",2);#从右边截取指定长度的字符串--->'cd'
	select empno,right(empno,2) from emp;
	
	select mid("abcd",2,2);#从指定位置截取指定长度的字符串--->'bc'
	select empno,mid(empno,2,3) from emp;
	
	select substr("wwssd",2);#从左边第n个字符开始截取后面的字符-->'wssd'
	select substr("wwssd" from 2);#-->'wssd'
	select substr("wwssd",-2);#从右边开始倒着数第n个字符截取-->'sd'
	select substr("abcd",2,2);#指定长度截取-->'bc'
	select substr("abcd" from 2 for 2);#-->'bc'

	select substring("abcd",2,2);#和substr()用法一样-->'bcd' 
	
	select Ltrim(" q 12s f ");#只去除字符串左边空格-->'q 12s f '
	select rtrim(" a dd d  ");#只去除字符串右边空格-->' a dd d'
	select rtrim(ltrim(" a ks "));#去除左右两端空格-->'a ks'
	select replace(" a ff "," ","");#去除所有空格-->'aff'
	
	select replace("asd-11","asd","a"); #字符替换 -->'a-11'
	select replace(ename,'A','a') from emp;
	
	select insert("18569117889",4,4,"****");#指定位置替换-->'185****7889'
	select insert(mgr,2,2,"**") as mgr from emp;
	
	select repeat("qw",2); #将字符串重复返回 -->'qwqw'
	# str长度不够len，使用s左侧填充
	lpad(str,len,s)
	# str长度不够len,使用s右侧填充
	rpad(str,len,s)

	#LOAD_FILE（file_name）#读取文件且返回文件内容为字符串
	SHOW VARIABLES LIKE 'secure_file_priv'; #查看权限，secure_file_priv = null 则修改配置文件my.ini
	show global variables like '%secure%';    #在[mysqld]下配置内容：secure-file-priv=""
	SELECT LOAD_FILE("E:\123.txt") AS Result;

-- 1.3数学函数
		select PI(); # 3.141593
		select abs(-1);  #绝对值
		select ceil(12.3);  # 向上取整  13
		select ceil(-12.3);  # -12
		select ceiling(12.3); #ceiling() ==ceil()  13
		select floor(12.3);  # 向下取整  12
		select floor(-12.3);  # 向下取整 -13
		select rand();   # [0,1) 随机数
		select mod(5,2);  # 取模/取余  1
		select round(153.453,-1);  # 四舍五入  120
		select round(153.453,-2); # 200
		select round(153.453,0); # 153
		select round(153.453,1);# 153.5
		select round(153.453,2);# 153.45

-- 1.4日期函数	
		select now();#返回当前日期和时间
		select current_timestamp();#返回当前日期和时间
		select current_date(); #返回当前日期
		select current_time(); #返回当前时间
		select date_format(now(),"%Y-%m-%d");#转化时间为指定格式
		select unix_timestamp();#返回当前时间的时间戳
		select unix_timestamp("2016-11-29");#返回指定日期的时间戳
		select from_unixtime(unix_timestamp());#将时间戳转化为日期格式
		
		select YEAR(now());#提取年份
		select YEAR('2013-11-29');#提取年份
		select month(now());#提取月份
		select month("2013-11-29");#提取月份
		select day(current_timestamp());#提取当前日期的天数
		select day('2013-11-29');#提取天数
		select week(now());# 返回一年中的周数
                select weekday(now());# 返回一周中的第几天(0-6)
        
                #EXTRACT(unit FROM date)，返回指定年、月、日、时、分、秒
                select extract(year FROM '2018-2-10');
                select extract(month FROM '2018-2-10');
                select extract(day FROM '2018-2-10');
                select extract(year_month FROM '2018-2-10');
		
		select date_add(now(),interval 2 month); #计算某个月后的今天
		select date_add('2013-11-29',interval 6 year);#计算某年后的某天
		select date_add(now(),intervar 3 day);#计算n天后的日期
		
		select date_sub(now(),interval 2 month);#计算前2个月的今天
		select date_sub('2019-11-29',interval 6 year);#减去n年后的日期
		select date_sub("2019-11-29",interval 6 day);#减去n天后的日期
		
		select timestampdiff(year,"2013-11-29",'2019-11-29'); #返回两个日期的时间差
		select timestampdiff(month,"2013-11-29","2019-11-29");
		select timestampdiff(day,"2013-11-29","2019-11-29");
		
		select last_day(now()); #返回当月的最后一天
		
-- 1.5聚合函数(max,min,avg,count,sum)   整张表一个结果
        select max(sal) from emp;
	    select min(sal) from emp;
		select avg(sal) from emp;
		select sum(sal) from emp;
		# 统计整张表的数目(记录数)
		select count(*) from emp;
		select count(1) from emp;
		# 统计该字段的数目(有效值,null不做统计)
		select count(sal) from emp;
		select count(comm) from emp;
		
-- 1.6分组函数 group by 分组字段 (先分组再聚合,每组一个结果)
	  # having子句: 和where类似,分组之后过滤
	  # 分组之后,select能够出现聚合函数,分组的字段
	  select deptno,avg(sal) avg from emp group by deptno;
	   
    # 查询平均工资>2000的部门的编号和平均工资
	    # 1.每个部门的平均工资
		# 2.平均工资中再筛选>2000的
	select deptno,avg(sal) avg from emp group by deptno having avg > 2000;
        
     # where和having实现区分:
		# 1.顺序: where --> group by --> having  
		# 2.能否使用聚合: having 可以使用		
	
-- 1.7加密函数(java/python --> 密码(两次md5加密,salt))
        # 密码必须加密
		select md5("root"); 
		select SHA("root");
		select password('root');