写点什么

Mysql 中常用函数 分组, 连接查询

  • 2024-10-18
    福建
  • 本文字数:3051 字

    阅读完需:约 10 分钟

函数


在 Mysql 中函数是一组预定义的指令,用于执行特定的操作并返回结果,可类比 Java 中的方法.在 SQL 中函数根据其作用范围和返回结果方法分为两大类:单行函数,分组函数


单行函数


单行函数的特点为对一行数据进行操作,并只返回一种结果.单行函数通常用于处理单个记录数据


  • 单行函数又可分为:字符函数,数学函数,其他函数,流程控制函数


字符函数


  • CHAR_LENGTH(S),LENGTH(S):返回字符串的长度


eg:查询员工姓名,姓名字数。


SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) FROM emplyees;
复制代码


  • CONCAT(S1,S2,…Sn):将两个以上的字符串连接


eg:将字符串'aaa','bbb','ccc'进行拼接。


SELECT CONCAT('aaa','bbb','ccc');
复制代码


  • UPPER(),LOWER():对字符进行大小写转化


eg::查询员工邮箱,并转为大写显示


SELECT UPPER(email) FROM emplyees;
复制代码


  • substr,substring(S, start, length):提取字符串 S 从 start 位置开始,长度为 length 的字字符串


eg:提取 hello world 中的 hello


SELECT substr('hello world',1,5);`
复制代码


  • replace(S, old, new): 在字符串 S 中将所有的 old 替换为 new


eg:查询员工电话号码,要求去除中间的横线 ’-’


SELECT REPLACE(phone_number, '-', '') FROM emplyees;
复制代码


数学函数


  • ROUND(X):对浮点数 X 进行四舍五入


eg:查询员工工资,和其四舍五入的整数值


SELECT salary,ROUND(salary) FROM employees;
复制代码


  • CEIL(X):对浮点数 X 向上取整,即返回≥X 的最小整数


eg:查询员工工资,并且向上取整


SELECT salary,CEIL(salary) FROM employees;
复制代码


  • FLOOR(X):对浮点数 X 向下取整,即返回≤X 的最大整数


eg:查询员工工资,并且向下取整


SELECT salary,FLOOR(salary) FROM employees;
复制代码


  • TRUNCATE(X,length):对浮点数的小数部分进行截取→常用于进行保留小数操作


SELECT TRUNCATE(1.9999,2);->1.99
复制代码


  • MOD(X,Y):对两个数进行区域操作即 X%Y


日期函数


  • NOW(),SYSDATE():返回当前系统日期+时间

  • CURDATE():返回当前系统日期,不包括时间

  • CURTIME():返回当前系统时间,不包括日期

  • DATE_FORMAT(date,format): 用于格式化日期,date 是要格式化的数据,format 是格式化的模式,格式化的通配符号如下表:



eg:查询员工姓名、入职时间,入职时间按照 xxxx 年 xx 月 xx 日输出


SELECT DATE_FORMAT(hiredate,'%Y年%m月%d日') FROM employees;
复制代码


流程控制函数


流程控制函数在 SQL 中根据条件选择性地返回不同的结果,其允许在查询过程中实现条件逻辑


  • IF(expr,true_val,false_val): 若表达式expr为真,则返回结果true_val,否则返回false_val的结果


eg: 如果查询的年纪大于 18 则返回 adult,否则返回 minor


SELECT age,IF(age>=18,'adult','minor');
复制代码


  • CASE语法结构:类似于 switch…case 结构,用于实现多支路条件选择


SELECT exper1,exper2...,CASE exper1		 WHEN value1 THEN result1		 WHEN value2 THEN result2		 WHEN value3 THEN result3		 ...		 ELSE resultENDFROM table_name;
复制代码


eg:根据查询的部门号返回部门名称


SELECT department_idCASE department_id		 WHEN 1 THEN '经理办公室'		 WHEN 2 THEN '财务部'		 WHEN 3 THEN '后勤部'		 ELSE 'unkown'END AS department_nameFROM departments;
复制代码


  • 搜索 CASE:语法结构与朴素 CASE 类似,但搜索 CASE 可根据多种不同的表达式条件返回不同值


SELECT exper1,exper2...,CASE 		 WHEN condition 1 THEN result1		 WHEN condition 2 THEN result2		 ...		 ELSE resultEND FROM table_name;
复制代码


eg:查询员工姓名以及工资,工资按照一定规则发放,入职时间在 2015-01-01 之前的员工工资*2,入职时间在 2018-01-01 之前的员工工资*1.5,其他不变


SELECT employee_name,hiredate,salary 原工资,CASE    WHEN hiredate<'2015-01-01' THEN salary*2		WHEN hiredate<'2018-01-01' THEN salary*1.3		ELSE salaryEND AS 新工资FROM employees;
复制代码


分组函数


分组函数也称为聚合函数,用于对一组值进行操作,并返回单个结构


  • COUNT(*):计算指定列中非 NULL 值的数量,SUM(column):计算指定列之和,AVG(column):计算指定列平均数,MAX(colum):取出指定列最大值,MIN(colum):取出指定列最小值


eg:查询所有员工工资总和、平均值、最大值、最小值、员工个数;


SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(*) FROM employees;
复制代码


分组查询


分组查询可根据某个或某些列对数据进行分组


按单个字段分组


  • 通过GROUP BY关键字:按指定列进行分组


SELECT 列表FROM 表[WHERE 筛选条件]GROUP BY 分组[ORDER BY 排序]
复制代码


eg:查询每个部门的最高工资


SELECT MAX(salary) FROM employees GROUP BY department_id;
复制代码


在分组前进行条件筛选


在 GROUP BY 语句之间使用 WHERE 语句对查询结果降序筛选


eg:查询每个部门入职时间在 2010-01-01 之后,并且工资最高的员工信息


SELECT *FROM employeesWHERE hiredate >'2010-01-01'GROUP BY department_id;
复制代码


在分组之后进行条件筛选


  • 通过HAVING语句可以在GRUOP BY语句之后进行条件筛选


eg:查询员工人数大于 120 的部门


SELECT *FROM employeesGROUP BY department_idHAVING COUNT(*)>120;
复制代码


按多字段分组


  • 在 GRUOP BY 语句后可接多个字段实现多字段分组


eg:查询每个部门,男女员工的平均工资


SELECT department_id,sex,AVG(salary) AS 平均工资FROM employeesGROUP BY department_id,sex;
复制代码


连接查询


连接查询是 SQL 中十分重要的知识点,就有”连接不会,通宵也白搭”,连接查询也称为多表查询,用于将两个以上的表的数据基于某些相关条件组合在一起,通过连接查询,可以从表中提取数据,生成一个新的结果集


  • 笛卡尔积现象:假设有两个表,表 1 有 n 行数据,表 2 有 m 行数据,在使用连接查询时会产生 n*m 条数据,因此在条件查询时需要假设连接的条件


内连接(INNER JOIN)


内连接用于返回两个表中所有满足连接条件的所有行数据,内连接可分为:等值连接,非等值连接,自连接


等值连接


等值连接是一种常见的连接方式,其基于两表中某一列的相等条件进行连接


其语法结构如下:


SELECT colum1,colum2,....,FROM table1 INNER JOIN table2ON table1.colum = table.colum;
复制代码


eg:查询员工姓名以及所在的部门名称


SELECT employee_name AS 员工名,department_name AS 部门名FROM employees e	INNER JOIN departments d ON e.department_id=d.department_id;
复制代码


非等值连接


非等值连接基于两表中某一列的不等条件进行连接.如大于,小于,不等于等等


语法结构:


SELECT colum1,colum2,....,FROM table1 INNER JOINON table1.colum <operator> table2.colum;
复制代码


其中operator可以是>,<,≥,≤,≠,BETWEEN…AND等等;


eg:查询员工工资及工资等级


SELECT e.salary,j.grade_levelFROM employees eINNER JOIN job_grades jON e.salary BETWEEN j.lowest_sal AND j.higest_sal;
复制代码


自连接


自连接是指同一个表的连接.这种连接通常用于处理表中有层次结构或函数递归关系的数据


eg:查询员工姓名以及对应的直系领导


SELECT t1.employee_name AS 员工,t2.employee_name AS 领导FROM employees t1INNER JOIN employees t2ON t1.manager_id=t2.employee_id;
复制代码


外连接


外连接用于返回主表中满足连接条件的行,同时保留另一个表中没有匹配的行


左/右外连接


  • 左(右)连接顾名思义即根据表位置区分主表,即在左连接即左表,右连接即右侧


语法结构:


SELECT colum1,colum2...,FROM table1 [LEFT|RIGHT]JOIN ON [连接条件];
复制代码


eg:查询员工姓名以及所在的部门名称,没有部门信息的员工也要查询出来


SELECT employee_name AS 员工姓名,department_name AS 部门名称FROM employees e LEFTJOIN departments dON e.department_id=d.department_id;
复制代码


文章转载自:ihav2carryon

原文链接:https://www.cnblogs.com/ihave2carryon/p/18472524

体验地址:http://www.jnpfsoft.com/?from=infoq

用户头像

还未添加个人签名 2023-06-19 加入

还未添加个人简介

评论

发布
暂无评论
Mysql中常用函数 分组,连接查询_MySQL_不在线第一只蜗牛_InfoQ写作社区