p where ename like '%/_%' escape '/'; 转义，转义的字符可以是任意的字符
select ename from emp where ename like '%/%%' escape '/';
select ename from emp where ename like '%1%%' escape '1';
字符串增减单引号，日期的注意格式
select sysdate from dual;
select ename,hiredate from emp where hiredate='1981-12-03';过滤时间时，确认系统的时间是什么样的
c/_/% 替换操作
逻辑运算
and,or,not
and 要求所有条件都为true
or 任意一个条件满足都为true
操作符优先级规则
7 not 8 and 9 or
排序数据使用order by asc 升序默认 desc 降序
***空值排序永远都是第一条，(null)***
***排序列用别名排序***
select ename,sal,comm,sal+nvl(comm,0) from emp order by sal+nvl(comm,0);两次运算，如何一次一运算，修改为如下
select ename,sal,comm,sal+nvl(comm,0) ts from emp order by ts;
***排序用列所在的数字位数***
select ename,sal,comm,sal+nvl(comm,0) from emp order by 4；
select ename,ename||' '||sal ename from emp order by 2;列名相同
多列排序
select ename,sal from emp order by sal desc;
替代变量
临时变量& 调用替代变量用&&(永久变量)
SQL> select ename,sal from emp where empno=&e;
Enter value for e: 7788
old 1: select ename,sal from emp where empno=&e
new 1: select ename,sal from emp where empno=7788
ENAME SAL
---------- ----------
SCOTT 3000
SQL> select &e,sal from emp where &&e=7788; //需要输入两次empno，sql语句执行从右到左，两次调用
Enter value for e: empno
Enter value for e: empno
old 1: select &e,sal from emp where &&e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
SQL> undefine e
SQL> select &&e,sal from emp where &e=7788;
Enter value for e: empno
old 1: select &&e,sal from emp where &e=7788
new 1: select empno,sal from emp where empno=7788
EMPNO SAL
---------- ----------
7788 3000
define 定义一个变量
define v1=7788
select ename,sal from emp where empno=&v1;
***set verfify on/off 显示新旧值设置
undefine 取消一个变量
undefine v1
unset NLS_LANG
11.单行函数
处理数据
单行函数：每行返回一个结果
字符函数：lower,uppper,initcap(注意此函数)
SQL> select initcap('king_scott') from dual;
INITCAP('K
----------
King_Scott
SQL> l
1* select initcap('king_scott') from dual
SQL> c/_/0
1* select initcap('king0scott') from dual
SQL> /
INITCAP('K
----------
King0scott
字符处理类：
concat 连接函数，只能连接两个表达式，结果为字符型
SQL> select concat(ename,'is work'),job from emp where deptno=10;
CONCAT(ENAME,'ISW JOB
----------------- ---------
CLARKis work MANAGER
KINGis work PRESIDENT
MILLERis work CLERK
SQL> select concat(concat(ename,'is work'),job)from emp where deptno=10; 函数嵌套
CONCAT(CONCAT(ENAME,'ISWOR
--------------------------
CLARKis workMANAGER
KINGis workPRESIDENT
MILLERis workCLERK
substr(expr,m,n) m从哪开始，n取多少个，n>0
SQL> select ename from emp where substr(ename,2,1)='A';
ENAME
----------
WARD
MARTIN
JAMES
SQL> select ename from emp where substr(ename,-2,1)='E';
ENAME
----------
ALLEN
JONES
TURNER
JAMES
MILLER
避免这种写法select ename from emp where substr(ename,1)='KING';
length 长度函数
SQL> select length(ename),ename from emp;
LENGTH(ENAME) ENAME
------------- ----------
5 SMITH
5 ALLEN
4 WARD
5 JONES
6 MARTIN
5 BLAKE
5 CLARK
5 SCOTT
4 KING
6 TURNER
5 ADAMS
lengthb,lengthc,length2,length4区别详见官网，中文能看见区别
SQL> select length(ename),lengthb(ename),lengthc(ename) from emp where deptno=10;
LENGTH(ENAME) LENGTHB(ENAME) LENGTHC(ENAME)
------------- -------------- --------------
5 5 5
4 4 4
6 6 6
instr查找指定字符在哪里出现
SQL> select instr(ename,'A'), ename from emp;
INSTR(ENAME,'A') ENAME
---------------- ----------
0 SMITH
1 ALLEN
2 WARD
0 JONES
2 MARTIN
3 BLAKE
3 CLARK
0 SCOTT
0 KING
0 TURNER
1 ADAMS
SQL> select ename from emp wh |