PLSQL编程手册.docVIP

转）sql ，PL/SQL编程手册原贴位置：/dvbbs/dispbbs.asp?boardID=11ID=115page=1sql语句的编程手册 SQL PLUS一、SQL PLUS??1 引言??SQL命令??以下17个是作为语句开头的关键字：??alter??drop??revoke??audit??grant??rollback*??commit*??insert??select??comment??lock??update??create??noaudit??validate??delete??rename??这些命令必须以“;”结尾??带*命令句尾不必加分号，并且不存入SQL缓存区。??SQL中没有的SQL*PLUS命令??这些命令不存入SQL缓存区??@??define??pause??#??del??quit??$??describe??remark??/??disconnect??run??accept??document??save??append??edit??set??break??exit??show??btitle??get??spool??change??help??sqlplus??clear??host??start??column??input??timing??compute??list??ttitle??connect??newpage??undefine??copy????2 数据库查询??数据字典??TAB 用户创建的所有基表、视图和同义词清单??DTAB 构成数据字典的所有表??COL 用户创建的基表的所有列定义的清单??CATALOG 用户可存取的所有基表清单??select * from tab;??describe命令 描述基表的结构信息??describe dept??select *??from emp;??select empno,ename,job??from emp;??select * from dept??order by deptno desc;??逻辑运算符??= !=或 = =??in??between value1 and value2??like??%??_??in null??not??no in,is not null??谓词in和not in??有哪些职员和分析员??select ename,job??from emp??where job in (clerk,analyst);??select ename,job??from emp??where job not in (clerk,analyst);??谓词between和not between??哪些雇员的工资在2000和3000之间??select ename,job,sal from emp??where sal between 2000 and 3000;??select ename,job,sal from emp??where sal not between 2000 and 3000;??谓词like,not like??select ename,deptno from emp??where ename like S%;??(以字母S开头)??select ename,deptno from emp??where ename like %K;??(以K结尾)??select ename,deptno from emp??where ename like W___;??(以W开头，后面仅有三个字母)??select ename,job from emp??where job not like sales%;??(哪些雇员的工种名不以sales开头)??谓词is null,is not null??没有奖金的雇员（即commision为null）??select ename,job from emp??where comm is null;??select ename,job from emp??where comm is not null;??多条件查询??select ename,job??from emp??where deptno=20??and job!=clerk;??表达式??+ - * /??算术表达式??选择奖金高于其工资的5%的雇员??select ename,sal,comm,comm/sal from emp??