多表查詢
笛卡爾積
虛擬表
s_emp? s_dept
上述兩個(gè)表所有的列在虛擬表都會(huì)出現(xiàn)，
引用那些列? 原始表.列名
連接本質(zhì)? 去除笛卡爾積中 無效的行
分組查詢
select sd.id,sd.name
from
where
group by se.dept_id,
having 組級(jí)別篩選;
分組函數(shù)可以出現(xiàn)的位置：
select、having、order by
where 后面絕對(duì)不可以出現(xiàn)分組函數(shù)
分組查詢的時(shí)候，select、order by后面出現(xiàn)的列，如果沒有分組函數(shù)修飾，則必須出現(xiàn)在group by后面;
分組并不會(huì) 篩選掉數(shù)據(jù)行，只是做了分類
如果在分組基礎(chǔ)上select,則一定注意輸出的內(nèi)容 必須是【組級(jí)別】的。
子查詢
兩種形式
where或having 里面用到值，通過select獲取到的
select時(shí)需要一張額外的表，select獲取到的
結(jié)果集處理
分頁(子查詢 偽列)
select n,*
from (
select rownum n,*
from table
where rownum < n1;
)
where r>n2;
第七章：運(yùn)行時(shí)參數(shù)
sql語句中的值,我們可以使用一個(gè)參數(shù)來代替,然后每次運(yùn)行的時(shí)候都可以重新輸入這個(gè)值
運(yùn)行時(shí)參數(shù) 格式：&參數(shù)名;
參數(shù)的名稱任意；
參數(shù)可以是數(shù)字，也可以是字符串，可以是整個(gè)sql子句[where|order by];
一個(gè)sql語句中也可以寫多個(gè)參數(shù);
1.參數(shù)可以是數(shù)字
select last_name,salary,dept_id
from s_emp
where id=&id;
2.參數(shù)可以是字符串(加單引號(hào)，不加單引號(hào))
select last_name,salary,dept_id
from s_emp
where last_name=&name;
select last_name,salary,dept_id
from s_emp
where last_name='&name';
3.參數(shù)可以出現(xiàn)多個(gè)
select last_name,salary,dept_id
from s_emp
where
salary>&a
and
salary<&b;
4.參數(shù)可以 設(shè)置為整個(gè)條件
select last_name,salary,dept_id
from s_emp
where &con;
//輸入1=1? 不限制
5.進(jìn)一步放開條件
select last_name,salary,dept_id
from s_emp
&con;
注意:&變量名 表示使用這個(gè)oracle定義的變量,如果這個(gè)變量之前沒有定義過,那么這個(gè)時(shí)候會(huì)讓你重寫輸入這個(gè)變量的值.如果之前有定義過,那么就是要之前定義過的值
6.幾個(gè)關(guān)鍵字：define undefine accept prompt hide命令
例如:
命令define(縮寫def)可以定義一個(gè)變量
定義變量: def A=s_emp
查看定義的變量:? def
取消定義的變量:? undef A
然后變量A之前定義過,那么這里會(huì)之前替換為之前定義的值
select id,last_name,salary
from &A;
accept命令也可以【定義】一個(gè)【變量】,而且變量的值需要用戶再次輸入
例如:
accept A
回車之前需要用戶再次輸入變量A的值
之后可以使用def命令來查看剛剛定義的A變量
prompt可以在用戶輸入的時(shí)候顯示一個(gè)【提示信息】:
例如:
accept name prompt '請(qǐng)輸入name變量的值: '
hide可以【隱藏】用戶的【輸入內(nèi)容】不被看見
例如:
accept name prompt '請(qǐng)輸入name變量的值: ' hide
注意:這些定義的都是【臨時(shí)變量】,sqlplus退出后重新登錄進(jìn)來就沒有了
第八章：數(shù)據(jù)建模和數(shù)據(jù)庫設(shè)計(jì)
1.軟件開發(fā)流程和數(shù)據(jù)庫設(shè)計(jì)
軟件開發(fā)的步驟可大致分為:
a.需求分析
b.系統(tǒng)設(shè)計(jì)
c.編碼實(shí)現(xiàn)
d.系統(tǒng)測(cè)試
e.運(yùn)行維護(hù)
系統(tǒng)設(shè)計(jì)中一個(gè)重要的環(huán)節(jié)就是數(shù)據(jù)庫設(shè)計(jì).?
數(shù)據(jù)庫設(shè)計(jì)的時(shí)候需要先進(jìn)行數(shù)據(jù)建模(實(shí)體關(guān)系圖 E-R圖)
數(shù)據(jù)建模的依據(jù)就是前期所做的需求分析.
2.數(shù)據(jù)建模
參照179頁的圖形:
1.Model of system in client's mind
用戶的想法
2.Entity model of client's model
實(shí)體模型圖
3.Table model of entity model
具體的表
4.Tables on disk
存到數(shù)據(jù)庫里面(磁盤)
3.實(shí)體-關(guān)系圖
實(shí)體-關(guān)系圖(Entity Relationship Diagram),也稱為E-R圖,提供了表示實(shí)體、屬性和關(guān)系的方法，用來描述現(xiàn)實(shí)世界的概念模型。
構(gòu)成E-R圖的基本要素是【實(shí)體、屬性和關(guān)系】
a.實(shí)體(Entity)：實(shí)體用來表示具有相同特征和性質(zhì)的事物(類似于java的類)，實(shí)體由實(shí)體名和實(shí)體屬性來表示?！緦?shí)體側(cè)重?cái)?shù)據(jù)存儲(chǔ)，類還包含行為】
b.屬性(Attribute)：實(shí)體所具有的某一特性，一個(gè)實(shí)體可以有若干個(gè)屬性
c.關(guān)系(Relationship)：實(shí)體彼此之間相互連接的方式稱為關(guān)系。一般可分為以下 3 種類型:
一對(duì)一關(guān)系 (1 ∶ 1)
人? 身份證
一對(duì)多關(guān)系 (1 ∶ N)
一個(gè)老師? 多個(gè)學(xué)生
多對(duì)多關(guān)系 (M ∶ N)
一個(gè)學(xué)生 選 多門課程，
一門課程 由 多個(gè)學(xué)生選
4.實(shí)體間關(guān)系
員工? --? 訂單? --? 明細(xì)
? ? may-be 和 must-be
在實(shí)體與實(shí)體之間的關(guān)系中,都會(huì)存在著may-be和must-be這倆種情況,
例如:
系統(tǒng)中有顧客和訂單倆個(gè)實(shí)體(1:N關(guān)系),一個(gè)顧客對(duì)應(yīng)多個(gè)訂單,一個(gè)訂單對(duì)應(yīng)一個(gè)顧客,而且一個(gè)顧客可以(may be)沒有訂單和他對(duì)應(yīng),一個(gè)訂單一定(must be)會(huì)有顧客和它對(duì)應(yīng).
5.ER圖中符號(hào)的表示
1) # : 唯一, 以后可能表示為主鍵
2) * : 非空
3) o : 可有可無
4) 虛線: may be? 顧客這邊虛線，顧客可能沒有訂單
5) 實(shí)線: must be 訂單這邊實(shí)線，訂單一定是屬于某個(gè)客戶。
6) 豎杠(|): 代表要強(qiáng)制在(|)一方建立一個(gè)聯(lián)合主鍵，將對(duì)方ID拿過來做聯(lián)合主鍵
7) 傘狀圖標(biāo)代表多的一方,不是傘狀圖標(biāo)則代表一的一方
6.數(shù)據(jù)庫具體設(shè)計(jì)
數(shù)據(jù)建模完成之后,可以把【E-R圖】轉(zhuǎn)換成數(shù)據(jù)中的【表】
1).實(shí)體的名字轉(zhuǎn)換為表的名字
2).實(shí)體的屬性轉(zhuǎn)換為表中的列
3).具有唯一特點(diǎn)的屬性設(shè)置為表中的主鍵
4).根據(jù)實(shí)體之間的關(guān)系設(shè)置為表中某列為外鍵列(主外鍵關(guān)聯(lián))
注意:第四步主要是:【實(shí)體關(guān)系--->表關(guān)系】
一對(duì)一的關(guān)系: 主外鍵放在兩個(gè)表中 都可以
一對(duì)多的關(guān)系: 主鍵放到一，外鍵放到多
多對(duì)多關(guān)系:
例如學(xué)生對(duì)課程, 利用橋表實(shí)現(xiàn)，橋接一下，(兩個(gè)表中的【主鍵】都拿過來【做外鍵】，然后【兩個(gè)外鍵】聯(lián)合到一起)【作聯(lián)合主鍵】。
學(xué)生表 課程表
id? name id? name
1 zs 1? ? 數(shù)學(xué)
2 ls 2 語文
3? ? ww
學(xué)生選課表(橋表)
學(xué)生id 課程id
1 1 數(shù)學(xué)
1 2 語文
3 1 數(shù)學(xué)
2 2 語文
7.主鍵和外鍵
【主鍵】:? s_emp? id? s_dept id? s_region id
在創(chuàng)建表的時(shí)候，通過primary key來設(shè)置主鍵列。
1).能做主鍵的列必要滿足【非空唯一】的特點(diǎn)
2).只要滿足非空唯一的任何列都可以做主鍵(具備資格，但并非只要非空唯一，就是主鍵)
3).可以讓表中一個(gè)【有意義的列做主鍵】,比如說學(xué)號(hào),它既表示學(xué)生學(xué)號(hào)又作為表中的主鍵,因?yàn)檫@個(gè)列滿足非空唯一的條件
4).也可以找一個(gè)【沒有意義的列做主鍵】,就是用來唯一標(biāo)識(shí)一行記錄的
5).我們可以讓【多個(gè)列聯(lián)合】在一起做表中的主鍵,那么它就是【聯(lián)合主鍵】,要求這幾個(gè)列的值聯(lián)合在一起是非空唯一的
課程成績表
學(xué)號(hào)? 課程? 成績
1 1 50
1 2 40
2 1 40
2 2 60
主鍵的作用:
1）保證實(shí)體的完整性
2）加快數(shù)據(jù)庫的操作速度
3）在表中添加新記錄時(shí)，數(shù)據(jù)庫會(huì)自動(dòng)檢查新記錄的主鍵值，不允許該值與其他記錄的主鍵值重復(fù)。
4) 數(shù)據(jù)庫自動(dòng)按主鍵值的順序顯示表中的記錄。如果沒有定義主鍵，則按輸入記錄的順序顯示表中的記錄。
主鍵不是非要不可，可以從：
1).是否滿足業(yè)務(wù)要求
2).數(shù)據(jù)查詢效率（主鍵可以提高查詢效率，當(dāng)然合理的索引替代也可以）
這兩個(gè)角度權(quán)衡是否需要主鍵。
主鍵列 所在的表? 屬于主表
外鍵列 是依賴主鍵列的，外表
外鍵列? 依賴? 主表中主鍵列
【外鍵】:
1).表中的某一個(gè)列聲明為【外鍵列】,一般這個(gè)外鍵列的值都會(huì)【引用于另外一張表的主鍵列的值】(有非空唯一約束的列就可以,不一定非要引用主鍵列)
2).另外一張表的主鍵列中出現(xiàn)過的值都可以在外鍵列中使用,【沒有出現(xiàn)過的值,絕對(duì)不能使用】
3).【外鍵列值可以為空】,前提是這個(gè)外鍵列在表中不做主鍵。
我們也可以把表中的外鍵列當(dāng)做主鍵來使用(只有滿足非空唯一的要求就可以)
4).如果把B表中的【聯(lián)合主鍵】的值引用到A表中做外鍵,那么A表引用過來的時(shí)候也要把倆個(gè)列的值都引用過來,那么它們?cè)贏表中就會(huì)作為一個(gè)【聯(lián)合外鍵】出現(xiàn)
8.數(shù)據(jù)庫范式
設(shè)計(jì)關(guān)系數(shù)據(jù)庫時(shí)，遵從不同的規(guī)范要求，才能設(shè)計(jì)出合理的關(guān)系型數(shù)據(jù)庫，這些不同的規(guī)范要求被稱為不同的范式，各種范式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫冗余越小。
目前關(guān)系數(shù)據(jù)庫有六種范式：
第一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)
第五范式(5NF，又稱完美范式)
注意:滿足最低要求的范式是第一范式(1NF)。在第一范式的基礎(chǔ)上進(jìn)一步滿足更多規(guī)范要求的稱為第二范式(2NF)，其余范式以次類推。
一般說來，數(shù)據(jù)庫只需滿足第三范式(3NF)就行了
第一范式:
? ? 一個(gè)表中,每個(gè)【列的值是不能再分割的】.
? ? 例如:我們?cè)O(shè)計(jì)的表中有一個(gè)列是:愛好
? ? 這個(gè)列的值可能會(huì)是這樣:足球籃球乒乓球
? ? 但是這值是可以再分割的:足球、籃球、乒乓球
? ? 所以這種設(shè)計(jì)是不滿足第一范式
第二范式:
第二范式是在滿足第一范式的基礎(chǔ)上
? ? 表中的【非主鍵列都必須依賴于主鍵列】
? ? 例如:
? ? 訂單表: 訂單編號(hào) 是主鍵
訂單編號(hào)? 訂單名稱? 訂單日期? 訂單中產(chǎn)品的生產(chǎn)地
這幾個(gè)非主鍵列中,產(chǎn)品生產(chǎn)地是不依賴于訂單編號(hào)的,所以這種設(shè)計(jì)是不滿足第二范式
第三范式:
第三范式是在滿足第二范式的基礎(chǔ)上
表中的非主鍵列都必須【直接依賴】于主鍵列,而不能間接的依賴.
(不能產(chǎn)生依賴傳遞)
? ? 例如:
? ? 訂單表:? 訂單編號(hào) 是主鍵
訂單編號(hào)? 訂單名稱? 顧客編號(hào)? 顧客姓名
顧客編號(hào)依賴于訂單編號(hào),顧客姓名依賴于顧客編號(hào),從而顧客姓名間接的依賴于訂單編號(hào),那么這里產(chǎn)生了依賴傳遞,所以這個(gè)設(shè)計(jì)是不滿足第三范式的。
第九章：建表
create 實(shí)現(xiàn)，屬于DDL
1.建表的格式(兩種)
格式1：
create table 表名(
列名1 數(shù)據(jù)類型 [列級(jí)約束],
列名2 數(shù)據(jù)類型 [列級(jí)約束],
列名3 數(shù)據(jù)類型 [列級(jí)約束],
列名4 數(shù)據(jù)類型 [列級(jí)約束]
);
格式2：
create table 表名(
列名1 數(shù)據(jù)類型 [列級(jí)約束],
列名2 數(shù)據(jù)類型 [列級(jí)約束],
列名3 數(shù)據(jù)類型 [列級(jí)約束],
列名4 數(shù)據(jù)類型 [列級(jí)約束],
[表級(jí)約束1],
[表級(jí)約束2],
...
);
2.建表需求
根據(jù)以上格式,可以看出,建表過程中,需要以下幾種東西:
a.關(guān)鍵字
b.表名
c.列名
d.數(shù)據(jù)類型
e.【約束】
f.固定格式
其中,約束分為【列級(jí)約束】和【表級(jí)約束】,列級(jí)約束和表級(jí)約束都是【對(duì)列中的值進(jìn)行約束】的。
例如:列的值不能為空,列的值必須是唯一的等等，都屬于列的約束。
列級(jí)約束 是跟在列的聲明后面寫的；
表級(jí)約束 是在全部列聲明完之后寫的；
注:列級(jí)約束也稱為【行級(jí)約束】。
同時(shí),【列級(jí)約束和表級(jí)約束都是可選的】,也就是都可以寫也可以不寫。例如:
create table 表名(
列名1 數(shù)據(jù)類型,
列名2 數(shù)據(jù)類型,
列名3 數(shù)據(jù)類型,
列名4 數(shù)據(jù)類型
);
3.表名要求
a.必須是字母開頭
b.必須是1-30個(gè)字符之間的長度
c.表名中只能出現(xiàn)A–Z, a–z, 0–9, _, $, #
d.不能和數(shù)據(jù)庫中已有對(duì)象的名字重復(fù)
e.不能是數(shù)據(jù)庫中的關(guān)鍵字
命名法：項(xiàng)目名_模塊名_表名;
4.列常用數(shù)據(jù)類型
a.char 默認(rèn)長度是1，最長不超過2000字節(jié)
b.varchar
c.varchar2 默認(rèn)長度是1，最長不超過4000字符
d.number(p)
? number(p,s) 既可以存浮點(diǎn)數(shù)，也可以存整數(shù),p表示最大位數(shù)，s指小數(shù)位數(shù)，可存負(fù)數(shù)
e.date
? timestamp 不但存儲(chǔ)日期的年月日，時(shí)分秒，以及秒后6位，同時(shí)包含時(shí)區(qū)
f.BLOB 存儲(chǔ)二進(jìn)制對(duì)象，如圖形、視頻、聲音等
h.CLOB 存儲(chǔ)大的文本，比如存儲(chǔ)非結(jié)構(gòu)化的XML文檔
BLOB和CLOB都是【大字段】類型
5.char varchar varchar2的區(qū)別及特點(diǎn)(面試題)
name char(20) tom
last_name varchar(20) jack
first_name? varchar2(20) larry
a．CHAR的長度是固定的，而VARCHAR2的長度是可以變化的
b．CHAR的效率比VARCHAR2的效率稍高。
c．VARCHAR是數(shù)據(jù)庫標(biāo)準(zhǔn)的字符類型
d. VARCHAR2是Oracle自己開發(fā)了一個(gè)數(shù)據(jù)類型VARCHAR2
e. VARCHAR2將數(shù)據(jù)庫中varchar列可以【存儲(chǔ)空字符】串的特性改為存儲(chǔ)【NULL值】。
f. Oracle建議使用VARCHAR2而不是VARCHAR
6.列的約束
列的約束就是對(duì)這個(gè)列中的值的要求(可有可無)
1).主鍵約束? PRIMARY KEY? primary key
2).外鍵約束? FOREIGN KEY? foreign key
3).唯一約束? UNIQUE? ? ? unique
4).非空約束? NOT NULL? ? not null
5).check約束 CHECK ? ? ? check(類似枚舉)
列級(jí)約束/行級(jí)約束:在列的后面直接加上的約束
create table 表名(
列名1 數(shù)據(jù)類型 列級(jí)約束,
列名2 數(shù)據(jù)類型 列級(jí)約束,
列名3 數(shù)據(jù)類型 列級(jí)約束,
列名4 數(shù)據(jù)類型 列級(jí)約束
);
表級(jí)約束:在所有列聲明以后加的約束
create table 表名(
列名1 數(shù)據(jù)類型 列級(jí)約束,
列名2 數(shù)據(jù)類型 列級(jí)約束,
列名3 數(shù)據(jù)類型 列級(jí)約束,
列名4 數(shù)據(jù)類型 列級(jí)約束,
表級(jí)約束1,
表級(jí)約束2
);
7.建表實(shí)例
例1:普通的建表例子
create table student(
id number primary key,
name varchar2(20) not null,
age number,
birthday date
);
select table_name from tabs;
drop table student;
例2:使用四種【列級(jí)約束】
主鍵約束 非空約束 唯一約束 check約束
create table student(
id number primary key,
name varchar2(20) not null,
email varchar2(30) unique,
gender char(1) check(gender in('f','m')),
age number,
birthday date
);
desc student 查看表結(jié)構(gòu)
drop table student;
例3:使用列級(jí)約束 聲明 外鍵約束
外鍵約束格式:
外鍵列 類型 references 主表(主鍵)
create table t_customer(
id number primary key,
name varchar2(20) not null
);
create table t_order(
id number primary key,
content varchar2(20) not null,
customer_id number references t_customer(id)
);
注意：先建立主鍵表，再建立外鍵表，有【次序之分】。
drop table t_customer;
drop table t_order;
注意:訂單表中的外鍵列customer_id的值,是引用自顧客表t_customer中的主鍵列id的值，【刪除表主外鍵列值】的時(shí)候，有一定限制。
a.這時(shí)候【直接刪除顧客表】是不行的,因?yàn)閠_customer的主鍵列的值被別的表給引用了.
b.我們可以【先刪除訂單表t_order】,然后再刪除t_customer就可以了
c.如果非要想直接刪除到顧客表t_customer,就需要使用下面的語句:
drop table t_customer cascade constraints;
d.該語句表示,刪除t_customer表的同時(shí),也【級(jí)聯(lián)刪除與表相關(guān)的約束】,外鍵約束沒有了,這個(gè)表自然可以被刪除掉
e.【cascade是級(jí)聯(lián)】的意思（選擇省份，自動(dòng)彈出市）
8.表級(jí)約束具體應(yīng)用
例4:普通建表語句
create table student(
id number,
name varchar2(20) not null,
age number,
email varchar2(100) not null,
gender char,
primary key(id),
unique(email),
check(gender in('f','m'))
);
drop table student;
注意:【非空約束】(not null)【不能】聲明成【表級(jí)約束】
例5:
使用表級(jí)約束 聲明 外鍵約束
create table t_customer(
id number primary key,
name varchar2(200) not null
);
create table t_order(
id number primary key,
content varchar2(200) not null,
customer_id number,
foreign key(customer_id) references t_customer(id)
);
表級(jí)外鍵約束格式:
foreign key(外鍵列) references 主表(主鍵列);
drop table t_order;
drop table t_customer;
例6:使用表級(jí)約束 聲明 【聯(lián)合唯一】約束
create table student(
id number primary key,
class varchar2(50) not null unique,
name varchar2(50) not null,
unique(class,name)
);
drop table student;
注意:學(xué)生的班級(jí)和學(xué)生的名字聯(lián)合起來必須是唯一的(聯(lián)合唯一)
注意:聯(lián)合唯一約束【必須使用表級(jí)約束】來聲明
例7:使用表級(jí)約束 聲明 【聯(lián)合主鍵】
create table t_customer(
id number,
name varchar2(50),
primary key(id,name)
);
drop table t_customer;
例8:使用表級(jí)約束 聲明 【聯(lián)合外鍵】
create table t_customer(
id number,
name varchar(50),
primary key(id,name)
);
create table t_order(
id number,
price number not null,
customer_id number,
customer_name varchar(50),
foreign key(customer_id,customer_name) references t_customer(id,name)
);
drop table t_order;
drop table t_customer;
注意：只要是【聯(lián)合的】(聯(lián)合主鍵、聯(lián)合外鍵、聯(lián)合唯一)，都【必須用表級(jí)約束】。
9.表級(jí)約束和列級(jí)約束對(duì)比(面試題)
1).表級(jí)約束和列級(jí)約束所寫的【位置不一樣】
2).【not null】約束不能用表級(jí)約束來聲明
3).表級(jí)約束和列級(jí)約束聲明【語法稍有所不同】
4).如果要聲明的約束為聯(lián)合主鍵、聯(lián)合外鍵、聯(lián)合唯一的時(shí)候,就一定要用表級(jí)約束.
10.constraint關(guān)鍵字
1).constraint是【約束】的意思
2).建表的時(shí)候可以給約束起一個(gè)名字,這個(gè)名字起的規(guī)律一般會(huì)是:【表名_列名_約束類型】
torder_customerid_for
3).如果沒有給約束起名字,那么系統(tǒng)也會(huì)給這個(gè)約束起一個(gè)【默認(rèn)的名字】,這不過這個(gè)默認(rèn)的名字對(duì)我們來說并不友好(我們可能看不懂)
4).將來我們可以根據(jù)約束名而找到這個(gè)約束,然后進(jìn)行修改
格式:
【constraint 約束名 具體約束;】
例1:列級(jí)約束 起約束名字
格式:
列名 數(shù)據(jù)類型 constraint 約束名 具體約束;
create table student(
id number constraint student_id_pk primary key,
name varchar2(100) constraint student_name_nn not null,
email varchar2(100) constraint student_email_un unique,
gender char(1) constraint student_gender_ck check(gender in('f','m')),
age number,
birthday date
);
drop table student;
例2:表級(jí)約束 起約束名字
格式: constraint 約束名 具體約束(列名);
create table t_customer(
id number,
name varchar2(20) not null,
age number,
email varchar2(100),
gender char,
constraint cus_id_pk primary key(id),
constraint cus_email_un unique(email),
constraint cus_gender_ck check(gender in('f','m'))
);
create table t_order(
id number,
price number not null,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);
drop table t_order;
drop table t_customer;
11.特殊的建表
建立一張表和s_dept一模一樣的表
create table 表名
as
select 語句;
例1:
將s_dept的表結(jié)構(gòu)和表中的數(shù)據(jù)全部復(fù)制過來
create table test1
as
select * from s_dept;
例2:
只拿來s_dept的表結(jié)構(gòu),沒有數(shù)據(jù)
create table test22
as
select * from s_dept
where 1=2;
例3:
只復(fù)制表中某幾個(gè)列以及數(shù)據(jù)
create table test3
as
select id,last_name,salary
from s_emp
where id<10;
12.ON DELETE CASCADE
on delete cascade
這個(gè)語句是在建表中,聲明外鍵約束的時(shí)候一個(gè)可選項(xiàng),我們后面的【DML章節(jié)】中再討論
第十章：數(shù)據(jù)字典(了解)
作用:幫助用戶了解當(dāng)前數(shù)據(jù)庫的一些信息,或是了解對(duì)象或用戶的信息.
注意:
a.數(shù)據(jù)字典隨著數(shù)據(jù)庫被創(chuàng)建 自動(dòng)創(chuàng)建。
b.數(shù)據(jù)字典中的數(shù)據(jù)被數(shù)據(jù)庫服務(wù)器自動(dòng)更新和維護(hù)。
不需要用戶額外操作。
對(duì)象: 表 視圖 索引 同義詞 序列
常見的數(shù)據(jù)字典(它們都是視圖)
USER開頭的視圖里面存放著用戶自己擁有的對(duì)象
ALL開頭的視圖存放著用戶有權(quán)限查看的對(duì)象
DBA開頭的視圖存放著數(shù)據(jù)庫所有的對(duì)象
V$開頭的視圖存放數(shù)據(jù)庫運(yùn)行的一些性能屬性數(shù)據(jù)
1、以u(píng)ser開頭的數(shù)據(jù)字典： 包含當(dāng)前用戶所擁有的相關(guān)對(duì)象信息。
//查詢用戶擁有的所有表的名字
select table_name
from user_tables;
//查詢用戶對(duì)象表,找出對(duì)象類型是TABLE類型的對(duì)象名字
【table view sequence index synonym等都是oracle中的對(duì)象】
//注意字符串的值是區(qū)分大小寫的
select object_name
from user_objects
where object_type = upper('synonym');
輸出結(jié)果可以分為兩類：實(shí)際存在的，刪除后的殘留數(shù)據(jù)
//查詢用戶對(duì)象表,找出對(duì)象類型的類型都有哪些
select distinct object_type
from user_objects;
//查詢出s_emp表中的列及其對(duì)應(yīng)的約束名字
select column_name,constraint_name
from user_cons_columns
where table_name = 'S_EMP';
//查詢出s_emp表中的約束名字
select constraint_name
from user_constraints
where table_name = 'S_EMP';
注意: 表名必須大寫。
2、以all開頭的數(shù)據(jù)字典：? 包含當(dāng)前用戶有權(quán)限訪問的所有對(duì)象的信息
//查到當(dāng)前用戶有權(quán)限訪問的對(duì)象
select table_name from all_tables;?
3、以dba開頭的數(shù)據(jù)字典：? 包含數(shù)據(jù)庫所有相關(guān)對(duì)象的信息。
//只能是有dba權(quán)限的用戶查詢,能查到數(shù)據(jù)庫中所有對(duì)象
select table_name from dba_tables;
(sys system,conn切換用戶system,密碼oracle)
其他視圖: DICTIONARY
dictionary 視圖中只有倆列:
TABLE_NAME 表示當(dāng)前表的名字
COMMENTS 表示對(duì)這個(gè)表的描述
SQL> desc dictionary
名稱
-----------------------------------------
TABLE_NAME
COMMENTS
select *
from dictionary
where table_name='USER_TABLES';
select *
from dictionary
where table_name='ALL_TABLES';
select table_name
from dictionary
where table_name like 'USER%';
select table_name
from dictionary
where table_name like 'V$%';
第十章：數(shù)據(jù)字典(了解)
作用:幫助用戶了解當(dāng)前數(shù)據(jù)庫的一些信息或是對(duì)象的信息或是用戶的信息.
1).數(shù)據(jù)字典在數(shù)據(jù)庫被創(chuàng)建時(shí)創(chuàng)建的。
2).數(shù)據(jù)字典中的數(shù)據(jù)被數(shù)據(jù)庫服務(wù)器自動(dòng)更新和維護(hù)
1.常見的數(shù)據(jù)字典(它們都是視圖)
USER開頭的視圖里面存放著用戶自己擁有的對(duì)象(表、視圖、索引、同義詞、序列)
ALL開頭的視圖存放著用戶有權(quán)限查看的對(duì)象
DBA開頭的視圖存放著數(shù)據(jù)庫所有的對(duì)象
V$開頭的視圖存放數(shù)據(jù)庫運(yùn)行的一些性能屬性數(shù)據(jù)
2.以u(píng)ser開頭的數(shù)據(jù)字典
包含當(dāng)前用戶所擁有的相關(guān)對(duì)象信息。
//查詢用戶擁有的所有表的名字
select table_name
from user_tables;
//查詢用戶對(duì)象表,找出對(duì)象類型是TABLE類型的對(duì)象名字
//table view sequence index(只要是唯一的列，默認(rèn)索引) synonym等都是oracle中的對(duì)象
//注意【字符串的值是區(qū)分大小寫】的
select object_name
from user_objects
where object_type = upper('table');
//查詢用戶對(duì)象表,找出對(duì)象類型的類型都有哪些
select distinct object_type
from user_objects;
//查詢出s_emp表中的列及其對(duì)應(yīng)的約束名字
select constraint_name, column_name
from user_cons_columns
where table_name = 'S_EMP';
//查詢出s_emp表中的約束名字
select constraint_name
from user_constraints
where table_name = 'S_EMP';
3.以all開頭的數(shù)據(jù)字典
包含當(dāng)前用戶有權(quán)限訪問的所有對(duì)象的信息
//查到當(dāng)前用戶有權(quán)限訪問的對(duì)象
select table_name from all_tables;?
4.以dba開頭的數(shù)據(jù)字典
包含數(shù)據(jù)庫所有相關(guān)對(duì)象的信息。
//只能是有dba權(quán)限的用戶查詢,能查到數(shù)據(jù)庫中所有對(duì)象
select table_name from dba_tables;
(conn切換用戶,輸入用戶名system,? 輸入密碼oracle)
5.其他視圖
dictionary視圖中只有倆列:
TABLE_NAME 表示當(dāng)前【表的名字】
COMMENTS 表示對(duì)這個(gè)【表的描述】
SQL> desc dictionary
名稱
--------------------
TABLE_NAME
COMMENTS
select *
from dictionary
where table_name='USER_TABLES';
select *
from dictionary
where table_name='ALL_TABLES';
select table_name
from dictionary
where table_name like 'USER%';
select table_name
from dictionary
where table_name like 'V$%';
第十一章：DML語句 和 事務(wù)控制
主要內(nèi)容:
DML語句，直接操作數(shù)據(jù)庫對(duì)象 里面的數(shù)據(jù)
insert into
update
delete
事務(wù)控制語句
commit
savepoint
rollback
sql語句的分類:
select查詢語句
DML insert update delete
DDL create alter drop truncate rename
DCL
事務(wù)控制語句 【針對(duì)DML】
DML: insert update delete
測(cè)試用表: 沒有主外鍵關(guān)聯(lián)
create table t_user(
id number,
name varchar2(50) constraint user_name_nn not null,
email varchar2(50),
gender char(1),
age number,
birthday date,
constraint user_id_pk primary key(id),
constraint user_email_un unique(email),
constraint user_gender_ck check(gender in('f','m'))
);
drop table t_user;
1.insert語句學(xué)習(xí)
向表中插入數(shù)據(jù):
格式：【insert into 表名[(列名1,列名2,...)] values(對(duì)應(yīng)值1，對(duì)應(yīng)值2,...);】
注意：在插入數(shù)據(jù)時(shí)，要特別【留意約束條件】。
1).默認(rèn)是向表中的每一個(gè)列中【依次】插入數(shù)據(jù)
insert into t_user values(1,'tom','abc','f',20,'11-8月-98');
注意:違反任意一種約束那么就插入數(shù)據(jù)失敗
2).也可以指明向表中的哪些列插入數(shù)據(jù)
insert into t_user(id,name,email,gender,age,birthday)? values(2,'tom','abc1','f',20,'11-8月-98');
注意:可以任意交換下面列名的位置,只有values語句中的值也對(duì)應(yīng)交換即可
3).列的值可以是null的話,那么也在插入的時(shí)候不指定這個(gè)列
注意:【unique約束】和【check約束】的值,都【可以為null】
【主鍵約束】和【非空約束】的值,都不可以為null
insert into t_user(id,name,email,gender) values(3,'tom','abc3','f');
insert into t_user(id,name,email) values(3,'tom','abc4');
insert into t_user(id,name) values(5,'tom');
使用運(yùn)行時(shí)參數(shù)設(shè)置需要輸入表中的值
insert into t_user(id,name) values(&id,'&name');
2.insert插入的特殊情況
我們可以把【select語句查詢結(jié)果】插入到表中！
但是的有個(gè)【前提，就是查詢列】的順序和要插入表中列的【順序一致】(數(shù)據(jù)類型)。另外查詢結(jié)果值要【滿足其他約束】條件。
insert into t_user(id,name,birthday)
select id,last_name,start_date
from s_emp;
3.update語句
格式： update 表名 set 列名=值[,列名=值,...]
? [where ...]
1).修改表中所有行的age值為20歲
update t_user set age=20;
2).修改表中所有數(shù)據(jù)的age和gender的值
update t_user set age=25,gender='m';
3).修改表中id小于10數(shù)據(jù)的age和gender的值為null
update t_user set age=null,gender=null where id<10;
4).修改id為18的用戶的名字為zhangsan
update t_user set name='zhangsan' where id=18;
4.delete語句
格式：delete from 表名 [where ...];
1).刪除表中id大于20的用戶信息
delete from t_user where id>20;
2).刪除名字為張三的用戶信息
delete from t_user where name='zhangsan';
3).刪除表中所有的數(shù)據(jù)
delete from t_user;
以上是DML語句(insert update delete)語句的基本用法,下面我們重點(diǎn)測(cè)試一下【主外鍵相關(guān)的DML操作】。
5.進(jìn)一步使用DML操作
測(cè)試主外鍵關(guān)聯(lián)的情形
先準(zhǔn)備兩個(gè)表，里面有主外鍵。
create table t_customer(
id number,
name varchar2(20) constraint customer_name_nn not null,
constraint customer_id_pk primary key(id)
);
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);
drop table t_order;
drop table t_customer;
1).準(zhǔn)備操作數(shù)據(jù)insert語句：
//t_customer表中插入數(shù)據(jù)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');
2).往t_order表中插入正常數(shù)據(jù)
//customer_id外鍵列的值必須是t_customer表中出現(xiàn)過的
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
3).表中插入無效數(shù)據(jù)
//插入出錯(cuò),因?yàn)?這個(gè)值并沒有在t_customer表中出現(xiàn)過的
insert into t_order(id,price,customer_id) values(3,3000,6);
4).其他注意事項(xiàng)
//t_order表中插入數(shù)據(jù)
//默認(rèn)情況下,【外鍵列值可以為空】
insert into t_order(id,price,customer_id) values(3,3000,null);
insert into t_order(id,price) values(4,4000);
注意:如果在外鍵列上加一個(gè)非空約束,那么這個(gè)外鍵列的值就不能為null了【可以給一個(gè)列上添加多種約束】
//t_order表中插入數(shù)據(jù)
//默認(rèn)情況下,【外鍵列上值可以重復(fù)】
insert into t_order(id,price,customer_id) values(5,5000,1);
insert into t_order(id,price,customer_id) values(6,6000,1);
注意:如果在外鍵列上加一個(gè)唯一約束,那么這個(gè)外鍵列的值就不能重復(fù)了(可以給一個(gè)列上添加多種約束)
6.update語句
1).準(zhǔn)備工作
把倆個(gè)測(cè)試表刪除，然后重新創(chuàng)建,再往表中插入一些數(shù)據(jù)
drop table t_order;
drop table t_customer;
//t_customer表中插入數(shù)據(jù)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');
//t_order表中插入數(shù)據(jù)
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
2).修改指定列的值
//把t_order表中id=1的數(shù)據(jù)的customer_id列修改為3
update t_order set customer_id = 3 where id = 1;
//把t_order表中id=1的數(shù)據(jù)的customer_id列修改為null
update t_order set customer_id = null where id = 1;
注意，主外鍵約束相關(guān)的列，修改的值一定要符合要求。
//把t_order表中id=1的數(shù)據(jù)的customer_id列修改為20
update t_order set customer_id = 20 where id = 1;
sql執(zhí)行出錯(cuò),因?yàn)榫蜎]id=20的顧客
7.delete語句
1).普通刪除情況
//刪除t_order表中的的所有數(shù)據(jù)
delete from t_order;
可以成功刪除,沒有問題,因?yàn)閯h除t_order不會(huì)對(duì)t_costomer表的數(shù)據(jù)產(chǎn)生任何影響.
//t_order表中插入數(shù)據(jù)
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
//刪除t_customer表中id=3的數(shù)據(jù)
delete from t_customer where id = 3;
可以刪除成功,t_order表中外鍵列中沒有引用過這個(gè)值
2).刪除時(shí)候特殊情況
//刪除t_customer表中id=1的數(shù)據(jù)
delete from t_customer where id = 1;
刪除失敗,因?yàn)閠_order表中外鍵列中已經(jīng)引用了這個(gè)值
8.on delete語句
【在上述那樣的情況下,on delete 語句就可以起作用了】
級(jí)聯(lián)cascade針對(duì)表的刪除， on delete針對(duì)表中列的刪除
on delete語句
on delete no action【默認(rèn)情況:什么不都寫】
on delete cascade
on delete set null
1).情形1測(cè)試，默認(rèn)情況下的on delete
如果在【建外鍵的時(shí)候,不加on delete語句,就是on delete no action】
例如:
先建立兩張表，主外鍵關(guān)聯(lián)
create table t_customer(
id number,
name varchar2(20) constraint customer_name_nn not null,
constraint customer_id_pk primary key(id)
);
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);
//drop table t_order;
//drop table t_customer;
然后插入測(cè)試數(shù)據(jù):
//t_customer表中插入數(shù)據(jù)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
insert into t_customer(id,name) values(3,'tom3');
//t_order表中插入數(shù)據(jù)
insert into t_order(id,price,customer_id) values(1,1000,1);
insert into t_order(id,price,customer_id) values(2,2000,2);
刪除主鍵列數(shù)據(jù)
delete from t_customer where id = 1;
刪除失?。?ORA-02292: 【違反完整約束條件】 - 已找到子記錄
2).情形2測(cè)試 on delete cascade(應(yīng)用在外表)
先建立兩張表出來，
【聲明外鍵列的時(shí)候在最后加上on delete cascade語句】
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete cascade
);
表中插入數(shù)據(jù)，為刪除做準(zhǔn)備；
最后做和上述操作相同的刪除測(cè)試；
刪除主表中 id為1的數(shù)據(jù)(外表中使用了1這個(gè)值)
delete from t_customer where id = 1;
刪除成功,同時(shí)級(jí)聯(lián)(cascade)刪除了t_order表中所關(guān)聯(lián)的那條數(shù)據(jù)。
注意，【on delete 語句出現(xiàn)在外鍵列約束 后面】。
3).情形3，on delete set null
刪除兩張表，重新建立，在聲明外鍵列的時(shí)候加入on delete set null語句
create table t_order(
id number,
price number,
customer_id number,
constraint order_id_pk primary key(id),
constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete set null
);
insert插入測(cè)試數(shù)據(jù)；
開始刪除測(cè)試，刪除主表中id為1的行。
delete from t_customer where id = 1;
刪除成功；
同時(shí)我們會(huì)發(fā)現(xiàn),t_order表中所關(guān)聯(lián)的那條數(shù)據(jù)的【外鍵值已經(jīng)被設(shè)置為null】
****************
1.數(shù)據(jù)庫事務(wù) DML
數(shù)據(jù)庫操作過程中，如果操作有誤，可以撤銷相應(yīng)操作(一系列);如果反復(fù)確定操作無誤，可以使之生效。
關(guān)鍵字:
commit提交事務(wù);
rollback回滾事務(wù);
savepoint設(shè)置回滾點(diǎn);
2.事務(wù)的產(chǎn)生與結(jié)束：
1).【DML語句執(zhí)行】的時(shí)候,如果當(dāng)前有事務(wù),那么就使用這個(gè)事務(wù);如果當(dāng)前沒有事務(wù),這個(gè)執(zhí)行的【DML就會(huì)產(chǎn)生一個(gè)新的事務(wù)】；
sqlplus test/test
insert
update
delete
create table ...;
2).只有DML語句才會(huì)產(chǎn)生事務(wù),【其他語句不會(huì)產(chǎn)生事務(wù)】；
3).commit/rollback/DDL語句都可以把當(dāng)前事務(wù)給結(jié)束掉；
4).【commit和DDL語句】結(jié)束事務(wù)的方式是把這個(gè)【事務(wù)提交】；
5).【rollback】結(jié)束事務(wù)的方式是把這個(gè)【事務(wù)回滾】。
注意:
【提交事務(wù)】是指讓這個(gè)事務(wù)里面的【所有操作都生效】到數(shù)據(jù)庫中；
【回滾】事務(wù)是指【撤銷】這個(gè)事務(wù)里所有操作。
3.具體測(cè)試
測(cè)試用表:
create table t_customer(
id number,
name varchar2(20) constraint customer_name_nn not null,
constraint customer_id_pk primary key(id)
);
drop table t_customer;
具體測(cè)試步驟:
1).使用倆個(gè)終端窗口,同一個(gè)賬號(hào)登錄到數(shù)據(jù)庫中,觀察事務(wù)是否提交對(duì)用戶查看數(shù)據(jù)的影響
注:一個(gè)用戶對(duì)A表做了DML操作,但是沒有提交事務(wù),這時(shí)候別的用戶是不能對(duì)A表再做其他的DML操作。(為了保證數(shù)據(jù)的安全和一致性)
insert into t_customer(id,name) values(1,'tom1');
insert into t_customer(id,name) values(2,'tom2');
2).事務(wù)實(shí)例1
insert ....產(chǎn)生事務(wù)A
update ... 這個(gè)操作是事務(wù)A中的操作
insert ..? 這個(gè)操作是事務(wù)A中的操作
commit;? ? 讓事務(wù)A里面的三個(gè)操作生效、事務(wù)A結(jié)束
delete ... 產(chǎn)生新的事務(wù)B
insert ..? 這個(gè)操作是事務(wù)B中的操作
insert ..? 這個(gè)操作是事務(wù)B中的操作
insert ..? 這個(gè)操作是事務(wù)B中的操作
rollback;? 讓事務(wù)B中的四個(gè)操作都撤銷,事務(wù)B結(jié)束
3).事務(wù)實(shí)例2
insert ....產(chǎn)生事務(wù)A
update ... 這個(gè)操作是事務(wù)A中的操作
insert ..? 這個(gè)操作是事務(wù)A中的操作
DDL語句;? 事務(wù)A會(huì)被提交
rollback;? 這時(shí)候回滾已經(jīng)對(duì)事務(wù)A不起作用,因?yàn)槭聞?wù)A以及被提交了
注:create語句 drop語句 alter語句,truncate,rename等都屬于DDL語句
4).回滾點(diǎn)/保存點(diǎn) savepoint
例如:
DML語句1
savepoint A
DML語句2
savepoint B
DML語句3
rollback to A/B
這個(gè)時(shí)候可以通過這個(gè)回滾點(diǎn)讓事務(wù)回滾到指定的位置,如果不指定回滾點(diǎn)而是【直接rollback】,那么【事務(wù)會(huì)一下子回滾完】。
特別注意:
【rollback to】到回滾點(diǎn)之后,這個(gè)【事務(wù)可能并沒結(jié)束】,這個(gè)時(shí)候還可以接著回滾或者commit提交事務(wù)。
create table t_user(
id number primary key,
name varchar2(100),
salary number
);
drop table t_user;
例如:
insert into t_user values(1,'tom',1000);
savepoint A;
insert into t_user(id,name) values(2,'zs');
savepoint B;
delete from t_user;
rollback to B;
然后查詢看結(jié)果
select * from t_user;
4.事務(wù)特征ACID(了解)
原子性：Atomicity
同時(shí)成功或者同時(shí)失敗
一致性：Consistency
事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。
隔離性：Isolation
事務(wù)操作應(yīng)該相互獨(dú)立
持久性：Durability
事務(wù)所做的影響 ，在事務(wù)結(jié)束之后應(yīng)該能夠是持久的。
5.isolation事務(wù)隔離級(jí)別(了解)
事務(wù)中會(huì)產(chǎn)生的問題:
1).臟讀? 主要針對(duì)update操作。 一個(gè)事務(wù)A讀到另一個(gè)事務(wù)B中修改過但是還沒有提交的數(shù)據(jù)
2).不可重復(fù)讀? 主要針對(duì)update操作。 一個(gè)事務(wù)A在第一次讀數(shù)據(jù)和第二次讀數(shù)據(jù)之間,有另一個(gè)事務(wù)B把這個(gè)數(shù)據(jù)更改并提交了,所以就出現(xiàn)了事務(wù)A里面讀一個(gè)數(shù)據(jù)倆次,但是讀到的結(jié)果是不同的。
3).幻讀? 主要針對(duì)的是insert/delete操作。事務(wù)A第一次用where條件篩選出了10條數(shù)據(jù),事務(wù)A第二次用同樣的where條件篩選出的卻是11條數(shù)據(jù),因?yàn)槭聞?wù)B在事務(wù)A的第一次和第二次查詢直接進(jìn)行了插入操作,并且插入的這個(gè)數(shù)據(jù)滿足事務(wù)A的where篩選條件.
事務(wù)隔離級(jí)別有:
read-uncommitted? 不提交也能讀
read-committed? ? 提交之后才能讀 解決了臟讀
repeatable-read? 解決了臟讀和不可重復(fù)讀
serializable? ? ? 三個(gè)問題都解決了
【級(jí)別越高解決的問題越多但是效率越低】。
注意:并不是所有數(shù)據(jù)庫都支持這四種事務(wù)隔離級(jí)別,比如【oracle就只支持第二種和第四種】這倆種,比如mysql就四種全支持.
oracle里面【默認(rèn)的事務(wù)隔離級(jí)別是第二種】:read-committed
oralce里面設(shè)置事務(wù)隔離級(jí)別:
Set Transaction Isolation Level Read Uncommitted
Set Transaction Isolation Level Read Committed
Set Transaction Isolation Level Read Repeatable
Set Transaction Isolation Level Serializable
此知識(shí)點(diǎn)重點(diǎn)了解，后期框架的學(xué)習(xí)會(huì)具體涉及。
******************
第十二章: 修改表和約束(alter語句)?
alter也屬于DDL語句。
update修改，修改數(shù)據(jù)庫表|視圖中數(shù)據(jù)；
alter修改對(duì)象結(jié)構(gòu)。
1.準(zhǔn)備測(cè)試用表
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
//drop table t_user;
2.alter具體作用
alter可以修改表的結(jié)構(gòu)，具體格式為:
alter table 表名 add|drop|modify|disable|enable ...;
2.1 在表中【添加一個(gè)新列】
alter table t_user
add birthday date;
2.2 【刪除表的某列】
alter table t_user
drop column birthday;
2.3 給表中的【列添加約束】
這個(gè)約束相當(dāng)于之前的表級(jí)約束
alter table t_user
add constraint user_name_un
unique(name);
//測(cè)試剛添加的唯一約束是否生效
insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'zs');
2.4 【刪除表中的約束】
alter table t_user
drop constraint user_name_un;
2.5 修改表中某列的類型
alter table t_user
modify name varchar2(100) not null;
2.6 讓【約束失效】(必須知道約束的名字)
alter table t_user
disable constraint user_id_pk cascade;
(該主鍵可能被其他表引用 作為外鍵使用，cascade級(jí)聯(lián)會(huì)使之失效)
//測(cè)試是否設(shè)置成功
insert into t_user(id,name) values(1,'zs1');
insert into t_user(id,name) values(1,'zs2');
2.7 讓失效的【約束再次生效】
alter table t_user
enable constraint user_id_pk;
3.其他DDL操作
truncate截?cái)?/p>
rename重命名
3.1 截?cái)啾碇械臄?shù)據(jù)
截取不需要提交,默認(rèn)已經(jīng)提交,并且不能回滾
truncate table t_user;
相當(dāng)于(將表中數(shù)據(jù)全部刪除，然后提交):
delete from t_user;
commit;
3.2 修改表的名字
rename t_user to mytest;
rename mytest to t_user;
4.注釋操作(了解)
comment是oracle數(shù)據(jù)庫系統(tǒng)關(guān)鍵字，可以用來給表或列添加注釋，有利于用戶對(duì)表結(jié)構(gòu)以及數(shù)據(jù)庫對(duì)象含義的理解。
用戶可以利用數(shù)據(jù)字典查看comment定義的信息。
1).給表添加注釋
格式:
comment on table 表名 is '注釋';
comment on table t_user is '很好';
2).給列添加注釋
格式:
comment on column 表名.列名 is '注釋';
comment on column t_user.name is 'good';
3).查看表中注釋(利用【數(shù)據(jù)字典】)
select * from user_tab_comments
where table_name=upper('t_user');
4).查看列中的注釋
select * from user_col_comments
where
comments is not null
and
table_name=upper('t_user');
對(duì)象: 表 視圖 索引 序列 同義詞
第十三章: 序列
Sequence 序列
作用:幫用戶自動(dòng)生成主鍵列的值(非空唯一)
currval? nextval
序列名.currval;
序列名.nextval;
1. 創(chuàng)建序列
一般不需要設(shè)置sequence的屬性,使用默認(rèn)的方式去創(chuàng)建就可以了.
基本格式:
create sequence 序列名;
完整格式:
create sequence 序列名
[INCREMENT BY n]? 每次拿出值加多少
[START WITH n]? ? 初始值從幾開始
[{MAXVALUE n | NOMAXVALUE}]? 最大值
[{MINVALUE n | NOMINVALUE}]? 最小值
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
如果需要設(shè)置屬性,那么就加上下面的語句.
[INCREMENT BY n]? 每次拿出值加多少
[START WITH n]? ? 初始值從幾開始
[{MAXVALUE n | NOMAXVALUE}]? 最大值
[{MINVALUE n | NOMINVALUE}]? 最小值
[{CYCLE | NOCYCLE}]? 到了最大值后是否循環(huán)(如果【循環(huán)會(huì)從1開始】,不循環(huán)出錯(cuò))
[{CACHE n | NOCACHE}] 每次在緩存里面放多少個(gè)值.
2.具體實(shí)例
1).創(chuàng)建序列并設(shè)置屬性
create sequence seq_test
increment by 2
start with 44
maxvalue 60
minvalue 10
cycle
nocache;
使用默認(rèn)屬性創(chuàng)建序列
create sequence seq_test;
2).刪除序列(格式類似 刪除表)
drop sequence seq_test;
3).具體序列操作
對(duì)應(yīng)序列,我們只有倆種操作:
a.獲得序列中的下一個(gè)值 【序列名.nextval】
//這個(gè)值對(duì)于當(dāng)前這個(gè)序列來的其他值來說,肯定是非空唯一
select seq_test.nextval
from dual;
b.查詢序列中當(dāng)前的值是多少【序列名.currval】
select seq_test.currval
from dual;
4).實(shí)際應(yīng)用
向t_user表插入數(shù)據(jù),其中id值可以需要生成
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
drop table t_user;
//創(chuàng)建序列
drop sequence seq_test;
create sequence seq_test;
//插入數(shù)據(jù) 使用序列產(chǎn)生id值
insert into t_user(id,name,salary)
values(seq_test.nextval,concat('tom',seq_test.currval),2000);
5).通過數(shù)據(jù)字典 查詢當(dāng)前用戶中 已經(jīng)定義的序列
select sequence_name
from user_sequences;
第十四章: 視圖view
1.視圖概念
視圖就是【提取一張或者多張表的數(shù)據(jù)】生成一個(gè)映射，操作視圖可以達(dá)到【操作原表】的效果，【方便數(shù)據(jù)的管理】以及實(shí)現(xiàn)【更安全】操作。
2.視圖的作用:
a.安全性 隱藏表中的重要數(shù)據(jù)
b.簡單性 代替一些比較長的sql語句
3.視圖的分類
視圖可以分為兩類，簡單視圖和復(fù)雜視圖。
簡單視圖:
? 視圖所代表的select語句中基于單個(gè)基表，且不包含函數(shù)和數(shù)據(jù)分組操作。
復(fù)雜視圖
? 視圖所代表的select中如果【有g(shù)roup by語句】,或者【有組函數(shù)】,或者【查詢的是多張表】,那么這樣的視圖就是復(fù)雜視圖.
注意: 僅僅由多表查詢的復(fù)雜視圖，可以刪除數(shù)據(jù)成功，但是【刪除的是外表】 中的數(shù)據(jù)，主表不會(huì)受到影響。
4.簡單視圖和復(fù)雜視圖的區(qū)別
通過【簡單視圖可以修改】原來表中的數(shù)據(jù), 這些修改包括插入數(shù)據(jù)，更新數(shù)據(jù)和刪除數(shù)據(jù)。
但是對(duì)于【復(fù)雜視圖】來說, 通過視圖修改數(shù)據(jù)必須滿足一定的規(guī)則。
復(fù)雜視圖是可以刪除數(shù)據(jù)的，會(huì)影響到原表。
? 可以插入數(shù)據(jù)，但是有一定限制。
5.創(chuàng)建與刪除視圖
創(chuàng)建視圖(【由select語句獲得視圖】)
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查詢
[WITH CHECK OPTION]
[WITH READ ONLY];
OR REPLACE：如果視圖已經(jīng)存在，則替換舊視圖。
? ? FORCE：即使基表不存在，也可以創(chuàng)建該視圖，但是該視圖不能正常使用，當(dāng)基表創(chuàng)建成功后，視圖才能正常使用。
? ? NOFORCE：如果基表不存在，無法創(chuàng)建視圖，該項(xiàng)是默認(rèn)選項(xiàng)。
WITH CHECK OPTION： 指出在視圖上所進(jìn)行的修改都要符合select_statement 所指定的限制條件.
? ? WITH READ ONLY：說明視圖是只讀視圖，不能通過該視圖進(jìn)行增刪改操作。現(xiàn)實(shí)開發(fā)中，基本上不通過視圖對(duì)表中的數(shù)據(jù)進(jìn)行增刪改操作。
刪除視圖
drop view 視圖名字;
注意:
在視圖定義時(shí)沒有with read only的前提下,如果視圖包含下面內(nèi)容
a.分組函數(shù)
b.group by
c.表達(dá)式
d.rownum偽列
則不可以通過【視圖刪除】表中內(nèi)容。
【插入數(shù)據(jù)】時(shí)，除了要滿足以上要求，還要滿足【沒有被視圖包含的列】可以為null值。
如果建立視圖時(shí)包含with check option，則還需滿足指定約束條件。
【視圖主要用來查詢，盡量不要DML操作】。
6.實(shí)際案例
1).準(zhǔn)備測(cè)試表:
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
//drop table t_user;
2).插入測(cè)試數(shù)據(jù):
insert into t_user(id,name,salary)
select id,last_name,salary
from s_emp;
3).創(chuàng)建視圖
create or replace view v_test
as
select *
from t_user
where id > 10;
4).查看視圖內(nèi)容
select *
from v_test;
具體測(cè)試：
通過*簡單視圖* 對(duì)原來的表進(jìn)行數(shù)據(jù)的刪除/更新/插入
delete from v_test where id=16;
update v_test set name = 'zhangsan' where id = 20;
insert into v_test(id,name,salary) values(28,'tom1',3000);
以上操作都可以成功。
7.視圖創(chuàng)建額外關(guān)鍵字
在創(chuàng)建視圖時(shí)，在Sql語句的最后跟上指定關(guān)鍵字，可以添加額外的約束。
1).with read only語句
特點(diǎn):【只能】通過視圖進(jìn)行【查詢】數(shù)據(jù),不能修改
例如:
create or replace view v_test1
as
select *
from t_user
where id > 10
with read only;
這個(gè)視圖v_test將來只能查詢,不能進(jìn)行修改
2).with check option語句
特點(diǎn):【通過視圖修改的信息】，必須可以【通過這個(gè)視圖能夠顯示】出來,否則就操作失敗
準(zhǔn)備測(cè)試用的表及其數(shù)據(jù)
drop table t_user;
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
insert into t_user values(1,'tom',1000);
insert into t_user values(2,'tom2',2000);
創(chuàng)建視圖:
create or replace view v_test
as
select id,name,salary
from t_user
where id>20
with check option;
查詢視圖中的數(shù)據(jù)
select * from v_test;
插入數(shù)據(jù)會(huì)報(bào)錯(cuò)，因?yàn)檫@個(gè)操作通過視圖顯示不出來
insert into v_test values(3,'tom3',3000);
更新也會(huì)失敗 因?yàn)檫@個(gè)操作通過視圖顯示不出來
update v_test
set name='lily'
where id=1;
更新成功 因?yàn)檫@個(gè)操作通過視圖可以顯示出來
update v_test
set name='lily'
where id=2;
8.復(fù)雜視圖
例如:
create or replace view v_test5
as
select id,avg(salary) avgs
from t_user
group by id;
復(fù)雜視圖盡量用來查詢,不要DML，因?yàn)椴僮鞅容^復(fù)雜。
****************
alter table 表名
add 列名 數(shù)據(jù)類型 約束;
add constraint 約束名(列名);
drop column 列名;
drop constraint 約束名;
modify 列名 數(shù)據(jù)類型 constraint 約束名 約束；
disable constraint 約束名 [cascade];
enable constraint 約束名；
truncate table 表名;
rename 表名 to 新表名；
序列
sequence
create sequence 序列名
increment by n
start with n
maxvalue n|nomaxvalue
minvalue n|nominvalue
cycle|nocycle
cache|nocache;
序列名.nextval? 序列名.currval;
生成主鍵值。
視圖
一張或多張表[原始數(shù)據(jù)|加工數(shù)據(jù)]的映射
create view 視圖名
as
select語句；
簡單視圖?
一個(gè)表，沒有g(shù)roup by，沒有函數(shù)
增刪改查 (符合基本約束)
操作視圖 == 操作原表
復(fù)雜視圖
多個(gè)表，函數(shù)，group by
增刪改查(操作的是外表)
******************
第十五章: 索引(index)
1. 索引的概念
a. 類似書的目錄結(jié)構(gòu)
b. Oracle 的"索引"是建立在表上的【可選對(duì)象】，能提高SQL查詢語句的速度
c. 索引直接指向包含所查詢值的行的位置，減少磁盤I/O
d. 【索引和表是相互獨(dú)立】的物理結(jié)構(gòu),刪除或創(chuàng)建都不影響表本身。
e. Oracle 【自動(dòng)使用并維護(hù)索引】，插入、刪除、更新表后，自動(dòng)更新索引
2. 索引的創(chuàng)建
索引的創(chuàng)建分為兩種情況，自動(dòng)創(chuàng)建和用戶創(chuàng)建。
1).自動(dòng)創(chuàng)建
當(dāng)在表中指定了primary Key或者unique約束時(shí)會(huì)【自動(dòng)創(chuàng)建唯一值索引】。
2).用戶創(chuàng)建和刪除
用戶可以創(chuàng)建非唯一值索引以【提高在訪問數(shù)據(jù)時(shí)的效率】。
創(chuàng)建索引：
create index 索引名
on 表名(列名);
例如：
create index emp_index
on s_emp(last_name);
刪除索引:
drop index 索引名;
例如:
drop index emp_index;
創(chuàng)建成功后可以通過【數(shù)據(jù)字典】查看：
select index_name from user_indexes;
3.給某列創(chuàng)建索引的原則（了解）
索引的最大優(yōu)勢(shì)是 提高效率。
a.列經(jīng)常作為where子句的限定條件或者作為連接條件
b.列包含的數(shù)據(jù)量很大，并且很多非空的值。
c.兩個(gè)或者更多列頻繁的組合在一起作為where的限定條件或者連接條件
d.列總是作為搜索條件
e.索引查出的數(shù)據(jù)量占總數(shù)據(jù)量的2%~4%
f.索引不是越多越好，不是索引越多越能加速查找。
g.要建立索引的表不經(jīng)常進(jìn)行修改操作
注意:
1.在表中的某一個(gè)合適的列加入上了索引,那么也只有在【數(shù)據(jù)量很大】的時(shí)候,【才能體現(xiàn)】出這個(gè)查詢的【效率】.
2.【索引一旦建立成功】,那么之后這個(gè)索引就由【系統(tǒng)來管理】,我們自己是控制不了的.
4.索引的種類(了解)
Single column 單行索引
Concatenated? 多行索引
Unique ? 唯一索引
NonUnique? ? 非唯一索引
5.索引結(jié)構(gòu)分類(了解)
B-tree(默認(rèn)是這種結(jié)構(gòu))
適合大量的增、刪、改（OLTP）；
不能用包含OR操作符的查詢；
適合高基數(shù)的列（唯一值多）
典型的樹狀結(jié)構(gòu)；
位圖
做UPDATE代價(jià)非常高；
非常適合OR操作符的查詢；
反轉(zhuǎn)關(guān)鍵字
基于函數(shù)
第十六章: 用戶權(quán)限控制
1.創(chuàng)建用戶(管理員權(quán)限)
具體語法:
create user 用戶名 identified by 密碼
例如
create user zhangsan identified by zhangsan;
2.刪除用戶
drop user zhangsan cascade;
3.賦予權(quán)限
格式:
grant 具體權(quán)限s to 用戶;
例如:把建表 建序列 建視圖的權(quán)限賦給zhangsan
grant create table,create sequence,create view
to zhangsan;
也可以把connect角色和resource角色賦給zhangsan
角色是一組權(quán)限的集合
grant connect,resource to zhangsan;
注意: 只是登陸oracle數(shù)據(jù)庫的話 需要的權(quán)限是【create session】登錄權(quán)限
4.修改密碼
(password zhangsan也可以修改密碼)
alter user 用戶名 identified by 密碼;
例如:
alter user zhangsan identified by zhangsan123;
5.賦予某一個(gè)用戶某種對(duì)象操作的權(quán)限
grant 操作 on 對(duì)象 to 用戶名;
注意，給指定用戶賦予操作某種對(duì)象的權(quán)限，對(duì)象可以是表，也可以是視圖，還可以是索引等等。
具體測(cè)試:
步驟1：使用jd1713用戶登錄，然后給zhangsan用戶授予查詢權(quán)限
grant select
on t_user
to zhangsan;
步驟2：使用zhangsan用戶登錄，然后測(cè)試select語句是否可以使用
select *
from t_user;
注意，這樣寫會(huì)出錯(cuò)，系統(tǒng)會(huì)在當(dāng)前用戶中找t_user這個(gè)對(duì)象。
所以必須改成 jd1713.t_user;
select *
from jd1713.t_user;
也可以查看表結(jié)構(gòu):
desc jd1713.t_user;
6.回收權(quán)限
revoke operator on object from user;
例如:
revoke select
on t_user
from zhangsan;
7.同義詞synonym(別名)
作用:可以隱藏表原來的名字。
思考一下dual，任何用戶都可以使用這個(gè)對(duì)象，但是并沒有正常使用? 【用戶名.dual】，為什么？ 就是因?yàn)閐ual是一個(gè)同義詞。
分為:私有同義詞 公共同義詞
8.私有同義詞
【注意：同義詞 和 原對(duì)象 是同一個(gè)東西】。
創(chuàng)建私有同義詞格式：
create synonym 同義詞
for 對(duì)象名;
1).給表t_user創(chuàng)建一個(gè)私有同義詞
create synonym my_test
for t_user;
創(chuàng)建成功后，一個(gè)表有倆名字。
2).給用戶zhangsan授權(quán)可以查詢my_test
grant select
on my_test
to zhangsan;
3).收回用戶zhangsan查詢my_test的權(quán)限
revoke select
on my_test
from zhangsan;
//利用數(shù)據(jù)字典查看同義詞synonyms
//用戶創(chuàng)建的同義詞有哪些
select synonym_name
from user_synonyms;
//用戶有權(quán)利查詢的同義詞有哪些
select synonym_name
from all_synonyms;
//用戶有權(quán)利查詢的同義詞有哪些是以字母D開頭的
//注意:表中的數(shù)據(jù)都是大寫存在
select synonym_name
from all_synonyms
where synonym_name like 'D%';
結(jié)果可以看到我們常用的dual
9.刪除同義詞synonym
格式:
drop [public] synonym 同義詞;
刪除私有同義詞
drop synonym my_test;
刪除公共同義詞
drop public synonym my_test;
10.公共的同義詞
dual就是【公共的同義詞,所有用戶都可以使用】。
注意，普通用戶沒有權(quán)限創(chuàng)建 public synonym公共同義詞，
所以我們需要用dba的身份登錄到數(shù)據(jù)庫中去創(chuàng)建,sqlplus "/as sysdba",或者使用系統(tǒng)管理員system用戶登錄創(chuàng)建。
1).創(chuàng)建同義詞:
create public synonym psyn
for jd1713.t_user;
jd1713.t_user表示的是jd1713用戶下面的t_user對(duì)象。
2).將查詢這個(gè)同義詞的權(quán)限賦予所有人
grant select on psyn to public;
3).然后使用其他用戶登錄，就可以通過這個(gè)公共的同義詞來查詢jd1713用戶下面的t_user對(duì)象了。
【dual就是一個(gè)公共的同義詞】
10,數(shù)據(jù)庫的導(dǎo)入導(dǎo)出
應(yīng)用場景:
你現(xiàn)在項(xiàng)目里面使用數(shù)據(jù)庫，保存了一定量的數(shù)據(jù)；現(xiàn)在重裝系統(tǒng)，重裝數(shù)據(jù)庫軟件(數(shù)據(jù)丟失)，重裝之前，就可以使用導(dǎo)出exp命令 數(shù)據(jù)庫進(jìn)行備份；
重裝完成以后，使用imp導(dǎo)入 命令 將數(shù)據(jù)庫數(shù)據(jù)重新導(dǎo)入 數(shù)據(jù)庫里面。
系統(tǒng)終端執(zhí)行
? 導(dǎo)出：exp? 根據(jù)提示按回車下一步即可
? 導(dǎo)入：imp ? 根據(jù)提示按回車下一步即可
1).具體導(dǎo)出過程
先exit退出登錄，調(diào)整當(dāng)前工作路徑到合適位置(cd 路徑名)
命令行輸入exp
輸入用戶名 密碼? 【輸入jd1713 jd1713，導(dǎo)出jd1713用戶信息】
按照提示，全部默認(rèn)往下操作
操作完成后，當(dāng)前工作目錄下多出一個(gè)目錄。
導(dǎo)入過程：
退出登錄
切換到正確的工作目錄
imp 輸入對(duì)應(yīng)用戶名和密碼 【zhangsan zhangsan】
按照提示，全部默認(rèn)往下操作 【中間有個(gè)驗(yàn)證，輸入jd1713這個(gè)用戶即可】
導(dǎo)入成功后，sqlplus登錄進(jìn)去，查詢有沒有多出來的表。
