------------------------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? --顯示當前環(huán)境變量的值

show error --顯示當前在創(chuàng)建函數(shù)、存儲過程、觸發(fā)器、包等對象的錯誤信息。

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

ora-01536: 超出表空間system的空間限量?? -->賦予dba角色可以解決問題。
系統(tǒng)權(quán)限????????????? 管理選項
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('=============================');?? 測試窗口，打印輸出。

聚合函數(shù)RANK 和 dense_rank 主要的功能是計算一組數(shù)值中的排序值。
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區(qū)別：
dense_rank與rank()用法相當，但是有一個區(qū)別：dence_rank在并列關(guān)系時，相關(guān)等級不會跳過,rank則跳過!
比如:存在兩個第一名是,dence_rank函數(shù)接下來是第二名，而rank函數(shù)接下來是第三名!/

收入排行
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操作符
算術(shù)操作符，+ - * /
比較操作符，= != < > <= >= between and, in,like, is null
邏輯操作符，and,or,not
集合操作符，union,union all,intersect,minus
連接操作符，||

操作符優(yōu)先級
算術(shù)操作符? 最高
連接操作符
比較操作符
邏輯操作符

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

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

日期函數(shù)包括：
add_months
months_between
last_day
round
next_day
trunc
extract

字符函數(shù)
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)
其他的字符函數(shù)
chr和ascii
lpad和rpad
trim
length
decode

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

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

轉(zhuǎn)換函數(shù)
to_char
to_date
to_number
轉(zhuǎn)換空值的函數(shù)
nvl
nvl2
nullif

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

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

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

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

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

select可以用于：
創(chuàng)建現(xiàn)有表的副本????? create table tbl as select * form tbl2 where 條件 and 1 <> 2? --復(fù)制表結(jié)構(gòu)
插入來自其他表的記錄? insert into tbl select * from tbl2;? --復(fù)制表數(shù)據(jù)
使用別名讓列顯示其他名稱 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位數(shù)字，以本地貨幣符號作為前綴
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

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

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

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

--根據(jù)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統(tǒng)計各個部門的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為基準，按部門統(tǒng)計各個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產(chǎn)生交叉表格報表
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.?關(guān)于Oracle的LONG類型描述正確的是：?? abd
A?LONG數(shù)據(jù)類型中存儲的是可變長字符串，最大長度限制是2GB
B?LONG類型主要用于不需要作字符串搜索的長串數(shù)據(jù)，如果要進行字符搜索就要用varchar2類型
C?索引LONG類型列會明顯提升查詢效率
D?一個表中只能包含一個LONG類型的列

根據(jù)別名來排序：
select empno 編號,ename,sal from scott.emp order by 編號;
--對檢索結(jié)果（第2個，第1個）的排序--
select empno 編號,ename,sal from scott.emp order by 2,1;
--對檢索結(jié)果（第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.?關(guān)于索引的說法錯誤的是BD
A、刪除索引不會影響表中的數(shù)據(jù)。
B、索引表中也有rowid 列
C、索引又系統(tǒng)管理和應(yīng)用。
D、索引總是能夠提高檢索效率

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