算法-mysql笔记

原创 已于 2025-07-22 16:19:12 修改 · 513 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#笔记

于 2025-04-14 08:21:42 首次发布

查询

寻找用户推荐人

IS  null

mysql判断数据是空 IS  null     

                        非空 IS  NOT  null

在 MySQL 中，三值逻辑是指逻辑表达式的结果可以是 TRUE、FALSE 或 UNKNOWN，涉及到and or 操作

584. 寻找用户推荐人 - 力扣（LeetCode）

# Write your MySQL query statement below
SELECT
    name
FROM
    Customer
WHERE
    referee_id != 2 
    OR referee_id IS null

文章概览

GROUP  BY

当查询到有多个相同的数据的时候，可以用  GROUP  BY  然后输入重复的数据

1148. 文章浏览 I - 力扣（LeetCode）

# Write your MySQL query statement below
SELECT
    author_id AS id
FROM
    Views
WHERE
    author_id = viewer_id
GROUP BY id
ORDER BY id asc

无效的推文

 LENGTH()

计算字符串的长度函数    LENGTH()    或者是    CHAR_LENGTH()  

相关解析：

mysql数据库中的字符串长度函数：LENGTH() 与 CHAR_LENGTH()_mysqllength-CSDN博客

1683. 无效的推文 - 力扣（LeetCode）

# Write your MySQL query statement below
SELECT
    tweet_id
FROM
    Tweets
WHERE
    CHAR_LENGTH(content) > 15
;

连接

使用唯一标识码替换员工ID

LEFT JOIN 

这里要完全显示一张表里面所有符合规定的字段，如果是null，也要显示，那么我们要用

LEFT JOIN  来全部显示左边的那一张表，包括null

1378. 使用唯一标识码替换员工ID - 力扣（LeetCode）

# Write your MySQL query statement below
SELECT
    uni.unique_id,
    e.name
FROM
    Employees e
LEFT JOIN
    EmployeeUNI uni
ON
    e.id = uni.id





产品销售分析 I

# Write your MySQL query statement below
Select 
    Product.product_name,
    Sales.year,
    Sales.price
from 
    Sales
left join 
    Product
on
    Sales.product_id = Product.product_id

或者

# Write your MySQL query statement below
Select 
    Product.product_name,
    Sales.year,
    Sales.price
from 
    Product, Sales 
where
    Sales.product_id = Product.product_id

进店却未进行过交易的顾客

not in

解法1：

这里面 用到 not in 来表示不在...里面

# Write your MySQL query statement below
select 
    customer_id,
    count(visit_id) as count_no_trans
from 
    Visits
where 
    visit_id not in (select distinct visit_id from Transactions)
group by customer_id

解法2：

仔细体会连接是怎么连的

select
    Visits.customer_id,
    count(Visits.visit_id) as count_no_trans 
from 
    Visits
left join 
    Transactions
on 
    Visits.visit_id = Transactions.visit_id 
where 
    Transactions.transaction_id is null
group by 
    Visits.customer_id

上升的温度

DATEDIFF

在 MySQL 中，DATEDIFF() 函数用于计算两个日期之间的差异，返回值是两个日期相差的天数。

语法

DATEDIFF(date1, date2)

参数

  • date1 和 date2 是两个日期值，可以是 DATE 类型或 DATETIME 类型。

  • 函数返回值是 date1 减去 date2 的结果，单位是天。

返回值

  • 如果 date1 晚于 date2，返回正数。

  • 如果 date1 早于 date2，返回负数。

  • 如果两个日期相同，返回 0。

# Write your MySQL query statement below
select 
    w1.id
from 
    Weather w1, Weather w2
where 
    w1.Temperature > w2.Temperature and
    dateDiff(w1.recordDate, w2.recordDate) = 1;

每台机器的进程平均运行时间

AVG , ROUND

AVG 函数

AVG 函数用于计算一组数值的平均值，适用于数据分析和统计场景。它通常作用于数值类型的列或表达式，返回所有非空值的算术平均值。

语法示例（以 SQL 为例）：

SELECT AVG(column_name) FROM table_name;

ROUND 函数

ROUND 函数用于对数值进行四舍五入操作，常用于结果格式化或简化计算精度。可指定保留的小数位数，支持正数（小数点后位数）和负数（整数部分位数）参数。

语法示例（以 SQL 为例）：

SELECT ROUND(column_name, decimal_places) FROM table_name;

# Write your MySQL query statement below
select 
    a1.machine_id,
    round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
from 
    Activity as a1
inner join 
    Activity as a2
on
    a1.machine_id = a2.machine_id and
    a1.process_id = a2.process_id and
    a1.activity_type = 'start' and
    a2.activity_type = 'end'
group by 
    a1.machine_id



员工奖金

LEFT JOIN 

left join 的 on 是用来连接的 不是用来过滤的

# Write your MySQL query statement below
select 
    Employee.name,
    Bonus.bonus 
from 
    Employee
left join 
    Bonus 
on
    Employee.empId = Bonus.empId 
where
    Bonus.bonus < 1000 or Bonus.bonus is null

学生们参加各科测试的次数

CROSS JOIN

CROSS JOIN（笛卡尔积连接）用于对两个表做全量配对，输出结果包含左表中每一行与右表中每一行的所有可能组合。

基本语法

SELECT <列列表> FROM 表A CROSS JOIN 表B;

结果含义

  • 如果表 A 有 M 行，表 B 有 N 行：

    • 则 A CROSS JOIN B 会产生 _M × N 行。

  • 每一行都是「A 表的某行」+「B 表的某行」的拼接。

举例：

-- 表 Students:
-- +------------+--------------+
-- | student_id | student_name |
-- +------------+--------------+
-- |          1 | Alice        |
-- |          2 | Bob          |
-- +------------+--------------+

-- 表 Subjects:
-- +--------------+
-- | subject_name |
-- +--------------+
-- | Math         |
-- | English      |
-- +--------------+

SELECT
  s.student_name,
  sub.subject_name
FROM
  Students AS s
CROSS JOIN
  Subjects AS sub;

结果：

student_namesubject_name
AliceMath
AliceEnglish
BobMath
BobEnglish

代码

# Write your MySQL query statement below
select
    Students.student_id,  -- 选择学生的ID
    Students.student_name,  -- 选择学生的姓名
    Subjects.subject_name,  -- 选择科目的名称
    Count(Examinations.subject_name) as attended_exams  -- 计算每个学生参加每门科目的考试次数
from 
    Students  -- 从学生表开始
Cross join  -- 使用交叉连接生成所有学生和科目的组合
    Subjects 
left join  -- 使用左连接将考试记录表与学生和科目组合连接起来
    Examinations 
on 
    Students.student_id = Examinations.student_id  -- 连接条件：学生ID匹配
    and Examinations.subject_name = Subjects.subject_name  -- 连接条件：科目名称匹配
group by 
    Students.student_id,  -- 按照学生ID分组
    Subjects.subject_name  -- 按照科目名称分组
order by 
    Students.student_id;  -- 按照学生ID排序

至少有5名直接下属的经理

group by having

-- 查询至少有5名直接下属的经理姓名
SELECT 
    manager.name
FROM 
    Employee manager
LEFT JOIN
    Employee niuma
ON
    manager.id = niuma.managerId
-- 按经理ID分组，以便统计每个经理的下属数量
GROUP BY
    manager.id
-- 过滤出那些下属数量大于等于5的经理
HAVING 
    COUNT(manager.id) >= 5;

1. 执行顺序概览

SQL 的逻辑执行顺序为：

  1. FROM + JOIN

  2. GROUP BY（并在分组阶段计算各组的聚合值）

  3. HAVING（基于刚才计算好的聚合值做过滤）

  4. SELECT（只把你在 SELECT 子句里指定的列投影到最终结果）

2. 示例数据和 JOIN 结果

假设原始表 Employee 如下：

IdNameSalaryManagerId
1Joe70000NULL
2Henry800001
3Sam600001
4Max90000NULL
5Janet690001
6Randy850001
7Will700004

FROM Employee AS Manager JOIN Employee AS Report ON Manager.Id = Report.ManagerId

生成的扁平化中间表（共 5 行）：

M.IdM.NameM.SalaryR.IdR.NameR.SalaryR.ManagerId
1Joe700002Henry800001
1Joe700003Sam600001
1Joe700005Janet690001
1Joe700006Randy850001
4Max900007Will700004

3. 分组并计算聚合值（GROUP BY）

 

GROUP BY Manager.Id 
 

  •  
    分组阶段，引擎为每个不同的 Manager.Id 开一个“组桶”。
     
  •  
    同时，它会在每个组桶里累加 COUNT(Report.Id) 的值：
     
  
    •  
      组 1（Joe）见到 4 行 → 内存中计数器记为 4
       
    •  
      组 4（Max）见到 1 行 → 内存中计数器记为 1
       
     
 

 
 
注意：虽然我们常常在演示里把这个计数器称作 cnt_reports，但数据库内部并不会“给它”一个列名，也不会把它附到结果表中；它只是存于执行上下文，用来后续判断。
 

 

此时逻辑上有两个分组及其计数：
 

Manager.Id（内部计数）COUNT(Report.Id)
14
41
 

 

4. 过滤分组（HAVING）
 

HAVING COUNT(Report.Id) >= 5 
 

  •  
    引擎去读每个组的内部计数：
     
  
    •  
      组 1：4 < 5 → 丢弃
       
    •  
      组 4：1 < 5 → 丢弃
       
     
  •  
    结果：零个组被保留下来。
     
 

5. 投影输出（SELECT）
 

SELECT Manager.Name AS Name 
 

  •  
    由于前一步已经没有任何组满足条件，输出自然是空结果集，也就看不到任何行。
     
 

 

聚合函数
 

平均售价
 

-- 选择产品ID和计算的平均售价
select 
    p.product_id,
    -- 计算平均售价，使用IFNULL处理NULL值，ROUND函数保留两位小数
    Round(ifnull(Sum(p.price * u.units) / Sum(u.units), 0), 2) as average_price 
from
    Prices p
-- 使用LEFT JOIN确保所有产品都被包含，即使没有销售记录
left join
    UnitsSold u
on
    p.product_id = u.product_id 
    -- 确保销售日期在价格有效期内
    and u.purchase_date Between p.start_date and p.end_date 
-- 按产品ID分组以计算每个产品的平均售价
group by
    p.product_id;

 

Between ... and ...
 

判断一个日期是否在一段日期的中间，用Between ... and ...。
 

ROUND
 

四舍五入
 

ifnull
 

如果是null，返回某一个指定的数字
 

 

各赛事的用户注册率
 

不要一上来就连接两张表
 

懂得利用子查询
 

SELECT
  r.contest_id,
  ROUND(
    COUNT(r.user_id) * 100.0
    / (SELECT COUNT(*) FROM Users),
    2
  ) AS percentage
FROM Register AS r
GROUP BY
  r.contest_id
ORDER BY
  percentage DESC,
  r.contest_id ASC;

 

 

查询结果的质量和占比
 

这里面，主查询需要用到分组，而子查询的数据要和主查询一致，所以子查询的数据也要和主查询一样分组。但是子查询不能直接进行分组，为了保证数据的一致，只能用等值匹配来保持数据的一致。
 

SELECT
    q1.query_name,                                                              -- 查询名称（分组维度）
    ROUND(SUM(q1.rating / q1.position) / COUNT(*), 2) AS quality,             -- 计算质量指标：所有查询的(rating/position)的平均值，保留2位小数
    ROUND(                                                                      -- 计算劣质查询占比，保留2位小数
        (
            SELECT COUNT(q2.rating)                                             -- 子查询：统计同一查询名称下评分<3的记录数
            FROM Queries q2
            WHERE q2.query_name = q1.query_name                                 -- 关联主查询的query_name
              AND q2.rating < 3                                                 -- 劣质查询条件
        ) * 100.0                                                               -- 转换为百分比（乘以100.0确保浮点运算）
        / COUNT(*)                                                              -- 除以当前查询名称的总记录数
    , 2) AS poor_query_percentage                                               -- 别名：劣质查询百分比
FROM Queries q1
GROUP BY q1.query_name;                                                         -- 按查询名称分组统计
 

 

每月交易 I
 

# Write your MySQL query statement below
select
    DATE_FORMAT(trans_date, '%Y-%m') as month,
    country,
    COUNT(id) AS trans_count,
    COUNT(IF(state = 'approved', 1, NULL)) as approved_count,
    SUM(amount) as trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) as approved_total_amount
from
    Transactions
group by
    month,
    country

 

1 DATE_FORMAT(date, format)
 

作用：将 DATE 或 DATETIME 类型的日期值，按指定格式输出为字符串。
 

  •  
    参数
     
  
    •  
      date：要格式化的日期列或表达式。
       
    •  
      format：格式化模板，使用 MySQL 定义的格式符号（%Y、%m、%d 等）。
       
     
  •  
    常用格式符
     
  
    •  
      %Y：4 位年（如 “2025”）
       
    •  
      %y：2 位年（如 “25”）
       
    •  
      %m：2 位月（01–12）
       
    •  
      %c：不补零的月（1–12）
       
    •  
      %d：2 位日（01–31）
       
    •  
      %e：不补零的日（1–31）
       
    •  
      %H：24 小时制小时（00–23）
       
    •  
      %i：分钟（00–59）
       
    •  
      %s：秒（00–59）
       
     
 

 

2. COUNT(expr)
 

作用：统计分组内满足条件的行数或非空值的个数。
 

  •  
    不同写法
     
  
    •  
      COUNT(*)：统计分组中所有行（包括列值 NULL 的行）。
       
    •  
      COUNT(column)：统计某列 非 NULL 的行数。
       
    •  
      COUNT(IF(condition, 1, NULL))：先用 IF 进行条件判断，只对满足条件时返回非 NULL，再计数，即实现“有条件的计数”。
       
     
 

 

3. SUM(expr)
 

作用：对分组内的数值表达式求和。
 

  •  
    特点
     
  
    •  
      忽略 NULL 值。
       
    •  
      可与 CASE WHEN 或 IF 配合，实现有条件的求和。
       
     
 

 

4. IF(condition, true_value, false_value)
 

作用：在 SQL 中根据条件返回不同的值，相当于简写版的 CASE WHEN … THEN … ELSE … END。
 

  •  
    参数
     
  
    •  
      condition：布尔表达式。
       
    •  
      true_value：当 condition 为真时返回的值。
       
    •  
      false_value：当 condition 为假时返回的值。
       
     
 

 

即时食物配送 II
 

时间也是可以用min函数去找最老最旧的数据的
 

# Write your MySQL query statement below
select
    ROUND(
            SUM(order_date = customer_pref_delivery_date) * 100 / 
            COUNT(*), 
            2
        ) 
        as immediate_percentage
from
    Delivery 
where 
    (customer_id, order_date) in 
    (
        select 
            customer_id,
            MIN(order_date)
        from
            Delivery
        group by
            customer_id
    )

 

 

排序和分组
 

销售分析 III
 

要判断某个字段出现的日期是否 只 在某个区间内，可以用这个字段的日期的最大值和最小值比较这个区间的右边界和左边界
 

# Write your MySQL query statement below
select
    p.product_id,
    p.product_name
from
    Product p
left join
    Sales s
on 
    p.product_id = s.product_id 
group by
    p.product_id 
having
    MAX(sale_date) <= '2019-03-31' and
    MIN(sale_date) >= '2019-01-01'


 


                

        

    

    
  



    



            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

  
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值