这个是对于oracle数据库的sql基本语句, SQL plus执行通过的 ------------------------------------------------------------------
select empno, to_char(sal,''''999,999.99'''') sal from emp; select distinct deptno from emp; select empno,ename,sal*0.5 from emp where deptno=10; select empno||''''''''||ename,nvl(sal,0)+nvl(comm,0) from emp; select empno,ename,job,sal from emp where empno=&empno; select sysdate,user,uid,rowid,rownum from emp; [sysdate,user,uid,rowid,rownum为伪列] select empno,ename,comm from emp where comm=null; [comm is null]; select empno,ename,nvl(comm,"0") from emp where comm is null; select deptno,dname from dept where deptno in(30,40); select deptno,dname,loc from dept where loc not in(''''NEW YORK'''',''''CHICAGO''''); select deptno,ename,sal from emp where deptno=10 or deptno=20 and sal>3000; [列别名] select e.ename EMPLOYEE,e.sal*1.15 NEW_SAl from emp e where e.deptno=10; [多表连接] select d.dname,e.ename,e.sal,e.comm from emp e,dept d where d.deptno=e.deptno order by d.deptno; [使用子查询] select ename from emp where deptno=(select deptno from dept where dname=''''SALES''''); [查询别名] select e.ename,d.dname,e.deptno||''''==''''||d.deptno from emp e, (select deptno,dname from dept where loc=''''NEW YORK'''') d where e.deptno=d.deptno order by d.deptno; [union:联合] select ename,sal,comm from emp union select ''''TOTAL'''',sum(sal),sum(comm) from emp order by sal
ENAME SAL COMM ---------- --------- --------- SMITH 800 JAMES 950 ADAMS 1100 SCOTT 3000 KING 5000 TOTAL 29025 2200 ------------------------------ [intersect:相交] select ename,sal,comm from emp where sal>1300 INTERSECT select ename,sal,comm from emp where comm is not null ===select ename,sal,comm from emp where sal>1300 and comm is not null
ENAME SAL COMM ---------- --------- --------- ALLEN 1600 300 TURNER 1500 0 ------------------------------ [minus] select ename,sal comm from emp where sal>1300 minus select ename,sal comm from emp where sal>1500; ===select ename,sal,comm from emp where sal>1300 and not(sal>1500) ENAME COMM ---------- --------- TURNER 1500 -------------------- select to_char(sysdate,''''yyyy/mm/dd hh24:mi'''') sys_date from dual; select to_date(''''2002/08/13'''',''''yyyy/mm/dd'''') from dual; select to_number(''''12345'''',99999) from dual; select empno,ename from emp where months_between(sysdate,hiredate)>=12; add_months(date,number) last_day(date) months_between(date1,date2) next_dat(date,day) round(date,format) trunc(date,format) --------------------- 数值函数 abs(number) ceil(number) cos(number) ln(number) mod(n,m) round(number,decimal_digits) sign(number) sqrt(number) sin(number) ------------------- 字符函数 ascii(character) chr(number) concat(string1,string2) #|| initcap(string) length(string) lower(string) upper(string) substr(string,start[,length]) replace(string,search_string,replace_string) ------------------- other greatest(list of values) least(list of values) nvl(expression,replacement_value) AVG(expression) COUNT(expression) MAX(expression) MIN(expression) SUM(expression) Welcome>select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
COUNT(*) SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL) --------- --------- --------- --------- --------- 14 29025 2073.2143 5000 800 --------------------------------------------------------------------------- [右连接:如下图,如果出现条件不符和的,以左边为主/e.deptno/,右边的/d.deptno/应该以空行还填补左边显示的内容] select d.dname,e.ename from emp e,dept d where e.deptno=d.deptno(+) order by d.dname,e.ename;
1 select d.dname D_Dname,e.ename E_Ename,d.deptno D_Deptno,e.deptno E_Deptno from emp e,dept d 2* where e.deptno=d.deptno(+) order by d.dname,e.ename Welcome>/
D_DNAME E_ENAME D_DEPTNO E_DEPTNO -------------- ---------- --------- --------- ACCOUNTING CLARK 10 10 ACCOUNTING KING 10 10 ACCOUNTING MILLER 10 10 RESEARCH ADAMS 20 20 RESEARCH FORD 20 20 RESEARCH JONES 20 20 RESEARCH SCOTT 20 20 RESEARCH SMITH 20 20 SALES ALLEN 30 30 SALES BLAKE 30 30 SALES JAMES 30 30 SALES MARTIN 30 30 SALES TURNER 30 30 SALES WARD 30 30 ---------------------------------------------
[左连接:如下图,如果出现条件不符和的,以右边为主/d.deptno/,左边的/e.deptno/应该以空行还填补右边显示的内容] select d.dname D_Dname,e.ename E_Ename,d.deptno D_Deptno,e.deptno E_Deptno from emp e,dept d where e.deptno(+)=d.deptno order by d.dname,e.ename
D_DNAME E_ENAME D_DEPTNO E_DEPTNO -------------- ---------- --------- --------- ACCOUNTING CLARK 10 10 ACCOUNTING KING 10 10 ACCOUNTING MILLER 10 10 OPERATIONS 40 RESEARCH ADAMS 20 20 RESEARCH FORD 20 20 RESEARCH JONES 20   [1] [2] 下一页 [Access]sql随机抽取记录 [Access]ASP&SQL让select查询结果随机排序的实现方法 [系统软件]EXP-00008: ORACLE error 904 encountered的解决方… [系统软件]SQL语句性能优化--LECCO SQL Expert [常用软件]PB7 连接 Oracle 的配置方法 [C语言系列]SQL Server到DB2连接服务器的实现 [C语言系列]SQL Server到SYBASE连接服务器的实现 [C语言系列]SQL Server到SQLBASE连接服务器的实现 [C语言系列]SQL Server连接VFP数据库的实现 [C语言系列]ASP+SQL Server之图象数据处理
|