SQL-常用SQL语句

qq_52631044

已于 2022-09-13 17:02:10 修改

阅读量1.4k

点赞数 1

分类专栏： mysql 文章标签： sql 数据库 mysql

于 2022-08-28 10:00:26 首次发布

本文链接：https://blog.csdn.net/qq_52631044/article/details/126477122

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

关键词及SQL结构

去重数据关键字：distinct

注：关键字distinct只能放在SQL语句的第一个，用在多个字段，只有多个字段完全重复才会去重

select distinct 去重的字段名 from 表名;

用来返回不重复的条数

select count(distinct 去重字段) as 别名 from 表名;

分页或要取数量关键字：limit

select 字段名 from 表名limit 0,2; //从第一条数据开始，取两条数据

offset 关键字表示跳过的数量

select 字段名from 表名limit 2 offset 0 // 跳过0条，从第一条数据开始取，取两条数据

起别名或给显示的结果改名关键字：AS

select 字段名as 显示的名字 from 表名;

某个值到某个值之间关键字：between

select 字段名1,字段名2,字段名3 from 表名where 字段名 between 20 and 23;

select 字段名 from 表名 where 字段名>=20 and 字段名 <=23;

判断某个条件不为空关键字：is not null

select字段名 from 表名where 字段名 is not null;

对一个以上条件进行过滤关键字：and 或 or

select 字段名 from 表名 where 条件1 and 条件2; //两个条件都需要用and

select 字段名 from 表名 where 条件1 or 条件2; //两个条件有一个满足用or

where 子句中有多个值关键字：in

select 字段名 from 表名 where 多个值的字段名 in (value1,value2,value3);

where 子句中不包括多个值关键字：not in

select 字段名 from 表名 where 多个值的字段名 not in (value1,value2,value3);

子查询结构

select 字段id1,字段名2,字段名3 from 表名 where 字段id1 in (select 字段id1 from 表名 where 条件) or 字段id1 in (select 字段id1 from 表名 where 条件);

字符匹配结构：列名[not] like

_：匹配任意一个字符；
SELECT * FROM 学生表 WHERE name LIKE '张__'//查询姓“张”且名字是3个字的学生姓名。
%：匹配0个或多个字符；
SELECT * FROM 学生表 WHERE 姓名 LIKE '张%'//查询学生表中姓‘张’的学生的详细信息。
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达 )；
SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%'//查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。
[^ ]：不匹配[ ]中的任意一个字符。
SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]' //从学生表表中查询学号的最后一位不是2、3、5的学生信息。

取某字段的最大值函数：max(字段名) ，min是最小函数

方法1：用Max（字段名）

select max(字段名) as 别名 from 表名 where 条件;

方法2：用分组(order by) +降序(desc) +分页(limit)1

select 字段名 from 表名 where 条件 order by 字段名 desc limit 1;

计算数量关键函数：count

计算平均数关键函数：avg

select count(数量的字段名) as 别名,round(avg(平均的字段名), 1) as 别名 from 表名 where 条件; //平均数可能有多位小数，用round函数保留一位小数

分组字段的关键字：group by

select 分组字段,聚合函数(数量字段),平均函数(要平均字段) from 表名 group by 分组字段 having 聚合函数条件; //有聚合函数可以不用where，出现关键字“每”，“各”，"不同"可以考虑分组

对结果集进行排序(默认升序)的关键字：order by

select 分组字段,avg(要平均的字段名) as 别名 from 表名 group by 分组字段 order by 别名

//对分组且要对平均数升序

两张表关联查询的两种方法

表连接指明条件的关键字： on

第一种：创建一张临时表

select 字段id,字段2,字段3 from 表名1 where 字段id = (select 字段id from 表名2 where 条件)

第二种：两张表关联，筛选明细信息

select 表1别名.字段id,字段2,字段3 from 表1 别名1 left join 表2 别名2 on 关联关系 where 条件

多表关联查询

select 分组字段1,分组字段2,
round(count(总题数) / count(distinct 总人数),4) as 结果别名
from 表1 别名1
left join 表2 别名2
on 别名1id = 别名2id
left join 表3 别名3
on 别名2id=别名3id
group by 分组字段1,分组字段2;
#计算每个学校用户不同难度下的用户平均答题题目数
select university,difficult_level,
round(count(qpd.question_id) / count(distinct qpd.device_id),4) as avg_answer_cnt
from user_profile up
left join question_practice_detail qpd
on up.device_id = qpd.device_id
left join question_detail qd
on qpd.question_id=qd.question_id
group by university,difficult_level;
注：精度保留四位：round(x,4)

两个条件组合在一起不去重关键字：union all

合并两个或多个select语句的结果集关键字：union

select 字段名 from 表名 where 条件1 union all select 字段名 from 表名 where 条件2

注：union 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

相当于java中的choose 多分支函数：case

select case
when 表达式1 then '结果表达式1'
when 表达式2 then '结果表达式2'

else '结果表达式3'
end as 别名,字段from 表名group by 别名

#判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。
SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级, COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END

日期函数：年：year(date)=xxxx年月：month(date)=xx月日：day(date)=xx日

date_format函数：date_format(date, '%Y-%m')='2021-08';

select distinct(month(日期字段)) as 别名1,
count(数量字段) as 别名2
from 表名 where year(date)=2022 group by 别名1;
#2021年8月每天用户练习题目的数量
select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date)=8 and year(date)=2021
group by date

向日期添加指定间隔的函数：date_add(date,interval 时间间隔 type)

date 参数是合法的日期表达式。type 参数可以是下列值：MySQL DATE_ADD() 函数

分组(我叫分区)的分析函数：partition by

排序区前几的时候用over开窗函数

　select * from (
　　select *,ROW_NUMBER() over(partition by 分区字段 order by 排序字段 desc) 别名
　　from 表名
　　) 别名2
　　where 别名2.别名=1 // 等于1是取第一个，同理里可以取三个、五个等

注：over(partition by)详细可以看：SQL中OVER（PARTITION BY）详解_Li--AiTao的博客-CSDN博客_sql中over

partition by和group by区别可以看：

group by和partition by区别小结_数据孤岛的博客-CSDN博客

访问日期间的空档期用函数：lead(date)

结构：LEAD(col, offset, default)

col - 指你要操作的那一列
offset - 偏移几行，如果是1就是下1行，以此类推
default - 如果下一行不存在，用什么值填充

具体看：＜数据库＞ LEAD函数的用法 Leetcode 1709. 访问日期之间最大的空档期_当代女大学生的博客-CSDN博客_数据库lead

返回两个日期天数的函数：datediff()

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
注：只有日期的部分才参与运算

日期函数可以看

SQL Date 函数

字符串截取函数：substring_index(）

select substring_index(要处理的分组字段,'分割符',计数) 别名1,count(*) 别名2
from 表名 group by 别名1;

注：计数详细看：

【MySQL】字符串截取之substring_index - 梦琪小生 - 博客园

字符串的截取：substring(字符串，起始位置，截取字符数）

根据条件返回不同的值的函数：if(条件表达式,值1,值2)

select if(条件表达式,'值1','值2') 别名1,count(*) 别名2
from 表名 group by 别名1;

注：常用if函数嵌套和if(聚合函数)，详细看：SQL入门之第二一讲——IF函数的使用_henry_rhy的博客-CSDN博客_sql中if函数的使用方法

字符串的切割、截取、删除、替换

select
1. 替换法 replace(字段名, '被替换部分','替换后的结果')
        -- 字段, replace(blog_url,'http:/url/','') as 别名

2. 截取法 substr(字段名, start_point, length*可选参数*)
        -- 字段, substr(blog_url,11,length(blog_url)-10) as 别名

3. 删除法 trim('被删除字段' from 列名)
        -- 字段, trim('http:/url/' from blog_url) as 别名

4.字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
        字段, substring_index(blog_url,'/',-1) as 别名

from 表名;

窗口函数

详细看：通俗易懂的学会：SQL窗口函数 - 知乎

求一列数的总和：sum()

select sum(字段名) from 表名

字符串的拼接：concat()

concat(字符串1，字符串2，字符串3,...) 常用于字段拼接

字母转大写：upper()、ucase()

upper(字段）as 别名

字母转小写：lower()、lcase()

lower(字段) as 别名

提升效率的优化

索引覆盖

索引覆盖是select语句中特殊的联合索引。一个联合索引对某个select语句，通过索引直接获取查询结果，不需要回表查询，就称该联合索引覆盖了这条select语句。

原理：查询字段在二级索引中全部找到，不需要回表查询

device_id,university 为联合索引， SQL可以这样写
Select device_id,university FROM user_profile where university = "北京大学" and device_id = user_profile.device_id;
注：device_id = user_profile.device_id 这一步是100%成立的，使用的目的就是索引覆盖