oracle基本查询语句总结
|
spool E:\基本查询.txt |
将命令行的语句写入到指定的目下的指定的文件中 |
|
|
host cls |
清屏命令 |
|
|
show user |
显示当前操作的用户 |
|
|
desc emp |
查看表结构 |
|
|
select * from emp; |
查看所有的员工的信息 |
|
|
--设置行宽 |
SQL> --设置行宽 SQL> show linesize SQL> set linesize 150 SQL> --设置列宽 SQL> col ename for a8代表8为字符组成的字符串 SQL> col sal for 9999 代表4位的数字 |
|
|
SQL中的null 1包含null的表达式都为null 2. null永远!=null |
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)
select * from emp where comm is null; 已选择 14 行。
SQL> --2. null永远!=null SQL> --查询奖金为null的员工 SQL> select * 2 from emp 3 where comm=null;
未选定行
SQL> select * 2 from emp 3 where comm is null; |
|
|
ed |
该命令主要用在当前一个sql语句写错时，然又不想重新写，这个时候可以用ed命令记事本调出来对先去的命令进行编辑,如果该命令用在Linux系统中，调出来的应该是vi编辑器 |
|
|
别名设置 |
select empno as "员工号",ename "姓名",sal "月 薪",sal*12,comm,sal*12+nvl(comm,0) 2* from emp
|
|
|
c 命令 |
该命令会对错误的行信息进行修改示例如下： SQL> select empno,ename,sal 2 form emp; form emp * 第 2 行出现错误: ORA-00923: 未找到要求的 FROM 关键字 SQL> 2 2* form emp SQL> --c命令 change SQL> c /form/from 2* from emp SQL> / |
|
|
nvl(comm,0)--滤空函数 |
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) |
|
|
distinct |
去掉重复记录select deptno from emp; |
|
|
Distinct组合 |
select distinct deptno,job from emp; |
|
|
Concat |
1. select concat('Hello',' World') from dual; 2. select 'hello'||' world' 值 from dual; 3. SQL> --查询员工信息： ***的薪水是**** SQL> select ename||'的薪水是'||sal 值 2 from emp;
|
|
|
过滤和查询 |
||
|
where |
select * from emp where deptno=10; 在where过滤中对大小写敏感 |
|
|
date |
SQL> -- 日期格式敏感 SQL> --查询入职日期是17-11月-81的员工 SQL> select * 2 from emp 3 where hiredate='17-11月-81';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- --------- ---------- -------------- ----- ---------- ---------- 7839 KING PRESIDENT 17-11月-81 5000 10 SQL> --修改日期格式 SQL> select * from v$nls_parameters; alter session set NLS_DATE_FORMAT='yyyy-mm-dd'; |
|
|
between and between and: . 包含边界 2. 小值在前 大值在后 |
SQL> --查询薪水1000~2000之间的员工 SQL> select * 2 from emp 3 where sal between 1000 and 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------- --------- ---------- -------------- ----- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 |
|
|
in |
select * from emp where deptno in (10,20); SQL> --null 3: 如果集合中含义null，不能使用not in；但可以使用in SQL> ed 已写入 file afiedt.buf
1 select * 2 from emp 3* where deptno in (10,20,null) |
|
|
like % |
1.查询名字以S打头的员工 select * from emp where ename like 'S%'; 2.查询名字是4个字的员工 select * from emp where ename like '____'; 3.-查询名字中含有下划线的员工 > select * from emp where ename like '%\_%'; escape '\'
|
|
|
order by |
排序操作order by 后面 + 列，表达式，别名，序号 order by 作用于后面所有的列，desc只作用于离他最近的一列 |
|
|
排序时的下技巧 |
当我们对某一类进行倒序排序的时候，null会放在最上面，这个时候为了避免前面n列都是null值，影响观察，可以采取下面的方法 select *from emp order by comm desc nulls last |
|
|
单行函数 |
|
|
|
Lower Uppper initcap
|
select lower('Hello World') 转小写,upper('Hello World') 转大写, initcap('hello world') 首字母大写 from dual; 转小写 转大写 首字母大写 ----------- ----------- ----------- hello world HELLO WORLD Hello World |
|
|
substr(a,b) |
substr(a,b) 从 a中，第b位开始 select substr('Hello World',3) from dual; SUBSTR('H --------- llo World
|
|
|
substr(a,b,c) |
SQL>substr(a,b,c) 从a中，第b位开始,取c位 SQL> select substr('Hello World',3,4) from dual;
SUBS ---- llo |
|
|
length 字符数 lengthb字节数 |
select length('Hello World') 字符,lengthb('Hello World') 字节 from dual; 字符 字节 ---------- ---------- 11 11 select length('中国') 字符,lengthb('中国') 字节from dual 字符 字节 ---------- ---------- 2 4 |
|
|
-instr(a,b) 在a中，查找b |
select instr('Hello World','ll') from dual; INSTR('HELLOWORLD','LL') ------------------------ 3 |
|
|
lpad 左填充 rpad右填充 |
select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 左 右 ---------- ---------- ******abcd abcd****** |
|
|
trim |
去掉前后指定的字符 select trim('H' from 'Hello WorldH') from dual; TRIM('H'FR ---------- ello World |
|
|
replace 替换 |
select replace('Hello World','l','*') from dual; REPLACE('HE ----------- He**o Wor*d
|
|
|
-四舍五入 |
select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三 round(45.926,-1) 四, round(45.926,-2) 五 from dual; 一 二 三 四 五 ---------- ---------- ---------- ---------- ---------------------------------------- 45.92 45.9 45 40 0 |
|
|
当前系统时间date
不允许日期 + 日期 |
select sysdate from dual; SYSDATE -------------- 17-9月 -14 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') from dual; 员工的工龄： 天 星期 月 年 SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp; |
|
|
add_months |
select add_months(sysdate,78) from dual; 加上n个月后的日期 |
|
|
last_day |
select last_day(sysdate) from dual; 本月的最后一天 |
|
|
next_day(sysdate,'星期四') |
SQL> select next_day(sysdate,'星期四') from dual;
NEXT_DAY(SYSDA -------------- 18-9月 -14 |
|
|
日期的四舍五入 |
select round(sysdate,'month'), round(sysdate,'year') from dual;
|
|
|
|
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI ---------------------------------- 2014-09-17 14:40:48今天是星期三 |
|
|
|
查询员工的薪水： 两位小数 千位符，货币代码 select to_char(sal,'L9,999.99') from emp; |
|
|
当a=null时，返回c；否则返回b |
SQL> select nullif('abc','abc') 值 from dual; 值 --- SQL> select nullif('abc','abcd') 值 from dual; 值 --- abc
|
|
|
coalesce 返回第一个为非空的值 |
select comm,sal,coalesce(comm,sal) 值 from emp; |
|
|
nvl2(a,b,c) |
当a为空时，返回c，否则返回b |
|
|
Case |
SQL> --给员工涨工资，总裁1000 经理800 其他400 SQL> select ename,job,sal 涨前, 2 case job when 'PRESIDENT' then sal+1000 3 when 'MANAGER' then sal+800 4 else sal+400 5 end 涨后 6 from emp; 第二种方式 SQL>select ename,job,sal 涨前, 2 decode(job,'PRESIDENT',sal+1000, 3 'MANAGER',sal+800, 4 sal+400) 涨后 5 from emp; |
|
|
多行函数 |
|
|
|
Sum（） |
select sum(sal) from emp; |
|
|
Count(*) |
select count(*) from emp; |
|
|
平均工资 |
select sum(sal)/count(*) 一, avg(sal) 二 from emp; -null值 4: 组函数自动滤空; -null值 5: 组函数自动滤空; 可以嵌套滤空函数来屏蔽滤空功能 |
|
|
Having 分组后的过滤 |
select deptno,avg(sal) 2 from emp 3 group by deptno 4 having avg(sal)>2000; select deptno,avg(sal) 2 from emp 3 group by deptno 4 having avg(sal)>2000;
|
|
|
group by的增强 |
SQL> /* SQL> group by的增强 SQL> select deptno,job,sum(sal) from emp group by deptno,job SQL> + SQL> select deptno,sum(sal) from emp group by deptno SQL> + SQL> select sum(sal) from emp SQL> SQL> == SQL> SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job) SQL> SQL> group by rollup(a,b) SQL> = SQL> group by a,b SQL> + SQL> group by a SQL> + SQL> group by null SQL> */ SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600
DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 SQL> break on deptno skip 2 SQL> break on null |
|
|
多表查询的理解 |
|
|
|
等值连接 |
select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno; |
|
|
外连接 |
|
|
|
左外连接： 右外连接： |
左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含 SQL> 写法：where e.deptno=d.deptno(+) SQL> 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含 SQL> 写法：where e.deptno(+)=d.deptno |
|
|
自连接 |
自连接: 通过表的别名，将同一张表视为多张表 自连接：不是适合操作大表 SQL> --层次查询 SQL> select level,empno,ename,sal,mgr 2 from emp 3 connect by prior empno=mgr 4 start with mgr is null 5 order by 1;
LEVEL EMPNO ENAME SAL MGR ---------- ---------- ---------- ---------- ---------- -------------------------- 1 7839 KING 5000 2 7566 JONES 2975 7839 2 7698 BLAKE 2850 7839 2 7782 CLARK 2450 7839 3 7902 FORD 3000 7566 3 7521 WARD 1250 7698 3 7900 JAMES 950 7698 3 7934 MILLER 1300 7782 3 7499 ALLEN 1600 7698 3 7788 SCOTT 3000 7566 3 7654 MARTIN 1250 7698
LEVEL EMPNO ENAME SAL MGR ---------- ---------- ---------- ---------- ---------- -------------------------- 3 7844 TURNER 1500 7698 4 7876 ADAMS 1100 7788 4 7369 SMITH 800 7902 |
|
