oracle入门总结
一、創建
1.創建用戶
CREATE USER user_name IDENTIFIED BY "password"
CREATE USER "LNCKQ" 创建一个用户，名称为LNCKQ
IDENTIFIED BY "LNCKQ" 密码为LNCKQ
DEFAULT TABLESPACE "GTOA" 默认表空间是GTOA
TEMPORARY TABLESPACE "TEMP" 临时表空间是TEMP
ACCOUNT UNLOCK; 帐号正常未锁定
GRANT"CONNECT" TO "LNCKQ" WITH ADMIN OPTION;
授予用户LNCKQ 创建连接的角色，并可再授权
GRANT"DBA" TO "LNCKQ" WITH ADMIN OPTION;
授予用户LNCKQ 数据库管理员的角色，并可再授权
2.權限
GRANT “權限類型”to user_name
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限grant unlimited session to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)
**********************
grant alert table on tablename to zhangsan with admin option;//关键字with admin option grant alert table on tablename to zhangsan with grant option;//关键字with grant option效角色
角色即权限的集合，可以把一个角色授予给用户
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
************************************
3.創建表
CREATE TABLE table_name (
字段1 字段類型not null ,
字段2 varchar2（12）null ,
.....
primary key(字段)
)
4.創建視圖(視圖中的數據可否修改？)
CREATE OR REPLACE VIEW view_name AS SELECT * FROM EMP, DEPT WHERE EMP.DEPORTNO=DEPT.DEPT_NO
CREATE OR REPLACE VIEW view_name(字段1，字段2.....)
AS SELECT A.字段1，B.字段2.......
FROM table1 A,table2 B
WHERE A.m字段=B.n字段
[GROUP BY ....
ORDER BY ....]
5.創建存儲過程
一个简单的例子修改emp表的ename字段
create or replace procedure update_emp(v_empno in varchar2,v_ename in varchar2) is
begin
update emp set ename=v_ename where empno=v_empno;
end update_emp;
调用事件: exec update_emp('7935','test');
CREATE OR REPLACE PROCEDURE procedure_name(v_no in number)
AS
r_str 表名%rowtype
BEGIN
select * into r_str from 表名where 对应字段=v_no;
dbms_output.put_line(r_str.字段);
END;
6.創建觸發器。
CREATE OR REPLACE TRIGGER trigger_name [BEFORE | AFTER INSTEAD OF ..] ON table_name FOR EACH ROW
begin
insert into 表名(字段名) values(对应值);
end if;
end;
7.創建函數。
CREATE OR REPLACE function SUMAA(A in NUMBER,B in NUMBER) return number;
AS
BEGIN
Result:=POWER(A,B)
END;
FP:=SUMAA(5,2);
create or replace function (Name in type, Name in type, ...) return number
is Result number;
begin
return (Result);
end ;
create or replace function ret_emp_sal(v_ename varchar2)
return numberisv_sal number(7,2);
begin
select nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);
return v_sal;
end;
调用：
var
vsla number
call ret_emp_sal('7935') into :vsal;
8.創建CURSOR
DECLARE CURSOR cussor_name IS select * from emp 可以代表一行數據
DECLARE CURSOR Students_cur --声明游标
IS
SELECT name,dob
FROM Students
WHERE specialty = v_specialty;
begin
--其他代码//
end;
用行變量分別計算各部門的總工資。
DECLARE
CURSOR c_DEPT IS SELECT * FROM DEPT ORDER BY DEPT_NO;
CURSOR C_EMP(p_dept varchar2) is select ename,salary from emp where deportno = p_dept order by ename;
R_emp emp%rowtype;
r_dept dept%rowtype;
v_tot_salary number(10,2);
begin
for r_dept in c_dept loop
dbms_output.put_line('department: ' || r_dept.dept_no ||' - ' ||r_dept.dept_name);
v_tot_salary:=0;
for r_emp in C_EMP(r_dept.dept_no) loop
dbms_output.put_line('name: ' || r_emp.ename ||' salary: ' ||r_emp.salary);
v_tot_salary := v_tot_salary+r_emp.salary;
end loop;
DBMS_OUTPUT.PUT_LINE(V_TOT_SALARY);
END LOOP;
CLOSE C_DEPT;
close C_EMP;
END;
9.創建同義詞。
CREATE [public] SYNONMY synonmy name for 表名
create synonym 名字for 所有者.对象名
10.創建PACKAGE.
CREATE OR REPLACE PACKAGE package_name AS [IS]
PROCEDURE AA();
FUNCTION BB()
............
END package_name
CREATE OR REPLACE PACKAGE BODY package_name AS [IS]
包含內容的命令和函數
end;
11.創建index
CREATE INDEX index_name on tablename (NAME);
CREATE [UNIQUE|BITMAP] INDEX[schema.] index_name ---UNIQUE:说明该索引是唯一索引BITMAP:创建位图索引
ON [schema.] table_name
(column_name[DESC|ASC][, column_name[DESC|ASC]]...) ---DESC|ASC:说明创建的索引
为降序升序排列的
12.創建sequence
create sequence sequence_name //
create sequence S_S_DEPART
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
二、表連接及統計
select d.dname,d.loc,e.ename,e.salary
from dept d,emp e
where d.depno=e.depno (不可用別名)
連接命令有：=，between...and...., in, =..(+)，left join..on ,right join on,...
表合并：
UNION 合并所有記錄，重復記錄只顯示一條。
,UNION ALL 合并所有記錄，包括重復記錄，有幾個顯示幾個。
,INTERSECT,顯示兩個表中都有的記錄，顯示一次。
MINUS ，顯示第一個表有，而第二個表沒有的記錄。
查詢子句：
select deptno,ename,salary from emp e1
where salary = (select max(salary) from emp where deptno deptno=e1.deptno);
--顯示每個部門工資最高的人和工資
select e1.ename,d.dept_name from emp e1,dept d
where e1.deportno=d.dept_no and
exists(select 'x' from emp e2 where e2.ename='ALLEN' and e2.deportno=e1.deportno);
---只要有ALLEN員工的部門出現，子句就會返回‘x’,exists 條件才為真。
等同于：
select e1.ename,d.dEPT_name from emp e1,dept d
where e1.depORtno=d.dept_no and E1.depORtno
in(select depORtno FROM EMP E2 where e2.ename='ALLEN')
顯示和ALLEN同一個部門的所有人。
三、常用命令
a.普通命令
字符
* 1. DECODE(A,A1,B1,A2,B2.....,C)
2. DELETE CASCADE .有外鍵的表，在外鍵字段對應的表刪除行時，會把和這個行相對應的行也刪除。
如：alter table emp
add constraint fk_emp_dept foreign key(dept) references dept(deptno) on delete cascade 在部門表中刪除部門時，對應的emp表中，該部門的員工也被刪除。
3.concat 等同于||
4.initcat 首字母大寫initcat(abc) = Abc upper, lower
* 5.instr(c1,c2,i,j) 返回c2 在c1中的位置。i 表示從c1的第幾個位置開始。j表示c1中的第j個出現的c2
常用于SQL語句
6.length 統計字符數
7.trim 去除前后空格
8.replace(c1,c2,c3) 將c1字符串中的c2用c3代替。一般情況下，c3為NULL，表示把c1中的c2去掉。
9.substr（c1,i,j）提取c1字符串中，從第i個開始的j個字條
數字
1.CEIL,FLOOR 去除非整數的小數部分。分別返回大于原數的整數和小于原數的整數。
2.MOD 返回余數
3.POWER 開方
4.ROUND（i,j）四舍五入i為原數，j為返回的小數位數
5.SIGN 返回正負數。返回值（-1，0，1）
6.TRUNC（i,j）用法同ROUND,直接去除多余部分。
日期：
1.add month(d,j) 返回日期d，加上j個月后的日期
https://www.wendangku.net/doc/7b9440714.html,ST_DAY 返回當月的最后一天
3.months_between（d1,d2）返回d1-d2 的月數
4.NEXT_DAY(d,dow) 返回d日期后的星期dow的日期
5.round（d,fmt）根據格式返回對應的值。
6.sysdate 提取當前日期
7.TRUNC() 同ROUND.取值不同。
FUNCTION:
*1.TO_CHAR(x,fmt) 根據格式返回字符串。
2.to_date(c,fmt) 根據字符串，返回一個日期
3.to_number(c,fmt) 根據格式返回一個數字。
*4.DECODE(x,m1,r1,m2,r2....,d) 相當于一個case語句。用法類似于IIF().
5.GREATEST,LEAST.分別返回后面字符列中，正序排列后最后和最前面的值。
6.UID,USER 返回當前ID和用戶名.
https://www.wendangku.net/doc/7b9440714.html,NDUAGE ,LANG 返回當前語言
GROUP FUNCTION
1.AVG 平均數
2.COUNT 計數
3.MAX,MIN 返回列中的最大值和最小值
4.STDDEV 返回標準差
5.VARIANCE 返回方差
6.SUM 求和
b.PL/SQL中的命令
1. IF...THEN...
2. IF...THEN...ELSE..
3. IF...THEN...ELSIF.......ELSE...
4. LOOP
5. FOOR...LOOP
6. WHILE...LOOP
7. GOTO 程序運行到指定位置
8.fech [語句或游標]...into..對應類型的變量
9.DBMS_OUTPUT.PUT_LINE()
四、數據交換
1. DELARE
變量1 表.a%type;
變量2 表.b%type;
變量3 表.c%type;
用select a,b,c into 變量1，變量2，變量3 from 表where...(表中數據賦值到變量中) 2.declare
變量1 表%rowtype
select * into 變量1 from 表where...
取值時：result:=變量.字段名
3.SQL%FOUND 返回TRUE 或FALSE 初始值為：NULL. 是否有DML的SQL語句在運行。
4.SQL%ROWCOUNT 最后一個SQL就語句，涉及到的行數。
5.FETCH..INTO...
FETCH cursor_name into 對應的變量
五、如何用SQL 制作報表？
六、SQL scrip的作用？
七、DELPHI +ORACLE最好數據處理全部在服務器運行。
一個trigger 和sequence自動輸入編號和日期的簡單實例：觸發器和sequence 的使用
建表：
create table prd
(id number not null primary key,
name varchar2(20) ,
spec varchar2(30) )
建sequence
create sequence prd_seq
increment by 1
start with 1
nomaxvalue
nocycle
nocache
建觸發器
create or replace trigger prd_trig before insert on prd for each row
when(new.id is null)
begin
select mgssfcs.prd_seq.nextval into :new.id from dual;
select sysdate into :new.datetime from dual; 不能直接用update或insert語句輸入end;
其中:new 表示修改后的表。:old表示原來的表，修改前的表
insert into prd(name,spec) values(‘abc’,’12324354235’)
附摘抄：
【d elphi+oracle报表解决方案(一)】
delphi中调用oracle的存储过程(分带返回游标，不返回值两种)
关键字: delphi ,oracle存储过程，游标,返回数据集,报表
注：delphi 6+ oracle 8.1.6
一.创建包与包体
1.附：建表aaclass为下面作测试用
create table aaclass(CID V ARCHAR2(50), CNAME V ARCHAR2(50), pnumber NUMBER(10,0) );
INSERT INTO aaclass values('c1', 'cn1', 10 ) ;
INSERT INTO aaclass values('c2', 'cn2', 40 ) ;
INSERT INTO aaclass values('c1', 'cn3', 30 ) ;
commit;
2.建包:
CREATE OR REPLACE PACKAGE PKG_JCCTEST1 AS
type rc_class is ref cursor;
--求p1,p2的和与差，返回的多个值通过游标返回
procedure GetSubAndSum2(p1 number,p2 number ,
ResultCursor out rc_class);
--查询满足条件的数据集，返回数据集通过游标返回
procedure GetClass2(a in number,ResultCursor out rc_class ) ;
--往表中插一条记录，不返回结果集时，本人用AdoQuery调用(adodataset好象要求必须返回结果集)
procedure InsertClass( p_cid varchar2 ,p_cname varchar2 ,
p_pnumber number) ;
end PKG_JCCTEST1;
3.建包体
CREATE OR REPLACE PACKAGE BODY PKG_JCCTEST1
AS
procedure GetSubAndSum2(p1 number,p2 number ,
ResultCursor out rc_class)
IS
BEGIN
open ResultCursor for
select p1-p2 as "sum", p1+p2 as "sub" from dual;
END ;
procedure GetClass2(a in number,ResultCursor out rc_class )
is
begin
open ResultCursor for
select aaclass.* from aaclass where pnumber >a;
end ;
procedure InsertClass( p_cid varchar2 ,p_cname varchar2 ,
p_pnumber number)
is
begin
insert into aaclass values(p_cid,p_cname,p_pnumber) ;
-- commit;
end ;
二.在delphi中利用AdoDataSet调用上述第一个存储过程
1.利用AdoConnection1连接数据库(驱动为oracle Provider for OLE DB)，
**并在连接字符串中加入这一节: PLSQLRSet=1; 如下所示：Provider=OraOLEDB.Oracle.1;Password=KXD;Persist Security Info=True;User ID=KXD;Data Source=TEST3;PLSQLRSet=1
2.在窗体上加AdoDataSet1 指明连接为上述AdoConnection1,下面可以放一个按钮，单击按钮就能调用第一步中创建的包过程，并返回数据集。代码如下所示：
procedure TForm1.Button1Click(Sender: TObject);
var
AResult , BResult : integer;
begin
ADODataSet1.Close ;
https://www.wendangku.net/doc/7b9440714.html,mandType := cmdText ;
ADODataSet1.Parameters.Clear ;
//***利用call方法调用oracle过程时，参数必须由?来传，即使你要传的参数为常理
//输出游标的参数不需要指定!!!!!!，本来此函数带三个参数，我们这里只需要传两个参数.
https://www.wendangku.net/doc/7b9440714.html,mandText := '{call PKG_JCCTEST1.GetSubAndSum2(?,?)}' ;
//***C 顺序有关，createparam必须放在commandtext赋值语句之后. // 创建第一个参数，对应call中的第一个?,ftinteger为类型,10为长度，45为传入的实参值
ADODataSet1.Parameters.CreateParameter('p1',ftinteger,pdinput,10,45); //创建第二个参数，根据createparameter的顺序自动与call中的第二个参数对应
ADODataSet1.Parameters.CreateParameter('p2',ftinteger,pdinput,10,4); //下面调用ADODataSet1 的open方法，返回数据集(对应包过程的游标)
ADODataSet1.Open ;
//根据存储过程，数据集只有一条记录，所以不需要用while do 来遍历数据集，直接取数据了
//此处的字段名根据包过程中的返回游标对应的字段名来取
//定义的存储过程返回游标如：open ResultCursor for
// select p1-p2 as "sum", p1+p2 as "sub" from dual;
//把对应的字段值取出来即可
AResult := ADODataSet1.Fields.FieldByName('sub').Value ;
BResult := ADODataSet1.Fields.FieldByName('sum').Value ;
//显示结果
showmessage(inttostr(AResult)) ;
showmessage(inttostr(BResult)) ;
end;
三.在delphi中利用AdoDataSet调用上述第二个存储过程
还是利用上述的AdoDataSet1来调用第二个存储过程，无需任何改动,加第二个按钮，单击时代码如下：
procedure TForm1.Button2Click(Sender: TObject);
begin
ADODataSet1.Close ;
https://www.wendangku.net/doc/7b9440714.html,mandType := cmdText ;
ADODataSet1.Parameters.Clear ;
//***利用call方法调用oracle过程时，参数必须由?来传，即使你要传的参数为常理
//输出游标的参数不需要指定!!!!!!，本来此函数带两个参数，我们这里只需要传一个参数.
https://www.wendangku.net/doc/7b9440714.html,mandText := '{call
PKG_JCCTEST1.GetClass2(?)}' ;
//***C 顺序有关，createparam必须放在commandtext赋值语句之后. // 创建第一个参数，对应call中的第一个?,ftinteger为类型,10为长度，20为传入的实参值
ADODataSet1.Parameters.CreateParameter('p1',ftinteger,pdinput,10,20); //下面调用ADODataSet1 的open方法，返回数据集(对应包过程的游标)
ADODataSet1.Open ;
while not ADODataSet1.Eof do
begin
showmessage('CID : '+string(ADODataSet1.FieldByName('CID').Value) +
'--CNAME :' + string(ADODataSet1.FieldByName('CNAME').Value) +
'--PNUMBER :' + string(ADODataSet1.FieldByName('PNUMBER').Value)
) ;
ADODataSet1.Next ;
end ;
end;
四利用adoquery调用第三个过程，不返回数据集的
procedure TForm1.Button3Click(Sender: TObject);
begin
AdoQuery1.Close ;
AdoQuery1.Parameters.Clear ;
AdoQuery1.SQL.Clear ;
AdoQuery1.SQL.Add('{call PKG_JCCTEST1.GetSubAndSum2(?,?)}') ; AdoQuery1.Parameters.CreateParameter('P1',ftstring,pdinput, 50,'c11') ;
AdoQuery1.Parameters.CreateParameter('P2',ftstring,pdinput, 50,'cn11') ;
AdoQuery1.Parameters.CreateParameter('P3',ftinteger,pdinput, 50,25) ; AdoQuery1.ExecSQL ;
end;
五利用adoquery调用第一个过程，返回数据集的.
procedure TForm1.Button4Click(Sender: TObject);
begin
AdoQuery1.Close ;
AdoQuery1.Parameters.Clear ;
AdoQuery1.SQL.Clear ;
AdoQuery1.SQL.Add('{call PKG_JCCTEST1.GetSubAndSum2(?,?)}') ; AdoQuery1.Parameters.CreateParameter('P1',ftinteger,pdinput, 50,25) ; AdoQuery1.Parameters.CreateParameter('P2',ftinteger,pdinput, 50,22) ; AdoQuery1.Open ;
Showmessage(string( AdoQuery1.FieldByName('sub').Value)+'-'+
string( AdoQuery1.FieldByName('sum').Value));
end;
六.关于三层体系的此类问题
两层的解决了，三层类似.
中间层用tadodataset 或tadoquery (+tdatasetprovider),中间层的adoconnection的连接字符串加上plsqlRset=1;
客户端用clientdataset ,大同小异，举例如下:
begin
//调用相应的过程
ClientDataSet1.Close ;
ClientDataSet1.Params.Clear ;
https://www.wendangku.net/doc/7b9440714.html,mandText:='{callPackageName.ProcedureName(?,?) }' ;
ClientDataSet1.Params.CreateParam(ftInteger , 'ParamName1', ptInput) ;
ClientDataSet1.Open ;
end ;
Oracle中分析函数用法小结
Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME(
oracle函数
许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。 关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。 定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual
oracle复习大纲(1)
chapter 7 1.Oracle数据库物理存储结构的组成及各个部分的功能、性质。 数据文件：用于存储数据库中的所有数据 控制文件：用于记录和描述数据库的物理存储结构信息 重做日志文件：用于记录外部程序（用户）对数据库的改变操作 归档文件：用于保存已经写满的重做日志文件 初始化参数文件：用于设置数据库启动时的参数初始化值 跟踪文件：用于记录用户进程、数据库后台进程等的运行情况 口令文件：用于保存具有SYSDBA,SYSOPER权限的用户名和SYS用户口令； 警告文件：用于记录数据库的重要活动以及发生的错误； 备份文件：用于存放数据库备份所产生的文件。 2.数据文件管理： （1）数据文件与表空间的关系 从物理角度看，数据库由数据文件构成，数据存储在数据文件中；从逻辑角度看，数据库是由表空间构成的，数据存储在表空间中。一个表空间包含多个数据文件，但一个数据文件只能属于一个表空间。 在oracle数据库中，数据文件是依附于表空间而存在。一个表空间可以包含多个数据文件，但一个数据文件只能从属于一个表空间。在逻辑上，数据库对象都存放在表空间中，实质上是存放在表空间所对应的数据文件中。 （2）数据文件的添加、数据文件大小的修改autoextend on 、resize（操作）、数据文件的移植 添加： 向表空间添加一个大小为xMB的数据文件： alter tablespace 表空间名add datafile ‘路径\*.dbf’ size xMB; 向表空间添加一个自动增长的数据文件： alter tablespace 表空间名add datafile ‘路径\*.dbf’ size xMB autoextend on next 1M maxsize 50MB; 向临时表空间添加一个大小为xMB的数据文件： alter tablespace 表空间名 add tempfile‘路径\*.dbf’ size xMB; 修改： ①修改大小 修改数据文件大小为yMB： Alter database datafile ‘路径\*.dbf’ resize yMB; 修改文件的自动增长方式： Alter database datafile ‘路径\*.dbf’ autoextend on next 1M maxsize unlimited；
oracle视图总结
oracle视图总结(转) 视图简介: 视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点： 1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂查询中得到结果。 3.维护数据的独立性，试图可从多个表检索数据。 4.对于相同的数据可产生不同的视图。 视图的分类： 视图分为简单视图和复杂视图。 两者区别如下： 1.简单视图只从单表里获取数据，复杂视图从多表获取数据； 2.简单视图不包含函数和数据组，复杂视图包含； 3.简单视图可以实现DML操作，复杂视图不可以。 视图的创建： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 其中： OR REPLACE：若所创建的试图已经存在，ORACLE自动重建该视图； FORCE：不管基表是否存在ORACLE都会自动创建该视图； NOFORCE：只有基表都存在ORACLE才会创建该视图： alias：为视图产生的列定义的别名； subquery：一条完整的SELECT语句，可以在该语句中定义别名； WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束； WITH READ ONLY ：该视图上不能进行任何DML操作。 例如： Sql代码 1.CREATE OR REPLACE VIEW dept_sum_vw 2.(name,minsal,maxsal,avgsal)
Oracle 常见函数(一)——数值函数
Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;
/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分； 没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分； 没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。
eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;
Oracle数据库基本知识点
1、数据库基本语句 （1）表结构处理 创建一个表：cteate table 表名（列1 类型，列2 类型）; 修改表的名字 alter table 旧表名 rename to 新表名 查看表结构 desc 表名（cmd） 添加一个字段 alter table 表名 add（列类型）; 修改字段类型 alter table 表名 modify(列类型); 删除一个字段 alter table 表名 drop column列名; 删除表 drop table 表名 修改列名 alter table 表名 rename column 旧列名 to 新列名; （2）表数据处理 增加数据：insert into 表名 values(所有列的值); insert into 表名（列）values(对应的值); 更新语句：update 表 set 列=新的值，…[where 条件] 删除数据：delete from 表名 where 条件 删除所有数据，不会影响表结构，不会记录日志， 数据不能恢复--》删除很快: truncate table 表名 删除所有数据，包括表结构一并删除: drop table 表名 去除重复的显示：select distinct 列 from 表名 日期类型:to_date(字符串1，字符串2)字符串1是日期的字 符串，字符串2是格式 to_date('1990-1-1','yyyy-mm-dd')-->返回日期的 类型是1990-1-1 （3）查询语句 1）内连接 select a.*,b.* from a inner join b on a.id=b.parent_id
ORACLE笔记
1.Oracle 工具:sqlplus Sqlplus / as sysdba Shutdown immediate(关闭数据库) Startup(启动数据库) 注意：数据库开启才可以进行操作 Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁) Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库) Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库) Show all;(看所有变量) Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示) 注意：如要变量下次重启生效必须把变量写入 /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中 Host(回到系统) Help index（查看所有命令） Help+命令(类似于LINUX中的MAN,查看命令的格式与用法) Disconnect(关闭SQLPLUS工具但是不离开数据库) Describe emp(查看EMP表) Define(定义变量,常用与写脚本用) 如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句) Get /u01/app/oracle/aa.sql (调用保存的SQL语句) 2.select（select 查询语句是ORACLE中最常用的语句） DML语句包括（insert 写入，update改变，delect删除，merge两张表同步） 注意：merge常用在ETL（数据仓库）底下 DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate 删除整个表） TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点) DCL权限语句（grant赋予权限，revoke(收回权限) ORACLE的表称之为堆表（keep table） SELECT (1) writing basic SQL select statemanes 1. selecting all columns SCOTT>select * from dept; SCOTT>select dname,deptno,loc from dept; Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
学习oracle数据库的总结(图文整理)
1、在开发环境中连接到数据库，进行基本的select查询操作； 2、熟悉plsql的使用； 3、熟悉sqlplus相关命令（登录、查询、导入导出等） 登录sqlplus： 第一：使用dos窗口登录sqlplus Sqlplus 用户名/密码@数据库实例名 Sqlplus system/密码@数据库实例名as sysdba 第二：使用oracle自带的一个sqlplus登录，提供界面，显得更简单一些。 显示当前用户名：show user; 创建一个用户：create user 用户名identified by 密码； 给用户赋予登录的权限：grant connect to 用户名；（此时才可以使用这个用户来登录这个数据库。）给其赋予dba的权限。 修改用户的密码：alter user 用户名identified by 新密码； 查询： Select * from t_user; Select id from t_user; Select name from t_user; Select birthday from t_user; Select id,name from t_user; 导入导出： 导出表: （注意，导出表的exp命令不是在sqlplus下使用的，是在dos窗口下使用的命令。） exp userid=test/sa@test tables=(qx) file=d:\e1223.dmp exp userid=test/sa@test tables=(t_user,qx) file=f:\test.dmp 导出方案： Exp userid=test/sa@test owner=test file=f:\test2.dmp 导出数据库： Exp userid=test/sa@test full=y inctype=complete file=f:\all.dmp 导入表： 下面以一个例子来说明： 看下面的图，我的用户名test，密码sa，数据库实例名test，所有的表都在这里 现在我执行导出表JD的操作：exp userid=test/sa@test tables=(jd) file=f:\jd.dmp 在我的f盘下就出现了这么一个.dmp文件
Oracle函数详解(经典)
Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数： 数值函数： ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL;
Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.
Oracle复习
一．创建用户 在YGGL数据库中创建一个用户MANAGER，授予DBA角色和SYSDBA系统权限，它可以替代system系统用户。 CONNECT SYSTEM/123456 AS SYSDAB CREATE USER MANAGER PROFILE "DEFAULT" IDENTIFIED BY manager DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT SYSDBA TO "MANAGER"; GRANT "COUNTNECT" TO "MANAGER"; GRANT "DBA" TO "MANAGER"; ALTER user MANGER GRANT CONNECT THROUGH SYSTEM; 二：创建角色 在YGGL数据库中创建一个角色ADMIN，授予DBA角色和SYSDBA系统权限。CREATE ROLE ADMIN IDENTIFILED BY "manager"; GRANT SYSDAB TO ADMIN ; GRANT SYSOPER TO ADMIN; GRANT "CONNECT" TO ADMIN; GRANT "DBA" TO "ADMIN"; 一．创建概要文件 在YGGL数据库中创建概要文件YGGL_PROFILE并分配给用户MANAGER。CREATE PROFILE "XSCJ _PROFILE"LIMIT COMPOSITE_LIMIT DEFAULT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 20 PASSWORD_GRACE_TIME 60 PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 120 PASSWORD_VERIFY_FUNCTION DEFAULT （1）使用命令方式分别向YGGL数据库个表中插入一行记录 INSERT INTO ZPEMPLOYEES V ALUES('011112','罗林 ',TO_DATE('26-06-1973','DD-MM-YYYY'),1,'解放路100 ',210002,4055663,5); INSERT INTO ZPDEPARTMENTS V ALUES('6','人力资源',NULL); INSERT INTO ZPSALARY V ALUES ('011112',1200.09,50) （2）修改表salry的记录值 UPDATE ZPSALARY SET InCome=2890 WHERE EmployeeID='011112'; （3）删除Salary中的一行记录 DELETE FROM ZPSALARY WHERE EmployeeID='01112'; 3.索引和完整性 (1).对YGGL数据库中的Employees表的DepartmentID列建立索引 CREATE INDEX PK_XS_BAK ON ZPEMPLOYEES(DepartmentID) TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100 STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 5 MAXEXTENTS 20
Oracle实验总结
Oracle实验总结 Oracle实验总结 Oracle实验总结 近日做了基于ORACLE的数据库安全、对象sql数据库，XML数据库的实验，其中遇到很多问题，先总结如下。 1、安装Oracle的过程中可以‘选择建立数据库’，也可以选择‘只安装软件’，后者可在安装好软件后，建立数据库，可参考网上的很多安装步骤。 2、Oracle的默认用户有sys和system,在命令行或者sQl*plus中登陆时，写法如下： Connectsys/123456assysdba;其中123456是密码，在创建数据库的时候设置的。 普通用户登录，connectzhangsan/123456; 3、grantselect不支持对表中选定的几列授权，只能将整个表的select权限授权出去。所以实验中先建立了基于选课信息表的视图SelectiveInfo1，视图中包含成绩列，学号列以及班级列（不包含其他的列）,再将对视图SelectiveInfo1的select权限授予角色teacher。 4、创建用户的时候，一定要说明该用户对表空间的权限即（quotaunlimitedontablespace或者具体限定大小的句子），否则该用户即使被授予了建表权限，也不能建表。 创建用户的时候必须给用户授予createsession的权限，这是最基本的权限。 5、不同的用户可以创建相同的表格，例如，teacher用户创建了table1,student 用户也创建了table1,且这两个表格式完全相同的，那么其他用户需要用这两个表格时，可以用teacher.table1和student.table1加以区分。系统用户创建的
ORACLE常用函数和一些项目中常用的SQL语句
Oracle笔记 实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept； 从已知表中复制数据和结构，但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段，select deptno ||dname from dept 比较运算付：> >= = !=< <= 逻辑运算符：not ，or 集合运算符：intersect，union，union all，minus， 使用ord er by 的时候必须使用位置序号，不能使用列名 Select *from emp intersect select * from emp where deptno=10； Select *from emp minus select * from emp where deptno=10； ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in（10，20）；----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in（10，20）； 三、常用ORACLE函数
oracle学习心得体会
oracle学习心得 一、sqlserver的理解 sqlserver服务器就像一栋大楼，大楼里的机房就像服务器的数据库，机房里的电脑如同数据库里的表 1、登录用户可以登录服务器——可以进大楼 2、登录用户成为数据库用户才能进指定的数据库——进入大楼的人给了某个机房的钥匙 才能进入机房 3、登录用户有权限使用表——进入机房的人有电脑的密码才能使用电脑 二、oracle的理解 oracle服务器（全局数据库）就像一个商场，商场的每一家公司是表空间，公司的业务是表 1、数据库由多个表空间组成——商场里有多家公司组成 2、表空间由段组成——公司要有自己的经营业务，可以只有一个业务，就是一个表空 间中只有一个段，可以有多个业务，就是一个表空间有多个段 3、段由区组成——单个业务的细分类别。例如有家公司经营三个业务，卖书，卖家电，
卖衣服，则每个业务就是一个段。而每个业务又有细分，比如卖书的话要进行分类了。计算机区，人文区，小说区等，每一区都要放上书架存放书籍，则书架就是oracle块，存放数据的 三、数据库，表空间，用户（指定默认表空间），表统统由管理员管理 四、在oem中管理数据库的步骤 1、创建 1)存储——表空间——创建表空间(tomspace)（类似于在sql中创建数据库， 通常可以省略，使用默认表空间为users,,临时表空间为temp） 2)首先展开安全性——创建新用户（tom，指定表空间）（类似于在sql中指定 数据库用户） 3)创建表——指定方案（用户）和表空间（列名不要带<>） 4)设置约束 5)输入信息：方案——用户名——表——右击——查看/编辑目录… 2、修改 1）方案——用户名——表 2）修改表结构，添加约束
oracle的sqlldr的使用总结
oracle的sqlldr的使用总结 part i一：sql loader 的特点 oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle 公司自己产品的兼容性的问题吧。 sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob 等类型的数据就有点麻烦了。 二：sql loader 的帮助 C:\>sqlldr SQL*Loader: Release 9.2.0.1.0 - Production on 星期六10月9 14:48:12 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 用法: SQLLDR keyword=value [,keyword=value,...] 有效的关键字: userid -- ORACLE username/password control -- Control file name log -- Log file name bad -- Bad file name data -- Data file name discard -- Discard file name discardmax -- Number of discards to allow (全部默认) skip -- Number of logical records to skip (默认0) load -- Number of logical records to load (全部默认) errors -- Number of errors to allow (默认50) rows -- Number of rows in conventional path bind array or between direct p ath data saves （默认: 常规路径64, 所有直接路径） bindsize -- Size of conventional path bind array in bytes(默认256000) silent -- Suppress messages during run (header,feedback,errors,discards,part itions) direct -- use direct path (默认FALSE) parfile -- parameter file: name of file that contains parameter specification s parallel -- do parallel load (默认FALSE) file -- File to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默 认FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus
oracle中常用函数大全
oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;
Oracle数据库期末复习知识点整理
基础知识 表3.2 Oracle数据类型
表3.3 XSB的表结构
操作表 创建表 CREATE TABLE [schema.] table_name ( column_namedatatype [DEFAULT expression] [column_constraint][,…n]
oracle体系结构总结
oracle体系结构总结 传统上Oracle体系结构由内存结构、进程结构、存储结构组成。其中，内存结构由SGA、PGA组成；进程结构由用户进程和Oracle进程组成；存储结构由逻辑存储、物理存储组成。 通过数据库的启动过程和一条sql的执行过程可以将oracle的内存结构、进程结构、存储结构串起来。 一、内存结构 (1)SGA(System Global Area):一个包含实例数据和控制信息的共享内存区域 (2)PGA(Program Global Area):一个包含服务器进程和后台进程的数据和控制信息的共享内存区域 1.1系统全局区SGA 系统全局区，是DB Server 中实例的必要组成部分，由很多小内存区共同构成，各个小内存区存放不同的信息，系统全局区用于暂存可以被所有进程（包括server process 以及background process）共享的信息，对系统的运行性能有好处，SGA 在实例启动的时候自动分配，实例关闭时自动释放。 SGA 暂存系统的大量共享数据，对系统有相当的性能影响，所以需要为SGA 选择适当的管理方式，根据所使用的数据库版本不同，SGA 的管理有三种方式：1）8i:SGA 的大小由所有内存组件大小之和决定，不能直接定义SGA 大小，对内存组件的大小可后台进行修改，但只能通过直接修改参数文件的内存参数大小，而且修改完数据库必须重起后才能生效，所以这种内存管理方法叫做SGA 的静态管理。 2）9i:SGA 的大小由初始化参数SGA_MAX_SIZ E 确定，各个内存组件大小之和不能超过这个参数。可以直接通过命令进行修改内存组件大小而不用重启，这种叫做SGA 的动态管理
Oracle常用函数及使用案例(珍藏版)
Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.
Oracle高级查询总结
高级查询总结 A．层次查询：start with……connec by prior…….. select lpad(' ',3*level)||ename,job,level from emp start with mgr is null connect by prior empno=mgr; 从根结点向下查，lpad()为左添加，level为第几层，prior为查找的方向；此句若省掉start with….则表示要遍历整个树型结构；若出现level，则后面一定要跟connect by B.交叉报表(case when then end 与decode()) select ename,case when sal>=1500then sal*1.01 else sal*1.1 end工资 from emp; select姓名, max(case课程when'语文'then分数end) 语文, max(case课程when'数学'then分数end) 数学, max(case课程when'历史'then分数end) 历史 from学生group by姓名;------(交叉报表与函数一起使用) select ename,sum(decode(sal,'sal',comm,null)) 奖金from emp group by ename;--可实现分支 decode(条件,(值),(返回值),…………,(默认值)) 部门 select sal,decode(sign(sal-1500),-1,1.1*sal,0,1.1*sal,1,1.05*sal) from emp; C.连接查询 1．等值： select * from emp,dept where emp.deptno(+)=dept.deptno; ‘+’在左则以右表为主，反之亦然 2.非等值：where的运算符不是等号 3.自然连接: select * from emp natural join dept 4.99乘法: select * from emp e full join dept d using (deptno) where deptno=10; --where必须放在using(on)的后面 D集合查询： 1．A Union B:消除重复行，有all则可以重复,默认第一列升序select ename,sal from deptno=20 union select ename,sal from job=’CLERK’; 2．A intersect B:A与B的交集 select ename,sal from deptno=20 intersect select ename,sal from job=’CLERK’; 3.A minus B:在A中减去既属于A又属于B的一部分