1、oracle append有什么作用?
insert /*+append*/ into table1 select * from table2
在使用了append选项以后,insert数据会直接加到表的最后面,而不会在表的空闲块中插入数据。
使用append会增加数据插入的速度。
/*+APPEND*/的作用是在表的高水位上分配空间,不再使用表的extent中的空余空间
append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo
不去寻找 freelist 中的free block , 直接在table HWM 上面加入数据。
2、不等连接的查询
外连接的限制
2.1:只能在连接的一端使用外连接操作符
error:
select p.name,pt.name from product p,product_type pt where p.product_type_id(+) = pt.product_type_id(+);
right:
select p.name,pt.name from product p,product_type pt where p.product_type_id(+) = pt.product_type_id;
right:
select p.name,pt.name from product p,product_type pt where p.product_type_id = pt.product_type_id(+);
2.2:不能同时使用外连接条件和IN操作符
error:
select p.name,pt.name from product p,product_type pt where p.product_type_id(+) in (1,2,3,4);
2.3:不能同时使用外连接条件和另一个使用OR操作符连接条件
error:
select p.name,pt.name from product p,product_type pt where p.product_type_id(+) = pt.product_type_id or p.product_type_id = 1;
3、使用USING关键字简化连接
3.1:使用USING必须满足
1、查义必须是等连接的。
2、等连接中的列名必须相同。
eg:用USING代替ON
select p.name,pt.name from product p INNER JOIN product_type pt USING(product_type_id);
此时如果想查看product_type_id的值,则
select p.name,pt.name, product_type_id from product p INNER JOIN product_type pt USING(product_type_id);
如果试图在列前面加表别名,则会出错。
另外,在USING子句中也只能单独使用列名
eg:
select p.name,pt.name from product p INNER JOIN product_type pt USING(product_type_id);
如果像这样
select p.name,pt.name from product p INNER JOIN product_type pt USING(p.product_type_id);
或
select p.name,pt.name from product p INNER JOIN product_type pt USING(pt.product_type_id);
都会报错。
4、GROUP BY,HAVING,聚合函数的使用
4.1:如果查询中包含一个聚合函数,而所选择的列并不在聚合函数中,那么这些列就必须在GROUP BY子句中,否则会出错。
error:
select product_type_id,avg(price) from product;
right:
select product_type_id,avg(price) from product group by product_type_id ;
4.2:不能在WHERE 子句中使用聚合函数来限制。
error:
select product_type_id,avg(price) from product where avg(price)>20 group by product_type_id;
right:
select product_type_id,avg(price) from product group by product_type_id having avg(price)>20;
/** GROUP BY可以不与HAVING子句一起使用,但是HAVING必须与GROUP BY子句一起使用 */
4.3:组合使用WHERE和GROUP BY子句
select product_type_id,avg(price) from product where price > 15 group by product_type_id order by product_type_id;
4.4:组合使用WHERE,GROUP BY和HAVING子句
select product_type_id,avg(price) from product where price > 15 group by product_type_id having avg(price)>13 order by product_type_id;
5、日期函数
这里主要讲一下平时用得少的,用得多的大家都非常熟悉了,不用讲了
5.1:LAST_DAY函数用于计算包含x的月的最后一天,样子如:LAST_DAY(DATE)
eg:
select last_day(sysdate) from dual;
or
select last_day(to_date('2011-11-15','yyyy-MM-dd')) from dual;
5.2:NEXT_DAY(x,day)用于计算从x开始,下一个day的日期,参数day是一个文本字符串,样子如:NEXT_DAY(DATE,VARCHAR2)
eg:
select next_day(sysdate,'星期二') from dual;
也可以这样,因为星期是从星期日到星期一,这样就是从1---7
select next_day(sysdate,2) from dual;
or
select next_day(sysdate,7) from dual;
5.3:ROUND函数,ROUND(x[,unit])用于对x取整,默认情况下,x取整为最近的一天,unit为可选,如yyyy,mm
eg:
select round(to_date('2011-11-15'),'yyyy') from dual;-----2012-01-01
select round(to_date('2011-11-15'),'mm') from dual;-----2011-12-01
select to_char(round(to_date('2011-11-15 21:26:32','yyyy-mm-dd hh24:mi:ss'),'hh24'),'yyyy-mm-dd hh24:mi:ss') from dual;---2011-11-15 21:00:00
5.4:TRUNC函数,TRUNC(x[,unit])用于对x截断,默认情况下,x被截断为当天的开始时间.unit为可选,它指明要截断的单元,如yyyy,mm
eg:
select trunc(to_date('2011-11-15'),'yyyy') from dual;------2011-01-01
select trunc(to_date('2011-11-15'),'mm') from dual;------2011-11-01
select to_char(round(to_date('2011-11-15 21:33:32','yyyy-mm-dd hh24:mi:ss'),'hh24'),'yyyy-mm-dd hh24:mi:ss') from dual;---2011-11-15 21:00:00