常用sql

素颜马尾好姑娘i 2022-10-05 14:46 340阅读 0赞

# SELECT语句 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70]

-- 测试完整SELECT 语句的形式
    SELECT addr,
    GROUP_CONCAT(username) AS usersDetail,
    COUNT(*) AS totalUsers,
    SUM(age) AS sum_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age,
    AVG(age) AS avg_age
    FROM user1
    WHERE id>=2
    GROUP BY addr;
    
    SELECT addr,
    GROUP_CONCAT(username) AS usersDetail,
    COUNT(*) AS totalUsers,
    SUM(age) AS sum_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age,
    AVG(age) AS avg_age
    FROM user1
    WHERE id>=2
    GROUP BY addr
    HAVING totalUsers>=2;
    
    
    SELECT addr,
    GROUP_CONCAT(username) AS usersDetail,
    COUNT(*) AS totalUsers,
    SUM(age) AS sum_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age,
    AVG(age) AS avg_age
    FROM user1
    WHERE id>=2
    GROUP BY addr
    HAVING totalUsers>=2
    ORDER BY totalUsers ASC;
    
    SELECT addr,
    GROUP_CONCAT(username) AS usersDetail,
    COUNT(*) AS totalUsers,
    SUM(age) AS sum_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age,
    AVG(age) AS avg_age
    FROM user1
    WHERE id>=2
    GROUP BY addr
    HAVING totalUsers>=2
    ORDER BY totalUsers ASC
    LIMIT 0,2;

# where 条件筛选记录 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 1]

SELECT id,username,age FROM user1
    WHERE id=50;
    
    -- 更新id<=9的用户 userDesc='this is a test'
    UPDATE user1 SET userDesc='this is a test'
    WHERE id<=9;
    
    -- IS [NOT] NULL检测NULL值
    SELECT id,username,age,userDesc FROM user1
    WHERE userDesc IS  NULL;
    -- IS NOT判断不等于null值
    select id ,userDesc from user1
    where userDesc is not null
    
    -- 查询薪水在10000~50000之间的用户
    SELECT id,username,age,salary FROM user1
    WHERE salary BETWEEN 10000 AND 50000;
    
    SELECT id,username,age,salary FROM user1
    WHERE salary NOT BETWEEN 10000 AND 50000;

# group by 分组 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 2]  
分组后可对组内数据做统计、聚合、求最大值、最小值、平均值等

## 1. 分组后，再对组做筛选(GROUP BY+HAVING ) ##

根据addr分组，求组内salary的平均值，过滤掉不满足salary的平均值>=4000的组

-- 要求平均薪水>=40000
    SELECT addr,
    GROUP_CONCAT(username) AS usersDetail,
    COUNT(*) AS totalUsers,
    SUM(salary) AS sum_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    AVG(salary) AS avg_salary
    FROM user1
    GROUP BY addr
    HAVING avg_salary>=40000;

## 2.先过滤掉不满足条件的数据再分组(WHERE+GROUP BY) ##

-- 查询age>=30的用户并且按照sex分组
    SELECT sex,GROUP_CONCAT(username) AS usersDetail,
    COUNT(*) AS totalUsers
    FROM user1
    WHERE age>=30
    GROUP BY sex;

## SQL实现group by 分组后组内排序,取组内topN数据 ##

[SQL实现group by 分组后组内排序][SQL_group by]

select a.Classid,a.English from
    (select Classid,English,row_number() over(partition by Classid order by English desc) as n
    from CJ) a
    where n<=2

[ROW\_NUMBER() OVER()函数用法详解 （分组排序 例子多）][SQL_group by]

## 组内比较(平均值、最大值、最小值) ##

[sql分组后组内排序\_SQL的开窗函数|解决topN、组内排序、移动平均问题][sql_SQL_topN]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 3]

# ORDER BY 排序 #

ASC升序，DESC降序，不写默认为升序

-- 按照id降序排列
    SELECT id,username,age
    FROM user1
    ORDER BY id DESC;
    
    -- 按照age升序
    SELECT id,username,age
    FROM user1
    ORDER BY age ;

-- 按照多个字段排序
    SELECT id,username,age
    FROM user1
    ORDER BY age ASC,id desc;

![在这里插入图片描述][20210609172235590.png]

SELECT id,username,age
    FROM user1
    WHERE age>=30
    ORDER BY age DESC;
    
    SELECT id,username,age
    FROM user1
    where age>=30
    ORDER BY age ASC, RAND();

# LIMIT限制结果集的显示条数 #

![在这里插入图片描述][20210609172545561.png]

SELECT id,username,age,sex
    FROM user1
    LIMIT 5;

![在这里插入图片描述][20210609172700833.png]

SELECT id,username,age
    FROM user1
    LIMIT 2,5

![在这里插入图片描述][20210609172732723.png]  
查询前3条记录，将age+5

SELECT id,username,age+5
    FROM user1
    LIMIT 3

![在这里插入图片描述][20210609172959853.png]  
按照id降序排列，查询前三条记录，将age+10

SELECT id,username,age+10
    FROM user1
    order by id desc
    LIMIT 2,5

![在这里插入图片描述][20210609173535977.png]

# 子查询的使用 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 4]

# 函数 #

## ROW\_NUMBER() ##

[SQL Server中ROW\_NUMBER()函数的使用][SQL Server_ROW_NUMBER]

## mysql中的concat函数,concat\_ws函数，concat\_group函数之间的区别 ##

[mysql中的concat函数,concat\_ws函数，concat\_group函数之间的区别][mysql_concat_concat_ws_concat_group]

## MySQL日期时间函数大全 ##

[MySQL日期时间函数大全][MySQL]

## 求和 sum()over()和count()over()分析函数 ##

[关于mysql中的count()函数][mysql_count]

[sum()over()和count()over()分析函数][sum_over_count_over]

## 字符串截取 ##

[MySQL|SUBSTR() 函数用法][MySQL_SUBSTR_]

## lag&lead(取上次&下次的值) ##

[mysql里 lag() 和 lead() 用法][mysql_ lag_ _ lead_]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70]: /images/20221005/f35dcead9dd747bd8871e8df1dd49b50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 1]: /images/20221005/9bce49963c3b4fa1aea625701109e83d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 2]: /images/20221005/c8f7d66c67634e0bbd75a8719d7c3786.png
[SQL_group by]: https://blog.csdn.net/qq_25221835/article/details/82762416
[sql_SQL_topN]: https://blog.csdn.net/weixin_32431435/article/details/113320589
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 3]: /images/20221005/d6d6ddc9e2ac41819dd72487bd39f655.png
[20210609172235590.png]: /images/20221005/0690491fcbad40fc9c8e98c91ed7a1bc.png
[20210609172545561.png]: /images/20221005/814353bd096f40c0a5f1d8d21067e458.png
[20210609172700833.png]: /images/20221005/4c7276dec0394dbeb31871c96df9bf1f.png
[20210609172732723.png]: /images/20221005/05c47f670f4146439c9e26f8b22f4dc5.png
[20210609172959853.png]: /images/20221005/45dbd0bd7cc141629eebb12b1181bb06.png
[20210609173535977.png]: /images/20221005/0f3fc84985424cb5a51e2c9e9994c239.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 4]: /images/20221005/97392ac8b86a43309599f5a79e13864b.png
[SQL Server_ROW_NUMBER]: https://blog.csdn.net/pan_junbiao/article/details/79941162
[mysql_concat_concat_ws_concat_group]: https://blog.csdn.net/qq_35211818/article/details/78977276
[MySQL]: https://blog.csdn.net/ai_zxc/article/details/50627918
[mysql_count]: https://www.cnblogs.com/-flq/p/10302965.html
[sum_over_count_over]: https://www.bbsmax.com/A/n2d96qvYzD/
[MySQL_SUBSTR_]: https://zhuanlan.zhihu.com/p/110142732
[mysql_ lag_ _ lead_]: https://blog.csdn.net/qq_42363032/article/details/110734500