【常用SQL总结，持续更新...】

t田田

已于 2022-05-07 17:35:02 修改

阅读量667

点赞数

文章标签： sql oracle

于 2022-05-05 18:15:27 首次发布

本文链接：https://blog.csdn.net/tian20171018/article/details/124582324

版权

常用SQL，默认为oracle数据库

SQL语法
Mybatis

SQL语法

left join：关键字从左表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为 NULL。
(inner) join：内连接或等值连接: 获取两个表中字段匹配关系的记录，就是根据on后面的关联条件，两张表中都有的数据才会显示。
right join：与left join相反，返回右表所有的行。如果左表没有匹配，则结果为null。
full (outer) join:全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。
(+)：如果是在等号右侧字段的后面加(+)，是左外连接，相当于普通SQL里的left join。where a.id=b.id(+) == a left join b
如果是在等号左侧字段的后面加(+)，是右外连接，相当于普通SQL里的right join。where a.id(+)=b.id == a right join b
Left join on 后面加 and 和最后加where的区别？(inner join时两个结果一样，前者性能差些)
a.多表left join是会生成一张临时表，并返回给用户
b.where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。
c.on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL
d.on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。
NVL（表达式1，表达式2）:如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
NVL2(表达式1，表达式2，表达式3）:如果表达式1为空，返回值为表达式3的值。如果表达式1不为空，返回值为表达式2的值。
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序，Union因为要进行重复值扫描，所以效率低。
Union All：对两个结果集进行并集操作，包括重复行，不进行排序，如果合并没有刻意要删除重复行，那么就使用Union All
Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
两个要联合的SQL语句字段个数必须一样，而且字段类型要“相容”（一致）
可以在最后一个结果集中指定Order by子句改变排序方式。
ceil(value)：产生大于或等于指定值的最小整数,取整,没有四舍五入这一说法。select Ceil(23.66) from dual; --24
floor(value)：产生小于或等于指定值的最小整数。select floor(23.66) from dual; --23
trunc(value,precision)：按精度截取某个数字,不进行舍入操作。select trunc(23.66,1) from dual; --23.6
round(value,precision)：根据给定的精度舍入数值。select round(23.66,1) from dual; --23.7
abs(value)：返回一个数值的绝对值 |x|。select ABS(-3) from dual; --3
sign(value)：与绝对值函数ABS（）相反。sign(value)则给出值的符号而不是量。select sign(-3) from dual; --(-1)
case when then:简单case函数:case sex /when ‘1’ then ‘男’ /when ‘2’ then '女’ /else ‘其他’ end
case搜索函数:case /when sex = ‘1’ then ‘男’ /when sex = ‘2’ then ‘女’ /else ‘其他’ end
这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有些限制，比如写判定式。
还有一个需要注重的问题，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。比如说，下面这段sql，你永远无法得到“第二类”这个结果。
case /when col_1 in (‘a’,‘b’) then ‘第一类’ /when col_1 in (‘a’) then ‘第二类’ /else ‘其他’ end
decode：decode（字段|表达式，条件1，结果1，条件2，结果2，…，缺省值）；–缺省值可以省略只能在select 语句用。
用法：使用decode 判断字符串是否一样。 DECODE(value,if1,then1,if2,then2,if3,then3,…,else)
使用decode比较大小。select decode(sign(var1-var2),-1,var1,var2) from dual；
group by:用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。
having：用来过滤由GROUP BY语句返回的记录集。where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常，你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样。例如：SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2
mod(a, b)：此函数接受两个参数a和b。当输入数字a除以b时，此函数将余数作为输出。
substr(string,start,length)：从一个字符表达式或备注字段中返回一个字符串。该字符串起始于字符表达式或备注字段的指定位置，到指定数目字符结束。
string参数：必选。数据库中需要截取的字段。start参数：必选。正数，从字符串指定位置开始截；负数，从字符串结尾指定位子开始截取；0，在字符串中第一个位子开始截取；1，同理(特殊)。length参数：可选。需要截取的长度。缺省，即截取到结束位置。注意：若必选参数为空，那返回的结果也为空。substr(‘123456’,3,2); 结果是34。
在各个数据库的函数名称略有差异。MySQL–substr()或substring()；Oracle–substr()；SQL Server–substring()；
instr：格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
注：在Oracle/PLSQL中，instr返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。
select instr(‘helloworld’,‘lo’) from dual; --返回结果：4 即“lo”同时出现，第一个字母“l”出现的位置
select instr(‘helloworld’,‘l’,4,2) from dual; --返回结果：9 也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
select instr(‘helloworld’,‘l’,-2,2) from dual; --返回结果：4 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果；如下所示：
MySQL： select * from tableName where name like ‘%helloworld%’;
Oracle：select * from tableName where instr(name,‘helloworld’)>0; --这两条语句的效果是一样的
SQL Server / MS Access 语法:SELECT TOP number|percent column_name(s) FROM table_name;
MySQL 语法:SELECT column_name(s) FROM table_name LIMIT number;
Oracle 语法:SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
REPLACE ( string_expression , string_pattern , string_replacement )
参数含义：
string_expression 要搜索的字符串表达式。string_expression 可以是字符或二进制数据类型。
string_pattern 是要查找的子字符串。string_pattern 可以是字符或二进制数据类型。string_pattern 不能是空字符串 (‘’)。
string_replacement 替换字符串。string_replacement 可以是字符或二进制数据类型。
返回类型：
如果其中的一个输入参数数据类型为 nvarchar，则返回 nvarchar；否则 REPLACE 返回 varchar。
如果任何一个参数为 NULL，则返回 NULL。
最直接、最直白的意思：REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str。
WMSYS.WM_CONCAT():其作用是将一列数据转换成一行，以逗号分隔。
mysql中GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])，默认逗号分隔。
执行顺序
from一＞on一＞loin一＞where一＞group by（开始使用select中的别名，后面的语句中都可以使用别名）->sum、count、max、avg一＞having一＞select一＞distinct一＞orderby一＞Iimit

Mybatis

Mybatis中的 ${ } 和 #{ } 的区别？
动态 SQL 是 mybatis 的强大特性之一，也是它优于其他 ORM 框架的一个重要原因。mybatis 在对 sql 语句进行预编译之前，会对 sql 进行动态解析，解析为一个 BoundSql 对象，也是在此处对动态 SQL 进行处理的。
select * from user where name = #{name};
#{} 在动态解析的时候，会解析成一个参数标记符。就是解析之后的语句是：select * from user where name = ？;
select * from user where name = '${name}';
${}在动态解析的时候，会将我们传入的参数当做String字符串填充到我们的语句中，select * from user where name = “dato”;
#{}表示一个占位符号,主要有以下几点功能：
•通过#{}可以实现preparedStatement向占位符中设置值，自动进行Java类型和jdbc类型转换。
•#{}可以有效的防止SQL注入。
•#{}可以接收键类型值或者pojo属性值。
•如果parameterType传给单个简单类型值，#{}括号中可以是value或其他名称。
•大多情况下，我们取参数的值都应该去使用#{}；
${}表示拼接SQL串：
•通过${}可以将parameterType传入的内容拼接在SQL中而不进行jdbc类型转换。
•可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，${}中只能是value
•MyBatis排序时使用order by 动态参数时需要注意，用${}而不是#{}
•原生jdbc不支持占位符的地方我们就可以使用${}进行取值，比如列名、分表、排序。。。