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