mysql学习笔记

最新推荐文章于 2025-03-20 05:11:00 发布

原创最新推荐文章于 2025-03-20 05:11:00 发布 · 200 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #mysql #sql #hive

子查询

子查询就是将一个查询（子查询）的结果作为另一个查询（主查询）的数据来源或判断条件的查询。

常见的子查询有WHERE子查询，HAVING子查询，FROM子查询，SELECT子查询，EXISTS子查询，子查询要使用小括号（）；

WHERE子查询

SELECT *
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP);

查询薪资比平均薪资低的员工信息

HAVING子查询

HAVING子句是对分组统计函数进行过滤的子句，也可以在HAVING子句中使用子查询

SELECT JOB,AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY JOB);

查询平均薪资最高的职位及其平均薪资

FROM子查询

FROM子查询就是将一个查询结构（一般多行多列）作为主查询的数据源

SELECT JOB,AVG(SAL)
FROM (SELECT JOB,AVG(SAL) AS AVGSAL FROM EMP GROUP BY JOB)TEMP
WHERE TEMP.AVGSAL>2000;

查询平均薪资高于2000的职位以及该职位的平均薪资

SELECT子查询

SELECT子查询在SELECT子句中使用查询的结果(一般会和dual空表一起使用)

SELECT (SELECT COUNT(*) FROM EMP WHERE JOB = 'SALESMAN')/(SELECT COUNT(*) FROM EMP)
FROM DUAL;

职位是SALESMAN的员工占总员工的比例

EXISIT子查询

将主查询的数据带到子查询中验证，如果成功则返回true，否则发水false。主查询接收true是就会显示这条数据,flase就不会显示。

SELECT *
FROM EMP E
WHERE EXISIT (
	SELECT *
	FROM DEPT D
	WHERE E.DEPTNO = D.DEPTNO);

查询有部门的员工信息

查询薪资排名的员工信息（面试）

SELECT *
FROM EMP
WHERE SAL = (SELECT MIN(SAL) 
    		FROM (SELECT ROWNUM,SAL 
          		 FROM (SELECT SAL FROM EMP GROUP BY SAL ORDER BY SAL DESC)
            	 WHERE ROWNUM<=n));

查询薪资排名第n个员工的信息（包括并列排名）
思路：
1.先按薪资降序分组
2.再取前n名薪资中最低的薪资，即第n名的薪资。
3.最后在原表中找出薪资与最低薪资相同的员工信息。

准备表格（复制源数据进行连接、筛选）

from

从数据库中找到from后指定的表格，复制一张完全一样的表格用于查询处理

join

详细学习链接

where

将数据按以下条件进行筛选

基于from复制的表格，按照where后的条件对表格中的行进行筛选
=单个数值精确匹配
in可以实现多选，not in可以反选
and/or可以并列多个条件
like配合通配符进行模糊匹配

查询字段

对准备好的表格进行排序和行数限制

orderby

根据以下字段的升/降序进行排序
按照字段的顺序对表格的行进行排序，默认升序，字段后加DESC为降序

limit

留下指定行数或指定位置的数据

基于排序好的表格，指定行数的数据
limit n 则返回前n行，如：limit 10 返回前10行
limit x,y 则从第x行后返回y行，如limit 10,2则会返回第11和12行
常用来取固定名次的数据，或进行数据的初步查看

查询表格中的字段，并基于已有的字段进行计算

select

（在已经查询好了的数据中）查看以下字段
基于from、where、group by、having处理好的数据表格，按照select后的计算规则，计算并复制指定的字段

处理函数

distinct去重

distinct还可以进行去重计数

count(distinct 字段)

like、%通配符、_占位符模糊查询

理论上只能跟在where后
'%'代表任意数量字符，"_"只代表单个字符

聚合函数

相当于Excel透视表里的值字段设置，不和group by一起用，会对整个表进行计算
在这里插入图片描述
聚合函数都会忽略列中的NULL值，但是COUNT(*)也就是统计全部数据的行数时，不会忽略NULL值。

case when条件判断函数

CASE语句是用来做条件判断的，如果满足条件A，那么就xxx，如果满足条件B，那么就xx。

需要注意的几点：

CASE 语句始终位于 SELECT 条件中。
CASE 必须包含以下几个部分：WHEN、THEN和 END。ELSE 是可选组成部分，用来包含不符合上述任一 CASE 条件的情况。
你可以在 WHEN 和 THEN之间使用任何条件运算符编写任何条件语句（例如 WHERE），包括使用 AND 和 OR 连接多个条件语句。

SELECT
    s.s_id,
    s.s_name,
    s.s_sex,
    CASE
WHEN s.s_sex = '1' THEN '男'
WHEN s.s_sex = '2' THEN '女'
ELSE '其他'
END as sex,
 s.s_age,
 s.class_id
FROM
    t_b_student s

如上，我们使用CASE WHEN(条件一) THEN(条件一的结果),ELSE(其他不符合条件一的结果),END语句设立了两个条件，即当standard_qty为0或者不存在时我们返回0，当standard_qty不为0时进行计算，并储存为新列unit_price。

coalesce()空值处理函数

COALESCE(col_1,0) – 将col_1中的NULL值替换为0
COALESCE(col_2,‘no DATA’) – 将col_2中的NULL值替换为no DATA

over()窗口函数

对准备好的表格，在任意分组内部进行排序和计算（为了区分，我们称之为分区）

SELECTyr,party, votes,RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY yr,votes DESC
limit 20

再创建一个之前准备好的表格（还未排序和限制）
FROM ge
WHERE constituency = ‘S14000021’
根据partition by 后的字段（没有则默认针对整个表）形成的分区（不去重合并）【Excel演示】
OVER (PARTITION BY yr ORDER BY votes DESC)
在分区内根据order by后的条件对所有行进行排序
————以上创建一个新的表格，并重新进行分区排序就是"over"的本质
OVER (PARTITION BY yr ORDER BY votes DESC)
计算over()前函数的结果
RANK()根据分区内的排序，分配排名
不同排序函数之间的区别：详细链接
ROW_NUMBER：唯一且连续排名；DENSE_RANK：不唯一连续排名；RANK：不唯一不连续排名
将结果返回到最终查询出的表格
无论最终查询出的表格排序如何，窗口函数计算的列都只跟窗口函数指定排序下的计算结果一致