目錄

簡介

在Android中存儲(chǔ)數(shù)據(jù)有時(shí)會(huì)用到數(shù)據(jù)庫，Android給我們提供了 一系列的API來操作數(shù)據(jù)庫，非常簡單，我們只需要輸入對(duì)應(yīng)的SQL語句，甚至不懂SQL語句，只傳入對(duì)應(yīng)的參數(shù)即可使用。還有一些第三方庫，如GreenDao，OrmLite等都極大的簡化了對(duì)數(shù)據(jù)庫的一些操作。這樣雖然我們不需要對(duì)數(shù)據(jù)庫有多了解一樣能實(shí)現(xiàn)功能，但是在面對(duì)復(fù)雜操作時(shí)，對(duì)SQL語句的熟練使用就顯得尤為重要了。因?yàn)樵贏ndroid我們只要是利用SQL語句對(duì)表操作，所以本文主要介紹SQL的使用，只是簡單介紹了數(shù)據(jù)庫一些相關(guān)的概念，這樣能有更加清晰的認(rèn)識(shí)。

數(shù)據(jù)模型

按照計(jì)算機(jī)系統(tǒng)的觀點(diǎn)對(duì)數(shù)據(jù)進(jìn)行建模。

概念模型

也稱為信息模型，即按照用戶的觀點(diǎn)來對(duì)數(shù)據(jù)和信息建模

• 實(shí)體（Entity）：客觀存在并可互相進(jìn)行區(qū)分的事物稱為實(shí)體
• 屬性（Attribute）：實(shí)體所具有的某一特性稱為屬性，一個(gè)實(shí)體可有若干個(gè)屬性來刻畫。
• 鍵（KEY）：唯一標(biāo)識(shí)實(shí)體的屬性集稱為鍵，一般分為超鍵、候選鍵、主鍵以及外鍵。
• 域（Domain）：屬性的取值范圍稱為該屬性的域。
• 實(shí)體型（Entity type）：用實(shí)體名以及其屬性名集合來抽象和刻畫同類實(shí)體稱為實(shí)體型
• 實(shí)體集：同型實(shí)體的集合稱為實(shí)體集

現(xiàn)實(shí)世界的實(shí)體進(jìn)入數(shù)據(jù)庫

• 1.講現(xiàn)實(shí)世界中的客觀對(duì)象抽象為概念模型
• 2.把概念模型轉(zhuǎn)化為某一DBMS（數(shù)據(jù)庫管理系統(tǒng)）支持的數(shù)據(jù)模型

鍵的概念

• 超鍵（super key）：在關(guān)系中能唯一區(qū)分實(shí)體的屬性集稱為關(guān)系模式中的超鍵。
• 候選鍵（candidate key）：不含有多余屬性的超鍵（每一個(gè)屬性都能區(qū)分實(shí)體）
• 主鍵（primary key）：用戶選作區(qū)分實(shí)體發(fā)的一個(gè)候選鍵（foreign key）
• 外鍵：在某個(gè)關(guān)系A(chǔ)中的主鍵出現(xiàn)在另一個(gè)關(guān)系B中，此時(shí)關(guān)系A(chǔ)中的該主鍵稱為關(guān)系B中的外鍵。

例子說明： 員工（身份證號(hào)、姓名、年齡、性別）假設(shè)姓名也是唯一的超鍵：身份證號(hào)、姓名、姓名+年齡、姓名+性別、身份證號(hào)+年齡、身份證號(hào)+性別候選鍵：身份證號(hào)、姓名

數(shù)據(jù)庫設(shè)計(jì)范式

數(shù)據(jù)庫設(shè)計(jì)范式簡單的說就是關(guān)系數(shù)據(jù)庫在設(shè)計(jì)時(shí)需要遵循的一種規(guī)范，數(shù)據(jù)庫范式按照要求從低到高分為6大范式，即第一范式（1NF）~第六范式（6NF）。在第一范式的基礎(chǔ)上進(jìn)一步滿足更多要求的稱為第二范式，一次類推。越高的范式數(shù)據(jù)庫冗余越小，一般來說，數(shù)據(jù)庫只需要滿足第三范式（3NF）就行了。

第一范式

是數(shù)據(jù)庫設(shè)計(jì)的最基本的要求，不滿足1NF的數(shù)據(jù)庫不是關(guān)系型數(shù)據(jù)庫
所謂第一范式就是指數(shù)據(jù)庫表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中只能有一個(gè)屬性（也就是說一個(gè)屬性下不能再分出其他的屬性來）

第二范式

第二范式是在第一范式的基礎(chǔ)上建立起來的，即滿足2NF必須先滿足1NF
只出現(xiàn)在復(fù)合主鍵的數(shù)據(jù)庫表中
第二范式要求數(shù)據(jù)庫表中的每個(gè)實(shí)例或者行必須可以被唯一地區(qū)分，所以實(shí)體必須設(shè)置主鍵，并且實(shí)體的屬性必須完全依賴于主鍵，不得出現(xiàn)非主鍵屬性部分依賴于主鍵的情況。

主鍵為學(xué)號(hào)+課程名稱，而學(xué)分依賴于學(xué)號(hào)，這就是所謂的非主鍵屬性依賴于主鍵的情況，這是不符合2NF的，出現(xiàn)了數(shù)據(jù)的冗余（存儲(chǔ)多余的數(shù)據(jù)，浪費(fèi)空間），解決辦法就是拆成3張表。

第三范式

要求一個(gè)數(shù)據(jù)庫表中不包含已在其他表中已包含的非主關(guān)鍵字信息（非主鍵屬性不能出現(xiàn)在第二張表中）

非主鍵屬性重復(fù)了，完全可以根據(jù)編號(hào)（主鍵）去查詢部門簡介和名稱

數(shù)據(jù)庫設(shè)計(jì)的完整性約束

實(shí)體完整性約束

每個(gè)實(shí)例或者行的主鍵都不能為空

參照完整性約束

外鍵可以為空值;當(dāng)外鍵不為空時(shí)，其取值只能等于參照的主鍵的某個(gè)值

數(shù)據(jù)庫的約束

約束是表級(jí)強(qiáng)制執(zhí)行的規(guī)則，當(dāng)表中數(shù)據(jù)有相互依賴性時(shí)，可以保護(hù)數(shù)據(jù)不被刪除

約束的類型：

• 表級(jí)約束和列級(jí)約束，他們兩者在作用上沒有任何的區(qū)別，但通常如果某個(gè)約束用于于不止一個(gè)列時(shí)，只能使用表級(jí)約束的形式來進(jìn)行表達(dá)，如果多個(gè)字段組成耨表的聯(lián)合主鍵時(shí)。
• 列級(jí)約束： 在定義列的時(shí)候：列名 約束類型 表級(jí)約束 在定義完所有的列后：列名，列名，。。。 約束類型

oracle有如下類型的約束：

not null：非空
primary key：主鍵約束
foreign key：外鍵約束
check：檢查約束
unique key：唯一性約束

not null(非空約束)

create table student(
     name varchar2(10), 
     sex varchar(5) not null, 
     age integer)

primary key(主鍵約束)

表級(jí)方式定義主鍵

create table student（ 
    name varchar2（19），
    sex varchar2（10），
    age integer，
    constraint student_age_pk primary key（age）
    ）；

把a(bǔ)ge定義為主鍵（student_age_pk:約束的名字有一定的規(guī)范：表名主鍵名約束類型）

列級(jí)方式定義主鍵

create table student（ 
   name varchar2（19） primary key，
   sex varchar2（10），
   age integer）;

foreign key（外鍵約束）

create table emp（
   empno number(4), 
   ename varchar2(10) not null, 
   job varchar2(9),
   deptno number(7,2) not null, 
   constraint emp_deptno_fk foreign key(deptno) references dept (deptn)
   );

--references dept (dept）：外鍵所對(duì)應(yīng)的主鍵所在的表 關(guān)鍵字 主表 主鍵

SQL語句

• 常用數(shù)據(jù)類型 2.子查詢（單行子查詢和多行子查詢）
• select語句 條件查詢 模糊查詢 簡單去掉重復(fù)的查詢結(jié)果 關(guān)于空值
• order by
• 單行函數(shù)
• 字符函數(shù)
• 數(shù)值處理函數(shù)
• 日期處理函數(shù)
• 類型轉(zhuǎn)換函數(shù)
• 通用函數(shù)
• 組（聚合）函數(shù)
• 語句的執(zhí)行順序
• insert
• detect
• update
• 事務(wù)與鎖
• DDL語句（數(shù)據(jù)定義語言）
• 多表聯(lián)合查詢
• 集合
• 視圖
• 約束條件
• 添加注釋
• 索引
• 經(jīng)典案例

常用數(shù)據(jù)類型

• varchar2（size）：可變長度的字符串，最大長度為size個(gè)字節(jié)，size最大值為4000，最小值為1
• char（size）：固定長度的字符數(shù)據(jù)，其長度為size個(gè)字節(jié)，最大值為2000，最小值和默認(rèn)值為1，不管實(shí)際的長度為多少都會(huì)分配指定的size個(gè)字節(jié)
• number(p,s)：有效位數(shù)為p且精度為s的數(shù)值（小數(shù)點(diǎn)后有s位）p的取值范圍為1到38
• date：有效日期范圍從公元前4712年1月到公元后4712年12月31日
• long：可變長度的字符數(shù)據(jù)，其長度可達(dá)2G個(gè)字節(jié)

查詢

子查詢

• 子查詢?cè)谥鞑樵兊那耙淮螆?zhí)行
• 主查詢使用子查詢的結(jié)果
• 子查詢要用括號(hào)括起來
• 將子查詢放在比較運(yùn)算符的右邊
• 子查詢中不要加ORDER BY子句

應(yīng)用場(chǎng)景：例如那些雇員的工資比林志玲的高？

單行子查詢

• 查詢結(jié)果為一行

• 單行子查詢只能使用單行比較運(yùn)算符 =，>,>=,<,<=,<>

  select name,job from emp where job = (select job from emp where empno = 7369);
  select ename,job,sal from emp where sal=(select min(sal) from emp)
  

相關(guān)子查詢

多行子查詢

• 查詢結(jié)果為多行
• 多行子查詢只能使用多行比較運(yùn)算符

  IN：等于列表中的任何值
  ANY：比較子查詢返回的每一個(gè)值，只要其中一個(gè)滿足條件就返回true
  ALL：比較子查詢返回的每一個(gè)值，要其中所有的值都滿足條件才返回true

使用IN：

select empno，ename，job from emp where deptno in（select deptno from emp where ename = 'SMITH' or ename ='MITLER'）;

使用ANY

• <ANY : 指小于最大值

• ANY : 指大于最小值

• ANY：與IN等價(jià)

  select empno，ename，job from emp where sal <ANY (select sal from emp where job = 'clerk')
  

使用ALL

• <ALL : 指小于最小值
• ">ALL" : 指大于最大值

select empno，ename，job，sal from emp where sal > ALL(select avg(sal) from emp GROUP BY depton)

SQL語句

• select語句

select 列名 form 表名select name from student
select * from student ：查詢所有的列
select name，sex from student

• select name as myname from student：隊(duì)列中使用別名

  • 查詢結(jié)果顯示的列用別名顯示（as 用別名顯示）
  • 之前是結(jié)果列名為name as后為myname
  • 不寫as就要加引號(hào)或者空格：select name "myname" form student

• 列使用算數(shù)表達(dá)式:

  • select name，sal，sal+100 from student： 查詢sal和sal+100

• 關(guān)于空值

  • 空值為不可用，不知道，不適用的值
  • 空值不等于0或者空格
  • 包含空值的算數(shù)表達(dá)式等于空

• 連接操作符

  • 可將列或者字符與其他列連接起來，用雙豎線標(biāo)識(shí)（||）
  • 產(chǎn)生的結(jié)果是一個(gè)字符表達(dá)式
  • 使用連接操作符查詢，返回結(jié)果是一個(gè)連接后的結(jié)果（每個(gè)結(jié)果的連接）
  • select name || sex as new from student：將name和sex的查詢結(jié)果連接起來顯示并設(shè)置查詢結(jié)果對(duì)應(yīng)的列名為new

• 簡單去掉重復(fù)的查詢結(jié)果

  • 使用distinct關(guān)鍵字可以是某列中重復(fù)的值不顯示
  • distinct操作會(huì)引起排序，通過排序去掉重復(fù)的記錄
  • distinct必須放在第一列的前面，只能實(shí)現(xiàn)單列去重
  • select distinct sex from student

• 條件查詢

  • select 列名 from 表名 where 條件
  • where后面不能使用別名

• 模糊查詢

  • 使用like運(yùn)算符進(jìn)行通配查詢
  • 查詢條件可以包含文字字符或數(shù)字
  • %：表示0或多個(gè)字符
  • _：表示一個(gè)字符

  select name from student where name like 'S%' ：只顯示以S開頭的姓名
  select name from student where name like '_A%' :只顯示第二個(gè)字符為A的名字
  select naem from student where name like '%A_B%' ：只顯示含有A_B的名字（\：轉(zhuǎn)義字符）

order by

• 使用order by可以將記錄進(jìn)行排序
• ASC：升序（默認(rèn)的），DESC：降序排列
• order by 只能放到所有select語句的最后

select name，sex，age from student order by age asc：按照age進(jìn)行降序排列
select name，sex，age，sal from student order by age asc，sal desc：按照age降序排列，如果相等則在按照sal升序排列

單行函數(shù)

• 用于操作數(shù)據(jù)項(xiàng)
• 可以接受參數(shù)并返回一個(gè)值
• 對(duì)每一個(gè)返回行起作用，每一行返回一個(gè)結(jié)果
• 可使用轉(zhuǎn)換函數(shù)修改數(shù)據(jù)類型
• 可使用嵌套形式
• 常見的單行函數(shù)包括字符、數(shù)字、日期處理函數(shù)、類型轉(zhuǎn)換函數(shù)、邏輯處理函數(shù)等

字符函數(shù)

• lower：將字符串全部變?yōu)樾?/p>

  select name，sex from student where LOWFR（name） = 'blake'; 查詢姓名為blake的相應(yīng)屬性，不管姓名是大小寫還是大小寫混寫的都能查出
  

• upper：將字符串全部變?yōu)榇髮?/p>

• initcap：將字符串的第一個(gè)字母變?yōu)榇髮?/p>

• concat：將兩個(gè)字符串拼接起來

• substr：截取字符串

  select substr('helloword',2,5) from dual;--從第二個(gè)開始數(shù)5個(gè)
  

• length：返回字符串的長度

  select length（t.empfirstname） from employee t;
  

• instr:在一個(gè)字符串中定位子字符串的位置（沒有的話返回值為0）

  select instr（'hellword','w'）from dual;--查詢w在hellword中的位置
  

• 左補(bǔ)齊函數(shù)(lpad)

• 右補(bǔ)齊函數(shù)（rpad）

  select e.empfirsname,rpad(e.empfistname,10,'&') from employees e;--設(shè)置長度為10，不夠的用&補(bǔ)齊
  

• replace：替換函數(shù)

  select replace('ABCDFG','D','www') from dual;--用www替換D
  

• trim(leading|trailing|both y from x):從x中截去單個(gè)字符y

• both：截去兩邊的；leading：從左邊；traiing：從右邊

  select trim（both 'h' from 'hooojdhh'） from dual;--截去兩邊的h
  

數(shù)值處理函數(shù)

• 1.round：保留指定的位數(shù)（按四舍五入）

  SELECT round(5.75), round(5.75, 1), round(15.75, -1) FROM dual;--1代表保留一位小數(shù)，-1表示個(gè)位也四舍五入
  

• trunc：直接去掉小數(shù)點(diǎn)，沒有四舍五入

  SELECT trunc(5.75), trunc(5.76, 1), trunc(15.75, -1) FROM dual;--1表示保留一位小數(shù)，直接去掉后面的，不四舍五入
  

• mod：去余數(shù)

  select mod(1600,300) from dual;--返回結(jié)果為100
  

• abs：去絕對(duì)值

  select t.quotedprice -100 from order_details t where t.ordernumber =354 and t.productnumber = 13；--返回
  

• sign(x)返回x的符號(hào)

若x<0，返回-1;若x=0，返回0；若x>0，返回1

• floor(x)返回小于或等于x的最大整數(shù)

  select floor(5.8),floor(-5.6) from dual;
  

• ceil(x)返回大于或等于x的最小整數(shù)

  select ceil(5.8),ceil(-5.6) from dual;
  

• power(x,y)返回x的y次冪

日期處理函數(shù)

• sysdate：返回系統(tǒng)的當(dāng)前時(shí)間

  select sysdate from dual；
  

• add_months(x,y):返回x加上y個(gè)月的結(jié)果，如果y為負(fù)值，就是減去

  select add_months(sysdate,10) from dual;
  

• last_day(x):返回包含x月的最后一天

  select last_day(date '1993-04-28') from dual;--返回結(jié)果為1993-04-30
  

• next_day(x,day):從x時(shí)間開始，返回下一個(gè)day的時(shí)間值

  select next_day(sysdate,'星期一') from dual;--從系統(tǒng)的當(dāng)前時(shí)間開始，返回下一個(gè)星期一的日期
  

• months_between(x,y):返回x和y之間有多少個(gè)月（x-y），可以是負(fù)數(shù)

  select months_between(sysdate,date '1993-04-28')/12 from dual;--計(jì)算出某個(gè)人的周歲
  

• round(date,month/day):

Month 1-15日算上一個(gè)月，15日后算下一個(gè)月
Year 1-6月算上一年，7-12月算下一年

select round(date'2014-7-16','month') from dual;--返回2014-8-1 select round(date'2014-7-16','year') from dual;--返回結(jié)果為2015-1-1

• trunc

  select trunc(date'2014-7-16','month') from dual;返回結(jié)果為2014-7-1
  

類型轉(zhuǎn)換函數(shù)

數(shù)據(jù)類型轉(zhuǎn)換包含隱士類型轉(zhuǎn)換和顯示類型轉(zhuǎn)換
隱士類型轉(zhuǎn)換：系統(tǒng)自動(dòng)轉(zhuǎn)換
顯示類型轉(zhuǎn)換：調(diào)用相應(yīng)的函數(shù)轉(zhuǎn)換

TO_CHAR：字符串轉(zhuǎn)換（其他類型轉(zhuǎn)換為字符串）
TO_NUMBER：數(shù)值轉(zhuǎn)換
TO_DATE：日期轉(zhuǎn)換

• TO_CHAR

將日期轉(zhuǎn)換為字符串格式
必須用單引號(hào)括起來，大小寫不敏感
有一個(gè)fm元素，用于填補(bǔ)空格或者禁止前面的零
使用逗號(hào)分離日期
TO_CHAR（date，'fmt'）
常用的日期格式：
YYYY：四位數(shù)表示的年
YEAR：拼寫出的年
MM：兩位數(shù)字的月
MONTH：全月明（例如：sepember）
DD：兩位數(shù)字表示的月
DAY：全天名

select name,TO_CHAR(hiredate,'fmDD Month YYYY') HIREDATE from emp

• TO_NUMBER:將字符串轉(zhuǎn)化為數(shù)值TO_NUMBER（char）

  select * from emp where deptno = TO_NUMBER('30')
  

• TO_DATE:將字符串轉(zhuǎn)換為日期TO_DATE（char，['fmt']）

  select TO_DATE('2006-05','YYYY-MM') from DUAL
  

通用函數(shù)

• nvl(value,srt):如果第一個(gè)參數(shù)不為null，就返回第一個(gè)參數(shù)，如果為null就返回第二個(gè)參數(shù)

  select nvl(e.name,'name is null') from employee e;
  

• nvl2(value,x,y):如果value為null就返回y，否則返回x

  select nvl2(s.class_id,1,2) from students s;
  

• nullif(x,y):如果相等返回null，不相等則返回x；

  select nullif(s.sex,'男') from students s;
  

case表達(dá)式：（選擇表達(dá)式）

Case國際sql通用支持的，使用case可移植更好。相當(dāng)于在SQL中執(zhí)行if語句 CASE 可用于允許使用有效表達(dá)式的任意語句或子句。 例如，可以在 SELECT、UPDATE、DELETE 和 SET 等語句以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。

select e.salary,
case e.type_id
   when 1 then e.salary*2 
   when 2 then e.salary*3
   when 3 then e.salary*4 
    eles e.salary*5
end 
as new_salary from employee e;

說明：當(dāng)typeid為1時(shí)，薪水2，當(dāng)typeid為2時(shí)，薪水3，當(dāng)type_id為3時(shí)，薪水4，其他情況下薪水5；

組（聚合）函數(shù)

• max(),min().avg()：注意單行與多行不能放在一起

  select max(e.aslary),e.type_id from employee e;--會(huì)直接報(bào)錯(cuò)的，單行結(jié)果與多行不能放在一起
  

• count:統(tǒng)計(jì)函數(shù) : 統(tǒng)計(jì)字段時(shí)不統(tǒng)計(jì)null的

  select count(*) from employee;--統(tǒng)計(jì)該表一共有多少條記錄（里面加任意的數(shù)字也可以count（2））
  select count(e.manager_id) from employee e;--按照某個(gè)字段統(tǒng)計(jì)，空值不統(tǒng)計(jì)
  

• group by：分組
  結(jié)合分組函數(shù)使用
  group by后面的列可以不出現(xiàn)在select后邊，但出現(xiàn)在select后面的列必須出現(xiàn)在group by子句里面
  如果select列表中既有普通列又有分組函數(shù)，則必須使用group by子句
  聚合分組函數(shù)的條件限定不能使用where，只能使用having，且用了having必須使用group by。

  select e.division*id,avg(e.salary) from employees2 e group by e.division*id;--求每個(gè)部門的工資
  

語法的執(zhí)行順序

1.select 2.from 3.where 4.group by 5.組函數(shù) 6.having 7.order by

說明：having與where功能一樣，就是執(zhí)行的順序不一樣eg：select e.division_id,avg(e.salary) from employees2 e group by e.division_id where avg(e.salary)>150000;--會(huì)報(bào)錯(cuò)的，因?yàn)閣here比組函數(shù)（avg）先執(zhí)行，在執(zhí)行where的時(shí)候，avg（e.salary）還沒有算出來，所以會(huì)出錯(cuò)，換成having就沒事了。

• insert into(插入語句)
  插入時(shí)注意完整性約束條件（外鍵的取值）
  插入的時(shí)候插入到首行

  insert into 表名（插入的字段）values（字段對(duì)應(yīng)的值）
  insert into student values（3，'name','sex',12）； 
  insert into student(id,name) values(4,'name'); 
  insert into student values（5，'''name','sex',12）;
  

插入的名字為'name insert into student(name,sex) values(&Name,'nan');
&:變量，在執(zhí)行該語句時(shí)，會(huì)讓你輸入name要插入的值（Name變量名自己起的） 一次插入多條語句，要查入得值從另一個(gè)表中取數(shù)據(jù) insert into student（name，sex，age） select name，sex，age from student2 where id>10;

• delete(刪除語句) : delect from 表名 條件

  delect from student where id=3
  

• update(修改語句) : update 表名 set 字段名=要修改的值 條件

  update student set name=gfdd where id=2;
  update studnet set name=gggg,sex=nv where id=3;
  

事務(wù)

一組sql語句一塊執(zhí)行，要么一起成功，要么一起失敗 以commit開始

• commit：提交eg：commit

• rollback：回滾（撤銷）eg：rollback

  commit;select * from customers;insert into customers values(6,'gfd','red',date '1993-04-28',null);
  insert into customers values(7,'gfd','red',date '1993-04-28',null);
  savepoint A;--添加保存的點(diǎn)，回滾時(shí)可以指定到該位置
  insert into customers values(8,'gfd','red',date '1993-04-28',null);
  savepoint B;
  insert into customers values(9,'gfd','red',date '1993-04-28',null);
  rollback to A;--回滾到A點(diǎn)
  

鎖：保證數(shù)據(jù)庫的一致性（同步）：自添加鎖

T1執(zhí)行 update students set name='gfd' where id=3; 如果T2想修改id=3的數(shù)據(jù)，必須等T1提交后才能修改（也就是說提交后自動(dòng)釋放鎖）

死鎖

T1 update students set name='gfd' where id=3;
update students set name='gfd' where id=4; T2 update students set name='gfd' where id=4; 
update students set name='gfd' where id=3; 

說明：在T1，T2執(zhí)行完第一條語句時(shí)，再執(zhí)行下一條語句時(shí)，都被各彼此鎖著，所以都不能執(zhí)行下去了。 就出現(xiàn)了死鎖的狀態(tài)，oracle數(shù)據(jù)庫會(huì)自動(dòng)解鎖一條語句

DDL語句（數(shù)據(jù)定義語言）

對(duì)表的操作可以通過DDL語句進(jìn)行，包括：

• create：創(chuàng)建新表,必須以字母開頭,訪問其他用戶的表時(shí)需要加上用戶名的前綴

  create table dept(deptno number(2),dname varchar2(14),loc varchar2(3));
  create table dept30 as select empno,ename,sal*12 ANNSAL,hiredate from emp where deptno=30;--數(shù)據(jù)從別的表中拷貝
  

• 創(chuàng)建聯(lián)合主鍵的表

  create table emp( emp_id integer, emp_name varchar2(20) not null, emp_bir date default sysdate primary key(emp_id,emp_name) );
  

• alter：更改表的結(jié)構(gòu)（字段）

alert table 表名 add (增加的列名 類型，。。。。。)；--增加列
alert table 表名 modify （修改的列名 類型）；--修改列
alert table 表名 rename column 要修改的列名 to 新的列名；--修改列名
alter table 表名 rename to 新的表名；--修改表名
alert table 表名 drop column 要?jiǎng)h除的列名；--刪除列
alert table dept30 add （job varchar2（9））；
alert table dept30 modify （job varchar2（15））；
alert table dept30 raname column deptname to dname；alert table dept30 drop column job；

• drop：刪除表

表中的所有數(shù)據(jù)將被刪除
事物被自動(dòng)提交
所有的索引被刪除
不能回退
徹底刪除表
drop table 表名；--刪除指定的表

• truncate：快速刪除表中的所有記錄

直接刪除全部的記錄，無法指定刪除條件
無法回退
只會(huì)刪除數(shù)據(jù)，會(huì)保留表的結(jié)構(gòu)（字段），可以再次插入數(shù)據(jù)
速度快于delect
truncate table 表名；--刪除指定的表

約束條件

• primary key(主鍵約束)

  • 在創(chuàng)建的表的時(shí)候指定

    create table emp(emp_id integer primary key)
    

  • 在表創(chuàng)建完成后添加主鍵約束

    alter table stu add primary key (emp_id);
    

• foregin key（外鍵約束）

  • 兩張表沒有建立外鍵關(guān)系

    alter table stu add foregin key(stu_id) references xi(xi_id)
    

  • 刪除外鍵的時(shí)候，相應(yīng)表中與該外鍵的有關(guān)的記錄也全部刪除

    alter table stu add foregin key(stu_id) references xi(xi_id) on delete cascade
    

  • 刪除外鍵的時(shí)候，相應(yīng)表中與該外鍵的有關(guān)的列全部設(shè)置為null

    alter table stu add foregin key(stu_id) references xi(xi_id) on delete set null
    

• unique（不能重復(fù)約束）

  alter table emp add unique(emp_name);--可以插入null（null值可以重復(fù)）
  

• check（取值范圍約束）

  alter table emp add check(em_sex in ("男","女"));alter table emp add check(em_id>0);
  

索引

使用索引大大加快查詢的速度
對(duì)數(shù)據(jù)量大的，經(jīng)常使用的表才去創(chuàng)建索引(需要維護(hù)的)
查詢的時(shí)候與正常的一樣

create index 索引名 on 表名(字段名。。。。);--創(chuàng)建索引delete index 索引名；--刪除索引

視圖

可以隱藏一些信息
虛擬的表（不真實(shí)存在，基表的數(shù)據(jù)刪除時(shí)，視圖中的數(shù)據(jù)也會(huì)刪除）
可以進(jìn)行增刪改查（對(duì)視圖表中的數(shù)據(jù)改變時(shí)，基表的數(shù)據(jù)也會(huì)改變）
只要視圖中能看到的，基表中肯定能看到

create or replace view ch_view as select * from products p where p.price<15;--創(chuàng)建視圖
select * from ch_view;--查詢視圖insert into ch_view values(14,3,'fff','ggg',12);--插入數(shù)據(jù)

添加注釋

comment on table 表名 is ‘注釋’；--表添加注釋
comment on column 表名.字段名 is ‘注釋’；--字段添加注釋

多表聯(lián)合查詢

沒有指定連接條件的多表查詢將造成笛卡爾積的結(jié)果
笛卡爾積結(jié)果：兩個(gè)表中所有數(shù)據(jù)的集合都會(huì)查詢出來

笛卡爾積

多表連接查詢中的連接類型

內(nèi)連接：返回符合特定連接條件的查詢記錄

• 等值連接：返回符合指定連接條件的值，這些值是相等關(guān)系

  select * from products p,product_type pt where p.type_id = pt.type_id;
  select * from products p inner join prodect_type on p.type_id = pt.type_id;--inner join on 條件
  

• 非等值連接：返回符合指定連接條件的值，這些值不是相等關(guān)系

  select * from products p,product_types pt where p.product_type_id <> pt.product_type_id;--<>:不等于
  

• 自然連接：特指在同一張表內(nèi)通過特定連接條件查詢符合連接條件的值

select * from products p natural inner join purchases pt;--自動(dòng)按著相等的去連接，不用加條件了 在emp中每一個(gè)員工都有自己的mgr（經(jīng)理），并且每一個(gè)經(jīng)理自身也是公司的員工，自身也有自己的mgr。我們需要將每一個(gè)員工自己的名字和經(jīng)理的名字都找出來。
select worker.ename,'work for',manager.ename from emp worker,emp managetr where worker.mgr = manager.empne work for:連接字符串

外連接：不僅返回符合連接條件的記錄，也返回指定表中的不符合連接條件的記錄

• 左外連接：先對(duì)A表和B表做內(nèi)連接，再把A表中沒有匹配的結(jié)果集添加進(jìn)去形成新的結(jié)果集(相應(yīng)的右表的值（對(duì)應(yīng)A表沒有匹配的）為null)

  select * from newdept left outer join newemp on newdept.deptid = newemp.deptid；--outer可以省略 
  。。。。。。。where a.deptno = b.deptno(+);--左連接
  

• 右外連接: 先對(duì)A表和B表做內(nèi)連接，再把B表中沒有匹配的結(jié)果集添加進(jìn)去形成新的結(jié)果集(相應(yīng)的左表的值（對(duì)應(yīng)B表沒有匹配的）為null)也返回

  select * from newdept right outer join newemp on newdept.deptid = newemp.deptid
  。。。。。。。where a.deptno(+) = b.deptno；--右連接
  

• 全外連接：在內(nèi)連接的基礎(chǔ)上，把左右兩表中不符合連接條件的記錄都返回

在emp中每一個(gè)員工都有自己的mgr（經(jīng)理），并且每一個(gè)經(jīng)理自身也是公司的員工，自身也有自己的mgr。我們需要將每一個(gè)員工自己的名字和經(jīng)理的名字都找出來。

  select worker.ename,'work for',manager.ename from emp worker,emp managetr 
  where worker.mgr = manager.empnework for:連接字符串

用SQL語句列出EMP表中所有部門的詳細(xì)信息以及對(duì)應(yīng)的部門人數(shù)

select a.* , b.* from dept a,(select deptno,count(*) from emp group by deptno) b where a.deptno = b.deptno(+)

• 全外連接

用SQL語句列出NEWEMP表和NEWDEPT表中所有部門和員工的詳細(xì)信息:

select * from newdept full outer join newemp on newdept.deptid = newemp.deptid

集合

集合中的元素可以把一條記錄或者一個(gè)字段當(dāng)做一個(gè)元素
用了集合再排序的時(shí)候，order by 字段的位置（例如 1）：只能用數(shù)字了

• intersect:交集

• minus:減集

  • 在一張表中顯示了，就不在另一張表中出現(xiàn)了（A表 minus B表）：顯示B表去除與A表相同的部分

• union all:并集（加all不去重）union ：去重

  select * from (select * from (select * from employees2 e order by e.salary desc) where rownum<11
  minus
  select * from (select * from employees2 e order by e.salary desc) e where rownum<4) order by 6;--用到了集合再排序只能使用數(shù)字了
  

經(jīng)典例子

根據(jù)時(shí)間統(tǒng)計(jì)勝負(fù)數(shù)

執(zhí)行sql語句變成為：

select
 t_date, 
 (select count(*) from t where t_status='勝' and t_date=e.t_date) 勝, 
 (select count(*) from t where t_status='負(fù)' and t_date=e.t_date) 負(fù)
from t e group by t_date order by e.t_date;

兩張表中的數(shù)據(jù)運(yùn)算

select kc.mc "種類",kc.s1-(select sum(s1) from ck where ck.mc = kc.mc ) "剩余量" from KC;

一次修改多條記錄

update employees e set e.salary = 
case e.employee_id
  when 1 then 5555
  when 2 then 6666
end
where e.employee_id<3;

多表聯(lián)查

購買數(shù)量超過一個(gè)的產(chǎn)品名，顧客名以及購買的數(shù)量（3張表）
select * from products;
select * from customers;
select * from purchases;

• 第一種方法

  select * from customers c inner join (select * from products p inner join purchases pr on p.product_id=pr.product_id) a on a.customer_id=c.customer_id where a.quantity>1;
  

• 第二種方法

  select * from (products p inner join purchases pr on p.product_id=pr.product_id and pr.quantity>1) inner join customers c on c.customer_id=pr.customer_id;
  

• 第三種方法

  select * from products p,purchases pr,customers c where p.product_id=pr.product_id and pr.customer_id=c.customer_id and pr.quantity>1;
  

四表聯(lián)查

有如下幾張表:

部門表：organization（orgid ，orgName）
商品表：Goods（goodsid，goodsName）
銷售單（單頭）：sale（billid，billdt，orgid，status）
銷售單（單體）：sale_item（billid，goodsid，qty）

orgid：組織編碼；
orgName：組織名稱；
billid：單據(jù)編號(hào)
billdt：訂單日期

查詢“銷售一部”本月的商品銷售明細(xì)（商品編碼。商品名稱，銷售日期，銷售數(shù)量）

select * from organization o,goods g,sale s,sale_item si 
where 
o.orgid=s.orgid and g.goodsid=si.goodsid ands.billid=si.billid and orgName="銷售一部" and billdt>trunc（sysdate） and billdt < last_day(sysdate);