资源描述:
《sql语句的编程手册》由会员上传分享,免费在线阅读,更多相关内容在应用文档-天天文库。
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 /
2、 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 用户创建的基表
3、的所有列定义的清单 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 有哪些职
4、员和分析员 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
5、 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%'; (哪些雇员的工种名
6、不以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 f
7、rom emp where comm>.05*sal order by comm/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