第一篇：黑馬程序員c語言教程：視圖 序列 索引概念

視圖 序列 索引 視圖的概念

--創(chuàng)建視圖 是一個(gè)邏輯概念,本身沒有數(shù)據(jù),還是來源于表

--視圖的優(yōu)點(diǎn) 簡化查詢 創(chuàng)建視圖

create view empincomeview as select e.empno, e.ename, e.sal, e.sal*12 annalsal, sal*12+nvl(comm, 0)income, d.dname from emp e, dept d where e.deptno = d.deptno;

--查詢員工編號(hào) 名稱 工資 年薪 年收入 部門名稱信息

* 第 1 行出現(xiàn)錯(cuò)誤: ORA-01031: 權(quán)限不足--需要有create view權(quán)限

用戶管理中 權(quán)限管理 需要管理員登錄進(jìn)行授權(quán)

C:>sqlplus /as sysdba

SQL> grant create view to scott;創(chuàng)建視圖的權(quán)限給scott用戶 操作視圖

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

TEST2 TABLE

STUDENT TABLE

EMPINCOME TABLE

EMPINCOMEVIEW VIEW select * from empincomeview;

--視圖的優(yōu)點(diǎn)

簡化復(fù)雜查詢(封裝 復(fù)雜sql語言)限制數(shù)據(jù)訪問 銀行系統(tǒng)視圖呈現(xiàn),而且只讀視圖, 通過存儲(chǔ)過程和存儲(chǔ)函數(shù)來做

通過視圖修改表中的數(shù)據(jù),不建議,有很多限制

提供數(shù)據(jù)的相互獨(dú)立

同樣的數(shù)據(jù)，可以有不同的顯示方式

但視圖不能提高性能視圖的幾個(gè)注意點(diǎn)

--視圖 with check option

create view view1

as

select * from emp where deptno=10

with check option;

insert into view1 values(***,***,...., 10);

insert into view1 values(***,***,...., 20);

--通過視圖只能看到10號(hào)部門的員工信息,不能插入20號(hào)部門員工

--視圖with read only;

create or replace view empincomeview2

as

select e.empno, e.ename, e.sal, e.sal*12 annalsal, sal*12+nvl(comm, 0)income, d.dname

from emp e, dept d

where e.deptno = d.deptno

with read only;

--修改視圖 視圖只能替換,不能修改

--區(qū)分復(fù)雜視圖和簡單視圖 沒有太多實(shí)際意義,關(guān)鍵是求解問題--視圖操作的限制

結(jié)論:一般不通過視圖做insert/update/delete操作

視圖的目的:就是簡化查詢的....====================--創(chuàng)建序列

create sequence myseq;序列基本概念 放在內(nèi)存中速度快

auto_increment

[ 1 , 2, 3, 4......20 ]

▲

tableA 序列的應(yīng)用

--創(chuàng)建表

create table tableA

（tid number，tname varchar2(40));

--從序列中取nextval,產(chǎn)生tableA的主鍵值

insert into tableA values(myseq1.nextval, 'aa');

序列的兩個(gè)屬性 NEXTVAL CURRVAL nextval應(yīng)在currval之前被指定

select myseq.currval from dual;

SQL> select myseq.currval from dual;

select myseq.currval from dual

*

第 1 行出現(xiàn)錯(cuò)誤:

ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此會(huì)話中定義

select myseq.nextval from dual

select myseq.currval from dual 刪除序列

--drop sequence myseq;序列需要注意的問題

多個(gè)表共用一個(gè)序列,造成序列不連續(xù)

回滾會(huì)造成,造成序列不連續(xù)

系統(tǒng)異常,內(nèi)存序列丟失,造成序列不連續(xù)

================== 有關(guān)索引小專題 0 索引原始知識(shí)

索引和表 是 單獨(dú)存放

索引對(duì)dba和開發(fā)人員，使用起來是透明的，只要?jiǎng)?chuàng)建索引 索引基本原理 圖示 10號(hào) 20號(hào)部門

表記錄

索引表

算法

--------▲

▲

b+樹--------★

▲

--------▲

▲

--------`(*∩_∩*)′

▲

--------▲

★

--------★

★

--------▲

`(*∩_∩*)′

--------`(*∩_∩*)′

`(*∩_∩*)′建立索引

create index myindex on emp(deptno);create index myindex2 on emp(deptno, job);索引說明

主鍵本身就是索引

唯一約束也是自動(dòng)索引

=========

同義詞

create synonym em for emp;

管理給scott用戶查詢權(quán)限

ALTER USER “HR” ACCOUNT UNLOCK GRANT SELECT ON “HR”.“EMPLOYEES” TO “SCOTT”

select * from hr.employees;SQL> select count(*)from hr.employees;

為hr.employees創(chuàng)建一個(gè)別名

create synonym hremp for hr.employees;* 第 1 行出現(xiàn)錯(cuò)誤: ORA-01031: 權(quán)限不足

---dba管理員分配權(quán)限

grant create synonym to scott;GRANT CREATE ANY SYNONYM TO “SCOTT” select count(*)from hremp;

圖像界面熟悉 PK 命令行工具

第二篇：黑馬程序員c語言教程：Oracle概念（推薦）

七、在執(zhí)行SQL語句時(shí)綁定變量

1.接收和定義變量的SQL*PLUS命令 ACCEPT DEFINE UNDEFINE & 2.綁定變量SQL語句的例子(1)SQL> select id, last_name, salary from s_emp where dept_id = &department_number;Enter value for department_number: 10 old 1: select id, last_name, salary from s_emp where dept_id=&department_number;new 1: select id, last_name, salary from s_emp where dept_id= 10 SQL> SET VERIFY OFF | ON;可以關(guān)閉和打開提示確認(rèn)信息old 1和new 1的顯示.3.綁定變量SQL語句的例子(2)SQL> select id, last_name, salary from s_emp where title = '&job_title';Enter value for job_title: Stock Clerk 11

J2EE @ zxw SQL> select id, last_name, salary from s_emp where hiredate >to_date('&start_hire_date','YYYY-MM-DD');Enter value for start_hire_date : 2001-01-01 把綁定字符串和日期類型變量時(shí)，變量外面要加單引號(hào) 也可綁定變量來查詢不同的字段名 輸入變量值的時(shí)候不要加;等其它符號(hào) 4.ACCEPT的語法和例子

SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE] 說明： variable 指變量名 datatype 指變量類型，如number,char等 format 指變量顯示格

式 prompt text 可自定義彈出提示符的內(nèi)容text hide 隱藏用戶的輸入符號(hào) 使用ACCEPT的例子：

ACCEPT p_dname PROMPT 'Provide the department name: ' ACCEPT p_salary NUMBER PROMPT 'Salary amount: ' ACCEPT pswd CHAR PROMPT 'Password: ' HIDE ACCEPT low_date date format 'YYYY-MM-DD' PROMPT“Enter the low date range('YYYY-MM-DD'):” 4.DEFINE的語法和例子

SQL> DEFINE variable = value 說明： variable 指變量名 value 指變量值

定義好了變良值后, 執(zhí)行綁定變量的SQL語句時(shí)不再提示輸入變量

使用DEFINE的例子：

SQL> DEFINE dname = sales SQL> DEFINE dname DEFINE dname = “sales”(CHAR)SQL> select name from dept where lower(name)='&dname';NAME-------------------------sales sales SQL> UNDEFINE dname SQL> DEFINE dname Symbol dname is UNDEFINED 5.SQL*PLUS里傳遞參數(shù)到保存好的*.sql文件里 SQL> @ /路徑名/文件名 參數(shù)名1[,參數(shù)名2, ?.] SQL> start /路徑名/文件名 參數(shù)名1[,參數(shù)名2, ?.] 注意事項(xiàng): 一次最多只能獲取9個(gè)&變量, 變量名稱只能是從&1,&2到&9 變量名后不要加特殊的結(jié)束符號(hào)

如果在SQL*PLUS里要把&符號(hào)保存在ORACLE數(shù)據(jù)庫里,要修改sql*plus環(huán)境變量define SQL> set define off;

八、概述數(shù)據(jù)模型和數(shù)據(jù)庫設(shè)計(jì) 1.系統(tǒng)開發(fā)的階段: Strategy and Analysis Design Build and Document Transition Production 12

J2EE @ zxw 2.數(shù)據(jù)模型

Model of system in client's mind Entity model of client's model Table model of entity model Tables on disk 3.實(shí)體關(guān)系模型(ERM)概念

ERM(entity relationship modeling)實(shí)體 存有特定信息的目標(biāo)和事件 例如: 客戶,訂單等 屬性 描述實(shí)體的屬性 例如: 姓名,電話號(hào)碼等 關(guān)系 兩個(gè)實(shí)體間的關(guān)系 例如:訂單和產(chǎn)品等 實(shí)體關(guān)系模型圖表里的約定

Dashed line(虛線)可選參數(shù) “may be”

Solid line(實(shí)線)必選參數(shù) “must be”

Crow's foot(多線)程度參數(shù) “one or more”

Single line(單線)程度參數(shù) “one and only one” 4.實(shí)體關(guān)系模型例子

每個(gè)訂單都必須有一個(gè)或幾個(gè)客戶

每個(gè)客戶可能是一個(gè)或幾個(gè)訂單的申請(qǐng)者 5.實(shí)體關(guān)系的類型

1:1 一對(duì)一 例如: 的士和司機(jī) M:1 多對(duì)一 例如: 乘客和飛機(jī) 1:M 一對(duì)多 例如: 員工和技能 6.校正實(shí)體關(guān)系的原則

屬性是單一值的, 不會(huì)有重復(fù)

屬性必須依存于實(shí)體, 要有唯一標(biāo)記

沒有非唯一屬性依賴于另一個(gè)非唯一的屬性 7.定義結(jié)構(gòu)時(shí)的注意事項(xiàng) 減少數(shù)據(jù)冗余

減少完整性約束產(chǎn)生的問題 確認(rèn)省略的實(shí)體,關(guān)系和屬性 8.完整性約束的要求

Primary key 主關(guān)鍵字 唯一非NULL Foreign key 外鍵 依賴于另一個(gè)Primary key,可能為NULL Column 字段名 符合定義的類型和長度

Constraint 約束條件 用戶自定義的約束條件,要符合工作流要求 例如: 一個(gè)銷售人員的提成不能超過它的基本工資

Candidate key 候選主關(guān)鍵字 多個(gè)字段名可組成候選主關(guān)鍵字, 其組合是唯一和非NULL的

9.把實(shí)體關(guān)系圖映射到關(guān)系數(shù)據(jù)庫對(duì)象的方法 把簡單實(shí)體映射到數(shù)據(jù)庫里的表

把屬性映射到數(shù)據(jù)庫里的表的字段, 標(biāo)明類型和注釋 把唯一標(biāo)記映射到數(shù)據(jù)庫里的唯一關(guān)鍵字 把實(shí)體間的關(guān)系映射到數(shù)據(jù)庫里的外鍵 13

J2EE @ zxw 其它的考慮: 設(shè)計(jì)索引,使查詢更快

建立視圖,使信息有不同的呈現(xiàn)面, 減少復(fù)雜的SQL語句 計(jì)劃存儲(chǔ)空間的分配 重新定義完整性約束條件

10.實(shí)體關(guān)系圖里符號(hào)的含義 PK 唯一關(guān)鍵字的字段 FK 外鍵的字段

FK1,FK2 同一個(gè)表的兩個(gè)不同的外鍵

FK1,FK1 兩個(gè)字段共同組成一個(gè)外鍵 NN 非null字段 U 唯一字段

U1,U1 兩個(gè)字段共同組成一個(gè)唯一字段

九、創(chuàng)建表

1.ORACLE常用的字段類型 ORACLE常用的字段類型有

VARCHAR2(size)可變長度的字符串, 必須規(guī)定長度 CHAR(size)固定長度的字符串, 不規(guī)定長度默認(rèn)值為１

NUMBER(p,s)數(shù)字型p是位數(shù)總長度, s是小數(shù)的長度, 可存負(fù)數(shù) 最長38位.不夠位時(shí)會(huì)四舍五入.DATE 日期和時(shí)間類型

LOB 超長字符, 最大可達(dá)4G CLOB 超長文本字符串 BLOB 超長二進(jìn)制字符串

BFILE 超長二進(jìn)制字符串, 保存在數(shù)據(jù)庫外的文件里是只讀的.數(shù)字字段類型位數(shù)及其四舍五入的結(jié)果 原始數(shù)值1234567.89 數(shù)字字段類型位數(shù) 存儲(chǔ)的值 Number 1234567.89 Number 12345678 Number 錯(cuò)

Number(9,1)1234567.9 Number(9,3)錯(cuò) Number(7,2)錯(cuò)

Number(5,-2)1234600 Number(5,-4)1230000 Number(*,1)1234567.9 2.創(chuàng)建表時(shí)給字段加默認(rèn)值 和約束條件 創(chuàng)建表時(shí)可以給字段加上默認(rèn)值

例如 : 日期字段 DEFAULT SYSDATE 這樣每次插入和修改時(shí), 不用程序操作這個(gè)字段都能得到動(dòng)作的時(shí)間 14

J2EE @ zxw 創(chuàng)建表時(shí)可以給字段加上約束條件 例如: 非空 NOT NULL 不允許重復(fù) UNIQUE 關(guān)鍵字 PRIMARY KEY 按條件檢查 CHECK(條件)外鍵 REFERENCES 表名(字段名)3.創(chuàng)建表的例子

CREATE TABLE DEPT（EPTNO NUMBER(2)CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13));CREATE TABLE region(ID number(2)NOT NULL PRIMARY KEY, postcode number default '0' NOT NULL, areaname varchar2(30)default ' ' NOT NULL);4.創(chuàng)建表時(shí)的命名規(guī)則和注意事項(xiàng)

表名和字段名的命名規(guī)則：必須以字母開頭，可以含符號(hào)A-Z,a-z,0-9,_,$,# 大小寫不區(qū)分

不用SQL里的保留字, 一定要用時(shí)可用雙引號(hào)把字符串括起來． 用和實(shí)體或?qū)傩韵嚓P(guān)的英文符號(hào)長度有一定的限制 注意事項(xiàng): 建表時(shí)可以用中文的字段名, 但最好還是用英文的字段名

創(chuàng)建表時(shí)要把較小的不為空的字段放在前面, 可能為空的字段放在后面 建表時(shí)如果有唯一關(guān)鍵字或者唯一的約束條件，建表時(shí)自動(dòng)建了索引 一個(gè)表的最多字段個(gè)數(shù)也是有限制的，254個(gè).5.約束名的命名規(guī)則和語法

約束名的命名規(guī)則約束名如果在建表的時(shí)候沒有指明，系統(tǒng)命名規(guī)則是SYS_Cn(n是數(shù)字)約束名字符串的命名規(guī)則同于表和字段名的命名規(guī)則 6.使用約束時(shí)的注意事項(xiàng)

約束里不能用系統(tǒng)函數(shù),如SYSDATE和別的表的字段比較 可以用本表內(nèi)字段的比較

想在事務(wù)處理后, 做約束的檢查

SQL> alter session set constraints deferred.7.由實(shí)體關(guān)系圖到創(chuàng)建表的例子 s_dept 前提條件:已有region表且含唯一關(guān)鍵字的字段id SQL> CREATE TABLE s_dept(id NUMBER(7)CONSTRAINT s_dept_id_pk PRIMARY KEY, name VARCHAR2(25)CONSTRAINT s_dept_name_nn NOT NULL, region_id NUMBER(7)CONSTRAINT s_dept_region_id_fk REFERENCES region(id), CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));8.較復(fù)雜的創(chuàng)建表例子 15

J2EE @ zxw SQL> CREATE TABLE s_emp(id NUMBER(7)CONSTRAINT s_emp_id_pk PRIMARY KEY, last_name VARCHAR2(25)CONSTRAINT s_emp_last_name_nn NOT NULL，first_name VARCHAR2(25), userid VARCHAR2 CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, start_date DATE DEFAULT SYSDATE, comments VARCHAR2(25), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7)CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id), salary NUMBER(11,2), commission_pct NUMBER(4,2)CONSTRAINT s_emp_commission_pct_ck CHECK(commission_pct IN(10,12.5,15,17.5,20)));

第三篇：黑馬程序員c語言教程：Oracle概念

一、選擇行

1.簡單的SELECT 語句

SELECT 字段名1 [AS] '字段名1 解釋' FROM table;2.處理NULL NVL函數(shù)可把NULL轉(zhuǎn)換成其它類型的符號(hào)

編程技巧: NVL函數(shù)在多條件模糊查詢的時(shí)候比較有用 NVL函數(shù)可返回多種數(shù)據(jù)類型: 返回日期 NVL(start_date,'2002-02-01')返回字符串 NVL(title,'no title')返回?cái)?shù)字 NVL(salary,1000)3.使用SQL*PLUS（1）

SQL> desc table;顯示表結(jié)構(gòu)

SQL> select * from tab;查看用戶下所有的表

SQL> set pause on;可以使大量結(jié)果集在用戶按“Enter”(回車)后翻頁

SQL> set pagesize 100;設(shè)定SQL語句返回結(jié)果集一頁的行數(shù)100, 默認(rèn)值是14 SQL> set linesize 100;設(shè)定SQL語句返回結(jié)果集一行的寬度100, 默認(rèn)值是80 4.SQL*PLUS里規(guī)定字段的顯示格式 規(guī)定數(shù)字的顯示格式

SQL>column 字段名 format 99999999999;SQL>column 字段名 format 999,999,999,999;SQL>column 字段名 format a數(shù)字 [word_wrapped];規(guī)定long字符的顯示寬度 SQL>set long 200;規(guī)定字段名的顯示內(nèi)容

SQL> column 字段名 heading '字段名顯示內(nèi)容';SQL> set heading off;查詢時(shí)不顯示字段名稱 規(guī)定字段的對(duì)齊方向

SQL> column 字段名 justify [left | right | center];清除字段的格式

SQL> column 字段名 clear;5.SQL*PLUS里規(guī)定字段的顯示格式例子

SQL> column last_name heading 'Employee|Name' format a15;SQL> column salary justify right format $99,999.99;SQL> column start_date format a10 null 'Not Hired';說明：如果start_date為null, 顯示字符串'Not Hired' 6.判斷題(T/F)(1).SQL command are always held in sql buffer.[T](2).SQL*PLUS command assit with query data.[T] 5

J2EE @ zxw SQL*PLUS命令只控制SELECT結(jié)果集的顯示格式及控制文件.只有SQL命令能訪問數(shù)據(jù)庫.二、限制選擇行

1.按指定的規(guī)則排序

SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];默認(rèn)的排序是ASC升序(由小到大)還可以O(shè)RDER BY 字段名的位置[1]| [2] ASC| DESC;2.用WHERE限制選擇行(1)比較操作符 = ><>= <=!= <> ^= 與NULL比較不能用上面的比較操作符 ANY SOME ALL SQL操作符 BETWEEN ? AND? IN LIKE IS NULL NOT BETWEEN ? AND? NOT IN NOT LIKE IS NOT NULL 邏輯操作符 AND OR NOT 3.用WHERE限制選擇行(2)比較順序(可以用括號(hào)改變它們的順序)(1).= <>>= <= in like is null between(2).and(3).Or 4.LIKE操作

% 零到任意多個(gè)字符 _ 一個(gè)字符

例如: 字段名 like 'M%' 字段名 like '%m%' 字段名 like 'job_' 如果要找含下劃線的字符, 要加反斜線 例如:字段名 like '%X/_Y%' escape '/' 5.日期字段的比較 舉例: 日期字段 between to_date('2001-12-12','YYYY-MM-DD')and to_date('2002-02-01','YYYY-MM-DD')日期字段> to_date('2001-12-12','YYYY-MM-DD')and日期字段<= to_date('2002-02-01','YYYY-MM-DD');6.不能用到索引的比較操作符 IS NULL IS NOT NULL LIKE '%m%'

三、單行函數(shù) 1.數(shù)字函數(shù)

ABS 取絕對(duì)值 POWER 乘方 LN 10為底數(shù)取0 SQRT平方根 EXP e的n次乘方 LOG(m,n)m為底數(shù)n取0 數(shù)學(xué)運(yùn)算函數(shù):ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH CEIL 大于或等于取整數(shù) FLOOR 小于或等于取整數(shù) MOD 取余數(shù)

ROUND(n,m)按m的位數(shù)取四舍五入值如果round(日期): 中午12以后將是明天的日期.round(sysdate,'Y')是年的第一天

TRUNC(n,m)按m的位數(shù)取小數(shù)點(diǎn)后的數(shù)值如果trunc(日期), 確省的是去掉時(shí)間

J2EE @ zxw 2.字符函數(shù)

CHR 按數(shù)據(jù)庫的字符集由數(shù)字返回字符

CONCAT(c1,c2)把兩個(gè)字符c1,c2組合成一個(gè)字符, 和 || 相同 REPLACE(c,s,r)把字符c里出現(xiàn)s的字符替換成r, 返回新字符

SUBSTR(c,m,n)m大于0,字符c從前面m處開始取n位字符,m等于0和1一樣, m小與0,字符c從后面m處開始取n位字符

TRANSLATE(c,f1,t1)字符c按f1到t1的規(guī)則轉(zhuǎn)換成新的字符串 INITCAP 字符首字母大寫,其它字符小寫 LOWER 字符全部小寫 UPPER 字符全部大寫

LTRIM(c1,c2)去掉字符c1左邊出現(xiàn)的字符c2 RTRIM(c1,c2)TRIM(c1,c2)去掉字符c1左右兩邊的字符c2 LPAD(c1,n,c2)字符c1按制定的位數(shù)n顯示不足的位數(shù)用c2字符串替換左邊的空位 RPAD(c1,n,c2)3.日期函數(shù)

ADD_MONTHS(d,n)日期值加n月

LAST_DAY 返回當(dāng)月的最后一天的日期

MONTHS_BETWEEN(d1,d2)兩個(gè)日期值間的月份,d1

DUAL是SYS用戶下一個(gè)空表，它只有一個(gè)字段dummy 4.轉(zhuǎn)換函數(shù)(1)TO_CHAR(date,'日期顯示格式')TO_CHAR(number)用于顯示或報(bào)表的格式對(duì)齊 TO_DATE(char,'日期顯示格式')TO_LOB 把long字段轉(zhuǎn)換成lob字段

TO_NUMBER(char)用于計(jì)算或者比較大小 4.轉(zhuǎn)換函數(shù)(2)to_date里日期顯示格式 YYYY 年 YEAR YYY YY Y Q 季度

MM 月 MONTH MON W 星期(week of month)WW, IW(week of year)(說明：周計(jì)是按ISO標(biāo)準(zhǔn),從1月1日的星期數(shù)到后面七天為一周,不一定是從周一到周日)DD 日 DAY DY HH24 小時(shí) HH12 HH MI 分鐘 SS 秒

如果想固定日期的顯示格式可以在數(shù)據(jù)庫的參數(shù)文件initorasid.ora里新寫一行參數(shù)

NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss可以在UNIX環(huán)境變量或者NT的注冊(cè)表里的設(shè)置 NLS_DATE_FORMAT=yyyy-mm-dd 7

J2EE @ zxw hh24:mi:ss 4.轉(zhuǎn)換函數(shù)(3)如果想固定日期的顯示格式可以用alter session命令改變

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';它的作用順序如下: initialization parameter Environment variable ALTER SESSION command 4.轉(zhuǎn)換函數(shù)(4)to_char(number)里數(shù)字顯示格式 9 數(shù)字位

0 數(shù)字前面補(bǔ)0 to_char(-1200,'00000.00').小數(shù)點(diǎn)的位置 , 標(biāo)記位置的逗號(hào) 用在數(shù)字顯示格式的左邊

L 根據(jù)數(shù)據(jù)庫字符集加貨幣符號(hào) to_char(-1200,'L9999.99')B 把數(shù)字0顯示為空格,用在數(shù)字顯示格式的右邊 MI 右邊的負(fù)數(shù)標(biāo)記 to_char(-1200,'9999.99MI')PR 括起來的負(fù)數(shù) to_char(-1200,'9999.99PR')EEEE 用指數(shù)方式表示 to_char(-1200,'9999.99EEEE')5.輸入字符,返回?cái)?shù)字的函數(shù)

instr(c1,c2)字符c2出現(xiàn)在c1的位置, 不出現(xiàn), 返回0, 常用于模糊查詢

length 按數(shù)據(jù)庫的字符集,算出字符c的長度,跟數(shù)據(jù)庫的字符集有關(guān), 一個(gè)漢字長度為1 6.有邏輯比較的函數(shù)NVL(EXPR1, EXPR2)函數(shù) 解釋: IF EXPR1=NULL RETURN EXPR2 ELSE RETURN EXPR1 DECODE(AA0V10R10V20R2....)函數(shù) 解釋: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2..? ELSE RETURN NULL 舉例: decode(id,1,'dept sale',2,'dept tech')

第四篇：黑馬程序員C語言教程：mysql中的索引

MySQL數(shù)據(jù)庫中的索引

在數(shù)據(jù)庫操作中，經(jīng)常需要查找特定的數(shù)據(jù)，例如：當(dāng)執(zhí)行“select * from student where id = 10000”語句時(shí)，MySQL數(shù)據(jù)庫必須從第一條記錄開始遍歷，直到找到id為10000的數(shù)據(jù)。這樣的效率非常低。為此，MySQL允許建立索引來加快數(shù)據(jù)表的查詢和排序。

索引的概念

數(shù)據(jù)庫的索引好比字典的目錄，是對(duì)數(shù)據(jù)庫表中一列或者多了的值進(jìn)行排序后的一種結(jié)構(gòu)，其作用就是提高表中的數(shù)據(jù)查詢速度。MySQL中的索引分為很多種，具體如下。

1.普通索引

普通索引是由key或index定義個(gè)索引，它是MySQL中的基本索引類型，可以創(chuàng)建在任何數(shù)據(jù)類型中。其值是否唯一和非空有字段本身的約束條件所決定。例如，在student表的id字段上建立一個(gè)普通索引，查詢記錄時(shí)，就可以根據(jù)該索引查詢，從而提高效率。

2.唯一性索引

唯一性索引是指由unique定義個(gè)索引，該索引所在字段的值必須是唯一的。例如，在grade表的stu_id字段上建立唯一性索引，那么stu_id字段的值就必須是唯一的。

3.全文索引

全文索引是由fulltext定義的索引，它只能創(chuàng)建在char、varchar或text類型的字段上。并且現(xiàn)在只有MyISAM存儲(chǔ)引擎支持全文索引。

4.單列索引

單列索引指的是在表中單個(gè)字段上創(chuàng)建索引，它可以是普通索引、唯一索引或者全文索引，只有保證該索引只對(duì)應(yīng)表中一個(gè)字段即可。

5.多列索引

多列索引是指在表的多個(gè)字段上創(chuàng)建索引，只有在查詢條件中使用了這些字段中的第一個(gè)字段時(shí)，該索引才會(huì)被使用。如，在student表的id、name和score字段上創(chuàng)建一個(gè)多列索引，那么只有查詢條件中使用了id字段時(shí)，該索引才會(huì)被使用。

相較于單列索引，當(dāng)我們頻繁的需要同時(shí)檢索表中多列時(shí)，多列索引的效率會(huì)高很多。6.空間索引

空間索引是由spatial定義的索引，它只能創(chuàng)建在空間數(shù)據(jù)類型的字段上。MySQL中的空間數(shù)據(jù)類型有4種：geometry、point、linestring和polygon。需要注意的是，創(chuàng)建空間索引的字段，必須將其聲明為NOT NULL，并且空間索引只能在存儲(chǔ)引擎為MyISAM的表中創(chuàng)建。

需要注意的是，雖然索引可以提高數(shù)據(jù)的查詢效率，但索引會(huì)占用一定的存儲(chǔ)空間。并且創(chuàng)建和維護(hù)索引所消耗的時(shí)間，是隨著數(shù)據(jù)量的增加而增加的。因此，使用索引時(shí)，應(yīng)綜合考慮其優(yōu)缺點(diǎn)，不能肆意創(chuàng)建。

創(chuàng)建索引

要想使用索引提高數(shù)據(jù)表的訪問速度，首先要?jiǎng)?chuàng)建一個(gè)索引。創(chuàng)建索引的方式大致可分為三種。

創(chuàng)建表的同時(shí)創(chuàng)建索引

創(chuàng)建表的時(shí)候可以直接創(chuàng)建索引，這種方式最簡單、方便，其基本語法格式如下所示：

create table 表名(字段名數(shù)據(jù)類型 [完整性約束條件]，字段名數(shù)據(jù)類型 [完整性約束條件], ??);

字段名數(shù)據(jù)類型

[unique|fulltext|spatial] index|key [別名](字段名1 [(長度)])[asc|desc] 關(guān)于上述語法相關(guān)解釋具體如下： 1)unique：可選參數(shù)，表示唯一索引。2)fulltext：可選參數(shù)，表示全文索引。3)spatial：可選參數(shù)，表示空間索引

4)index和key：用來表示字段的索引，二者選一即可。5)別名：可選參數(shù)，表示穿件的索引名稱。6)字段名1：指定索引對(duì)應(yīng)字段的名稱。7)長度：可選參數(shù)，用于表示索引的長度。

8)asc和desc：可選參數(shù)。asc表升序，desc表降序排列。

MySQL中的6種索引類型，如下： 1)創(chuàng)建普通索引

【例】在t1表中id字段上創(chuàng)建索引，SQL語句如下：

create table t1(id int, name varchar(20), score float，);index(id)可使用explain語句查看索引是否被使用，SQL語句如下：

explain select * from t1 where id = 1;2)創(chuàng)建唯一性索引

【例】創(chuàng)建一個(gè)表名為t2的表，在表中的id字段上建立索引名為unique_id的唯一性索引，并按升序排列，SQL語句如下：

create table t2(id int not null，);name varchar(20)not null, score float, unique index unique_id(id asc)這樣，便在id字段上建立了一個(gè)名為unique_id的唯一性索引。3)創(chuàng)建全文索引

【例】創(chuàng)建一個(gè)表名為t3的表，在表中的name字段上建立索引名為fulltext_name的全文索引，SQL語句如下：

create table t3(id int not null，name varchar(20)not null, score float, fulltextindex fulltext_name(name))engine=MyISAM;這樣，即可在name字段上建立一個(gè)名為fulltext_name的全文索引。

需要注意的是，由于目前只有MyISAM存儲(chǔ)引擎支持全文索引，默認(rèn)的InnoDB存儲(chǔ)引擎不支持全文索引。因此，在建立全文索引時(shí)，一定要注意表存儲(chǔ)引擎的類型，對(duì)于經(jīng)常需要索引的字符串、文字?jǐn)?shù)據(jù)等信息，可以考慮存儲(chǔ)到MyISAM存儲(chǔ)引擎的表中。

4)創(chuàng)建單列索引

【例】創(chuàng)建一個(gè)表名為t4的表，在表中的name字段上建立索引名為single_name的單列索引，SQL語句如下：

create table t4(id int not null，);name varchar(20)not null, score float, index single_name(name(20))這樣，即可在name字段上建立一個(gè)名稱為single_name的單列索引，并且索引的長度為20。

5)創(chuàng)建多列索引

【例】創(chuàng)建一個(gè)表名為 t5的表，在表中的id和name字段上建立索引名為multi的多列索引，SQL語句如下：

create table t5(id int not null，);name varchar(20)not null, score float, indexnulti(id, name(20))這樣，即可在id和name字段上建立一個(gè)名為multi的多列索引。

需要注意的是，在多列索引中，只有查詢條件中使用了這些字段中的第一個(gè)字段時(shí)多列索引才會(huì)被使用。

為了驗(yàn)證這個(gè)說法是否正確，將id字段作為查詢條件，通過explain語句查可看索引的使用情況，SQL語句如下：

explain select * from t5 where id = 1;但是，如果只使用name字段作為查詢條件，multi索引不會(huì)被使用。6)創(chuàng)建空間索引

【例】創(chuàng)建一個(gè)表名為t6的表，在空間類型為geometry的字段上創(chuàng)建空間索引，SQL語句如下：

create table t6(id int，space geometry not null, spatial index sp(space))engine=MyISAM;這樣，即可在t6表中的space字段上建立名稱為sp的空間索引了。

需要注意的是，創(chuàng)建空間索引時(shí)，所在字段的值不能為空值，并且表的存儲(chǔ)引擎為MyISAM。

使用 create index 語句在已經(jīng)存在的表上創(chuàng)建索引

若想在一個(gè)已經(jīng)存在的表上創(chuàng)建索引，可以使用 create index語句，其創(chuàng)建索引的具體語法格式如下所示：

create [unique|fulltext|spatial] index 索引名 on表名(字段名 [(長度)] [asc|desc]);在上述語法格式中，unique、fulltext和spatial都是可選參數(shù)，分別用于表示唯一性索引、全文索引和空間索引；index用于指明字段為索引。

為了更好的展示如何使用create index語句在已經(jīng)存在的表上創(chuàng)建索引，接下來創(chuàng)建一個(gè)book表，該表中沒有建立任何索引，創(chuàng)建book表的SQL語句如下：

create table book（bookid int not null, bookname varchar(255)not null, authors varchar(255)not null, info varchar(255)null, comment varchar(255)null, publicyear year not null);創(chuàng)建好數(shù)據(jù)表book后，通過具體案例演示如何使用create index語句在已經(jīng)存在的數(shù)據(jù)表中創(chuàng)建索引，具體如下：

1)創(chuàng)建普通索引

【例】在book表中的bookid字段上建立一個(gè)名稱為index_id的普通索引，SQL語句如下：

create index index_id on book(bookid);這樣，即可在book表中，為bookid字段建立一個(gè)名稱為index_id的普通索引。2)創(chuàng)建唯一性索引

【例】在book表中的bookid字段上建立一個(gè)名稱為uniqueidx的唯一性索引，SQL語句如下：

create unique index uniqueidx on book(bookid);這樣，即可在book表中，為bookid字段建立一個(gè)名稱為uniqueidx的唯一性索引。3)創(chuàng)建單列索引

【例】在book表中的comment字段上建立一個(gè)名稱為singleidx的單列索引，SQL語句如下所示：

create indexsingleidx on book(comment);這樣，即可在book表中，為comment字段建立一個(gè)名稱為singleidx的單列索引。4)創(chuàng)建多列索引

【例】在book表中的authors字段和info字段上建立一個(gè)名稱為mulitidx的多列索引，SQL語句如下所示：

create index mulitidxon book(authors(20), info(20));這樣，即可在book表中，為authors和info字段建立一個(gè)名稱為mulitidx的多列索引。5)創(chuàng)建全文索引

【例】刪除表book，重新創(chuàng)建表book，在book表中的info字段上建立全文索引。首先刪除book表。SQL語句如下所示：

drop table book;然后重新創(chuàng)建表book，SQL語句如下所示：

create table book（bookid int not null, bookname varchar(255)not null, authors varchar(255)not null, info varchar(255)null, comment varchar(255)null, publicyear year not null)engine=MyISAM;接下來使用create index 語句在book表的info字段上創(chuàng)建名稱為fulltextidx的全文索引，SQL語句如下所示：

create fulltext indexfulltextidx on book(info);這樣，即可在book表中，為info字段建立一個(gè)名稱為fulltextidx的全文索引。6)創(chuàng)建空間索引

【例】創(chuàng)建表t7，在表中的g字段上創(chuàng)建名稱為spatialidx的空間索引。首先創(chuàng)建數(shù)據(jù)表t7，SQL語句如下所示：

create table t7(g geometry not null)engine=MyISAM;使用create index 語句在t7表的g字段上，創(chuàng)建名稱為spatialidx的空間索引，SQL語句如下所示：

create spatial index spatialidx on t7(g);這樣，即可在t7表中，為g字段建立一個(gè)名稱為spatialidx的空間索引。

使用alter table語句在已經(jīng)存在的表上創(chuàng)建索引

在已經(jīng)存在的表中創(chuàng)建索引，除了可以使用create index語句外，還可以使用alter table語句來完成。其語法格式：

alter table 表名 add [unique|fulltext|spatial] index 索引名(字段名 [(長度)] [asc|desc])在上述語法格式中，unique、fulltext和spatial都是可選參數(shù)，分別用于表示唯一性索引、全文索引和空間索引；add表示向表中添加字段。

接下來，同樣以book表為例，對(duì)不同類型的索引進(jìn)行說明，為了使book表不包含任

何索引，首先刪除book表，SQL語句如下：

drop table book;然后重新建立book表，SQL語句如下：

create table book（bookid int not null, bookname varchar(255)not null, authors varchar(255)not null, info varchar(255)null, comment varchar(255)null, publicyear year not null);創(chuàng)建好數(shù)據(jù)表book后，就可以使用alter table語句在已存在的數(shù)據(jù)表中創(chuàng)建索引了，具體如下：

1)創(chuàng)建普通索引

【例】在表中的bookid字段上創(chuàng)建名稱為index_id的普通索引，SQL語句如下：

alter table book add index index_id(bookid);這樣，即可在book表中，為bookid字段建立一個(gè)名稱為index_id的普通索引。2)創(chuàng)建唯一性索引

【例】在book表中的bookid字段上建立一個(gè)名稱為uniqueidx的唯一性索引，SQL語句如下：

alter table book add unique uniqueidx(bookid);這樣，即可在book表中，為bookid字段建立一個(gè)名稱為uniqueidx的唯一性索引。3)創(chuàng)建單列索引

【例】在book表中的comment字段上建立一個(gè)名稱為singleidx的單列索引，SQL語句如下所示：

alter table book add indexsingleidx(comment(50));這樣，即可在book表中，為comment字段建立一個(gè)名稱為singleidx的單列索引。4)創(chuàng)建多列索引

【例】在book表中的authors字段和info字段上建立一個(gè)名稱為mulitidx的多列索引，SQL語句如下所示：

alter table book add indexmultidx(authors(20), info(50));這樣，即可在book表中，為authors和info字段建立一個(gè)名稱為mulitidx的多列索引。

5)創(chuàng)建全文索引

【例】刪除表book，重新創(chuàng)建表book，在book表中的info字段上建立全文索引。首先刪除book表。SQL語句如下所示：

drop table book;然后重新創(chuàng)建表book，SQL語句如下所示：

create table book（bookid int not null, bookname varchar(255)not null, authors varchar(255)not null, info varchar(255)null, comment varchar(255)null, publicyear year not null)engine=MyISAM;接下來使用alter table語句在book表的info字段上創(chuàng)建名稱為fulltextidx的全文索引，SQL語句如下所示：

alter table book add fulltext indexfulltextidx(info);這樣，即可在book表中，為info字段建立一個(gè)名稱為fulltextidx的全文索引。6)創(chuàng)建空間索引

【例】創(chuàng)建表t8，在表中的space字段上創(chuàng)建名稱為spatialidx的空間索引。首先創(chuàng)建數(shù)據(jù)表t8，SQL語句如下所示：

create table t8(space geometry not null)engine=MyISAM;使用alter table語句在t8表的space字段上，創(chuàng)建名稱為spatialidx的空間索引，SQL語句如下所示：

alter table t8 add spatial index spatialidx(space);這樣，即可在t8表中，為space字段建立一個(gè)名稱為spatialidx的空間索引。

刪除索引

由于索引會(huì)占用一定的磁盤空間，因此，為了避免影響數(shù)據(jù)庫性能，應(yīng)該及時(shí)刪除不再使用的索引。刪除索引的方法有兩種，如下：

使用alter table刪除索引

使用alter table刪除索引的基本語法格式如下所示：

alter table 表名 drop index 索引名

【例】刪除表book中名稱為fulltextidx的全文索引

alter table book drop index fulltextidx;上述SQL語句執(zhí)行后，可以使用show create table語句查看表結(jié)構(gòu)，來確認(rèn)索引是否已經(jīng)成功被刪除。

show create talbe book;使用dropindex刪除索引

使用dropindex刪除索引的基本語法格式如下所示：

drop index 索引名 on表名;【例】刪除表t8中名稱為spatialidx的空間索引，SQL語句如下：

drop index spatialidx on t8 使用show create table 語句查看表結(jié)構(gòu)

show create table t8 發(fā)現(xiàn)，t8表中名稱為spatialidx的索引被成功刪除了。

第五篇：黑馬程序員c語言教程：Oracle概念

四、從多個(gè)表里選取數(shù)據(jù)記錄 1.數(shù)據(jù)表間的連接 簡單的連接語法: SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 表名1.字段名 = 表名2.字段名 [ AND ……];SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 表名1.字段名 = 表名2.字段名(+)[ AND ……];有(+)號(hào)的字段位置自動(dòng)補(bǔ)空值 連接的分類: 等于的連接 = 不等于的連接!= BETWEEN … AND … IN 注意IN和OR不能一起用 8

J2EE @ zxw 外連接 有一個(gè)字段名(+), 沒有滿足的條件補(bǔ)空值 自連接 同一個(gè)表自己跟自己連接 例如找重復(fù)記錄 2.數(shù)據(jù)表間的連接例子

刪除table_name表里字段名email重復(fù)的記錄: SQL>delete from table_name t1 where t1.rowid >(select min(rowid)from table_name t2 where t1.email = t2.email group by email having count(email)> 1);找到手機(jī)用戶的服務(wù)區(qū)域: SQL> select a.handphoneno,nvl(c.name,'null'),a.totalscore from topscore a,chargeoperator cc,chargeoperatorinfo c where substr(a.handphoneno,1,7)=cc.hpnohead(+)and cc.chargetype=c.chargetype(+)order by a.totalscore desc;3.數(shù)據(jù)表間的連接技巧

連接N個(gè)表, 需要N-1個(gè)連接操作

被連接的表最好建一個(gè)單字符的別名, 字段名前加上這個(gè)單字符的別名 BETWEEN..AND..比用 >= AND <= 要好 連接操作的字段名上最好要有索引 連接操作的字段最好用整數(shù)數(shù)字類型

有外連接時(shí), 不能用OR或IN的比較操作 4.如何分析和執(zhí)行SQL語句

寫多表連接SQL語句時(shí)要知道它的分析執(zhí)行計(jì)劃的情況.Sys用戶下運(yùn)行@/ORACLE_HOME/sqlplus/admin/plustrce.sql 產(chǎn)生plustrace角色

Sys用戶下把此角色賦予一般用戶 SQL> grant plustrace to &username;一般用戶下運(yùn)行@/ORACLE_HOME/rdbms/admin/utlxplan.sql

產(chǎn)生plan_table SQL> set time on;說明：打開時(shí)間顯示

SQL> set autotrace on;說明：打開自動(dòng)分析統(tǒng)計(jì)，并顯示SQL語句的運(yùn)行結(jié)果 SQL> set autotrace traceonly;說明：打開自動(dòng)分析統(tǒng)計(jì)，不顯示SQL語句的運(yùn)行結(jié)果

接下來你就運(yùn)行測試SQL語句，看到其分析統(tǒng)計(jì)結(jié)果了。一般來講，我們的SQL語句應(yīng)該避免大表的全表掃描。SQL> set autotrace off;說明：關(guān)閉自動(dòng)分析統(tǒng)計(jì)

五、集合函數(shù)

經(jīng)常和group by一起使用 1.集合函數(shù)列表

AVG(DISTINCT | ALL | N)取平均值

COUNT(DISTINCT | ALL | N | expr | *)統(tǒng)計(jì)數(shù)量 MAX(DISTINCT | ALL | N)取最大值 MIN(DISTINCT | ALL | N)取最小值 SUM(DISTINCT | ALL | N)取合計(jì)值 9

J2EE @ zxw STDDEV(DISTINCT | ALL | N)取偏差值,如果組里選擇的內(nèi)容都相同,結(jié)果為0 VARIANCE(DISTINCT | ALL | N)取平方偏差值 2.使用集合函數(shù)的語法

SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column;3.使用count時(shí)的注意事項(xiàng)

SELECT COUNT(*)FROM table;SELECT COUNT(常量)FROM table;都是統(tǒng)計(jì)表中記錄數(shù)量,如果沒有PK后者要好一些 SELECT COUNT(all 字段名)FROM table;SELECT COUNT(字段名)FROM table;不會(huì)統(tǒng)計(jì)為NULL的字段的數(shù)量

SUM,AVG時(shí)都會(huì)忽略為NULL的字段 4.用group by時(shí)的限制條件

SELECT字段名不能隨意, 要包含在GROUP BY的字段里 GROUP BY后ORDER BY時(shí)不能用位置符號(hào)和別名 限制GROUP BY的顯示結(jié)果, 用HAVING條件 5.例子

SQL> select title,sum(salary)payroll from s_emp where title like 'VP%' group by title having sum(salary)>5000 order by sum(salary)desc;找出某表里字段重復(fù)的記錄數(shù), 并顯示

SQL> select(duplicate field names)from table_name

group by(list out fields)having count(*)>1;6.判斷題(T/F)(1)Group functions include nulls in calculations [F](2)Using the having clause to exclude rows from a group calculation [F] 解釋: Group function 都是忽略NULL值的 如果您要計(jì)算NULL值, 用NVL函數(shù)

Where語句在Group By前把結(jié)果集排除在外Having語句在Group By后把結(jié)果集排除在外

六、子查詢

1.查詢語句可以嵌套

例如: SELECT …… FROM(SELECT …… FROM表名1, [表名2, ……] WHERE 條件)WHERE 條件2;2.何處可用子查詢? 當(dāng)查詢條件是不確定的條件時(shí)

DML(insert, update,delete)語句里也可用子查詢 HAVING里也可用子查詢

3.兩個(gè)查詢語句的結(jié)果可以做集合操作 例如: 并集UNION(去掉重復(fù)記錄)并集UNION ALL(不去掉重復(fù)記錄)10

J2EE @ zxw 差集MINUS, 交集INTERSECT 4.子查詢的注意事項(xiàng)

先執(zhí)行括號(hào)里面的SQL語句，一層層到外面 內(nèi)部查詢只執(zhí)行一次

如果里層的結(jié)果集返回多個(gè)，不能用= ><>= <=等比較符要用IN.5.子查詢的例子(1)SQL> select title,avg(salary)from s_emp group by title Having avg(salary)=(select min(avg(salary))from s_emp group by title);找到最低平均工資的職位名稱和工資 5.子查詢的例子(2)子查詢可以用父查詢里的表名 這條SQL語句是對(duì)的: SQL>select cty_name from city where st_code in(select st_code from state where st_name='TENNESSEE' and city.cnt_code=state.cnt_code);說明：父查詢調(diào)用子查詢只執(zhí)行一次． 6.取出結(jié)果集的80 到100的SQL語句

ORACLE處理每個(gè)結(jié)果集只有一個(gè)ROWNUM字段標(biāo)明它的邏輯位置, 并且只能 用ROWNUM<100, 不能用ROWNUM>80。

以下是經(jīng)過分析后較好的兩種ORACLE取得結(jié)果集80到100間的SQL語句(ID是唯一關(guān)鍵字的字段名)語句寫法：

SQL>select * from((select rownum as numrow, c.* from(select [field_name,...] from table_name where 條件1 order by 條件2)c)where numrow > 80 and numrow <= 100)order by 條件3;