DB DAY DAY UP

 

数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点.

-------------------------------------------------------------------------------------------------------------

Oracle中SQL语句中两个单引号之间的字符数不能大于4000.

Oracle表名称限制在30内。

-------------------------------------------------------------------------------------------------------------

UNION 与 UNION ALL 不同，UNION 会去掉结果集中的重复记录。

UNION因为要去掉重复的记录，会对结果集排序而用到零时表空间。

-------------------------------------------------------------------------------------------------------------

where与having的区别

where条件作用于分组前的每一条记录，having条件作用于分好组后的组。

SELECT<attributes and function lists>

FROM<Table list>

[WHERE <condition>

GROUP BY <grouping attributes>

HAVING <group condition>

ORDER BY<attribute list>]; 

-------------------------------------------------------------------------------------------------------------

对字段建立了索引，但不一定会被使用：
select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)
select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)

-------------------------------------------------------------------------------------------------------------

SQL语句最好用大写，因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

-------------------------------------------------------------------------------------------------------------

子查询不但可以出现在Where子句中，也可以出现在from子句中，作为一个临时表使用，也可以出现在select list中，作为一个字段值来返回。

-------------------------------------------------------------------------------------------------------------

利用create schema authorization “登录用户名” 把多个DDL “限定在一个”事务“中。

在创建系统的安装脚本时，经常创建一部分表后出现错误，退出安装脚本。这个时候一般要先清除脚本，把原来创建的表先删除。如果使用Create Schema语句，当后面的语句失败了，前面的语句创建的对象或授权自动取消，就不必再执行清除操作。

CREATE SCHEMA AUTHORIZATION yorker
create table yorker.aa(id int)
create table yorker.bb (id int);

注意只在最后一句加分号，如果创建bb时候失败(如把int写成了int2)，aa表也不会被创建。

目前,只有CREATE TABLE,CREATE VIEW,CREATE INDEX,CREATE SEQUENCE,CREATE TRIGGER和GRANT是在CREATE SCHEMA

里面可以接受的子句。

-------------------------------------------------------------------------------------------------------------

高效的删除重复记录方法，只保留一条 (使用ROWID)例子：

DELETE  FROM  BB E  WHERE  E.ROWID >
(SELECT MIN(X.ROWID) FROM  BB X  WHERE  X.ID = E.ID);

-------------------------------------------------------------------------------------------------------------

如何取top n记录,(使用rownum)

SELECT * FROM (SELECT id from bb order by id) where rownum <3;

如果top 3有4条记录怎么办呢?

SELECT * FROM ( SELECT test.*,RANK() OVER (ORDER BY id DESC) ranknum from test) where ranknum <3;

[这种取法只能用于取前多少条或者第一条,如第一条(=1或者<=2),前两条(<3或者<=2),不支持第二条(=2),不支持第2条到第5条的取法(rownum>=2 and rownum<=5)]

取第M条到第N条之间的

select *
  from (select id,name,row_number() over(order by id) rn
          from user) t
where t.rn>=4 and t.rn <=6

SELECT *
  FROM (SELECT ROWNUM AS rownumber, t.*
          FROM (SELECT   ID
                    FROM TEST
                ORDER BY ID) t)
 WHERE rownumber > 3 AND rownumber <= 7;

删除M条到第N条之间的记录

delete from tb1 where rowid in
(SELECT rowid  FROM (SELECT rowid,id,VAL,row_number() over (order by VAL) rn from TB1 ) 

where rn >2 and rn<6);

-------------------------------------------------------------------------------------------------------------

NULL值对AVG和COUNT函数的影响：

AVG会把对应字段值为NULL的行排除在外，用 AVG(ISNULL(Col,合理值:如0代替))。

COUNT(Col) 会把对应字段值为NULL的行排除在外,COUNT(*)不会，即使该行所有的列的值都为NULL。

-------------------------------------------------------------------------------------------------------------

如下表中，对于相同的name，取time时间最新的记录

name time1   time2 
AA 2008-2-12 2008-2-13
AA 2008-2-12 2007-2-12
BB 2007-2-12 2006-2-12
BB 2008-2-12 2007-2-12
BB 2009-2-12 2008-1-12
取到：
AA 2008-2-12 2008-2-13
BB 2009-2-12 2008-1-12
AA里面2008-2-13是最新的
BB里面2009-2-12最新

 

select * from mytable1 outtb
where time1>= (select decode(sign(MAX(time1)-MAX(time2)),-1,MAX(time2),MAX(time1)) From mytable1 innertb  where outtb.name = innertb.name group by innertb.name )
or time2>= (select decode(sign(MAX(time1)-MAX(time2)),-1,MAX(time2),MAX(time1)) From mytable1 innertb  where outtb.name = innertb.name group by innertb.name )

-------------------------------------------------------------------------------------------------------------

表被drop后重建，需要重建与之关联的trigger。

视图被drop后重建，也需要重建与之关联的trigger。

-------------------------------------------------------------------------------------------------------------

如何取字符串中的每一个字符：connect by

selectsubstr(s, rownum,1)
from (select' 12345678 ' s fromdual)
connect byrownum<= length(s);

-------------------------------------------------------------------------------------------------------------

 

AUTHID CURRENT_USER子句不能应用于子程序的静态调用。因为程序的静态调用是在编译时就被解释的，而不是运行时。

-------------------------------------------------------------------------------------------------------------

DBMS_JOB里的方法调用后,要COMMIT才生效.

-------------------------------------------------------------------------------------------------------------

Oracle package的spec里定义的Function,Procedure在和Body里的实现进行一致性检查的时候,是逐字符进行比较的(空白字符不计),所以,形式参数的位置,命名也必须保持一致.

Package Body里的初始化代码只在第一次装载的时候调用一次,必须放在body的最后,以begin开头,没有end关键字.

BEGIN

-- initialization part starts here

 

-------------------------------------------------------------------------------------------------------------

where条件中不能用SELECT中出现的别名作为字段查询,必须用表的字段的真实名称.

如: select isbn as bookid from books where bookid ='1122' 是不合法的.

但是select * from(select id as myid from t) where myid=3;是合法的.

-------------------------------------------------------------------------------------------------------------

动态SQL里的数据库对象(如表)不能用绑定变量的方式执行,如:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE ’DROP TABLE :tab’ USING table_name; --不合法
END;

数据库对象(如表)只能用连接字符串的方式组成动态SQL,如:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE ’DROP TABLE ’ || table_name;
END;

-------------------------------------------------------------------------------------------------------------

将cursor的值fetch到用%rowtype定义的record的时候,select list的顺序必须和表的定义的字段顺序保持一致或者用

select * 来定义cursor.

-------------------------------------------------------------------------------------------------------------

当字段名和局部变量重名时,字段名的优先级更高,下面的例子会把emp表的数据全删掉.

DECLARE
ename VARCHAR2(10) := ’KING’;
BEGIN
DELETE FROM emp WHERE ename = ename;

一个更隐蔽的例子,tab中有val和val1字段.

DECLARE
val1 VARCHAR2(10) := 'b';
BEGIN
DELETE FROM tab WHERE val = val1;
end;

-------------------------------------------------------------------------------------------------------------

 

NULL and TRUE = NULL;

NULL or FALSE = NULL;

NOT　NULL = NULL;

DELETE FROM emp WHERE ename NOT IN (NULL, ’KING’);不会删除任何行. NOT IN (NULL...)总是FALSE

NOT的优先级比and和or高,如 NOT valid AND done = (NOT valid) AND done

-------------------------------------------------------------------------------------------------------------

付给用户的role权限需要用户从新登录才起作用.

-------------------------------------------------------------------------------------------------------------

游标变量(ref cursor)可以用open for打开多次,而静态游标在关闭前不能再被打开.

不能在包的声明中定义游标变量,游标变量类型不能作为集合元素的类型.

-------------------------------------------------------------------------------------------------------------

Oracle中,在更新同一行中的A字段,在还没有提交前,不能在另一个session中更新同一条记录的B字段.

-------------------------------------------------------------------------------------------------------------

用一条SQL更新满足条件记录的A为X值,B在不为B特殊值的时候为Y值:

update tb set A='X', B=decode(B,'sp_b',val,'Y') where .....

-------------------------------------------------------------------------------------------------------------

Oracle 表的外键可以为空.

-------------------------------------------------------------------------------------------------------------

sys用户密码丢失，1，用orapwd重建密码文件。2，修改sqlnet.ora为操作系统认证，登录后修改密码。

 

-------------------------------------------------------------------------------------------------------------

查看数据库当前字符集参数设置SELECT * FROM v$nls_parameters;或select * from nls_database_parameters
客户端 NLS_LANG 的设置方法 Windows:
# 常用中文字符集set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
# 常用unicode字符集set NLS_LANG=american_america.AL32UTF8
可以通过修改注册表键值永久设置HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOMExx/NLS_LANG

-------------------------------------------------------------------------------------------------------------

以一个表为基准更新另一个表：

UPDATE nadkey1
   SET nadkey = (SELECT nadkey2.nkn
                   FROM nadkey2
                  WHERE nadkey1.nadkey = nadkey2.nk0 and rownum=1)
 WHERE EXISTS (SELECT nkn
                 FROM nadkey2
                WHERE nadkey1.nadkey = nadkey2.nk0);

select name,sequence#,first_change#,next_change# from v$archived_log

select checkpoint_change# from v$database;

select checkpoint_change# from v$datafile;

select checkpoint_change# from v$datafile_header;

select group#,sequence#,first_change#,status from v$log;

insert into test values(dbms_flashback.get_system_change_number,'b')