[这个贴子最后由轮胎菲拉洛在 2005/07/28 06:20pm 第 2 次编辑]
1.sql(学习目录中的9isql)
(1)select
(2)sqlplus管理oracle的工具
运行sqlplusw /nolog或sqlplus /nolog 启动到界面
nolog不与任何用户连接
*运行 connect system/manager 连接到sql用户
show user 显示用户
*运行 alter user system(用户) identified by manager;(密码)以’;’结束
验证:connect system/manager ‘语句通用’
解锁: conn / as sysdba
alter user system account unlock identified by manager;
conn system/manager
*运行 建立模型库
忘记密码:conn /as sysdba (联接到老大)－－老大指数据库权限最大的管理者
alter user system account unlock identified by manager;
conn system/manager
*conn teach/oracle
*select * from tab; /准备好实验环境/看当前用户有哪些表.
*select table_name from user_tables;
select 语句的作用:查表的列.行及多张表联合查询
*select * from DEPARTMENTS;看一张表所有的列
*select DEPARTMENT_ID,DEPARTMENT_NAME from DEPARTMENTS;
语句写法：
A.大小定不敏感
B.关键字不能缩写也不能跨行写
C.子名通常放在不同的行
D.能被放在一行或多行
E.续行用’-‘
*SELECT LAST_NAME,COMMISSION_PCT FROM employees;
给列起别名:A改的是头名;B利于计算;C增加可读性;D显示小写加’ “ ‘
*SELECT LAST_NAME name,COMMISSION_PCT comm FROM employees;
*select LAST_NAME||SALARY||HIRE_DATE from employees; ‘||’并列显示
*字符串用’’
*l(list) 列出上一条语句，sql下的输入方式
* select distinct DEPARTMENT_ID from employees; 消除重复的行
与SQL联系的就与数据库有关
SQL是结构性语言,结构化查询,是美国的一个标准,不支持缩写
SQLPLUS是一个小程序,是ORACLERR 语言,不操作数据库,可以在IE下运行,支持缩写;
SQL SERVER 是微软的数据库
PL/SQL 是ORACLE的一个模块下编程的数据库语言
*desc ‘描述表结构’
展开备注页:视图---备注页
*desc employees
称 是否为空? 类型
-------------------------------------- -------- -------------
LOYEE_ID NOT NULL NUMBER(6)
ST_NAME VARCHAR2(20)
T_NAME NOT NULL VARCHAR2(25)
IL NOT NULL VARCHAR2(25)
NE_NUMBER VARCHAR2(20)
E_DATE NOT NULL DATE
_ID NOT NULL VARCHAR2(10)
ARY NUMBER(8,2)
MISSION_PCT NUMBER(2,2)
AGER_ID NUMBER(6)
ARTMENT_ID NUMBER(4)
*SELECT select last_name,salary from EMPLOYEES where DEPARTMENT_ID=90;
字符串大小写敏感,日期格式敏感
*select last_name,salary from EMPLOYEES where salary<3000;
*select last_name,salary from EMPLOYEES where salary between 2500 and 3000;含边界
* select last_name,salary from EMPLOYEES where salary in(17000,4000);指定具体的值
*select last_name from EMPLOYEES where last_name like ’s%’ or last_name like ‘%A%’;选择第一个字母为’s’或含有’A’的人
逻辑运算符的前后是两个表达式
‘*’为通配所有字符,’_’为通配一个字符
* SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA/_%' ESCAPE '/';找job_id中含SA_的行
*select first_name from employees where first_name like '%/_%' escape '/';
* SELECT LAST_NAME ,COMMISSION_PCT FROM employees
WHERE COMMISSION_PCT IS NULL;
* SELECT LAST_NAME ,COMMISSION_PCT FROM employees
WHERE COMMISSION_PCT IS NOT NULL;
* SELECT LAST_NAME ,manager_id FROM employees
WHERE manager_id IS NOT NULL;
算术运算高于逻辑运算,NOT高于and高于or
* SELECT LAST_NAME ,COMMISSION_PCT FROM employees
WHERE COMMISSION_PCT IS NOT NULL order by salary/desc (反序);
* SELECT LAST_NAME ,DEPARTMENT_ID,SALARY
FROM employees
ORDER BY DEPARTMENT_ID,SALARY DESC; 多列排序按列的出现先后
不同的子句尽量放在不同的行
select last_name ,salary Monthly_Salary from employees WHERE department_id in(20,50,30) and
salary between 5000 and 12000
order by last_name;
浪淘沙
[免费送钱] 岁末年初有奖征文，想要什么自己定！
2005/07/28 05:59pm IP: 已设置保密 [本文共5722字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 2 楼]
函数
单行函数 :数据库中每一行都有一个值及作用有每一行
字符串函数:大小写,串长或子串
大小写
*SELECT LOWER小写('SQL course'),upper大写('SQL course'),initcap第一个字母大写('SQL course') from dual(一张虚拟表以获得函数的值);
*SELECT LOWER('SQL course'),upper('SQL course'),initcap('SQL course') from employees;每有一行运算一次
('SQL UPPER('SQL INITCAP('S
----- ---------- ----------
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
择20行。运行结果
字符串运算:
CONCAT字符串联接;SUBSTR子串运算;LENGTH求串长;INSTR子串在父串中出现的位置;LPAD向左填充;RPAD向右填充;TRIM去掉指定的字母;replace
Select lpad(last_name,20,’*’) from employees;
*select concat(last_name,first_name) from employees
where length(last_name)>5;
数值函数:
日期函数:数据库内部存储格式一致
select sysdate from dual;从系统中取当前日期及时间
日期相减得数为天
select LAST_NAME,round取整(sysdate-HIRE_DATE ) days from EMPLOYEES ;
日期相加为一个日期(具体的那一天)
字符串的转换:稳示的转换char—numb;char—date
显示的转换
*select LAST_NAME,to_char(hire_date,'yyyy/mm/dd') from employees;
*select LAST_NAME,to_char(hire_date,'yyyy/mm/dd” ”year” ”day” ”dy” ”month') from employees;
select LAST_NAME,to_char(hire_date,'fmyyyy/mm/dd') from employees;
select LAST_NAME,to_char(hire_date,'ddspth') from employees;
select LAST_NAME,to_char(hire_date,'yyyy/mm/ddyear " of "day" in "month') from employees;
*select LAST_NAME,to_char(salary,'l99999999.9999') from employees;L本地货币,9的个数代表显示的位数
“yy”与操作者所在世纪保持一致
“RR”指前半年世纪与后半个世纪
select to_char(sysdate,'yyyy') current_year,
to_char(to_date('08','yy'),'yyyy') "yy08",
to_char(to_date('95','yy'),'yyyy') "yy95",
to_char(to_date('08','rr'),'yyyy') "rr08",
to_char(to_date('95','rr'),'yyyy') "rr95"
from dual;
当前日期为2005年
SQL> select to_char(sysdate,'yyyy') current_year,
2 to_char(to_date('08','yy'),'yyyy') "yy08",
3 to_char(to_date('95','yy'),'yyyy') "yy95",
4 to_char(to_date('08','rr'),'yyyy') "rr08",
5 to_char(to_date('95','rr'),'yyyy') "rr95"
6 from dual;
CURR yy08 yy95 rr08 rr95
---- ---- ---- ---- ----
2005 2008 2095 2008 1995
当前日期为1999年
nvl(a,b)函数:如果a是not null 就是a,如果a是null 就是b
nvl2(a,b,c)就是a是not null 就是b,如果a是null 就是c
nullif(a,b)就是a=b 就是a,如果a<>b就是null
coalesce(a,b,c,d,e,…….)直到找到第一个非空的值
DECODE子句
select last_name,job_id,salary,
decode(job_id,'AD_PRES','A',
'ST_MAN','B',
'IT_PRGO','C',
'SA_REP','D',
'ST_CLERK','E',
'0') JOB
from employees
/
系统自动记录运行情况spool 文件名…….spool off
多张表联合查询:
非国标连接:迪卡尔连接.等值连接.不等连接.外键连接.自连接
迪卡尔连接:
*select LAST_NAME,DEPARTMENT_NAME from EMPLOYEES, DEPARTMENTS;
等值连接
*select LAST_NAME,DEPARTMENT_NAME
from EMPLOYEES, DEPARTMENTS
where EMPLOYEES.DEPARTMENT_id=DEPARTMENTS.DEPARTMENT_id; (看每个人在哪能个部门上班)
浪淘沙
[免费送钱] 岁末年初有奖征文，想要什么自己定！
2005/07/28 06:00pm IP: 已设置保密 [本文共4867字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 3 楼]
设置显示环境
控制每一列的宽度:column 列名 for 宽度
col DEPARTMENT_NAME for a15(设定每列宽度)
col last_name for a15
set linesize 100(设定每页显示行数)
查询部门号和地址号相同的员工名.部门号.部门名.地址号和城市
*select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,l.city
from EMPLOYEES e, DEPARTMENTS d,LOCATIONS l(表的别名)
where E.DEPARTMENT_id=D.DEPARTMENT_id
and d.LOCATION_ID=l.LOCATION_ID
order by 2(可用列号排序);
不等连接
*select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,l.city
from EMPLOYEES e, DEPARTMENTS d,LOCATIONS l
where E.DEPARTMENT_id=D.DEPARTMENT_id 各条件中用and接连
and d.LOCATION_ID=l.LOCATION_ID
and lower(e.last_name)=’king’;(显示每个员工的工作总门及部门所在地)
* select 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;(显示每个员工的工资级别)
外键连接
*select e.LAST_NAME,e.DEPARTMENT_ID "edept" ,d.DEPARTMENT_ID "ddept",d.DEPARTMENT_NAME
from EMPLOYEES e, DEPARTMENTS d
where E.DEPARTMENT_id(+)=D.DEPARTMENT_id; (+)外键连接,加在少的一方
(+)可加在=的两边,但两边不能同时加
自连接:
*select w.LAST_NAME,m.last_name
from employees w,employees m
where m.EMPLOYEE_ID(+)=w. MANAGER_ID; 查询每个员工的经理
查询一个数据库中含哪些表:select * from tab;
国标连接:实质连接.自然连接.不等连接.自连接
实质连接
*select d.DEPARTMENT_NAME,l.CITY
from DEPARTMENTS d,LOCATIONS l
where d.LOCATION_ID =l.LOCATION_ID;
*select LAST_NAME,DEPARTMENT_NAME
from EMPLOYEES cross join DEPARTMENTS;
自然连接
列同名的都做为等值条件,各条件间为and关系
查询部门号和经理号都相同的员工名及地点
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS natural join EMPLOYEES(两个条件同时成立)
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS d,EMPLOYEES e
where d. DEPARTMENT_ID=e.DEPARTMENT_ID
and d.MANAGER_ID=e.MANAGER_ID; (两个条件同时成立)
查询部门号相同的员工名及地点
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS d,EMPLOYEES e
where d. DEPARTMENT_ID=e.DEPARTMENT_ID;(一个条件成立)
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID); (一个条件成立)
不等连接
查询员工的工资级别
*select 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;(条件在一个范围下)
*select LAST_NAME,salary,grade_LEVEL
from EMPLOYEES e join job_grades g
on(e.salary between g.LOWEST_SAL and g.highest_sal); (条件在一个范围下)
自连接
*SELECTe.department_id department, e.last_name employee,
c.last_name colleague
FROM employees e JOIN employees c
ON (e.department_id = c.department_id)
WHERE e.employee_id <> c.employee_id
ORDER BY e.department_id, e.last_name, c.last_name;查询一张表中部门号相同的员工
浪淘沙
[免收开户费] 114竞价广告，按效果付费，不点击不付费!
2005/07/28 06:00pm IP: 已设置保密 [本文共3118字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 4 楼]
第五章 主函数
所有的主函数除count 外都不计null值
分组
每个部门的平均工资
*select department_id,avg(salary) from employees
group by department_id;
多列分组
查每个部门不同工种的平均工资
*select department_id, job_id,avg(salary)
from employees
group by
DEPARTMENT_ID,job_id;
二次淘汰 having,作用在分组之后
子查询(内部查询)
先于外部查询运行
用在()中;关系运算的右边;不能用order dy 子句;单对单,多对多
关系运算:in .any.all
*select employee_id,last_name,salary
from employees
where salary >(select avg(salary) from employees)
order by salary
/
替代变量&.&&
SQL> select LAST_NAME from employees where employee_id=&1;&替代变量,执行一次
输入 1 的值: 206
LAST_NAME
---------------
Gietz
已选择 1 行。
&&全程替变量
show all /看sqlplus当前所有的配置/
set feedback 10 运行结果反回的行数
set heading off 运行结果不反回头部
set heading on
col last_name heading 'employee|name' 设置列名头
select last_name from employees;
clear screen 清屏
创建一个用户:
(3)数据操作语言(dml):insert into/update/delete/merge
使用 scott/tiger用户,含有6张表
insert into向表中插入一行,暂时存入内存
显示插入,隐示插入
*insert into d select * from d; 自身插入自身
commit;向表内写入添加操作,并且结束当前进程
update 修改已有的数据
update …. Set ….
*update e set sal=sal+1; 修改列值
*select ename,sal from e; 检证修改
*update e set sal=sal+1 where deptno=10; 修指定条件的值
*select ename,sal from e; 检证修改
帐号被锁:
conn / as sysdba
drop user scott cascade;
@%oracle_home%/rdbms/admin/scott
*update e set ename='smith2' where empno=7369;修改指定条件的指定值
delete 删除指定行
delete from …where …
*delete e where deptno=10;
*delete e where sal>2000;
融合(merge)把后一张表与前一张不同的内容写入前一张表
生成两表
CONN SCOTT/TIGER
DROP TABLE E1;
DROP TABLE E2;
CREATE TABLE E1 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO=10;
CREATE TABLE E2 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO IN (10,20);
UPDATE E2 SET SAL=SAL+100;
COMMIT;
把e2写入e1
merge into E1
USING E2
ON (E1.EMPNO=E2.EMPNO)
WHEN MATCHED THEN
UPDATE SET
E1.SAL=E1.SAL
WHEN NOT MATCHED THEN
INSERT valueS(E2.EMPNO,E2.ENAME,E2.SAL);
transaction(事物,交易)是一个整个要么都成立,要么不成立.Cmmit/rollback
commit:显式提交(commit)/隐式提交(ddl.dcl.exit)
rollback:显式回退(rollback)/隐式回退(中断程序,数据库崩溃)
事物进行过程中:1.加锁;2.占用回退段;3,独一性,其他用户看不用当前变动的结果
建立一个表进行写入,设立存储点,然后回退到指定存储点.
conn scott/tiger
drop table e;
create table e as select * from emp;
update e set sal=10 where deptno=10;
savepoint u10;
update e set sal=10 where deptno=20;
savepoint u20;
update e set sal=10 where deptno=30;
savepoint u30;
delete e where sal>15;
select * from e;
rollback to savepoint u30;
select * from e;
rollback to savepoint u20;
select * from e;
rollback to savepoint u10;
select * from e;
rollbackup ;
select * from e;
浪淘沙
[免费送钱] 岁末年初有奖征文，想要什么自己定！
2005/07/28 06:01pm IP: 已设置保密 [本文共3363字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 5 楼]
创建与维护表
*select object_name,object_type from user_objects;当前用户所有的对象及对象类型
对象名唯一
drop table t1;删除表t1
create table t1(name char(10),day date);创建t1表
select table_name from user_tables;查看用户中的表
desc t1; 查看表t1的结构
表分为user 与dictionary两类
查看系统字典
conn system/manager
select table_name,owner from dba_tables where table_name like '%$';
create table t1 as select ename,12*sal annsal from emp;创建一个和t1表
alter table t1 add(comm number(3));向t1表中增加一列
alter table ti modify (comm. Number(6));修改表t1中的comm列的数个类型
ALTER TABLE T1 DROP (COMM);删除指定的列
RENAME T1 TO TTT; 把t1改为ttt
COMMENT ON TABLE TTT IS 'THIS TABLE IS RENAME FROM T1;为表t1加注示
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME='TTT';查看注示
COMMENT ON COLUMN EMP.ENAME IS 'THIS IS EMPLOYEE NAME'; 为指定的列加注示
SELECT * FROM USER_COL_COMMENTS WHERE COLUMN_NAME='ENAME' AND TABLE_NAME='EMP';查看列的注示
约束:(第十章)
*create table d as select * from dept;
*alter table d modify (dname not null);产生一个非空约束
*select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints;查看用户约束的约束名和约束类型
*select * from user_cons_columns;查看当前用户约束列
*select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;查看表与列的约束名称.类型
非空约束:
*insert into d values(null,'a','k'); 插入非空约束
*insert into d values(50,null,'s'); 插入一个空约束,系统禁止插入
*select * from d;查看表
唯一约束:
*alter table d add constraint d_u_deptno unique (deptno);对表d插入一个唯一约束在deptno列上
*select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints;验证约束
* select index_name,table_name from user_indexes;查看索引
*insert into d values(null,'a','k');
*insert into d values(null,'a','k');成功
*select * from d;
查询结果表中含有多个相同的空值
唯一性通过索引实现,null不入索引,所以可以插入多个null.
联合约束:
conn scott/tiger
drop table d;
create table d as select * from dept;
alter table d add constraint d_u_deptno_lianhe unique (deptno,dname);创建一个联合约束
select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;
select index_name,table_name from user_indexes; 查看索引
insert into d values(null,null,'a');可以插入
insert into d values(null,null,'a');可以插入
insert into d values(null,'d','a');可以插入
insert into d values(null,'d','a');不可以插入
主键(primary key):非空+唯一,可以是联合的
conn scott/tiger
drop table d;
create table d as select * from dept;
alter table d add constraint d_pk primary key (deptno);在deptno列上设立主键
select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;查询约束设置
select index_name,table_name from user_indexes; 查询索引
insert into d values(null,null,'a');不能插入
insert into d values(10,null,'a'); 不能插入
外键(foreign key):在引用的表已存在主键的情况下,本表将主键设立为外键
alter table e add constraint e_fk foreign key (deptno) references d(deptno);在表e的deptno列上设立外键
select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;查询约束设置
delete d where deptno=10; 不允许删除,已找到子记录日记
update e set deptno=12 where deptno=10;不允许插入,未找到父关键字
alter table e drop constraint e_fk;删除一个约束
alter table e add constraint e_fk
foreign key (deptno) references d(deptno)
on delete cascade; 设立一个约束,当d表的deptno列删除时,e表的相应列也删除
delete d where deptno=10;验证
rollback;回退
alter table e drop constraint e_fk; 删除一个约
alter table e add constraint e_fk
foreign key (deptno) references d(deptno)
on delete set null;设立一个约束,当d表r deptno列删除时,e表的相应列设为null值.
delete d where deptno=30;
检测约束(check):
alter table e drop constraint e_fk;删除一个约束
alter table e add constraint e_check
check (deptno<99); 检测约束,表中的每一行必须满足检测条件
浪淘沙
[免收开户费] 114竞价广告，按效果付费，不点击不付费!
2005/07/28 06:01pm IP: 已设置保密 [本文共4837字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 6 楼]
视图view(第十一章)
从一张或多张表获得数据的子集
conn scott/tiger
drop table e;
create table e as select * from emp;
create view e30
as select ename name,sal salary from e where deptno=30;创建一个视图,在表e的deptno=30时
select view_name,text from user_views;查看视图
desc e30;查看视图结构
select * from e30; 查看视图
视图作用:
限制查看,生成简单查询,每个查询是独立的,不同的窗口不同的数据
视图中不存储数据,视图中的修改,是对基表的修改
To r通信
drop table d;
create view dv as select dname,loc from d; 不允许在没有基表的情况下创建视图
drop table d;
create force view dv as select dname,loc from d;强制设立一个视图,创建的视图带有编译错误select view_name,text from user_views;查看视图
conn system/manager
set long 10000
select text from dba_views where view_name='USER_USERS';
通常情况下不通过视图修改基表
CONN SCOTT/TIGER
SELECT ROWNUM,ENAME FROM EMP; 假的列(rownum)
create OR REPLACE view e30
as select ename name,sal salary,DEPTNO from e where deptno=30;
ROLLBACK;
create OR REPLACE view e30
as select ename name,sal salary,DEPTNO from e where deptno=30
WITH CHECK OPTION; 设定限制修改条件(deptno=30不能修改),及where 语句中的条件不变.
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM user_constraints; 查询约束
DROP VIEW E30;删除视图定义
select rownum rank ,ename,sal
from (select ename,sal from emp order by sal desc---是一个视图)
where rownum<=3; 内嵌式视图,查看工资前三名
CREATE OR REPLACE VIEW V1 AS select ename,sal from emp order by sal desc;
SELECT rownum rank ,ename,sal FROM V1 where rownum<=3; 创建一个视图
序列(第十二章)
SELECT * FROM USER_SEQUENCES;查看现有序列,没有显示行
CREATE SEQUENCE S1;创建序列
SELECT * FROM USER_SEQUENCES;查看现有序列,有一个序列
SELECT S1.NEXTVAL FROM DUAL; 查看下一个值
SELECT S1.CURRVAL FROM DUAL;查看当前值
自动生成序列值,公用的与表同级,自动生成主键,
CREATE SEQUENCE S2 定义序列名
INCREMENT BY 10 定义步长
START WITH –20 定义起始值
MAXvalue 30 定义最大值
MINvalue -50 定义最小值
NOCYCLE 不循环
NOCACHE; 不发生,不放入内存…..创建一个序列
SELECT S2.NEXTVAL FROM DUAL;查看序列s2
CREATE SEQUENCE S3
INCREMENT BY 1
START WITH 1
MAXvalue 10
MINvalue -10
CYCLE
CACHE 5; 一次计数5个,放入内存,容易产生缝隙
SELECT SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER FROM USER_SEQUENCES; 查看序列名.序列值
SELECT S3.NEXTVAL FROM DUAL; 查看序列的下一个值
索引
自动创建,手动创建
CREATE INDEX IE ON E(EMPNO);创建一个基于表e中empno列的索引
SELECT I.INDEX_NAME,I.TABLE_NAME,COLUMN_NAME
FROM USER_INDEXES I,USER_IND_COLUMNS C
WHERE I.INDEX_NAME=C.INDEX_NAME; 查询创建的索引
同义词
CONN SYSTEM/MANAGER
SELECT * FROM EMP;查不到
SELECT * FROM SCOTT.EMP;指定绝对路径后可查到
CREATE SYNONYM EMP FOR SCOTT.EMP;定义同义词
SELECT * FROM USER_SYNONYMS WHERE TABLE_NAME='EMP';在系统表中查找同义词’emp’
SELECT * FROM EMP;可以查询到表EMP
DROP SYNONYM EMP;删除同义词EMP
SELECT * FROM EMP; 不能查询到表EMP
(4)ddl
集合
conn scott/tiger;
drop table t1;
drop table t2;
create table t1 as select deptno,empno,sal,ename from emp where deptno=10;
create table t2 as select deptno,empno,sal,ename from emp where sal>2000;
select * from t1
union all
select * from t2;并排显示两张表,不删除重复的行
select * from t1
union
select * from t2; 并排显示两张表,删除重复的行,并且排序
select * from t1
intersect
select * from t2;显示两张表中相同的部分
select * from t1
minus
select * from t2;显示t1表中不含t2表所含的内容(补集)
浪淘沙
[这是真的吗?] 350 + 100 + 150 = 350 ?
2005/07/28 06:01pm IP: 已设置保密 [本文共4095字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 7 楼]
高级分组(第十七章)
select deptno,sum(sal) from emp group by deptno;
select deptno,sum(sal) from emp group by rollup(deptno);上卷式分组,
select deptno,sum(sal) from emp group by deptno
union all
select null,sum(sal) from emp; 与上卷分组结果一样
select deptno,job,sum(sal) from emp group by rollup(deptno,job); 根据部门.工作统计工资合
select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,null,sum(sal) from emp
order by 1; 与上句结果相同
select deptno,job,sum(sal) ,grouping伪列(deptno),grouping(job)
from emp group by rollup(deptno,job);统计参加分组
如果有N个集合,上卷是N+1个集合的并集
立方分组
select deptno,job,sum(sal) ,grouping(deptno),grouping(job)
from emp group by cube(deptno,job); 按工种.部门
高级子查询(第十八章)
select ename,sal, DEPTNO from emp outer
where sal >( select avg(sal) from emp
where DEPTNO=outer.DEPTNO);
存在(exists):测试查询有没有值
select ename,job,DEPTNO,sal from emp outer
where exists
(select 'x' from emp
where lower(outer.job) like 'manager');查找部门经理
select ename,job,DEPTNO,sal from emp outer
where exists
(select 'x' from emp
where outer.empno=emp.mgr) ;查找部门经理
conn scott/tiger;
drop table e;
create table e as select * from emp;
alter table e add(dname varchar2(10));
alter table e drop column hiredate;
select * from e;
update e set dname=(select dname from dept where dept.deptno=e.deptno); 向表e中加入dname列并且赋值
with子句:
爬树(第十九章)
select ename,empno,mgr from emp;
select level(在第几层找到) ,ename from emp
start with ename=’smith’
connect by
PRIOR MGR=EMPNO;查找’smith’的上级
select LEVEL(伪列,在第几层找到),ename from emp
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR; 向下查找各级成员
select LPAD('-',LEVEL)向左垫||ename from emp
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR;
剪枝(pruning
select LPAD('-',LEVEL)||ename from emp
WHERE ENAME<>'BLAKE'
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR; 去掉指定的人;
select LPAD('-',LEVEL)||ename from emp
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR
AND ENAME<>'BLAKE'; 去掉从指定的人开始时的一枝
(5)高级SQL-------pl/sql
2.过程
匿名块
conn scott/tiger
set serveroutput on
begin
dbms_output.put_line('hello world');
end;
/ 在屏幕上显示’hello world’
定义变量
一行赋一个变量,以分号结束,constant 常量
conn scott/tiger
set serveroutput on
declare
v1 date;
v2 number(8);定义变量 v1.v2
v1:=sysdate;
v2:=12345;向v1.v2赋值
begin
dbms_output.put_line(V1);
dbms_output.put_line(V2);
end;,输出v1.v2
/
conn scott/tiger
set serveroutput on
declare
v1 emp_ename%type;
v2 emp_sal%type;
v3 v2%type;定义变量 v1.v2.v3,%type与指定表的列保持相同的属性
v1:=smith;
v2:=800;
v3:=1000;向v1.v2. .v3赋值
begin
dbms_output.put_line(V1);
dbms_output.put_line(V2);
dbms_output.put_line(V3);
end;,输出v1.v2.v3
/
declare
v1 char(20);
v2 varchar2(80);
v3 number(10):=1;
begin
v1:=to_char(sysdate ,'yyyy/mon/dd');
v2:='ww'||chr(10)||'we'||chr(10)||'dd';
v3:=v3+1;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
declare
v1 emp.ename%type;
v2 emp.sal%type;
v3 v2%type;
begin
select ename,sal into v1,v2 from emp
where empno=7900;
v3:=v2+300;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
/
declare
v1 number(2);
begin
delete e where deptno=30;
v1:=sql%rowcount;系统定义游标,当前语句处理表中的行数
dbms_output.put_line(to_char(v1)||' rows is delete');
--dbms_output.put_line(v2);
--dbms_output.put_line(v3);
end;
/
循环
if …
elsif ….
Else…
Endif
Loop
….
…
Exit (when …).
endloop
for I计数器,不须声明 in 1….10 loop 步长总是1,只能引用不能修改
insert into ….
End loop;
While …..loop
….;
….;
End loop;
declare
v1 char(20);
v2 varchar2(80);
v3 number(10):=1;
begin
v1:=to_char(sysdate ,'yyyy/mon/dd');
v2:='ww'||chr(10)||'we'||chr(10)||'dd';
v3:=v3+1;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
declare
type type1 is record定义新的记录类型
(f1 emp.ename%type,f2 emp.sal%type);
v1 type1;定义数据类型
begin
select ename,sal into v1 from emp where empno=7900;
dbms_output.put_line(v1.f1);
dbms_output.put_line(v1.f2);
end;
/
declare
v1 emp%rowtype;与emp表的结构保持一致
begin
select * into v1 from emp where empno=7900;
dbms_output.put_line(v1.ename);
dbms_output.put_line(v1.sal);
dbms_output.put_line(v1.deptno);
end;
/
表名%rowtype 定义一个复合的数据类型,与指定表一致
定义集合:
declare
type newtype is table of
emp.ename%type
index by binary_integer;定义一个新的集合newtype,与emp.ename保持一致的数据类型,有一个关于binary_integer(二进制整型)的主键盘,集合必须有主键
v1 newtype;
begin
select ename into v1(1) from emp where empno=7900;
select ename into v1(4) from emp where empno=7902;
select ename into v1(9) from emp where empno=7839;
dbms_output.put_line(v1(1));
dbms_output.put_line(v1(4));
dbms_output.put_line(v1(9));
end;
/
declare
type newtype is table of
emp%rowtype
index by binary_integer;集合的数据类型与emp表的一行一致
v1 newtype;
begin
select * into v1(1929) from emp where empno=7900;
select * into v1(4000) from emp where empno=7902;
select * into v1(9000) from emp where empno=7839;
dbms_output.put_line(v1(1929).ename||' job is '||v1(1929).job);
end;
/
浪淘沙
[这是真的吗?] 350 + 100 + 150 = 350 ?
2005/07/28 06:02pm IP: 已设置保密 [本文共6593字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 8 楼]
游标(cursors)
declare
cursor c1 is select ename,sal from emp order by sal desc;定义游标
ct number(3):=1;定义计数器
v1 emp.ename%type;定义变量v1
v2 emp.sal%type;
begin
open c1;打开游标,把指针指向第一个数据
loop
fetch c1 into v1,v2;提取游标中的数据
dbms_output.put_line(v1||' salary is '||to_char(v2));
ct:=ct+1;
exit when ct>3;
end loop;
end; declare
cursor c1 is select ename,sal from emp order by sal desc;
ct number(3):=1;
v1 emp.ename%type;
v2 emp.sal%type;
begin
open c1;
loop
fetch c1 into v1,v2;
dbms_output.put_line(v1||' salary is '||to_char(v2));
ct:=ct+1;
exit when ct>3;
end loop;
end;
/生成emp表中工资前三名
游标的属性:isopen notfount fount rowcount
declare
cursor c1 is select ename,sal from emp order by sal desc;
ct number(3):=1;
v1 c1%rowtype;与游标的类型一致
begin
open c1;
loop
fetch c1 into v1;
ct:=ct+1;
exit when ct>30 or c1%notfound;
dbms_output.put_line(v1.ename||' salary is '||to_char(v1.sal));
end loop;
end;
/按工资的高低显示emp表的人名与相应的工资.
declare
cursor c1 is select ename,sal from emp order by sal desc;
begin
for i in c1 loop
dbms_output.put_line(i.ename||' salary is '||to_char(i.sal));
end loop;
end;
/游标配合for循环,对表进行查询
begin
for i in (select ename,sal from emp order by sal desc) loop隐示的游标
dbms_output.put_line(i.ename||' salary is '||to_char(i.sal));
end loop;
end
游标可带变量,
declare
cursor c1 is select ename,sal from emp order by sal for update nowait;打开一个可以修改的游标
v1 number;
begin
for i in c1 loop
v1:=c1%rowcount;
update emp set sal=sal+v1
where current of c1;与for update 配合使用
exit when v1>5;当V1>5时退出循环
end loop;
commit;
end;
/ 修改游标指向的当前行的数据,每个人依次增加工资
报错处理:(exception)
预定义错误:
oracle把错误号与错误名称对应在一起,可以用错误名捕获错误,并做处理
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
end;
/出现1422号错误
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
exception
when too_many_row then
dbms_output.put_line(‘more peploe’);进行报错处理.
end;
/
declare
v1 emp.sal%type;
begin
select sal into v1 from emp where sal>10000;
dbms_output.put_line('ok');
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person ');
when NO_DATA_FOUND then
dbms_output.put_line('no person ');两个报错处理
end;
/
非预定义错误
定义-----与错误号相关联
declare
ttt exception;
pragma exception_init(ttt,-2292);定义错误
v1 emp.sal%type;
begin
delete depe;
select sal into v1 from emp where sal>10000;
dbms_output.put_line('ok');
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person ');
when NO_DATA_FOUND then
dbms_output.put_line('no person ');
when ttt then
dbms_output.put_line('forign key ');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
/
自定义错误
过程
desc dbms_output
CREATE OR REPLACE PROCEDURE raise_salary (v_id in emp.empno%TYPE)
IS
BEGIN
UPDATE emp SET sal = sal * 1.10
WHERE empno = v_id;
END raise_salary;
/创建一个过程，为指定代码的人员增加工资
select text from user_source
where NAME ='RAISE_SALARY';
select OBJECT_NAME,OBJECT_TYPE,STATUS
from user_objects
where OBJECT_TYPE='PROCEDURE';
EXECUTE raise_salary (7369);
select empno,ename,sal from emp;
CREATE OR REPLACE PROCEDURE raise_salary (v_id in emp.empno%TYPE) IS
BEGIN
UPDATE emp SET sal = sal * 1.10 WHERE empno = v_id;
commit;
END raise_salary;
/
begin
raise_salary (7900);
end;
/
begin
raise_salary (7900);
raise_salary (7902);
raise_salary (7839);
end;
/
CREATE OR REPLACE PROCEDURE query_emp (v_id IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_salary OUT emp.sal%TYPE, v_comm OUT emp.comm%TYPE)
IS
BEGIN
SELECT ename, sal, comm INTO v_name, v_salary, v_comm FROM emp WHERE empno = v_id;
END query_emp;
/
VARIABLE g_name varchar2(15)
VARIABLE g_salary number
VARIABLE g_comm number
EXECUTE query_emp (7654, :g_name, :g_salary, :g_comm);
PRINT g_name print g_salary print g_comm
Declare
v1 emp.ename%TYPE;
v2 emp.sal%TYPE;
v3 emp.comm%TYPE;
begin
query_emp(7654,v1,v2,v3);
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
/
CREATE OR REPLACE PROCEDURE format_phone (v_phone_no IN OUT VARCHAR2)
IS
BEGIN
v_phone_no := '(' || SUBSTR(v_phone_no,1,3) || ')' || SUBSTR(v_phone_no,4,3) || '-' || SUBSTR(v_phone_no,7);
END format_phone;
/
declare
v1 varchar2(20);
begin
v1:='010456789';
format_phone(v1);
dbms_output.put_line(v1);
end;
/
VARIABLE g_phone_no varchar2(15)
BEGIN
:g_phone_no := '8006330575';
END;
/
EXECUTE format_phone (:g_phone_no) PRINT g_phone_no
CREATE OR REPLACE FUNCTION get_sal (v_id IN emp.empno%TYPE)
RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp
WHERE empno = v_id;
RETURN (v_salary);
END get_sal;
/
select get_sal(7900) from dual;
select 'drop '||object_type||' '||object_name||';'
from user_objects
where object_type in('FUNCTION','PROCEDURE');
set heading off
spool d:/2.txt
select 'drop '||object_type||' '||object_name||';'
from user_objects
where object_type in('FUNCTION','PROCEDURE');
spool off
set heading on
desc UTL_FILE
conn system/manager
select object_name from dba_objects
where object_type='PACKAGE';
DESC DBMS_OUTPUT
CONNECT SCOTT/TIGER
create or replace package PK87 is Function F1 (NO NUMBER) return NUMBER;
Function F1 (NO EMP.ENAME%TYPE) return NUMBER;
PROCEDURE P1(V_NO NUMBER);
end PK87;
/
SHOW ERRORS
create or replace package body PK87 is
-- Function and procedure implementations
FUNCTION F1 (NO IN NUMBER) RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp
WHERE empNO = NO;
RETURN v_salary;
END F1;
FUNCTION F1 (NO EMP.ENAME%TYPE) RETURN NUMBER
IS
v_salary emp.sal%TYPE :=0;
BEGIN SELECT sal INTO v_salary FROM emp
WHERE eNAME = NO;
RETURN v_salary;
END F1;
procedure P1(V_NO in NUMBER) is
begin
UPDATE EMP SET SAL=SAL+1 WHERE EMPNO=V_NO;
COMMIT;
end P1;
end PK87;
/
SELECT TEXT FROM USER_SOURCE WHERE NAME='PK87';
set serveroutput on
DECLARE
V1 EMP.SAL%TYPE;
V2 EMP.ENAME%TYPE;
BEGIN
V1:=PK87.F1(7900);
V2:=PK87.F1('KING');
pk87.P1(7902);
DBMS_OUTPUT.PUT_LINE(V1);
DBMS_OUTPUT.PUT_LINE(V2);
END;
/
create or replace package body PK87 is
-- Function and procedure implementations
FUNCTION F1 (NO IN NUMBER) RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp
WHERE empNO = NO;
RETURN v_salary;
END F1;
FUNCTION F1 (NO EMP.ENAME%TYPE) RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp WHERE eNAME = NO;
RETURN v_salary;
END F1;
procedure P1(V_NO in NUMBER) is
begin
UPDATE EMP SET SAL=SAL+1
WHERE EMPNO=V_NO;
COMMIT;
end P1;
end PK87;
/
set nls_lang=american_amrica.us7ascii
wrap iname=1.txt
@d:/1.plb
SELECT TEXT FROM USER_SOURCE WHERE NAME='PK87';
connect scott/tiger
drop table d;
drop table e;
create table d as select * from dept;
create table e as select * from emp;
drop trigger d_update;
CREATE or replace TRIGGER d_update
AFTER delete or UPDATE OF deptno ON d
FOR EACH ROW BEGIN
--当D表的部门号修改的时候E表的对应部门号也相应的修改
IF (UPDATING AND :old.deptno != :new.deptno)
THEN
UPDATE e SET deptno = :new.deptno
WHERE deptno = :old.deptno;
END IF;
--当D表的某个部门号删除的时候，E表的对应部门同时被删除
if deleting then delete e
where deptno=:old.deptno;
end if;
END;
/
select * from d;
select * from e;
update d set deptno=80
where deptno=10;
DROP TABLE T1;
CREATE TABLE T1 AS SELECT SAL OLD_value,SAL NEW_value
FROM EMP WHERE 0=9
CREATE OR REPLACE TRIGGER TRG1
BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW
BEGIN
INSERT INTO T1 valueS(:OLD.SAL,:NEW.SAL);
END;
/
update emp set sal=1000 where empno=7900;
--验证触发器的状态
select trigger_name,status from user_triggers;
--改变触发器的状态
--禁用某个触发器
ALTER TRIGGER trigger_instead_of disable;
--禁用某个表上的所有触发器
alter table e disable all triggers;
--删除触发器
DROP TRIGGER trigger_instead_of ;
drop table e1;
create table e1 as select * from emp;
drop view v1;
create view v1 as select distinct deptno from e1;
select * from v1;
select * from e1;
delete v1 where deptno=10;
--建立一个替代触发器，当修改V1的时候会自动的修改基表
create or replace trigger trigger_instead_of
instead of insert or update or delete on v1 for
each row
begin
if updating then update e1 set deptno=:new.deptno
where deptno=:old.deptno;
end if;
end;
/
--验证触发器起作用
update v1 set deptno=50 where deptno=10;
select * from e1;
commit;
浪淘沙
[这是真的吗?] 350 + 100 + 150 = 350 ?
2005/07/28 06:03pm IP: 已设置保密 [本文共10551字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 9 楼]
2.数据库的体系
体系结构(oracle_study/oracle_ppt/9idba1)
user---------sercer---------- instance--------数据库(date. -
PGA
Oracle server
Instance实例
是访问数据库的方法;一个实例仅能打开一个数据库,但一个库可以为多个实例访问
select tableSPACE_name from dba_tables where table_name='EMP'; 显示指定文件的位置
SELECT NAME FROM V$DATAFILE; 显示数据库已有的文件
集群RAC
CONN SYSTEM/MANAGER
SELECT * FROM V$SESSION WHERE USERNAME='SYSTEM';查看会话信息
数据库分为:DATE.CONTROL.REDO
SELECT * FROM EMP;
1.语法分析;
2.语意分析; 这些过程存入内存,当再次执行时只需要运行第4步
3.执行计算
4.运行计划
select * from v$sga;显示sga的内容
NAME value
------------------------------------------ ----------
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
show parameter sga_max_size显示参数,sga的最大区域,总的最大不能变化,内部可以互相动态调整
进程ptocess :(user.server.backgroud)
后台进程包括:
desc v$dgprocess;查看后台进程
show user;
select NAME,DESCRIPTION from v$bgprocess where paddr<>'00'; 查看后台进程的名称和描述
数据库写进程DBWN:存盘.空块.超时.RAC.表空间发生变化.
日志写进程LGWR:把日志写入硬盘,commit时.1/3时,达到1M时
show parameter log_buffer显示容量
系统监测进程SMON
进程监测进程PMON:
检测点进程CKPT:ORACLE的存盘策略
数据库管理工具
1.oul
2.ODCA
3.PFU
4.SQL*PLUS
5.OEM
SYS:数据库的所有者
System:
密码管理:
conn / as sysdba
show user 显示用户
conn sys/sdhfdskfh as sysdba 以任何密码进入sys,在s系统认证的情况下
e:/oracle/ora92/network/admin /sqlnet.ora
# SQLNET.ORA Network Configuration 文件: E:/oracle/ora92/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NTS)不允许系统验证,只能通过密码验证进入系统
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
[专用]SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn system/manager
已连接。
SQL> alter user sys identified by oracle;
用户已更改。
SQL> conn sys/oracle as sysdba
已连接。
通过system修改sys的密码后,可以连接sys
select instance_name from v$instance;查找数据库密码文件各
E:/oracle/ora92/database/pwdsystem.ora 找到现有的数据库密码文件
SQL>Shutdown immediate
Delete pwdsystem.ora 使用’资源管理器’删除
Dos>orapwd file=E:/oracle/ora92/database/pwdsystem.ora password=qq
Sqlplus /nolog
SQL>startup 启动系统例程
SQL> conn sys/qq as sysdba
已连接到空闲例程。 重建密码文件,并写入新的密码
select count(*) from session_privs;查看当前用户的权限
select * from v$pwfile_users;查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
select * from v$version;查看数据库版本
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
grant sysoper to scott;把操作员身分授权给scott
授权成功
select * from v$pwfile_users; 查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
SCOTT FALSE TRUE
SQL> conn scott/tiger
已连接。
SQL> show user
USER 为"SCOTT"
SQL> conn scott/tiger as sysoper 以系统操作员的身份登录
已连接。
SQL> show user
USER 为"PUBLIC"
SQL> conn sys/qq as sysdba
已连接。
SQL> grant sysdba to scott;把系统管理员的身份授权给scott
授权成功。
SQL> conn scott/tiger as sysdba 以系统管理员身份连接
已连接。
SQL> show user;
USER 为"SYS"
管理一个实例
E:/oracle/ora92/database/spfilesystem.ora 二进制的初始化参数文件
Regedit : 本地---》软件----》oracle->home0 :oracle_home 参数 查看oracle的存在位置
Sql>create pfile from spfile;生成纯文本的初始化参数文件
文件已创建
已存在E:/oracle/ora92/database/initsystem.ora纯文本的初始化参数文件
SQL> select distinct ISSPECIFIED from v$spparameter; 查询二进制的初始化参数
ISSPEC
------
FALSE
TRUE 指明数据库使用二进制的初始化参数
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
修改文件明E:/oracle/ora92/database/spfilesystem.ora为E:/oracle/ora92/database/spfile.or
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC
------
FALSE 使用纯文本的初始化参数文件
数据库启动参数文件的顺序:spfilesystem.ora----spfile.ora-----initsystem.ora
动态修改数据库,下次启动时使用上次修改过的参数.
体系结构(oracle_study/oracle_ppt/9idba1)
user---------sercer---------- instance--------数据库(date. -
PGA
Oracle server
Instance实例
是访问数据库的方法;一个实例仅能打开一个数据库,但一个库可以为多个实例访问
select tableSPACE_name from dba_tables where table_name='EMP'; 显示指定文件的位置
SELECT NAME FROM V$DATAFILE; 显示数据库已有的文件
集群RAC
CONN SYSTEM/MANAGER
SELECT * FROM V$SESSION WHERE USERNAME='SYSTEM';查看会话信息
数据库分为:DATE.CONTROL.REDO
SELECT * FROM EMP;
5.语法分析;
6.语意分析; 这些过程存入内存,当再次执行时只需要运行第4步
7.执行计算
8.运行计划
select * from v$sga;显示sga的内容
NAME value
------------------------------------------ ----------
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
show parameter sga_max_size显示参数,sga的最大区域,总的最大不能变化,内部可以互相动态调整
进程ptocess :(user.server.backgroud)
后台进程包括:
desc v$dgprocess;查看后台进程
show user;
select NAME,DESCRIPTION from v$bgprocess where paddr<>'00'; 查看后台进程的名称和描述
数据库写进程DBWN:存盘.空块.超时.RAC.表空间发生变化.
日志写进程LGWR:把日志写入硬盘,commit时.1/3时,达到1M时
show parameter log_buffer显示容量
系统监测进程SMON
进程监测进程PMON:
检测点进程CKPT:ORACLE的存盘策略
数据库管理工具
6.oul
7.ODCA
8.PFU
9.SQL*PLUS
10.OEM
SYS:数据库的所有者
System:
密码管理:
conn / as sysdba
show user 显示用户
conn sys/sdhfdskfh as sysdba 以任何密码进入sys,在s系统认证的情况下
e:/oracle/ora92/network/admin /sqlnet.ora
# SQLNET.ORA Network Configuration 文件: E:/oracle/ora92/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NTS)不允许系统验证,只能通过密码验证进入系统
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
[专用]SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn system/manager
已连接。
SQL> alter user sys identified by oracle;
用户已更改。
SQL> conn sys/oracle as sysdba
已连接。
通过system修改sys的密码后,可以连接sys
select instance_name from v$instance;查找数据库密码文件各
E:/oracle/ora92/database/pwdsystem.ora 找到现有的数据库密码文件
SQL>Shutdown immediate
Delete pwdsystem.ora 使用’资源管理器’删除
Dos>orapwd file=E:/oracle/ora92/database/pwdsystem.ora password=qq
Sqlplus /nolog
SQL>startup 启动系统例程
SQL> conn sys/qq as sysdba
已连接到空闲例程。 重建密码文件,并写入新的密码
select count(*) from session_privs;查看当前用户的权限
select * from v$pwfile_users;查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
select * from v$version;查看数据库版本
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
grant sysoper to scott;把操作员身分授权给scott
授权成功
select * from v$pwfile_users; 查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
SCOTT FALSE TRUE
SQL> conn scott/tiger
已连接。
SQL> show user
USER 为"SCOTT"
SQL> conn scott/tiger as sysoper 以系统操作员的身份登录
已连接。
SQL> show user
USER 为"PUBLIC"
SQL> conn sys/qq as sysdba
已连接。
SQL> grant sysdba to scott;把系统管理员的身份授权给scott
授权成功。
SQL> conn scott/tiger as sysdba 以系统管理员身份连接
已连接。
SQL> show user;
USER 为"SYS"
浪淘沙
[免收开户费] 114竞价广告，按效果付费，不点击不付费!
2005/07/28 06:03pm IP: 已设置保密 [本文共11518字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 10 楼]
管理一个实例
E:/oracle/ora92/database/spfilesystem.ora 二进制的初始化参数文件
Regedit : 本地---》软件----》oracle->home0 :oracle_home 参数 查看oracle的存在位置
Sql>create pfile from spfile;生成纯文本的初始化参数文件
文件已创建
已存在E:/oracle/ora92/database/initsystem.ora纯文本的初始化参数文件
SQL> select distinct ISSPECIFIED from v$spparameter; 查询二进制的初始化参数
ISSPEC
------
FALSE
TRUE 指明数据库使用二进制的初始化参数
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
修改文件明E:/oracle/ora92/database/spfilesystem.ora为E:/oracle/ora92/database/spfile.or
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC
------
FALSE 使用纯文本的初始化参数文件
数据库启动参数文件的顺序:spfilesystem.ora----spfile.ora-----initsystem.ora
动态修改数据库,下次启动时使用上次修改过的参数.
3.远程数据库如何连接
4.backup/recover
5.优化
[这个贴子最后由轮胎菲拉洛在 2005/07/28 06:20pm 第 2 次编辑]
1.sql(学习目录中的9isql)
(1)select
(2)sqlplus管理oracle的工具
运行sqlplusw /nolog或sqlplus /nolog 启动到界面
nolog不与任何用户连接
*运行 connect system/manager 连接到sql用户
show user 显示用户
*运行 alter user system(用户) identified by manager;(密码)以’;’结束
验证:connect system/manager ‘语句通用’
解锁: conn / as sysdba
alter user system account unlock identified by manager;
conn system/manager
*运行 建立模型库
忘记密码:conn /as sysdba (联接到老大)－－老大指数据库权限最大的管理者
alter user system account unlock identified by manager;
conn system/manager
*conn teach/oracle
*select * from tab; /准备好实验环境/看当前用户有哪些表.
*select table_name from user_tables;
select 语句的作用:查表的列.行及多张表联合查询
*select * from DEPARTMENTS;看一张表所有的列
*select DEPARTMENT_ID,DEPARTMENT_NAME from DEPARTMENTS;
语句写法：
A.大小定不敏感
B.关键字不能缩写也不能跨行写
C.子名通常放在不同的行
D.能被放在一行或多行
E.续行用’-‘
*SELECT LAST_NAME,COMMISSION_PCT FROM employees;
给列起别名:A改的是头名;B利于计算;C增加可读性;D显示小写加’ “ ‘
*SELECT LAST_NAME name,COMMISSION_PCT comm FROM employees;
*select LAST_NAME||SALARY||HIRE_DATE from employees; ‘||’并列显示
*字符串用’’
*l(list) 列出上一条语句，sql下的输入方式
* select distinct DEPARTMENT_ID from employees; 消除重复的行
与SQL联系的就与数据库有关
SQL是结构性语言,结构化查询,是美国的一个标准,不支持缩写
SQLPLUS是一个小程序,是ORACLERR 语言,不操作数据库,可以在IE下运行,支持缩写;
SQL SERVER 是微软的数据库
PL/SQL 是ORACLE的一个模块下编程的数据库语言
*desc ‘描述表结构’
展开备注页:视图---备注页
*desc employees
称 是否为空? 类型
-------------------------------------- -------- -------------
LOYEE_ID NOT NULL NUMBER(6)
ST_NAME VARCHAR2(20)
T_NAME NOT NULL VARCHAR2(25)
IL NOT NULL VARCHAR2(25)
NE_NUMBER VARCHAR2(20)
E_DATE NOT NULL DATE
_ID NOT NULL VARCHAR2(10)
ARY NUMBER(8,2)
MISSION_PCT NUMBER(2,2)
AGER_ID NUMBER(6)
ARTMENT_ID NUMBER(4)
*SELECT select last_name,salary from EMPLOYEES where DEPARTMENT_ID=90;
字符串大小写敏感,日期格式敏感
*select last_name,salary from EMPLOYEES where salary<3000;
*select last_name,salary from EMPLOYEES where salary between 2500 and 3000;含边界
* select last_name,salary from EMPLOYEES where salary in(17000,4000);指定具体的值
*select last_name from EMPLOYEES where last_name like ’s%’ or last_name like ‘%A%’;选择第一个字母为’s’或含有’A’的人
逻辑运算符的前后是两个表达式
‘*’为通配所有字符,’_’为通配一个字符
* SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA/_%' ESCAPE '/';找job_id中含SA_的行
*select first_name from employees where first_name like '%/_%' escape '/';
* SELECT LAST_NAME ,COMMISSION_PCT FROM employees
WHERE COMMISSION_PCT IS NULL;
* SELECT LAST_NAME ,COMMISSION_PCT FROM employees
WHERE COMMISSION_PCT IS NOT NULL;
* SELECT LAST_NAME ,manager_id FROM employees
WHERE manager_id IS NOT NULL;
算术运算高于逻辑运算,NOT高于and高于or
* SELECT LAST_NAME ,COMMISSION_PCT FROM employees
WHERE COMMISSION_PCT IS NOT NULL order by salary/desc (反序);
* SELECT LAST_NAME ,DEPARTMENT_ID,SALARY
FROM employees
ORDER BY DEPARTMENT_ID,SALARY DESC; 多列排序按列的出现先后
不同的子句尽量放在不同的行
select last_name ,salary Monthly_Salary from employees WHERE department_id in(20,50,30) and
salary between 5000 and 12000
order by last_name;
浪淘沙
[免费送钱] 岁末年初有奖征文，想要什么自己定！
2005/07/28 05:59pm IP: 已设置保密 [本文共5722字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 2 楼]
函数
单行函数 :数据库中每一行都有一个值及作用有每一行
字符串函数:大小写,串长或子串
大小写
*SELECT LOWER小写('SQL course'),upper大写('SQL course'),initcap第一个字母大写('SQL course') from dual(一张虚拟表以获得函数的值);
*SELECT LOWER('SQL course'),upper('SQL course'),initcap('SQL course') from employees;每有一行运算一次
('SQL UPPER('SQL INITCAP('S
----- ---------- ----------
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
ourse SQL COURSE Sql Course
择20行。运行结果
字符串运算:
CONCAT字符串联接;SUBSTR子串运算;LENGTH求串长;INSTR子串在父串中出现的位置;LPAD向左填充;RPAD向右填充;TRIM去掉指定的字母;replace
Select lpad(last_name,20,’*’) from employees;
*select concat(last_name,first_name) from employees
where length(last_name)>5;
数值函数:
日期函数:数据库内部存储格式一致
select sysdate from dual;从系统中取当前日期及时间
日期相减得数为天
select LAST_NAME,round取整(sysdate-HIRE_DATE ) days from EMPLOYEES ;
日期相加为一个日期(具体的那一天)
字符串的转换:稳示的转换char—numb;char—date
显示的转换
*select LAST_NAME,to_char(hire_date,'yyyy/mm/dd') from employees;
*select LAST_NAME,to_char(hire_date,'yyyy/mm/dd” ”year” ”day” ”dy” ”month') from employees;
select LAST_NAME,to_char(hire_date,'fmyyyy/mm/dd') from employees;
select LAST_NAME,to_char(hire_date,'ddspth') from employees;
select LAST_NAME,to_char(hire_date,'yyyy/mm/ddyear " of "day" in "month') from employees;
*select LAST_NAME,to_char(salary,'l99999999.9999') from employees;L本地货币,9的个数代表显示的位数
“yy”与操作者所在世纪保持一致
“RR”指前半年世纪与后半个世纪
select to_char(sysdate,'yyyy') current_year,
to_char(to_date('08','yy'),'yyyy') "yy08",
to_char(to_date('95','yy'),'yyyy') "yy95",
to_char(to_date('08','rr'),'yyyy') "rr08",
to_char(to_date('95','rr'),'yyyy') "rr95"
from dual;
当前日期为2005年
SQL> select to_char(sysdate,'yyyy') current_year,
2 to_char(to_date('08','yy'),'yyyy') "yy08",
3 to_char(to_date('95','yy'),'yyyy') "yy95",
4 to_char(to_date('08','rr'),'yyyy') "rr08",
5 to_char(to_date('95','rr'),'yyyy') "rr95"
6 from dual;
CURR yy08 yy95 rr08 rr95
---- ---- ---- ---- ----
2005 2008 2095 2008 1995
当前日期为1999年
nvl(a,b)函数:如果a是not null 就是a,如果a是null 就是b
nvl2(a,b,c)就是a是not null 就是b,如果a是null 就是c
nullif(a,b)就是a=b 就是a,如果a<>b就是null
coalesce(a,b,c,d,e,…….)直到找到第一个非空的值
DECODE子句
select last_name,job_id,salary,
decode(job_id,'AD_PRES','A',
'ST_MAN','B',
'IT_PRGO','C',
'SA_REP','D',
'ST_CLERK','E',
'0') JOB
from employees
/
系统自动记录运行情况spool 文件名…….spool off
多张表联合查询:
非国标连接:迪卡尔连接.等值连接.不等连接.外键连接.自连接
迪卡尔连接:
*select LAST_NAME,DEPARTMENT_NAME from EMPLOYEES, DEPARTMENTS;
等值连接
*select LAST_NAME,DEPARTMENT_NAME
from EMPLOYEES, DEPARTMENTS
where EMPLOYEES.DEPARTMENT_id=DEPARTMENTS.DEPARTMENT_id; (看每个人在哪能个部门上班)
浪淘沙
[免费送钱] 岁末年初有奖征文，想要什么自己定！
2005/07/28 06:00pm IP: 已设置保密 [本文共4867字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 3 楼]
设置显示环境
控制每一列的宽度:column 列名 for 宽度
col DEPARTMENT_NAME for a15(设定每列宽度)
col last_name for a15
set linesize 100(设定每页显示行数)
查询部门号和地址号相同的员工名.部门号.部门名.地址号和城市
*select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,l.city
from EMPLOYEES e, DEPARTMENTS d,LOCATIONS l(表的别名)
where E.DEPARTMENT_id=D.DEPARTMENT_id
and d.LOCATION_ID=l.LOCATION_ID
order by 2(可用列号排序);
不等连接
*select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,l.city
from EMPLOYEES e, DEPARTMENTS d,LOCATIONS l
where E.DEPARTMENT_id=D.DEPARTMENT_id 各条件中用and接连
and d.LOCATION_ID=l.LOCATION_ID
and lower(e.last_name)=’king’;(显示每个员工的工作总门及部门所在地)
* select 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;(显示每个员工的工资级别)
外键连接
*select e.LAST_NAME,e.DEPARTMENT_ID "edept" ,d.DEPARTMENT_ID "ddept",d.DEPARTMENT_NAME
from EMPLOYEES e, DEPARTMENTS d
where E.DEPARTMENT_id(+)=D.DEPARTMENT_id; (+)外键连接,加在少的一方
(+)可加在=的两边,但两边不能同时加
自连接:
*select w.LAST_NAME,m.last_name
from employees w,employees m
where m.EMPLOYEE_ID(+)=w. MANAGER_ID; 查询每个员工的经理
查询一个数据库中含哪些表:select * from tab;
国标连接:实质连接.自然连接.不等连接.自连接
实质连接
*select d.DEPARTMENT_NAME,l.CITY
from DEPARTMENTS d,LOCATIONS l
where d.LOCATION_ID =l.LOCATION_ID;
*select LAST_NAME,DEPARTMENT_NAME
from EMPLOYEES cross join DEPARTMENTS;
自然连接
列同名的都做为等值条件,各条件间为and关系
查询部门号和经理号都相同的员工名及地点
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS natural join EMPLOYEES(两个条件同时成立)
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS d,EMPLOYEES e
where d. DEPARTMENT_ID=e.DEPARTMENT_ID
and d.MANAGER_ID=e.MANAGER_ID; (两个条件同时成立)
查询部门号相同的员工名及地点
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS d,EMPLOYEES e
where d. DEPARTMENT_ID=e.DEPARTMENT_ID;(一个条件成立)
*select LAST_NAME,LOCATION_ID
from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID); (一个条件成立)
不等连接
查询员工的工资级别
*select 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;(条件在一个范围下)
*select LAST_NAME,salary,grade_LEVEL
from EMPLOYEES e join job_grades g
on(e.salary between g.LOWEST_SAL and g.highest_sal); (条件在一个范围下)
自连接
*SELECTe.department_id department, e.last_name employee,
c.last_name colleague
FROM employees e JOIN employees c
ON (e.department_id = c.department_id)
WHERE e.employee_id <> c.employee_id
ORDER BY e.department_id, e.last_name, c.last_name;查询一张表中部门号相同的员工
浪淘沙
[免收开户费] 114竞价广告，按效果付费，不点击不付费!
2005/07/28 06:00pm IP: 已设置保密 [本文共3118字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 4 楼]
第五章 主函数
所有的主函数除count 外都不计null值
分组
每个部门的平均工资
*select department_id,avg(salary) from employees
group by department_id;
多列分组
查每个部门不同工种的平均工资
*select department_id, job_id,avg(salary)
from employees
group by
DEPARTMENT_ID,job_id;
二次淘汰 having,作用在分组之后
子查询(内部查询)
先于外部查询运行
用在()中;关系运算的右边;不能用order dy 子句;单对单,多对多
关系运算:in .any.all
*select employee_id,last_name,salary
from employees
where salary >(select avg(salary) from employees)
order by salary
/
替代变量&.&&
SQL> select LAST_NAME from employees where employee_id=&1;&替代变量,执行一次
输入 1 的值: 206
LAST_NAME
---------------
Gietz
已选择 1 行。
&&全程替变量
show all /看sqlplus当前所有的配置/
set feedback 10 运行结果反回的行数
set heading off 运行结果不反回头部
set heading on
col last_name heading 'employee|name' 设置列名头
select last_name from employees;
clear screen 清屏
创建一个用户:
(3)数据操作语言(dml):insert into/update/delete/merge
使用 scott/tiger用户,含有6张表
insert into向表中插入一行,暂时存入内存
显示插入,隐示插入
*insert into d select * from d; 自身插入自身
commit;向表内写入添加操作,并且结束当前进程
update 修改已有的数据
update …. Set ….
*update e set sal=sal+1; 修改列值
*select ename,sal from e; 检证修改
*update e set sal=sal+1 where deptno=10; 修指定条件的值
*select ename,sal from e; 检证修改
帐号被锁:
conn / as sysdba
drop user scott cascade;
@%oracle_home%/rdbms/admin/scott
*update e set ename='smith2' where empno=7369;修改指定条件的指定值
delete 删除指定行
delete from …where …
*delete e where deptno=10;
*delete e where sal>2000;
融合(merge)把后一张表与前一张不同的内容写入前一张表
生成两表
CONN SCOTT/TIGER
DROP TABLE E1;
DROP TABLE E2;
CREATE TABLE E1 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO=10;
CREATE TABLE E2 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO IN (10,20);
UPDATE E2 SET SAL=SAL+100;
COMMIT;
把e2写入e1
merge into E1
USING E2
ON (E1.EMPNO=E2.EMPNO)
WHEN MATCHED THEN
UPDATE SET
E1.SAL=E1.SAL
WHEN NOT MATCHED THEN
INSERT valueS(E2.EMPNO,E2.ENAME,E2.SAL);
transaction(事物,交易)是一个整个要么都成立,要么不成立.Cmmit/rollback
commit:显式提交(commit)/隐式提交(ddl.dcl.exit)
rollback:显式回退(rollback)/隐式回退(中断程序,数据库崩溃)
事物进行过程中:1.加锁;2.占用回退段;3,独一性,其他用户看不用当前变动的结果
建立一个表进行写入,设立存储点,然后回退到指定存储点.
conn scott/tiger
drop table e;
create table e as select * from emp;
update e set sal=10 where deptno=10;
savepoint u10;
update e set sal=10 where deptno=20;
savepoint u20;
update e set sal=10 where deptno=30;
savepoint u30;
delete e where sal>15;
select * from e;
rollback to savepoint u30;
select * from e;
rollback to savepoint u20;
select * from e;
rollback to savepoint u10;
select * from e;
rollbackup ;
select * from e;
浪淘沙
[免费送钱] 岁末年初有奖征文，想要什么自己定！
2005/07/28 06:01pm IP: 已设置保密 [本文共3363字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 5 楼]
创建与维护表
*select object_name,object_type from user_objects;当前用户所有的对象及对象类型
对象名唯一
drop table t1;删除表t1
create table t1(name char(10),day date);创建t1表
select table_name from user_tables;查看用户中的表
desc t1; 查看表t1的结构
表分为user 与dictionary两类
查看系统字典
conn system/manager
select table_name,owner from dba_tables where table_name like '%$';
create table t1 as select ename,12*sal annsal from emp;创建一个和t1表
alter table t1 add(comm number(3));向t1表中增加一列
alter table ti modify (comm. Number(6));修改表t1中的comm列的数个类型
ALTER TABLE T1 DROP (COMM);删除指定的列
RENAME T1 TO TTT; 把t1改为ttt
COMMENT ON TABLE TTT IS 'THIS TABLE IS RENAME FROM T1;为表t1加注示
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME='TTT';查看注示
COMMENT ON COLUMN EMP.ENAME IS 'THIS IS EMPLOYEE NAME'; 为指定的列加注示
SELECT * FROM USER_COL_COMMENTS WHERE COLUMN_NAME='ENAME' AND TABLE_NAME='EMP';查看列的注示
约束:(第十章)
*create table d as select * from dept;
*alter table d modify (dname not null);产生一个非空约束
*select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints;查看用户约束的约束名和约束类型
*select * from user_cons_columns;查看当前用户约束列
*select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;查看表与列的约束名称.类型
非空约束:
*insert into d values(null,'a','k'); 插入非空约束
*insert into d values(50,null,'s'); 插入一个空约束,系统禁止插入
*select * from d;查看表
唯一约束:
*alter table d add constraint d_u_deptno unique (deptno);对表d插入一个唯一约束在deptno列上
*select CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints;验证约束
* select index_name,table_name from user_indexes;查看索引
*insert into d values(null,'a','k');
*insert into d values(null,'a','k');成功
*select * from d;
查询结果表中含有多个相同的空值
唯一性通过索引实现,null不入索引,所以可以插入多个null.
联合约束:
conn scott/tiger
drop table d;
create table d as select * from dept;
alter table d add constraint d_u_deptno_lianhe unique (deptno,dname);创建一个联合约束
select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;
select index_name,table_name from user_indexes; 查看索引
insert into d values(null,null,'a');可以插入
insert into d values(null,null,'a');可以插入
insert into d values(null,'d','a');可以插入
insert into d values(null,'d','a');不可以插入
主键(primary key):非空+唯一,可以是联合的
conn scott/tiger
drop table d;
create table d as select * from dept;
alter table d add constraint d_pk primary key (deptno);在deptno列上设立主键
select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;查询约束设置
select index_name,table_name from user_indexes; 查询索引
insert into d values(null,null,'a');不能插入
insert into d values(10,null,'a'); 不能插入
外键(foreign key):在引用的表已存在主键的情况下,本表将主键设立为外键
alter table e add constraint e_fk foreign key (deptno) references d(deptno);在表e的deptno列上设立外键
select u.CONSTRAINT_NAME,u.CONSTRAINT_TYPE ,c.table_name,c.column_name
from user_constraints u,user_cons_columns c
where u.CONSTRAINT_NAME=c.CONSTRAINT_NAME;查询约束设置
delete d where deptno=10; 不允许删除,已找到子记录日记
update e set deptno=12 where deptno=10;不允许插入,未找到父关键字
alter table e drop constraint e_fk;删除一个约束
alter table e add constraint e_fk
foreign key (deptno) references d(deptno)
on delete cascade; 设立一个约束,当d表的deptno列删除时,e表的相应列也删除
delete d where deptno=10;验证
rollback;回退
alter table e drop constraint e_fk; 删除一个约
alter table e add constraint e_fk
foreign key (deptno) references d(deptno)
on delete set null;设立一个约束,当d表r deptno列删除时,e表的相应列设为null值.
delete d where deptno=30;
检测约束(check):
alter table e drop constraint e_fk;删除一个约束
alter table e add constraint e_check
check (deptno<99); 检测约束,表中的每一行必须满足检测条件
浪淘沙
[免收开户费] 114竞价广告，按效果付费，不点击不付费!
2005/07/28 06:01pm IP: 已设置保密 [本文共4837字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 6 楼]
视图view(第十一章)
从一张或多张表获得数据的子集
conn scott/tiger
drop table e;
create table e as select * from emp;
create view e30
as select ename name,sal salary from e where deptno=30;创建一个视图,在表e的deptno=30时
select view_name,text from user_views;查看视图
desc e30;查看视图结构
select * from e30; 查看视图
视图作用:
限制查看,生成简单查询,每个查询是独立的,不同的窗口不同的数据
视图中不存储数据,视图中的修改,是对基表的修改
To r通信
drop table d;
create view dv as select dname,loc from d; 不允许在没有基表的情况下创建视图
drop table d;
create force view dv as select dname,loc from d;强制设立一个视图,创建的视图带有编译错误select view_name,text from user_views;查看视图
conn system/manager
set long 10000
select text from dba_views where view_name='USER_USERS';
通常情况下不通过视图修改基表
CONN SCOTT/TIGER
SELECT ROWNUM,ENAME FROM EMP; 假的列(rownum)
create OR REPLACE view e30
as select ename name,sal salary,DEPTNO from e where deptno=30;
ROLLBACK;
create OR REPLACE view e30
as select ename name,sal salary,DEPTNO from e where deptno=30
WITH CHECK OPTION; 设定限制修改条件(deptno=30不能修改),及where 语句中的条件不变.
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM user_constraints; 查询约束
DROP VIEW E30;删除视图定义
select rownum rank ,ename,sal
from (select ename,sal from emp order by sal desc---是一个视图)
where rownum<=3; 内嵌式视图,查看工资前三名
CREATE OR REPLACE VIEW V1 AS select ename,sal from emp order by sal desc;
SELECT rownum rank ,ename,sal FROM V1 where rownum<=3; 创建一个视图
序列(第十二章)
SELECT * FROM USER_SEQUENCES;查看现有序列,没有显示行
CREATE SEQUENCE S1;创建序列
SELECT * FROM USER_SEQUENCES;查看现有序列,有一个序列
SELECT S1.NEXTVAL FROM DUAL; 查看下一个值
SELECT S1.CURRVAL FROM DUAL;查看当前值
自动生成序列值,公用的与表同级,自动生成主键,
CREATE SEQUENCE S2 定义序列名
INCREMENT BY 10 定义步长
START WITH –20 定义起始值
MAXvalue 30 定义最大值
MINvalue -50 定义最小值
NOCYCLE 不循环
NOCACHE; 不发生,不放入内存…..创建一个序列
SELECT S2.NEXTVAL FROM DUAL;查看序列s2
CREATE SEQUENCE S3
INCREMENT BY 1
START WITH 1
MAXvalue 10
MINvalue -10
CYCLE
CACHE 5; 一次计数5个,放入内存,容易产生缝隙
SELECT SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER FROM USER_SEQUENCES; 查看序列名.序列值
SELECT S3.NEXTVAL FROM DUAL; 查看序列的下一个值
索引
自动创建,手动创建
CREATE INDEX IE ON E(EMPNO);创建一个基于表e中empno列的索引
SELECT I.INDEX_NAME,I.TABLE_NAME,COLUMN_NAME
FROM USER_INDEXES I,USER_IND_COLUMNS C
WHERE I.INDEX_NAME=C.INDEX_NAME; 查询创建的索引
同义词
CONN SYSTEM/MANAGER
SELECT * FROM EMP;查不到
SELECT * FROM SCOTT.EMP;指定绝对路径后可查到
CREATE SYNONYM EMP FOR SCOTT.EMP;定义同义词
SELECT * FROM USER_SYNONYMS WHERE TABLE_NAME='EMP';在系统表中查找同义词’emp’
SELECT * FROM EMP;可以查询到表EMP
DROP SYNONYM EMP;删除同义词EMP
SELECT * FROM EMP; 不能查询到表EMP
(4)ddl
集合
conn scott/tiger;
drop table t1;
drop table t2;
create table t1 as select deptno,empno,sal,ename from emp where deptno=10;
create table t2 as select deptno,empno,sal,ename from emp where sal>2000;
select * from t1
union all
select * from t2;并排显示两张表,不删除重复的行
select * from t1
union
select * from t2; 并排显示两张表,删除重复的行,并且排序
select * from t1
intersect
select * from t2;显示两张表中相同的部分
select * from t1
minus
select * from t2;显示t1表中不含t2表所含的内容(补集)
浪淘沙
[这是真的吗?] 350 + 100 + 150 = 350 ?
2005/07/28 06:01pm IP: 已设置保密 [本文共4095字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 7 楼]
高级分组(第十七章)
select deptno,sum(sal) from emp group by deptno;
select deptno,sum(sal) from emp group by rollup(deptno);上卷式分组,
select deptno,sum(sal) from emp group by deptno
union all
select null,sum(sal) from emp; 与上卷分组结果一样
select deptno,job,sum(sal) from emp group by rollup(deptno,job); 根据部门.工作统计工资合
select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,null,sum(sal) from emp
order by 1; 与上句结果相同
select deptno,job,sum(sal) ,grouping伪列(deptno),grouping(job)
from emp group by rollup(deptno,job);统计参加分组
如果有N个集合,上卷是N+1个集合的并集
立方分组
select deptno,job,sum(sal) ,grouping(deptno),grouping(job)
from emp group by cube(deptno,job); 按工种.部门
高级子查询(第十八章)
select ename,sal, DEPTNO from emp outer
where sal >( select avg(sal) from emp
where DEPTNO=outer.DEPTNO);
存在(exists):测试查询有没有值
select ename,job,DEPTNO,sal from emp outer
where exists
(select 'x' from emp
where lower(outer.job) like 'manager');查找部门经理
select ename,job,DEPTNO,sal from emp outer
where exists
(select 'x' from emp
where outer.empno=emp.mgr) ;查找部门经理
conn scott/tiger;
drop table e;
create table e as select * from emp;
alter table e add(dname varchar2(10));
alter table e drop column hiredate;
select * from e;
update e set dname=(select dname from dept where dept.deptno=e.deptno); 向表e中加入dname列并且赋值
with子句:
爬树(第十九章)
select ename,empno,mgr from emp;
select level(在第几层找到) ,ename from emp
start with ename=’smith’
connect by
PRIOR MGR=EMPNO;查找’smith’的上级
select LEVEL(伪列,在第几层找到),ename from emp
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR; 向下查找各级成员
select LPAD('-',LEVEL)向左垫||ename from emp
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR;
剪枝(pruning
select LPAD('-',LEVEL)||ename from emp
WHERE ENAME<>'BLAKE'
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR; 去掉指定的人;
select LPAD('-',LEVEL)||ename from emp
start with ename='KING'
CONNECT BY
PRIOR EMPNO=MGR
AND ENAME<>'BLAKE'; 去掉从指定的人开始时的一枝
(5)高级SQL-------pl/sql
2.过程
匿名块
conn scott/tiger
set serveroutput on
begin
dbms_output.put_line('hello world');
end;
/ 在屏幕上显示’hello world’
定义变量
一行赋一个变量,以分号结束,constant 常量
conn scott/tiger
set serveroutput on
declare
v1 date;
v2 number(8);定义变量 v1.v2
v1:=sysdate;
v2:=12345;向v1.v2赋值
begin
dbms_output.put_line(V1);
dbms_output.put_line(V2);
end;,输出v1.v2
/
conn scott/tiger
set serveroutput on
declare
v1 emp_ename%type;
v2 emp_sal%type;
v3 v2%type;定义变量 v1.v2.v3,%type与指定表的列保持相同的属性
v1:=smith;
v2:=800;
v3:=1000;向v1.v2. .v3赋值
begin
dbms_output.put_line(V1);
dbms_output.put_line(V2);
dbms_output.put_line(V3);
end;,输出v1.v2.v3
/
declare
v1 char(20);
v2 varchar2(80);
v3 number(10):=1;
begin
v1:=to_char(sysdate ,'yyyy/mon/dd');
v2:='ww'||chr(10)||'we'||chr(10)||'dd';
v3:=v3+1;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
declare
v1 emp.ename%type;
v2 emp.sal%type;
v3 v2%type;
begin
select ename,sal into v1,v2 from emp
where empno=7900;
v3:=v2+300;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
/
declare
v1 number(2);
begin
delete e where deptno=30;
v1:=sql%rowcount;系统定义游标,当前语句处理表中的行数
dbms_output.put_line(to_char(v1)||' rows is delete');
--dbms_output.put_line(v2);
--dbms_output.put_line(v3);
end;
/
循环
if …
elsif ….
Else…
Endif
Loop
….
…
Exit (when …).
endloop
for I计数器,不须声明 in 1….10 loop 步长总是1,只能引用不能修改
insert into ….
End loop;
While …..loop
….;
….;
End loop;
declare
v1 char(20);
v2 varchar2(80);
v3 number(10):=1;
begin
v1:=to_char(sysdate ,'yyyy/mon/dd');
v2:='ww'||chr(10)||'we'||chr(10)||'dd';
v3:=v3+1;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
declare
type type1 is record定义新的记录类型
(f1 emp.ename%type,f2 emp.sal%type);
v1 type1;定义数据类型
begin
select ename,sal into v1 from emp where empno=7900;
dbms_output.put_line(v1.f1);
dbms_output.put_line(v1.f2);
end;
/
declare
v1 emp%rowtype;与emp表的结构保持一致
begin
select * into v1 from emp where empno=7900;
dbms_output.put_line(v1.ename);
dbms_output.put_line(v1.sal);
dbms_output.put_line(v1.deptno);
end;
/
表名%rowtype 定义一个复合的数据类型,与指定表一致
定义集合:
declare
type newtype is table of
emp.ename%type
index by binary_integer;定义一个新的集合newtype,与emp.ename保持一致的数据类型,有一个关于binary_integer(二进制整型)的主键盘,集合必须有主键
v1 newtype;
begin
select ename into v1(1) from emp where empno=7900;
select ename into v1(4) from emp where empno=7902;
select ename into v1(9) from emp where empno=7839;
dbms_output.put_line(v1(1));
dbms_output.put_line(v1(4));
dbms_output.put_line(v1(9));
end;
/
declare
type newtype is table of
emp%rowtype
index by binary_integer;集合的数据类型与emp表的一行一致
v1 newtype;
begin
select * into v1(1929) from emp where empno=7900;
select * into v1(4000) from emp where empno=7902;
select * into v1(9000) from emp where empno=7839;
dbms_output.put_line(v1(1929).ename||' job is '||v1(1929).job);
end;
/
浪淘沙
[这是真的吗?] 350 + 100 + 150 = 350 ?
2005/07/28 06:02pm IP: 已设置保密 [本文共6593字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 8 楼]
游标(cursors)
declare
cursor c1 is select ename,sal from emp order by sal desc;定义游标
ct number(3):=1;定义计数器
v1 emp.ename%type;定义变量v1
v2 emp.sal%type;
begin
open c1;打开游标,把指针指向第一个数据
loop
fetch c1 into v1,v2;提取游标中的数据
dbms_output.put_line(v1||' salary is '||to_char(v2));
ct:=ct+1;
exit when ct>3;
end loop;
end; declare
cursor c1 is select ename,sal from emp order by sal desc;
ct number(3):=1;
v1 emp.ename%type;
v2 emp.sal%type;
begin
open c1;
loop
fetch c1 into v1,v2;
dbms_output.put_line(v1||' salary is '||to_char(v2));
ct:=ct+1;
exit when ct>3;
end loop;
end;
/生成emp表中工资前三名
游标的属性:isopen notfount fount rowcount
declare
cursor c1 is select ename,sal from emp order by sal desc;
ct number(3):=1;
v1 c1%rowtype;与游标的类型一致
begin
open c1;
loop
fetch c1 into v1;
ct:=ct+1;
exit when ct>30 or c1%notfound;
dbms_output.put_line(v1.ename||' salary is '||to_char(v1.sal));
end loop;
end;
/按工资的高低显示emp表的人名与相应的工资.
declare
cursor c1 is select ename,sal from emp order by sal desc;
begin
for i in c1 loop
dbms_output.put_line(i.ename||' salary is '||to_char(i.sal));
end loop;
end;
/游标配合for循环,对表进行查询
begin
for i in (select ename,sal from emp order by sal desc) loop隐示的游标
dbms_output.put_line(i.ename||' salary is '||to_char(i.sal));
end loop;
end
游标可带变量,
declare
cursor c1 is select ename,sal from emp order by sal for update nowait;打开一个可以修改的游标
v1 number;
begin
for i in c1 loop
v1:=c1%rowcount;
update emp set sal=sal+v1
where current of c1;与for update 配合使用
exit when v1>5;当V1>5时退出循环
end loop;
commit;
end;
/ 修改游标指向的当前行的数据,每个人依次增加工资
报错处理:(exception)
预定义错误:
oracle把错误号与错误名称对应在一起,可以用错误名捕获错误,并做处理
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
end;
/出现1422号错误
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
exception
when too_many_row then
dbms_output.put_line(‘more peploe’);进行报错处理.
end;
/
declare
v1 emp.sal%type;
begin
select sal into v1 from emp where sal>10000;
dbms_output.put_line('ok');
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person ');
when NO_DATA_FOUND then
dbms_output.put_line('no person ');两个报错处理
end;
/
非预定义错误
定义-----与错误号相关联
declare
ttt exception;
pragma exception_init(ttt,-2292);定义错误
v1 emp.sal%type;
begin
delete depe;
select sal into v1 from emp where sal>10000;
dbms_output.put_line('ok');
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person ');
when NO_DATA_FOUND then
dbms_output.put_line('no person ');
when ttt then
dbms_output.put_line('forign key ');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
/
自定义错误
过程
desc dbms_output
CREATE OR REPLACE PROCEDURE raise_salary (v_id in emp.empno%TYPE)
IS
BEGIN
UPDATE emp SET sal = sal * 1.10
WHERE empno = v_id;
END raise_salary;
/创建一个过程，为指定代码的人员增加工资
select text from user_source
where NAME ='RAISE_SALARY';
select OBJECT_NAME,OBJECT_TYPE,STATUS
from user_objects
where OBJECT_TYPE='PROCEDURE';
EXECUTE raise_salary (7369);
select empno,ename,sal from emp;
CREATE OR REPLACE PROCEDURE raise_salary (v_id in emp.empno%TYPE) IS
BEGIN
UPDATE emp SET sal = sal * 1.10 WHERE empno = v_id;
commit;
END raise_salary;
/
begin
raise_salary (7900);
end;
/
begin
raise_salary (7900);
raise_salary (7902);
raise_salary (7839);
end;
/
CREATE OR REPLACE PROCEDURE query_emp (v_id IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_salary OUT emp.sal%TYPE, v_comm OUT emp.comm%TYPE)
IS
BEGIN
SELECT ename, sal, comm INTO v_name, v_salary, v_comm FROM emp WHERE empno = v_id;
END query_emp;
/
VARIABLE g_name varchar2(15)
VARIABLE g_salary number
VARIABLE g_comm number
EXECUTE query_emp (7654, :g_name, :g_salary, :g_comm);
PRINT g_name print g_salary print g_comm
Declare
v1 emp.ename%TYPE;
v2 emp.sal%TYPE;
v3 emp.comm%TYPE;
begin
query_emp(7654,v1,v2,v3);
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
/
CREATE OR REPLACE PROCEDURE format_phone (v_phone_no IN OUT VARCHAR2)
IS
BEGIN
v_phone_no := '(' || SUBSTR(v_phone_no,1,3) || ')' || SUBSTR(v_phone_no,4,3) || '-' || SUBSTR(v_phone_no,7);
END format_phone;
/
declare
v1 varchar2(20);
begin
v1:='010456789';
format_phone(v1);
dbms_output.put_line(v1);
end;
/
VARIABLE g_phone_no varchar2(15)
BEGIN
:g_phone_no := '8006330575';
END;
/
EXECUTE format_phone (:g_phone_no) PRINT g_phone_no
CREATE OR REPLACE FUNCTION get_sal (v_id IN emp.empno%TYPE)
RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp
WHERE empno = v_id;
RETURN (v_salary);
END get_sal;
/
select get_sal(7900) from dual;
select 'drop '||object_type||' '||object_name||';'
from user_objects
where object_type in('FUNCTION','PROCEDURE');
set heading off
spool d:/2.txt
select 'drop '||object_type||' '||object_name||';'
from user_objects
where object_type in('FUNCTION','PROCEDURE');
spool off
set heading on
desc UTL_FILE
conn system/manager
select object_name from dba_objects
where object_type='PACKAGE';
DESC DBMS_OUTPUT
CONNECT SCOTT/TIGER
create or replace package PK87 is Function F1 (NO NUMBER) return NUMBER;
Function F1 (NO EMP.ENAME%TYPE) return NUMBER;
PROCEDURE P1(V_NO NUMBER);
end PK87;
/
SHOW ERRORS
create or replace package body PK87 is
-- Function and procedure implementations
FUNCTION F1 (NO IN NUMBER) RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp
WHERE empNO = NO;
RETURN v_salary;
END F1;
FUNCTION F1 (NO EMP.ENAME%TYPE) RETURN NUMBER
IS
v_salary emp.sal%TYPE :=0;
BEGIN SELECT sal INTO v_salary FROM emp
WHERE eNAME = NO;
RETURN v_salary;
END F1;
procedure P1(V_NO in NUMBER) is
begin
UPDATE EMP SET SAL=SAL+1 WHERE EMPNO=V_NO;
COMMIT;
end P1;
end PK87;
/
SELECT TEXT FROM USER_SOURCE WHERE NAME='PK87';
set serveroutput on
DECLARE
V1 EMP.SAL%TYPE;
V2 EMP.ENAME%TYPE;
BEGIN
V1:=PK87.F1(7900);
V2:=PK87.F1('KING');
pk87.P1(7902);
DBMS_OUTPUT.PUT_LINE(V1);
DBMS_OUTPUT.PUT_LINE(V2);
END;
/
create or replace package body PK87 is
-- Function and procedure implementations
FUNCTION F1 (NO IN NUMBER) RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp
WHERE empNO = NO;
RETURN v_salary;
END F1;
FUNCTION F1 (NO EMP.ENAME%TYPE) RETURN NUMBER IS
v_salary emp.sal%TYPE :=0;
BEGIN
SELECT sal INTO v_salary FROM emp WHERE eNAME = NO;
RETURN v_salary;
END F1;
procedure P1(V_NO in NUMBER) is
begin
UPDATE EMP SET SAL=SAL+1
WHERE EMPNO=V_NO;
COMMIT;
end P1;
end PK87;
/
set nls_lang=american_amrica.us7ascii
wrap iname=1.txt
@d:/1.plb
SELECT TEXT FROM USER_SOURCE WHERE NAME='PK87';
connect scott/tiger
drop table d;
drop table e;
create table d as select * from dept;
create table e as select * from emp;
drop trigger d_update;
CREATE or replace TRIGGER d_update
AFTER delete or UPDATE OF deptno ON d
FOR EACH ROW BEGIN
--当D表的部门号修改的时候E表的对应部门号也相应的修改
IF (UPDATING AND :old.deptno != :new.deptno)
THEN
UPDATE e SET deptno = :new.deptno
WHERE deptno = :old.deptno;
END IF;
--当D表的某个部门号删除的时候，E表的对应部门同时被删除
if deleting then delete e
where deptno=:old.deptno;
end if;
END;
/
select * from d;
select * from e;
update d set deptno=80
where deptno=10;
DROP TABLE T1;
CREATE TABLE T1 AS SELECT SAL OLD_value,SAL NEW_value
FROM EMP WHERE 0=9
CREATE OR REPLACE TRIGGER TRG1
BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW
BEGIN
INSERT INTO T1 valueS(:OLD.SAL,:NEW.SAL);
END;
/
update emp set sal=1000 where empno=7900;
--验证触发器的状态
select trigger_name,status from user_triggers;
--改变触发器的状态
--禁用某个触发器
ALTER TRIGGER trigger_instead_of disable;
--禁用某个表上的所有触发器
alter table e disable all triggers;
--删除触发器
DROP TRIGGER trigger_instead_of ;
drop table e1;
create table e1 as select * from emp;
drop view v1;
create view v1 as select distinct deptno from e1;
select * from v1;
select * from e1;
delete v1 where deptno=10;
--建立一个替代触发器，当修改V1的时候会自动的修改基表
create or replace trigger trigger_instead_of
instead of insert or update or delete on v1 for
each row
begin
if updating then update e1 set deptno=:new.deptno
where deptno=:old.deptno;
end if;
end;
/
--验证触发器起作用
update v1 set deptno=50 where deptno=10;
select * from e1;
commit;
浪淘沙
[这是真的吗?] 350 + 100 + 150 = 350 ?
2005/07/28 06:03pm IP: 已设置保密 [本文共10551字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 9 楼]
2.数据库的体系
体系结构(oracle_study/oracle_ppt/9idba1)
user---------sercer---------- instance--------数据库(date. -
PGA
Oracle server
Instance实例
是访问数据库的方法;一个实例仅能打开一个数据库,但一个库可以为多个实例访问
select tableSPACE_name from dba_tables where table_name='EMP'; 显示指定文件的位置
SELECT NAME FROM V$DATAFILE; 显示数据库已有的文件
集群RAC
CONN SYSTEM/MANAGER
SELECT * FROM V$SESSION WHERE USERNAME='SYSTEM';查看会话信息
数据库分为:DATE.CONTROL.REDO
SELECT * FROM EMP;
1.语法分析;
2.语意分析; 这些过程存入内存,当再次执行时只需要运行第4步
3.执行计算
4.运行计划
select * from v$sga;显示sga的内容
NAME value
------------------------------------------ ----------
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
show parameter sga_max_size显示参数,sga的最大区域,总的最大不能变化,内部可以互相动态调整
进程ptocess :(user.server.backgroud)
后台进程包括:
desc v$dgprocess;查看后台进程
show user;
select NAME,DESCRIPTION from v$bgprocess where paddr<>'00'; 查看后台进程的名称和描述
数据库写进程DBWN:存盘.空块.超时.RAC.表空间发生变化.
日志写进程LGWR:把日志写入硬盘,commit时.1/3时,达到1M时
show parameter log_buffer显示容量
系统监测进程SMON
进程监测进程PMON:
检测点进程CKPT:ORACLE的存盘策略
数据库管理工具
1.oul
2.ODCA
3.PFU
4.SQL*PLUS
5.OEM
SYS:数据库的所有者
System:
密码管理:
conn / as sysdba
show user 显示用户
conn sys/sdhfdskfh as sysdba 以任何密码进入sys,在s系统认证的情况下
e:/oracle/ora92/network/admin /sqlnet.ora
# SQLNET.ORA Network Configuration 文件: E:/oracle/ora92/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NTS)不允许系统验证,只能通过密码验证进入系统
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
[专用]SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn system/manager
已连接。
SQL> alter user sys identified by oracle;
用户已更改。
SQL> conn sys/oracle as sysdba
已连接。
通过system修改sys的密码后,可以连接sys
select instance_name from v$instance;查找数据库密码文件各
E:/oracle/ora92/database/pwdsystem.ora 找到现有的数据库密码文件
SQL>Shutdown immediate
Delete pwdsystem.ora 使用’资源管理器’删除
Dos>orapwd file=E:/oracle/ora92/database/pwdsystem.ora password=qq
Sqlplus /nolog
SQL>startup 启动系统例程
SQL> conn sys/qq as sysdba
已连接到空闲例程。 重建密码文件,并写入新的密码
select count(*) from session_privs;查看当前用户的权限
select * from v$pwfile_users;查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
select * from v$version;查看数据库版本
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
grant sysoper to scott;把操作员身分授权给scott
授权成功
select * from v$pwfile_users; 查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
SCOTT FALSE TRUE
SQL> conn scott/tiger
已连接。
SQL> show user
USER 为"SCOTT"
SQL> conn scott/tiger as sysoper 以系统操作员的身份登录
已连接。
SQL> show user
USER 为"PUBLIC"
SQL> conn sys/qq as sysdba
已连接。
SQL> grant sysdba to scott;把系统管理员的身份授权给scott
授权成功。
SQL> conn scott/tiger as sysdba 以系统管理员身份连接
已连接。
SQL> show user;
USER 为"SYS"
管理一个实例
E:/oracle/ora92/database/spfilesystem.ora 二进制的初始化参数文件
Regedit : 本地---》软件----》oracle->home0 :oracle_home 参数 查看oracle的存在位置
Sql>create pfile from spfile;生成纯文本的初始化参数文件
文件已创建
已存在E:/oracle/ora92/database/initsystem.ora纯文本的初始化参数文件
SQL> select distinct ISSPECIFIED from v$spparameter; 查询二进制的初始化参数
ISSPEC
------
FALSE
TRUE 指明数据库使用二进制的初始化参数
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
修改文件明E:/oracle/ora92/database/spfilesystem.ora为E:/oracle/ora92/database/spfile.or
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC
------
FALSE 使用纯文本的初始化参数文件
数据库启动参数文件的顺序:spfilesystem.ora----spfile.ora-----initsystem.ora
动态修改数据库,下次启动时使用上次修改过的参数.
体系结构(oracle_study/oracle_ppt/9idba1)
user---------sercer---------- instance--------数据库(date. -
PGA
Oracle server
Instance实例
是访问数据库的方法;一个实例仅能打开一个数据库,但一个库可以为多个实例访问
select tableSPACE_name from dba_tables where table_name='EMP'; 显示指定文件的位置
SELECT NAME FROM V$DATAFILE; 显示数据库已有的文件
集群RAC
CONN SYSTEM/MANAGER
SELECT * FROM V$SESSION WHERE USERNAME='SYSTEM';查看会话信息
数据库分为:DATE.CONTROL.REDO
SELECT * FROM EMP;
5.语法分析;
6.语意分析; 这些过程存入内存,当再次执行时只需要运行第4步
7.执行计算
8.运行计划
select * from v$sga;显示sga的内容
NAME value
------------------------------------------ ----------
Fixed Size 453492
Variable Size 109051904
Database Buffers 25165824
Redo Buffers 667648
show parameter sga_max_size显示参数,sga的最大区域,总的最大不能变化,内部可以互相动态调整
进程ptocess :(user.server.backgroud)
后台进程包括:
desc v$dgprocess;查看后台进程
show user;
select NAME,DESCRIPTION from v$bgprocess where paddr<>'00'; 查看后台进程的名称和描述
数据库写进程DBWN:存盘.空块.超时.RAC.表空间发生变化.
日志写进程LGWR:把日志写入硬盘,commit时.1/3时,达到1M时
show parameter log_buffer显示容量
系统监测进程SMON
进程监测进程PMON:
检测点进程CKPT:ORACLE的存盘策略
数据库管理工具
6.oul
7.ODCA
8.PFU
9.SQL*PLUS
10.OEM
SYS:数据库的所有者
System:
密码管理:
conn / as sysdba
show user 显示用户
conn sys/sdhfdskfh as sysdba 以任何密码进入sys,在s系统认证的情况下
e:/oracle/ora92/network/admin /sqlnet.ora
# SQLNET.ORA Network Configuration 文件: E:/oracle/ora92/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.AUTHENTICATION_SERVICES= (NTS)不允许系统验证,只能通过密码验证进入系统
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
[专用]SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys/sys as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn system/manager
已连接。
SQL> alter user sys identified by oracle;
用户已更改。
SQL> conn sys/oracle as sysdba
已连接。
通过system修改sys的密码后,可以连接sys
select instance_name from v$instance;查找数据库密码文件各
E:/oracle/ora92/database/pwdsystem.ora 找到现有的数据库密码文件
SQL>Shutdown immediate
Delete pwdsystem.ora 使用’资源管理器’删除
Dos>orapwd file=E:/oracle/ora92/database/pwdsystem.ora password=qq
Sqlplus /nolog
SQL>startup 启动系统例程
SQL> conn sys/qq as sysdba
已连接到空闲例程。 重建密码文件,并写入新的密码
select count(*) from session_privs;查看当前用户的权限
select * from v$pwfile_users;查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
select * from v$version;查看数据库版本
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
grant sysoper to scott;把操作员身分授权给scott
授权成功
select * from v$pwfile_users; 查看系统已有的用户
USERNAME SYSDB SYSOP
--------------- ----- -----
SYS TRUE TRUE
SCOTT FALSE TRUE
SQL> conn scott/tiger
已连接。
SQL> show user
USER 为"SCOTT"
SQL> conn scott/tiger as sysoper 以系统操作员的身份登录
已连接。
SQL> show user
USER 为"PUBLIC"
SQL> conn sys/qq as sysdba
已连接。
SQL> grant sysdba to scott;把系统管理员的身份授权给scott
授权成功。
SQL> conn scott/tiger as sysdba 以系统管理员身份连接
已连接。
SQL> show user;
USER 为"SYS"
浪淘沙
[免收开户费] 114竞价广告，按效果付费，不点击不付费!
2005/07/28 06:03pm IP: 已设置保密 [本文共11518字节]
轮胎菲拉洛
信息:
威望: +1
魅力:
经验:
现金: 3983 美斯特 币
存款: 147751 美斯特 币
贷款: 没贷款
来自: 甘肃兰州
发帖: 1265 篇
精华: 1 篇
资料:
在线: 190 时 22 分 32 秒
注册: 2003/10/17
消息 查看 搜索 好友 复制 引用 回复 [第 10 楼]
管理一个实例
E:/oracle/ora92/database/spfilesystem.ora 二进制的初始化参数文件
Regedit : 本地---》软件----》oracle->home0 :oracle_home 参数 查看oracle的存在位置
Sql>create pfile from spfile;生成纯文本的初始化参数文件
文件已创建
已存在E:/oracle/ora92/database/initsystem.ora纯文本的初始化参数文件
SQL> select distinct ISSPECIFIED from v$spparameter; 查询二进制的初始化参数
ISSPEC
------
FALSE
TRUE 指明数据库使用二进制的初始化参数
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
修改文件明E:/oracle/ora92/database/spfilesystem.ora为E:/oracle/ora92/database/spfile.or
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC
------
FALSE 使用纯文本的初始化参数文件
数据库启动参数文件的顺序:spfilesystem.ora----spfile.ora-----initsystem.ora
动态修改数据库,下次启动时使用上次修改过的参数.
3.远程数据库如何连接
4.backup/recover
5.优化
182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
