oracle sql语句精讲

表 ：建立表主要指定义下列信息：
列定义
完整性约束
表所在表空间
存储特性
可选择的聚集
从一查询获得数据
语法如下：
CREATE TABLE tablename (column1 datatype [DEFAULT expression] [constraint], column1 datatype [DEFAULT expression] [constraint], ……)
例如：
SQL> create table serv (serv_id number(10),serv_seq_nbr number(3))
2 tablespace data_bill;
视图
视图是一个逻辑表，它允许操作者从其它表或视图存取数据，视图本身不包含数据。视图所基于的表称为基表。
引入视图有下列作用：
提供附加的表安全级，限制存取基表的行或/和列集合。
隐藏数据复杂性。
为数据提供另一种观点。
促使ORACLE的某些操作在包含视图的数据库上执行，而不在另一个数据库上执行。
2.1分组视图
[例1]简单的分组视图
SQL> create or replace view dept_tot as
2 select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
3 where a.deptno=b.deptno group by a.dname;

查看已建立。
SQL> select * from dept_tot;

DEPT TOTAL_SAL
-------------- ---------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
[例2]合计视图
[例]合计函数视图实例
SQL> create or replace view emp_no1 as
2 select deptno,sum(sal) 工资和,sum(comm) 总和
3 from scott.emp group by deptno;
SQL> select * from emp_no1;
DEPTNO 工资和 总和
--------- --------- ---------
10 8750
20 10875
30 9400 2200
索引
索引是种数据库对象。对于在表或聚集的索引列上的每一值将包含一项，为行提供直接的快速存取。在下列情况ORACLE可利用索引改进性能：
按指定的索引列的值查找行。
按索引列的顺序存取表。
语法：
create index <index_name> on <table_name(column1,column2…)>
[storage语句] [其它语句];
例如：
SQL> create index idx_serv_01 on serv(srev_id,serv_seq_nbr)
2 tablespace data_bill_idx
3 storage (initial 5m next 5m);
存储函数
语法：
create [or replace] function 函数名(参数1，参数2……) RETURN 类型 IS | AS
[局部变量说明]
BEGIN
执行语句；
END；
[例子]：
建立oracle 的函数
create or replace FUNCTION SumDeptSalary(dept_no in number)
return number is
total number(11,2);
begin
select sum(sal) into total from emp where deptno=dept_no;
return(total);
end;
存储过程
语法：
create [or replace] procedure 函数名(参数1，参数2……) IS | AS
[局部变量说明]
BEGIN
执行语句；
END；
[例子]
过程的输入、输出参数的用例
create or replace procedure RaiseSalary(RaiseRate in number,empnum in number,
outname out varchar2,outsal out number) is
begin
update emp set sal=sal*(1+RaiseRate);
select ename into outname from emp where empno=empnum;
select sal into outsal from emp where empno=empnum;
end;
函数pl/sql 中的过程参数调用示例
set serveroutput on; //显示变量值的属性
declare name varchar2(10);sal number;
begin
RaiseSalary(0.05,7566,name,sal);
dbms_output.put_line(name);
dbms_output.put_line(sal);
end;：
查找系统时间

select sysdate from dual;
系统日期的格式转换
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS AM') from dual;
分解日期
年 select to_char(sysdate,'year') from dual;
月 select to_char(sysdate,'mon') from dual;
月中第几日 select to_char(sysdate,'dd') from dual;
一年中的第几星期 select to_char(sysdate,'ww') from dual;
季度 select to_char(sysdate,'q') from dual;
一年中的第几天 select to_char(sysdate,'ddd') from dual;
查询时间
testdate1 date
select * from testdate where to_char(trunc(testdate1),'yyyy/mm/dd')='2001/04/09'
oracle 中使用trunc()函数把所有日期的时间值设置为12:00AM，消除sysdata中的时间
trunc()函数可进行时间加减
在select中日期相减得出天数
select trunc(sysdate)-trunc(hiredate) from emp where trunc(sysdate)-trunc(hiredate)>5000;
case 语句使用decode()函数替代

例如：select decode(deptno,10,'十',20,'二十',30,'三十') as chinesecode from emp;
列名 值 表示值
decode列乘积的应用
decode(Yjtx.Lb,0,Yprckmx.Ypsl*Xmxx.Xmpzlbl,1,Yprckmx.Ypsl*Xmxx.Xmpzlbl,2,Yprckmx.Ypsl) as Ypsl
使用decode函数计算
select sum(decode(deptno,20,1))-sum(decode(deptno,30,1)) as d from emp;

伪列
伪列不是表中真正的列,只是特征与列相同
---currval和nextval伪列
currval和nextval伪列与序列联合使用。currval伪列返回被引用的序列的当前值；
nextval伪列增加序列的值，并返回序列的新值。只能用于select values 子句和set 子句
表中主键列增加流水值
insert into employee
values(emp_id_seq.nextval,'stanton bernard');
emp_id_seq为序列
---rownum伪列
rownum伪列指出从表中检索数据的次序。例如，值为1的rownum表明该记录是从表中检索的第一条记录。
rownum伪列最常见的用途是用于where子句。例如
select * from emp where rownum <10;
建立bh流水序列值
create sequence seqczyqx
start with 1
increment by 1
nocycle;
生成表中没有的列的方法
select 'testchar' as dd,sysdate deptno from emp,dual;
字符值 列名
select 2342345234 as dd,sysdate deptno from emp,dual;
数值 列名
________________________________________________________________________
返回字符串的一部分
select substr(ename,1,2) from emp;
-----------------------------------------------------------------------------------集合操作
select * from emp where deptno in(10,20);
-----------------------------------------------------------------------------------
在表中插入常量
insert into testdate select 1,sysdate,sysdate from dual;
-----------------------------------------------------------------------------------
在select中对多个变量赋值
SELECT SFBL ,DWBL into XMBL,DWBL FROM HZBL WHERE CFLBBM=CFLBBM AND HZLBBM=HZLBBM and Degree=Hzsf;
-----------------------------------------------------------------------------------
比较操作中的空问题
is null;is not null
使用nvl()函数
select count(*),nvl(comm_amt,0) from comm;
nvl函数用'0'值置换comm_amt表列中值为空的所有数据行
------------------------------------------------------------------------------
· 从另一张表改进得到
Sql语句：
Create Table <新表> As
Select <列的列表> From <旧表>
Where <约束条件>[可选]
· 拷贝表结构
Create Table <表名> As
Select <列的列表>From <旧表>
Where 1=2