sqlplus 指令
conn sqlplus desc

一个认知
oracle
两个概念
主键
外键
三个名词
sql
pl/sql
sqlplus
四种约束
实体约束
引用约束
列级约束
用户自定义约束
五种分类
select
DML
DDL
TCL
DCL
六种对象
table
view
sequence
index
Synonym
program unit

第一章 select
select [distinct] {*|columnName,columnName,...}
from tableName;

1.select子句中可以使用算数运算，但是要注意优先级的问题。
查询出所有员工的last_name 和年薪
2.别名
给当前列另外起一个名字，在做算术运算时非常有效。
别名紧跟在列名后
select columnName alias
from tableName；
eg：
select last_name name
from s_emp;
当别名中包含特殊字符或区分大小写时，使用双引号标记
3.处理空值
和null做算术运算得出的结果永远是null
nvl(列名,替换的值)

select (salary+nvl(commission_pct,0))*12
from s_emp;

nvl2(列名，非空的替换值，空的替换值)

4.去重
distinct出现在select之后，去除重复的行

5.sqlplus指令
编辑命令
A[PPEND] text ：在当前行的末尾追加文本
C[HANGE] / old/ new：替换当前行中指定的内容
CL[EAR] BUFF[ER]：清空缓存
DEL： 删除当前行
I[NPUT] text：在最后一行之后另起一行添加新的文本
L[IST] n：显示某一行的内容
n text：整行替换

文件操作命令
SAVE filename
GET filename
START filename
@ filename
EDIT filename
SPOOL filename spool off
EXIT

6.column 格式化
column {columnName|alias} option
clear
format 9 0 . , $ L An
justify
heading
null

第二章 排序 条件查询
select ...
from ...
order by ...

1 排序
asc正序排列 默认的
desc 倒序排列 需添加desc关键字

null在正序排列中出现在末尾
在倒序排列中出现在开头

2 条件查询
select ...
from ...
where ...
order by ...

1) 逻辑比较操作符
2）sql比较操作符
between ... and ..
in(list)
查询id为 1,3,5,7,9的员工的last_name,salary
select id,last_name,salary
from s_emp
where id=1 or id=3 or id=5;

select id,last_name,salary
from s_emp
where id in(1,3,5,7,9);

like

select last_name,salary
from s_emp
where last_name like ‘a%‘;

% 零到多个任意字符
_ 一个任意字符

查询last_name中包含字母a的员工的工资

select last_name,salary
from s_emp
where last_name like ‘%a%‘;

查询last_name中第二个字母为a的员工的工资

select last_name,salary
from s_emp
where last_name like ‘_a%‘;

A_tom

select last_name,salary
from s_emp
where last_name=‘A_tom‘;

转义 ： escape

select last_name,salary
from s_emp
where last_name like ‘A\_%‘ escape ‘\‘;

3）逻辑操作符
and
or
not

存在多个判断条件时（没有括号），执行顺序如下：

先计算所有的比较操作符（逻辑比较操作符和sql比较操作符），在执行and，最后是or。

第三章 单行函数
一次处理一行数据，并返回一个结果
哑表：dual

1.字符函数
查询last_name为patel（不区分大小写）的员工的信息
Patel ---> patel
pAtel

select last_name,salary
from s_emp
where lower(last_name)=‘patel‘;

2.数字函数
round：四舍五入
trunc: 截断
3.日期函数
select round(to_date(‘15-10月-14‘,‘dd-month-yy‘),‘day‘)
from dual;
4.转换函数
to_char
sysdate
中文环境中
例子： 2014-10月-28 星期二
英文环境
例子： 2014-october-28 tuesday

eg:
select to_char(sysdate,‘yyyy-month-dd" of "hh:mi:ss am‘)
from dual;
双引号只能在两个地方使用：
a) 添加别名的时候
b) 在日期类型转换时需要原样输出的字符串

to_date(char[,‘fmt‘])
前后参数要相互照应

第四章 多表查询
实质上还是从一张表中查询数据
1.等值连接
1)需求：查找员工的last_name,和员工所在部门的名字
select last_name,name
from s_emp,s_dept
where dept_id=s_dept.id;

2)需求:查找所有部门的名称和部门所在区域的名称

select s_dept.id,s_dept.name,s_region.name
from s_region,s_dept
where s_region.id=region_id;

2.不等值连接(使用=以外的操作符):

1)需求：查找员工工资，以及工资所属等级
create table salgrade(
id number primary key,
min number,
max number,
gradename varchar(10)
);

insert into salgrade values(1,0,1000,‘蓝领‘);
insert into salgrade values(2,1001,1500,‘白领‘);
insert into salgrade values(3,1501,2500,‘金领‘);

select last_name,salary,gradename
from s_emp,salgrade
where salary between min and max;

3.外连接：
(+)
在等值连接的基础上，语义上值较少的一边添加(+)来做内容补齐。
左外
右外
1)需求：查看员工last_name,和员工所在部门的名称，哪怕员工没有所属部门对应，依然显示员工信息。
select last_name,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+);


2)查看员工last_name,和员工所在部门的名称，哪怕部门里边没有对应的员工，依然显示部门的信息。
select last_name,name
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

4.自连接
在同一张表中建立等值连接
1)需求：所有员工的last_name,以及员工所属经理的last_name
select w.last_name,m.last_name
from s_emp w,s_emp m
where w.manager_id=m.id;

union
并集
select id,last_name
from s_emp
where id<5
union
select id,last_name
from s_emp
where id between 2 and 10;

union all
合集
select id,last_name
from s_emp
where id<5
union all
select id,last_name
from s_emp
where id between 2 and 10;
minus
差集
select id,last_name
from s_emp
where id<3
minus
select id,last_name
from s_emp
where id<4;
intersect
交集
select id,last_name
from s_emp
where id<5
intersect
select id,last_name
from s_emp
where id between 2 and 10;

使用伪列 rownum 对s_emp进行编号，然后查询第4行到第6行的数据。

select rownum,last_name
from s_emp
where rownum<7
minus
select rownum,last_name
from s_emp
where rownum<4;

第五章 组函数
AVG (DISTINCT|ALL|n)
COUNT (DISTINCT|ALL|expr|*)
MAX (DISTINCT|ALL|expr)
MIN (DISTINCT|ALL|expr)
SUM (DISTINCT|ALL|n)

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

1.需求：查询部门id和当前部门的平均工资

select dept_id,avg(salary)
from s_emp
group by dept_id;

2.查询平均工资>1000的 id为31到35的部门的id和平均工资

select dept_id,avg(salary)
from s_emp
where dept_id between 31 and 35
group by dept_id
having avg(salary)>1400
order by dept_id;
select字句中出现的列名并且没有被组函数计算，那么它必须出现在group by 字句中。反之不成立

select dept_id,title,avg(salary)
from s_emp
group by dept_id,title
having avg(salary)>1000
order by dept_id;

当group by字句中出现多个列名时，按照从左向右的顺序，依次进行分组。

第六章 子查询

1.需求：查询员工工资大于 43号部门的平均工资 的员工的last_name和工资

select avg(salary)
from s_emp
where dept_id=43;

select last_name,salary
from s_emp
where salary>(select avg(salary)
from s_emp
where dept_id=43);

select last_name,salary
from s_emp,(select avg(salary) a
from s_emp
where dept_id=43) s
where salary>s.a;

2.需求：查询部门在3号区域的所有员工的last_name和dept_id
中间目标：三号区域的所有部门
select id
from s_dept
where region_id=3;

终极目标
select last_name,dept_id
from s_emp,(select id
from s_dept
where region_id=3) d
where s_emp.dept_id=d.id;

select last_name,dept_id
from s_emp
where dept_id=(select id
from s_dept
where region_id=3);

3.需求：查询s_emp表中第4行到第6行的员工last_name,id

select rownum,last_name,id
from s_emp;

select last_name,id
from (select rownum n,last_name,id
from s_emp) s
where s.n between 4 and 6;

第七章 运行时添加参数

select &c1,&c2
from &t1;

& 取缔符，用来定义可变参数
define 用来事先定义参数
undefine 解除已经定义的参数
accept
语法 accept 参数名 参数类型 prompt ‘自定义的提示信息‘
start fileName var1 var2
要求文件中参数名必须是数字

sql