oracle學習筆記

標籤：des   http   java   使用   os   io   檔案   資料   

第一集：

#目前主流資料庫 微軟       sqlsever and access mysql ad2 sybase informix oracle #小型資料庫：access：負載量小 100人，安全性要求不高 #中型資料庫：mysql，sqlsever  ：負載在5000到15000 #大型資料庫：sybase，oracle，db2：負載可以可以處理海量的  sybase《oracle《db2 #用什麼資料庫？ 1.項目規模：a、負載量多大（使用者量） 2.成本 3.安全性 oracle認證： 第二集 oracle安裝 資料庫標識 myOracle

#預設產生兩個使用者 a.sys------->change_on_install (超級使用者，具有最高許可權，sysdba角色，create database的許可權) b.system---->manager(管理操作員，許可權很大。具有sysoper角色，createdatebase許可權)

#一般來說對資料庫維護，使用system使用者就可以了

第三集，oracle的啟動、配置  #Oracle 的資料庫是  多戶 ---->到一個執行個體資料庫  ①不同使用者對錶有不同的許可權；  #sqlserver 一使用者----->多個資料庫 #啟動 ①OracleServer+資料庫名； ②啟動oracleOraHome90TNSListener

#卸載 ① #oracle管路工具的介紹

內建的工具軟體，主要於執行sql語句，p1\sql快。 步驟： 1）開始-->oracle orachome90-->appliacation  development --.sql*plus 2）在運行欄輸入sqlpusw 即可 3)程式-->Oracle orahome 90-->appplication  development -->sql*plus  worksheet

oracle企業

主機字串：即資料庫名稱；

#https://localhost:5500/em 可以查看資料庫運行狀態，進行建立資料表空間和使用者配置

#show user;

#plSqlDeveloper

#scott/tiger #串連命令 1)conn 用法：conn 使用者名稱/密碼@網路伺服器名 當特權使用者身份串連時，必須帶上 as  sysdba 或是 as  sysoper conn system/manager #中斷連線命令 ：disc #修改密碼命令：passw # 退出命令：exit    #檔案操作命令  1）start和@   //運行指令碼 start d：\aa.sql； 2）edit ：編輯指定檔案 3）spool：將sql*plus螢幕上的內容輸出到指定檔案中去。 spool d：\b.sql 最後輸入 spool  off

#設定顯示行的寬度：set linesize 120;  分頁設定：set pagesize 5;（設定每頁的行數，預設為14）

 第三節oracle 使用者管理

 #建立一個新的使用者使用create user語句，一般是具有dba的許可權才能使用  create user xiaoming identified by m123；

 #給使用者修改密碼  需要有dba的許可權或者是擁有alter user系統許可權；  password xiaoming；

 #刪除使用者  一般以dba的身份刪除某使用者，不能自殺；如果其他使用者去刪除使用者需要drop user的許可權。

*在刪除使用者時，如果刪除的使用者已經建立了表，那麼就需要帶一個參數。（cascade）。 列入：drop user 使用者名稱 【cascade】

#建立的新使用者是沒有任何許可權的，甚至連登陸資料庫的許可權都沒有，需要為其指定相應的許可權，給使用者賦予許可權使用grant命令，回收許可權使用revoke。 *grant connect to xiaoming #許可權： 系統許可權（使用者對資料庫訪問的許可權）和 對象許可權（使用者對其他使用者的資料對象操作的許可權select、insert、update） #資料對象：使用者建立的表、視圖、索引、觸發器。。。。。 #角色：自訂角色和預定義角色 dba角色； connect角色； resource角色（可以在任何錶空間建表）；

#建立表 *grant resource to xiaoming（授予建立表的全許可權） create table test（userId vachar2（30），userName vachar2（30））；

#希望使用者去查詢scott的emp表； *grant select on emp to xiaoming #grant select on emp xiaoming//授權給小明

#select *form scott.emp；

#收回許可權：revoke select on emp from xiaoming

#許可權的傳遞（許可權的維護）： 小明使用者可以查詢scott的emp表，還希望小明可以把這個許可權繼續傳給其他使用者。

*如果是對象許可權，就加入with grant option *grant select on emp to xiaoming with grant option； 如果是系統許可權，就加 grant connect to xiaoming with admin option；

#使用profile 系統管理使用者口令：profile是口令限制，資源限制的命令集合，當建立資料庫時，oracle就會自動建立名稱為default的profile 。當建立使用者沒有指定profile選項，oracle就會將default分配給使用者。 1）賬戶鎖定：指使用者登入時最多可以輸入密碼的次數，也可以指使用者鎖定的時間，一般用dba的身份去執行該命令。

//①列子：建立profile檔案 create profile lock_account limit//lock——account代表名稱 failed_logion_attempts 3 password_lock_time 2; //②指定使用者 alter user xiaoming profile lock_account;

2）給賬戶解鎖 *alter user xiaming account unlock；

3）終止 為了讓使用者定期修改密碼，可以使用終止口令來執行；用dba身份來操作 *create profile myprofile limit password_life_time 10 password_garce_time 2;//要求使用者每隔10天修改自己的登入面膜。寬限2天。

4）口令曆史  希望使用者在修改密碼時，不能使用以前用過的密碼，可以使用口令曆史。 *create profile password_history limit password_life_time 10 password_grace_time2password_reuse_time 10;

5)刪除profile *drop profile password_history[cascade];

https://localhost:5500/em

第06講 oracle 表的管理

#表名和列的命名規則 1）必須以字母開頭 20不能超過30字元 3）不能使用oracle保留字

#oracle的資料類型 1）char 定長 最長2000字元 2)varchar2 變長 最大4000字元 3）clob  字元型大對象 最大4g

4）數字類型 number 範圍-10的-38~10的38次方，可以表示整數，也可以表示小數

5）日期類型  date  包含年月日和分秒 timestamp是對date資料類型的擴充

6）圖片（包括聲音、視頻） blob  位元據

#怎樣建立表 ---學生表 create table student（ StNumber number（4）， StName   varchar2（20）， sex char（2）， birthday date， sal number（7,2） ）； #刪除表 drop table students；

---班級表 CREATE table classes( classId number(2), cname varchar2(40));

#添加欄位 alter table student add （classId number（2））；

#查看錶 desc student

#修改欄位的長度 alter table student modify （xm varchar2（30））;

#刪除一個欄位//盡量不使用 alter table student drop column sal;

#修改表名 rename student to stu;

#添加資料 所有欄位都添加 insert into student values （‘A001’，‘張三’，‘男’，‘11-12月-1997’，’222.2‘，21） 日期的格式：oracle預設的日期格式‘DD-MON-YY’改日期的預設格式： alter session set nls_date_format=‘yyyy-mm-dd’； #插入部分欄位 insert into student （xh，xm，sex）value （‘’，‘’，）

#插入空值 insert into student （xh，xm，sex）value （‘’，‘’，null）

#查詢沒有生日的人 select *from student where birthday is null；//is not null;

#修改一個欄位 update student set sex=‘女’ where=‘A001’;

#修改多個欄位 update student set sex=‘女’， birthday=‘1980-04-01’ where=‘A001’; 修改欄位加逗號就可以。

#修改含有null用is修改。

#刪除資料 delete from student；//刪除了表裡面的內容，但是表的結構還在；可以通過日誌恢複；

#在刪除之前添加復原點（設定儲存點） savepoint a； #復原命令 rollback to a；

#刪除表的結構和資料 drop table student；

#刪除一條記錄 delete from student where xh=‘A001’；

#刪除表中的所有記錄，表結構還在，不寫日誌，無法找回刪除記錄，熟讀快 truncate table student；

#第八講 oracle表查詢

#查看錶的結構 desc emp

#查詢指定列 select ename ，sal,job,deptno from emp;

#set timing on；開啟操作時間；

#insert into users (userid,username,userpassword) select *from users;自動成長資料；

#如何取消重複行 select distinct deptno，job from emp；

#如何smith 的薪水，工作，所在部門 select deptno,job,sal from emp where ename=‘SMITH‘;

#使用算術運算式

select sal*13，ename from emp；

#給列取別名 select sal*13+nvl（comm,0)*13 ”年工資“，ename from emp；

#如何處理null值：使用nvl函數來處理

#顯示工資高於3000的員工 *select ename，sal from emp where sal>=3000;

#顯示在1982後入職的職員 *select ename,hiredate from emp where hiredate>‘1-1月-1982‘

#顯示工資在2000~2500的員工 *select ename，sal from emp where sal>=2000&sal在<=2500;

#如何使用like操作符 %表示任意0到多個字元； _表示任意單個字元； #如何顯示首字元為S的員工姓名和工資 *select ename，sal from emp where ename like ‘S%’；

#如何顯示第三個字元為S的員工姓名和工資 *select ename，sal from emp where ename like ‘__o%’；

#在where條件中使用in *select *from emp where empno in（123,213,324）；

#如何顯示沒有上級的員工 *select *from emp where mgr is null；

#第09講

#使用order by字句

#如何按照工資從低到高顯示員工資訊 *select *from emp order by sal #從高到底 *select *from emp  order by sal desc；

#按照部門號升序而僱員的工資降序； *select *from emp order by deptno ，sal desc；

#使用列的別名排序 *select enamel，sal*12 ”年薪“ from emp order by “年薪”；

#oracle表複雜查詢 #資料分組----max，min，avg，count

#如何顯示所有員工最高最低工資 *select max（sal），min（sal） from emp；

#如何顯示所有員工最高最低工資 *select ename,sal from emp where sal=(select min(sal) from emp);

#顯示工資高於平均工資的員工資訊 *select *from emp where sal>(select avg(sal) from emp);

#group by 和group by group by用於對查詢的結果分組統計 group by用於限制分組顯示結果

#顯示每個部門的平均工資和最高工資 *select avg(sal),max(sal),deptno from emp gruop by deptno;

#顯示每個部門的每種崗位的平均工資和最低工資 *select avg(sal),max(sal),deptno,job from emp group by deptno,job;

#顯示平均工資低於2000的部門 *select avg(sal),max(sal),deptno from emp gruop by deptno having avg(sal)<2000 #注意 1）分組只能出現在選擇列裡面，或者出現在having 。orderby裡面； 2）先出現order by再有having；

#顯示平均工資高於2000的部門，再按avg排序 *select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);

#多表查詢：兩個兩個以上的表或是視圖的查詢。

#顯示僱員工資所在部門的名字 *select a1.ename,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;

#規定：多表查詢的條件是 至少不能少於 表的個數-1；

#第10講多表查詢 #顯示部門號為10的部門名、員工名和工資 *select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

#顯示各員工的姓名，工資，及其工資層級 #select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.LOSAL and a2.HISAL;

#自串連：在同一張表的串連查詢；

#顯示某員工的上級領導的姓名//把一張表看作兩張 *select woker.ename,?boss.ename from emp worker,emp boss where wokker.mgr=boss.empno and worker.ename=KING;

#子查詢(巢狀查詢)：嵌入在其他sql中的select語句；

#單行子查詢：只返回一行資料的查詢 顯示SMITN同一部門的員工

*查詢SIMITH的部門號 select deptno from emp where ename=‘SMITH‘ *顯示 select *from emp where deptno=(select deptno from emp where ename=‘SMITH‘); #執行語句從左至右；

#多行子查詢 #查詢和10號部門的工作相同的僱員的名字、崗位、工資、部門號

select * from emp where job in (select distinct job from emp where deptn=10 );

注意：where job 後面寫=還是in 取決於是單行還是多行，單用=；

#在多行子查詢中使用all 操作符

#如何顯示工資比部門30的所有員工的工資高的員工的姓名。工資和部門 *select ename,sal,dept from emp where sal> all(select sal from emp wherr deptno=30); *方法2 select *from emp where sal>(select max(sal) from emp where deptno=30);

#在多行子查詢中使用any 操作符

顯示工資比部門30的任意一個員工的工資的姓名、 *select ename,sal,dept from emp where sal> any(select sal from emp wherr deptno=30); *方法2 select *from emp where sal>(select min(sal) from emp where deptno=30);

#多列子查詢

#如何查詢與simth 部門和崗位相同的員工 //查詢他的部門 select deptno,job from emp where ename=‘SMITH‘; //顯示 select *from emp where (deptno,job)=(select deptno,job from emp where ename=‘SMITH‘);

#在from子句中使用子查詢 #顯示高於自己部門平均工資的員工的資訊 //1、查詢部門的平均工資和部門號 select deptno,avg(sal) mysal from emp group by deptno; //把上面的查詢看作是一張子表 select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;

#分頁查詢 *分頁一共有三種方式 1）rownum分頁 select *from emp 2）顯示rownum select a1.*,rownum rn from (select *from emp) a1 3）

取6~10行 select a1.*,rownum rn from (select *from emp) a1;//rn行號 步驟 1.select a1.*,rownum rn from (select *from emp) a1 where rownum<10; 2.select *from（select a1.*,rownum rn from (select *from emp) a1 where rownum<=10） where rn>=6;

注意： a。若指定查詢列，只需修改最裡層的查詢 select *from（select a1.*,rownum rn from (select ename，sal from emp) a1 where rownum<=10） where rn>=6; b。排序 只需修改最裡層的查詢 select *from（select a1.*,rownum rn from (select ename，sal from emp order by sal ) a1 where rownum<=10） where rn>=6;

#用結果建立新表 這個命令是一個快捷的建標的方法 create table my table (id,name,sal,deptno) as select empno,ename,sal,job,deptno from emp; *比如create table Myemp1 (id,name,sal) as select empno,ename,sal from emp;

#合并查詢：操作符號 union ，union all，intersect，minus 1）union 該操作用於取得兩結果集的並集。會自動去掉結果集中重複行。 *select ename,sal,job from emp where sal >2500 union select ename,sak ,job from emp where job=‘manager‘; 2)union all:不會取消重複行 3）intersect 操作用於取得兩結果集的交集 4）minus 操作用於取得兩結果集的差集

#建立新的資料庫 1）工具嚮導：adca 2）手工建立

-----------第12講java操作oracle--------

#java串連oracle？？？？？ #

#使用特定格式插入日期 *使用to_date函數：使用者指定添加日期的格式 *insert into emp values(9998,‘小紅‘,‘MANAGER‘,‘7782‘,to_date(1998-12-12,‘yyyy-mm-dd‘),78.9,55.33,10);

#當用values子句時一次能插入多值 *create table kkk(myid number(4),myname varchar2(50),mydept number(5)); insert into kkk(myid,muname,mydept)select empno,ename,deptno from emp where deptno=10;

#用子查詢更新資料 *希望員工scott的崗位、工資、補助與smith一樣； *update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=‘SMITh‘) where ename=‘SCOTT‘;

--------oracle交易處理------

#事物用於保證資料的一致性，它由一組相關的dml語句組成，該組的dml語句要麼全部成功，要麼全部失敗；

#事物和鎖：當執行一事務操作時（dml語句），oracle會在被作用的表上加鎖，防止其他使用者該表的結構。 #鎖：檔案鎖。。。

#提交事務 *當使用commit語句可以提交事務，當執行commit語句後，會確認事務的變化。結束事務。刪除儲存點、釋放鎖。當使用commit語句結束事務之後，其他會話將卡哇伊查看到事務變化後的新資料

#回退事務：儲存點是事務中的一點，用於取消部分事務，當結束事務時，會自動刪除該事物所定義的所有儲存點。當執行rollback時，通過儲存點可以回退到指定的點。

#savapoint a1; #delete from emp where empno=9996; #savapoint a2; #delete from emp where empno=9999; #rollback to a2;

#第14講 oracle的函數

#唯讀事務：只允許執行查詢的操作，而不允許執行任何其他dml操作事務，使用唯讀事務可以確保使用者只能取得某時間點的資料。其他會話可以提交新事務，但是唯讀事務將不會取得最新資料變化 #設定唯讀事務：set transaction read only;

#sql函數的使用 1）字元函數 lower(char)：將字串轉換為小寫格式； upper(char)：將字串轉換為大寫格式； length(char):返回字串的長度； substr(char,m,n):取字串的子串； replace(char1,search_string,replace_string) instr(char1,char2,[,n[,m]])取子串在字串的位置

*將員工的名字按小寫方式顯示 select lower(ename) from emp;

*將員工的名字按大寫的方式顯示 select upper(ename) from emp;

*顯示員工名字正好為5個的字元的員工； select *from emp length(ename)=5;

*顯示所有員工姓名的前三個字元； select substr(ename,1,3) from emp;

*以首字母大寫的方式顯示所有員工的姓名； select upper(substr(ename,1,1)) from epm;//首大寫 select lower(substr(ename,1,length(ename)-1)) from epm;//小寫 select upper(substr(ename,1,1)) || lower(substr(ename,1,length(ename)-1)) from emp;合并

#替換 select replace(ename,‘A‘,‘我是老虎‘)；

--------數學函數-------- #包括：cos cosh,exp,il,log,sin,round,trunc,mod,floor,ceil #round(n,[m]) 該函數用於執行 四捨五入； #trunc(n,[m])該函數用於截取數字；如果省掉m，就截取小數部分，如果m是正數就截取到小數點的m位後，如果m是負數就截取到小數點的前m位， #mod模數； #floor(返回小於或等於n的最大整數)；向上取整 #ceil(返回大於或等於n的最小整數)；

*顯示在一個月為30天的情況所有員工的日薪，忽略餘數；

#SQL 函數的使用--日期函數 #日期函數用於處理date類型的資料。預設情況下日期的格式是dd-mon-yy. 1)sysdate:該函數返回系統時間； *select sysdate from dual; 2）add_months(d,n):加月份 *尋找八個月多的員工 select *from emp where sysdate>add_months(hiredate,800) 3)last_day(d):返回指定日期所在月份額度最後一天 顯示員工加入公司的天數 select sysdate-hiredate "入職天數",ename from emp; 4)找出各月倒數第3天受雇的所有員工 select hiredate, ename from emp where  last_day(hiredate)-2=hiredate;

#轉換函式