Mysql的语法及相关题型

zhou_145

已于 2024-04-03 00:29:54 修改

阅读量76

点赞数

文章标签： mysql 数据库 sql

于 2023-05-18 10:50:04 首次发布

本文链接：https://blog.csdn.net/zhou_145/article/details/130741979

版权

表连接

输入：
Users 表:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2018-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2018-08-04 | 1       | 4        | 2         |
| 5        | 2018-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
输出：
+-----------+------------+----------------+
| buyer_id  | join_date  | orders_in_2019 |
+-----------+------------+----------------+
| 1         | 2018-01-01 | 1              |
| 2         | 2018-02-09 | 2              |
| 3         | 2018-01-19 | 0              |
| 4         | 2018-05-21 | 0              |
+-----------+------------+----------------+

select Users.user_id as buyer_id,Users.join_date as join_date,count(Orders.buyer_id) as orders_in_2019
from Users left join Orders
on Users.user_id=Orders.buyer_id
and order_date >='2019-01-01'    #此处也可以使用year(order_date)='2019'
group by user_id

这里有一个地方需要注意的是如果是使用left join等连接的时候，如果是加条件需要使用on，因为on是在连接构造临时表时执行的，不管on中条件是否成立都会返回主表（也就是left join左边的表）的内容，where是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。如果这里用的是 where year(order_date)='2019' 那么得到的结果将会把不满足条件的user_id为3，4的行给删掉。用on的话会保留user_id为3，4的行。

IF语句、SUM语句、count语句、LEFT

输入：
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

# Write your MySQL query statement below
select left(trans_date,7) as month,
country,count(state) as trans_count,
count(if(state='approved',1,null)) as approved_count,  #细节，count（）统计一切非null的元素
sum(amount) as trans_total_amount,
sum(if(state='approved', amount,0)) as approved_total_amount
from Transactions
group by month,country

细节一：日期转化，可以使用LEFT进行截取,也可以使用DATE_FORMAT()函数进行截取。
数据表中的 trans_date 是精确到日，我们可以使用 DATE_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01
细节二：IF语句
IF，IF(expr,v1,v2)满足表达式为v1，不满足表达式为v2.
细节三：count语句count()语句为统计一切不为null的行数
细节四：SUM语句
SUM()表示满足条件的列中的总和

自连接

select a.person_name 
from queue a,queue b
where a.turn>=b.turn
group by a.person_id
having SUM(b.weight)<=1000
order by a.turn
desc limit 1;

自连接可以产生多个组合，通过where的条件进行过滤，筛选的条件还有SUM(b.weight)<=1000
此处的条件也可以筛选出b的个数，此时可以筛选出的b的大小为1，2，3，而结果如下图所示。
查询条件 a.turn >= b.turn 找到所有在他之前以及他自己的重量，目的是找b。
再使用 SUM()计算总和并过滤掉大于 1000 的数据,最后只取最后的一个a.turn，group by 
函数是为了按照每一个人进行分组，如果不分组的话，会导致不能进行分组，得到的是一团东西。

在这里插入图片描述

左连接和右连接

在这里插入图片描述
执行左连接的时候

只影响右表中的内容，结构集为左表的内容+右表中与左表相匹配的内容。

也就是表明左链接是以关键字左边的表为主，在加上右表与左表相匹配的内容。

第一种：on：关联两张表，然后进行匹配。
在这里插入图片描述

第二种：on…where：进行匹配后然后进行条件查询，只显示查询到的。

在这里插入图片描述第三种：on…and：显示左表的内容+匹配时的条件为双层匹配

这里其实和前面说的一样，就是说使用where（）过滤的时候会直接去掉，而on...and的组合会保留

group by 函数、round（）函数、avg（）函数

题号为：查询结果的质量和占比
select 
    query_name,
    round(avg(rating/position),2) as quality,
    round(avg(rating<3)*100,2) as poor_query_percentage
from Queries
group by query_name

其中group by函数的目的是将需要分组的query_name进行分类，其中可以分为dog和cat两种类型
而round()函数可以将其进行小数点的处理,round(num,k)表示将其取多少位的小数，
而同时avg函数可以多列的数据进行平均数的取值，其中一共有多少行就除多少。

找出和为最大值的方法ALL

select seller_id from 
  Sales group by seller_id
  having sum(price) = (select sum(price) from Sales group by seller_id order by sum(price) desc limit 1)

可以通过以上的手法进行最大值的寻找，当然也可以使用ALL（）函数，ALL函数的目的找出所有满足sum(price)>=ALL()的条件的情况，即找出后面的条件的最大值

  select seller_id from 
  Sales group by seller_id
  having sum(price) >= all(select sum(price) from Sales group by seller_id )

好像从运行的情况来看，all（）函数的使用使得运行更快了

UNION和UNION ALL的区别

对于数据库
在这里插入图片描述
最后的结果是

在这里插入图片描述

最后的结论就是说Union的结果是会对最后的结果进行去重，如果结果中有重复的部分就进行去重，如果希望保留

在这里插入图片描述 带有where筛选条件的UNION ALL

窗口函数

https://zhuanlan.zhihu.com/p/629460362

2、组内排序，涉及排序窗口函数，这里有3个，分别是rank、dense_rank、row_number，
顺便解释一下他们的区别

    rank：值相等时会重复，会产生空位，比如某列有13，12，12，11这4个数
    ，用rank排序该列时，排序结果为1、2、2、4
    dense_rank：值相等时会重复，不会产生空位，还是上面的例子，用dense_rank排序该列时
    ，排序结果为1、2、2、3
    row_number：值相等时不会重复，不会产生空位，还是上面的例子，用row_number排序该列
    时，排序结果为1、2、3、4