sql常见操作

1 拼接

1 union ,union all

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

1
2
3
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

1
2
3
4
5
6
7
8
#多个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
1
2
3
4
5
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

1
2
3
4
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

1
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

两种形式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--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. 查询活跃业务

1
2
3
4
5
6
7
8
# 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。

1
2
3
4
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

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

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

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

distinct , count 一起用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
##建表
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/

1
2
3
4
5
6
7
8
9
10
11
12
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 :

1
2
3
SELECT UserID,ManagerID,Name,Name AS ManagerName
FROM dbo.Employee
WHERE ManagerID=-1
userid managerid name managername
1 -1 boss boss

R1:

1
2
3
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
1
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 常数

1
2
3
4
5
6
7
8
9
10
11
12
13
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


:D 一言句子获取中...