oracle 查询函数练习

浏览数：240 / 时间：2015年06月12日

/*
--以下代码是对emp表进行显示宽度设置
col empno for 9999;
col ename for a10;
col job for a10;
col mgr for 9999;
col hiredate for a12;
col sal for 9999;
col comm for 9999;
col deptno for 99;
set pagesize 20;

--创建新表new_emp，复制emp表中的结构和数据到new_emp表中
create table xxx
as
select * from emp;

--删除xxx表
drop table xxx purge;
*/

oracle数据库服务器由二个部分组成：
A)数据库（物理概念，底层是数据库专用文件的集合）
B)数据库实例（逻辑概念，只能能通过数据库实例操作数据库）

SQL
B) DML（数据操纵语言，例如：insert，update，delete，SELECT）
B）DDL（数据定义语言，例如：create table,drop table,alter table）
C) DCL（数据控制语言，例如：grant授予，revoke收权）
D) TCL（事务控制语言，例如：rollback，commit，事务开始）

-------------------------------------------------------------------------------------

从sqlplus环境，以scott用户和123456密码进入oracle数据库实例
c:\>sqlplus scott/123456

退出sqlplus环境
SQL:\>exit;

查询scott用户下的所有表
select * from tab;

查询当前用户是谁
show user;

设置显示的列宽（字符型、日期型），10个字符宽度，a表示字符型
col ename for a12;
a12：表示显示宽度为12个字符,a大小写不敏感

执行最近一次的SQL语句，并不是SQLPLUS命令
/表示最近执行过的SQL语句
col ename for a10，它不是SQL语句，而是oracle提供的专用SQLPLUS工具中的命令

清屏
host cls;
SQLPLUS命令，可以简写，分号可省略
SQL语句，不可简写，必须以分号结束

查询emp表的结构
desc emp;
数字型：number
字符型：varchar2
日期型：date

查询emp表的所有内容
select * from emp;

设置显示的列宽（数字型），9表示数字型，一个9表示一个数字位，四个9表示四个数字位
col empno for 999999;
col表示:column
empno表示：列名
for表示:format
999999：表示显示6个字符宽度，只用于数字

设置在一页中最多显示20条记录
set pagesize 20;

查询emp表的员工编号，姓名，工资，部门号
select empno,ename,job,deptno from emp;

查询emp表的不重复的工作
select distinct job from emp;

select distinct job,sal from emp;
最终由job和sal的笛卡尔积决定

查询员工的编号，姓名，月薪，年薪（月薪*12)
select empno,ename,sal,sal*12 from emp;

修改最近的一条SQL语句
edit;

查询员工的编号，姓名，月薪，年薪，年收入（年薪+奖金有空值)
select empno,ename,sal,sal*12,sal*12+comm from emp;
NULL运算数字=NULL

解决null的问题，使用NVL()函数，NVL(a,b)：如果a是空，用b替代
select empno,ename,sal,sal*12,sal*12+NVL(comm,0) from emp;
null!=0
null!=空白字符串

使用别名，查询员工的编号，姓名，月薪，年薪，年收入（年薪+奖金)
select empno AS "编号",ename AS "姓名",sal AS "月薪",sal*12 AS "年薪",sal*12+NVL(comm,0) AS "年收入"
from emp;//正确

select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;//正确

select empno ‘编号‘,ename ‘姓名‘,sal ‘月薪‘,sal*12 ‘年薪‘,sal*12+NVL(comm,0) ‘年收入‘
from emp;//出错，

select empno 编号,ename 姓名,sal 月薪,sal*12 年薪,sal*12+NVL(comm,0) 年收入
from emp;//正确

select empno 编号,ename 姓名,sal 月薪,sal*12 年薪,sal*12+NVL(comm,0) 年收入
from emp;//出错

select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;//正确，要在别名中使用空格符，得用双引号

使用字符串连接符号，输出"hello world"，使用dual表
dual表是oracle为了拼一些语言，而设计的一个哑表。
因为oracle语法，有select子句，必须有from子句

显示系统当前时间
select now()这是mysql的语法
select sysdate from dual;

使用字符串连接符号，显示如下格式信息：xxxx的薪水是yyyy
方式一：select ‘hello world‘ from dual;
方式二：select ‘hello‘ || ‘ world‘ from dual;
select ename || ‘的薪水‘ || sal "薪水情况" from emp;

保存SQL语句到文件，并创建sql文件
spool d:\1234.sql;

保存SQL语句及其执行的结果
spool off;

执行文件中的sql语句，该文件必须是*.sql文件
@ d:\1234.sql;

单行注释
--select * from emp;

多行注释
/**/

SQL vs SQLPLUS

SQL
1）语言
2）ANSI标准
3）关健字不可缩写
4）必须是分号结束
5）更新表中的数据

SQLPLUS
1）环境，命令
2）是Oracle的特征之一
3）关健字可缩写
4）分号可有可无
5）不能更新表中的数据

HTML：
JSP： <%-- --%>
XML： 
Pros：#
Java：
A)//
B)/* */
C)/** */
MySQL：#
Oracle:
A)--
B)/* */
JS：
A)//
B)/* */
CSS：
/* */

-------------------------------------------------------------------------------------

查询20号部门的员工
select *
from emp
where deptno = 20;

查询姓名是SMITH的员工，字符串值，大小写敏感
select *
from emp
where ename=‘SMITH‘;
不敏感：表名、字段名，‘A‘
敏感：字符串常量值

查询1980年12月17日入职的员工，"17-12月-80"满足oracle默认日期格式（DD-MON-RR表示2位的年份)
select *
from emp
where hiredate = ‘17-12月-80‘;

查询工资大于1500的员工
select * from emp where sal > 1500;

查询薪水在1300到1600之间的员工，包括1300和1600
select * from emp where sal>=1300 and sal<=1600;//正确
select * from emp where sal<=1600 and sal>=1300;//正确
select * from emp where sal between 1300 and 1600;//如果是数值型：小值在前，大值在后，返之无值

查询入职时间在"20-2月-81"到"23-1月-82"之间的员工
select * from emp where hiredate between ‘23-1月-82‘ and ‘20-2月-81‘;//无值
select * from emp where hiredate between ‘20-2月-81‘ and ‘23-1月-82‘;//如果是日期型：小值在后，大值在前，返之无值

查询20号或30号部门的员工
select * from emp where deptno=20 or deptno=30;
select * from emp where deptno in (20,30);

查询姓名以"S"开头的员工
select * from emp where ename like ‘S%‘;
%表示：0个，1个，多个字符

_表示：1个字符

查询姓名以"N"结束的员工,%表示0个，或多个字符
select * from emp where ename like ‘%N‘;

查询姓名是4个字符的员工,_下划线只能表示1个字符
select * from emp where ename like ‘W___‘;

查询员工姓名中含有‘_‘的员工，让\后的字符回归本来意思【like ‘%\_%‘ escape ‘\‘】
select * from emp where ename like ‘%\_%‘ escape ‘\‘;//正确
select * from emp where ename like ‘%E_%‘ escape ‘E‘;//正确
select * from emp where ename like ‘%$_%‘ escape ‘$‘;//正确
select * from emp where ename like ‘%#_%‘ escape ‘#‘;//正确
select * from emp where ename like ‘%@_%‘ escape ‘@‘;//正确
select * from emp where ename like ‘%%_%‘ escape ‘%‘;//错误

查询佣金为null的员工
select * from emp where comm = null;//错误
select * from emp where comm is null;//正确
select * from emp where comm is not null;//正确

查询无佣金且工资大于1500的员工
select * from emp where (comm is null) and (sal>1500);

查询工资是1500或，3000或5000的员工
select * from emp where sal in (1500,3000,5000);

查询职位是"MANAGER"或职位不是"ANALYST"的员工（方式一）
select * from emp where (job = ‘MANAGER‘) or (job != ‘ANALYST‘);

查询职位是"MANAGER"或职位不是"ANALYST"的员工（方式二）
select * from emp where (job = ‘MANAGER‘) or not (job = ‘ANALYST‘);

查询员工信息（编号，姓名，月薪，年薪），按月薪升序排序，默认升序
select empno "编号",ename "姓名",sal "月薪",sal*12 "年薪" from emp order by sal asc;

查询员工信息，按入职日期降序排序
select * from emp order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号，从1开始
列名排序：select * from emp order by hiredate desc;
别名排序：select ename "姓名",sal "月薪",hiredate "入职时间" from emp order by 入职时间 desc;
表达式排序：select ename "姓名",sal*12 "年薪",hiredate "入职时间" from emp order by sal*12 desc;
列号排序：select ename "姓名",sal*12 "年薪",hiredate "入职时间" from emp order by 2 desc;

查询员工信息，按佣金升序或降序排列，null值看成最大值
select * from emp order by comm desc;

查询员工信息，按工资降序排列,相同工资的员工再按入职时间升序排列
select * from emp order by sal desc,hiredate asc;

查询20号部门，且工资大于1500，按职入时间降序排列
select *
from emp
where (deptno=20) and (sal > 1500)
order by hiredate desc;

-------------------------------------------------------------------------------------

测试lower/upper/initcap函数
select lower(‘www.itCAST.cN‘) from dual;
select upper(‘www.itCAST.cN‘) from dual;
select initcap(‘www.itCAST.cN‘) from dual;

测试concat/substr函数，从1开始
select concat(‘hello‘,‘ world‘) from dual;
select ‘hello‘ || ‘ world‘ from dual;
select substr(‘www.itcast.cn‘,12,2) from dual;
12表示：从第几个字符开始，第一个字符为1
2表示：共取几个字符

测试length/lengthb函数，前提是，装oracle时，编码方式为UTF-8，一个中文占3个Byte长度
select length(‘CHINA中国‘) from dual;//7
select lengthb(‘CHINA中国‘) from dual;

测试instr/lpad/rpad函数
select instr(‘你好 world‘,‘w‘) from dual;//4
select lpad(‘hello‘,10,‘@‘) from dual;
select rpad(‘hello‘,10,‘#‘) from dual;

测试trim/replace函数
select trim(‘w‘ from ‘wwwwwbookwwwwwwwwwwwwww‘) from dual;
select replace(‘www.itcast.cn‘,‘cn‘,‘com‘) from dual;

测试round/trunc/mod函数
select round(3.1415,3) from dual;
select trunc(3.1415,3) from dual;
select mod(10,3) from dual;

测试round作用于当前时间（month）
12-3月-13
select round(sysdate,‘month‘) from dual;//01-3月-13
select round(sysdate,‘year‘) from dual;//01-1月-13

测试trunc作用于当前时间（month）
12-3月-13
select trunc(sysdate,‘month‘) from dual;//01-3月-13

测试trunc作用于当前时间（year）
select trunc(sysdate,‘year‘) from dual;//01-1月-13

round和trunc除了用在数值型，也能用在日期型。

测试sysdate，默认情况下，只显示日期部份，格式为：日-月-2位的年
select sysdate from dual;

显示昨天，今天，明天的日期，日期类型只能+-数值
select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄，只能日期-日期=数值
select ename,hiredate,(sysdate-hiredate)/30 "近似月工龄",(sysdate-hiredate)/365 "近似年工龄" from emp;

使用months_between函数，计算到年底还有多少个月
select months_between(‘31-12月-13‘,sysdate) from dual;