数据库(oracle)简单用法举例

数据库（oracle）简单用法举例：ConnectedtoOracle9iEnterpriseEditionRelease9.0.1.1.1Connectedasscott@ORADBSQLselect*fromemp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------7369SMITHCLERK79021980-12-17800.00207499ALLENSALESMAN76981981-2-201600.00300.00307521WARDSALESMAN76981981-2-221250.00500.00307566JONESMANAGER78391981-4-22975.00207654MARTINSALESMAN76981981-9-281250.001400.00307698BLAKEMANAGER78391981-5-12850.00307782CLARKMANAGER78391981-6-92450.00107788SCOTTANALYST75661987-4-193000.00207839KINGPRESIDENT1981-11-175000.00107844TURNERSALESMAN76981981-9-81500.000.00307876ADAMSCLERK77881987-5-231100.00207900JAMESCLERK76981981-12-3950.00307902FORDANALYST75661981-12-33000.00207934MILLERCLERK77821982-1-231300.001014rowsselectedSQLselectename,salfromemp;ENAMESAL-------------------SMITH800.00ALLEN1600.00WARD1250.00JONES2975.00MARTIN1250.00BLAKE2850.00CLARK2450.00SCOTT3000.00KING5000.00TURNER1500.00ADAMS1100.00JAMES950.00FORD3000.00MILLER1300.0014rowsselectedSQLselectsalfromemp;SAL---------800.001600.001250.002975.001250.002850.002450.003000.005000.001500.001100.00950.003000.001300.0014rowsselectedSQLselectdeptnofromemp;DEPTNO------203030203030102010302030201014rowsselectedSQLselectdistinctdeptnofromemp;DEPTNO------102030SQLselectenameas职工名,sal薪水fromemp;职工名薪水-------------------SMITH800.00ALLEN1600.00WARD1250.00JONES2975.00MARTIN1250.00BLAKE2850.00CLARK2450.00SCOTT3000.00KING5000.00TURNER1500.00ADAMS1100.00JAMES950.00FORD3000.00MILLER1300.0014rowsselectedSQLselect*fromdept;DEPTNODNAMELOC---------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONSQLselectenameas职工名,sal薪水,dname部门fromemp,dept;职工名薪水部门---------------------------------SMITH800.00ACCOUNTINGALLEN1600.00ACCOUNTINGWARD1250.00ACCOUNTINGJONES2975.00ACCOUNTINGMARTIN1250.00ACCOUNTINGBLAKE2850.00ACCOUNTINGCLARK2450.00ACCOUNTINGSCOTT3000.00ACCOUNTINGKING5000.00ACCOUNTINGTURNER1500.00ACCOUNTINGADAMS1100.00ACCOUNTINGJAMES950.00ACCOUNTINGFORD3000.00ACCOUNTINGMILLER1300.00ACCOUNTINGSMITH800.00RESEARCHALLEN1600.00RESEARCHWARD1250.00RESEARCHJONES2975.00RESEARCHMARTIN1250.00RESEARCHBLAKE2850.00RESEARCH职工名薪水部门---------------------------------CLARK2450.00RESEARCHSCOTT3000.00RESEARCHKING5000.00RESEARCHTURNER1500.00RESEARCHADAMS1100.00RESEARCHJAMES950.00RESEARCHFORD3000.00RESEARCHMILLER1300.00RESEARCHSMITH800.00SALESALLEN1600.00SALESWARD1250.00SALESJONES2975.00SALESMARTIN1250.00SALESBLAKE2850.00SALESCLARK2450.00SALESSCOTT3000.00SALESKING5000.00SALESTURNER1500.00SALESADAMS1100.00SALESJAMES950.00SALESFORD3000.00SALES职工名薪水部门---------------------------------MILLER1300.00SALESSMITH800.00OPERATIONSALLEN1600.00OPERATIONSWARD1250.00OPERATIONSJONES2975.00OPERATIONSMARTIN1250.00OPERATIONSBLAKE2850.00OPERATIONSCLARK2450.00OPERATIONSSCOTT3000.00OPERATIONSKING5000.00OPERATIONSTURNER1500.00OPERATIONSADAMS1100.00OPERATIONSJAMES950.00OPERATIONSFORD3000.00OPERATIONSMILLER1300.00OPERATIONS56rowsselectedSQLselectenameas职工名,sal薪水,dname部门fromemp,deptwhereemp.deptno=dept.deptno;职工名薪水部门---------------------------------SMITH800.00RESEARCHALLEN1600.00SALESWARD1250.00SALESJONES2975.00RESEARCHMARTIN1250.00SALESBLAKE2850.00SALESCLARK2450.00ACCOUNTINGSCOTT3000.00RESEARCHKING5000.00ACCOUNTINGTURNER1500.00SALESADAMS1100.00RESEARCHJAMES950.00SALESFORD3000.00RESEARCHMILLER1300.00ACCOUNTING14rowsselectedSQLselectenamefromempwheresal3000;ENAME----------KINGSQLselectenamefromempwheresal=2000;ENAME----------SMITHALLENWARDMARTINTURNERADAMSJAMESMILLER8rowsselectedSQLselectenamefromempwheresal=2000andsal=3000;ENAME----------JONESBLAKECLARKSCOTTFORDSQLselectenamefromempwheresal=2000andsal=3000;ENAME----------SQLselectenamefromempwheresal=2000orsal=3000;ENAME----------SMITHALLENWARDMARTINSCOTTKINGTURNERADAMSJAMESFORDMILLER11rowsselectedSQLselectenamefromempwheresalbetween2000and3000;ENAME----------JONESBLAKECLARKSCOTTFORDSQLselectenamefromempwheresalnotbetween2000and3000;ENAME----------SMITHALLENWARDMARTINKINGTURNERADAMSJAMESMILLER9rowsselectedSQLselectenamefromempwhereenamelike'A%';ENAME----------ALLENADAMSSQLselectenamefromempwhereenamelike'%A';ENAME----------SQLselectenamefromempwhereenamelike'_A%';ENAME----------WARDMARTINJAMESSQLselectenamefromempwhereenamelike'__A%';ENAME----------BLAKECLARKADAMSSQLselectenamefromempwheredeptnoin(20,40);ENAME----------SMITHJONESSCOTTADAMSFORDSQLselect*fromdept;DEPTNODNAMELOC---------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONSQLselectenamefromempwheredeptnoin(selectdeptnofromdeptwheredname='RESEARCH'ordname='OPERATIONS');ENAME----------SMITHJONESSCOTTADAMSFORDSQLselectename,job,salfromempwheredeptnoin(20,40);ENAMEJOBSAL----------------------------SMITHCLERK800.00JONESMANAGER2975.00SCOTTANALYST3000.00ADAMSCLERK1100.00FORDANALYST3000.00SQLselectename,job,salfromempwheredeptnoin(20,40)orderbysaldesc;ENAMEJOBSAL----------------------------SCOTTANALYST3000.00FORDANALYST3000.00JONESMANAGER2975.00ADAMSCLERK1100.00SMITHCLERK800.