Oracle语句不详解，还是抄的，笔记

SQL之case when then用法

case具有两种格式。简单case函数和case搜索函数。

--简单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  

nvl

NVL是Oracle PL/SQL中的一个函数。它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

join语法

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

1.SQL中on条件与where条件的区别

       数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

      在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

#{}实现的是向prepareStatement中的预处理语句中设置参数值，sql语句中#{}表示一个占位符即?。 

 

1. <!--根据id查询用户信息 -->  
2. <select id="findUserById"parameterType="int"resultType="user">  
3.     select * from user where id =#{id}  
4. </select>  

2.#{}与${}的区别

使用占位符#{}可以有效防止sql注入，在使用时不需要关心参数值的类型，mybatis会自动进行Java类型和jdbc类型的转换。#{}可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，#{}括号中可以是value或其它名称。说得再通俗一点，当我们使用#{}的时候，发出的sql中，#{}代表的内容会自动被加上“”，而${}是直接把东西取出来直接用举个例子：

id="liweizhong"，#{id}输出后是"liweizhong",而${value}输出是liweizhong

 

${}和#{}不同，通过${}可以将parameterType传入的内容拼接在sql中且不进行jdbc类型转换， ${}可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是value。使用${}不能防止sql注入，但是有时用${}会非常方便，如下的例子：

 

1. <!--根据名称模糊查询用户信息 -->  
2. <select id="selectUserByName"parameterType="string"resultType="user">  
3.     select * from user whereusername like '%${value}%'  
4. </select>  

 

 

如果本例子使用#{}则传入的字符串中必须有%号，而%是人为拼接在参数中，显然有点麻烦，如果采用${}在sql中拼接为%的方式则在调用mapper接口传递参数就方便很多。

如果使用占位符号则必须人为在传参数中加%

List<User> list =userMapper.selectUserByName("%管理员%");

 

如果使用${}原始符号则不用人为在参数中加%，直接在mapper配置文件里面接受这个参数就可以了，显得更加的方便，可是sql注入问题？  

List<User>list = userMapper.selectUserByName("管理员");

 

再比如order by排序，如果将列名通过参数传入sql，根据传的列名进行排序，应该写为：

 

1. ORDER BY ${columnName}  

 

 

这样要执行的sql是：

 

1. ORDER BY columnName  

如果使用#{}将无法实现此功能，因为如果这样的话，执行的sql就变成了

 

1. ORDER BY "columnName" .

insert into 语句的三种写法

 

方式1、 INSERT INTO t1(field1,field2) VALUE(v001,v002);            // 明确只插入一条Value

 

方式2、 INSERT INTO t1(field1,field2) VALUES(v101,v102),(v201,v202),(v301,v302),(v401,v402);

在插入批量数据时方式2优于方式1.

 

方式3.1、  INSERT INTO t2(field1,field2) SELECT col1,col2 FROM t1 WHERE ……

这里简单说一下，由于可以指定插入到talbe2中的列，以及可以通过相对较复杂的查询语句进行数据源获取，可能使用起来会更加的灵活一些，但我们也必须注意，我们在指定目标表的列时，一定要将所有非空列都填上，否则将无法进行数据插入，还有一点比较容易出错的地方就是，当我们写成如下简写格式：

 

方式3.2、  INSERT INTO t2 SELECT id, name, address FROM t1

此时，我们如果略掉了目标表的列的话，则默认会对目标表的全部列进行数据插入，且SELECT后面的列的顺序 必须和目标表中的列的定义顺序完全一致 才能完成正确的数据插入，这是一个很容易被忽略的地方，值得注意。