Oracle 基础学习笔记

2012-10-11 09:20 颜熙阅读(216) 评论(0) 编辑收藏举报

group by

GROUP BY 子句时，一定要记住下面的规则：

不能 GROUP BY text、image 或 bit 数据类型的列

SELECT 列表中指定的每一列也必须出现在 GROUP BY 子句中，除非这列是用于聚合函数。（重要）

不能 GROUP BY 列的别名。这是说 GROUP BY 字段列表中的所有字段必须是实际存在于 FROM 子句中指定的表中的列。

进行分组前可以使用 WHERE 子句消除不满足条件的行。

使用 GROUP BY 子句返回的组没有特定的顺序。可以使用 ORDER BY 子句指定想要的排序次序。

Eg:

错

select ord.prodname,ord.billid,ord.prodid,ord.prodno,ord.price, sum(ord.quantity) as totalQuantity

from tb_gos_sale_orddet ord

group by ord.prodname

对

select ord.prodname,ord.billid,ord.prodid,ord.prodno,ord.price, sum(ord.quantity) as totalQuantity

from tb_gos_sale_orddet ord

group by ord.prodname,ord.billid,ord.prodid,ord.prodno,ord.price

order by

asc 表示升序 desc 表示降序默认为asc

运用order by 也可以同时对多个属性进行排序

运用order by 语句查询时，若存在null值，按照升序排序则含有null值得记录（元组）将在最后显示，按降序则显示在最前面

Eg:

select ord.billid,ord.prodname
from tb_gos_sale_orddet ord
order by ord.prodname asc,billid desc

对使用聚集函数列也可以使用排序

Eg:

select ord.billid,ord.prodname,avg(ord.quantity) as avgquantity
from tb_gos_sale_orddet ord
group by ord.billid,ord.prodname
order by ord.prodname asc,billid desc,avgquantity desc

指定字段的序号进行排序

Select Depid,Stdname,Salar

From Stuff

Order by 1,3 desc

不显示的字段也可以进行排序

对字段别名行进排序

Select DEPID,STDNAME,SALARY 工资

From stuff

Order by DEPID,工资 desc

having

HAVING子句经常与GROUP BY语句同时出现，它相当于一个用于组的WHERE子句。也就是说，WHERE子句限定行，而HAVING子句限定组。

HAVING子句只有与GROUP BY子句联用才能对分组进行约束。只使用HAVING子句而不使用GROUP BY子句是没有意义的。

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

Eg:

select ord.billid,ord.prodname,sum(ord.quantity)

from tb_gos_sale_orddet ord

group by ord.billid,ord.prodname

having sum(ord.quantity)>100

当同时含有where子句、group by 子句、having子句及聚集函数时，执行顺序如下：
1.where子句查找符合条件的数据行；
2.group by 子句对数据行进行分组；
3.聚集函数对group by 子句形成的组运行计算每一组的值；
4.最后用having 子句去掉不符合条件的组。

Eg:

select ord.billid,ord.prodname,sum(ord.quantity) 3

from tb_gos_sale_orddet ord

where ord.prodname like '维生素%' 1

group by ord.billid,ord.prodname 2

having sum(ord.quantity)>100 4

DISTINCT

对于使用了GROUP BY的查询，再使用DISTINCT是多余的，因为已经进行分组，不会移除任何行。

1、distinct col1,col2是将col1,col2两个字段看成一体，只要col1,col2这两个字段相同，
就将其视为重复记录;
2、在Select中只能用一次distinct或all;
3、在Select中用了distinct就不能用ALL；用了ALL就不能用distinct;不能同时存在；
4、如果指定了SELECT DISTINCT，那么ORDER BY 子句中的项就必须出现在选择列表中;

Eg:
select distinct ord.prodname,ord.quantity
from tb_gos_sale_orddet ord

DISTINCT 省略选择字段中包含重复数据的记录。

DISTINCTROW 省略基于整个重复记录的数据，而不只是基于重复字段的数据。

Top

TOP 子句用于规定要返回的记录的数目

Eg:

SQL Server：

SELECT TOP 5 (percent) * FROM tb_gos_sale_orddet

Oracle：

SELECT * FROM tb_gos_sale_orddet WHERE ROWNUM <= 5 ;

PERCENT

UNION

SQL UNION查询用于合并两个或以上SQL SELECT 查询结果。每一个查询语句必须有相同的列结构：数目相同的列，相同或相容的数据类型以及相同的序列。

注： SQL UNION查询返回唯一不同的值(distinct value)。

SELECT Column(s) FROM 表1

UNION

SELECT Column(s) FROM 表2

Eg:

SELECT det.createtime FROM tb_gos_sale_orddet det
union
select sum.createtime from tb_gos_sale_ordsum sum

Where

字段名1 >(或<、=等) 字段名2(常量)//字段间（或常量）进行比较

字段名3 [Not] Between 字段名4 And 字段名5

（或Between 常量1 And 常量2）//设定范围

字段名6 [Not] In（集合） // 确定集合

字段名7 [Not] Like 字符串 // 字符匹配

字段名8 Is [Not] Null // 利用空值查询

集合函数

COUNT() 统计元组个数

SUM() 计算一列值的总和（该列为数据型）

AVG() 计算一列的平均值（该列为数据型）

MAX() 求一列值中的最大值

MIN() 求一列值中的最小值

INSERT…SELECT

所有满足 SELECT 语句的行都被插入最外层

必须检验被插入了新行的表是否存在数据库中

确保数据类型是兼容的

确定是否存在缺省值，或所有被忽略的列是否允许空值

Eg：

insert Customers

select lastname,firstname,title,address,city,null from Employees

SELECT INTO 语句创建表

可使用 SELECT INTO 语句创建一个表并在同一操作中往表里插入行

SELECT lastname AS name,title AS tit

INTO Customers2

FROM Employees

连接

从多个表中选择指定的字段，用join指定要连接的表，以及连接方式，关键字ON指定连接条件。所有的连接表必须有相同或兼容的数据类型

内连接(排它的连接—排除在两表中没有匹配的所有记录)INNER JOIN

select * from tb_gos_sale_ordsum ord inner join tb_gos_sale_orddet det on ord.pk=det.fk;

外连接（从两个表中返回符合连接条件的记录，同时也将返回左（右）边不符合连接条件的记录。不满足连接条件的几率将显示空值，左连接显示第一个表中所有记录，右连接可以显示第二个表中所有的记录）

LEFT JOIN

select * from tb_gos_sale_ordsum ord left join tb_gos_sale_orddet det on ord.pk=det.fk;

RIGHT JOIN

select * from tb_gos_sale_ordsum ord right join tb_gos_sale_orddet det on ord.pk=det.fk;

完全连接（将JOIN两侧的数据全部匹配，并返回所有的记录。目的是返回没有参考的记录之间的所有关系）FULL JOIN

select * from tb_gos_sale_ordsum ord full join tb_gos_sale_orddet det on ord.pk=det.fk;

约束

域（或列）约束：指定对于列有效的数据值

实体（或行）约束：要求表中所有行具有唯一性

参照完整性约束：在表或者同一个表的列之间存在值的匹配

约束类型	约束类型	描述
域	DEFAULT	指定列的默认值
	CHECK	指定列的允许值
	FOREIGN KEY	指定必须存在值的列
	NULL	指定是否允许为NULL
实体	PRIMARY KEY	唯一标识每一行
实体	UNIQUE	防止非主键重复
引用	FOREIGN KEY	定义值与同一个表或另一个表的主键值匹配的一列或多列组合
引用	CHECK	指定根据同一个表中其他列的值可在列中接受的数据值

键约束

PRIMARY KEY 约束定义表中构成主键的一列或多列

一个表只能有一个 PRIMARY KEY 约束

指定为 PRIMARY KEY 的列中的值必须是唯一的

包含在 PRIMARY KEY 约束中的列不能接受 NULL值

create table xsb

(

xh int CONSTRAINT PK_cjb PRIMARY KEY,

xm varchar(50) not null,

zy int null

)

或CONSTRAINT PK_cjb PRIMARY KEY (xh))

FOREIGN KEY 约束

FOREIGN KEY 约束确保同一个表或者不同表之间的参照完整性约束

必须引用一个PRIMARY KEY或者UNIQUE约束

1. 名词

主键表—被引用表

外键表—引用表

2. 定义外键约束后,一些操作被限制

引用表插入的外键列值必须在被引用表中存在

删除被引用表中一行时必须保证引用表中没有相关记录

删除外键约束前,不允许删除被引用表

Eg:

create table Loan

(

PID varchar(8) not null,

BID varchar(8) not null,

money int not null,

Bdate date not null,

Rdate date,

primary key(PID,BID),

constraint FK_Loan_Project foreign key (PID) references Project(PID),

constraint FK_Loan_Bank foreign key (BID) references Bank(BID),

constraint CN_Loan_Rdate_Check check (Rdate<Bdate)

);

UNIQUE约束

UNIQUE约束确保列中的每个值都是唯一的

在约束列中允许一个NULL值

可以包含一列或者多列

Eg：

ALTER TABLE Employees

ADD CONSTRAINT AK_EmployeeSSN

UNIQUE (SSN)

CHECK 约束

CHECK 约束限制在 INSERT 和 UPDATE 语句期间用户可输入特定列中的数据值

每列可以定义多个CHECK约束

在同一个表格中可以引用列

不能包含子查询

Eg:（控制CJB的fs列不能小于0或大于100）

ALTER TABLE cjb

ADD CONSTRAINT CN_CHECK_fs

CHECK (fs BETWEEN 0 AND 100)

DEFAULT 约束

当没有提供值的时候，DEFAULT 为用定义一个默认的列值。

每列只允许一个DEFAULT 约束

只适用于INSERT 约束

允许系统提供的一些值

Eg:(CJB的fs列默认值0)

ALTER TABLE cjb

ADD CONSTRAINT CN_default_fs

DEFAULT '0' FOR fs

-------删除约束
alter table 表名
drop constraint 约束名

约束不能修改，只能删除后再添加

索引

群集索引

数据将按照索引的顺序重新进行物理存储

一个表中只能有一个群集索引

非群集索引

不改变数据的物理顺序，只保存执行对应行的指针

一个表中可以有多个非群集索引

创建索引需要消耗服务器资源，也会影响到整个数据库的运行性能

查询中很少涉及的列或者重复值比较多是列，不要建立索引

对于按范围查询的列，最好建立索引（范围查询最好能利用TOP关键字来限制查询，可以大大的提高查询的效率）

表中若有主键或者外键，一定要为其建立索引

特殊数据类型，不要建立索引

索引和Where语句的集合融为一体

下面的表总结了何时使用聚集索引或非聚集索引(很重要)。

动作描述	使用聚集索引	使用非聚集索引
外键列	应	应
主键列	应	应
列经常被分组排序(order by)	应	应
返回某范围内的数据	应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
频繁修改索引列	不应	应
一个或极少不同值	不应	不应

创建Oracle索引的标准语法:

CREATE INDEX 索引名 ON 表名 (列名)

TABLESPACE 表空间名;

创建唯一索引:

CREATE unique INDEX 索引名 ON 表名 (列名)

TABLESPACE 表空间名;

创建组合索引:

CREATE INDEX 索引名 ON 表名 (列名1,列名2)

TABLESPACE 表空间名;

创建反向键索引:

CREATE INDEX 索引名 ON 表名 (列名) reverse

TABLESPACE 表空间名;

总结：
1.什么是索引：数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
3.创建索引的标准：用于频繁搜索的列；用于对数据进行排序的列（汉语字典中的部首查询）
注意：如果表中仅有几行，或列中只包含几个不同的值，不推荐创建索引，因为SQL Server 在小型表中用索引搜索数据所花的时间比逐行搜索更长。

降低索引效率：

1、索引的字段不能参与运算（索引无效）

Select * from table where substr(name,1,1)='李'

Select * from table where age+10>30

2、索引的字段上不能使用函数

select * from emp

where to_char(hiredate,'yyyymm') >= '198210' and

to_char(hiredate,'yyyymm') <='199909'; （用不上hiredate的索引）

select * from emp

where hiredate <= to_date('19990901','yyyymmdd') and

hiredate >= to_date('19821001','yyyymmdd');（能用上hiredate的索引）

3、使用不等于操作符（<>,!=）(用or语法替代不等号进行查询)

4、使用is null或is not null (建议将需要索引的列定义为非空)

5、查询中数据类型需要相同

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)

索引使用规则：

（1）能用唯一索引，一定用唯一索引

（2）能加非空，就加非空约束

（3）一定要统计表的信息，索引的信息，柱状图的信息。

（4）联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面

（5）只有做到以上四点，数据库才会正确的选择执行计划。

视图

一个虚拟表，其内容由查询定义；不在数据库中以物理存储的形式存在

优点：为用户集中数据；掩盖数据库复杂性；简化用户权限的管理；提高性能；组织数据以便于导出到其他应用程序

创建好的视图，可以象普通表一样使用

对视图修改将修改基表

视图更新规则：

只能对一个表进行修改

如果视图包含了聚合函数或者group by子句，那么视图就是不可更新的

如果视图包含了with check option ，那么，对视图的insert和update操作就必须满足where子句中条件的要求

Eg:

create view view_cj

select xs.xs_id,xs.name as xsname,kc.kc_id,kc.name as kcname,cj.fs

from xsb xs inner join cjb cj

on xs.xs_id=cj.xs_id

inner join kcb kc

on cj.kc_id=kc.kc_id

where cj.fs>80

存储过程

要考虑使用存储过程：（使用存储过程会降低系统的移植性）

当一个事务涉及到多个SQL语句或者涉及到多个表的操作时

当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）

比较复杂的统计和汇总

存储过程的威力和优势：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

优点：
1.速度快。尤其对于较为复杂的逻辑，减少了网络流量之间的消耗
2.写程序简单，采用存储过程调用类，调用任何存储过程都只要1-2行代码。

3.升级、维护方便
4.调试其实也并不麻烦，可以用查询分析器
5.如果把所有的数据逻辑都放在存储过程中，那么asp.net只需要负责界面的显示阿什么的，出错的可能性最大就是在存储过程。我碰到的就一般是这种情况。

缺点：
1.可移植性差
2.采用存储过程调用类，需要进行两次调用操作，一次是从sql server中取到过程的参数信息，并且建立参数；第二次才是调用这个过程。多了一次消耗。

创建存储过程：

create procedure pro_query_cj

select xs.name as xsname,sum(fs) as total

from cjb cj inner join xsb xs

on cj.xs_id=xs.xs_id

group by xs.name

order by sum(fs)

执行存储过程

EXECUTE pro_query_cj

create proc proc_GETfs

(

@xs_id int,--输入参数

@kc_id int,

@fs varchar(10) output --输出参数

)

select @fs=fs from cjb cj where cj.xs_id=@xs_id and cj.kc_id=@kc_id

declare @getfs int

exec proc_GETfs '2012001','3001',@getfs output

sql server 中 case when then else end 的用法（条件判断语法）

//显示性别信息

select xs_id,name,age,

case sex

when 1 then '男'

else '女'

end as sex

from xsb

//根据分数计算等级

select xs.xs_id,xs.name as xsname,kc.name as kcname,cj.fs,

case

when cj.fs>60 then '合格'

else '不合格'

end

as dj

from xsb xs inner join cjb cj

on xs.xs_id=cj.xs_id

inner join kcb kc

on cj.kc_id=kc.kc_id

常用函数

GETDATE( ) sysdate

DATEADD( )--向指定日期添加一段时间间隔

DATEDIFF( )--计算指定的两个日期的时间差的日期部分

CONVERT( )与 CAST( )

CONVERT(转换后的数据类型,需转换的数值,转换格式)

CAST(需转换的数值 AS 转换后的数据类型)

数值函数

Cell(n) 小数部分四舍五入后的整数

Floor(n) 小树部分截去后的整数

Mod(m,n) m整除n后的余数

Power(m,n) m的n次幂

Sqrt(n) n的算术平方根

Round(m,n) 对m的第n位小数后的四舍五入

Trunc(m,n) 对m的第n位小数后截去

字符串函数

Length(s) s的长度

Insrt(s1,s2,n,m) s1的n位开始查s2第m次出现的位置

Substr(s,n,m) 取s的n位开始的m个字符

Initcap(s) s的单词首字母大写

Upper(s) s全部大写

Lower(s) s全部小写

Concat(s1,s2) s1与s2合起来

用户定义函数

用户定义函数采用零或多个输入参数并返回标量值或表

与存储过程的区别

UDF可以放在SELECT语句中使用，存储过程不能

UDF可以返回结果集，存储过程不能

存储过程可以使用非确定性系统函数，UDF不能

语法

CREATE FUNCTION 函数名(参数列表)

RETURNS 数据类型 | TABLE

BEGIN

函数体

RETURN 返回值|表

END

游标

①从表中检索出结果集，从中每次指向一条记录进行交互的机制。

②关系数据库中的操作是在完整的行集合上执行的。
由SELECT 语句返回的行集合包括满足该语句的WHERE 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。

游标有什么作用？
①指定结果集中特定行的位置。
②基于当前的结果集位置检索一行或连续的几行。
③在结果集的当前位置修改行中的数据。
④对其他用户所做的数据更改定义不同的敏感性级别。
⑤可以以编程的方式访问数据库。

为什么避免使用游标？
①在创建游标时，最需要考虑的事情是，“是否有办法避免使用游标？”
因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；
如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

4，Oracle游标的类型？
①静态游标：结果集已经确实（静态定义）的游标。分为隐式和显示游标。
⑴隐式游标：所有DML语句为隐式游标，通过隐式游标属性可以获取SQL语句信息。
⑵显示游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。
②REF游标：动态关联结果集的临时对象。

5，Oracle游标的状态有哪些，怎么使用游标属性？
①游标的状态是通过属性来表示。
%Found ：Fetch语句（获取记录）执行情况True or False。
%NotFound : 最后一条记录是否提取出True or False。
%ISOpen ：游标是否打开True or False。
%RowCount ：游标当前提取的行数。
②使用游标的属性。

1、申明游标

cursor mycur(id varchar(50)) is

select empno,ename from emp

where ename like id||'%'

2、打开游标

Open mycur('Mar')

3、读取数据

fetch mycur into varno, varprice;

4、关闭游标

Close mycur;

Eg:

declare

empsal emp.sal%type :=0; --定义与表字段相同类型

cursor mycursor is --定义游标

select * from emp

where deptno = '10';

my_record mycursor%rowtype; --定义游标记录类型

begin

open mycursor; --打开游标

if mycursor%isopen then --判断打开成功

loop --循环获取记录集

fetch mycursor into my_record; --获取游标中的记录

if mycursor%found then --游标的found属性判断是否有记录

dbms_output.put_line(y_record.ename||'#'||my_record.sal);

else

exit;

end if;

end loop;

else

dbms_output.put_line('游标没有打开');

end if;

close mycursor;

end;

oracle存储过程

1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...

3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;

4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

5.变量赋值
V_TEST := 123;

6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;

7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;

8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

触发器

编写触发器时，需要注意以下几点：

l 触发器不接受参数。

l 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l 在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l 触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l 在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

SQL优化原则

尽量减少不必要的大表全表扫描

尽量建好和使用好索引，但也不是索引越多越好

增加SQL语句的重用率，减少硬解析

尽可能避免排序，尽可能在内存中排序

逻辑运算符有"NOT"、"AND"、"OR"三个，优先顺序是：()>NOT>AND>OR

Between a and b 从a到b的范围内（包括a和b）

Not between a and b 从a到b的范围之外（不包括a和b）

会员力量，点亮园子希望

刷新页面返回顶部

颜熙

Oracle 基础学习笔记

About