SQL试题

Zincy星辰

已于 2023-03-29 23:40:57 修改

阅读量588

点赞数

分类专栏： Python题集 CodeTop手撕代码教程文章标签： sql mysql 数据库

于 2023-02-06 14:27:50 首次发布

本文链接：https://blog.csdn.net/qq_52057693/article/details/127313768

版权

CodeTop手撕代码教程同时被 2 个专栏收录

21 篇文章 2 订阅

订阅专栏

Python题集

12 篇文章 1 订阅

订阅专栏

本文介绍了SQL中的表连接方式，包括内连接(INNERJOIN)、外连接(LEFT和RIGHTJOIN)以及三表联查。还涉及了分组查询(GROUPBY)、排序 ORDERBY)、限制返回行数(LIMIT)等操作，以及日期处理和条件查询功能。

摘要由CSDN通过智能技术生成

连接 JOIN … ON

# 表连接（三种方法）
SELECT * FROM tA, tB WHERE tA.ID=tB.ID
SELECT * FROM tA JOIN tB ON tA.ID=tB.ID
SELECT * FROM tA JOIN tB USING(ID)

# 三表联查（连续使用JOIN）
SELECT * FROM A
INNER JOIN B ON A.id=B.id
INNER JOIN C ON B.id=C.id

# 内连接、外连接
(INNER)JOIN … ON …
# 左连接
LEFT OUTER JOIN
# 右连接
RIGHT OUTER JOIN

常用

DISTINCT 去重    NOT IN 去除    IS NOT NULL 不为空    DESC降序
# 模糊查询
[NOT] LIKE '%xxx%';
            _ 匹配任意一个字符
            % 匹配0个或多个字符
            [ ] 匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
            [^ ] 不匹配[ ]中的任意一个字符
# 分区函数
over(partition by xx order by …)

# 选择指定区间
WHERE xx BETWEEN 5 AND 100    # [5,100]左闭右闭

# 分组+过滤
GROUP BY … HAVING …

# 排序
# ORDER BY … DESC    # 降序（默认升序：ASC）

# 限制返回行数
LIMIT … OFFSET …     # limit限制返回多少行，offset（截掉前几行）开始显示
LIMIT 5              # 前5行
LIMIT 5,95           # 从第5个以后开始，显示95个（6-100行）
LIMIT 95 OFFSET 5    # 从第5个以后开始，显示95个（6-100行）
LIMIT m-n offset n   # 第 n 到第 m 行

数学

count()计数    avg()平均    sum()和
round(xx,2)取整    if(xx,1,0)判断

字符串处理

# 拼接字符串
CONCAT(string1, string2, …)

# 截取字符串
SUBSTRING(xx, 起始位置1, 截取字符个数)
# 截取字符串（按/分割）
SUBSTRING_INDEX(xx,'/',-1)

时间

now()                                  # 当前时间
date()                                 # 日期
curdate()                              # 当前日期
date_format(xx, '%Y-%m')               # 按格式输出日期
date_sub(date(xx), interval 30 day)    # 筛选30天范围内
timestampdiff(second, tima, timb)      # 计算时间差

date()日期    year()年    month()月    day()日    hour()时    minites()分    second()秒

# 查询最近X天的数据
SELECT * FROM tablename WHERE 👇
# 今天
WHERE to_days(时间字段名) = to_days(now())
# 昨天
WHERE to_days(now()) - to_days(时间字段名) <= 1
# 近7天
WHERE date_sub(curdate(), interval 7 days) <= date(时间字段名)
# 近30天
WHERE date_sub(curdate(), interval 30 days) <= date(时间字段名)
# 本月
WHERE date_format(时间字段名, '%Y%m') = date_format(CURDATE(), '%Y%m')
# 上一月
WHERE date_format(时间字段名, '%Y%m') = date_format(date_sub(curdate(), interval 1 month), '%Y%m')
WHERE PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(时间字段名, '%Y%m')) = 1
# 本季度
WHERE QUARTER(时间字段名) = QUARTER(now())
# 上季度
WHERE QUARTER(时间字段名) = QUARTER(date_sub(now(), interval 1 QUARTER))
# 本年
WHERE YEAR(时间字段名) =  YEAR(now())
# 上年
WHERE YEAR(时间字段名) = YEAR(date_sub(now(), interval 1 year))
# 本周
WHERE YEARWEEK(date_format(时间字段名, '%Y-%m-%d')) = YEARWEEK(now())
# 上周
WHERE YEARWEEK(date_format(时间字段名, '%Y-%m-%d')) = YEARWEEK(now()) - 1

CASE 自定义列名

CASE (WHEN 条件 THEN … ELSE … END 列名)

SELECT
    CASE
      WHEN age < 25 OR age IS NULL THEN '25岁以下'
      ELSE '25岁及以上'
    END age_cut,
    COUNT(*) number
FROM
    tableA

计算用户的平均次日留存率

SELECT
  count(date2) / count(date1) as avg_ret
FROM (
    select
        distinct qpd.device_id,
        qpd.date as date1,
        uniq_id_date.date as date2
    from
        question_practice_detail as qpd
    left join(
        select distinct device_id,
        date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id=uniq_id_date.device_id
    and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date