最近在复习mysql相关的语句,总结了mysql相关的知识点。
高频 SQL 50 题(基础版) - 学习计划 - 力扣(LeetCode)全球极客挚爱的技术成长平台
高频 SQL 50 题(进阶版) - 学习计划 - 力扣(LeetCode)全球极客挚爱的技术成长平台
一、查询
1、单表条件查询
select * from table where 条件1 逻辑符号 条件2
有多个条件时,用逻辑符号连接,如果是并的关系,用and,如果是或的关系,用or
如果表中对象无数据,用到语句is null
“is” 和 “=”的用法
is 可用于测试布尔值
判断字段是否为空值,is null
判断值是否相等,用“=”
字符串长度函数:length
length(列名)=num
某列字符串长度为num
条件关键字
where
having
where和having都是条件查询关键字,两者有什么区别呢?
分组后查询,group by后面,不能用where,只能用having
力扣上有道题
力扣https://leetcode.cn/problems/customers-who-never-order/submissions/
笔者记错了,以为左连接后面的查询语句必须要用having(其实是分组后只能用having),在开始的时候我的sql语句是这样写的:
运行时提示:Unknown column 'O.Id' in 'having clause'
这句话出问题了,于是乎,就select时将O.id也一并取出来
select C.Name as Customers from
Customers as C
left join
Orders as O
on C.Id = O.CustomerId
having O.Id is null
这样就不报错了,但是题目要求取出的表只有一列,我的写法多了ID这一列,怎么处理呢?
select C.Name as Customers,O.Id from
Customers as C
left join
Orders as O
on C.Id = O.CustomerId
having O.Id is null
于是笔者想到子查询,在查询结果上再写一层查询,取出Customers这一列,顺利运行通过,问题出在哪呢?
select T.Customers as Customers from
(select C.Name as Customers,O.Id from
Customers as C
left join
Orders as O
on C.Id = O.CustomerId
having O.Id is null) as T
笔者又发现,当用where O.Id is null时,可以运行通过
select C.Name as Customers from
Customers as C
left join
Orders as O
on C.Id = O.CustomerId
where O.CustomerId is null
那么接下来比较下where和having
where的数据是在结果返回之前起作用的,不需要在select查询的字段里过滤。
而having是在结果返回之后起作用的,having是对查询结果的过滤作用,当没有select出对应的列时,自然无法去过滤。
扩展:
having可使用聚合函数,而where不可以,举例:
力扣https://leetcode.cn/problems/customers-who-bought-products-a-and-b-but-not-c/submissions/笔者的解法,先进行左连接查询,再进行分组,筛选出符合条件的购买A和B,但没有购买C的客户ID,这里就只能用having,不能用where(两个原因:1、group后只能用having进行过滤,2、having能使用聚合函数,但where不能用聚合函数)
select C.customer_id,C.customer_name from
Customers as C
left join
Orders as O
on C.customer_id = O.customer_id
group by O.customer_id
having
sum(O.product_name="A")>0
and
sum(O.product_name="B")>0
and
sum(O.product_name="C")=0;
on
如果是多条件查询时,用到了内连接、左连接、右连接,连接条件用on
字符串的长度用到的函数时length,比如length(str1),可得到字符串str1的长度
处理查询结果需要用到分组和排序
group by + 列名
语句的作用是合并该列的重复项
order by +列名
对某列的数据进行排序,默认按升序,后面加desc则按降序
not like "str%"
查询不以某某字符串开头
union
使用要求,select语句必须有相同数量的列
union合并多个select语句执行结果,去除重复行
union all 不会去重,合并所有的执行结果
if语句的使用
if (条件,条件成立返回的值,条件不成立返回的值)
力扣上的一道典型题:
力扣https://leetcode.cn/problems/calculate-special-bonus/submissions/笔者开始给出的解法,非常麻烦,写了两次查询,将两次查询结果用union结合起来
先筛选出ID是奇数并且name不以M开头的(这里需要注意,不同于python语言,mysql中的判断"="就是用等于符号,没有"=="的用法)
再筛选出ID是偶数,或者name以M开头的
能不能在第一次查询时就将两种情况处理了呢?如果不满足(ID是奇数并且name不以M开头),就走另外的条件,只有这两种情况,想到if语句。
select employee_id,salary as bonus from Employees
where employee_id % 2 != 0
and name not like "M%"
union
select employee_id,salary=0 from Employees
where employee_id % 2 = 0
or name like "M%"
order by employee_id
用If语句,可以顺利通过。
select employee_id,
if(employee_id%2!=0 and name not like "M%",salary,0) as bonus
from Employees
order by
employee_id
parttiton by
分区
rank over
按顺序排序
这道题目用到了分区和排序
力扣https://leetcode.cn/problems/highest-grade-for-each-student/解法如下
1、先通过学生ID进行分组,先按成绩排序,再按课程ID排序
2、再取得每个学生ID下,刚排出的最高的课程和分数
3、取上一步中的第一个
4、最后按学生ID进行排序
select student_id,course_id,grade from
(select *,
rank ()over (PARTITION BY student_id order by grade desc,course_id) as rank_result
from Enrollments) as t
where t.rank_result = 1
order by student_id
牛客上的一道题:
这道题综合了条件筛选、分组筛选、排序的考察。
select left(event_time,7) as month,sum(total_amount) as GMV from tb_order_overall
where total_amount>0 and left(event_time,4)="2021"
group by left(event_time,7)
having sum(total_amount)>100000
order by sum(total_amount)
这道题在截取字符串长度时,用到了left(列名,从左到右需要截取的长度)---这个函数
这里,截取的方法也可以用以下方法:
date_format(time,'%Y-%m')
时间格式筛选
2021-10-01 10:00:00
筛选出年和月:2021-10
扩展:仅取查出的前几个字符串怎么取?
lelf(name,number)
从取出的name里从左到右取出几个字符
上面时间筛选,可写成:left(time,7)
还可以用到:
substring(str, pos, length)
(被截取字符串,从第几位开始截取,截取长度)
从右开始截取:
right(str,length)
(被截取的字符串,截取长度)
Mysql如何截取字符串&获取指定字符串中的数据_mysql 截取指定字符串之后的数据_꧁ᝰ苏苏ᝰ꧂的博客-CSDN博客
2、多表连接查询-内连接、左连接、右连接
多表查询对应三种不同的情况
第一种情况:表1和表2的值是一一对应的
第二种情况:表1里的值,表2里没有
第三种情况:表2里有的值,表1里没有
同样,多表连接查询也有三种方法
第一种:内连接,无论以上三种情况的哪一种,都可以取出两表的交集
第二种:左连接,针对第二种情况,表1里没有的值,补充null空值
第三种:右连接,针对第三种情况,表2里没有的值,补充null空值
左连接查询
公式:
select * from
table 1 as 别名(可以起别名 也可以不起,as可省略)
left join
table 2 as 别名
on where 条件(连接条件)
内连接用到inner join
右连接用到right join
举个例子
table1
id | name |
1 | LiMing |
2 | LiuHua |
3 | WangMing |
table2
num | age |
1 | 10 |
2 | 20 |
连接关系:table1.id=table2.num
左连接sql语句:select * from table1 left join table2 on table1.id=table2.num
左连接得到的新表如下,在table2中没有的值,补充null,对象为空
id | name | num | age |
1 | LiMing | 1 | 10 |
2 | LiuHua | 2 | 20 |
3 | WangMing | null | null |
还是这两个表,用右连接来连接(把表1和表2位置换一下)
右连接sql语句:select * from table2 right join table1 on table2.num=table1.id
右连接得到的新表如下,在table1中没有的值,补充null,对象为空
num | age | id | name |
1 | 10 | 1 | LiMing |
2 | 20 | 2 | LiuHua |
null | null | 3 | WangMing |
如果是用内连接
内连接sql语句:select * from table1 inner join table2 on table1.id=table2.num
内连接得到的新表如下,table1和table2中都有值的才会出现在新表中。
num | age | id | name |
1 | 10 | 1 | LiMing |
2 | 20 | 2 | LiuHua |
未完待续……