OracleÖÐSQL PLUSµÄ³£ÓÃÖ¸Áî
½øÈësql*plusÐèÒªÊäÈëÓû§Ãû¡¢¿ÚÁîºÍÖ÷»ú±êÖ¾·û
system/manager
sys/change_on_intall
scott/tiger
internal/oracle
ÒÔÉÏΪ³õʼÃÜÂë
Ö÷»ú×Ö·û´®¿Õ»òÕßÊÇ@+ÄãµÄ·þÎñÃû,ÔÚ±¾µØµÇ½²»ÐèÒª±¾µØ·þÎñÃû¡£
ÏÂÃæ¾Í½éÉÜÒ»ÏÂһЩ³£ÓõÄsql*plusÃüÁ
Ê×ÏȲ鿴µ±Ç°Ê¹ÓõÄÊý¾Ý¿âʵÀý:
select name from V$database;
Çл»Á½¸öÊý¾Ý¿âʵÀý
ÔÚsqlplusÀïconnect username/password@sid
»òhost set ORACLE_SID=sid
»òcmdÀïset ORACLE_SID=sid
»òÐÞ¸Ä×¢²á±íÀïORACLE_SIDÏî
¡ô1. Ö´ÐÐÒ»¸öSQL½Å±¾Îļþ
SQL>start file_name
SQL>@ file_name
ÎÒÃÇ¿ÉÒÔ½«¶àÌõsqlÓï¾ä±£´æÔÚÒ»¸öÎı¾ÎļþÖУ¬ÕâÑùµ±ÒªÖ´ÐÐÕâ¸öÎļþÖеÄËùÓеÄsqlÓï¾äʱ£¬ÓÃÉÏÃæµÄÈÎÒ»ÃüÁî¼´¿É£¬ÕâÀàËÆÓÚdosÖеÄÅú´¦Àí¡£
¡ô2. ¶Ôµ±Ç°µÄÊäÈë½øÐбà¼
SQL>edit
¡ô3. ÖØÐÂÔËÐÐÉÏÒ»´ÎÔËÐеÄsqlÓï¾ä
SQL>/
¡ô4. ½«ÏÔʾµÄÄÚÈÝÊä³öµ½Ö¸¶¨Îļþ
SQL> SPOOL file_name
ÔÚÆÁÄ»ÉϵÄËùÓÐÄÚÈݶ¼°üº¬ÔÚ¸ÃÎļþÖУ¬°üÀ¨ÄãÊäÈëµÄsqlÓï¾ä¡£
¡ô5. ¹Ø±ÕspoolÊä³ö
SQL> SPOOL OFF
Ö»ÓйرÕspoolÊä³ö£¬²Å»áÔÚÊä³öÎļþÖп´µ½Êä³öµÄÄÚÈÝ¡£
¡ô6£®ÏÔʾһ¸ö±íµÄ½á¹¹
SQL> desc table_name
¡ô7. COLÃüÁ
Ö÷Òª¸ñʽ»¯ÁеÄÏÔʾÐÎʽ¡£
¸ÃÃüÁîÓÐÐí¶àÑ¡Ï¾ßÌåÈçÏ£º
COL[UMN] [{ column|expr} [ option ...]]
OptionÑ¡Ïî¿ÉÒÔÊÇÈçϵÄ×Ó¾ä:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
1). ¸Ä±äȱʡµÄÁбêÌâ
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- ---------
10 ACCOUNTING NEW YORK
sql>col LOC heading location
sql>select * from dept;
DEPTNO DNAME location
--------- ---------------------------- -----------
10 ACCOUNTING NEW YORK
2). ½«ÁÐÃûENAME¸ÄΪÐÂÁÐÃûEMPLOYEE NAME²¢½«ÐÂÁÐÃû·ÅÔÚÁ½ÐÐÉÏ£º
Sql>select * from emp
Department name Salary
---------- ---------- ----------
10 aaa 11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from
Ïà¹ØÎĵµ£º
ʲôÊǺϲ¢¶àÐÐ×Ö·û´®£¨Á¬½Ó×Ö·û´®£©ÄØ£¬ÀýÈ磺
SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y &nb ......
Ê×ÏÈдºÃ½¨¿â½Å±¾c.sql
¡¡¡¡
¡¡¡¡create database mydb
¡¡¡¡controlfile reuse
¡¡¡¡maxinstances 1
¡¡¡¡maxloghistory 1
¡¡¡¡maxlogfiles 5
¡¡¡¡maxlogmembers 5
¡¡¡¡maxdatafiles 100
¡¡¡¡datafile '$ORACLE_HOME/oradata/system01.dbf'size 325M reuse
¡¡¡¡autoextend on next 10240K maxsize unlimited
¡¡¡¡u ......
--Óï ¾ä ¹¦ ÄÜ
--Êý¾Ý²Ù×÷
SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ
DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ
UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý
--Êý¾Ý¶¨Òå
CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í
DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í
ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹
CREATE VIEW --´´½¨Ò»¸öÊÓͼ
DRO ......
--¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price) --ËãÆ½¾ùÊý
from titles
where type='business'
go
use pubs
go
select max(ytd_sales) --×î´óÊý
from titles
go
use pubs
go
select min(ytd_sales) --×îÐ¡Ê ......
ÐÐÁÐת»»
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2, ......