--SQLåºæ¬å½ä»¤
--æ°æ®å®ä¹è¯è¨
Create(å建)
Alter(æ´æ¹)
Drop(å é¤)
--æ°æ®æ纵è¯è¨
Insert(æå
¥)
select(éæ©)
delete(å é¤)
update(æ´æ°)
--äºå¡æ§å¶è¯è¨
commit(æ交)
savepoint(ä¿åç¹)
rollback(åæ»)
--æ°æ®æ§å¶è¯è¨
Grant(ææ)
Revoke(åæ¶)
--Orcaleæ°æ®ç±»å
--å符æ°æ®ç±»å
charè¿ç§æ°æ®ç±»åçåé¿åº¦å¯ä»¥æ¯1å°2000个åèã
varchar2该æ°æ®ç±»åç大å°å¨1-4000个åèèå´å
longè¿ç§æ°æ®ç±»åå¯åå¨æ大2GBã
--æ°å¼æ°æ®ç±»å
numberæ°æ®ç±»åå¯ä»¥åå¨æ£æ°ãè´æ°ãé¶ãå®ç¹æ°å精度为38ä½çæµ®ç¹æ°ã
--æ¥ææ¶é´æ°æ®ç±»å
dateæ°æ®ç±»å使ç¨ä¸ä¸ªåèåºå®é¿åº¦ï¼æ¯ä¸ªåèåå«åå¨ä¸çºªãå¹´ãæãæ¥ãå°æ¶ãååç§ãä»4712å¹´1æ1æ¥å°å
Œ
9999å¹´12æ13æ¥ã
Timestampæ°æ®ç±»åç¨äºåå¨æ¥æçå¹´ãæãæ¥ä»¥åæ¶é´çå°æ¶ãååç§å¼ãç§å¼æ¯ç²¾ç¡®å°å°æ°ç¹å6ä½ã该æ°æ®ç±»åè¿å
æ¬äºæ¶åºä¿¡æ¯ã
--RawåLongRawæ°æ®ç±»å
Rawæ°æ®ç±»å
Rawæ°æ®ç±»åç¨äºåå¨åºäºåèçæ°æ®ãå¦äºè¿å¶æ°æ®æåè串ï¼è¯¥æ°æ®ç±»åæå¤è½åå¨2000个åèã
LongRawæ°æ®ç±»åç¨äºåå¨å¯åé¿åº¦çäºè¿å¶æ°æ®ï¼æå¤è½åå¨2GBã(该æ°æ®ä¸è½ä½¿ç¨ç´¢å¼ï¼longåå°çææéå¶å¯¹longrawæ°æ®ç±»ååæ ·ææ)
--LOBæ°æ®ç±»å
Clobæ°æ®ç±»åè½å¤åå¨å¤§éå符æ°æ®ã该æ°æ®ç±»åå¯ä»¥åå¨ååèæ°æ®åå¤åèå符æ°æ®ãclobå¯ç¨äºåå¨éç»æåçXMLææ¡£ã
Blobæ°æ®ç±»åå¯ä»¥åå¨è¾å¤§çäºè¿å¶å¯¹è±¡ï¼å¦å¾å½¢ãè§é¢åªè¾å声é³åªè¾çã
Bfileæ°æ®ç±»åè½å¤å°äºè¿å¶æ件åå¨äºè¿å¶æ件åå¨å¨æ°æ®åºå¤é¨çæä½ç³»ç»æ件ä¸ãBfileåååå¨ä¸ä¸ªBfileå®ä½å¨ãå®æåä½äºæå¡å¨æ件系ç»ä¸çäºè¿å¶æ件ãæ¯æçæ件æ大为4GBã
--æ¥çæå®ä¼ªåç¨æ·(scott)
select rowid,ename,comm from emp where sal=1500
--æ¥ç伪åæ°
select * from emp where rownum<2
--æ°æ®å®ä¹è¯è¨
create table (éè¦å建ç表)
alter table (éè¦æ´æ¹ç表)
truncate table (éè¦æªæç表)
drop table (éè¦å é¤ç表)
--create tableä¿å½ä»¤
create table [ææè
å³æ¨¡å¼çå称,å¦æç¨æ·å¨èªå·±ç模å¼ä¸å建表ï¼åå¯ä»¥ä¸æå®ææè
å称ã][éè¦å建ç表å]
([åçå称] [æ°æ®ç±»ååé¿åº¦]);
--ä¾å(å¨å½åç¨æ·ä¸å建äºä¸å¼ å为(Cool)ç表ï¼éææä¸ä¸ªååå«æ¯:Cool_IDãCool_NameãCool_like):
create table Cool(
Cool_ID number,
Cool_Name varchar(20),
Cool_like varchar(30));
--å¨è¡¨ä¸æå
¥æ°æ®
insert into Cool values(8850,'é
·å¿','ç¼åJavaåºç¨ç¨åº');
insert into Cool values(8851,'é
·å¿','ç¼åJavaåºç¨ç¨åº1');
insert into Cool values(8852,'é
·å¿','ç¼åJavaåºç¨ç¨åº2');
insert into Cool values(8853,'é
·å¿','ç¼åJavaåºç¨ç¨åº3');
insert into Cool values(8854,'é
·å¿','ç¼åJavaåºç¨ç¨åº4');
insert into Cool values(8856,'é
·å¿','ç¼åJavaåºç¨ç¨åº5');
insert into Cool values(8855,'é
·å¿','ç¼åJavaåºç¨ç¨åº6');
--alter tableå½ä»¤
alter table [éè¦æ´æ¹ç表å½ç§°] [模å¼(modify=æ´æ¹,add=æ·»å ,drop=å é¤)] (表ä¸çåå)
--ä¾å
--æ´æ¹è¡¨ä¸Cool_likeåæ°æ®é¿åº¦æ´æ¹ä¸º15
alter table cool modify (cool_name varchar(15));
--æå
¥æ°æ®è¿è¡æµè¯ç¬¬ä¸æ¡æ°æ®å¯ä»¥é¡ºå©æå
¥å°åä¸ï¼è第äºæ¡æ°æ®åä¸è½æå
¥åæ°ï¼åºä¸ºå®çé¿åº¦å·±è¶
è¿äº15个å符ã(æ§è¡ç»æ为:Errorä½äºç¬¬ä¸è¡:ora-01401:æå
¥æ°æ®å¼å¯¹äºåè¿å¤§);
insert into Cool values(8855,'é
·å¿Test','ç¼åJavaåºç¨ç¨åºã游æççã');
insert into Cool values(8855,'æµè¯ç¨æ·åï¼æ¤åé¿åº¦å°è¶
è¿15个å符ã','ç¼åJavaåºç¨ç¨åºã游æççã');
--å¨è¡¨ä¸æ·»å ä¸ä¸ªåï¼å¨è¡¨ä¸æ·»å ä¸ä¸ªCool_Telæ¥åå¨çµè¯å·ç ã
alter table Cool add (Cool_Tel varchar(12));
insert into Cool values(8856,'é
·å¿8856','ç¼åJavaåºç¨ç¨åº','13529424360');
--å¨è¡¨ä¸å é¤Cool_Telå;
alter table cool drop column cool_tel;
--Truncate tableå½ä»¤
--å é¤è¡¨ä¸çè®°å½èä¸å é¤å,æ¤æ¹æ³ä¸è½åæ»å 为å®ä¸ä½¿ç¨äºå¡å¤çã(ä¼ç¹:å é¤é度快)
truncate table cool;(æ§è¡ç»æ:表己æªæ)
--descæ¥çcool表çç»æ
desc cool;
--drop tableå½ä»¤
--å é¤Cool表ãæ¤æ¹æ³å¯ä»¥ä½¿ç¨äºå¡åæ»ã
drop table cool;
--æ°æ®æ纵è¯è¨
--éæ©å½ä»¤(Select)
select * from emp;(æ¥è¯¢è¡¨ä¸æææ°æ®)
select * from emp where ename='adams';(ææ¡ä»¶æ¥è¯¢è¡¨ä¸æ°æ®)
select distinct * from emp;(ä¸éæ©è¡¨ä¸çéå¤è¡ï¼ä½¿ç¨distinctå
³é®å);
select * from cool where cool_name='é
·å¿' order by cool_id asc;(æCoo_IDè¿è¡ååºæåº)
select * from cool where cool_name='é
·å¿' order by cool_id desc;(æCoo_IDè¿è¡éåºæåº)
create table CoolDemo as select * from cool;(ç¨ç°æ表Coolå建ä¸ä¸ªæ°è¡¨ï¼æ°æ®å
æ¬å¨selectè¯å
é,å¯ä»¥æ¯ææï¼ä¹å¯ä»¥æ¯æå®çå)
create table CoolDemo2 as select * from cool where 1 = 2;(æ¤æ¡ä»¶ä¸æç«ï¼ææåªå建äºè¡¨ç»æï¼æ²¡ææ°æ®)
select cool_id+10 sum ,cool_name from cool;(æ¤æ¡è¯å¥ä½¿ç¨äºå«åsum并ä¸æ¯cool_id+10è¯åçå¼ï¼ä½åå§å¼ä¸ä¼æ¹åã)
--æå
¥å½ä»¤(Insert)
insert into cool values(8857,'é
·å¿','ç¼åJavaåºç¨ç¨åº');(å¨Cool表ä¸æå
¥æ°æ®)
insert into cool(cool_name,cool_like) values('é
·å¿','ç¼åJavaåºç¨ç¨åºnull');(åªæå
¥å§åå(cool_name)åç±å¥½å(cool_like)çå¼)
insert into cool values(8858,'é
·å¿',null);(å¨cool表ä¸è·³è¿äºcool_likeåï¼ä½¿ç¨ç©ºå¼æ¥å¡«å
)
alter table cool add(cool_date date);(å¨è¡¨ä¸æ·»å ä¸ä¸ªæ¥æåï¼ä»¥ä¾¿ä¸é¢æå
¥æ¥æå¼)
insert into cool values(8859,'é
·å¿','ç¼åJavaåºç¨ç¨åº8859','22-11æ-06');(æ¤æ¡è¯å¥å¨Cool表ä¸cool_dateåæå
¥äº2006å¹´11æ12æ¥çæ¥æ)
insert into cool select * from cooldemo;(æ¤æ¡è¯å¥æå
¥äºæ¥èªæå®è¡¨çæ°æ®)
--æ´æ°å½ä»¤(Update)
update cool set cool_name='é
·å¿è³é
·' where cool_name='é
·å¿';(å¦æcool_nameåçå¼çäº(é
·å¿)é£ä¹å°±å°å®æ´æ¹ä¸º(é
·å¿è³é
·),è¿æ¯ææ¡ä»¶çæ¥è¯¢)
update cool set cool_name='é
·å¿';(æ¤æ¡è¯å¥å°cool_nameåçææå¼æ´æ¹ä¸º(é
·å¿))
--å é¤å½ä»¤(delete)
delete cool where cool_like='ç¼åJavaåºç¨ç¨åºnull';(å¦æcool表ä¸cool_likeåå¼çäº(ç¼åJavaåºç¨ç¨åºnull)å°±å é¤å®)
--æ交å½ä»¤(Commit)
commit;(æ交äºå¡)
commit work;(åè½åä¸)
--æ è®°å½ä»¤(SavePoint)
savepoint savepoint_id;(å®ç¨æ¥æ è®°äºå¡ä¸å¯ä»¥åºç¨åæ»çç¹ã)
--åæ»äºå¡(RollBack)
rollback;
rollback work;
rollback to savepoint [èªå·±æ è®°çè®°å½ç¹];
--æ°æ®æ§å¶è¯è¨
--ææå½ä»¤(Grant)
Grant [æä½æéå¦:select,update,deleteç] on [表å称] to [ç¨æ·å]
grant select,update,delete on cool to hedong911;(æ¤æ¡è¯å¥ææç»ç¨æ·hedong911æ¥è¯¢ãæ´æ°ãå é¤cool表çæå)
grant update(cool_id,cool_name) on cool to hedong911;(æ¤æ¡è¯å¥ææç»ç¨æ·hedong911æ´æ°cool表(cool_id,cool_name)åæå)
grant select on cool to hedong911 with grant option;(æ¤è¯å¥ææçç¨æ·hedong911å¯ä»¥ç»å
¶å®ç¨æ·ææ)
--åæ¶ææå½ä»¤(revoke)
revoke select,update,delete on cool to hedong911;(æ¤æ¡è¯å¥åæ¶äºhedong911çæ¥è¯¢ãæ´æ°ãå é¤æå)
--ç®æ¯æä½ç¬¦
(è·³è¿)
--æ¯è¾æä½ç¬¦
--æ¯è¾æä½ç¬¦å
æ¬(=ã!=ã<ã>ã<=ã>=ãbetweenâ¦â¦and(æ£æ¥æ¯å¦å¨ä¸¤ä¸ªå¼ä¹é´)ãin(ä¸å表ä¸çå¼ç¸å¹é
)ãlike(å¹é
å符模å¼)åis null(æ£æ¥æ¯å¦ä¸ºç©º)ï¼æåå个æä½ç¬¦è¿å¯ä»¥ånot(é)ä¸èµ·ä½¿ç¨å¦:not betweenâ¦â¦andç
select * from cool where cool_name!='1';
select * from cool where cool_name like 'é
·å¿';
select * from cool where cool_id < 8853;
select * from cool where cool_id > 8853;
select * from cool where cool_id <= 8853;
select * from cool where cool_id >= 8853;
select * from cool where cool_like in ('ç¼åJavaåºç¨ç¨åº');
--é»è¾æä½ç¬¦
--é»è¾æä½ç¬¦å
æ¬and(ä¸)ãor(æ)ånow(é);
select * from cool where cool_id <=8856 and cool_id >= 8853;
select * from cool where cool_id =9999 or cool_like in ('ç¼åJavaåºç¨ç¨åº');
--éåæä½ç¬¦
--èåæ¥è¯¢(union)
select * from cool union select * from cooldemo;(æ¤æ¡è¯å¥å°CoolåCoolDemoä¸¤å¼ è¡¨çæ¥è¯¢ç»æåæ¼ï¼å¹¶å é¤éå¤è¡)
--èåæææ¥è¯¢(union all)
select * from cool union all select * from cooldemo;(æ¤æ¡è¯å¥å°CoolåCoolDemoä¸¤å¼ è¡¨çæ¥è¯¢ç»æåæ¼ï¼ä¸å é¤éå¤è¡)
--交éæ¥è¯¢(intersect)
select * from cool intersect select * from cooldemo;(æ¤æ¡è¯å¥æ¥è¯¢ä¸¤å¼ 表é½æçè¡)
--åéæ¥è¯¢(minus)
select * from cool minus select * from cooldemo;(æ¤æ¡è¯å¥è¿å第äºå¼ 表没æçæ°æ®)
--è¿æ¥(||)æä½ç¬¦
select ('ç¨æ·ç¼å·:'||cool_id||'å§åæ¯:'||cool_name||'ç±å¥½æ¯:'||cool_like||'æ¥ææ¯:'||cool_date) from cool where cool_id=8859;(è¿è¡ç»æ:ç¨æ·ç¼å·:8859å§åæ¯:é
·å¿ç±å¥½æ¯:ç¼åJavaåºç¨ç¨åº8859æ¥ææ¯:22-11æ-06)
温馨提示:答案为网友推荐,仅供参考