ORACLE数据库基础

PL/SQL总结

这段时间在公司里学习了一下数据库编程，感觉收获挺大的，就我以前写的那些SQL语言，有时候自认为还不错，可是当数据库的数据量达到一定量的时候就完全不行了。现想把学到的一些东西拿出来分享一下，为了进成一个完整的数据库资料，我想还是从基础的说起吧：

一：关系数据库的标准语言

    1 . DDL语言： create、drop、alter、truncate

    2 . DML语言：insert、update、delete

    3 . 数据查询语言：select

    4 . 数据控制语言：grant、revoke

       下面就这些语句用一个例子来说明一下如何应用：

创建表：

CREATE TABLE TEST (    T_ID VARCHAR2(16) NOT NULL,    T_NAME VARCHAR2(32),    T_STATUS VARCHAR2(1),    CONSTRAINT PK_TEST PRIMARY KEY(T_ID) );

 

往表里添加数据：

       插入多条记录：

INSERT INTO TEST(T_ID,T_NAME,T_STATUS) SELECT (FILED1,FILED2,FILED3) FROM OTHER_TABLE

 

       插入一条记录：

INSERT INTO TEST(T_ID,T_NAME,T_STATUS)    VALUES(FILED1,FILED2,FILED3);

 

创建表时添加数据：

CREATE TABLE TEST_TEMP AS    SELECT (FILED1,FILED2,FILED3) FROM OTHER_TABLE

NOTE:上面的方式在建立临时表的时候比较常用，他是根据后面的数据类型来生成表。

 

修改表：

       修改表属性：

              修改表列名：

ALTER TABLE TEST RENAME T_STATUS TO T_S;

 

              修改表列类型：

ALTER TABLE TEST MODIFY T_ID TO NUMBER;

 

              增加表列：

ALTER TABLE TEST ADD T_AGE NUMBER;

 

              修改表数据：

UPDATE TEST SET T_NAME=’user1’ WHRER T_ID=1;

NOTE:ORACLE对DML语言对数据库进行操作的时候都加上了行级锁，只有在当前事务COMMIT或者ROLLBACK后才会解锁。

删除表：

       删除表列：

ALTER TABLE TEST DROP COLUMN T_AGE;

 

       删除表：

DROP TABLE TEST;

 

       删除数据：

DELETE TEST WHERE T_ID=1; --删除表中的一条数据

DELETE TEST ;--删除表中的所有数据(DML方式)

TRUNCATE TABLE TEST; --删除表中的所有数据(DDL方式)

 

NOTE：可能你已经注意到了，上面的删除所有数据里用了两种方法：DELETE和TRUNCAE两种方式，那么这两种方式有什么区别呢？其实很简单，我在上面介绍语言分类的时候已经说了，DELETE是DML语言，而TRUNCATE则是DDL语言，很显然用TRUNCATE即DDL语言操作数据库要快得多，这对于数据量越大越明显。（至于在这里快的很大一部分原因我想可能是DML在进行数据库操作的时候有大量的日志记录吧）

 

索引：

       索引是一种加快查询速度的有效手段，在一张表中可以建立一个或者多个索引，但是索引也不能创建过多，因为索引也是要空间的，如果数据里很大，并且建立了很索引的话就会占用很多存储空间，这样的话会严重影响数据的插入速度。

       创建索引：

CREATE INDEX TEST_INDEX_ID ON TEST(T_ID);

       删除索引：

DROP INDEX TEST_INDEX_ID;

 

查询语言：

       笛卡尔积：

       两张表的简单关联，如果没有加任务条件的话就会得到一个笛卡尔积数据，这样得到的数据记录条数年就会是两张表的数据量的乘积。比如：

SELECT TEST.T_NAME,TEST_TEMP.T_NAME     FROM TEST,TEST_TEMP

       但是如果在上面的SQL语句中加上WHERE语句进行限定的话就会根据条件查询出结果，不会产生笛卡尔积数据，在查询的时候得注意一下这个问题。

       外关联、内关联：在两张表关联查询的时候可以用左关联或右关联，即是标量中的外关联或内关联，这样查询出来的数据会以其中的一张表为基表，然后再去查另一张表，这样基表中的数据不会少，如果在另一张表中没有数据的时候会填上该数据类型下的默认值，如（VARCHAR2为NULL，NUMBER为0等）。书写的时候有两种方式：一种是在条件判断的时候在一边加上（+）号，表示以加号对面的那个字段中的表为基表；二种方法则是比较传统的写法，即是：LEFT JOIN …ON…/RIGHT JOIN…ON。

       简单集合函数：COUNT/AVG/SUM/MAX/MIN

       嵌套SQL语句：SQL语言是可以嵌套使用的，记住：任何返回结果集的SQL都可以当成一张表来来使用。比如说：

SELECT T.T_ID FROM     (SELECT T_ID FROM TEST)T;--自身嵌套

       条件判断：CASE WHEN …   例如：

SELECT ID，MAJOR，CURRENT_CREDITS,          CASE          WHEN CURRENT_CREDITS > 90 THEN ‘A’          WHEN CURRENT_CREDITS > 80 THEN ‘B’          WHEN CURRENT_CREDITS > 70 THEN ‘C’          ELSE ‘D’ END GRADE          FROM STUDENTS

 

       DECODE：可实现类似于CASE WHEN的逻辑,语法为：

decode(colum_name,comparison1,action1,comparison2,action2,…else

action);

Decode语句把colum_name的内容与每一个comparison进行比较，如果结

果相等，decode执行action动作。如果没有任何一项与comparison匹配，

程序就执行else action动作。

注意：DECODE 只能进行等值判断。下面是例子：

select id, major, current_credits ,          decode(current_credits,98,’a’,88,’b’,75,’c’,66,’d’) grade          from students

    另外，关于横表转纵表用UNION,纵表转横表用DECODE。

      

分析函数：分析函数是ORACLE提供的一些对数据进行统计的函数，下面就介绍一些常用的函数：

       rollup：比直接用group by 执行速度要快得多，用法就是在group by 关键了后面直接跟rollup再加要分组的字段。比如：

group by rollup(area_code)

 

       cube:这个分析函数与rollup的功能基本相同，当然了，用法上也是一样的，但是这个函数能够对多个字段进行数据汇总，换句话说就是在rollup函数中汇总的字段如果为多个的话，它只会对第一个字段进行数据汇总，而忽略其他的字段，但是cube是汇总所有的字段，那么查询出来的记录行数就可以想得到谁多谁少了，当然是cube返回的记录条数大于等于rollup返回的记录条数了。比如：

group by cube(area_code,bill_month)

NOTE：在上面两个函数里，如果是对数据汇总的那条记录里，它是根据哪个字段来进行汇总的就在那条记录的那个字段那里显示为null，为了解决这个问题，又得引进另外一个函数，grouping函数，它专门是为了解决这一问题而产生的，下面是它的用法：

select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code, decode(grouping(bill_month),1,'all month',bill_month) bill_month,     sum(local_fare) local_fare     from t    group by cube(area_code,bill_month)    order by area_code,bill_month

 

       rank：紧跟着下面的三个函数的主要功能就是对一个数据进行汇总之后进行一个排名。比如下面这个例子：

select area_code,sum(local_fare) local_fare, rank() over (order by sum(local_fare) desc) fare_rank  from t  group by area_codee

这个函数返回的记录的汇总排名是：如果数据相同的话，则排名是一样的，但总的排名数据不变，是什么意思呢，就下面这种数据：1，2，2，4，5。

       dense_rank:作用和用法都同rank函数，不同的是返回值不一样，这个函数对数据相同的排名也是一样的，但是不会出现跨越，比如：1，2，2，3，4。

       row_number:当然了，这个函数跟上面两个函数差不多，不同的也是返回的排名值，这个数返回的就是一个自然顺序，即使数据是一样的，也不会有重复的排名，对重复字段的处理它是一个随机的排名，比如返回的排名始终是这样的：1，2，3，4，5。

       NOTE:上面这三个排名函数同rank分析函数结合使用就能实现对所有记录中的数据分组进行排名，即是先指定分组，然后在分组中的数据进行排名，这种方法比较常用。比如说要查询出每个月份畅销产品的排名。

       sum:移动计算函数，它算出的是连接的数据，比如说可以是连续的几个月的和，但是一定要注意是有数据的，如果上个月没有数据，就会再往上调，即时是说如果说你想求出前三个月的数据和，你就得保证前三个月有数据，如果没有的话就用零来填充，否则就不对了。下面是一个例子：

select area_code,bill_month, local_fare,   sum(local_fare)              over (  partition by area_code                     order by to_number(bill_month)                       range between 1 preceding and 1 following ) "3month_sum", from (      select area_code,bill_month,sum(local_fare) local_fare     from t      group by area_code,bill_month )

 

       avg:作用和用法同上，只不过这个函数是求平均值，上面是求和。不再进行说明。

       ratio_to_report:计算数据所占的百分比。下面这个例子：

select bill_month,area_code,sum(local_fare) local_fare,        ratio_to_report(sum(local_fare)) over         ( partition by bill_month ) area_pct     from t    group by bill_month,area_code

 

 

二：PL/SQL编程（后续）