--过滤数据(比较运算符的使用)where子句中的数值值不用加单引号,字符值/日期值必须加单引号
select * from emp where deptno=20;
select * from emp where job='MANAGER';
select * from emp where hiredate='02-4-81';
--比较运算符(> ,= ,>= ,<= ,<>, !=)
--范围查询between ..and..,in
select * from emp where sals between 1000 and 3000;
select * from emp where sals>=1000 and sals<=3000;
select * from emp where job in('MANAGER','SALESMAN');
select * from emo where job='MANAGER' or job='SALESMAN';
--模糊查询like 通配符%后面匹配多个字符,通配符_后面匹配单个字符
select * from emp where ename like 'J%';
select * from emp where ename like 'J_';
--模糊查询中特殊字符的处理,使用escape选项和转义字符实现\
select * from emp where ename like 'G\_%' escape '\';--查询以G_开头的姓名
--判断空值is null,is not null
select * from emp where comm is null;
select * from emp where comm is not null;
--过滤数据(逻辑运算符) and(与),or(或),not(否),优先级not>and>or
select * from emp where job='MANAGER' and deptno=10;
select * from emp where deptno=30 or deptno=10;
select * from emp where deptno not in(10,30);
select empno,ename,job,sal from emp where (sal>2000 or deptno=30)and job not in('MANAGER');
--排序数据(单列,多列,别名排序),order by ,当select 语句包含多个子句时(where,group by,having,order by),
--order by 必须是最后一条语句,ASC升序,DESC降序,默认升序。
select * from emp order by sal DESC;
select * from emp order by sal DESC;
select * from emp order by sal ASC,deptno DESC;--多列排序
select * from emp order by comm;--存在空值默认空值最大
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
14 rows selected
--别名排序
select deptno,sal*12 incoming from emp order by incoming;
--oracle sql语句和sql plus命令的区别,sql语句中关键字不能缩写,例如:select;sqlplus命令可以缩写,例如connect ->conn;
--都不区分大小写,sqlplus password 修改密码,column 列 heading 别名(定义列别名)。
--sqL函数,单行函数(字符函数,数值函数,日期函数,转换函数,通用函数)
--字符函数-大小写控制函数(Upper(大写),LOWER(小写),INICAP(将字符串每个单词首字母转换成大写)),
--字符控制函数(CONCAT,SUBSTR,LENGTH,INSTR,LPAD,RPAD,TRIM,REPLACE)
--多行函数
函数类型(字符串函数) | 函数名称 | 参数 | 案例 |
字符串大写转换函数 | UPPER | UPPER('hello')=HELLO | |
字符串小写转换函数 | LOWER | LOWER('HELLO')=hello | |
字符串个单词首字母大写转换函数 | INICAP | INCAP('hello') = Hello | |
字符串连接函数 | CONCAT | CONCAT('hello','wor\d')=hello world | |
字符串街区函数 | SUBSTR | substr('hello',1,3) = hel | |
返回字符串长度 | LENGTH | LENGTH('hello')=5 | |
返回字串在字符串中的位置 | INSTR | INSTR('hello oracle','oracle') = 7 INSTR('hello oracle hello oracle','oracle',1,2) = 20 | |
在字符串左侧填充字符 | LPAD | LPAD('hello',10,'#')=####hello | |
在字符串右侧填充字符 | RPAD | RPAD('hello',10,'#')=hello#### | |
去除字符串中的空格(只能去除左右空格) | TRIM | TRIM('helloworld ')=helloworld | |
替换字符按串中的字串 | REPLACE | REPLACE(‘hello oracle’,'oracle','world')=hello world |