sql笔记

=和:=区别

=只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。

:=不只在set和update时时赋值的作用，在select也是赋值的作用。

题：求一个表中lat_n对应的中位数，并保留4位有效数

set @row_index:=-1;

select round(avg(lat_n),4) from(
    select @row_index:=@row_index+1 as row_index,lat_n
        from station 
        order by lat_n
    )as subquery
    where subquery.row_index in(floor(@row_index/2),ceil(@row_index/2));

解释：@用于定义变量，:=用于赋值

cte的使用

cte是公用表表达式

on和where的区别

sql语句中的on和where都是表示搜索条件.
在sql中有三种连接查询, cross join，inner join，和outer join
其中 cross join 和 inner join 有on和where的查询结果相同,但是在outer join 中left join 和 right join ,查询结果并不相同.

• on条件是在生成临时表时使用,他不管条件是否为真,都会返回左表的数据.
• where条件则是在临时表生成之后,判断条件,若条件不符合,则删除数据.

在sql语句中执行的顺序,on在where之前先执行.

case when的用法

在SQL中，“Case When”语句用于选择判断，在执行时先对条件进行判断，然后根据判断结果做出相应的操作；语法“CASE 字段 WHEN 条件1 THEN 操作1 WHEN 条件2 THEN 操作2…ELSE 操作n END;”。

select 
    n,
    case
        when p is null then 'Root'
        when n in (select p from bst) then 'Inner'
        else 'Leaf'
    end
from bst
order by n;

join

join等价于inner join内连接，是返回两个表中都有的符合条件的行。

left join左连接，是返回左表中所有的行及右表中符合条件的行。

right join右连接，是返回右表中所有的行及左表中符合条件的行。

full join全连接，是返回左表中所有的行及右表中所有的行，并按条件连接。

通常情况下，left join肯定比inner join返回的行数多。

select 
    case 
        when g.grade>=8 then s.name
        else null
    end as Names,
    grade,
    marks
from students as s
left join grades as g
on marks between min_mark and max_mark
order by grade desc,name

using的使用

using等价于join中的on，使用的条件是：

• 查询必须是等值连接。
• 等值连接中的列必须具有相同的名称和数据类型。

SELECT h.hacker_id,h.name
FROM hackers h
JOIN submissions s USING (hacker_id)
JOIN challenges c USING (challenge_id)
JOIN difficulty d USING (difficulty_level)
WHERE s.score = d.score
GROUP BY s.hacker_id, h.name
HAVING COUNT(s.hacker_id) > 1
ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id ASC

使用的时候要注意表之间连接的顺序。

group concat ()和group by的使用

对于多行的数据在一行显示，可以使用group_concat()和group by进行操作

SELECT 
GROUP_CONCAT( if(occupation='doctor',name,NULL) ) AS 'doctor', 
GROUP_CONCAT( if(occupation='pro',name,NULL) ) AS 'professor', 
GROUP_CONCAT( if(occupation='sin',name,NULL) ) AS 'singer', 
GROUP_CONCAT( if(occupation='actor',name,NULL) ) AS 'actor' 
FROM ( SELECT *, row_number() OVER(PARTITION BY OCCUPATION ORDER BY NAME) AS N FROM OCCUPATIONS ) AS ORD GROUP BY N;

occupation表

对应的查询结果

日期year,month,day的使用

select day(date) as day,count(question_id) as question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=08
group by day(date);