MySQL DQL加强知识整理

最新推荐文章于 2024-07-20 16:33:27 发布

才疏学浅的小缘同学

最新推荐文章于 2024-07-20 16:33:27 发布

阅读量1k

点赞数 1

分类专栏： MySQL学习文章标签： mysql

本文链接：https://blog.csdn.net/qq_44790505/article/details/119892200

版权

MySQL学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

2.3.1 DATE_ADD(datetime,INTERVAL expr type)

2.3.2 DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

2.4 流程函数

2.5 其他函数

2.6 聚合函数

2.6.1 可以对数值型数据使用AVG和SUM函数

2.6.2 可以对任意数据类型的数据使用MIN和MAX函数

2.6.3 COUNT(*) COUNT(1)返回表中记录总数，使用于任意数据类型

2.6.4 COUNT(expr) 返回expr不为空的记录总数。

2.6.5 count(*)，count(1)谁更好?

1.1 子查询

在一个DQL查询中嵌套一个DQL查询，所有的子查询都包含在父查询里面。所有的子查询必须定义在一对小括号中。

子查询可以出现的地方：where之后、having之后、from之后、from之前

子查询返回的结果集有三中：单(单行单列)、多(多行单列)、多(多行多列

1.1.1 单行单列子查询

场景：查询工资高于公司平均工资的所有员工

select avg(sal) 公司平均工资
from emp;

select empno,ename,job,sal,deptno,hiredate
from emp
where sal >(select avg(sal) 公司平均工资 from emp);

1.1.2 多行单列子查询

场景：查询工资大于任意一部门的最高工资的员工信息

select empno,ename,sal,job,deptno,hiredate
from emp
where sal > any(
	select max(sal) 
	from emp
	group by deptno
);

分析：需要用到any关键字。该关键字通常定义在where后面用作子查询，子查询结果是一个多行单列的集合，只要父查询条件匹配子查询集合的任意的一行数据，就提取父查询的数据。

场景：查询工资大于所有部门的平均工资的员工信息

分析：需要用到all关键字，该关键字通常定义在where后面作为子查询，子查询的结果是一个多行单列的结果集，当父查询的条件匹配子查询结果集所有数据，就提取父查询的数据。

select empno,ename,sal,job,deptno
from emp
where sal > all(
	select avg(sal)
    from emp
    group by deptno
);

场景：查询员工编号、员工名称、员工所在部门

select e.empno,e.ename,d.dname
from emp e inner join dept d on e.deptno = d.deptno;
-- 查询员工编号、员工名称、员工所在部门 要求：使用子查询完成
-- 部门名称作为子查询嵌入在父查询的select语句中
select empno,ename,
		(select dname from dept where dept.deptno = emp.deptno)部门名称
from emp ;

场景：查询部门平均工资高于公司平均工资的部门编号和该部门的员工平均工资

分析：公司平均工资我们不知道，可以作为子查询嵌入在父查询中

select deptno,avg(sal)部门平均工资
from emp
group by deptno
having 部门平均工资 >(
	select avg(sal)
	from emp
);

1.1.3 多行多列子查询

场景：查询平均工资高于2000的职位名称和职位平均工资

select job 职位名称, avg(sal)职位平均工资 
from emp
group by 职位名称
having  职位平均工资 > 2000;

上面场景使用子查询完成

select 职位名称,职位平均工资
from (
	select job 职位名称, avg(sal)职位平均工资
    from emp
    group by  职位名称  
)e
where 职位平均工资 > 2000;

小结：定义在from之后的子查询是一张伪表，返回结果多行多列

工作中尽量不要在having后面编写子查询，效率不高

小结

1 子查询出现的地方： where之后、from之前、from之后、having之后

2 子查询返回的结果：单行单列(where之后、having之后、from之前) 、多行单列( where之后的any和all)、多行多列(from之后)

from之前的子查询：伪列

from之后的子查询：伪表

3 工作中尽量不要在having后面编写子查询，而是使用伪表+where条件

问题：where和having区别？

having主要对分组和聚合函数查询的结果集进行过滤

where 对from结果集进行过滤，where不支持聚合函数过滤，因为where先执行group by后执行。

2.1 字符串函数

函数	用法
CONCAT(S1,S2,......,Sn)	连接S1,S2,......,Sn为一个字符串
CONCAT_WS(分隔符, S1,S2,......,Sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上分隔符
LENGTH(s)	返回字符串s的字节数，和字符集有关
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

2.2 数值函数、数学函数

函数	用法
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

2.3 日期函数

函数	用法
CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
WEEK(date) / WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type)	返回与给定日期时间相差的INTERVAL(间隔)时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

2.3.1 DATE_ADD(datetime,INTERVAL expr type)

表达式类型：

参数类型	参数类型
YEAR	YEAR_MONTH
MONTH	DAY_HOUR
DAY	DAY_MINUTE
HOUR	DAY_SECOND
MINUTE	HOUR_MINUTE
SECOND	HOUR_SECOND
	MINUTE_SECOND

select DATE_ADD(NOW(),INTERVAL 1 YEAR);
select DATE_ADD(NOW(),INTERVAL -1 YEAR);
select DATE_ADD(NOW(),INTERVAL '1_1' YEAR);

可以看出年份被修改

2.3.2 DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,....）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，....）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数(01,02...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）	%h和%I	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)
%j	以3位数字表示年中的天数(001,002...)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

2.4 流程函数

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	两位数字表示年份
%M	月名表示月份（January,....）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，....）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数(01,02...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）	%h和%I	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)
%j	以3位数字表示年中的天数(001,002...)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

2.4 流程函数

函数	用法
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 .... [ELSE resultn] END	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "实发工资"
FROM   employees;

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees;

2.5 其他函数

函数	用法
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名
password(str)	返回字符串str的加密版本，41位长的字符串
md5(str)	返回字符串str的md5值，也是一种加密方式

2.6 聚合函数

2.6.1 可以对数值型数据使用AVG和SUM函数

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

2.6.2 可以对任意数据类型的数据使用MIN和MAX函数

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

2.6.3 COUNT(*) COUNT(1)返回表中记录总数，使用于任意数据类型

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

2.6.4 COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

2.6.5 **count(*)，count(1)谁更好?**

其实，对于myisam引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb引擎的表用count(*)直接读行数，效率很低，因为innodb真的要去数一遍。

才疏学浅的小缘同学

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL DQL加强知识整理

1.1 子查询在一个DQL查询中嵌套一个DQL查询，所有的子查询都包含在父查询里面。所有的子查询必须定义在一对小括号中。子查询可以出现的地方：where之后、having之后、from之后、from之前子查询返回的结果集有三中：单(单行单列)、多(多行单列)、多(多行多列1.1.1 单行单列子查询场景：查询工资高于公司平均工资的所有员工select avg(sal) 公司平均工资from emp;select empno,ename,job,sal,deptno,hi..
复制链接

扫一扫