ITPub博客

User Access and Security对象权限，系统权限，角色权限。Creating and Modifying User Accounts：Configuring Account Authentication：用户连接到数据库，必须做认证，一般有3种认证类型。1.数据库认证。Oracle检查用户是否合法，check用户名和密码。Database-authenticated user accounts默认账号类型。建立账号例子:Create user piyush IDENTIFIED BY welcome;2.oracle只check用户名的合法性，密码由系统和网络来检验。Externally authenticated user accounts这些账户一般称之为OPS$,例子：Create user ops$appl IDENTIFIED EXTERNAL

Other Database ObjectsCreating and Managing Sequences：序列值存在于数据字典中。Creating and Dropping Sequences：Create sequence schema..sequence 参数；参数：start with integer : 默认为1 Increment by integer ：每次增长或减少数，默认为1 Nominvalue Minvalue integer ： 产生最小的数字 NOMAXVALUE MAXVALUE interger ：最大的数字 NOCYCLE CYCLE NOCACHE CACHE ： 序列的size保存在内存中。默认为20.Drop sequence　sequence_nameUsing Sequences:得到序列的后一

Managing Views：Creating and Modifying Views：Create view view_name ASSelect column1,column2From tableWhere conditionUsing Defined Column Names:Create view view_name (column1,column2,…..)AS select xxxx From table Where condition.如果建立一个视图是使用select *的话，如果之后修改主表的话，必须重新建立视图。Creating Views with Errors：建立视图的时候，使用FORCE参数。（NO FORCE是默认的）Create force view view_name ASSelect *From table1即使没有table1，视图照样建立。但是进去视图的时候，会出错。

/* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-languag

/* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-languag

Simple Joins: 使用符号‘=’相连2个表。属于内连接（inner join），因为只返回满足连接条件的数据。Complex Joins:除了在where中使用join 条件，还可以在where中添加限制条件。注意：当alias name定义好之后，只能使用alias name。Using ANSI Syntax：<table name> NATURAL [INNER] JOIN <table name>这种自然连接，根据2个表中具有同样名称的column，来连接多个表。等同于使用符号‘=’，来连接表。注意不能使用qualify the column name。如果使用select * ，common columns只列出一个。<table name> [INNER] JOIN &l

Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan;

/* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-lang

st1:*{behavior:url(#ieooui) } /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; ms

st1:*{behavior:url(#ieooui) } /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-farea

/* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-lang

Single-Row Functions: /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;（事务级的）返回的数据总是最新的SET TRANSACTION ISOLATION LEVEL READ COMMITTED;（语句级的）所返回的数据是在语句执行时候，处理的数据。Check constraints cannot reference the SYSDATE function orother tables.[@more@]

[@more@]

相关子查询 1.执行步骤： （1）外部查询得到一条记录并将其传入到内部查询。 （2）内部查询基于传入的值执行。 （3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成其处理。 例如： SELECT department_id, last_name, salary FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department_id = e2.department_id) ORDER BY 1, 2, 3;DEPARTMENT_ID LAST_NAME SALARY------------- ------------------------- ----------10 Whalen 440020 Hartstein 1300030 Ra

AVG([{DISTINCT | ALL}] <n>) 默认是all CORR(<y> , <x>)返回一对表达式的相关系数 运算为：COVAR_POP(y,x) / (STDDEV_POP(y) * STDDEV_POP(x)). COUNT({* | [DISTINCT | ALL] <x>}),default is ALL COVAR_POP(<y> , <x>) 返回一对表达式的总体协方差 (SUM(y*x) - SUM(y) * SUM(x) / COUNT(x)) / COUNT(x) COVAR_SAMP(<y> , <x>)返回一对表达式的样本协方差 (SUM(y*x) - SUM(y) * SUM(x) / COUNT(x)) / (COUNT(x)-1) CUME_DIST(<val_list>) WITHIN GROUP (ORDER BY col_l

1、为什么要将表空间置于热备状态？这是因为OS在Copy Block时的无序性，也就是说在Copy数据文件的时候并不能保证最先Copy 出来的Block是Datafile header Block（因为Datafile header Block中记录着datafile 的SCN，恢复的时候就是从这个Block中记录的SCN开始）。例如：OS Copy 开始时Block 1 的时间为t1，Copy 过程中Block 1 可能被别的进程修改，Copy 完成之时的时间为t2，而此时开始Copy datafile Header block，时间为t3，这样在恢复的时候是从t3 开始恢复的，也就是说此时Block 1 将丢失t1到t3这段时间内的数据。所以说不置于热备状态

rollup,和 cube 的作用就是在汇聚函数的基础上进行超汇聚。 select *** from *** where *** group by A,rollup ( B );------在A被汇聚的基础上，在A中每个小类别中，对B进行超汇聚。（此时超汇聚B中无值）其实rollup就是归纳。 select *** from *** where *** group by rollup（ A , B );------同上，最后加上对A和B的总体归纳。 另一种情况：group by rollup（B,A）；----------------在B被汇聚的基础上，在B中的每个小类别中，对A进行超汇聚。最后加上对A和B的总体归纳。 select *** from *** where ***

SQL> select * from wltest;A B C---------- ---------- --------------------1 10 jiejie2 30 gege3 25 mama4 50 baba5 100 didi2 20 didi4 30 jiejie5 90 mama4 60 gege4 60 gege已选择10行。SQL> select a,cume_dist(50) within group(order by b desc) cum_50 from wltest group by a;A CUM_50---------- ----------1 .52 .3333333333 .54 .85 1 --------------------------------------------------- 1 50 1/2 1 102 50 1/3 2 302 203 50 1/2 3 254 604 604 504 50 4/5 4 305