Oracle基础

安装

Oracle 11g 32 位下载：http://www.java1234.com/a/javaziliao/kfgj/2015/0817/4787.html


Oracle 11g 64 位下载：http://www.java1234.com/a/javaziliao/kfgj/2015/0817/4786.html




PLSQL Developer 安装
Plsql developer 10 下载：http://www.java1234.com/a/javaziliao/kfgj/2015/0815/4776.html


Plsql developer 没有开发 64 位版本，所以导致了 64 位用户没法直接连接 oracle 数据库；


解决办法：


第一步：下载 32 位 oracle 客户端 instantclient-basic-win32-10.2.0.5，


下载地址：http://pan.baidu.com/s/1o6sx7n0 解压第二步：安装 plsql developer ；


第三步：环境变量：TNS_ADMIN：oracle 根目录\product\11.2.0\dbhome_1\NETWORK\ADMIN


NLS_LANG：SIMPLIFIED CHINESE_CHINA.ZHS16GBK 配置 plsql developer 首选项


Oracle 11g 卸载


Oracle 卸载：http://jingyan.baidu.com/article/922554468d4e6b851648f4e3.html


Oracle 表空间//存储表的
一个数据库可以有多个表空间，一个表空间里可以有多个表。表空间就是存多个表的物理空间；可以指定表空间的大小位置等。


创建表空间：create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;


自动扩展大小：create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 50M autoextend on next 10M;


设置最大空间：create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;


更改用户默认表空间：alter database default tablespace ts1;


表空间改名：alter tablespace ts1 rename to tss1;


删除表空间：drop tablespace ts2 including contents and datafiles;//包括内容和物理文件


select * from A1 t for update 更新


Oracle 虚拟表 dual 表
Dual 表是 sys 用户下的一张虚表；提供一些运算和日期操作时候用到；
//用户名.表 scott.a2 a1属于最高用户用户名可以省略


select sysdate from dual;


第一,系统用户sysdba
1,创建表空间
--创建表空间语法:create tablespace 表空间名称 datafile 'dbf文件路径' size 大小 autoextend on;
create tablespace kusy
datafile 'kusy' size 1000m autoextend on;
 
--创建表空间
create tablespace myspace
datafile 'D:\app\Administrator\oradata\orcl\myspace.dbf' 
size 100m
autoextend on;
2,创建临时表空间
2-1创建临时表空间
创建临时表空间语法:create temporary tablespace 表名 tempfile '临时表空间存放的dbf文件路径' size 大小 autoextend on;
create temporary tablespace mytemp
tempfile 'D:\app\Administrator\oradata\orcl\mytemp.dbf' size 10mautoextend on ;
2-2删除表空间
--假如表空间为空了就用 drop tablespace 表空间名;
--假如表空间不为空就级联删除  drop tablespace 表空间名 including contents and datafiles
--但是现在被删除的数据库 dbf文件还是存在的 直接删除dbf文件
drop tablespace db1pwd including contents and datafiles;
 
3,创建用户
3-1创建用户,为用户分配表空间(表空间和临时的表空间)
--创建用户zhangsan
create user zhangsan --用户名
identified by zhangsan --密码
default tablespace myspace --指定表空间
temporary tablespace mytemp --指定临时表空间
quota 5M on myspace;
--------------------------------
--创建用户yjh
create user yjh --用户名
identified by yjh --密码
default tablespace myspace --指定表空间
temporary tablespace mytemp --指定临时表空间
3-2修改密码,锁定/解锁用户
--修改密码
alter user 用户名 identified by 密码 ;
--锁定用户
alter user 用户名 account lock;
--解锁用户
alter user 用户名 account unlock;
3-3删除用户
--新创建的用户,假如没有表了就用 drop user 用户名;
--假如有表了就级联删除  drop user 用户名 cascade;
drop user zhangsan cascade;
3-4创建用户 (没有表空间 ,使用默认的表空间system)
--用户尽量避免使用系统表空间,所以我们在创建用户的时候 去指定用户自己创建的表空间和临时表空间
create user zhangsan identified by zhangsan;
create user lisi identified by lisi;
-创建一个表空间-->临时表空间-->创建用户-->分配表空间和临时表空间
-->给新用户创建会权限,创建表权限,查询表的权限
 
4,给用户权限
给用户权限语法:grant 权限名 to 用户名
收回用户的权限:revoke 权限名 from 用户名
4-1关于序列的权限
1. Create sequence 允许被授权者在他们自己的模式中创建新的序列
2. CREATE ANY SEQUENCE允许被授权者在任意一个模式中创建新的序列
3. ALTER ANY SEQUENCE允许被授权者修改数据库中任意一个序列的属性
4. DROP ANY SEQUENCE允许从数据库内的任意一个模式中删除任意一个序列
5. SELECT ANY SEQUENCE
4-2关于会话的权限
1. CREATE SESSION允许被授权者连接到数据库。该特权对用户账户是必需的，但对软件账户可能是不受欢迎的。
2. ALTER SESSION允许被授权者执行ALTER SESSIONS语句
3. ALTER RESOURCE COST允许被授权者修改ORACLE为一个概况中的资源约束计算资源成本的方式。
4. RESTRICTED SESSION允许数据库在RESTRICTED SESSION模式时连接到数据库，一般是为了管理性目的。
4-3关于表空间的权限
1. CREATE TABLESPACE允许创建新的表空间
2. ALTER TABLESPACE允许被授权者更改现有表空间
3. DROP TABLESPACE允许删除表空间
4. MANAGE TABLESPACE允许更改表空间。例如ONLINE、OFFILE、BEGIN BACKUP或END BACKUP
5. UNLIMITED TABLESPACE允许消耗任意一个表空间中的磁盘限额。相当于给指定授权者每个表空间中的无限限额。以上介绍Oracle系统特权。
4-4关表于的权限
1. CREATE TABLE允许在自己的对象模式中创建表
2. CREATE ANY TABLE允许在任意一个对象模式中创建表
3. ALTER ANY TABLE允许更改任意一个对象模式中的表
4. DROP ANY TABLE允许从任意一个对象模式中删除表
5. COMMENT ANY TABLE允许给任意一个对象模式中的任意一个表或列注释
6. SELECT ANY TABLE允许查询任意表
7. INSERT ANY TABLE允许插入新行到任意表
8. UPDATE ANY TABLE允许更新任意表
9. DELETE ANY TABLE允许删除任意表中的行
10. LOCK ANY TABLE允许执行一条LOCK TABLE来明确锁定任意一个表
11. FLASHBACK ANY TABLE允许使用AS OF 语法对任意一个对象模式的任意一个表或视图执行一个SQL回闪查询。
4-5关于同义词的权限
1. CREATE SYNONYM允许在自己的对象模式中创建同义词
2. CREATE ANY SYNONYM允许在任意对象模式中创建新的同义词
3. CREATE PUBLIC SYNONYM允许被授权者创建新的公用同义词。这些同义词对数据库中的所有用户都是可访问的。
4. DROP ANY SYNONYM允许从任意对象模式中删除任意一个同义词
5. DROP PUBLIC SYNONYM允许被授权者从数据库中删除任意一个公用同义词
4-6关于表对象的权限
1. select允许查询指定表
2. INSERT允许在指定表创建新行
3. UPDATE允许修改指定表的现有行
4. DELETE允许删除指定表的行
5. ALTER允许添加、修改或重命名指定表中的列，转移该表到另一个表空间，乃至重命名指定表。
6. DEBUG允许被授权者借助于一个调度程序访问指定表上的任意触发器中的PL/SQL代码
7. INDEX允许被授权者在指定表上创建新的索引
8. REFERENCES允许创建参考指定表的外部键约束
4-7关于视图对象的权限
1. SELECT查询指定视图
2. INSERT允许在指定视图创建新行
3. UPDATE允许修改指定视图的现有行
4. DELETE允许删除指定视图的行
5. DEBUG允许被授权者借助于一个调度程序访问指定视图上的任意触发器中的PL/SQL代码
6. REFERENCES允许创建参考指定视图的外部键约束
4-8关于序列的权限
1. SELECT允许访问当前值和下一个值(即CURRVAL和NEXTVAL)
2. ALTER允许修改指定序列的属性
4-9举例
例如:新创建的用户连基本的登陆权限就没有,必须以管理员的身份赋予权限
1--给予用户 会话和创建表的权限
可以进行的操作为:连接数据库,创建表,查询表内容容
grant create session to yjh;
grant create table to yjh;
grant select on tb_user to yjh;
无限使用表空间
grant unlimited tablespace to role_test;
 
 
revoke select on tb_user from yjh;
revoke unlimited tablespace from yjh;
select * from user_role_privs;
select * from tb_user;
2--赋予张三创建会话的权限
grant create session to zhangsan;
--无限使用表空间的权限
grant unlimited tablespace to zhangsan;
--允许数据库的权限
grant connect to zhangsan;
--创建序列的权限
grant create sequence to zhangsan;
 
--赋予用户创建表的权限
grant create table to zhangsan;
--赋予用户查询某张表的权限
grant select on tb_user to zhangsan;
--赋予用户某张表的所有权限
grant all on tb_user to zhangsan;
-----------------------------------
3-回收权限
--语法:revoke 权限名 from 用户名 ;
revoke all on tb_user from zhangsan;
revoke connect from zhangsan;
revoke create session from zhangsan;
 
5,角色和权限传递
5-1权限传递
系统权限：grant create session to zhangsan with admin option;
(表示把系统权限授予给zhangsan，并允许其授予给其他用户)
用户权限：grant update on mytab to lisi with grant option;
(表示把用户权限授予给lisi，并允许其授予给其他用户)
5-1-1传递系统权限用 with admin option
grant create session to zhangsan with admin option;
5-1-2传递用户权限用 with grant option
grant select on scott.emp to zhangsan with grant option;
---回收了zhangsan的用户权限,那么经过zhangsan传递的权限全部失效
---回收了zhangsan的系统权限,那么经过zhangsan传递的权限不会失效
revoke select on scott.emp from zhangsan;
5-2角色管理：
创建角色：create role roleName;
给角色授予权限：grant 权限 to roleName;
将角色授予给用户：grant roleName to userName;
用户查询拥有的角色：select * from user_role_privs;
删除角色：drop role roleName;
当给角色授予权限的时候，拥有此角色的用户也同时增加了权限；
当撤销角色权限的时候，拥有此角色的用户的对应权限也被撤销；
当角色被删除，拥有此角色的用户将丧失之前角色所有的所有权限。
5-2-1创建角色
创建角色role_test-拥有创建session和创建table的权限
create role role_test;
5-2-2给角色赋予权限
grant create session to role_test;
grant create table to role_test;
5-2-3把角色给softeem
grant role_test to softeem;
5-2-4收回角色,那么这个角色所拥有的权限全部收回
revoke role_test from softeem;
5-2-5删除角色,拥有此角色的用户对应的权限也失效
drop role role_test;
5-2-6授予角色 connect resource
grant connect to li;
grant resource to li;
5-2-7查询当前用户拥有的角色
select * from user_role_privs;

Oracle序列

序列简介
序列作为数据库里的对象，主要作用是生成唯一的主键值；
第二节：创建序列
create sequence 序列名称；
重要属性： 序列名称.currval 当前值nextval 下一个值
指定初始值： start with xx
第三节：序列属性
Minvalue maxvalue 最大值和最小值 默认最小值 1 最大值 10 的 27 次方； Increment by 属性 序列增长步长 默认值 1 Cycle 设置循环； （了解即可，不使用）；
Cache 缓存设置； 一次获取序列的多个连续值，默认 20 ，放到内存中，方便下次快速获取；
优化配置create sequence user_seq5 cache 40;




create sequence user_seq start with 100;
insert into t_user values(user_seq.nextval,"123","123");


create sequence user_seq4 increment by 3;


create sequence user_seq3 minvalue 0 maxvalue 100;


-- 表
create table test (names varchar2(12),
dates date,
num int,
dou double);
-- 视图
create or replace view vi_test as
select * from test;
-- 同义词
create or replace synonym aa
for dbusrcard001.aa;
-- 存储过程
create or replace produce dd(v_id in employee.empoy_id%type)
as
begin


end
dd;
-- 函数
create or replace function ee(v_id in employee%rowtype) return varchar(15)
is
var_test varchar2(15);
begin
return var_test;
exception when others then


end
-- 三种触发器的定义
create or replace trigger ff
alter delete
on test
for each row
declare
begin
delete from test;
if sql%rowcount < 0 or sql%rowcount is null then
rais_replaction_err(-20004,"错误")
end if
end


create or replace trigger gg
alter insert
on test
for each row
declare
begin
if :old.names = :new.names then
raise_replaction_err(-2003,"编码重复");
end if
end


create or replace trigger hh
for update
on test
for each row
declare
begin
if updating then
if :old.names <> :new.names then
reaise_replaction_err(-2002,"关键字不能修改")
end if
end if
end 
-- 定义游标
declare
cursor aa is
select names,num from test;
begin
for bb in aa
loop
if bb.names = "ORACLE" then


end if
end loop;


end
-- 速度优化，前一语句不后一语句的速度快几十倍
select names,dates 
from test,b
where test.names = b.names(+) and
b.names is null and
b.dates > date(''2003-01-01'',''yyyy-mm-dd'')


select names,dates
from test 
where names not in ( select names 
from b
where dates > to_date(''2003-01-01'',''yyyy-mm-dd''))
-- 查找重复记录
select names,num 
from test 
where rowid != (select max(rowid) 
from test b 
where b.names = test.names and
b.num = test.num)


-- 查找表TEST中时间最新的前10条记录
select * from (select * from test order by dates desc) where rownum < 11
-- 序列号的产生
create sequence row_id
minvalue 1
maxvalue 9999999999999999999999
start with 1
increment by 1
insert into test values(row_id.nextval,....) 

oracle表设计

插入 insert 语句
给指定列插入数据：insert into dept(deptno,dname) values(50,'xx');
插入全部列数据：insert into dept(deptno,dname,loc) values(60,'xx','lll');  简写 insert into dept values(70,'xxx','llll');


更新 update 语句
更新指定数据：update dept set dname='司法部' where deptno=50; update dept set dname='司法部' ,loc='china' where deptno=50;


删除 delete 语句
删除指定数据：delete from dept where deptno=70;
删除指定条件的数据：delete from dept where deptno>40;


查询 select 语句
查询所有：select * from emp;


指定字段查询：select ename,sal from emp;


加 where 条件：select * from emp where sal>=800; select * from emp where sal>=1500 and job='SALESMAN'; Distinct 去重复记录； Group by 分组查询：select job,count(ename) as num from EMP t group by job;


Having 过滤分组：select job,count(ename) as num from EMP t group by job having count(ename)>=2;


Order by 排序：select * from emp order by sal desc;


子查询：查询出基本工资大于平均工资的员工：select * from emp where sal>(select avg(sal) from emp)


联合查询：


并集（去重复）：


select * from t_user1 union
select * from t_user2;
并集：


select * from t_user1 union all
select * from t_user2;


交集：


select * from t_user1 intersect
select * from t_user2;


差集：


select * from t_user1 minus
select * from t_user2;


内连接：


select * from emp t,dept d where t.deptno=d.deptno;//关联查询只能查询有部门的
类似：select * from emp e inner join dept d on e.deptno=d.deptno; inner 可以省略；


外连接：


左外连接：select * from emp e left join dept d on e.deptno=d.deptno;左边所有数据全有不管右边有数据不存在null表示
右外连接：select * from emp e right join dept d on e.deptno=d.deptno;




1 模糊查询
模糊查询是相对于等值查询来说的
主要是通过关键字来查询
•例如只知道一个员工姓名的开头字母
•例如只知道一个地址中间的门牌号
•例如只知道一个商品的后面的名称
LIKE比较运算符要配合通配符来使用
•% 代表零个或多个任意字符
•_ 代表一个任意字符
可以使用 ESCAPE 标识符 选择‘%’和 ‘_’ 符号
查询job_id的倒数第5个字符是下划线的员工
select * from employees where job_id like '%\_____' escape '\';
（\为你卡的位置后面有5个下划线）


2 逻辑查询
FROM   employees
WHERE  salary >= 10000
AND     job_id LIKE '%MAN%';


查询部门是10,20 以及还没有部门的员工
SELECT employee_id, last_name, job_id, salary
select * from employees where department_id in(10,20) or department_id is null;


查询工资超过5000且以last_那么以及s结尾的员工
select * from employees where salary >5000 AND last_name like '%s';


3 排序子句
使用 ORDER BY 子句排序
•ASC: 升序（默认）
•DESC: 降序
ORDER BY 子句在SELECT语句的结尾
SELECT last_name, job_id, department_id, hire_date
  FROM employees
 ORDER BY hire_date;


SELECT last_name, job_id, department_id, hire_date
  FROM employees
 ORDER BY hire_date DESC;


查询员工编号，姓名，工资，入职日期，部门编号，按部门升序，工资降序，入职日期升序
select employee_id,last_name,salary,hire_date,department_id from employees order by department_id,salary desc,hire_date;


按第一列降序排列
select * from employees order by 1 desc


排序表达式可以是以下四种情况
列名
别名
表达式
列索引


多个排列表达式
SELECT last_name, department_id, salary
  FROM employees
 ORDER BY department_id, salary DESC;


可以使用不在SELECT 列表中的列排序。
大排序问题
排序默认产生的临时数据放到排序区的内存中，如果排序区不够用，就会利用临时表空间
大排序之前一定要做数据库优化


4 别名
别名可以随意起不一定是开头的字母
select * from departments d;


SELECT e.*
FROM   employees e,
       employees,
       employees,
       employees,
       employees,
       employees,
       employees
ORDER  BY e.last_name;


select e.* 
from employees e // 没有逗号
ORDER BY e.last_name;


Rownum和rowid伪列，查询表中前5条数据查询结果的自增编号
以下条件一定为假
rownum>n rownum>=n rownum=1以外的值


select rownum, e.* //rownum后面有逗号
from employees e 
where rownum <=5


select rownum,employee_id,last_name,salary from employees where department_id = 50;


1-3之间
select rownum,e.* from employees e where rownum between 1 and 3


rowed唯一的id硬件
select rowid,rownum,employees.* from employees;


多个排序表达式
SELECT last_name, department_id, salary
  FROM employees
 ORDER BY department_id, salary DESC;


查询员工的编号，姓名，部门名称
Select employee_id,last_name,department_name from employees,departments


Sql 1992链接查询法
使用连接在多个表中查询数据。
在 WHERE 字句中写入连接条件。
在表中有相同列时，在列名之前加上表名前缀。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;


查询员工的编号，姓名，部门名称 多表重复效率
select employee_id,last_name,department_name from employees e,departments d where e.department_id=d.department_id


5 多表查询
都存在的项目必须有前缀 d.
查询员工编号，姓名，部门编号，部门名称
select employee_id,last_name,department_name,d.department_id from employees e,departments d where e.department_id=d.department_id




有重复的都用where。And 对应重复出现的项目
SELECT employees.employee_id,
       employees.last_name,
       employees.department_id,
       departments.department_id,
       departments.location_id
  FROM employees, departments


多个连接条件与 AND 操作符，and后面的项目是一致的
查询员工编号，姓名，部门编号，部门名称，职务名称
select employee_id,last_name,d.department_id,department_name,job_title 
from employees e,departments d,jobs j 
where e.department_id=d.department_id 
and e.job_id=j.job_id
and e.salary >8000
ORDER BY e.salary


区分重复的列名
使用表名前缀在多个表中区分相同的列。
使用表名可以提高效率。
在不同表中具有相同列名的列可以用别名加以区分。
使用别名可以简化查询。
使用表名前缀可以提高执行效率。


SELECT e.employee_id,
       e.last_name,
       e.department_id,
       d.department_id,
       d.location_id
  FROM employees e, departments d
 WHERE e.department_id = d.department_id;




6 连接多个表
连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件


查询所有部门，部门名称，部门的经理ID，部门的经理姓名，部门所在的城市，地区
select d.department_id,d.department_name,e.manager_id,e.last_name manager_name,l.city,r.region_name //manager_name 新名字
 from departments d,employees e,locations l,regions r,countries c
 where d.manager_id=e.employee_id
 and d.location_id=l.location_id
 and l.country_id=c.country_id
 and c.region_id=r.region_id




7 非等值连接
查询每个员工的编号，姓名，工资，工资级别（job_grades）
select e.employee_id,e.last_name,e.salary,g.grade_level
 from employees e,job_grades g
 where e.salary between g.lowest_sal and g.highest_sal




8 内连接，外连接
内连接：查询仅仅返回满足条件的记录
外连接的符号是 (+)。
使用外连接可以查询不满足连接条件的数据。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;


SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);
查询员工的编号，姓名，部门名称
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id = d.department_id


外连接，没有部门的员工也返回
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id = d.department_id（+）


没有员工的部门
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id（+） = d.department_id（+）


查询每个部门编号，部门名称，部门经理，部门经理姓名（没有部门经理的部门返回）
select * from departments d,employees e
where e.manager_id(+)=d.manager_id


9 自连接
WORKER 表中的MANAGER_ID 和 MANAGER 表中的EMPLOYEE_ID相等
SELECT worker.last_name || ' works for ' || manager.last_name
  FROM employees worker, employees manager
 WHERE worker.manager_id = manager.employee_id


查询员工编号，姓名，员工管理者编号，员工管理者姓名（自连接）
select e.employee_id,e.last_name,m.employee_id,m.last_name
 from employees e,employees m
 where e.manager_id=m.employee_id(+)


查询谁的工资比abel高（自连接）其中m代表Abel
select * from employees e,employees m
where e.salary > m.salary
and m.last_name='Abel';




10 SQL 1999 语法连接
内连接
外连接：左外连接，右外连接，慢连接
在SQL: 1999中，内连接只返回满足连接条件的数据。
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外联接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外联接。


内连接查询
ON后面是条件
查询员工编号，姓名，部门编号，部门名称，职务编号，职务的名称
可以使用 ON 子句指定额外的连接条件。这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性
select * 
from employees e
INNER JOIN departments d ON e.department_id=d.department_id


ON子句创建多表连接
SELECT employee_id, city, department_name
  FROM employees e
  JOIN departments d
    ON d.department_id = e.department_id
  JOIN locations l
ON d.location_id = l.location_id;


左外连接
查询员工的编号，姓名，部门编号，部门名称
select e.employee_id,e.last_name,d.department_id,d.department_name
from employees e （左边的相对于departments，上面的永远在右边下面从左出来）
LEFT OUTER JOIN departments d ON e.department_id=d.department_id
左边是20 右边内连接19


右外连接
select e.employee_id,e.last_name,d.department_id,d.department_name
from employees e（右边相对于departments，上面的在右边下面的从右出来就是自己出来）
RIGHT OUTER JOIN departments d ON e.department_id=d.department_id
左19不重名 右边重名


满连接
select e.employee_id,e.last_name,d.department_id,d.department_name
from employees e
FULL OUTER JOIN departments d ON e.department_id=d.department_id
都是20 
11叉集 
使用CROSS JOIN 子句使连接的表产生叉集。叉集和笛卡尔集是相同的。代码冗余
SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;


有些有s项目里面没有s 项目本身带s
select e.employee_id,e.last_name,d.department_id,department_name
from employees e
CROSS JOIN departments d
INNER JOIN jobs j ON e.job_id = j.job_id