【ORACLE数据库基本语法】

一、基本语法

1.null不等于0或空白。
空值函数：nvl()
2.列别名：直接跟在列名后面，用空格隔开；也可在列与别名之间用as增加可读性
3.连接列字符串:使用||作连接作用 优点：批量生成脚本
select ename || ‘salary is ’ || sal from emp;
select ename || q’[‘s salary is ]’ || sal from emp; #特殊字符进行转义
4.去掉重复行：distinct关键字 （默认降序）
5.转换大小写函数：lower() upper() initcap() #首字母大写
字符处理函数：
concat(,)#连接函数，仅限制两个列，使用函数嵌套，可以实现多个列的连接 concat(concat(,),)
eg:select concat(concat(ename,q’[‘s salary is]’),sal) from emp;
substr()#截断函数
eg:select substr(‘hellowrold’,6,5) from dual;
select substr(‘hellowrold’,6) from dual;
select ename from emp where ename like ‘_A%’;
select ename from emp where substr(ename,2,1)=‘A’;
length()#求字符长度函数
lengthb()#求字节长度函数
instr()#求某个字符的位置
trim()#去掉两边的符合这个字符
replace()#替换函数
round() #四舍五入函数
trunc()#截取函数
mod() #取余函数 适用于数据规整的时候
abs() #绝对值函数
months_between()#相差多少月
select sysdate from dual;
show paiameter nls;
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’
to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)#日期转换字符
round(sysdate,‘mm’) #四舍五入日期
add_months() #增加月份函数
6.比较运算符：>= > < <= != <>
between … and … 等价于 >= and <=
7.模糊搜索：like （like ‘%a’）
%表示任意长度的任意字符
_表示1位任意字符
is null 过滤为空值
is not null 过滤为非空值
8.and or not 优先级：not最优先，and其次，or最后
9.升序降序：order by asc(desc) 列名
select ename,sal from emp order by 1;#先查再排序
10.替代变量：&变量名(临时变量：仅当次生效) &&变量名(局部变量：针对当前会话生效)
定义变量：define 变量名=值 (查看有哪些变量define)
(如果定义的变量长期有效，即重新打开会话依然有效，就需要将define 变量名=值 写入$ORACLE_HOME/sqlplus/admin/glogin.sql文件中)
调用变量：&&变量名 (实现全局变量)
11.类型转换：显示转换、隐式转换
隐式转换：orace自己给转换
显式转换：调用转换函数进行转换 to_char() to_date() to_number()
日期转字符：to_char()
to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)
数字转字符：to_char()
to_char(sal,’$99,999.99’)
to_char(sal,‘L99,999.99’) （L代表local）
字符转数字：to_number()
时间转日期：to_date()
to_date(‘2015,11,19’,‘yyyy,mm,dd’)
12.分组函数：单行分组函数、多行分组函数
单行分组函数:avg() count() max() min() sum()
多行分组函数：group by 列名 having 条件 (降序)
eg:select deptno,max(sal) from emp group by deptno having deptno<>10 order by 2;
13.dual表：共享表，只有一行数据，输入啥返回啥，用于测试

SQL> select * from dual；
D
-
X

14.设置行大小：set linesize 100; 设置页面大小 ：set pagesize 100;
15.case条件表达式(列筛选):case when then else end 别名
decode(deptno,10,sal1,1,20,sal1.2,30,sal*1.3,sal)
15.多表连接：自然连接、内连接（等值连接）、外连接（左外连接，又外连接、全外连接）、交叉连接(笛卡尔积)
自然连接natural join：基于两个表的同名列
eg:select ename,dname from emp natural join dept;
内连接inner join：等值连接
使用join on解决问题：
eg1:select e1.ename,e2.ename from test e1 join test e2 on e1.id1=e2.id2;
eg2:select e1.ename,e2.ename from test e1 join test e2 using(id);
外连接：
左外连接left join on：
eg:select * from t1 left join t2 on t1.id1=t2.id2;
右外连接right join on:
eg:select * from t1 right join t2 on t1.id1=t2.id2;
交叉连接：所有可能性
补充：三张表的连接：将两张表的连接结果作为一张表与第三张表再进行连接
16.子查询：把查询的结果当成条件、表再做查询

SQL> select ename,sal from emp where sal>(select sal from
  2  emp where ename='SCOTT');
ENAME            SAL
---------- ---------
KING         5000.00

有单行子查询(查询的结果仅返回一行值,标量子查询(且返回的列为一列))和多行子查询(查询的结果返回多行值，in any all)之分。
in:在，eg:a in (1,3,7)#或的关系 等价于 a=1 or a=3 or a=7
not in:不在 eg:a not in (1,3,7) 等价于 (a!=1 and a!=3 and a!=7)

SQL> select ename from emp where
  2  empno in (select mgr from emp);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
6 rows selected

SQL> select ename from emp where
  2  empno not in (select mgr from emp where mgr is not null);
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
8 rows selected

any:大于最小的，小于最大的 eg:= any(1,3,5) eg:> any(1,3,5) 等价于 > 1 eg:< any(1,3,5) 等价于 < 5

SQL> select ename,sal from emp where
  2  sal > any (2500,3000);
ENAME            SAL
---------- ---------
JONES        2975.00
BLAKE        2850.00
SCOTT        3000.00
KING         5000.00
FORD         3000.00

SQL> select ename,sal from emp where
  2  sal > 2500;
ENAME            SAL
---------- ---------
JONES        2975.00
BLAKE        2850.00
SCOTT        3000.00
KING         5000.00
FORD         3000.00

all:大于最大的，小于最小的 eg:= all(1,3,5) 等价于 a=1 and a=3 and a=5 eg:> all(1,3,5) 等价于 >5 eg:< all(1,3,5) 等价于 < 1

SQL> select ename,sal from emp where
  2  sal > all (2500,3000);
ENAME            SAL
---------- ---------
KING         5000.00

SQL> select ename,sal from emp where
  2  sal > 3000;
ENAME            SAL
---------- ---------
KING         5000.00
rownum:伪列
SQL> select sal from (select sal from emp order by sal) 
  2  where rownum=1;
      SAL
---------
   800.00

注：在主查询查询之前，会先查询子查询，子查询会执行一次
17.集合操作：并、交
并集：(1)union all：不可以去重

SQL> select 10,max(sal) from emp where deptno=10
  2  union all
  3  select 20,max(sal) from emp where deptno=20
  4  union all
  5  select 30,max(sal) from emp where deptno=30
  6  union all
  7  select 20,max(sal) from emp where deptno=20;
        10   MAX(SAL)
---------- ----------
        10       5000
        20       3000
        30       2850
        20       3000

(2)union：可以去重

SQL> select 10,max(sal) from emp where deptno=10
  2  union
  3  select 20,max(sal) from emp where deptno=20
  4  union
  5  select 30,max(sal) from emp where deptno=30
  6  union
  7  select 20,max(sal) from emp where deptno=20;
        10   MAX(SAL)
---------- ----------
        10       5000
        20       3000
        30       2850

交集：(1)intersect

SQL> create table e1 as select empno,ename,deptno,sal from emp where deptno in (10,20);
Table created

SQL>  create table e2 as select empno,ename,deptno,sal from emp where deptno in (10,30);
Table created
SQL> select * from e1
  2  intersect
  3  select * from e2;
EMPNO ENAME      DEPTNO       SAL
----- ---------- ------ ---------
 7782 CLARK          10   2450.00
 7839 KING           10   5000.00
 7934 MILLER         10   1300.00

差集：(1)minus

SQL> select * from e1
  2  minus 
  3  select * from e2;
EMPNO ENAME      DEPTNO       SAL
----- ---------- ------ ---------
 7369 SMITH          20    800.00
 7566 JONES          20   2975.00
 7788 SCOTT          20   3000.00
 7876 ADAMS          20   1100.00
 7902 FORD           20   3000.00
SQL> select * from e2
  2  minus
  3  select * from e1;
EMPNO ENAME      DEPTNO       SAL
----- ---------- ------ ---------
 7499 ALLEN          30   1600.00
 7521 WARD           30   1250.00
 7654 MARTIN         30   1250.00
 7698 BLAKE          30   2850.00
 7844 TURNER         30   1500.00
 7900 JAMES          30    950.00
6 rows selected