oracle基本查询用法入门示例

SELECT *|{[DISTINCT] column|expression [alias], ...}FROM table;

--查询所有数据select * from emp;--查询指定列数据select empno,ename,sal from emp;--算数运算符(+ - * /)select ename,sal,sal+30 from emp;--使用括号select ename,sal,12*(sal+30) from emp;--定义空值--(空值是无效的，未指定，未知的或不可预知的值，空值不是空格或是0)select ename,job,sal,comm from emp;--空值的数学运算--包含空值的数学表达式的值都为空值select ename,12*sal+comm from emp;--列的别名--别名使用双引号，AS可以省略select deptno as "no",ename as "name" from emp;--连接符,把列与列，列与字符连接在一起select deptno || '--' || ename from emp;--字符串--日期和字符只能在单引号中出现select 'hello ' || ename from emp;--删除重复行select distinct deptno from emp;--显示表结构desc[ribe] tablename;

SELECT *|{[DISTINCT] column|expression [alias], ...}FROM table[WHERE condition(s)];

--查询指定条件数据select deptno,ename from emp where deptno=10;--字符串和日期包含在单引号中--字符串大小写敏感，日期格式敏感select ename,job,deptno from emp where ename='King';--比较运算符(= > < <= >= <> !=)select ename,sal from emp where sal<1500;--其他比较运算符--BETWEEN ... AND ... 在两个值之间包含边界--IN(set) 等于值列表中的一个--LIKE 模糊查询--IS NULL 空值select ename,sal,deptno from emp where deptno in(10,30);select ename,sal,comm from emp where comm is null;--逻辑运算(AND OR NOT)select ename,sal from emp where deptno=10 and sal>1500;

ORDER BY 字段 [DESC|ASC]

select ename,sal from emp order by sal desc;--多列排序--先按第一列排序，如果相同，则按第二列排序，以此类推select * from emp order by sal desc,hiredate desc;

--LOWER 转换小写--UPPER 转换大写--INITCAP 首字母大写select lower(ename) from emp;--CONCAT 接接字符串--SUBSTR 截取字符串--LENGTH 字符串长度--INSTR 查找字符串--LPAD 左边填充字符--RPAD 右边填充字符--TRIM([leading|trailing|both] 字符串1 from 字符串2) --TRIM可以删除两边空格，也可删除其他字符--REPLACE 替换字符串select concat('aa','bb') from emp;select substr('abcdefg', 2, 3) from emp;select length('test...') from emp;select instr('hello world', 'w') from emp;select lpad(sal, '10', '0') from emp;select rpad(sal, '10', '*') from emp;select trim(' test ') from emp;--从尾部删除字符串*号select trim(trailing '*' from '**1212121**') from emp;--把字符串中的22替换成88select replace('11223344', '22', '88') from emp;

--ROUND 四舍五入--TRUNC 截断--MOD 求余select round(25.533,2) from dual;select trunc(25.323,2) from dual;select mod(8, 3) from dual;

--返回系统时间select sysdate from dual;--两个日期相减，返回日期之间相差的天数select ename,(sysdate-hiredate) / 7 "weeks" from emp;--MONTHS_BETWEEN 两日期相差月数--ADD_MONTHS 指定日期加上若干月数--NEXT_DAY 指定日期的下一个日期--LAST_DAY 本月的最后一天--ROUND 日期四舍五入--TRUNC 日期截断select months_between(sysdate,hiredate) from emp;

--TO_CHAR(date, 'format_model')--把日期转换成字符串select to_char(sysdate, 'YYYY MM DD HH:MI:SS') from dual;--TO_CHAR(number, 'format_model')select ename,sal,to_char(sal, '$99,999.00') from emp;--TO_NUMBER(char[,'format_model'])--TO_DATE(char[,'format_model'])

NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...)

CASE expr WHEN comparison_expr1 THEN return_expr1     [WHEN comparison_expr2 THEN return_expr2      WHEN comparison_expr3 THEN return_expr3      ELSE else_expr]END

DECODE(col|expression, search1, result1           [,search2, result2, ..., ]           [,defautl])

select ename,sal,decode(round(sal/1000),1, '一倍'，2, '二倍'，3, '三倍','不知倍数') from emp;

--AVG--COUNT--MAX--MIN--SUMselect sum(sal) as "total" from emp;select max(sal) from emp;select count(*) from emp where deptno=10;select count(distinct deptno) from emp;

select deptno,avg(sal) from emp group by deptno;

--多列分组select deptno,job,avg(sal) from emp group by deptno,job;

select deptno,avg(sal) from emp group by deptno having deptno in(10,20);

select max(avg(sal)) from emp group by deptno;

SELECT table1.column, table2.columnFROM table1,table2WHERE table1.column1=table2.column2;

--等值连接select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno;--多连接条件和and操作符select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno and e.deptno=10;

--不等值连接--查询员工部门名称和工资等级select d.dname, e.ename, e.sal, s.gradefrom emp e, dept d, salgrade swhere e.deptno = d.deptnoand e.sal >= s.losaland e.sal <= s.hisal;

SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column(+) = table2.column;SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column= table2.column(+) ;

--查询部门人数select d.dname, d.deptno, count(e.empno) from emp e, dept d where e.deptno(+) = d.deptno group by d.deptno,d.dname;

--查询员工的上级select e.ename as "员工", e2.ename as "上级" from emp e, emp e2 where e.empno = e2.mgr;

SELECT table1.column, table2.columnFROM table1[CROSS JOIN table2] |[NATURAL JOIN table2] |[JOIN table2 USING (column_name)] |[JOIN table2 ON(table1.column_name=table2.column_name)] |[LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];

select e.ename,d.dname from emp e cross join dept d;

select e.ename,d.dname from emp e natural join dept d;

select e.ename,d.dname from emp e join dept d USING (deptno);

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

select e.ename, d.dname, e2.enamefrom emp ejoin dept don e.deptno = d.deptnojoin emp e2on e.empno = e2.mgr;

--左外连接select e.ename, d.dnamefrom emp eleft outer join dept don e.deptno = d.deptno;

--右外连接，返回右表中不满足条件的行select e.ename, d.dnamefrom emp eright outer join dept don e.deptno = d.deptno;

--满外连接select e.ename, d.dnamefrom emp efull outer join dept don e.deptno = d.deptno;