Mysql使用技巧

新的改变

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

1. 案例1 ，按字节长度排序

`SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees ORDER BY 字节长度 DESC；

2. `SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;`；
   

3. SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROMemployeesORDER BY 年薪 ASC；
4. SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 ,salary FROMemployeesWHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY 年薪 ASC,first_name DESC；

常见函数

LENGTH函数

length(‘apple’) 返回5
length（‘张三丰’)返回9 gbk一个汉字两个字符，utf一个汉字三个字符

concat 函数

拼接字符串

select concat (last_name,'_',first_name)

upper

select upper('jon');
SELECT CONCAT(UPPER(first_name),'_',LOWER(last_name)) AS 姓名 FROM employees

substr/substring 截取字符串

一共有四个格式
SELECT SUBSTR(‘李莫愁喜欢和论自由’,6) AS out_Put
结果是"和论自由"
Mysql序号从1开始
select substr(‘李莫愁喜欢和论自由’,1,3)as out_put 输出是李莫愁
SELECT SUBSTR(‘asljj’ FROM 2) 从第二个字符往后截取
SELECT SUBSTR('asdasd’FROM 2 FOR 3) AS hello 从第二字符后截取3位，这里一个汉字也算一个字符

instr 返回起始索引

SELECT INSTR('杨不悔爱仕达圣诞节圣诞节','圣诞节')AS output 

返回起始索引7，如果没找到怎返回0

trim

select TRIM('   张三丰  ') 

返回张三丰

SELECT TRIM('a' FROM 'aaaa张三aa丰aaa')AS hello

结果返回张三aa丰

lpad，rpad

SELECT LPAD('sd张三',10,'*') 

左填充*让总字符串长度为10，这里中文算1一个字符

SELECT RPAD('sd张三',10,'*') 

右填充*让总字符串长度为10，这里中文算1一个字符

relpace 替换字符串

SELECT REPLACE('这三个张无忌he张无忌','张无忌','利好')

结果是 “这三个利好he利好”

数学函数

round 四舍五入

select round（4.5）；返回5
select 柔嫩的（1.567，2） ；返回1.57

ceil 向上取整，大于等于参数的最小参数

select ceil(1.01);返回2

floor 向下取整，小于等于参数的最大参数

select floor（1.01）；返回1

TRUNCATE 截断

SELECT TRUNCATE(1.8345,2)

返回1.83

mod 取余

select mod（10，3）；
返回1，等于10%3

日期函数

now

select now （）；
返回当前系统日期+时间

curdata 返回日期，没有时间 curtime返回当前时间，没有日期

select curdata（）；
select curtime（）；
SELECT YEAR(NOW()) AS 年;
SELECT MONTH(NOW()) AS 月;
SELECT DAY(NOW()) AS 天;
SELECT HOUR(NOW()) AS 小时;
SELECT MINUTE(NOW()) AS 分钟;
SELECT SECOND(NOW()) AS 秒;
SELECT YEAR(`hiredate`) AS 年 FROM employees;

上面是返回9
SELECT MONTHNAME(NOW()) AS 月;
返回时September

date_format日期转字符串

SELECT DATE_FORMAT(NOW(),’%Y年%c月%d日’)AS oupit
返回2020年7月16日

SELECT DATE_FORMAT(hiredate,'%m月/%d日 %Y年') AS 入职日期,`commission_pct` FROM employees WHERE`commission_pct` IS NOT NULL 

其他函数

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

流程控制函数

if 语句选择判读

SELECT IF(10>5,‘first’,‘second’) 返回first

SELECT IF(ISNULL(`commission_pct`),'无','有') AS 奖金 ,`commission_pct` AS 奖金比例 
FROM employees

case 语句

第一种

SELECT salary AS 原工资 ,
CASE `department_id` 
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END
AS 新工资,`department_id`FROM `employees`

根据不同部门算出不同的工资

第二种
例一

SELECT salary AS 原工资 ,
CASE  
WHEN `department_id`=30 THEN salary*1.1
WHEN `department_id`=40 THEN salary*1.2
WHEN `department_id`=50 THEN salary*1.3
ELSE salary
END
AS 新工资,`department_id`FROM `employees`

例二

SELECT salary AS 工资 ,
CASE  
WHEN salary>20000 THEN 'A'
WHEN SALARY>15000 THEN 'B'
WHEN SALARY>10000 THEN 'C'
WHEN SALARY>5000 THEN 'D'
ELSE 'E'
END
AS 新工资,`department_id`FROM `employees`

函数汇总：
字符型：LENGTH(),UPPER(),LOWER(),CONCAT(),SUBSTR(),INSTR(),
TRIM(),LPAD().RPAD(),REPLACE