spool:

1.保存sql语句到文件

【spool C:\Users\guoyongfeng\Desktop\基本查询.txt】

1. spool off

写入内容到文件

host cls:

清屏

show user

显示当前登录用户

select * from tab;

当前用户下的表

desc

查询表的结构

【desc emp;】

select * from emp;

所有的员工信息

show linesize

显示行宽

set linesize 150

设置列宽

col ename for a8

设置ename列宽，显示8个字符，a表示字符

col sal for 9999

设置sal列显示4个数字，9表示数字

/

表示执行上一条语句

c

Change 命令，输错语句时用【如:c /form/from】

* +

乘法和加法运算

【查询员工信息： 员工号  姓名 月薪 年薪 奖金 年收入】

SQL> select empno,ename,sal,sal*12,comm,sal*12+comm

SQL中的null值

1.包含null的表达式都为null

SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)  from emp

1. null永远!=null

如：查询奖金为null的员工

SQL> select * from emp where comm=null;

SQL> select * from emp  where comm is null;【正确写法】

as

列的别名

SQL> select empno as "员工号",ename "姓名",sal "月    薪",sal*12,comm,sal*12+nvl(comm,0)  from emp

ed

写入进入缓存文件 file afiedt.buf

distinct

1.去除重复

SQL> select distinct deptno from emp;

SQL> select job deptno from emp;

2.distinct作用于后面所有的列【了解】

SQL> select distinct deptno, job from emp;

concat

字符串连接

SQL> select concat('Hello','  World')

dual

伪表

SQL> select concat('Hello','  World') from dual;

SQL> select 3+2 from dual;

||

1.也表示字符串连接

SQL> select 'Hello'||'  World' 字符串 from dual;

1. 查询员工信息： ***的薪水是****

SQL>  select ename||'的薪水是'||sal 信息 from emp;

字符大小写敏感

如查询员工为SMITH的信息

SQL> select * from emp where ename='SMITH';

SQL> select * from emp where ename='SMITh';

日期格式敏感

SQL> select * from v$nls_parameters;【查看当前的日期格式】

SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';【修改当前会话的日期格式】

查询日期：

SQL> select * from emp where hiredate='17-12月-80';

SQL> select * from emp where hiredate='1980-12-17';

between  and

查询薪水1000~2000之间的员工，结果是包含1000和2000的

SQL> select * from emp where sal between 1000 and 2000;

注：含有边界 小值在前 大值在后，下面写是不合法的

in 在集合中

not in 不在集合中

1.查询10和20号部门的员工

SQL> select * from emp where deptno in(10,20);

2.查询不在10和20号部门的员工

SQL> select * from emp where deptno not in(10,20);

如果集合中含有null，不能使用not in; 但可以使用in

like 模糊查询

1.查询名字以S打头的员工

SQL> select * from emp where ename like 'S%';

2查询名字是4个字的员工，4个下划线即可

SQL> select * from emp where ename like '____'

3.查询名字中含有下划线的员工 需要转意字符

SQL> select * from emp where ename like '%\_%' escape '\';

rollback 回滚

Oracle是自动开启事务,不同mysql需要手动开启

order by 排序

1.查询员工信息 按照月薪排序

SQL> select * from emp order by sal;

1. 按年薪降序排序【order by后面  + 列，表达式，别名，序号】

SQL> select empno,ename,sal,sal*12 from emp order by sal*12 desc;

SQL> select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;别名

SQL> select empno,ename,sal,sal*12 年薪 from emp order by 4 desc;列数

1. 按部门升序然后按工资降序排序员工信息

SQL> select * from emp order by deptno,sal desc

4.按部门降序然后按工资降序排序员工信息

SQL> select * from emp order by deptno desc,sal desc

1. order by 作用于后面所有的列；desc只作用于离他最近的列
2. 查询员工信息  按照奖金降序排序，null值是最大，所以会排在最前面

SQL> select * from emp order by comm desc;

select * from emp order by comm desc nulls last;

set pagesize

SQL> set pagesize 20 设置分页大小

函数格式：参数可以是一个列或一个值

function_name[(arg1,arg2,...)]

lower 转小写

SQL> select lower('Hello') 转小写upper('Hello') 转大写,initcap('hello') 首写母大写 from dual

upper转大写

Initcap 首写母大写

substr(a,b)

从a中，第b位开始取

SQL> select substr('Hello,How Are You',3) from dual;

substr(a,b,c)

从a中，第b位开始取,取后面c个字符

SQL> select substr('Hello,How Are You',7,3) from dual;

instr(a,b)

 b字符串在a 中的位置

SQL> select instr('hello','ll') from dual;

length 字符数

lengthb 字节数

SQL> select length('hello你好') 字符串,lengthb('hello你好') from dual;

lpad 左填充

rpad 右填充

SQL> select lpad('abc',10,'*') from dual;

SQL> select rpad('abc',10,'*') from dual;

trim 去掉前后指定的字符

SQL> select trim('H' from 'Hello,WorldH') from dual;

replace 替换

SQL> select replace('hello','ll','**') from dual;

SQL> select substr('13522221234',1,3)||'****'||substr('13522221234',-4,4) 手机 from dual;【手机格式】

round 四舍五入

SQL>select round(466.691,2) 一,round(466.691,1) 二,round(466.691,0) 三,round(466.691,-1) 四,round(466.691,-2) 五 from dual;

trunc 截断

SQL>select trunc(466.691,2) 一,trunc(466.691,1) 二,trunc(466.691,0) 三,trunc(466.691,-1) 四,trunc(466.691,-2) 五 from dual

格式

说明

举例

YYYY

Full year in numbers

2011

YEAR

Year spelled out(年英文全称)

Twenty eleven

MM

Tow-digit value of month 月分(两位数字)

04

MONTH

Full name of month（月的全称）

4月

DY

Three-letter abrreviation of the day of the week(星期几)

星期一

DAY

Full name of the day of the week

星期一

DD

Numeric day of the moth

02

9

数字

0

零

$

美元符

L

本地货币符号

.

小数点

,

千位符

sysdate  当前时间

SQL> select sysdate from dual;

to_char

格式化时间

to_char的函数格式 to_char(date,’formate_model’)

SQL> select to_char(sysdate,'yyyy-mm-dd HH24:mi:ss') 当前时间 from dual;

时间相加减

1.昨天 今天 明天

SQL>select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;

1. 计算员工的工龄：天  星期  月 年

SQL>select empno,ename,hiredate, (sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp

months_between

两个日期相差的月数

SQL> select empno,ename,hiredate, (sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 from emp;

add_months

当前日期加上n个月的时间

一年后的时间

SQL> select add_months(sysdate,12) from dual;

last_day

最后一天

当前月的最后一天

SQL> select last_day(sysdate) from dual;

next_day

下个星期几

下个星期

SQL>select next_day(sysdate,'星期日') from dual;

next_day的应用：设置时间自动备份数据

to_char

也可以格式数字

1.年和月的四舍五入

SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;

2.2017-06-29 09:50:09今天是星期四

SQL>select to_char(sysdate,'yyyy-mm-dd HH24:mi:ss"今天是"day') from dual

1. 查询员工薪水：两位小数 千位符 货币代码

SQL> select to_char(sal,'L9,999.99') from emp;

nvl2(a,b,c)

当a=null的时候，返回c；否则返回b

如果奖金是空，输出0，注意2前的是L的小写，不是1

SQL> select ename,nvl2(comm,comm,0) from emp;

nullif(a,b)

当a=b的时候，返回null；否则返回a

SQL> select nullif('abc','abc') from dual;

SQL> select nullif('abc','abcb') from dual;

coalesce

从左到右找到第一个不为null的值

SQL> select comm,sal,coalesce(comm,sal) 第一个不为空的值 from emp;

nvl(a,b)

需求：涨工资，总裁1000 经理 800 其他400

1. case when then end

sql>select ename,job,sal 涨前,case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+800 else sal+400 end 涨后 from emp;

1. decode

SQL>select ename,job,sal 涨前,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后 from emp

SELECT [column,] group_function(column),...

FROM table

[WHERE condition]

[GROUP BY column]

[GROUP BY column]

Having...

Avg 求平均数

平均工资

SQL> select avg(sal) from emp;

Count 求记录数

员工数

SQL> select count(*) from emp;

有奖金的个数

SQL> select count(comm) from emp;

distinct 可去除重复

SQL> select count(distinct deptno) from emp;

Max 求最大值

最高工资

SQL> select max(sal) from emp;

Min 求最小值

最低工薪

SQL> select min(sal) from emp;

Sum 示各和

每月工薪支出

SQL> select sum(sal) from emp;

平均奖金

SQL> select avg(comm) from emp; 奖金不为空的员工的平均奖金

SQL> select sum(comm)/count(*) from emp;所有员工的平均奖金

1.null值 组函数（多行函数）自动滤空，也就是空值也会计算

SQL> select count(*), count(nvl(comm,0)) from emp;

select avg(nvl(comm,0)) from emp;

求每个部门的平均工资

SQL> select deptno,avg(sal) from emp group by deptno;【一个列的分组】

按部门和职位求平均工资

SQL> select deptno,job,avg(sal) from emp group by deptno,job order by 1;【多个列的分组】

求平均工资大于2000的部门

SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

查询10号部门的平均工资

SQL> select deptno,avg(sal) from emp where deptno='10' group by deptno;

select deptno,job,avg(sal) from emp group by deptno,job【根据部门和职位汇总平均工资】

select deptno,avg(sal) from emp group by deptno;【根据部门汇总平均工资】

select deptno,avg(sal) from emp group【平均工资】

group by rollup(a,b)

=================

group by a,b

group by a

group by null

select deptno,job,avg(sal) from emp group by rollup(deptno,job)

break on deptno skip 2

break on null

格式化输出 skip2表示空两行