[大数据]第07章_单行函数

-- 单行函数
SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),
CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

-- 随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
-- 四舍五入
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

# 3： 字符串函数
SELECT ASCII('abc'),LENGTH('hello'),CHAR_LENGTH('我们')
LENGTH('hello'),LENGTH('我们')
FROM DUAL;

# xxx worked for yyy
SELECT CONCAT (emp.last_name,'worked_for',mgr.last_name)
FROM employees emp JOIN employees mgr
WHERE emp.`manager_id`=mgr.`manager_id`;

SELECT NOW()
FROM DUAL;

# 流程控制函数
SELECT last_name,salary ,IF(salary>=6000,'高工资','低工资')"details"
FROM employees;
-- 4-1 IF
SELECT last_name,commission_pct,IF(
commission_pct IS NOT NULL,commission_pct,0)"details",
FROM employees;

-- 4.2 ifnull
SELECT last_name,commission_pct,IFNULL(commission_pct,0)
FROM employees;


-- 4.3 case when  .. case when(类似于 if else if else )
SELECT last_name,salary,CASE WHEN salary>=15000 THEN '白骨精'
			WHEN salary>=10000 THEN '潜力股'
			WHEN salary>=8000 THEN 'xiao'
			ELSE '草根' END "details",department_id	
FROM employees;

-- 4.4CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN
/*练习：查询部门号为 10,20, 30 的员工信息,
 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 
 则打印其工资的 1.2 倍, 
 30 号部门打印其工资的 1.3 倍数。
其他部门1.4被*/
SELECT employee_id,last_name,department_id,salary,CASE 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 "detaols"
	 FROM employees			
WHERE department_id IN (10,20,30);

# 5.加密和解密
SELECT MD5('mysql'),SHA('PANG1234')
FROM DUAL;

SELECT BENCHMARK(9999999,MD5('MYSQL'))
FROM DUAL;
# 单行函数的课后练习
# 1.显示系统时间(注：日期+时间)
SELECT NOW()
FROM DUAL;
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) "name_size"
FROM employees
ORDER BY name_size ASC;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT (employee_id,',',last_name,',',salary) "out_put"
FROM employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(),
hire_date) worked_days
FROM employees
ORDER BY worked_years DESC
# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id
#为80 或 90 或110, commission_pct不为空
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天的员工姓名、入职时间


# 8.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>