------------------------2013-5-6------------------------
system/manager@ora1128 as sysdba

Connect system/****@ora1128 AS SYSDBA

start C:\script.sql;

desc scott.emp;

col LOC heading location;? --修改列的標題名稱

show all? --顯示當前環境變量的值

show error --顯示當前在創建函數、存儲過程、觸發器、包等對象的錯誤信息。

show rel --顯示數據庫版本
show sga --顯示SGA的大小
show user --顯示當前的用戶名

ora-01536: 超出表空間system的空間限量?? -->賦予dba角色可以解決問題。
系統權限????????????? 管理選項
create procedure????? ?x
create table??x
create tablespace?x
unlimited tablespace?x

角色??????? 管理選項? 默認值
connect???x? 勾選
resource??x? 勾選

show user;

set sqlprompt Oaichinese;

alter table newlifeyhj.students add(stu_xxx varchar(12));

DBMS_OUTPUT.put_line('=============================');?? 測試窗口，打印輸出。

聚合函數RANK 和 dense_rank 主要的功能是計算一組數值中的排序值。
select * from (select rank() over(partition by title order by fenshu desc) rk,newlifeyhj.a.* from newlifeyhj.a) t
where t.rk<=3;

dense_rank和rank區別：
dense_rank與rank()用法相當，但是有一個區別：dence_rank在并列關系時，相關等級不會跳過,rank則跳過!
比如:存在兩個第一名是,dence_rank函數接下來是第二名，而rank函數接下來是第三名!/

收入排行
select empno, sal, rank() over(order by sal desc) R from emp;
select empno,sal,dense_rank() over(order by sal desc) R from emp;

sql操作符
算術操作符，+ - * /
比較操作符，= != < > <= >= between and, in,like, is null
邏輯操作符，and,or,not
集合操作符，union,union all,intersect,minus
連接操作符，||

操作符優先級
算術操作符? 最高
連接操作符
比較操作符
邏輯操作符

not邏輯操作符
and邏輯操作符
or邏輯操作符??? 最低

sql函數
單行函數,每一行只返回一個值，日期函數，數字函數，字符函數，轉換函數，其他函數。
分組函數,一組行來返回結果，為每個組行返回一個值。
分析函數,一組行來計算聚合值，累計排名，移動平均數，為每組記錄返回多個行。

日期函數包括：
add_months
months_between
last_day
round
next_day
trunc
extract

字符函數
Initcap(char)
lower(char)
upper(char)
ltrim(char)
rtrim(char)
translate(char,from,to)
instr(char,m,n)
substr(char,m,n)
concat(expr1,expr2)
其他的字符函數
chr和ascii
lpad和rpad
trim
length
decode

490.?在ORACLE 中，語句Select decode(7782,7369,'test1',7782,'test2','other') from dual;查詢結
DECOD
-----
test2

數字函數
abs(n)?? 絕對值
ceil(n)? 比該值大的最小值
cos(n)?? 余弦
cosh(n)
floor(n)??? 比該值小的最大值
power(m,n)? m的n次方
mod(m,n)??? 求余數
round(m,n)? n位小數點，四舍五入。
trunc(m,n)? n位小數點，截斷，無四舍五入。
sqrt(n)???? 求開平方
sign(n)???? >0? 1,? =0? 0, <0? -1? 標記值。

轉換函數
to_char
to_date
to_number
轉換空值的函數
nvl
nvl2
nullif

分組函數
avg min max sum count? select count(distinct qty_hand) from itemfile;

group by和having
group by子句
用于將信息劃分為更小的組，每一組行返回針對該組的單個結果
having子句
用于指定group by子句檢索行的條件

分析函數 row_number,rank,dense_rank
row_number 返回連續的排位，不論值是否相等。
rank 具有相等值的行排位相同，序數隨后跳躍。
dense_rank 具有相等值的行排位相同，序號是連續的。

--返回連續的排位，不論值是否相等。
select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from scott.emp;
--按deptno分區排序
select ename,job,deptno,sal,rank() over(partition by deptno order by sal desc) as sal_rank from scott.emp;
--具有相等值的行排位相同，序數隨后跳躍。
select ename,job,deptno,sal,rank() over(order by sal desc) as sal_rank from scott.emp;
--按deptno分區排序
select ename,job,deptno,sal,dense_rank() over(partition by deptno order by sal desc) as sal_rank from scott.emp;
--具有相等值的行排位相同，序號是連續的。
select ename,job,deptno,sal,dense_rank() over(order by sal desc) as sal_rank from scott.emp;

------------------------2013-5-7------------------------
用于存儲和檢索數據庫中數據的語言是SQL

select可以用于：
創建現有表的副本????? create table tbl as select * form tbl2 where 條件 and 1 <> 2? --復制表結構
插入來自其他表的記錄? insert into tbl select * from tbl2;? --復制表數據
使用別名讓列顯示其他名稱 select col 字段1, col2 as 字段2 from tbl; --別名顯示

select initcap('hello') from dual;??? --Hello

select lower('FUN') from dual;? --fun

select upper('sun') from dual;? --SUN

select ltrim('xyzadmin','xyz') from dual;??? --admin

select rtrim('xyzadmin','admin') from dual;? --xyz

select translate('jack','j','b') from dual;? --back

select replace('jack and jue','j','bl') from dual;? --black and blue

select translate('jack and jue','j','k') from dual; --kack and kue? 只能改變一個字符。

select instr('worldwide','d') from dual;? --5

select substr('abcdefg',3,2) from dual;?? --cd

select concat('Hello','world') from dual; --Helloworld

select chr(65) from dual; --A

select lpad('function',15,'=') from dual;?? =======function
select rpad('function',15,'=') from dual;?? function=======

select trim(9 from 9999876789999) from dual;? --87678

select length('frances') from dual;? --7

select to_char(sysdate,'YYYY "年" fmMM "月" fmDD "日" HH24:MI:SS') from dual;? --2013 年 5 月 07 日 10:19:39

--最多為5位數字，以本地貨幣符號作為前綴
select empno,to_char(sal,'C99999') from scott.emp;??? --7369??????? CNY800

select to_date('2005-12-06','yyyy-mm-dd') from dual;? --06-12月-05

select to_number('100') from dual;??????? --100

臨時表
只在用戶會話期間存在的表
數據在用戶事務處理期間持續存在
使用create global temporary table命令
不會自動獲得空間
插入的值僅在此特定會話中可用

desc dual;
?名稱????????????????????????????????????? 是否為空? 類型
?----------------------------------------- -------- ----------------------------
?DUMMY????????????????????????????????????????????? VARCHAR2(1)

Rollup和Cube運算符用于查找表達式的聚集和超級聚集。
統計函數???????????????????????????? 根據分組情況進行統計，最終進行全部匯總。
Rollup結果集包含分組行和小計行
rollup(column_name1,column_name2)

--根據Deptno和JOB進行分組,求相同deptno,相同job員工工資總和。
select deptno,job,sum(sal) from scott.emp group by deptno,job order by deptno;

select dummy from dual group by rollup(dummy);

--以deptno為基準，按job統計各個部門的job總和。
select deptno,job,sum(sal) from scott.emp where deptno is not null group by rollup(deptno,job) order by deptno;
??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 10 CLERK?????????? 1300
??????? 10 MANAGER???????? 2450
??????? 10 PRESIDENT?????? 5000
??????? 10???????????????? 8750
??????? 20 ANALYST???????? 6000
??????? 20 CLERK?????????? 1900
??????? 20 MANAGER???????? 2975
??????? 20??????????????? 10875
??????? 30 CLERK??????????? 950
??????? 30 MANAGER???????? 2850
??????? 30 SALESMAN??????? 5600

??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 30???????????????? 9400
????????????????????????? 29025

--以job為基準，按部門統計各個job的總和。
select deptno,job,sum(sal) from scott.emp where deptno is not null group by rollup(job,deptno) order by job;
??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 20 ANALYST???????? 6000
?????????? ANALYST???????? 6000
??????? 10 CLERK?????????? 1300
??????? 20 CLERK?????????? 1900
??????? 30 CLERK??????????? 950
?????????? CLERK?????????? 4150
??????? 10 MANAGER???????? 2450
??????? 20 MANAGER???????? 2975
??????? 30 MANAGER???????? 2850
?????????? MANAGER???????? 8275
??????? 10 PRESIDENT?????? 5000

??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
?????????? PRESIDENT?????? 5000
??????? 30 SALESMAN??????? 5600
?????????? SALESMAN??????? 5600
????????????????????????? 29025

select deptno,job,sum(sal) from scott.emp where deptno is not null group by rollup(job,deptno) order by deptno;
??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 10 CLERK?????????? 1300
??????? 10 MANAGER???????? 2450
??????? 10 PRESIDENT?????? 5000
??????? 20 ANALYST???????? 6000
??????? 20 CLERK?????????? 1900
??????? 20 MANAGER???????? 2975
??????? 30 CLERK??????????? 950
??????? 30 SALESMAN??????? 5600
??????? 30 MANAGER???????? 2850
?????????? ANALYST???????? 6000
?????????? PRESIDENT?????? 5000

??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
?????????? SALESMAN??????? 5600
????????????????????????? 29025
?????????? MANAGER???????? 8275
?????????? CLERK?????????? 4150

?

Cube產生交叉表格報表
cube(column_name1,column_name2,column_name3)

select deptno,job,sum(sal) from scott.emp where deptno is not null group by cube(deptno,job) order by deptno;
?? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 10 CLERK?????????? 1300
??????? 10 MANAGER???????? 2450
??????? 10 PRESIDENT?????? 5000
??????? 10???????????????? 8750
??????? 20 ANALYST???????? 6000
??????? 20 CLERK?????????? 1900
??????? 20 MANAGER???????? 2975
??????? 20??????????????? 10875
??????? 30 CLERK??????????? 950
??????? 30 MANAGER???????? 2850
??????? 30 SALESMAN??????? 5600

??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 30???????????????? 9400
?????????? ANALYST???????? 6000
?????????? CLERK?????????? 4150
?????????? MANAGER???????? 8275
?????????? PRESIDENT?????? 5000
?????????? SALESMAN??????? 5600
????????????????????????? 29025

grouping: 如果列值為空，那么grouping()返回1，否則返回0
select grouping(deptno), deptno,job,sum(sal) from scott.emp where deptno is not null group by cube(deptno,job) order by deptno;

grouping sets提供了指定匯總集合條件的功能。
select deptno,job,sum(sal) from scott.emp where deptno is not null group by grouping sets(deptno,job);
??? DEPTNO JOB???????? SUM(SAL)
---------- --------- ----------
??????? 10???????????????? 8750
??????? 20??????????????? 10875
??????? 30???????????????? 9400
?????????? ANALYST???????? 6000
?????????? CLERK?????????? 4150
?????????? MANAGER???????? 8275
?????????? PRESIDENT?????? 5000
?????????? SALESMAN??????? 5600

#177.?關于Oracle的LONG類型描述正確的是：?? abd
A?LONG數據類型中存儲的是可變長字符串，最大長度限制是2GB
B?LONG類型主要用于不需要作字符串搜索的長串數據，如果要進行字符搜索就要用varchar2類型
C?索引LONG類型列會明顯提升查詢效率
D?一個表中只能包含一個LONG類型的列

根據別名來排序：
select empno 編號,ename,sal from scott.emp order by 編號;
--對檢索結果（第2個，第1個）的排序--
select empno 編號,ename,sal from scott.emp order by 2,1;
--對檢索結果（第3個）的排序--
select empno 編號,ename,sal from scott.emp order by 3 desc;

select instr('Mississippi','i',3,3) from dual;???? -- 11

short類型長度16位。
int,float類型長度32位。
long,double類型長度64位。

472.?關于索引的說法錯誤的是BD
A、刪除索引不會影響表中的數據。
B、索引表中也有rowid 列
C、索引又系統管理和應用。
D、索引總是能夠提高檢索效率

Oracle Class2. SQL查詢和SQL函數(Oracle數據類型,ddl,dml,dcl,事務控制語言tcl,sql操作符,sql函數,select語句,運算符,分析函數,臨時表)