[ where condition ];
select distinct [as] alias from
;
算术表达式
sql中不区分大小写，默认转化为大写，使用引号照原样输出
语句分行书写使用/结尾
别名中有特殊字符要加双引号
字符要加单引号
连接字符串|| 相当于concat()函数
<> ^= != 不等于
where：
any,all,like，between..and..,not,and,or
not like,not in,not between..and..
like: % 匹配0个或多个
_ 匹配一个
如果字符中包含%或_的话用转义字符
SQL>select * from a1 where name like '\%%' escape '\';
运算优先级：() , * / + -
||, concat()
like/not like, in, between..and
not, and ,or （and的优先级比or高）
order by 默认升序排序 desc关键字降序 asc
order by 后接列名或数字（第几列），可对多列同时排序，order by 放在语 句的最后
&符号替换 &&替换后下面的相同变量不需要再输入
undifine取消 define可以定义
set define off 关闭&替换功能，原意表示
select "coda & sid" from dual
set define on 打开&替换功能
set verify on|off 是否打开新语言
！！！关于字符中有单引号
SQL>select q'' "q<>" from dual;
SQL>select q'Xscott's password is tigerX' "qX" from dual;
函数：单行函数和多行函数
单行：
字符：upper,lower,initcap,concat,substr(str,m,n),instr(str,s,m,n),lpad(col,n,'*'),rpad(col,n,'*'),trim('c' from 'caaac'),ltrim,rtrim,replace(str,a1,a2),length,lengthb,lengthc
trim(str) 去掉str两边的空格
trim(leading 'c' from 'caaac') 去掉前面的
trim(trailing 'c' from 'caaac') 后面的
trim(both 'c' from 'caaac') 都去
ltrim('abacdeab','ab')=cdeab 有a或b的都被截取掉
length char
lengthb byte
中文：单字节：2 ZHS32gbk
多字节：3 al32utf8
修改为中文环境export NLS_LANG='simplified chinese_china.al32utf8'
lengthc unicode
数字：round,abs,ceil,floor,trunc..
ceil(a) 向上取整
floor(a) 向下取整
trunc(a,n) 截取
trunc 也可用来求一年的第一天，一个月或周的第一天
日期：sysdate,months_between,adds_mounth,last_day,next_day,round,trunc...
日期round以日周月年四舍五入
SQL>alter session set nls_date_format='yyyy-mm-dd';
SQL>alter system set nls_date_format='yyyy-mm-dd' scope=spfile;
[root@oracle dbs]# strings spfilekangle.ora 查看参数文件内容
空值函数（条件函数）：nvl,nvl2,nullif,coalesce,decode,case
nvl(m,n) m为空则用n填充
nvl2(m,x,y) m为空返回y，不为空返回x
nullif(x,y) 如果x=y返回空值
coalesce(x,y,z,...) 返回第一个不为空的值
分支函数decode(a,a1,r1,a2,r2...iffalse) 实现if-then-else逻辑， 如果a和a1相等则返回r1,否则比较a2,f返回r2,依次类推
case exp
when comp1 then result1
when comp2 then result2
else iffalse
end
case
when condition1 then result1
when condition2 then result2
else iffalse
end
多行函数：
avg(),min(),max(),count(),sum() 不包含空值
count(*)包括空值
group by column ：select子句中的属性需要分组
group by 后面不能有where子句，用having代替，对单行过滤的where 子句要放到group by前面
having用来在分组函数后面过滤条件
rollup(a) 滚动分组 分n+1次组
相当于先按a分组，然后group by0不分组求平均
rollup(a,b) = groupby a,groupby b,groupby 0
cube 交叉分组 分2^n次组
cube(a)=groupby 0,group by a
cube(a,b)=groupby 0,groupby b,groupby a,groupby ab
grouping set(a,b) 合并分组
等于groupby a unionall groupby b
grouping(a) 检查是否按照a列分组，放在select子句中
union 会去掉重复的行，会排序
union all 不去掉重复的行，不会排序
minus 差
intersect 交
用order by排序结果时必须时第一个查询中的列
子查询：
可以在where子句中也可以在from子句中，还可以在select子句中
子查询不能放在insert子句values关键字后面
exists(a) exists子句只返回真假
层次查询：
select level column from table start with ...
connect by prior
多表查询：
select column from table1 alias1,table2 alias2 where...
联接：
自然联接：natural join/join..using(column)/join..on(condition)
join using(column) 列名一样，数据类型不一样的联接
join..on(condition) 列名可以不一样，数据类型可以不一样，但内容一样
多表联接
自联结
左联接 a.id(+)=b.id left [outer] join
右联接 a.id=b.id(+) right [outer] join
全联接 full [outer] join
交叉联接 cross join
补充： 排序
row_number() over (partion by .. order by..) 按什么分组，在组内排序
rank()..over.. 跳跃排序，即两个2接下来是4
dense_rank()..over.. 连续排序 两个2下一个还是3
lag(a,b,c)..over.. a的不超过b的数字的行用c填充
count(0)..over.. 统计每个分组内的总行数
select deptno,ename,sal from (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from emp) where sal_order<4; #显示每个部门工资前3位
select deptno,ename,sal,row_number() over (order by sal desc) as sal_order from emp; #按sal排序
select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) as salnum,count(0) over(partition by deptno) cnt from emp; #按部门分组统计顺序并统计每隔部门人数
select deptno,ename,sal,lag(sal,1,null) over(partition by deptno order by sal desc) as salnum from emp;
2+3
----------
5
SQL> select sysdate from dual;
select sysdate from dual;
SYSDATE
---------
19-NOV-13 #dd-mon-yy
#空值null不能进行运算
SQL> select ename,sal,comm,sal+comm as sa from emp;
select ename,sal,comm,sal+comm as sa from emp;
ENAME SAL COMM SA
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 1500
ADAMS 1100
ENAME SAL COMM SA
---------- ---------- ---------- ----------
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
#函数nvl(comm,0)表示如果comm为空则赋值0
SQL> select ename,sal,comm,sal+nvl(comm,0) as sa from emp;
select ename,sal,comm,sal+nvl(comm,0) as sa from emp;
ENAME SAL COMM SA
---------- ---------- ---------- ----------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 1500 0 1500
ADAMS 1100 1100
ENAME SAL COMM SA
---------- ---------- ---------- ----------
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 rows selected.
#去除重复的列
SQL> select distinct deptno from emp;
select distinct deptno from emp;
DEPTNO
----------
30
20
10
SQL> select unique deptno from emp;
select unique deptno from emp;
DEPTNO
----------
30
20
10
SQL> select ename,sal,(sal+nvl(comm,0))*12 nianxin from emp;
select ename,sal,(sal+nvl(comm,0))*12 nianxin from emp;
ENAME SAL NIANXIN
---------- ---------- ----------
SMITH 800 9600
ALLEN 1600 22800
WARD 1250 21000
JONES 2975 35700
MARTIN 1250 31800
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
ENAME SAL NIANXIN
---------- ---------- ----------
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
14 rows selected.
#如果别名中有特殊字符用引号引起来
SQL> select ename,sal,(sal+nvl(comm,0))*12 "nian xin" from emp;
select ename,sal,(sal+nvl(comm,0))*12 "nian xin" from emp;
ENAME SAL nian xin
---------- ---------- ----------
SMITH 800 9600
ALLEN 1600 22800
WARD 1250 21000
JONES 2975 35700
MARTIN 1250 31800
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
ENAME SAL nian xin
---------- ---------- ----------
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
14 rows selected.
SQL> select ename||' work is '||job from emp;
select ename||' work is '||job from emp;
ENAME||'WORKIS'||JOB
----------------------------
SMITH work is CLERK
ALLEN work is SALESMAN
WARD work is SALESMAN
SQL> select concat(concat(ename,' is work '),job) from emp;
select concat(concat(ename,' is work '),job) from emp;
CONCAT(CONCAT(ENAME,'ISWORK'
----------------------------
SMITH is work CLERK
ALLEN is work SALESMAN
#any与all
SQL>select ename,sal from emp where sal>any(1000,2000,3000);
select ename,sal from emp where sal>any(1000,2000,3000);
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
FORD 3000
ENAME SAL
---------- ----------
MILLER 1300
12 rows selected.
SQL>select ename,sal from emp where sal>all(1000,2000,3000);
select ename,sal from emp where sal>all(1000,2000,3000);
ENAME SAL
---------- ----------
KING 5000
#like转义
SQL>select * from a1 where name like '\%%' escape '\';
select * from a1 where name like '\%%' escape '\';
NAME
----------
%FDS
SQL>select * from a1 where name like '\_%\%%' escape '\';
select * from a1 where name like '\_%\%%' escape '\';
NAME
----------
_FD%S
SQL>select ename,sal from emp where sal in(1000,2000,3000,5000);
select ename,sal from emp where sal in(1000,2000,3000,5000);
ENAME SAL
---------- ----------
SCOTT 3000
KING 5000
FORD 3000
SQL>select ename,sal from emp where sal between 1000 and 2000;
select ename,sal from emp where sal between 1000 and 2000;
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
6 rows selected.
#order by
SQL>select ename,sal from emp where ename like 'A%' order by sal desc;
select ename,sal from emp where ename like 'A%' order by sal desc;
ENAME SAL
---------- ----------
ALLEN 1600
ADAMS 1100
SQL>select ename,sal from emp where ename like 'A%' order by 2 desc;
ENAME SAL
---------- ----------
ALLEN 1600
ADAMS 1100
________________________________________________
————————————————————————————————————————————————
SQL>select employee_id,last_name,phone_number from employees where last_name='&LASTNAME' and employee_id=&EMP;
SQL>select first_name,last_name from employees where last_name like '%&&SEARCH%' and first_name like '%&SEARCH%';
select first_name,last_name from employees where last_name like '%&&SEARCH%' and first_name like '%&SEARCH%';
Enter value for search: S
S
old 1: select first_name,last_name from employees where last_name like '%&&SEARCH%' and first_name like '%&SEARCH%'
new 1: select first_name,last_name from employees where last_name like '%S%' and first_name like '%S%'
SQL>undefine SEARCH
SQL>set define off;
SQL>select '&hello' from dual;
select '&hello' from dual;
'&HELL
------
&hello
SQL>set define on;
set define on;
——————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————
#函数
SQL>select upper('hello'),lower('ARE'),initcap('you') from dual;
select upper('hello'),lower('ARE'),initcap('you') from dual;
UPPER LOW INI
----- --- ---
HELLO are You
SQL>select concat('hello','word') from dual;
select concat('hello','word') from dual;
CONCAT('H
---------
helloword
SQL>select substr('abcde',2,2) from dual;
SU
--
bc
SQL>select substr('abcde',-2,2) from dual;
select substr('abcde',-2,2) from dual;
SU
--
de
SQL>select instr('abcdcdc','c') from dual;
select instr('abcdcdc','c') from dual;
INSTR('ABCDCDC','C')
--------------------
3
SQL>select instr('abcdcdc','c',1,2) from dual;
select instr('abcdcdc','c',1,2) from dual;
INSTR('ABCDCDC','C',1,2)
------------------------
5
SQL>select length('chengdu'),lengthb('chengdu'),lengthc('chengdu') from dual;
select length('chengdu'),lengthb('chengdu'),lengthc('chengdu') from dual;
SQL>select * from nls_database_parameters;
select * from nls_database_parameters;
SQL>col LPAD(DNAME,15,'*') for a25
col LPAD(DNAME,15,'*') for a25
SQL>select deptno,lpad(dname,15,'*'),loc from dept;
select deptno,lpad(dname,15,'*'),loc from dept;
DEPTNO LPAD(DNAME,15,'*') LOC
---------- ------------------------- -------------
10 *****ACCOUNTING NEW YORK
20 *******RESEARCH DALLAS
30 **********SALES CHICAGO
40 *****OPERATIONS BOSTON
SQL>col RPAD(DNAME,15,'*') for a25
col RPAD(DNAME,15,'*') for a25
SQL>select deptno,rpad(dname,15,'*'),loc from dept;
select deptno,rpad(dname,15,'*'),loc from dept;
DEPTNO RPAD(DNAME,15,'*') LOC
---------- ------------------------- -------------
10 ACCOUNTING***** NEW YORK
20 RESEARCH******* DALLAS
30 SALES********** CHICAGO
40 OPERATIONS***** BOSTON
SQL>select trim(leading 'a' from 'abcdaaaa') from dual;
select trim(leading 'a' from 'abcdaaaa') from dual;
TRIM(LE
-------
bcdaaaa
SQL>select trim(trailing 'a' from 'abcdaaaa') from dual;
select trim(trailing 'a' from 'abcdaaaa') from dual;
TRIM
----
abcd
SQL>select trim(both 'a' from 'abcdaaaa') from dual;
select trim(both 'a' from 'abcdaaaa') from dual;
TRI
---
bcd
SQL>select ltrim('abacdefgab','ab') from dual;
select ltrim('abacdefgab','ab') from dual;
LTRIM('
-------
cdefgab
SQL>select rtrim('abacdefgab','ab') from dual;
select rtrim('abacdefgab','ab') from dual;
RTRIM('A
--------
abacdefg
SQL>select replace('ababcd','a','gg') from dual;
REPLACE(
--------
ggbggbcd
SQL>select replace('ababcd','a') from dual;
select replace('ababcd','a') from dual;
REPL
----
bbcd
______________________________________
——————————————————————————————————————
SQL>select round(123.45,1) from dual;
select round(123.45,1) from dual;
ROUND(123.45,1)
---------------
123.5
SQL>select round(456.345,-1) from dual;
select round(456.345,-1) from dual;
ROUND(456.345,-1)
-----------------
460
SQL>select abs(-123) from dual;
select abs(-123) from dual;
ABS(-123)
----------
123
SQL>select ceil(123.00002) from dual;
select ceil(123.00002) from dual;
CEIL(123.00002)
---------------
124
SQL>select floor(123.000001) from dual;
select floor(123.000001) from dual;
FLOOR(123.000001)
-----------------
123
SQL>select trunc(12.566,2) from dual;
select trunc(12.566,2) from dual;
TRUNC(12.566,2)
---------------
12.56
SQL>select trunc(sysdate,'YYYY') from dual;
select trunc(sysdate,'YYYY') from dual;
TRUNC(SYS
---------
01-JAN-13
SQL>select trunc(sysdate,'mm') from dual;
select trunc(sysdate,'mm') from dual;
TRUNC(SYS
---------
01-NOV-13
SQL>select trunc(sysdate,'day') from dual;
select trunc(sysdate,'day') from dual;
TRUNC(SYS
---------
17-NOV-13
——————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————
SQL>select ename,hiredate,months_between(sysdate,hiredate) from emp;
SQL>select ename,hiredate,add_months(hiredate,3) from emp;
SQL>select last_day(sysdate) from dual;
SQL>select next_day(sysdate,6) from dual;
select next_day(sysdate,6) from dual;
NEXT_DAY(
---------
22-NOV-13
SQL>select next_day(sysdate,'fri') from dual;
select next_day(sysdate,'fri') from dual;
NEXT_DAY(
---------
22-NOV-13
——————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————
转换函数
SQL>select to_date('19911124','yyyymmdd') from dual;
select to_date('19911124','yyyymmdd') from dual;
TO_DATE('
---------
24-NOV-91
SQL>select to_date(19911124,'yyyymmdd') from dual;
select to_date(19911124,'yyyymmdd') from dual;
TO_DATE(1
---------
24-NOV-91
SQL>select to_char(00001) from dual;
select to_char(00001) from dual;
T
-
1
SQL>select to_char(00001,'0999999') from dual;
select to_char(00001,'0999999') from dual;
TO_CHAR(
--------
0000001
SQL>select to_char(8765,'$9,999.99') from dual;
select to_char(8765,'$9,999.99') from dual;
TO_CHAR(87
----------
$8,765.00
——————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————
SQL>select nvl2('abc','1','2') from dual; 为空返回2，不为空返回1
select nvl2('abc','1','2') from dual;
N
-
1
SQL>select nullif('1','1') from dual;
select nullif('1','1') from dual;
N
-
SQL> select deptno,ename,sal,decode(deptno,10,sal+10000,20,sal+20000,sal+30000) from emp;
SQL> select deptno,ename,sal,
2 case deptno
3 when 10 then sal+10000
4 when 20 then sal+20000
5 else sal+30000
6 end
7 as tsal
8 from emp;
SQL> list
list
1 select ename,sal,
2 case
3 when sal<1500 then 1
4 when sal>=1500 and sal<3000 then 2
5 when sal>=3000 then 3
6 end
7 as sallevel
8* from emp
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
SQL> select deptno,avg(sal) from emp where deptno=20 group by deptno having
avg(sal) > 2000;
select deptno,avg(sal) from emp where deptno=20 group by deptno having
avg(sal) > 2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
SQL> select deptno,avg(sal) from emp group by rollup(deptno);
select deptno,avg(sal) from emp group by rollup(deptno);
SQL> select ename,sal from emp where sal=(select max(sal) from emp);
select ename,sal from emp where sal=(select max(sal) from emp);
ENAME SAL
---------- ----------
KING 5000
SQL> select ename,empno from emp where empno in (select mgr from emp);
select ename,empno from emp where empno in (select mgr from emp);
ENAME EMPNO
---------- ----------
FORD 7902
BLAKE 7698
KING 7839
JONES 7566
SCOTT 7788
CLARK 7782
6 rows selected.
SQL> select level,lpad(ename,length(ename)+level*2-2,'_') ee,empno,mgr from
emp start with empno=7839 connect by prior empno=mgr;
select level,lpad(ename,length(ename)+level*2-2,'_') ee,empno,mgr from emp
start with empno=7839 connect by prior empno=mgr;
SQL> select level,lpad(ename,length(ename)+level*2-2,'_') ee,empno,mgr from
emp start with empno=7788 connect by prior empno=mgr;
select level,lpad(ename,length(ename)+level*2-2,'_') ee,empno,mgr from emp
start with empno=7788 connect by prior empno=mgr;
LEVEL EE EMPNO MGR
---------- ------------------------- ---------- ----------
1 SCOTT 7788 7566
2 __ADAMS 7876 7788
#反过来查询领导层次
SQL> select level,ename,empno,mgr from emp start with empno=7369 connect by
prior mgr=empno;
select level,ename,empno,mgr from emp start with empno=7369 connect by prior
mgr=empno;
LEVEL ENAME EMPNO MGR
---------- ---------- ---------- ----------
1 SMITH 7369 7902
2 FORD 7902 7566
3 JONES 7566 7839
4 KING 7839
自然联接：
SQL> select * from dept natural join emp;
SQL> select * from dept join emp using(deptno);
SQL> select * from dept d join emp e on(d.deptno=e.deptno);
SQL> select dname,ename,sal,grade from dept d natural join emp e join salgrade
s on e.sal between losal and hisal;
select dname,ename,sal,grade from dept d natural join emp e join salgrade s on
e.sal between losal and hisal;
DNAME ENAME SAL GRADE
-------------- ---------- ---------- ----------
RESEARCH SMITH 800 1
SALES JAMES 950 1
RESEARCH ADAMS 1100 1
SALES WARD 1250 2
SALES MARTIN 1250 2
ACCOUNTING MILLER 1300 2
SALES TURNER 1500 3
SALES ALLEN 1600 3
ACCOUNTING CLARK 2450 4
SALES BLAKE 2850 4
RESEARCH JONES 2975 4
DNAME ENAME SAL GRADE
-------------- ---------- ---------- ----------
RESEARCH SCOTT 3000 4
RESEARCH FORD 3000 4
ACCOUNTING KING 5000 5
14 rows selected.
SQL> select e.ename,e.empno,e.mgr,m.ename from emp e join emp m on
e.mgr=m.empno;
select e.ename,e.empno,e.mgr,m.ename from emp e join emp m on e.mgr=m.empno;
ENAME EMPNO MGR ENAME
---------- ---------- ---------- ----------
FORD 7902 7566 JONES
SCOTT 7788 7566 JONES
JAMES 7900 7698 BLAKE
TURNER 7844 7698 BLAKE
MARTIN 7654 7698 BLAKE
WARD 7521 7698 BLAKE
ALLEN 7499 7698 BLAKE
MILLER 7934 7782 CLARK
ADAMS 7876 7788 SCOTT
CLARK 7782 7839 KING
BLAKE 7698 7839 KING
ENAME EMPNO MGR ENAME
---------- ---------- ---------- ----------
JONES 7566 7839 KING
SMITH 7369 7902 FORD
