mysql基礎-mysql中的DQL-常見函數

點擊藍色「潭時錄」關注我丫

每天和小潭一起快樂的學習~

    你好，我是在學mysql的小潭。鑑於篇幅原因，小潭將mysql查詢部分的知識點分成同期不同篇的方式進行學習和介紹。該篇中我們將了解mysql中的常見函數。

sql語句選中使用F9可快速提交查詢，使用F12可以格式化sql語句。

SELECT LENGTH('test'); SELECT LENGTH('牛年大吉niuniuniu'); 
SELECT CONCAT(last_name,'_',first_name) FROM employees;
SELECT UPPER('aBc');SELECT LOWER('aBc');SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;
SELECT SUBSTR('牛年大吉',3) output;SELECT SUBSTR('牛年大吉',1,2) output;
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_putFROM employees;
SELECT INSTR('牛年大吉','大吉') AS out_put;
SELECT LENGTH(TRIM('    牛年大吉     ')) AS out_put;SELECT TRIM('a' FROM 'aaaaa新年aaaa好aaaa') AS out_put;
SELECT LPAD('新年大吉', 10,'*') AS out_put; 
SELECT RPAD('新年大吉', 10,'*') AS out_put;
SELECT REPLACE('新年好','好','大吉') AS out_put;

SELECT ROUND(1.65); SELECT ROUND(-1.45); SELECT ROUND(1.567,2); 
SELECT CEIL(1.002);  SELECT CEIL(1.00);  SELECT CEIL(-1.02); 
SELECT FLOOR(-9.99);  
SELECT TRUNCATE(1.65,1); 
SELECT MOD(10,3); SELECT 10%3; SELECT MOD(-10,-3); 

SELECT NOW();SELECT CURDATE();SELECT CURTIME();SELECT YEAR(NOW()) 年;SELECT YEAR('2020-2-10') 年;SELECT YEAR(hiredate) 年 FROM employees;SELECT MONTH(NOW()) 月;SELECT MONTHNAME(NOW()) 月;
SELECT STR_TO_DATE('2020-2-10','%Y-%c-%d') AS out_put;SELECT * FROM employees WHERE hiredate = '1992-4-3';SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') AS out_put;
SELECT last_name, DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入職日期FROM employees WHERE commission_pct IS NOT NULL;

SELECT VERSION();SELECT DATABASE();SELECT USER();

SELECT IF(10>5,'大','小') AS out_put;SELECT last_name,commission_pct, IF(commission_pct IS NULL,'沒有獎金','有獎金') 備註FROM employees;
SELECT salary 原始工資,department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS 新工資FROM employees;
SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工資級別FROM employees;
小測試：
SELECT NOW();SELECT employee_id,last_name,salary,salary*1.2 AS "new salary"FROM employees;SELECT *,LENGTH(last_name) 長度FROM employeesORDER BY SUBSTR(last_name,1,1) ASC;SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary"FROM employeesWHERE salary=24000;
SELECT last_name,job_id AS job,CASE job_idWHEN 'AD_PRES' THEN 'A'WHEN 'ST_MAN' THEN 'B'WHEN 'IT_PROG' THEN 'C'WHEN 'SA_PRE' THEN 'D'WHEN 'ST_CLERK' THEN 'E'END AS GradeFROM employeesWHERE job_id = 'AD_PRES';
mysql中的格式符：
格式符
功能
%Y
四位的年份%y2位的年份%m月份(01,02,03....12)%c
月份(1,2,3...11,12)
%d日(01,02...)%H小時(24小時制)%h小時(12小時制)%i分鐘(00,01,02....59)%s秒(00,01,02....59)