资源描述:
《sql语句的编程手册.doc》由会员上传分享,免费在线阅读,更多相关内容在应用文档-天天文库。
1、sql语句的编程手册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 / disco
2、nnect 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 用户创建的基表的所有列定义的清单 CATA
3、LOG 用户可存取的所有基表清单 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,jo
4、b 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 l
5、ike 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 没有
6、奖金的雇员(即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 where comm>.05*sal order by comm
7、/sal desc; 日期型数据的运算 add two days to 6-Mar-87 6-Mar-87 + 2 = 8-Mar-87 add two hours to 6-Mar-87 6-Mar-87 + 2/24 = 6-Mar-87 and 2hrs add 15 seconds to 6-Mar-87 6-Mar-87 + 15/(24*60*60) = 6-Mar-87 and 15 secs 列名的别名 select ename employee from emp