石油网页设计与网站建设,网站推广优化外包公司哪家好,织梦网站怎么关闭手机模板,教做甜品的网站2023.9.3 单行函数的SQL语句学习笔记如下#xff1a;
#常见单行函数介绍#xff08;部分省略#xff09;
#字符函数
#将姓变大写#xff0c;名变小写#xff0c;然后拼接。
SELECT CONCAT(UPPER(last_name), ,LOWER(first_name)) AS 姓名 FROM employees;
# 姓名中首字符…2023.9.3 单行函数的SQL语句学习笔记如下
#常见单行函数介绍部分省略
#字符函数
#将姓变大写名变小写然后拼接。
SELECT CONCAT(UPPER(last_name), ,LOWER(first_name)) AS 姓名 FROM employees;
# 姓名中首字符大写其他字符小写然后用_拼接显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),_,LOWER(SUBSTR(last_name,2))) AS 姓名 FROM employees;
#查询员工的工资要求部门号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;
##案例:查询员工的工资的情况 如果工资20000,显示A级别 如果工资15000,显示B级别 如果工资10000显示c级别 否则显示D级别
SELECT salary,
CASE
WHEN salary20000 THEN A
WHEN salary15000 THEN B
WHEN salary10000 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) AS 长度,SUBSTR(last_name,1,1) AS 首字母,last_name FROM employees ORDER BY 首字母;
SELECT CONCAT(last_name, earns ,salary, monthly but wants ,salary*3) AS dream salary FROM employees WHERE salary24000;
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
WHERE job_idAD_PRES; 分组函数的SQL学习笔记
#常见分组函数介绍
#功能用作统计使用又称为聚合函数或统计函数或组函数
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) AS 薪资和,ROUND(AVG(salary),2) AS 平均薪资 FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;#分组函数会忽略null值。
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; #搭配distinct使用
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;#同上
分组函数测试题
1、查询公司员工工资的最大值最小值平均值总和
2、查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)查询都门编号为90的员工个数
3、查询部门编号为90的员工个数
# 分组函数测试题
SELECT MAX(salary),MIN(salary),ROUND(AVG(salary),2),SUM(salary) FROM employees;
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
SELECT COUNT(*) AS 个数 FROM employees WHERE department_id90;
