數(shù)據(jù)庫(kù) DataBase
關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng) RDBMS
數(shù)據(jù)庫(kù)的功能： 存儲(chǔ)和管理數(shù)據(jù)。
數(shù)據(jù)庫(kù)較文本文件
文本文件存儲(chǔ)數(shù)據(jù)的缺點(diǎn):沒(méi)有數(shù)據(jù)類(lèi)型，只能存儲(chǔ)字符串;查詢(xún)數(shù)據(jù)不方便;不適合存儲(chǔ)大數(shù)據(jù)量;不安全
數(shù)據(jù)庫(kù)的優(yōu)點(diǎn):它克服了文本文件的以上缺點(diǎn)之外，還可以供多用戶(hù)同時(shí)訪(fǎng)問(wèn)。
數(shù)據(jù)庫(kù)的工作模式：使用SQL語(yǔ)言進(jìn)行通信:Structed Query Language(結(jié)構(gòu)化查詢(xún))
連接oracle步驟：
1. 登陸，利用telnet命令
2. 鍵入sqlplus username/password
3. 執(zhí)行sql命令對(duì)數(shù)據(jù)進(jìn)行CURD操作
4. 退出exit (斷開(kāi)連接，退出客戶(hù)端程序)
對(duì)數(shù)據(jù)庫(kù)的基本操作：
因?yàn)閿?shù)據(jù)庫(kù)中存儲(chǔ)數(shù)據(jù)的單位是表（table），所以我首先講的是對(duì)表的基本操作。
1.建表sql
語(yǔ)法：create table( columnname type constrain);
2.刪表sql
語(yǔ)法：drop table tablename;
3.對(duì)表的增刪改查。具體細(xì)節(jié)，后面將作詳細(xì)介紹。
將成批的sql語(yǔ)句放在.sql文件中，可用命令start/@ 路徑/文件名 的方式批量執(zhí)行
其它常用操作：
1.查看當(dāng)前用戶(hù)下有哪些表：select table_name from user_tables;
2.獲得表的結(jié)構(gòu)：desc 表名 desc是describe的簡(jiǎn)寫(xiě)
3.查詢(xún)表中數(shù)據(jù) 可用 select 列名，列名，... from 表名
sqlplus命令與sql命令的區(qū)別：
1. 執(zhí)行的客戶(hù)端不一樣。
2. sqlplus命令可以不以;作為結(jié)束，sql命令必須以;結(jié)束。
sqlplus命令：exit，desc 表名，start (@)文件名
col 列名 format 格式(控制查詢(xún)結(jié)果顯示)：
col empno format 9999
col sal format 9999.99
col job format a10
set pagesize 200
/ 執(zhí)行最近一次sql命令
list 顯示最近一次sql命令
c/舊值/新值 修改最近一次sql命令
conn 用戶(hù)名/密碼 切換用戶(hù)
help index 顯示幫助
CURD操作
select查詢(xún)
常規(guī)查詢(xún)：select 列1，列2,.... from 表名 where 條件;
注意其中列名如果是表的全部列，則可用*代替，不過(guò)它比直接寫(xiě)列名在執(zhí)行效率上要慢。
在sql語(yǔ)句中如果要用到字符串，則需使用''括起來(lái)，并且字符串的比較是區(qū)分大小寫(xiě)的。
在條件子句中如果用邏輯運(yùn)算符，記住它們是有優(yōu)先級(jí)別的：優(yōu)先級(jí)not> and > or，所以可用()來(lái)改變優(yōu)先級(jí)
模糊查詢(xún)：like 字符串,e.g:
查詢(xún)姓名中第二個(gè)字母是A的員工:select * from emp where ename like '_A%';
在模糊查詢(xún)中有兩種通配符：% 表示匹配0到多個(gè)任意字符；_表示匹配1個(gè)任意字符
查詢(xún)語(yǔ)句中可用的函數(shù)
一般函數(shù)sql:
1.select 100 + 200 (as) result from dual; //dual為系統(tǒng)表，它只有一行一列。result為列的一個(gè)別名，as可以省略不寫(xiě)
2.select abs(-4738) from dual;//abs()函數(shù)是求絕對(duì)值的
3.select mod(10, 3) from dual;//mod()函數(shù)是求模(余數(shù))的
4.select dbms_random.random() from dual;//dbms_random.random()用來(lái)生成隨機(jī)數(shù)
例：隨機(jī)生成一個(gè)1000以?xún)?nèi)的正整數(shù)：
select mod(abs(dbms_random.random()),1000 ) from dual;
字符串函數(shù)
1.連接字符串
select 'abc' || 'def' from dual;
select concat('abc','def') from dual;
select empno || '/' || ename || '/' || job from emp;
2.大小寫(xiě)轉(zhuǎn)換：lower()，upper()
select upper('fdjk') from dual;
3.select instr('abcdefg', 'c') from dual;ans:3
//instr 求子串在字符串中的初始位置(注意它是從1開(kāi)始的)
4.select substr('abcdefg', 4) from dual;ans:defg
//substr用來(lái)截取字符串，注意它也是從1開(kāi)始的
5.select length('abcdefg') from dual;//length()是用來(lái)求字符串長(zhǎng)度的
日期函數(shù)
1.select sysdate from dual;//sysdate表示的是當(dāng)前日期，它的類(lèi)型在oracle里為date類(lèi)型
2.to_char(日期, '格式') 將日期轉(zhuǎn)換為字符串
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate, 'q') from dual;//季度
select to_char(sysdate, 'd') from dual;//一個(gè)星期的第幾天(注意：以周日為1開(kāi)始計(jì)算)
select to_char(sysdate, 'day') from dual;//星期幾
select to_char(sysdate, 'am') from dual;//上下午
select to_char(sysdate, 'mon') from dual;//月份
日期的默認(rèn)格式為：dd-mon-yy;
3.trunc (截?cái)嗳掌?/span>) 返回值還是日期
select trunc(sysdate, 'year') from dual;//今年的1月1號(hào)0時(shí)0分0秒
select trunc(sysdate, 'month') from dual;
select trunc(sysdate, 'day') from dual;
4.對(duì)時(shí)間的加減
select sysdate - 5 from dual;//日期+ -時(shí)，它以天為單位的
select to_char(sysdate + 1/24,'hh24:mi:ss') from dual;//將當(dāng)前間加1小時(shí)
select add_months(sysdate, -1) from dual;//對(duì)月進(jìn)行減一
其它函數(shù)
1.對(duì)某列進(jìn)是否為null的條件選擇，可用is null，is not null e.g:
select * from emp where comm is not null;
2.對(duì)有可能取值為null的列進(jìn)行運(yùn)算時(shí)，需用到nvl()函數(shù)
e.g: select (sal+nvl(comm,0))*12 asal from emp;
//nvl(檢查值，替代值) 如果某列的值等于檢查值時(shí)，用替代值替代，此例是將null值替換成0，在Oracle中null是指無(wú)窮大
組函數(shù):
max() 求某列的最大值；min()，求某列的最小值； avg() 求某列的平均值； sum() 求某列值的和； count() 求某一列取值不為null的個(gè)數(shù)
select distinct job from emp;//distinct 去除重復(fù)取值
select count(distinct job) from emp;//去除重復(fù)記錄后，求個(gè)數(shù)
order by:對(duì)查詢(xún)結(jié)果進(jìn)行排序
select * from emp order by comm desc/asc;
中文排序
order by nlssort(列, '格式');
拼音 'NLS_SORT=SCHINESE_PINYIN_M'
筆畫(huà) 'NLS_SORT=SCHINESE_STROKE_M'
部首 'NLS_SORT=SCHINESE_RADICAL_M'
e.g: select * from product order by nlssort(productname, 'NLS_SORT=SCHINESE_PINYIN_M' );
group by： 分組
語(yǔ)法：group by 列1, 列2-->根據(jù)group by 之后的列，取值歸為一組，配合組函數(shù)求每組的最大值，最小值...
e.g: select max(sal) from emp group by deptno;
注意：如果使用了group by分組，則在select子句中出現(xiàn)的列必須是在group by子句中有的，如果不是，則必須配合組合函數(shù)一起使用
按多列分組：select count(*), deptno, job from emp group by deptno, job;
group by...having... 分組之后再進(jìn)行條件選擇
e.g:select max(sal) ,deptno from emp group by deptno having (max(sal) > 3000); //求每個(gè)部門(mén)最高工資大于3000的最高工資和部門(mén)編號(hào)
注意：where子句也能達(dá)到條件選擇的效果，如果where和having都能達(dá)到要求，從效率角度出發(fā)，應(yīng)優(yōu)先選擇where; 如果只能在分組之后才可以進(jìn)行條件選擇的話(huà)，則使用having。
偽列 (rowid, rownum) 不真正存在于表中的列
select empno,ename,rowid from emp;
rowid 特點(diǎn):
每張表中的rowid取值沒(méi)有重復(fù)
每個(gè)rowid對(duì)應(yīng)一條記錄，可以看做是此記錄的唯一標(biāo)識(shí)
通過(guò)rowid 能夠最快速地查找到記錄
rownum 為每條查詢(xún)結(jié)果產(chǎn)生一個(gè)編號(hào)
e.g: select empno,ename,rownum from emp;
查詢(xún)前5條記錄
select empno,ename,rownum from emp where rownum <=5;
select empno,ename,rownum from emp where rownum > 5;(錯(cuò))
select empno,ename,rownum from emp where rownum = 5;(錯(cuò))
注意：rownum用作比較條件時(shí)只能使用<或者<=比較；特例=1, >=1可以
select empno,ename,rownum from emp where rownum <=5;
語(yǔ)法順序
select ... from ... where 條件 group by ... having 條件 order by ...
執(zhí)行順序
where > group by > having> select >order by
子查詢(xún)
在很多時(shí)候單一的查詢(xún)不能完成實(shí)際的要求，而子查詢(xún)可以完對(duì)單表的復(fù)雜查詢(xún)
1.將子查詢(xún)結(jié)果作為主查詢(xún)的條件，e.g：
select * from emp where sal = (select max(sal) from emp);//查詢(xún)具有最高工資的員工信息
select * from emp where sal > (select avg(sal) from emp);//查詢(xún)所有工資高于平均工資
select * from emp where sal in (select max(sal) from emp group by deptno);//查詢(xún)每個(gè)部門(mén)工資最高的員工信息
2.將子查詢(xún)結(jié)果作為一張臨時(shí)表，作進(jìn)一步的查詢(xún)
e.g: select e.*,rownum from (select * from emp order by sal desc) e where rownum <=5;//查詢(xún)工資最高的前五名員工
select * from (select empno,ename,sal,rownum r from (select * from emp order by sal desc) where rownum <=10) where r > 5;//查詢(xún)工資最高的6-10名員工
連接查詢(xún) (實(shí)際開(kāi)發(fā)):從兩張或更多張表中查詢(xún)到數(shù)據(jù)
1. 內(nèi)連接
e.g: select empno, ename ,d.deptno, dname from dept d inner join emp e on (e.deptno = d.deptno);
2.(左)外連接
e.g: select empno, ename , d.deptno, dname from dept d left outer join emp e on (e.deptno = d.deptno);
3.(右)外連接
e.g: select empno, ename , d.deptno, dname from dept d right outer join emp e on (e.deptno = d.deptno);
4.自連接
e.g: select e1.ename, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
5. 多表連接:就是利用以上連接將多個(gè)表連接起來(lái)
建表
create table 表名(
列名1 數(shù)據(jù)類(lèi)型 約束,
列名2 數(shù)據(jù)類(lèi)型 約束,
列名3 數(shù)據(jù)類(lèi)型 約束,
....
列名n 數(shù)據(jù)類(lèi)型 約束
);
在建表時(shí)可設(shè)定列的默認(rèn)值，語(yǔ)法如下： 列 數(shù)據(jù)類(lèi)型 default 默認(rèn)值 約束
建表示例：
create table users_jone(
username varchar2(20) primary key,
password varchar2(20) not null check ( length(password) >=4 ),
birthday date,
married number(1) check (married in(0,1)),
age number(3) check (age >0 and age<150),
salary number(7,2) check (salary >0.0)
);
Oracle中數(shù)據(jù)類(lèi)型有如下幾種：
1.字符串類(lèi)型: varchar2(字符個(gè)數(shù)); clob 字符型大對(duì)象 (需補(bǔ)充對(duì)這兩種類(lèi)型的分析)
2.日期類(lèi)型: date 年月日，時(shí)分秒，毫秒; timestamp 可以到納秒
3.布爾類(lèi)型：可將數(shù)據(jù)類(lèi)型設(shè)定為number(1)或者是char(1),在Oracle里是0和1來(lái)表示false和truth的 ，之后可設(shè)檢查約束為：check(列名 in(0,1))
4. 數(shù)字
整數(shù) integer(整數(shù)位數(shù)) e.g:age integer(3)
小數(shù) number(最大有效數(shù)字長(zhǎng)度,小數(shù)點(diǎn)后位數(shù)) e.g:salary number(7,2)//99999.99
Oracle中的約束
作用：保證數(shù)據(jù)的有效性和完整性
種類(lèi)：not null 非空約束；unique 唯一約束；primary key 主鍵約束（唯一并且非空，在一張表里只能有一個(gè)主鍵）；check(條件) 檢查約束
添加(插入)數(shù)據(jù)
語(yǔ)法1：insert into 表名(列名1,列名2...) values(值1,值2 ...);
語(yǔ)法2：insert into 表名 values(值1,值2 ...);//在插入給出的所有列的數(shù)值時(shí)使用，注意值的順序必須與列名順序一致
語(yǔ)法3：插入部分列的值 語(yǔ)法：insert into 表名(列名1,列名2...) values(值1,值2 ...);//注意：此時(shí)不能省略列名
對(duì)事務(wù)的操作：
commit; //在對(duì)表進(jìn)行增刪改操作后，需執(zhí)行commit之后才能讓所作操作對(duì)其他用戶(hù)可見(jiàn)。
rollback;//若想取消對(duì)表的增刪改操作，可以執(zhí)行此語(yǔ)句。
update 更新
語(yǔ)法：update 表 set 列=值, 列=值,... where 條件;
e.g: update emp set sal=800,comm=500 where empno=7369;
delete 刪除
語(yǔ)法：delete from 表 where 條件；
e.g: delete from emp where empno = 1234;
delete from 表; -- 刪除表中所有記錄
truncate table 表名; -- 刪除表中所有記錄 注意：無(wú)法使用rollback 撤銷(xiāo),但運(yùn)行效率高
注意：如果兩張表存在著關(guān)聯(lián)關(guān)系，則先刪從表記錄，再刪主表記錄。創(chuàng)建這樣的有著關(guān)聯(lián)關(guān)系的表時(shí)，則要先建主表，再建從表。
e.g:create table users_jone; (主) create table orders_jone; (從)
drop table orders_jone; (從) drop table users_jone; (主)
drop table users_jone cascade constraint;//cascade constraint的作用是先刪除相關(guān)的外鍵約束再刪除表
根據(jù)舊表創(chuàng)建新表
做法1：先創(chuàng)建與舊表一樣的表結(jié)構(gòu)，然后執(zhí)行：insert into 新表名 select * from 舊表名;commit;
做法2: 語(yǔ)法：create table 新表名 as select * from 舊表; commit;
注意：此法創(chuàng)建的表結(jié)構(gòu)與舊表幾乎一致，只是約束只能復(fù)制not null約束。
e.g:create table emp_jone as select * from emp;
create table emp_jone as select * from emp where 1=2;//僅僅復(fù)制了表結(jié)構(gòu)，沒(méi)有復(fù)制表數(shù)據(jù)
約束：
1.行級(jí)約束
primary key, references，unique, check, not null
2.表級(jí)約束
primary key, references，unique, check
表級(jí)約束的寫(xiě)法，e.g:
create table orders_jone(
orderId number(6) ,
orderDate date not null,
state number(1) not null ,
totalPrice number(10,2) not null,
username varchar2(20) not null,
primary key (orderId),
check (state in(1,0)),
foreign key (username)
references users_jone(username)
);
create table score(
stuName varchar2(20),
courseName varchar2(20),
score number(3) not null,
primary key(stuName, courseName)//聯(lián)合主鍵
);
約束命名
命名規(guī)則： 表名_列名_約束類(lèi)型簡(jiǎn)寫(xiě)（PK 主鍵，NN 非空，CK 檢查，UK 唯一，FK 外鍵）
如果沒(méi)有給約束起名，oracle會(huì)自動(dòng)為約束命名
e.g:create table orders_jone (
orderId number(6)
constraint orders_jone_orderId_pk primary key,
orderDate date
constraint orders_jone_orderDate_nn not null,
state number(1) not null
check (state in(1,0)),
totalPrice number(10,2) not null,
username varchar2(20) not null
constraint orders_jone_username_fk
references users_jone(username)
);
外鍵約束:限制某一列不能是任意的，必須來(lái)源于另外一個(gè)主鍵列
語(yǔ)法：列 數(shù)據(jù)類(lèi)型 references 表名(引用列)
系統(tǒng)表(數(shù)據(jù)字典)：存儲(chǔ)的都是與定義（表的,約束的）有關(guān)信息
user_tables 存儲(chǔ)當(dāng)前用戶(hù)創(chuàng)建的表相關(guān)信息
TABLE_NAME -> 表名
e.g:select table_name from user_tables;
user_constraints 存儲(chǔ)了當(dāng)前用戶(hù)的所有約束信息
OWNER -> 擁有(創(chuàng)建)者
CONSTRAINT_NAME -> 約束名
CONSTRAINT_TYPE -> 約束類(lèi)型
TABLE_NAME -> 約束所在表
user_cons_columns 存儲(chǔ)了當(dāng)前用戶(hù)的所有約束信息
COLUMN_NAME -> 約束所在列
select owner,constraint_name,constraint_type from user_constraints where table_name='ORDERS_jone';
select column_name from user_cons_columns where CONSTRAINT_NAME ='SYS_C0032843';
序列(sequence ): oracle提供的產(chǎn)生唯一值的一種機(jī)制
創(chuàng)建語(yǔ)法：create sequence 序列名 選項(xiàng); e.g: create sequence orders_jone_seq;
select orders_jone_seq.nextval from dual;//取得序列的下個(gè)一值
選項(xiàng):start with 初始值 ; increment by 遞增值 ; cache 緩存值
e.g: create sequence orders_jone_seq start with 300001 increment by 2 cache 10;
user_sequences(保存用戶(hù)的序列信息):
SEQUENCE_NAME -> 序列名,INCREMENT_BY -> 遞增值,CACHE_SIZE -> 緩存值
e.g:select * from user_sequences where sequence_name ='ORDERS_jone_SEQ';
應(yīng)用：可以利用序列生成唯一的ID值。
e.g:insert into orders_jone values(orders_jone_seq.nextval,sysdate,0, 1000.00, 'liucy');
注意點(diǎn)：
1.例如：create sequence orders_jone_seq start with 30000;
select orders_jone_seq.currval,orders_jone_seq.nextval,orders_jone_seq.nextval from dual; //30000,30000,30000
結(jié)論：如果一條查詢(xún)語(yǔ)句中多次出現(xiàn)nextval，則實(shí)際取值時(shí)只從序列中取了一次值
2.序列第一次使用時(shí)不能使用currval
序列剛創(chuàng)建后，運(yùn)行select orders_jone_seq.currval from emp;
會(huì)報(bào)錯(cuò)誤： 序列 ORDERS_JONE_SEQ.CURRVAL 尚未在此會(huì)話(huà)中定義
sql語(yǔ)句的分類(lèi)
DQL (數(shù)據(jù)查詢(xún)語(yǔ)言data query) select
DML (數(shù)據(jù)操控) insert, update, delete
TCL (事務(wù)控制語(yǔ)言transaction control) commit, rollback
DDL (數(shù)據(jù)定義data defination) create, drop, alter， truncate
DCL(數(shù)據(jù)控制) grant, revoke
alter
添加列 例：alter table users_jone add address varchar2(20) default '北苑家園' not null;
刪除列 例：alter table users_jone drop column address;
重命名 例：alter table users_jone rename column address to addr;
修改列 例：alter table users_jone modify username varchar2(30);
刪除約束 例：alter table orders_jone drop constraint orders_jone_orderId_pk;
修改約束 例：alter table employees modify manager_id integer;
添加約束 例：
alter table orders_jone add constraint orders_jone_orderId_pk primary key(orderId);
事務(wù) (transaction)
事務(wù)代表一組原子（不可再分）操作，它當(dāng)中的一到多條增刪改語(yǔ)句必須作為一個(gè)不可分割的整體若，要么都成功(commit),若其中一條失敗，應(yīng)當(dāng)撤銷(xiāo)所有操作(rollback)。
事務(wù)的邊界（開(kāi)始和結(jié)束）
執(zhí)行update, insert , delete 時(shí)事務(wù)開(kāi)始
執(zhí)行commit/rollback (事務(wù)結(jié)束)
rollback只會(huì)撤銷(xiāo)本次事務(wù)所做的所有操作，不會(huì)影響已經(jīng)結(jié)束的事務(wù)
select 不會(huì)受事務(wù)影響
鎖 synchronize:作用：用來(lái)保護(hù)并發(fā)操作下的數(shù)據(jù)完整性。（需補(bǔ)充）
事務(wù)的4特性:ACID
A - 原子性
C - 一致性
事務(wù)開(kāi)始前和結(jié)束后的數(shù)據(jù)狀態(tài)應(yīng)當(dāng)一致
I - isolation 隔離性
D - 持久性
指事務(wù)結(jié)束后，數(shù)據(jù)的狀態(tài)應(yīng)當(dāng)永久保存下來(lái)
oracle 數(shù)據(jù)庫(kù)對(duì)象
表 (table)
序列 (sequence)
約束 (constraint)
視圖 (view)
索引 (index)
函數(shù) (function)
(存儲(chǔ))過(guò)程(procedure)
對(duì)視圖，索引，過(guò)程等數(shù)據(jù)庫(kù)對(duì)象方面的使用有待補(bǔ)充。
Oracle數(shù)據(jù)庫(kù)中用戶(hù)權(quán)限角色等方面的知識(shí)有待補(bǔ)充。