#单行函数
#数值函数
#举例：
SELECT #取绝对值#取符号位- 为-1 +为1 0为0 # 32 向上取整 向下取整
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5),12%5 #取余数
FROM DUAL;
#取随机值
SELECT RAND(),RAND()#,RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
#四舍五入，截断操作
SELECT ROUND(123.456),ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1)##0 表示保留0位小数位 1保留一位小数因为是5所以进位
,ROUND(123.456,-1),ROUND(123.456,-2)#-1是从0开始也就是2的位置第二位看后一位四舍五入不足5舍去为120
FROM DUAL;#-2从3往前第二位四舍五入进位
#截断 没有四舍五入
SELECT TRUNCATE(123.456,0),TRUNCATE(123.456,1)
FROM DUAL;
#单行函数的嵌套
#ROUND保留两位小数 进位为123.5 truncate截断为123
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;
#三角函数略
#指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;
#进制之间的转换
# 十进制 十六进制 八进制 准换
SELECT BIN(10),HEX(10),OCT(19),CONV(10,2,8)#转换的数 要转换的数的进制 要转换成的进制
FROM DUAL;
#字符串函数
# 返回第一个字符的ASCII码
SELECT ASCII('aasda'),CHAR_LENGTH('thistest'),CHAR_LENGTH('测试'),#CHAR_LENGTH表示的是内存层面存取的字符数
LENGTH('thistest'),LENGTH('测试')#LENGTH的结果是底层存储的字节数
FROM DUAL;
#字符串的连接
SELECT CONCAT(emp.last_name,' Workfor ',mrg.last_name)
FROM employees emp JOIN employees mrg
ON emp.`manager_id`=mrg.`employee_id`;
SELECT CONCAT_WS('+','asdad','fafa','thistest','jkask')
FROM DUAL;
#字符串的索引从1开始的！
SELECT INSERT('helloworrld',2,3,'aaaaa')#从索引为2的位置开始替换后3个字符串
FROM DUAL;
SELECT REPLACE('hello','ll','aa')#若字符串中没有指定要替换的字符串则替换失败
FROM DUAL;
SELECT UPPER('hello'),LOWER('HELLO')#大小写转换
FROM DUAL;
SELECT RIGHT('hello',2),LEFT('hello',3)
FROM DUAL;
#LPAD:实现右对齐效果
#RPAD:实现左对齐效果
SELECT employee_id,last_name,LPAD(salary,20,' ')
FROM employees;
SELECT employee_id,last_name,RPAD(salary,20,' ')
FROM employees;
#去除首尾空格
SELECT LENGTH(TRIM(' h el lo '))
FROM DUAL;
SELECT CONCAT ('---',TRIM(' h el lo '),'----')
FROM DUAL;
#指定字符去除首尾
SELECT TRIM('o' FROM 'oohellooo')
FROM DUAL;
#去除两个o，一个不去除
SELECT TRIM('oo' FROM 'oohellooo')
FROM DUAL;
/*
ASCII(S) 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s) 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn) 连接s1,s2,......,sn为一个字符串
CONCAT_WS(x,s1,s2,......,sn)同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x INSERT(str, idx, len,replacestr)
将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母
LEFT(str,n) 返回字符串str最左边的n个字符
RIGHT(str,n) 返回字符串str最右边的n个字符
LPAD(str, len, pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) 用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s) 去掉字符串s左侧的空格
RTRIM(s) 去掉字符串s右侧的空格
TRIM(s) 去掉字符串s开始与结尾的空格
TRIM(s1 FROM s) 去掉字符串s开始与结尾的s1
TRIM(LEADING s1FROM s)去掉字符串s开始处的s1TRIM(TRAILING s1FROM s)去掉字符串s结尾处的s1
REPEAT(str, n) 返回str重复n次的结果
SPACE(n) 返回n个空格
STRCMP(s1,s2) 比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)
返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、
MID(s,n,len)相同LOCATE(substr,str)
返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr
IN str)、INSTR(str,substr)相同。未找到，返回0ELT(m,s1,s2,…,sn)
返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn) 返回字符串s在字符串列表中第一次出现的位置
*/
#日期和时间函数
#略写
/*
CURDATE() ，CURRENT_DATE()返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /
LOCALTIMESTAMP()返回当前系统日期和时间
UTC_DATE()返回UTC（世界标准时间）日期
UTC_TIME()返回UTC（世界标准时间）时间
*/
SELECT NOW(),DATE_ADD(NOW(),INTERVAL -1 MONTH)
FROM DUAL;
#例子
/*
YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
HOUR(time) / MINUTE(time) /
SECOND(time)返回具体的时间值
MONTHNAME(date) 返回月份：January，...
DAYNAME(date) 返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date) 返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date)
返回周几，注意：周日是1，周一是2，。。。周六是7
*/
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
/*
EXTRACT(type FROM date) 返回指定日期中特定的部分，type指定返回的值
*/
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
/*
TIME_TO_SEC(time)将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟*60+秒
SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间
*/
/*
DATE_ADD(datetime, INTERVAL expr type)，
ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，
SUBDATE(date,INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期
*/
#计算时间相关的函数
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
/*
ADDTIME(time1,time2)返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数SUBTIME(time1,time2)返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数
DATEDIFF(date1,date2) 返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2) 返回time1 - time2的时间间隔
FROM_DAYS(N) 返回从0000年1月1日起，N天以后的日期
TO_DAYS(date) 返回日期date距离0000年1月1日的天数
LAST_DAY(date) 返回date所在月份的最后一天的日期
MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n) 返回time加上n后的时间
*/
#例子
SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-
01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
#日期的格式化
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %r %W %w %T')
FROM DUAL;
#解析 格式化的逆过程
SELECT STR_TO_DATE('2022-April-25th 03:16:53 ','%Y-%M-%D %h:%i:%S ')
FROM DUAL;
SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;#获取格式
#关于获取格式的用法
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
#流程控制函数
#IF(VALUE,VALUE1,VALUE2)如果value的值为TRUE，返回value1，否则返回value2
SELECT e.last_name,salary,IF(salary>6000,'高工资','低工资') details
FROM employees e
ORDER BY e.salary ASC;
DESC employees
SELECT *
FROM employees;
SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) d,salary,
salary*12*(IF(commission_pct IS NOT NULL,commission_pct,1)) e
FROM employees;
#IFNULL(value1, value2) 如果value1不为NULL，返回value1，否则返回value2
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;
#CASE WHEN....THEN....WHEN...THEN....ELSE...END
SELECT last_name,salary,CASE WHEN salary>=15000 THEN'成功人士'
WHEN salary>=10000 THEN'人生赢家'
WHEN salary>=5000 THEN'躺平者'
ELSE '失败者' END "status"
FROM employees;
#CASE....WHEN...THEN...WHEN...THEN....WHEN...THEN...ELSE...END
#练习：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其
#工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。
#if.... else if ..... else if
SELECT e.employee_id,e.last_name,e.department_id,e.salary,CASE WHEN e.department_id=10 THEN salary*1.1
WHEN e.department_id=20 THEN salary*1.2
WHEN e.department_id=30 THEN salary*1.3
END "salaryas"
FROM employees e JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id=10 OR e.department_id=20 OR e.department_id= 30
ORDER BY salary ASC;
#类似于 switch case
SELECT e.employee_id,e.last_name,e.department_id,e.salary,CASE e.department_id WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
WHEN 30 THEN salary*1.3
END "salarys"
FROM employees e
WHERE department_id IN(10,20,30);
#2.练习
SELECT e.employee_id,e.last_name,e.department_id,e.salary,CASE e.department_id WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
WHEN 30 THEN salary*1.3
ELSE salary*1.4 END "salarys"
FROM employees e;
#加密与解密函数
/*PASSWORD(str) 返回字符串str的加密版本，41位长的字符串。加密结果 不可逆 ，常用于用户的密码加密 (mysql 8.0 不支持此函数使用)
5.7的测试
SELECT PASSWORD('这是一个测试')
FROM DUAL;
*/
SELECT MD5('测试'),SHA('测试'),MD5(MD5('测试'))
FROM DUAL;
#不可逆加密
/*
ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value
DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value
mysql 8.0 已弃用
5.7使用例子
SELECT ENCODE('测试','114514')
FROM DUAL;
SELECT DECODE(ENCODE('测试','114514'),'114514')
FROM DUAL;
*/
#MySQL信息函数
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER(),CHARSET('中文'),
COLLATION('中文')
FROM DUAL;
#其他函数
#后一位 四舍五入
#如果n的值小于或者等于0，则只保留小数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,1)
FROM DUAL;
#16的十进制转换为二进制
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;
#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100
SELECT INET_ATON('127.0.0.1'),INET_NTOA(2130706433)
FROM DUAL;
/*BENCHMARK(n,expr)
将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费
的时间
*/
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;
#将value所使用的字符编码修改为char_code CONVERT: 实现字符集的转换
SELECT CHARSET('数据库'),CHARSET(CONVERT('数据库'USING 'gbk'))
FROM DUAL;
#课后练习
# 1.显示系统时间(注：日期+时间)
SELECT NOW()
FROM DUAL;
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT e.employee_id,e.last_name,e.salary,e.salary+(salary*0.2) "new salary"
FROM employees e;
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name ASC;
SELECT last_name,first_name,(LENGTH(last_name)+LENGTH(first_name)) 'NameLength'
FROM employees e
ORDER BY last_name ASC;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id,'--',last_name,'--',salary)AS OUT_PUT
FROM employees e;
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT CURDATE() AS nowtime,hire_date,DATEDIFF(CURDATE(),hire_date)AS days,SUBSTR((CURDATE()-hire_date),1,2)AS `year`
FROM employees
ORDER BY `year` DESC;
# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空
SELECT e.last_name,e.hire_date, e.department_id
FROM employees e
WHERE hire_date>='1997-1-1'
AND e.`department_id`IN (80,90,110)
AND e.`commission_pct` IS NOT NULL;
SELECT DATE_FORMAT(hire_date, '%y') a
FROM employees;
SELECT last_name, hire_date, department_id
FROM employees
#WHERE hire_date >= '1997-01-01'
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date,'%Y') >= '1997'
AND department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date,DATEDIFF(CURDATE(),hire_date) AS days
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date)>10000;
# 8.做一个查询，产生下面的结果
SELECT CONCAT(last_name,'earns',TRUNCATE(salary,0),' monthly but wants ',TRUNCATE(salary*3,0))AS 'Dream salary'
FROM employees;
# 9.使用case-when，按照下面的条件：
SELECT last_name,job_id,CASE job_id WHEN'AD_PRES' THEN'A'
WHEN'ST_MAN' THEN'B'
WHEN'IT_PROG' THEN'C'
WHEN'SA_REP' THEN'D'
WHEN'ST_CLERK'THEN'E'
ELSE'F' END "gread"
FROM employees;
SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F'
END "grade"
FROM employees;
|
