sql常见操作

1 拼接

1 union ，union all

https://www.w3school.com.cn/sql/sql_union.asp

select 'mobile' as platform union
select 'desktop' as platform union
select 'both' as platform 

2 join

left join 、right join、 inner join，FULL OUTER JOIN，默认join 为 inner join

https://www.cnblogs.com/ingstyle/p/4368064.html

#多个left join
select a.*,b.*,c.* 
from a left join b on a.id=b.id 
left join c on b.id=c.id

select *
from Trips T left join  Users  U1 on T.client_id =U1.users_id  
left join  Users U2 on T.driver_id  =U2.users_id  

from Trips T1
left join Users U1 on (T1.client_id =U1.users_id  and U1.banned ="Yes" )    不可

from Trips T1
join Users U1 on (T1.client_id =U1.users_id  and U1.banned ="Yes" ) 可

3.from student A,student B,student C

将三个 student 表相互连接成一个

https://blog.csdn.net/zhangyj_315/article/details/2249209

2 分组

1 group by

1.group by columns1,columns2

按照字段分组,多个字段看成整体， 分完每组就一行，取每组第一行数据

2.+条件判断

Having

having 子句的作用是筛选满足条件的组，不是在组内选行

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

3.注意

有时候不能取第一行，怎么解决？1070. 产品销售分析 III

a 可以先排序，把目标行变成第一行 可以参考https://blog.csdn.net/shiyong1949/article/details/78482737 好像不行

b 使用开窗函数解决，可以

4 子查询

嵌套

http://c.biancheng.net/sql/sub-query.html

select a.Score as Score,
        (select count(DISTINCT b.Score) from Scores  b where b.Score >= a.Score) as 'Rank'
from Scores a
order by a.Score DESC

5 别名

as后加别名，也可不要as

6 模糊匹配

关键字like

通配符

https://www.cnblogs.com/Leophen/p/11397621.html

select * from table where name like "%三%"

7 条件

1.IF

表达式：IF( condition, valua1, value2 )

条件为true，则值是valua1，条件为false，值就是value2

2 case

input几行output几行

一行一行来

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

两种形式：

--type1
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END
--type2
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END

then后面多个值

then 1 可

then (1,1) 不可

where xx in （1,1）可

3 where

1126. 查询活跃业务

# Write your MySQL query statement below
select t.business_id 
from 
(select *, avg(E1.occurences  ) over(partition by E1.event_type ) as ave_occu
from Events E1 ) t
where t.occurences > t.ave_occu
group by t.business_id
having    count(t.business_id)>=2

4 判断

1 in

(E1.id , E1.month) in ((1,8))

2 BETWEEN AND

BETWEEN ‘2019-06-28’ AND ‘2019-07-27’

3 EXISTS

用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

8 NULL

1.空字符和null的区别

https://blog.csdn.net/weixin_42214393/article/details/80463912

2.判断NULL

is not NULL

!=NULL 有问题

ifnull(sum(quantity), 0)

3 和 in、not in结合时

https://blog.csdn.net/qq_22592457/article/details/108024521

1. 使用in时，in后面的括号中忽略null值
2. 使用not in时，如果 not in后面的括号中没有null，正常判断，会查询条件列中符合要求的数据
3. 使用not in时，如果 not in后面的括号中有null，直接返回false，查询结果为空。

9 日期

1 大小判断

available_from < ‘2019-05-23’

datediff(date1,date2)

2 格式转化

DATE_FORMAT()

https://www.w3school.com.cn/sql/func_date_format.asp

10 去重

1 distinct

https://blog.csdn.net/zhangzehai2234/article/details/88361586

select distinct expression[,expression...] from tables [where conditions];

在使用distinct的过程中主要注意一下几点：

• 在对字段进行去重的时候，要保证distinct在所有字段的最前面
• 如果distinct关键字后面有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的才会被去重

distinct , count 一起用

##建表
Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int);
Truncate table Spending;
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'desktop', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-01', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-02', 'mobile', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-01', 'desktop', '100');
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-02', 'desktop', '100');

###查询
select distinct spend_date ,count(user_id ) 
from Spending

##result
##返回结果就一行，distinct后多行，count一行，多行一行还是一行；count结果还是distinct前的数量
spend_date	count(user_id )
2019-07-01	6

11 show status

https://blog.csdn.net/qq_29168493/article/details/79149132

查看当前数据库状态，可以统计当前数据库不同语句的执行频次

12 explain

获取sql执行计划，结果明细参考

https://cloud.tencent.com/developer/article/1093229

14 事务

http://m.biancheng.net/sql/transaction.html

15 递归

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

https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b

https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/

WITH expression_name (column_list)
AS
(
    -- Anchor member
    initial_query  
    UNION ALL
    -- Recursive member that references expression_name.
    recursive_query  
)
-- references expression name
SELECT *
FROM   expression_name

分析 https://zhuanlan.zhihu.com/p/372330656

R0 ：

SELECT UserID,ManagerID,Name,Name AS ManagerName
 FROM dbo.Employee
 WHERE ManagerID=-1

userid	managerid	name	managername
1	-1	boss	boss

R1：

SELECT c.UserID,c.ManagerID,c.Name,p.Name AS ManagerName
FROM CTE P
INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID

此时Employee为完整的，cte为

userid	managerid	name	managername
1	-1	boss	boss

得到结果为

c.userid	c.managerid	c.name	c.managername	p.userid	p.managerid	p.name	p.managername
11	1	A1	A1	1	-1	boss	boss
12	1	A2	A2	1	-1	boss	boss
13	1	A3	A3	1	-1	boss	boss

SELECT c.UserID,c.ManagerID,c.Name,p.Name AS ManagerName

userid	managerid	name	name
11	1	A1	boss
12	1	A2	boss
13	1	A3	boss

R2,R3…

最后union all R0，R1，R2，。。。

16 select 常数

SELECT 1,-1,N'Boss'
UNION  ALL
SELECT 11,1,N'A1'
UNION  ALL
SELECT 12,1,N'A2'
UNION  ALL
SELECT 13,1,N'A3'
UNION  ALL
SELECT 111,11,N'B1'
UNION  ALL
SELECT 112,11,N'B2'
UNION  ALL
SELECT 121,12,N'C1'

1 -1 Boss —字段

1 -1 Boss
11 1 A1
12 1 A2
13 1 A3
111 11 B1
112 11 B2
121 12 C1

17 CTE

Common Table Expression

with …as…

18 触发器

就是做了某些操作，自动触发的行为

触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。

https://blog.csdn.net/weixin_48033173/article/details/111713117