ORACLE数据库技术小发现（一）

1.DESCRIBE 

2.用“select name from v$database;”的name就是主机字符串；

3.在SQL中,DELETE语句与DROP TABLE的区别

DELETE  是有条件的删除表中的数据，如果不写条件，全表删除，而删除后，表仍然存在，仍可以对表进行操作。具体语法为DELETE FROM TABLE WHERE 条件。
同样，还有一个TRUNCATE TABLE TABLENAME语句，是全表不提交删除，也是删除数据，表仍然存在。

DROP TABLE，是从数据库中删除表。删除后，表结构不在存在，无法再对该表进行任何操作。

举个通俗的例子，比如你有一个水桶，桶里装着水。
而DELETE或者TRUNCATE，只是相当于把水倒掉，而桶仍然存在。
而DROP TABLE，则相当于把桶都扔掉了

truncate,delete,drop的异同点

注意:这里说的delete是指不带where子句的delete语句    
       
  相同点:truncate和不带where子句的delete,   以及drop都会删除表内的数据      
  不同点:      
  1.   truncate和delete只删除数据不删除表的结构(定义)       
       drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态.      
  2.   delete语句是dml,这个操作会放到rollback   segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.       
        truncate,drop是ddl,操作立即生效,原数据不放到rollback   segment中,不能回滚.   操作不触发trigger.      
  3.    delete语句不影响表所占用的extent,高水线(high   watermark)保持原位置不动，DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项；      
        drop语句显然将表所占用的空间全部释放      
        truncate语句缺省情况下将空间释放到   minextents个   extent,除非使用reuse   storage;      truncate会将高水线复位(回到最开始).      

但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。

TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。

    新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

    TRUNCATE TABLE 不能用于参与了索引视图的表。

  4.    速度,一般来说:   drop>   truncate   >   delete      
  5.    安全性:小心使用drop   和truncate,尤其没有备份的时候.否则哭都来不及   

  6.    DELETE可以返回被删除的记录数，而TRUNCATE TABLE返回的是0。

  7.    DELETE可以加where语句，而TRUNCATE TABLE不可加where语句。    
  使用上,想删除部分数据行用delete,注意带上where子句.   回滚段要足够大.       
  想删除表,当然用drop      
  想保留表而将所有数据删除.   如果和事务无关,用truncate即可.   如果和事务有关,或者想触发trigger,还是用delete.      
  如果是整理表内部的碎片,可以用truncate跟上reuse   stroage,再重新导入/插入数据。

4.Oracle to_char的时间数据格式转换

1)以12小时制显示时间

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH12:MI:SS AM')FROM DUAL;

2)以24小时制显示时间

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM')FROM DUAL;

5.DISTINCT消除重复项

SELECT 指令让我们能够读取表格中一个或数个栏位的所有资料。 这将把所有的资料都抓出，无论资料值有无重复。在资料处理中，我们会经常碰到需要找出表格内的不同 资料值的情况。换句话说，我们需要知道这个表格/栏位内有哪些不同的值，而每个值出现的次数并不重要。这要如何达成呢？在 SQL 中，这是很容易做到的。我们只要在 SELECT 后加上一个 DISTINCT 就可以了。DISTINCT 的语法如下：

SELECT DISTINCT "栏位名" 

FROM "表格名"

举例来说，若要在以下的表格，Store_Information，找出所有不同的店名时，

Store_Information表格

store_name Sales Date

Los Angeles $1500 Jan-05-1999

San Diego $250 Jan-07-1999

Los Angeles $300 Jan-08-1999

Boston $700 Jan-08-1999

我们就打入，

SELECT DISTINCT store_name FROM Store_Information

结果:

store_name

Los Angeles

San Diego

Boston

6.“||”操作符实现字符串连接

7.ORACLE空值处理

NVL(expr1,expr2):如果expr1为空值转换成expr2的值；(第一个和第二个参数需要类型一致)

例如：

column name：a1 type：varchar2（10）

NVL(a1,'is null')

NVL2(expr1,expr2,expr3):如果expr1为非空值，转换成expr2;若为空值，转换成expr3;

NULLIF(expr1,expr2):将两个表达式进行比较，如果相等，则返回空值，如果不相等，则返回第一个表达式；

COALESCE(expr1,expr2.....,exprn):返回第一个非空表达式；

update 语句中将某列设置成null，列名=null

例如：update employees set first_name = null where ....

8.Oracle中where子句和having子句中的区别

一、聚合函数 

聚合函数有时候也叫统计函数，它们的作用通常是对一组数据的统计，比如说求最大值，最小值，总数，平均值如MAX,MIN，COUNT, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。简单举个例子：SELECT SUM(sal) FROM emp，这里的SUM作用是统计emp表中sal(工资)字段的总和，结果就是该查询只返回一个结果，即工资总和。通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。

二、where子句 

where自居仅仅用于从from子句中返回的值，from子句返回的每一行数据都会用where子句中的条件进行判断筛选。where子句中允许使用比较运算符(>,<,>=,<=,<>,!=|等)和逻辑运算符(and，or，not)。

三、having子句 

having的作用是对使用group by进行分组统计后的结果进行进一步的筛选。现在需要找到部门工资总和大于10000的部门编号

第一步：select deptno,sum(sal) from emp group by deptno;

第二步：select deptno,sum(sal) from emp group by deptno having sum(sal)>10000;

四、where子句和having子句对比

在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行，简单的理解为只有有了统计结果后我才能执行筛选啊。where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)，因为它是一句一句筛选的。HAVING子句可以让我们筛选成组后的对各组数据筛选，而WHERE子句在聚合前先筛选记录。现查询部门号不等于10的部门并且工资总和大于8000的部门编号。分析为通过where子句筛选出部门编号不为10的部门，然后在对部门工资进行统计，然后再使用having子句对统计结果进行筛选。

select deptno,sum(sal) from emp where deptno!='10' group by deptno having sum(sal) > 8000;

五、总结 

相似之处就是定义搜索条件。不同之处是where子句为单个筛选，having子句与组有关而不是与单个的行有关。

①where不能放在GROUP BY 后面

②HAVING是跟GROUP BY连在一起用的，放在GROUP BY后面，此时的作用相当于WHERE

③WHERE 后面的条件中不能有聚集函数如SUM(),AVG()等而HAVING可以