Oracle的數(shù)據(jù)類型：加粗字體（ctrl+B）

使用ctrl+/可以快速的彈出一個虛擬符號鍵盤：

(alt + 9999)

如果只想復制表的結構到另一張表,，而不復制任何的數(shù)據(jù),，則可以使用一個永遠查詢不到結果的查詢來執(zhí)行；

    Create table empnull as select * from emp where 1=2;

為表重命名：

在oracle數(shù)據(jù)庫中,，所有的數(shù)據(jù)實際上都是通過數(shù)據(jù)字典保存的,，例如：select * from tab；

以上就是一個數(shù)據(jù)字典,，而在oracle數(shù)據(jù)庫中,，提供了三種類型的數(shù)據(jù)字典，最常用的是dbo,、user,、所以下面查詢一個user_tables數(shù)據(jù)字典；

Select * from user_tables；

也就是說oracle中的所有數(shù)據(jù)都是按照文件保存的,，那么所有的內(nèi)容都會在數(shù)據(jù)字典中注冊,，既然這樣，修改表名稱就相當于修改一條數(shù)據(jù)而已：

Rename 舊的表名稱 to 新的表名稱,；

如果希望徹底釋放掉一張表所占用的全部資源（表空間、索引等等）就可以使用截斷表的語法,，語法如下：

Truncate table 表名稱,；

在oracle10G中，為了防止用戶的誤刪除表的操作,，專門提供了回收站的功能,，用戶所刪除的表默認情況下會在一個回收站之中保存，而用戶也可以通過回收站進行表的恢復,，所以此技術成為閃回（flashback）,；

可以通過如下名稱查看回收站中的表：

Show RECYCLEBIN;

ORIGINAL NAME  RECYCLEBIN NAME     OBJECT TYPE        DROP TIME

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

MEMBER   BIN$r34Nm9OVRxqjy8Jwh1KWJw==$0 TABLE        2012-01-25:10:28:51

可以通過如下的命令恢復別刪除的表：

FLASHBACK TABLE 表名稱 TO BEFORE DROP;

例如恢復上面的member表：

Flashback table member TO before drop；

當然呢,，也可以直接刪除掉回收站中的而一些數(shù)據(jù)表,，語法如下：

Purage table 表名稱;

比如刪除回收站中的member表：

Purge table member；

SQL> purge table member;

表已清除,。

也可以清空回收站,；

PURGE RECYCLEBIN;

如果希望刪除的時候不進入回收站，則可以加一個purge關鍵字,；

Drop table myemp purge;

表結構的修改：

添加列：Alter table add（列1 類型）等和創(chuàng)建類似,；

如果希望修改已經(jīng)存在的列：

Alter table modify （）；和創(chuàng)建表類似,；

但是表結構能不修改就不修改,，因為世界上性能最好的DB2是不允許修改表結構的；如果遇到要修改表結構的時候,，把表刪除,，然后重新建立；

唯一約束： UNIQUE

Unique對于插入多行為null的值的處理理解為不重復,，因為這里不知道它的具體值,；

主鍵約束：PRIMARY KEY;

主鍵約束=非空約束+唯一約束；

設置為主鍵之后,，不允許為空,，而且不允許重復；作為數(shù)據(jù)的唯一標記出現(xiàn),；

從正常的開發(fā)而言,，一張表一般只設置一個主鍵，但是從SQL語法來看，一張表可以設置多個主鍵,，稱為復合主鍵：

Create table member(Mid number,Name varchar2(50) not null,Constraint pk_mid_name primary key(mid,name));

檢查約束：

Check

Create table member(Mid number,Name varchar2(50) not null,

Sex varchar2(10) not null,

Age number(3) ,

Constraint pk_mid_name primary key(mid),

Constraint ck_sex check (sex in(‘男’,’女’,’中’)),

Constraint ck_age check (age between 0 and 200)

);

Drop table member;

Drop table book;

Create table member(mid  number,name varchar2(50) not null,

Constraint pl_mid primary key(mid)

);

Create table book(bid number,title varchar2(50) not null,mid number,

Constraint pk_bid primary key(bid),

Constraint fk_mid foreign key(mid) references member(mid)

);

筆記1部分：

1,、使用host 可以引用windows系統(tǒng)的copy命令等：

Host copy d:\demo.sql d:\hello.txt

2、scott用戶的表結構：

可以使用以下命令查看所有表：

Select *  from tab,；

可以使用以下命令查看表結構：

Desc 表名稱,；

雇員表（以下）

以下表存放工資數(shù)據(jù)，基本上用不到,；

工資登記表：

SQL> select '雇員編號是：'||empno||'的雇員姓名是:'||ename||',基本工資是：'||sal||',職位是'||job||'!'雇員信息 from emp;

雇員信息

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

雇員編號是：7369的雇員姓名是:SMITH,基本工資是：800,職位是CLERK!

雇員編號是：7499的雇員姓名是:ALLEN,基本工資是：1600,職位是SALESMAN!

雇員編號是：7521的雇員姓名是:WARD,基本工資是：1250,職位是SALESMAN!

雇員編號是：7566的雇員姓名是:JONES,基本工資是：2975,職位是MANAGER!

雇員編號是：7654的雇員姓名是:MARTIN,基本工資是：1250,職位是SALESMAN!

雇員編號是：7698的雇員姓名是:BLAKE,基本工資是：2850,職位是MANAGER!

雇員編號是：7782的雇員姓名是:CLARK,基本工資是：2450,職位是MANAGER!

雇員編號是：7788的雇員姓名是:SCOTT,基本工資是：3000,職位是ANALYST!

雇員編號是：7839的雇員姓名是:KING,基本工資是：5000,職位是PRESIDENT!

雇員編號是：7844的雇員姓名是:TURNER,基本工資是：1500,職位是SALESMAN!

雇員編號是：7876的雇員姓名是:ADAMS,基本工資是：1100,職位是CLERK!

在oracle中所有的數(shù)據(jù)都是區(qū)分大小寫的,；大小寫錯誤可能查詢不出相應的數(shù)據(jù)；

以下三個查詢語句的結構是相同的：

Select * from emp where job!=’CLERK’;

Select * from emp where job<>’CLERK’;

Select * from emp where not job=’CLERK’  --這三種語法都可以在SQL Server中使用,；

對between and 求反：

Select * from emp where not sal between 1500 and 3000

Between and 不只是對數(shù)據(jù),，對日期也一樣進行操作；

Select * from emp where hiredate between ’01-1月-1981’ and ’31-12月-81’

Set linesize 300;

Set pagesize 200;

★  對于like操作符,，不止可以用于字符串型的數(shù)據(jù),，而且可以用于其他類型的數(shù)據(jù)；

★  Order by子句是寫在所有語句最后的子句,；

★  INITCAP函數(shù),，將字符串首字母變?yōu)榇髮懀弧惺Ｓ嗟淖帜笗兂尚懀?SPAN lang=EN-US>

★  Oracle數(shù)據(jù)庫中為了查詢的方便,，專門提供了一個dual的虛擬表,，這樣from后邊便可以跟這個表名進行查詢；

★  Select * from emp where ename=UPPER(‘&hh’);中的單引號中間的字符表示一個變量,，下一步系統(tǒng)會讓用戶輸入該變量的值,，然后系統(tǒng)進行查詢：結果圖如下：

★  SQL> select * from emp where ename=upper('&hh');

★  輸入 hh 的值:  smith

★  原值    1: select * from emp where ename=upper('&hh')

★  新值    1: select * from emp where ename=upper('smith')

EMPNO ENAME  JOB MGR HIREDATE     SAL       COMM     DEPTNO

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

7369  SMITH CLERK  7902 17-12月-80     800                       20

★  字符串操作有兩種方式：

語法一：substr(字符串，開始點)表示截取該字符串從開始點一直到結尾的部分,；

語法二：substr（字符串,，開始點，結束點）

截取字符串倒數(shù)三個字符：

除了可以用length（字符串）-2以外,，還可以使用負數(shù)表示：

SQL> select substr(ename,-3) from emp;

SUBSTR

------

ITH

LEN

ARD

NES

TIN

AKE

ARK

OTT

ING

NER

AMS

面試題：請問substr截取的時候下標是從0還是從1開始,？

答：從0和1都一樣；

數(shù)字函數(shù)：

Round(數(shù)字|列,，保留小數(shù)的位數(shù)),，表示四舍五入的操作,；保留小數(shù)位數(shù)如果不寫，則表示不保留小數(shù),；

Trunc（數(shù)字|列,，保留小數(shù)的位數(shù)），舍棄指定位置的內(nèi)容,；

Mod（數(shù)字1,，數(shù)字2）取模，取余數(shù),；

SQL> select round(903.53567),round(-903.53567),round(903.53567,-1) from dual;

ROUND(903.53567) ROUND(-903.53567) ROUND(903.53567,-1)

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

             904              -904                 900

SQL> select trunc(903.5325),trunc(-903.5367),trunc(903.53567,2),trunc(903.53567,-1) from dual;

TRUNC(903.5325) TRUNC(-903.5367) TRUNC(903.53567,2) TRUNC(903.53567,-1)

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

            903             -903             903.53                 900

可以使用sysdate來取得當前日期的值,；

Oracle 函數(shù)大全(字符串函數(shù)，數(shù)學函數(shù),，日期函數(shù)，邏輯運算函數(shù),，其他函數(shù))

關鍵詞： Oracle函數(shù)    字符串函數(shù)    數(shù)學函數(shù)    日期函數(shù)    邏輯運算函數(shù)                                          

SQL中的單記錄函數(shù)
1.ASCII
返回與指定的字符對應的十進制數(shù);
SQL> select ascii(’A’) A,ascii(’a’) a,ascii(’0’) zero,ascii(’ ’) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32

2.CHR
給出整數(shù),返回對應的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
趙 A
3.CONCAT
連接兩個字符串;
SQL> select concat(’010-’,’88888888’)||’轉23’ 高乾競電話 from dual;
高乾競電話
----------------
010-88888888轉23
4.INITCAP
返回字符串并將字符串的第一個字母變?yōu)榇髮?SPAN lang=EN-US>;
SQL> select initcap(’smith’) upp from dual;
UPP
-----
Smith

5.INSTR(C1,C2,I,J)
在一個字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的開始位置,默認為1
J 出現(xiàn)的位置,默認為1
SQL> select instr(’oracle traning’,’ra’,1,2) instring from dual;
INSTRING
---------
9

6.LENGTH
返回字符串的長度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾競 3 北京市海錠區(qū) 6 9999.99 7

7.LOWER
返回字符串,并將所有的字符小寫
SQL> select lower(’AaBbCcDd’)AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd

8.UPPER
返回字符串,并將所有的字符大寫
SQL> select upper(’AaBbCcDd’) upper from dual;
UPPER
--------
AABBCCDD

9.RPAD和LPAD(粘貼字符)
RPAD 在列的右邊粘貼字符
LPAD 在列的左邊粘貼字符
SQL> select lpad(rpad(’gao’,10,’*’),17,’*’)from dual;
LPAD(RPAD(’GAO’,1
-----------------
*******gao*******
不夠字符則用*來填滿

10.LTRIM和RTRIM
LTRIM 刪除左邊出現(xiàn)的字符串
RTRIM 刪除右邊出現(xiàn)的字符串
SQL> select ltrim(rtrim(’ gao qian jing ’,’ ’),’ ’) from dual;
LTRIM(RTRIM(’
-------------
gao qian jing

11.SUBSTR(string,start,count)
取子字符串,從start開始,取count個
SQL> select substr(’13088888888’,3,8) from dual;
SUBSTR(’
--------
08888888

12.REPLACE(’string’,’s1’,’s2’)
string 希望被替換的字符或變量
s1 被替換的字符串
s2 要替換的字符串
SQL> select replace(’he love you’,’he’,’i’) from dual;
REPLACE(’H
----------
i love you

13.SOUNDEX
返回一個與給定的字符串讀音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values(’weather’);
SQL> insert into table1 values(’wether’);
SQL> insert into table1 values(’gao’);
SQL> select xm from table1 where soundex(xm)=soundex(’weather’);
XM
--------
weather
wether

14.TRIM(’s’ from ’string’)
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默認為空格符
15.ABS
返回指定值的絕對值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100

16.ACOS
給出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927

17.ASIN
給出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878

18.ATAN
返回一個數(shù)字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816

19.CEIL
返回大于或等于給出數(shù)字的最小整數(shù)
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4

20.COS
返回一個給定數(shù)字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一個數(shù)字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598

22.EXP
返回一個數(shù)字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818

23.FLOOR
對給定的數(shù)字取整數(shù)
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345

24.LN
返回一個數(shù)字的對數(shù)值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999

25.LOG(n1,n2)
返回一個以n1為底n2的對數(shù)
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2

26.MOD(n1,n2)
返回一個n1除以n2的余數(shù)
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2

27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27

28.ROUND和TRUNC
按照指定的精度進行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55

29.SIGN
取數(shù)字n的符號,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0

30.SIN
返回一個數(shù)字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1

31.SIGH
返回雙曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598

32.SQRT
返回數(shù)字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777

33.TAN
返回數(shù)字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083

34.TANH
返回數(shù)字n的雙曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609

35.TRUNC
按照指定的精度截取一個數(shù)
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16

36.ADD_MONTHS
增加或減去月份
SQL> select to_char(add_months(to_date(’199912’,’yyyymm’),2),’yyyymm’) from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date(’199912’,’yyyymm’),-2),’yyyymm’) from dual;
TO_CHA
------
199910

37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,’yyyy.mm.dd’),to_char((sysdate)+1,’yyyy.mm.dd’) from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月 -04

38.MONTHS_BETWEEN(date2,date1)
給出date2-date1的月份
SQL> select months_between(’19-12月-1999’,’19-3月-1999’) mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date(’2000.05.20’,’yyyy.mm.dd’),to_date(’2005.05.20’,’yyyy.mm.dd’)) mon_betw from dual;
MON_BETW
---------
-60

39.NEW_TIME(date,’this’,’that’)
給出在this時區(qū)=other時區(qū)的日期和時間
SQL> select to_char(sysdate,’yyyy.mm.dd hh24:mi:ss’) bj_time,to_char(new_time
2 (sysdate,’PDT’,’GMT’),’yyyy.mm.dd hh24:mi:ss’) los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY(date,’day’)
給出日期date和星期x之后計算下一個星期的日期
SQL> select next_day(’18-5月-2001’,’星期五’) next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用來得到系統(tǒng)的當前日期
SQL> select to_char(sysdate,’dd-mm-yyyy day’) from dual;
TO_CHAR(SYSDATE,’
-----------------
09-05-2004 星期日
trunc(date,fmt)按照給出的要求將日期截斷,如果fmt=’mi’表示保留分,截斷秒
SQL> select to_char(trunc(sysdate,’hh’),’yyyy.mm.dd hh24:mi:ss’) hh,
2 to_char(trunc(sysdate,’mi’),’yyyy.mm.dd hh24:mi:ss’) hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

42.CHARTOROWID
將字符數(shù)據(jù)類型轉換為ROWID類型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

43.CONVERT(c,dset,sset)
將源字符串 sset從一個語言字符集轉換到另一個目的dset字符集
SQL> select convert(’strutz’,’we8hp’,’f7dec’) "conversion" from dual;
conver
------
strutz

44.HEXTORAW
將一個十六進制構成的字符串轉換為二進制

45.RAWTOHEXT
將一個二進制構成的字符串轉換為十六進制

46.ROWIDTOCHAR
將ROWID數(shù)據(jù)類型轉換為字符類型

47.TO_CHAR(date,’format’)
SQL> select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;
TO_CHAR(SYSDATE,’YY
-------------------
2004/05/09 21:14:41

48.TO_DATE(string,’format’)
將字符串轉化為ORACLE中的一個日期

49.TO_MULTI_BYTE
將字符串中的單字節(jié)字符轉化為多字節(jié)字符
SQL> select to_multi_byte(’高’) from dual;
TO
--
高

50.TO_NUMBER
將給出的字符轉換為數(shù)字
SQL> select to_number(’1999’) year from dual;
YEAR
---------
1999

51.BFILENAME(dir,file)
指定一個外部二進制文件
SQL>insert into file_tb1 values(bfilename(’lob_dir1’,’image1.gif’));

52.CONVERT(’x’,’desc’,’source’)
將x字段或變量的源source轉換為desc
SQL> select sid,serial#,username,decode(command,
2 0,’none’,
3 2,’insert’,
4 3,
5 ’select’,
6 6,’update’,
7 7,’delete’,
8 8,’drop’,
9 ’other’) cmd from v$session where type!=’background’;
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none

53.DUMP(s,fmt,start,length)
DUMP函數(shù)以fmt指定的內(nèi)部數(shù)字格式返回一個VARCHAR2類型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D

54.EMPTY_BLOB()和EMPTY_CLOB()
這兩個函數(shù)都是用來對大數(shù)據(jù)類型字段進行初始化操作的函數(shù)

55.GREATEST
返回一組表達式中的最大值,即比較字符的編碼大小.
SQL> select greatest(’AA’,’AB’,’AC’) from dual;
GR
--
AC
SQL> select greatest(’啊’,’安’,’天’) from dual;
GR
--
天

56.LEAST
返回一組表達式中的最小值
SQL> select least(’啊’,’安’,’天’) from dual;
LE
--
啊

57.UID
返回標識當前用戶的唯一整數(shù)
SQL> show user
USER 為"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25

58.USER
返回當前用戶的名字
SQL> select user from dual;
USER
------------------------------
GAO

59.USEREVN
返回當前用戶環(huán)境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看當前用戶是否是DBA如果是則返回true
SQL> select userenv(’isdba’) from dual;
USEREN
------
FALSE
SQL> select userenv(’isdba’) from dual;
USEREN
------
TRUE
SESSION
返回會話標志
SQL> select userenv(’sessionid’) from dual;
USERENV(’SESSIONID’)
--------------------
152
ENTRYID
返回會話人口標志
SQL> select userenv(’entryid’) from dual;
USERENV(’ENTRYID’)
------------------
0
INSTANCE
返回當前INSTANCE的標志
SQL> select userenv(’instance’) from dual;
USERENV(’INSTANCE’)
-------------------
1
LANGUAGE
返回當前環(huán)境變量
SQL> select userenv(’language’) from dual;
USERENV(’LANGUAGE’)
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回當前環(huán)境的語言的縮寫
SQL> select userenv(’lang’) from dual;
USERENV(’LANG’)
----------------------------------------------------
ZHS
TERMINAL
返回用戶的終端或機器的標志
SQL> select userenv(’terminal’) from dual;
USERENV(’TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字節(jié))數(shù)
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM

60.AVG(DISTINCT|ALL)
all表示對所有的值求平均值,distinct只對不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
語句已處理,。
SQLWKS> insert into table3 values(’gao’,1111.11);
SQLWKS> insert into table3 values(’gao’,1111.11);
SQLWKS> insert into table3 values(’zhu’,5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59

61.MAX(DISTINCT|ALL)
求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000

62.MIN(DISTINCT|ALL)
求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11

63.STDDEV(distinct|all)
求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951

64.VARIANCE(DISTINCT|ALL)
求協(xié)方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9

65.GROUP BY
主要用來對一組數(shù)進行統(tǒng)計
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400

66.HAVING
對分組統(tǒng)計再加限制條件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400

67.ORDER BY
用于對查詢到的結果進行排序輸出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950

Oracle的左右連接使用起來更加方便：

select * from emp e,dept d where e.deptno(+)=d.deptno;

（+）放在=的左邊表示右連接，放在右邊表示左連接,；

SQL 1999連接查詢：cross join 專門用于產(chǎn)生笛卡爾積,；

Natural join  自動找到匹配的關聯(lián)字段，消除笛卡爾積,；

Join  using 字段名,，使用指定的字段來自動消除笛卡爾積；

Join  on  用戶自己指定一個可以消除笛卡爾積的條件,；

連接方向的改變

Left  （outer） join

Right （outer） join

Full  （out）  join

SQL 1999主要用于其他的數(shù)據(jù)庫,，如果一直使用oracle數(shù)據(jù)庫，可以略過此部分,；

Group by 要放在order by之前,；

Group by 子句中的內(nèi)容必須出現(xiàn)在select子句中；

分組函數(shù)允許嵌套,，但是嵌套之后的分組函數(shù)的查詢之中不能再出現(xiàn)任何的其他字段,；

按照職位分組，統(tǒng)計平均工資最高的工資：

第一步：Select job,avg(sal) from emp group by job;

第二步:  select max(avg(sal)) from emp group by  job;

多表查詢的性能可能會比較低,，而它最可能的替代者是子查詢,，所以子查詢再數(shù)據(jù)量較大時使用的較多；

完整的查詢語句可能如下：

Select  字段1,，字段2,，（子句 別名1）……

From 表名稱1，表名稱2,，子句3

Where 條件,，子句

Group by 分組字段1,，分組字段2……

Having 分組后的過濾條件（可以使用統(tǒng)計函數(shù)）

Order by 排序字段

Where子查詢一般都返回單行單列，多行單列或者單行多列的結果,；

From子查詢一般返回多行多列的數(shù)據(jù),，當做臨時表出現(xiàn)；

Where子句為單行多列的例子：(單行多列的例子很少出現(xiàn))

Select * from emp where (job,sal)=(select job,sal from emp where ename=’ALLEN’);

Where子句如果返回的是多行單列的話,，這個時候要用到三種判斷：

In any  all

1,、 in 免記；

2,、 any  與每一個內(nèi)容相匹配,，有三種匹配形式：

=any  Select * from emp where sal =any( select sal from emp where job=’MANAGER’);可以看出這里的功能和in是類似的；

>any  Select * from emp where sal >any( select sal from emp where job=’MANAGER’);比子查詢中返回記錄最小的還要大的,；

<any  Select * from emp where sal <any( select sal from emp where job=’MANAGER’);比子查詢中返回記錄最大的還要小的,；

All操作符：與每一個內(nèi)容相匹配，有兩種匹配形式：

>ALL: Select * from emp where sal >ALL( select sal from emp where job=’MANAGER’);比子查詢最大的查詢結果還要大,；

<ALL: Select * from emp where sal <ALL( select sal from emp where job=’MANAGER’);比子查詢中最小的結果還要小的,；

查詢出每個部門的編號、名稱,、位置,、部門人數(shù)、平均工資：

Select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)

From  emp e, dept d

Where e.deptno(+)=d.deptno

Group by d.deptno,d.dname,d.loc;

使用分組連接查詢,，實際操作的是56條數(shù)據(jù),，而最終過濾出4條數(shù)據(jù)，效率很低,；

新的解決方案：通過子查詢完成,，所有的統(tǒng)計查詢只能在group by中出現(xiàn)，所以在子查詢之中負責統(tǒng)計數(shù)據(jù),，而在外部的查詢之中,，負責將統(tǒng)計數(shù)據(jù)和dept表數(shù)據(jù)相統(tǒng)一；

Select d.deptno,d.dname,d.loc, temp.count,temp.avg

From dept d,(select deptno dno,count(empno) count,avg(sal) avg from emp  group by deptno) temp

Where d.deptno=temp.dno(+);

所操作的數(shù)據(jù)量：

子查詢中統(tǒng)計的的記錄是14條記錄,，最終統(tǒng)計的顯示結果是3條記錄,，dept表之中一共有4條記錄；如果現(xiàn)在產(chǎn)生笛卡爾積的話只有12條記錄,，再加上雇員的14條記錄,，一共才26條記錄；

如果要增加數(shù)據(jù)的話,，要對不同的數(shù)據(jù)類型分別處理：

增加數(shù)字時,，直接編寫數(shù)字；

增加字符時,，要用單引號聲明,；

增加日期類型的數(shù)據(jù)時,，有好幾種做法：

|-按照已有的字符串格式；

|-利用to_date函數(shù)將字符串變成date型數(shù)據(jù),；

|-如果設置的系統(tǒng)當前時間,，在使用sysdate（）函數(shù)；

Insert into myemp(empno,ename,hiredate,sal,mgr,job,comm)

values (8888,'張三',to_date('1960-08-07','yyyy-mm-dd'),8000,7369,'清潔工',1000);

刪除1987年入職的員工數(shù)據(jù)：

Delete from myemp where to_char(hiredate,’yyyy’)=1987;

事務處理：

在DML中,，查詢操作比更新操作要安全,；

事務的兩個命令：

提交：commit；

回滾：rollback,；

所有的數(shù)據(jù)更新一定會受到事務的控制,；

每個不同的用戶連接到Oracle數(shù)據(jù)庫時都獨占一個session，如果同時進行同一個操作,，則會產(chǎn)生死鎖,；

數(shù)據(jù)偽列：

Rownum 這是根據(jù)查詢的內(nèi)容自動生成的行號；不是固定不變的,；

Rowid 這是數(shù)據(jù)在數(shù)據(jù)庫中存放的物理地址的編號,；

ROWID                  DEPTNO LOC

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

AAAMfNAAEAAAAAQAAA         10 NEW YORK

AAAMfNAAEAAAAAQAAB         20 DALLAS

AAAMfNAAEAAAAAQAAC         30 CHICAGO

AAAMfNAAEAAAAAQAAD         40 BOSTON