/*
以下代码是对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;
查询scott用户下的所有表
select * from tab;
查询当前用户是谁
show user;
设置显示的列宽（字符型），10个字符宽度
col tname for a10;
执行最近一次的SQL语句，并不是SQLPLUS命令
/
清屏
host cls;
查询emp表的结构
desc emp;
查询emp表的所有内容
select * from emp;
设置显示的列宽（数字型），9表示数字型，一个9表示一个数字位，四个9表示四个数字位
col empno for 9999;
设置在一页中显示20条记录
set pagesize 20;
查询emp表的员工编号，姓名，工资，部门号
select empno,ename,sal,deptno from emp;
查询emp表的不重复的工作
select distinct job from emp;
查询员工的编号，姓名，月薪，年薪
select empno,ename,sal,sal*12 from emp;
修改上一条SQL语句
edit;
查询员工的编号，姓名，月薪，年薪，年收入（年薪+奖金有空值)
select empno,ename,sal,sal*12,sal*12+comm from emp;
解决null的问题，使用NVL()函数
select empno,ename,sal,nvl(comm,0) from emp;
NVL(a,b)：如果a是空，用b替代
select empno,ename,sal,sal*12,sal*12+nvl(comm,0) from emp;
使用别名，查询员工的编号，姓名，月薪，年薪，年收入（年薪+奖金)
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;
提倡列名使用""号下界
使用字符串连接符号，输出"hello world"
select 'Hello' || ' World' "结果" from dual;
显示系统当前时间
select sysdate from dual;
Oracle默认日期格式为：DD(2位日)-MON(月的简写)-RR(2位年)
使用字符串连接符号，显示如下格式信息：xxxx的薪水是xxxx
select ename || '的工资是' || sal || '元' AS "薪水情况" from emp;
保存SQL语句到文件
spool d:\1234.sql;
保存SQL语句及其执行的结果
spool off;
执行文件中的sql语句，该文件必须是*.sql文件
@ d:\1234.sql;
单行注释
--单行注释
多行注释
select *
/*
这是
多行
注释
*/
from emp;
查询10号部门的员工
select * from emp where deptno=10;
查询姓名是KING的员工，字符串值，大小写敏感
select * from emp where ename='KING';
查询1981年11月17日入职的员工，"17-11月-81"满足oracle默认日期格式（DD-MON-RR表示2位的年份)
select * from emp where hiredate='17-11月-81';
查询工资大于1500的员工
select * from emp where sal>1500;
select * from emp where sal!=1500;
查询薪水在13
00到1600之间的员工
select * from emp where sal between 1300 and 1600;
查询入职时间在"20-2月-81"到"23-1月-82"之间的员工
select * from emp where hiredate between '20-2月-81' and '23-1月-82';【ok】
select * from emp where hiredate between '23-1月-82' and '20-2月-81';【未选中行】
查询10号和20号部门的员工
select * from emp where deptno in (10,20);
查询姓名以"S"开头的员工
select * from emp where ename like 'S%';
select * from emp where ename like '%%';
查询姓名是4个字符的员工
select * from emp where ename like 'K____';
查询员工姓名中含有'_'的员工
让\后的字符回归本来意思
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 '%w_%' escape 'w';
查询佣金为null的员工
select * from emp where comm is null;
select * from emp where comm is not null;
对null的值比较，只能使用is null 或 is not null
查询无佣金且工资大于1500的员工
select * from emp where comm is null and sal>1500;
查询工资是1500或，3000或5000的员工
select * from emp where sal=1500 or sal=3000 or sal=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 * from emp order by sal desc;
查询员工信息，按入职日期降序排序
select * from emp order by hiredate desc;
order by后面可以跟列名、别名、表达式、列号
select * from emp order by sal desc;
select empno "编号",ename "姓名",sal "工 资" from emp order by "工 资" desc;
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪" from emp order by sal*12 desc;
select empno "编号",ename "姓名",sal "工资",sal*12 "年薪" from emp order by 4 desc;
查询员工信息，按佣金升序或降序排列，null值放后面（nulls last）
select * from emp order by comm asc;
select * from emp order by comm desc nulls last;
查询员工信息，按工资降序排列,相同工资的员工再按入职时间降序排列
select * from emp order by sal desc,hiredate desc;
测试LOWER/UPPER/INITCAP函数
select lower('Hello World') "转小写",upper('Hello World') "转大写",initcap('hello world') "首字母大写"
from dual;
测试CONCAT/SUBSTR函数，从1开始
select concat('hello',' world') "字符串拼接",substr('hello world',7,11) "字符串截取"
from dual;
测试LENGTH/LENGTHB函数
select length('中CHINA') "字符",lengthb('中CHINA') "字节"
from dual;
length：不管中英文，都是1位
lengthb：英文，是1位；中文是3位【unicod
e即utf-8】
测试INSTR/LPAD/RPAD函数
select instr('hello world','o'),lpad('haha',10,'#'),rpad('hehe',10,'@')
from dual;
测试TRIM/REPLACE函数
select trim('x' from 'xxxHELLOxxxWORLDxxx'),replace('abcd','c','$')
from dual;
select trim(' ' from ' HELLO WORLD ')
from dual;
测试ROUND/TRUNC/MOD函数
select round(3.1415926,3),trunc(3.1415926,3),mod(1000,300)
from dual;
--19-12月-12
select round(sysdate,'month')
from dual;
--1-1月-13
--19-12月-12
select trunc(sysdate,'month')
from dual;
--1-12月-12
--19-12月-12
select round(sysdate,'year')
from dual;
--1-1月-13
测试SYSDATE，默认情况下，只显示日期部份
select sysdate from dual;
显示昨天，今天，明天的日期
select sysdate-1 "昨天",sysdate "今天",sysdate+1 明天
from dual;
以年和月形式显示员工近似工龄
select ename "姓名",sysdate-hiredate "日工龄",(sysdate-hiredate)/30 "月工龄",(sysdate-hiredate)/365 "年工龄"
from emp;
使用MONTHS_BETWEEN函数，以精确月形式显示员工工龄
select ename "姓名",(sysdate-hiredate)/30 "近似月工龄",months_between(sysdate,hiredate) "精确月工龄"
from emp;
测试ADD_MONTHS函数
select add_months(sysdate,3)
from dual;
测试NEXT_DAY函数【中文平台】
select next_day(sysdate,'星期三')
from dual;
从今天开始算，下一个星期三是多少号？
select next_day(next_day(sysdate,'星期三'),'星期三')
from dual;
从今天开始算，下下一个星期三是多少号？
测试LAST_DAY函数
select last_day(sysdate)
from dual;
日期隐式转换，读字符串，判断是否符合日期格式要求，再隐式转换，效率低
select ename,hiredate from emp where hiredate='17-12月-80';
隐式转换
'123'字符串-->123数值
'1-1月-13'--->1-1月-13
123---------->'123'
1-1月-13----->'1-1月-13'
日期显式转换，效率高，项目中提倡使用
select ename,to_char(hiredate,'yyyy-mm-dd')
from emp where hiredate=to_date('1980-12-17','yyyy-mm-dd');
使用to_char()函数将日期转成字符串，显示如下格式：2012-12-1 今天是 星期六 20:12:47
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
from dual;
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss am')
from dual;
select to_char(sysdate,'YYYY-MM-DD-DAY HH:MI:SS AM')
from dual;
select to_char(sysdate,'yyyy-mm-dd "今天是" day hh24:mi:ss') "时间"
from dual;
常量一定要用""双引号定界
显示员工工资，加上$或￥符号和千位符
select ename,to_char(sal,'$9,999') from emp;
select ename,to_char(sal,'L9,999') from emp;
使用to_number()函数将字符串转成数字
select to_number('123')+100 "结果"
from dual;
使用nul函数，统计员工年收入
select ename,12*sal+nvl(comm,0)
from emp;
使用nul2函数，统计员工年收入
select ename,12*sal+nvl2(comm,comm,0)
from emp;
使用nullif函数，比较10和10.0是否相同
select nullif(10,10.0)
from dual;
使用case表达式，职位是总载的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
select ename,job,sal,
case job
when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end "涨后薪水"
from emp;
各数据库厂都支持ANSISQL标准
使用decode函数，职位是总载的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
select ename,job,sal 涨前薪水,
decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) "涨后薪水"
from emp;
decode函数专用
嵌套函数
select next_day(next_day(sysdate,'星期三'),'星期三')
员工总工资，平均工资
select sum(sal) "总工资",round(avg(sal),2) "平均工资"
from emp;
员工最高工资，最低工资
select max(sal) "最高工资",min(sal) "最低工资"
from emp;
入职最早员工，入职最晚员工
select max(hiredate) "最晚入职员工",min(hiredate) "最早入职员工"
from emp;
求员工总人数
select count(*) 员工总人数
from emp;
统计有佣金的员工人数
select count(comm) 有佣金的员工人数
from emp;
分组函数不统计null值
统计公司有多少个部门
select count(distinct deptno)
from emp;
按部门求出部门平均工资
--以下SQL语句是正确
select deptno,trunc(avg(sal),0)
from emp
group by deptno;
select trunc(avg(sal),0)
from emp
group by deptno;
--以下SQL语句是错误，原因在于select子句中的非组函数没有全部出现在group by子句中
select ename,deptno,trunc(avg(sal),0)
from emp
group by ename;
查询部门平均工资大于2000元的部门
select deptno,trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal),0)>2000;
--以下SQL语句是错误，原因在别名不能参与运算
select deptno,trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having "部门平均工资">2000;
除10号部门外，查询部门平均工资大于2000元的部门
方式一
select deptno,trunc(avg(sal),0) "部门平均工资"
from emp
group by deptno
having trunc(avg(sal),0)>2000 and deptno!=10;
方式二【先行过滤，再组过滤，性能较佳】
select deptno,trunc(avg(sal),0) "部门平均工资"
from emp
where deptno!=10
group by deptno
having trunc(avg(sal),0)>2000;
显示部门平均工资的最大值
select max(avg(sal))
from emp
group by deptno;