Mysql:常用函数方法大全

最新推荐文章于 2025-03-18 10:12:41 发布
最新推荐文章于 2025-03-18 10:12:41 发布
阅读量314 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Joseph25/article/details/114001372
版权 
分类：
	一、按条件表达式筛选
	
	简单条件运算符：> < = != <> >= <=
	
	二、按逻辑表达式筛选
	逻辑运算符：
	作用：用于连接条件表达式
		&& || !
		and or not
		
	&&和and：两个条件都为true，结果为true，反之为false
	||或or： 只要有一个条件为true，结果为true，反之为false
	!或not： 如果连接的条件本身为false，结果为true，反之为false
	
	三、模糊查询
		like
		between and
		in
		is null

1.like
	①一般和通配符搭配使用
	通配符：
	% 任意多个字符,包含0个字符
	_ 任意单个字符
	#案例1：查询员工名中包含字符a的员工信息
	select * from employees where last_name like '%a%';#abc

	#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
    select salary FROM employees WHERE last_name LIKE '__e_a%';

#2.between and

	①使用between and 可以提高语句的简洁度
	②包含临界值
	③两个临界值不要调换顺序

	#案例1：查询员工编号在100到120之间的员工信息
	SELECT * FROM employees WHERE employee_id BETWEEN 120 AND 100;

#3.in判断某字段的值是否属于in列表中的某一项(也可以判断某列是否在另一个列里)
	①使用in提高语句简洁度
	②in列表的值类型必须一致或兼容
	③in列表中不支持通配符
	
	#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编
	SELECT last_name,job_id FROM employees WHERE job_id IN('AD_VP','AD_PRES');
	
	#判断region_tmp表里region_name在region表里的region_name列里
	select * from wufan.region_tmp where region_name in (
		select region_name from wufan.region
	)
	
分类：
	1、单行函数
	如 concat、length、ifnull等
	2、分组函数
	
	功能：做统计使用，又称为统计函数、聚合函数、组函数
	
常见函数：
	一、单行函数
	字符函数：
	length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
	concat
	substr
	instr
	trim
	upper
	lower
	lpad
	rpad
	replace
	
	数学函数：
	round
	ceil
	floor
	truncate
	mod
	
	日期函数：
	now
	curdate
	curtime
	year
	month
	monthname
	day
	hour
	minute
	second
	str_to_date
	date_format
	其他函数：
	version
	database
	user
	控制函数
	if
	case



#3.upper、lower
SELECT UPPER('john'); #JOHN
SELECT LOWER('JOB');  #job

#4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put; #陆展元

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; #李莫愁

#5.instr 返回子串第一次出现的索引，存在返回1，不存在返回0
SELECT INSTR('杨不侠悔爱上了殷六侠','殷八侠') AS out_put; #1

#6.trim
SELECT TRIM('    张翠山    ') AS out_put; #张翠山
//以aa为单位去掉
SELECT TRIM('aa' FROM 'aaaaa张翠山aaaaaaaaaa')  AS out_put; #a张翠山

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;

#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',7,'ab') AS out_put; #殷素素abab

#9.replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put; #张无忌爱上赵敏


#二、数学函数
#round 四舍五入
SELECT ROUND(-1.55); #-2
SELECT ROUND(1.567,2); #

#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02); #-1

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

#mod取余(hive不支持)
SELECT MOD(10,-3); 
SELECT 10%3; (hive支持)


#三、日期函数
#返回当前系统日期+时间
	mysql:SELECT NOW();
	hive:select from_unixtime(unixtimestamp(),'yyyy-MM-dd HH:mm:ss')
	 
#返回当前系统日期，不包含时间
SELECT CURDATE();

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


#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

SELECT  YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;


#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

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


#五、流程控制函数
#1.if函数：如果表达式成立返回第一个值，不成立返回第二个值
#1.nvl函数：nvl(str1,str2) str1为null返回str2，若str1不为null则返回str1

SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

select nvl(null,nihao)

#2.case函数
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;

select case when 10>5 then True else FALSE end from 表


六、排序order by
	SELECT * FROM employees ORDER BY salary DESC;#按工资降序
	SELECT * FROM employees ORDER BY salary asc; #默认升序，asc可以省略
	
	
七、数学聚合函数
	SELECT SUM(salary) FROM employees;
	SELECT AVG(salary) FROM employees;
	SELECT MIN(salary) FROM employees;
	SELECT MAX(salary) FROM employees;
	SELECT COUNT(salary) FROM employees;

#八、关联查询
/*
分类：
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross 
*/

#一）内连接
/*
分类：
等值
非等值
自连接
*/

#1、等值连接
#案例1.查询员工名、部门名

SELECT last_name,department_name
FROM departments d
 JOIN  employees e
ON e.`department_id` = d.`department_id`;


#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;


#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;


#二）非等值连接 
 #查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;
 
 
 #三）自连接
 
 #查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;
 
  #查询姓名中包含字符k的员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';
 
 
 #二、外连接
 
 /*
 应用场景：用于查询一个表中有，另一个表没有的记录
 
 特点：
 1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
 */
 #引入：查询男朋友 不在男神表的的女神名
 
 SELECT * FROM beauty;
 SELECT * FROM boys;
 
 #左外连接
 SELECT b.*,bo.*
 FROM boys bo
 LEFT OUTER JOIN beauty b
 ON b.`boyfriend_id` = bo.`id`
 WHERE b.`id` IS NULL;
 
 
 #案例1：查询哪个部门没有员工
 #左外
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 
 #右外
 
  SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 
 #全外
 USE girls;
 SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.id;


九、子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	
	from后面：
		支持表子查询
	where或having后面：★
		标量子查询（单行） √
		列子查询  （多行） √
		
		行子查询
		
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）
*/


#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/

#1.标量子查询★
#案例1：谁的工资比 Abel 高?

#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'

);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143

);


#案例3：返回公司工资最少的员工的last_name,job_id和salary

#①查询公司的 最低工资
SELECT MIN(salary)
FROM employees

#②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);


#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#①查询50号部门的最低工资
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

#②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50


);


#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);


#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

#①查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';


#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN( salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';



#3、行子查询（结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees


#②查询最高工资
SELECT MAX(salary)
FROM employees


#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees


)AND salary=(
	SELECT MAX(salary)
	FROM employees

);


#二、select后面
/*
仅仅支持标量子查询
*/
#案例：查询每个部门的员工个数
		
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;
 
 
 #案例2：查询员工号=102的部门名
 
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
	
) 部门名;



#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id


SELECT * FROM job_grades;


#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;



#四、exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)
结果：
1或0
*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#案例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees

)

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);


#案例2：查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`
);

 

齐全且实用的MySQL函数使用大全
Sheenky的博客
06-06 2万+
目录一、MySQL函数介绍二、MySQL函数分类（一）单行函数①字符串函数②数学函数③日期函数④流程控制函数⑤系统信息函数⑥其他函数（二）聚合函数三、函数使用示例（一）字符函数①length(str)函数②concat(str1,str2,...)函数③upper(str)、lower(str)函数④substr(str,start,len)函数⑤instr(str,要查找的子串)函数③trim(str)函数⑧lpad(str,len,填充字符)、rpad(str,len,填充字符)函数⑨replace(s
MySQL常用函数大全（总结篇）
热门推荐
zeng_ll的博客
02-19 8万+
本篇文章讲解是是MySQL的函数方法，涵盖所有的MySQL常见的方法。下面是本篇文章的目录结构，可以根据自己需求划到相应段落查找： 一、数字函数 二、字符串函数 三、日期函数 四、MySQL高级函数 （一）数字函数 1、ABS(x) 返回x的绝对值 SELECT ABS(-1); ---- 返回1 2、AVG(expression) 返回一个表达式的平均值，expres...
Mysql的函数方法
娓娓道来
09-06 282
mysql基础函数
MySQL函数大全（持续更新）
最新发布
milk_yan的博客
03-18 677
持续更新MySQL的常用和高阶函数。
MySQL学习笔记(四)子查询、分页查询及联合查询
Lizhj的博客
04-06 271
子查询 含义： 出现在其他语句中的select语句，称为子查询或内查询 外部的查询语句，称为主查询或外查询 分类： 按子查询出现的位置： select后面： 仅仅支持标量子查询 from后面： 支持表子查询 where或having后面：★ 标量子查询（单行） √ 列子查询 （多行） √ 行子查询 exists后面（相关子查询） ...
Mysql常用函数/方法
李指导的博客
03-22 878
Mysql常用函数/方法
记录一些MySQL的系统函数和方法
jxiaojian的博客
08-01 477
在百度经验里面发现了和我想写的东西差不多的文章，转载过来，后续再更新自己的 →  百度经验原文   本篇主要介绍MySQL自带的各种系统函数 字符串函数 1 concat():字符串连接; concat_ws():使用指定的分隔符进行连接。 例子： SELECT concat("my","_","sql") AS new_col; SELECT concat_ws(...
MYSQL基础：常用函数.docx
08-08
以下是关于MySQL常用函数的详细说明： 一、聚合函数 聚合函数对一组数据进行操作并返回单个结果，常用于统计分析。常见的聚合函数有： 1. COUNT()：计算指定列或表中的行数。例如，`SELECT COUNT(*) FROM table_...
Mysql常用函数大全(分类汇总讲解)
09-09
本文将对MySQL中的常用函数进行分类汇总讲解，帮助你更好地理解和应用这些功能。 首先，我们来看数学函数。数学函数在SQL查询中用于进行各种数学运算。例如，`ABS(x)`返回数字的绝对值；`BIN(x)`将十进制数转换为二...
mysql截取函数常用方法使用说明
12-15
这里我们将详细介绍四个常见的MySQL截取函数：`LEFT()`、`RIGHT()`、`SUBSTRING()` 和 `SUBSTRING_INDEX()`。 1. **`LEFT()` 函数**： `LEFT(str, length)` 用于从字符串 `str` 的左侧开始截取 `length` 个字符。...
MySql常用内置函数
10-28
内容概要：本文档介绍了 MySQL 数据库中的内置函数及其使用方法，包括日期时间函数、字符串函数、数学函数及其他常用函数。详细解释了每种类型的函数及其常见应用场景，提供了具体的 SQL 语句实例。 适合人群：MySQL...
MySQL_函数
底层码农
04-15 7354
MySQL_函数
MySQL函数使用
BestEternity的博客
04-30 2468
1.UUID()函数 UUID是可以生成时间、空间上都独一无二的值，是随机+规则组合而成，UUID产生的是字符串类型值，固定长度为 36 个字符。 例： SELECT UUID(),UUID(),LENGTH(UUID()),CHAR_LENGTH(UUID()); 2.IF()函数语法： IF(expr1,expr2,expr3); 如果expr1是TRU...
mysql常用方法及函数
空谷余音的博客
08-18 371
1. SQL 结构化查询语言 structured query language sql语句对于不同数据库有微小差异 1) DDL 数据定义语言 create 创建表、视图、索引… drop 删除表、视图、索引… alter 修改表 truncate 截断 2) DML 数据操控语言 (**) insert 插入数据 upda...
MySQL常用函数大全（实例演示）
weixin_55076626的博客
10-18 2万+
【代码】MySQL常用函数大全（实例演示）
列出各部门的最低工资MySQL_mysql练习题五
weixin_35435986的博客
02-05 2455
26、列出所有员工的姓名、部门名称和工资。mysql> SELECT-> e.ename,-> d.dname,-> e.sal-> FROM-> emp e-> JOIN-> dept d-> ON-> e.deptno = d.deptno;+--------+------------+---------+| ename ...
第六章分组函数
two2123427的博客
08-03 1238
分组函数: 单行函数和分组函数的区别： 单行：输入一行，输出也是一行。处理一行 分组：输入可能多行（一组），输出只有一行。处理多行 • MIN函数和MAX函数        –MIN和MAX函数主要是返回每组的最小值和最大值。            •MIN([DISTINCT|ALL] column|expression)            •MAX([DISTINCT|ALL
第七章子查询课后练习和作业
码艺丫嘿的博客！
08-01 7524
– 练习 练习一 -- 1.查询入职日期最早的员工姓名，入职日期 select ename 员工姓名, hiredate 入职日期 from emp where hiredate = (select min(hiredate) from emp); -- 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称 sel...
Mysql总结--查询语言--子查询
跨界小白
03-17 633
Mysql总结--查询语言--子查询
MySQL基础知识整理：常用函数与操作指南
对于学习MySQL常用函数，创建一个思维导图能够帮助记忆和快速查找各个函数。思维导图中可以包括函数类别、函数名称、使用场景等关键信息。例如，聚合函数和条件函数是常见的分类，它们各自包含多个具体的函数。 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值