【Mysql数据库基础02】单行函数、排序

单行函数、排序 1 单行函数1.1 常用函数1.1.1 length 字符串的长度1.1.2 ifnull 判断表达式是否为空 1.2 字符函数1.2.1 substr 提取自串1.2.2 转换大小写1.2.3 instr 返回起始索引1.2.4 trim 去除两端指定字符1.2.5 lpad 左填充指定长度 1.3 数学函数1.3.1 round 四舍五入1.3.2 ceil 向上取整1.3.3 floor 向下取整1.3.4 truncate 截断1.3.5 mod 取余 1.4 日期函数1.4.1 now 返回当前日期+时间1.4.2 curdate 返回当前日期1.4.3 curtime 返回当前时间1.4.4 year month day获取时间的指定部分1.4.5 str_to_date 通过字符转换为日期1.4.6 date_format 将日期转换为字符 1.5 流程控制函数1.5.1 if1.5.2 case 1.6 其他函数1.7 练习 2 排序查询2.1 练习

1 单行函数 1.1 常用函数 1.1.1 length 字符串的长度

LENGTH() 函数用于返回一个字符串的字符数（即字符串的长度） 例如：姓名字节长度

length(last_name) 1.1.2 ifnull 判断表达式是否为空

IFNULL() 函数用于判断一个表达式是否为 NULL，如果为 NULL，则返回指定的替代值；如果不为 NULL，则返回原始值。 例如：如果奖金率的值为 NULL，那么返回 0；否则返回奖金率的实际值。

ifnull(commission_pct,0) 1.2 字符函数 1.2.1 substr 提取自串

SUBSTR() 函数用于提取字符串的子串（子序列）。 例如：提取从第7位开始的5个字符，结果为 ‘World’

substr('Hello World', 7, 5); 1.2.2 转换大小写 LOWER() 函数：将字符串转换为小写。 SELECT LOWER('Hello World'); -- 输出 'hello world' UPPER() 函数：将字符串转换为大写。 SELECT UPPER('Hello World'); -- 输出 'HELLO WORLD' 1.2.3 instr 返回起始索引

INSTR() 函数用于返回一个字符串在另一个字符串中第一次出现的位置。 例如：'World’在’Hello World’中从第7个位置开始，返回 7

SELECT INSTR('Hello World', 'World'); 1.2.4 trim 去除两端指定字符

TRIM() 函数用于去除字符串开头、结尾或两端指定字符（或空格）。 例如：

SELECT LTRIM(' Hello '); -- 输出 'Hello'，去除了开头和结尾的空格 SELECT LTRIM('x' FROM 'xxxHelloxxxxxx'); -- 输出 'Hello'，去除了开头和结尾的 'x' 1.2.5 lpad 左填充指定长度

LPAD() 函数用于向字符串的左侧填充指定的字符，使字符串达到指定的长度。 例如：

SELECT LPAD('hello', 10, '*'); -- 输出 '*****hello' 1.3 数学函数 1.3.1 round 四舍五入

ROUND() 函数用于对一个数进行四舍五入，可以指定保留小数点后的位数。 例如：

SELECT ROUND(3.14159); -- 输出结果为 3，将小数部分四舍五入 SELECT ROUND(3.14159, 2); -- 输出结果为 3.14，将小数部分四舍五入保留两位 1.3.2 ceil 向上取整

CEIL(x)：向上取整，返回大于或等于x的最小整数。

1.3.3 floor 向下取整

FLOOR(x)：向下取整，返回小于或等于x的最大整数。

1.3.4 truncate 截断

TRUNCATE(x, d)：截断，将x保留到指定小数位数d。

1.3.5 mod 取余

MOD(x, y)：取余，返回x除以y的余数。

---

SELECT CEIL(3.14); -- 输出 4 SELECT FLOOR(3.14); -- 输出 3 SELECT TRUNCATE(3.14159, 2); -- 输出 3.14 SELECT MOD(10, 3); -- 输出 1 1.4 日期函数 1.4.1 now 返回当前日期+时间

NOW()：返回当前日期和时间。

select now(); 1.4.2 curdate 返回当前日期

CURDATE()：返回当前日期。

1.4.3 curtime 返回当前时间

CURTIME()：返回当前时间。

1.4.4 year month day获取时间的指定部分

YEAR(date)：返回日期date的年份部分。 MONTH(date)：返回日期date的月份部分。 DAY(date)：返回日期date的日份部分。

1.4.5 str_to_date 通过字符转换为日期

例如：

select str_to_date('2001-2-28','%Y-%c-%d') as output;

1.4.6 date_format 将日期转换为字符 1.5 流程控制函数 1.5.1 if

例如：

select if(10>5,'big','small'); 1.5.2 case

基本语法：

CASE case_expression WHEN when_expression_1 THEN result_1 WHEN when_expression_2 THEN result_2 ... ELSE else_result END

案例1: 查询员工的工资，要求: 部门号=30，显示的工资为1.1倍 部门号=40，显示的工资为1.2倍 部门号=50，显示的工资为1.3倍 其他部门，显示的工资为原工资

select salary as 原始工资,department_id, 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 新工资 from employees;

案例2： 查询员工的工资的情况 如果工资>20000，显示A级别 如果工资>15000，显示B级别 如果工资>10000，显示c级别 否则，显示D级别

select salary, case when salary> 20000 then 'A' when salary>15000 then 'B' when salary>10000 then 'C' else 'D' end as 工资级别 from employees; 1.6 其他函数 version( ) 查看版本号database( ) 查看数据库user( ) 查看用户 1.7 练习

一、

select now();

二、

select employee_id,last_name,salary,salary*(1+0.2) as "new salary" from employees;

三、

select last_name,length(last_name) as 姓名的长度 from employees order by substr(last_name,1,1) asc;

四、

select concat(last_name," earns ",salary," monthly but wants ",salary*3) as "Dream Salary" from employees where salary = 24000;

五、

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' end as Grade from employees;

2 排序查询

在MySQL中，ORDER BY 子句用于对查询结果进行排序。可以按照一个或多个列对结果进行排序，以便以特定的顺序呈现数据。 升序asc 降序desc

2.1 练习

一、查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序

select last_name,department_id,salary*12*(1+ifnull(commission_pct,0) as 年薪 from employees order by 年薪 desc,last_name asc;

二、选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序

select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc;

三、查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

select * from employees where email like '%e%' order by length(email) desc,department_id asc;