数据开发sql常见函数大全

🐼 sql常见函数用法大全

先观看视频可以更好的理解手册 视频

关注公众号：”大数据领航员“领取PDF版本和大数据面试面经

打死也要记住法则1：col

打死也要记住法则2：select

SELECT col,col,col 找什么？

FROM table 从哪找？

WHERE col 条件 条件是啥？

条件：数字(where)

当查找条件col是数字

select * from table where col = 1;

Operator	Condition	SQL Example	解释
=, !=, < ,<=, >, >=	Standard numerical operators	col != 4	等于 大于 小于
BETWEEN … AND …	Number is within range of two values (inclusive)	col BETWEEN 1.5 AND 10.5	在 X 和 X之间
NOT BETWEEN … AND …	Number is not within range of two values (inclusive)	co NOT BETWEEN 1 AND10	不在 X 和 X之间
IN (…)	Number exists in a list	col IN (2, 4, 6)	在 X 集合
NOT IN (…)	Number does not exist in a list	col NOT IN (1, 3, 5)	不在 X 集合

条件：文本(where)

当查找条件col是文本

select * from table where col like ‘%jin’;

Operator	Condition	SQL Example	解释
=	Case sensitive exact string comparison (notice the single equals)	col = “abc”	等于
!= or <>	Case sensitive exact string inequality comparison	col != “abcd”	不等于
LIKE	Case insensitive exact string comparison	col LIKE “ABC”	等于
NOT LIKE	Case insensitive exact string inequality comparison	col NOT LIKE “ABCD”	不等于
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)	col LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”)	模糊匹配
_	Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)	col LIKE “AN_” (matches “AND”, but not “AN”)	模糊匹配单字符
IN (…)	String exists in a list	col IN (“A”, “B”, “C”)	在集合
NOT IN (…)	String does not exist in a list	co NOT IN (“D”, “E”, “F”)	不在集合

排序(rows)

需要对结果rows排序和筛选部分rows

select * from table where col > 1 order by col asc limit 2 offset 2

Operator	Condition	SQL Example	解释
ORDER BY	.	ORDER BY col ASC/DESC	按col排序
ASC	.	ORDER BY col ASC/DESC	升序
DESC	.	ORDER BY col ASC/DESC	降序
LIMIT OFFSET	.	LIMIT num_limit OFFSET num_offset	从offset取limit
ORDER BY	.	ORDER BY col1 ASC,col2 DESC	多列排序

join:连表(table)

当查找的数据在多张关联table里

select * from table1 left join table2 on table1.id = table2.id where col > 1

Operator	Condition	SQL Example	解释
JOIN … ON …	.	t1 JOIN t2 ON t1.id = t2.id	按ID连成1个表
INNER JOIN	.	t1 INNER JOIN t2 ON t1.id = t2.id	只保留id相等的row
LEFT JOIN	.	t1 LEFT JOIN t2 ON t1.id = t2.id	保留t1的所有row
RIGHT JOIN	.	t1 RIGHT JOIN t2 ON t1.id = t2.id	保留t2的所有row
IS/IS NOT NULL	.	col IS/IS NOT NULL	col是不是为null

算式(select / where)

当需要对select的col 或 where条件的col 经过一定计算后才能使用

select ,col2 from table where col/2 > 1

Operator	Condition	SQL Example	解释
+ - * / %	.	col1 + col2	col加减乘除
substr	.	substr(col,0,4)	字符串截取
AS	.	col * 2 AS col_new	col取别名
…			还有很多

统计（select）

对查找的rows需要按col分组统计的情况

select count(*),avg(col),col from table where col > 1 group by col

Operator	Condition	SQL Example	解释
COUNT(*), COUNT(column)	A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.	count(col)	计数
MIN(column)	Finds the smallest numerical value in the specified column for all rows in the group.	min(col)	最小
MAX(column)	Finds the largest numerical value in the specified column for all rows in the group.	max(col)	最大
AVG(column)	Finds the average numerical value in the specified column for all rows in the group.	avg(col)	平均
SUM(column)	Finds the sum of all numerical values in the specified column for the rows in the group.	sum(col)	求和
GROUP BY	.	group by col,col2	分组
HAVING	.	HAVING col>100	分组后条件

子表 (table)

一次select的结果rows作为下一次select的临时table才能得到最终结果

select * from (select * from table where col > 1) as tmp where col < 1

Operator	Condition	SQL Example	解释
（select -）as tmp		（select -）as tmp	select结果做子表
in（select -）		in（select -）	select结果做条件
avg（select -）		avg（select -）	select结果做条件