Mysql中常用函数之分组,连接查询功能实现

你奈人生何

函数

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

复制代码

单行函数

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

复制代码

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

字符函数

• 1. CHAR_LENGTH(S),LENGTH(S):

  复制代码
  返回字符串的长度
  

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

1. SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) FROM emplyees;

复制代码

• 1. CONCAT(S1,S2,…Sn):

  复制代码
  将两个以上的字符串连接
  

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

1. SELECT CONCAT('aaa','bbb','ccc');

复制代码

• 1. UPPER(),LOWER():

  复制代码
  对字符进行大小写转化
  

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

1. SELECT UPPER(email) FROM emplyees;

复制代码

• 1. substr,substring(S, start, length):

  复制代码
  提取字符串S从start位置开始,长度为length的字字符串
  

eg:提取hello world中的hello

1. SELECT substr('hello world',1,5);`

复制代码

• 1. replace(S, old, new):

  复制代码
  在字符串S中将所有的old替换为new
  

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

1. SELECT REPLACE(phone_number, '-', '') FROM emplyees;

复制代码

数学函数

1. ROUND(X):

复制代码

对浮点数X进行四舍五入
eg:查询员工工资，和其四舍五入的整数值

1. SELECT salary,ROUND(salary) FROM employees;

复制代码

1. CEIL(X):

复制代码

对浮点数X向上取整,即返回≥X的最小整数
eg:查询员工工资，并且向上取整

1. SELECT salary,CEIL(salary) FROM employees;

复制代码

1. FLOOR(X):

复制代码

对浮点数X向下取整,即返回≤X的最大整数
eg:查询员工工资，并且向下取整

1. SELECT salary,FLOOR(salary) FROM employees;

复制代码

1. TRUNCATE(X,length):

复制代码

对浮点数的小数部分进行截取→常用于进行保留小数操作

1. SELECT TRUNCATE(1.9999,2);->1.99

复制代码

• 1. MOD(X,Y)

  复制代码
  :对两个数进行区域操作即X%Y
  

日期函数

• 1. NOW(),SYSDATE()

  复制代码
  :返回当前系统日期+时间
  
• 1. CURDATE():

  复制代码
  返回当前系统日期,不包括时间
  
• 1. CURTIME()

  复制代码
  :返回当前系统时间,不包括日期
  
• 1. DATE_FORMAT(date,format):

  复制代码
  用于格式化日期,date是要格式化的数据,format是格式化的模式,格式化的通配符号如下表:
  

格式符功能%Y4位年份%y2位年份%m月份（01，02,…,11,12）%c月份（1,2,…,11,12）%d日（01，02，…）%H小时（24小时制）%h小时（12小时制）%i分钟（00，01，…,58,59）%s秒（00，01，…,58,59）eg:查询员工姓名、入职时间，入职时间按照xxxx年xx月xx日输出

1. SELECT DATE_FORMAT(hiredate,'%Y年%m月%d日') FROM employees;

复制代码

流程控制函数

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

复制代码

• 1. IF(expr,true_val,false_val):

  复制代码
  若表达式
  1. expr

  复制代码
  为真,则返回结果
  1. true_val

  复制代码
  ,否则返回
  1. false_val

  复制代码
  的结果
  

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

1. SELECT age,IF(age>=18,'adult','minor');

复制代码

• 1. CASE

  复制代码
  语法结构:类似于switch…case结构,用于实现多支路条件选择
  

1. SELECT exper1,exper2...,
   
2. CASE exper1
   
3.                  WHEN value1 THEN result1
   
4.                  WHEN value2 THEN result2
   
5.                  WHEN value3 THEN result3
   
6.                  ...
   
7.                  ELSE result
   
8. END
   
9. FROM table_name;

复制代码

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

1. SELECT department_id
   
2. CASE department_id
   
3.                  WHEN 1 THEN '经理办公室'
   
4.                  WHEN 2 THEN '财务部'
   
5.                  WHEN 3 THEN '后勤部'
   
6.                  ELSE 'unkown'
   
7. END AS department_name
   
8. FROM departments;

复制代码

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

1. SELECT exper1,exper2...,
   
2. CASE
   
3.                  WHEN condition 1 THEN result1
   
4.                  WHEN condition 2 THEN result2
   
5.                  ...
   
6.                  ELSE result
   
7. END
   
8. FROM table_name;

复制代码

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

1. SELECT
   
2. employee_name,hiredate,salary 原工资,
   
3. CASE
   
4.     WHEN hiredate<'2015-01-01' THEN salary*2
   
5.                 WHEN hiredate<'2018-01-01' THEN salary*1.3
   
6.                 ELSE salary
   
7. END AS 新工资
   
8. FROM employees;

复制代码

分组函数

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

复制代码

• 1. COUNT(*):

  复制代码
  计算指定列中非NULL值的数量,
  1. SUM(column)

  复制代码
  :计算指定列之和,
  1. AVG(column)

  复制代码
  :计算指定列平均数,
  1. MAX(colum):

  复制代码
  取出指定列最大值,
  1. MIN(colum)

  复制代码
  :取出指定列最小值
  

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

1. SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(*) FROM employees;

复制代码

分组查询

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

复制代码

按单个字段分组

• 通过
  1. GROUP BY

  复制代码
  关键字:按指定列进行分组
  

1. SELECT 列表
   
2. FROM 表
   
3. [WHERE 筛选条件]
   
4. GROUP BY 分组
   
5. [ORDER BY 排序]

复制代码

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

1. SELECT MAX(salary)
   
2. FROM employees
   
3. GROUP BY department_id;

复制代码

在分组前进行条件筛选

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

复制代码

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

1. SELECT *
   
2. FROM employees
   
3. WHERE hiredate >'2010-01-01'
   
4. GROUP BY department_id;

复制代码

在分组之后进行条件筛选

通过

1. HAVING

复制代码

语句可以在

1. GRUOP BY

复制代码

语句之后进行条件筛选
eg:查询员工人数大于120的部门

1. SELECT *
   
2. FROM employees
   
3. GROUP BY department_id
   
4. HAVING COUNT(*)>120;

复制代码

按多字段分组

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

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

1. SELECT department_id,sex,AVG(salary) AS 平均工资
   
2. FROM employees
   
3. GROUP BY department_id,sex;

复制代码

连接查询

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

复制代码

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

内连接(INNER JOIN)

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

复制代码

等值连接

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

复制代码

其语法结构如下:

1. SELECT colum1,colum2,....,
   
2. FROM table1
   
3. INNER JOIN table2
   
4. ON table1.colum = table.colum;

复制代码

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

1. SELECT employee_name AS 员工名,department_name AS 部门名
   
2. FROM employees e       
   
3. INNER JOIN departments d ON
   
4. e.department_id=d.department_id;

复制代码

非等值连接

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

复制代码

语法结构:

1. SELECT colum1,colum2,....,
   
2. FROM table1
   
3. INNER JOIN
   
4. ON table1.colum <operator> table2.colum;

复制代码

其中

1. operator

复制代码

可以是

1. >,<,≥,≤,≠,BETWEEN…AND

复制代码

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

1. SELECT e.salary,j.grade_level
   
2. FROM employees e
   
3. INNER JOIN job_grades j
   
4. ON e.salary BETWEEN j.lowest_sal AND j.higest_sal;

复制代码

自连接

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

复制代码

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

1. SELECT t1.employee_name AS 员工,t2.employee_name AS 领导
   
2. FROM employees t1
   
3. INNER JOIN employees t2
   
4. ON t1.manager_id=t2.employee_id;

复制代码

外连接

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

复制代码

左/右外连接

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

语法结构:

1. SELECT colum1,colum2...,
   
2. FROM table1 [LEFT|RIGHT]
   
3. JOIN ON [连接条件];

复制代码

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

1. SELECT employee_name AS 员工姓名,department_name AS 部门名称
   
2. FROM employees e LEFT
   
3. JOIN departments d
   
4. ON e.department_id=d.department_id;

复制代码

到此这篇关于Mysql中常用函数 分组,连接查询 的文章就介绍到这了,更多相关Mysql分组,连接查询 内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/328980agw.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！