Oracle×óÓÒÈ«Á¬½Ó×ܽá
--½¨Á¢²âÊÔÊý¾Ý
create table a(id number);
create table b(id number);
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into b values(1);
insert into b values(2);
insert into b values(4);
commit;
--×ó:
--Ö÷Á÷Êý¾Ý¿âͨÓõķ½·¨
select * from a left join b on a.id=b.id;
--OracleÌØÓеķ½·¨
select * from a, b where a.id=b.id(+);
ID ID
---------- ----------
1 1
2 2
3
--ÓÒ£º
--Ö÷Á÷Êý¾Ý¿âͨÓõķ½·¨
select * from a right join b on a.id=b.id;
--OracleÌØÓеķ½·¨
select * from a, b where a.id(+)=b.id;
ID ID
---------- ----------
1 1
2 2
4
--ÄÚ
--Ö÷Á÷Êý¾Ý¿âͨÓõķ½·¨
select * from a join b on a.id=b.id;
--where¹ØÁª
select * from a, b where a.id=b.id;
ID ID
---------- ----------
1 1
2 2
--È«Íâ
--Ö÷Á÷Êý¾Ý¿âͨÓõķ½·¨
select * from a full join b on a.id=b.id;
--OracleÌØÓеķ½·¨
select *
from a, b
where a.id = b.id(+)
union
select *
from a, b
where a.id(+) = b.id;
ID ID
---------- ----------
1 1
2 2
3
4
--ÍêÈ«£¬Ò²½Ð½»²æÁ¬½Ó»òÕߵѿ¨¶û»ý
--Ö÷Á÷Êý¾Ý¿âͨÓõķ½·¨
select * from a,b;
--»òÕß
select * from a cross join b;
ID ID
---------- ----------
1 1
1 2
1 4
2 1
2 2
2 4
3 1
3 2
3 4
Á¬½ÓÎÞ·ÇÊÇÕ⼸¸ö
--ÄÚÁ¬½ÓºÍwhereÏàͬ
inner join
--×óÏòÍâÁ¬½Ó£¬·µ»Ø×ó±ß±íËùÓзûºÏÌõ¼þµÄ
left join
--ÓÒÏòÍâÁ¬½Ó£¬·µ»ØÓұ߱íËùÓзûºÏÌõ¼þµÄ
right join
--ÍêÕûÍⲿÁ¬½Ó£¬×óÏòÍâÁ¬½ÓºÍÓÒÏòÍâÁ¬½ÓµÄºÏ¼¯
full join
--½»²æÁ¬½Ó£¬Ò²³ÆµÑ¿¨¶ù»ý¡£·µ»Ø×ó±íÖеÄÿһÐÐÓëÓÒ±íÖÐËùÓÐÐеÄ×éºÏ
cross join
--²¹³ä£º
--×óÏòÍâÁ¬½Ó£¬·µ»Ø×ó±ß±íËùÓзûºÏÌõ¼þµÄ£¬
--×¢ÒâÕâÀïûÓеڶþ¸ö¼ÓºÅ£¬»áÖ±½Ó¹ýÂ˵ôÊý¾Ý£¬Ö»ÏÔʾ·ûºÏÌõ¼þµÄ¼Ç¼
select *
from a, b
where a.id = b.id(+)
and b.id = 2;
ID ID
---------- ----------
2 2
--×óÏòÍâÁ¬½Ó£¬·µ»Ø×ó±ß±íËùÓзûºÏÌõ¼þµÄ
--×¢ÒâwhereÉϵڶþ¸ö¼ÓºÅ£¬ËüµÄ×÷ÓÃÊÇÐÞ¸ÄÓұ߱í¼Ç¼µÄÏÔʾ£¬ÀýÈçÈç¹ûb.id(+) = 2£¬ÏÔʾΪ2£¬·ñÔòÏÔʾnull
select *
from a, b
where a.id = b.id(+)
and b.id(+) = 2;
ID ID
---------- --
Ïà¹ØÎĵµ£º
ÏÂÃæ°´Àà±ðÁгöһЩORACLEÓû§³£ÓÃÊý¾Ý×ÖµäµÄ²éѯʹÓ÷½·¨¡£
Ò»¡¢Óû§
²é¿´µ±Ç°Óû§µÄȱʡ±í¿Õ¼ä
SQL>select username,default_tablespace from user_users;
²é¿´µ±Ç°Óû§µÄ½ÇÉ«
SQL ......
trunc()º¯ÊýÓÐÁ½ÖÖÓ÷¨ 1£ººóÃæ¸úÈÕÆÚ 2£º ºóÃæ¸úÊý×Ö
A£º SELECT a.times,to_date(a.times,'yyyymmdd'),trunc(to_date(a.times,'yyyymmdd'),'month') from dmf_loan_limit a
ÏÔʾµÄ½á¹ûΪ£º
1 20080131 2008-1-31 2008-1-1
2 20080131 &nb ......
Èç¹ûÊý¾ÝÎļþË𻵣¬»áÓöµ½1157 ºÍ 1110µÄ´íÎó¡£ÕâʱºòÐèÒª½éÖʻָ´Êý¾Ý¿â¡£
»Ö¸´Êý¾Ý¿âÐèÒªÓÐÏàÓ¦µÄ±¸·ÝºÍ±¸·ÝÒÔÀ´²úÉúµÄËùÓеĹ鵵ÈÕÖ¾ÎļþºÍÁª»úÈÕÖ¾Îļþ¡£
Èç¹ûÔÚmountµ½openµÄʱºò·¢ÏÖÆÕͨµÄÊý¾ÝÎļþË𻵣¬Êý¾Ý¿â»áÍ£ÁôÔÚmount״̬Ï£¬Õâʱºò¿ÉÒÔÖ´ÐÐalter database datafile x offline drop ºÍalter database open à ......
ÅäÖÃÐÅÏ¢Ó¦¸Ã¶¼Ã»ÓÐ´í£¬Çý¶¯Ò²·´¸´¼ÓÔØÁË£¬Ã»ÓÐÎÊÌâ
ÔËÐÐMyeclipse±àÒë¿É¹ý£¬ÔÚÍøÒ³²âÊÔʱ³öÏÖÈçÏ´íÎó£º
java.sql.SQLException: µ÷ÓÃÖÐÎÞЧµÄ²ÎÊý
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError ......