spool E:\基本查询.txt

将命令行的语句写入到指定的目下的指定的文件中

host cls

清屏命令

show user

显示当前操作的用户

desc emp

查看表结构

select * from emp;

查看所有的员工的信息

--设置行宽

SQL> --设置行宽

SQL> show linesize

SQL> set linesize 150

SQL> --设置列宽

SQL> col ename for a8代表8为字符组成的字符串

SQL> col sal for 9999 代表4位的数字

SQL中的null

1包含null的表达式都为null

2. null永远!=null

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

select * from emp where comm is null;

已选择 14 行。

SQL> --2. null永远!=null

SQL> --查询奖金为null的员工

SQL> select *

2  from emp

3  where comm=null;

未选定行

SQL> select *

2  from emp

3  where comm is null;

ed

该命令主要用在当前一个sql语句写错时，然又不想重新写，这个时候可以用ed命令记事本调出来对先去的命令进行编辑,如果该命令用在Linux系统中，调出来的应该是vi编辑器

别名设置

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

2* from emp

c 命令

该命令会对错误的行信息进行修改示例如下：

SQL> select empno,ename,sal

2  form emp;

form emp

*

第 2 行出现错误:

ORA-00923: 未找到要求的 FROM 关键字

SQL> 2

2* form emp

SQL> --c命令  change

SQL> c /form/from

2* from emp

SQL> /

nvl(comm,0)--滤空函数

select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

distinct

去掉重复记录select deptno from emp;

Distinct组合

select distinct deptno,job from emp;

Concat

1. select concat('Hello','  World') from dual;

2. select 'hello'||'   world' 值 from dual;

3. SQL> --查询员工信息： ***的薪水是****

SQL> select ename||'的薪水是'||sal  值

2  from emp;

过滤和查询

where

select * from emp where deptno=10;

在where过滤中对大小写敏感

date

SQL> -- 日期格式敏感

SQL> --查询入职日期是17-11月-81的员工

SQL> select *

2  from emp

3  where hiredate='17-11月-81';

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7839 KING     PRESIDENT            17-11月-81      5000                    10

SQL> --修改日期格式

SQL> select * from v$nls_parameters;

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

between and

between and: . 包含边界  2. 小值在前 大值在后

SQL> --查询薪水1000~2000之间的员工

SQL> select *

2  from emp

3  where sal between 1000 and 2000;

EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7499 ALLEN    SALESMAN        7698 20-2月 -81      1600        300         30

7521 WARD     SALESMAN        7698 22-2月 -81      1250        500         30

7654 MARTIN   SALESMAN        7698 28-9月 -81      1250       1400         30

7844 TURNER   SALESMAN        7698 08-9月 -81      1500          0         30

7876 ADAMS    CLERK           7788 23-5月 -87      1100                    20

7934 MILLER   CLERK           7782 23-1月 -82      1300                    10

in

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

SQL> --null 3: 如果集合中含义null，不能使用not in；但可以使用in

SQL> ed

已写入 file afiedt.buf

1  select *

2  from emp

3* where deptno in (10,20,null)

like

%

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

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

2.查询名字是4个字的员工

select *  from emp where ename like '____';

3.-查询名字中含有下划线的员工

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

order by

排序操作order by 后面 + 列，表达式，别名，序号

order by 作用于后面所有的列，desc只作用于离他最近的一列

排序时的下技巧

当我们对某一类进行倒序排序的时候，null会放在最上面，这个时候为了避免前面n列都是null值，影响观察，可以采取下面的方法

select *from emp order by comm desc nulls last

单行函数

Lower

Uppper

initcap

select lower('Hello World') 转小写,upper('Hello World') 转大写,

initcap('hello world') 首字母大写

from dual;

转小写      转大写      首字母大写

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

hello world HELLO WORLD Hello World

substr(a,b)

substr(a,b) 从 a中，第b位开始

select substr('Hello World',3) from dual;

SUBSTR('H

---------

llo World

substr(a,b,c)

SQL>substr(a,b,c)  从a中，第b位开始,取c位

SQL> select substr('Hello World',3,4) from dual;

SUBS

----

llo

length 字符数 lengthb字节数

select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;

字符       字节

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

11         11

select length('中国') 字符,lengthb('中国') 字节from dual

字符       字节

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

2          4

-instr(a,b) 在a中，查找b

select instr('Hello World','ll') from dual;

INSTR('HELLOWORLD','LL')

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

3

lpad 左填充  rpad右填充

select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右

左            右

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

******abcd   abcd******

trim

去掉前后指定的字符

select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR

----------

ello World

replace 替换

select replace('Hello World','l','*') from dual;

REPLACE('HE

-----------

He**o Wor*d

-四舍五入

select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三

round(45.926,-1) 四, round(45.926,-2) 五

from dual;

一         二         三         四         五

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

45.92       45.9         45         40          0

当前系统时间date

不允许日期 + 日期

select sysdate  from dual;

SYSDATE

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

17-9月 -14

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  from dual;

select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff')  from dual;

员工的工龄： 天 星期 月 年

SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,

(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年

from emp;

add_months

select add_months(sysdate,78) from dual; 加上n个月后的日期

last_day

select last_day(sysdate) from dual; 本月的最后一天

next_day(sysdate,'星期四')

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

NEXT_DAY(SYSDA

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

18-9月 -14

日期的四舍五入

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

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

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI

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

2014-09-17 14:40:48今天是星期三

查询员工的薪水： 两位小数 千位符，货币代码 select to_char(sal,'L9,999.99') from emp;

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

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

值

---

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

值

---

abc

coalesce

返回第一个为非空的值

select comm,sal,coalesce(comm,sal) 值 from emp;

nvl2(a,b,c)

当a为空时，返回c，否则返回b

Case

SQL> --给员工涨工资，总裁1000 经理800 其他400

SQL> select ename,job,sal 涨前,

2         case job  when 'PRESIDENT' then sal+1000

3                  when 'MANAGER' then sal+800

4                  else sal+400

5          end 涨后

6  from emp;

第二种方式

SQL>select ename,job,sal 涨前,

2         decode(job,'PRESIDENT',sal+1000,

3                    'MANAGER',sal+800,

4                              sal+400) 涨后

5  from emp;

多行函数

Sum（）

select sum(sal) from emp;

Count(*)

select count(*) from emp;

平均工资

select sum(sal)/count(*) 一, avg(sal) 二 from emp;

-null值 4: 组函数自动滤空;

-null值 5: 组函数自动滤空;

可以嵌套滤空函数来屏蔽滤空功能

Having 分组后的过滤

select deptno,avg(sal)

2  from emp

3  group by deptno

4  having avg(sal)>2000;

select deptno,avg(sal)

2  from emp

3  group by deptno

4  having avg(sal)>2000;

group by的增强

SQL> /*

SQL> group by的增强

SQL> select deptno,job,sum(sal) from emp group by deptno,job

SQL> +

SQL> select deptno,sum(sal) from emp group by deptno

SQL> +

SQL> select sum(sal) from emp

SQL>

SQL> ==

SQL>

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job)

SQL>

SQL> group by rollup(a,b)

SQL> =

SQL> group by a,b

SQL> +

SQL> group by a

SQL> +

SQL> group by null

SQL> */

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB         SUM(SAL)

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

10 CLERK           1300

10 MANAGER         2450

10 PRESIDENT       5000

10                 8750

20 CLERK           1900

20 ANALYST         6000

20 MANAGER         2975

20                10875

30 CLERK            950

30 MANAGER         2850

30 SALESMAN        5600

DEPTNO JOB         SUM(SAL)

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

30                 9400

29025

SQL> break on deptno skip 2

SQL> break on null

多表查询的理解

等值连接

select e.empno,e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno=d.deptno;

外连接

左外连接：

右外连接：

左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含

SQL>     写法：where e.deptno=d.deptno(+)

SQL> 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含

SQL>      写法：where e.deptno(+)=d.deptno

自连接

自连接: 通过表的别名，将同一张表视为多张表 自连接：不是适合操作大表

SQL> --层次查询

SQL> select level,empno,ename,sal,mgr

2  from emp

3  connect by prior empno=mgr

4  start with mgr is null

5  order by 1;

LEVEL      EMPNO ENAME         SAL        MGR

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

1       7839 KING             5000

2       7566 JONES            2975       7839

2       7698 BLAKE            2850       7839

2       7782 CLARK            2450       7839

3       7902 FORD             3000       7566

3       7521 WARD             1250       7698

3       7900 JAMES             950       7698

3       7934 MILLER           1300       7782

3       7499 ALLEN            1600       7698

3       7788 SCOTT            3000       7566

3       7654 MARTIN           1250       7698

LEVEL      EMPNO ENAME         SAL        MGR

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

3       7844 TURNER           1500       7698

4       7876 ADAMS            1100       7788

4       7369 SMITH             800        7902