#案例：表字段说明
-- 注释：员工编号，员工姓名，领导姓名，领导编号，入职时间，工资，奖金，部门编号
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');
