ORACLE笔记

SQL-----------关系数据库的基本操作语言，是应用程序与数据库进行交互操作的接口

 

DESC(describe) 表----查看某表的结构

 

SQL包括：

数据查询语言（SELECT）

数据操纵语言DML（INSERT，UPDATE，DELETE）--------用于操纵表和视图的数据

事务控制语言（COMMIT，ROLLBACK，SAVEPOINT）

数据定义语言（CREATE，ALTER，DROP）

数据控制语言（GRANT，REVOKE）

 

SELECT注意：

1.SELECT出来的日期默认显示格式为"DD-MM-YY",若要显示"YYYY-MM-DD"格式的字段内容,则在SELECT后要查询的字段使用TO_CHAR函数---------TO_CHAR(date字段,"YYYY-MM-DD")

2.DISTINCT(取消重复行)(distinct截然不同的意思)

   默认的SELECT查询是保留重复行的,若想取消重复行则使用SELECT DISTINCT +字段名

3.给列取别名时，AS可用可省略，若别名中包含特殊字符或空格，则必须要用“”把别名引起来

4.NULL----表示未知值，即不是空格也不是0.若插入数据时没有给某列提供数据且该列没有默认值，则其值为NULL，算数表达式中若包含NULL其结果也为NULL

若想将值为NULL(值为NULL的列内容不显示数据)的数据有所显示，可使用NVL或NVL2函数

NVL（X，0）即当X为NULL时显示为0，若不为NULL时显示X（即正常显示原值）

NVL2（X，e1，e2）即当X不为NULL时显示e1（即正常时），为NULL时显示e2

5.连接字符串-------用||

即在SELECT后面，将要查询的字段名及一些字符串连接起来，显示的时候会显示查询出的字段值及所连接的字符串结合的整个字符串，注意：使用||连接数字直接加上数字即可,若连接字符串和日期则要使用单引号''引起来

6.在WHERE条件子句中可使用UPPER或LOWER来避免由于查询条件的大小写不匹配引起的问题

如WHERE LOWER(name)='gaga'

7.在WHERE条件中使用日期值----------------WHERE date>'日期常量'(要使用单引号)

注：日期值必须要符合默认的日期显示格式，否则要使用TO_DATE函数进行转换

如：符合默认日期显示格式------WHERE date>'01-1月-10'

      不符合默认日期显示格式----WHERE date>TO_DATE('2010-01-01','YYYY-MM-DD')

8.模糊查询LIKE--------------

通配符%:表示0个或多个字符

          _:表示1个字符

注：若字符串原值本身包含%或_这两个特殊字符，则要在ESCAPE后使用转义字符(oracle中默认\为转义字符)

如：WHERE name LIKE '%a_%' ESCAPE 'a'

查询姓名中包含_字符的记录,ESCAPE后的字符a为转义字符

9.IS (NOT )NULL-----判断是否为NULL,不要使用=NULL,如WHERE name=NULL,则WHERE条件句不管什么情况都返回false

10.查询结果显示的顺序默认是按照数据插入的先后顺序来显示，ORDER BY x ASC|DESC用于排序,默认是ASC升序排列

当包含有GROUP BY，WHERE，ORDER BY多个子句时，ORDER BY必须在最后

注：升序排列（ASC）--------排序列值为NULL的记录显示在最后面，（即视NULL为无限大）

      降序排列（DESC）------排序列值为NULL的记录显示在最前面，（即视NULL为无限大）

 

INSERT注意：

1.INSERT插入日期值

插入日期值时，日期值(日期常量,用''括起)必须符合默认日期格式、日期语言.若用习惯方式(即YYYY-MM-DD格式)插入日期数据,则使用TO_DATE函数进行转换,例:

INSERT INTO TABLE1 (id,date) VALUES ('1',TO_DATE('2012-12-21','YYYY-MM-DD'))

2.INSERT插入数据时可使用DEFAULT关键字,如：INSERT INTO T1 (id,name) VALUES ('1',DEFAULT)

****即当name列有默认值则使用默认值,若没有则自动使用NULL（*****等同于不给该列提供数据？？？）

3.使用子查询插入数据------INSERT INTO TABLE [(field1,field2,....)] +子查询语句(即SELECT)

将INSERT列的数据类型和个数必须要与子查询列的数据类型和个数完全匹配.

例:

//使用子查询插入数据

INSERT INTO employee (id,name) SELECT id,name FROM emp

//使用子查询执行直接装载

INSERT /*+APPEND*/ INTO employee (id,name) SELECT id,name FROM emp

以上两种方式执行结果一样,但方式2使用/*+APPEND*/表示采用直接装载方式，当要装载大批量数据时速度要远远优于方式1

4.使用多表插入数据------将子查询或某个表的数据同时插入到多张表中

使用INSERT ALL/FIRST************************待整理

 

 

UPDATE注意：

1.更新日期列数据，日期格式必须与默认日期格式、日期语言相匹配。如果使用习惯格式(YYYY-MM-DD)修改日期数据，则需要使用TO_DATE函数进行转换,如UPDATE table1 SET date=TO_DATE('2012-12-21','YYYY-MM-DD')

2.使用子查询更新数据(有时可减少网络开销,比如多次对数据库的操作可由此变为对数据库的一次操作)

比如：希望使员工A的岗位、工资、补助与员工B的完全相同------

原始做法：先查询出员工B的信息：SELECT job,sal,comm FROM emp WHERE name='B'(查询结果为x,y,z)

               然后修改员工A的信息与B相同:UPDATE emp SET job='x',sal='y',comm='z' WHERE name='A'

使用子查询更新的数据的做法：

UPDATE emp SET (job,sal,comm)=(SELECT job,sal,comm FROM emp WHERE  name='B')WHERE name='A'

3.复制表数据*******待整理，触发器****？？？

 

DELETE注意：

1.删除表中所有的数据：DELETE FROM table1(不释放表的空间,可回退)

   截断表：                  TRUNCATE table1 (速度更快,释放表的空间,不可回退)

DELETE删除表中所有的数据，但不释放表所占用的空间；TRUNCATE不仅删除表中所有数据，还会释放表占用的空间
DELETE操作可回退，TRUNCATE不可回退

2.使用子查询删除数据(即使用子查询作为删除条件)

删除/解雇SALES部门所有的员工：DELETE FROM emp WHERE deptno=

                                                                            (SELECT deptno FROM dept WHERE dname='SALES')

3.删除主表数据的注意事项：当删除主表数据时，必须确保从表不存在相关记录。

 

 

使用事务控制语句-----事务---用于确保数据库数据的一致性，它由一组相关的DML语句组成。(如:INSERT,DELETE,

                                        UPDATE或SELECT...FOR UPDATE)

                                        该组DML语句所执行的操作要么全部成功，要么全部取消。

                                        (比如帐户转帐,两帐户金额一增一减两个操作要么全部执行,要么全都不执行)

                       在应用程序中当执行第一条SQL语句时，开始事务；当执行COMMIT或ROLLBACK时结束事务

 

1.当执行事务操作(DML语句)时,Oracle会在被作用表上加表锁,以防止其他会话更改表的结构;同时会在被作用行上加行锁,

以防止其他事务在相应行上执行DML操作

2.为确保数据库数据的读一致性,不允许其他会话读取脏数据(即事务还未提交的数据)

例事务A修改了一数据D,在事务A提交之前,另一事务B访问数据D时,获得的是事务A修改数据D之前的数据

(在事务A修改数据D后事务提交之前,修改后的数据D为脏数据,其他会话是不充许读取的)

3.提交事务代表着事务结束、删除保存点、释放锁.当下列情况出现时会自动提交事务：

                                   1)当执行DDL(Data Definition Language)语句时,如CREATE TABLE,ALTER TABLE,DROP

                                   2)当执行DCL(Data Control Language)语句时,如GRANT,REVOKE.

                                   3)当退出SQL*Plus时*****???

4.保存点:事务中的一点,当ROLLBACK时,通过指定保存点可以回退部分事务

设置保存点：savepoint a;(使用SQL命令)

               或exec dbms_transaction.savepoint('a') (使用包DBMS_TRANSACTION的过程SAVEPOINT设保存点)

 回退事务:    rollback to a;(使用rollback命令,回退部分事务)

               或exec dbms_transaction.rollback_savepoint('a')(使用包DBMS_TRANSACTION的过程rollback_...)

                  rollback;        (使用rollback命令,回退全部事务)

               或exec dbms_transaction.rollback (使用包DBMS_TRANSACTION的过程ROLLBACK)

5.当出现系统灾难时会自动回退其事务变化

6.只读事务----只允许查询操作,不允许执行任何DML操作的事务.----使用只读事务可取得特定时间点的数据,如:

在特定时间设置只读事务,即不允许DML操作,因此不会担心该特定时间之后的DML操作对数据查询造成影响

设置只读事务:SET TRANSACTION READ ONLY

                或exec dbms_transaction.read_only(使用过程READ_ONLY设置只读事务)

注意:设置只读事务时,该语句必须是事务开始的第一条语句

7.顺序事务----可以使用户取得特定时间点的数据,且允许执行DML操作.

会话A在时间点1设置顺序事务:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE(必须为事务开始的第一条)

会话B在时间点2更改数据:

会话A在时间点3查询被更改的数据:获得的是时间点1的数据,而不是时间点2更改后的数据(注意但是更改操作已经执行)

 

 

数据分组(分组函数,GROUP BY,HAVING共同实现)

1.分组函数(多行函数)----与单行函数不同,分组函数作用于多行,并返回一个结果.以下是最常用的7个分组函数

  MAX,MIN,AVG,SUM,COUNT,VARIANCE(方差),STDDEV(标准方差)

2.分组函数只能出现在选择列表(用来显示统计的数据),HAVING子句(统计数据用来作为筛选条件),ORDER BY子句(在此

   不作为统计的结果,而是作为列名,如avg(sal),指按该列排序)

3.使用分组函数时,除了COUNT(*)之外,其它分组函数都会忽略NULL行,COUNT(列名)也忽略NULL行

   即COUNT(name)指表中name列的值不为NULL的个数

4.当执行SELECT语句时,如果选择列表同时包含列、表达式和分组函数,则这些列和表达式必须出现在GROUP BY子句中

5.分组函数中可指定ALL/DISTINCT选项,默认为ALL即包括重复项;DISTINCT只会统计不同行值

   如统计员工表中所有员工不同的姓名的个数:

   SELECT count(distinct name) FROM emp

   等同于:SELECT count(*) FROM (SELECT DISTINCT name FROM emp)

6.GROUP BY进行多列分组,例:员工表emp中有岗位(job),部门编号(deptno),工资(sal)等字段,若想显示每个部门每个岗

                                          的平均工资和最高工资

                                      SELECT deptno,job,avg(sal),max(sal) FROM emp GROUP BY deptno,job

                                      (每个部门有多个岗位,这样每个部门的每个岗位为一条记录)

7.使用HAVING子句限制分组显示结果

   例:SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)<2000

8.使用GROUP BY子句统计数据时,会自动按照分组列的升序方式显示统计结果,通过ORDER BY子句可改变数据分组的顺序

   例:SELECT deptno,avg(sal) FROM emp GROUP BY deptno ORDER BY avg(sal) (此处avg(sal)相当于列名)

9.若SELECT子句中同时包含GROUP BY,HAVING,ORDER BY子句,则必须将ORDER BY子句放在最后.

10.ROLLUP/CUBE操作符---产生横向/纵向的统计小计