Mysql查询排序
新的改变
我们对Markdown编辑器进行了一些功能拓展与语法支持,除了标准的Markdown编辑器功能,我们增加了如下几点新功能,帮助你用它写博客:
- 案例1 ,按字节长度排序
`SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees ORDER BY 字节长度 DESC;
-
`SELECT * FROM employees ORDER BY salary ASC ,employee_id DESC;`;
SELECT first_name,
department_id,
salary*12*(1+IFNULL(
commission_pct,0)) AS 年薪 FROM
employeesORDER BY 年薪 ASC
;SELECT first_name,
department_id,
salary*12*(1+IFNULL(
commission_pct,0)) AS 年薪 ,salary FROM
employeesWHERE 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(