第二章 DQL语言的学习二（常见函数、单行函数）

第二章 DQL语言的学习二（常见函数、单行函数）

准备工作

第一、在做查询时，需要打开指定的库，

第二、着重号，表示这是一个字段，

第三、执行的时候选中打算执行的命令。

2.4 常见函数

2.4.1 函数简介

• 概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
• 优点：
  • 隐藏了实现细节
  • 提高代码的重用性
• 调用：select 函数名(实参列表) [from 表];
• 特点：
  • 函数名
  • 函数功能
• 分类
  • 单行函数：例如concat、length、ifnull
  • 分组函数：做统计使用的，又称为统计函数、聚合函数、组函数

2.4.2 单行函数详细介绍

一、字符函数

#进阶4:常见函数
#一、字符函数
#1.length：获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('MySQL数据库');

SHOW VARIABLES LIKE '%char%';

#2.concat：拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper、lower：转换大小写
SELECT LOWER('MySQL');
SELECT UPPER('mysql');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;

#4.substr、substring：截取字符
#sql语言中索引从1开始
#使用两个参数时，截取从指定索引处后面所有字符
SELECT SUBSTR('数据库MySQL',6) out_put;

#使用三个参数时，截取从指定索引处指定字符长度的字符
SELECT SUBSTR('数据库MySQL',1,3) out_put;

#案例:姓名中首字符大写，其他字符小写，然后用下划线拼接并显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put 
FROM employees;

#5.instr:用于返回子串在父串的起始索引,如果找不到，返回0
SELECT INSTR('开始学习数据库','数据库') AS out_put;

#6.trim:删除字符串头或尾的指定字符串，通常用来移除字符串首尾两端的空格
SELECT LENGTH(TRIM('    数据库    ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaa啦啦啦aaaaaaaaaaa') AS out_put;

#7.lpad:用指定的字符实现左填充指定长度，原长度超过指定长度从右边截断
SELECT LPAD('数据库',10,'*') AS result;

#8.rpad:用指定的字符实现右填充指定长度，原长度超过指定长度从右边截断
SELECT RPAD('数据库',10,'*') AS result;

#9.replace 替换
SELECT REPLACE('学习数据库','数据库','mysql') AS out_put;

二、数学函数

#1.round：四舍五入
SELECT ROUND(1.65);
SELECT ROUND(1.567,2);

#2.ceil:向上取整，返回>=该参数的最小整数
SELECT CEIL(1.4002);

#3.floor:向下取整，返回<=该参数的最大整数
SELECT FLOOR(9.99);

#4.truncate：截断,从小数点后指定位截断
SELECT TRUNCATE(1.655559,1);

#5.mod：取模/取余，与被除数有关
/*内层逻辑
MOD(a,b): a-a/b*b
MOD(-10,-3): -10-(-10)/(-3)*(-3)=-1
*/
SELECT MOD(-10,-3);

三、日期函数

#1.now:返回当前系统日期+时间
SELECT NOW();

#2.curdate:返回当前系统日期，不包含时间
SELECT CURDATE();

#3.curtime:返回当前时间，不包含日期
SELECT CURTIME();

#4.可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('2001-07-01') 年;
SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;	#英文显示

#1.str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('10-5-2024','%m-%d-%Y');

#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

#2.date_format:将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期(X月/X日 X年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期 
FROM employees
WHERE commission_pct IS NOT NULL;

四、其他函数

#1.VERSION:查看版本号
SELECT VERSION();

#2.DATABASE:查看当前库
SELECT DATABASE()

#3.USER：查看当前用户
SELECT USER();

五、流程控制函数

1. if函数

#1.if函数:实现if-else的效果
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'无奖金','有奖金') FROM employees;

2. case函数

• case函数的使用一：switch case 的效果 (判断等于)
  Java中：

  switch(变量或表达式){
  	case 常量1: 语句1: break;
  	……
  	default: 语句n；break;
  }
  

  mysql中：

  case 要判断的字段或表达式
  when 常量1 then 要显示的值1或 语句1;
  when 常量2 then 要显示的值2或 语句2;
  ...
  else 要显示的值n或语句n；
  end
  

  搭配查询来用，CASE要放在SELECT后面，作为一个表达式，THEN后面放要显示的值
  后面我们会用存储过程或函数，把case当成语句来用，then后面放语句

#case函数的使用一
/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,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;

• case函数的使用二：类似于多重if (判断的是区间，大于小于)
  Java中：

  if(条件1){
      语句1;
  }else if(条件2){
      语句2;
  }
  ...
  else{
      语句n;
  }
  

  mysql中：

  case
  when 条件1 then 要显示的值1或 语句1;
  when 条件2 then 要显示的值2或 语句2;
  ...
  else 要显示的值n或 语句n;
  end
  

#case函数的使用二
/*案例：查询员工的工资情况
如果工资>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.显示系统时间
SELECT NOW()

#2.查询员工号、姓名、工资，以及工资提高20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 AS "new salary" FROM employees;

#3.将员工的名字按首字母排序，并写出姓名的长度
SELECT last_name,LENGTH(last_name) 字节长度,SUBSTR(last_name,1,1) 首字符
FROM employees
ORDER BY 首字符;


#4.做一个查询，产生下面的结果
/*
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
*/

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary" 
FROM employees
WHERE salary = 24000; 


#5.使用case-when，按照下面的条件：
job 		grade
AD_PRES		A
ST_MAN		B
IT_PROG		C
SA_REP		D
ST_CLERK	E
产生下面的结果
last_name	job_id	Grade

SELECT last_name,job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'IT_PROG' THEN 'C'
WHEN 'IT_PROG' THEN 'C'
END AS Grade
FROM employees
WHERE job_id='AD_PRES';