|
-- 數(shù)據(jù)庫存儲數(shù)據(jù) -- 市面上主流的數(shù)據(jù)庫有哪些 -- 甲骨文 oracle mysql -- IBM db2 金融 -- 微軟 sqlserver --這些是關(guān)系型數(shù)據(jù)庫。 --NOSQL 不僅僅是sql,典型 mongodb. ----------------- ---------------------------------------- --數(shù)據(jù)庫的語言有哪些分類 -- ************************************** /*** 1.數(shù)據(jù)定義語言(DDL),create,alter,drop 2.數(shù)據(jù)操縱語言(DML),select,update,insert,delete 3.數(shù)據(jù)事務(wù)語言, commit,savepoint,rollback 事務(wù)特性:原子性，一致性，持久性，隔離型， ...........后面補充............. 在關(guān)系型數(shù)據(jù)庫中數(shù)據(jù)是以table (二維數(shù)據(jù)結(jié)構(gòu)，行和列的形式) 來組織數(shù)據(jù) table 就是表來組織數(shù)據(jù)，這個table的設(shè)計的規(guī)范是什么? --三范式 ***************** --第一范式:數(shù)據(jù)庫表中的字段應(yīng)該最小單位， 是不能夠分割(本地化) --第二范式:數(shù)據(jù)庫表中的一個字段 不能由另外一個字段通過計算得出。 age birthday 工資 養(yǎng)老保險費用 ***/ ------------------------------------------------------- -- 創(chuàng)建表 oracle 列名和列的類型來定義的 --1.數(shù)據(jù)類型： --number 數(shù)值類型 number(長度),number(10) --number(長度，小數(shù)點位數(shù)) number(7,2) --2.char類型 char(長度)，定長 char(6) 固定長度為6位 --3.varchar2類型 varchar2(20) --4.date類型 日期類型 --5.int類型，表示一個正整數(shù) -- 大文本類型 blob ,clob -- 路徑 -- 首先我們來創(chuàng)建一張表 命名規(guī)范 t_ create table t_students ( sn number(7) primary key ,--學號 -- 主鍵（唯一不能重復(fù)），主鍵約束 sname varchar2(20) , --學生姓名 spwd char(6), -- 學生密碼 sbirthday date,--出生年月 smoney number(7,2), --賬戶金額 job varchar2(20), --學生職位 saddress varchar2(20),--學生籍貫 sphone char(11) --學生手機號碼 ) --查詢表 select * from t_students -- 插入數(shù)據(jù) insert into t_students values(1, '趙蓉','12345', to_date('1992-12-03','yyyy-MM-dd'),500.65,'學生會委員', '鹽城','13913321089'); commit; insert into t_students values(2, '秦日霞','123456', to_date('1992-11-03','yyyy-MM-dd'),1500.65,'學生會委員', '鹽城','13913321085'); commit; -- 修改表 -- 表增加一個字段 alter table t_students add ssex char(4); --表字段類型修改 alter table t_students modify saddress varchar2(15) --刪除表的字段 alter table t_students drop column ssex ; -- 刪除整個表 drop table t_students; select length(s.spwd) from t_students s where s.sname='趙蓉' -- 創(chuàng)建表 create table t_employees ( eid number(6) primary key, -- 員工編號 主鍵 ename varchar2(20), --員工姓名 epwd char(5) , -- 員工密碼 ebirthday date, --出生年月 esalary number(7,2), --工資 ejob varchar2(20), --職位 esex char(4) --性別 ) -- oracle序列機制，自增長機制 sequence create sequence seq_employees start with 100 --開始編號 increment by 2 --步長 nocache --沒有緩存，立即生成 --查看表結(jié)構(gòu) desc t_employees select * from t_employees for update select * from t_employees where --數(shù)據(jù)操作語言（select ,insert,update,delete）DML --select 操作 ,不需要commit 70% --insert,update,delete 都需要commit 30% --查詢 select * from t_employees --插入 -- 插入數(shù)據(jù) insert into t_employees values(seq_employees.nextval, '胡瓜','1234',to_date('1992-12-30','yyyy-MM-dd'),10000.88, '職工','男'); insert into t_employees values(seq_employees.nextval, '李欣','11111',to_date('1992/08/30','yyyy/MM/dd'),12000.88,'組長','男'); commit; --更新數(shù)據(jù) update t_employees e set e.epwd='12345' ; commit; update t_employees e set e.epwd='00000' where e.ename='李欣'; commit; --刪除數(shù)據(jù) delete from t_employees delete from t_employees e where e.eid=100; commit; --約束 --1.主鍵約束，唯一不能重復(fù)，數(shù)據(jù)唯一性 primary key --2.非空約束 這個字段的取值不能為空 not null --3.默認約束 這個字段取值如果不給值，取默認值 default --4.檢查約束 這個字段取值是一定范圍內(nèi) check(字段取值范圍) --5.外鍵約束 一個字段取值從屬于另外一個的值 drop table t_stus create table t_stus ( sid number(3) primary key, sname varchar2(20) not null ) insert into t_stus values(seq_stus.nextval,null); commit; insert into t_stus values(seq_stus.nextval,'李欣',to_date('1976-12-30','yyyy-MM-dd')); commit; insert into t_stus values(seq_stus.nextval,'李欣1',default); commit; insert into t_stus values(seq_stus.nextval,'李欣1',default,'男'); commit; select * from t_stus --default 是默認，sysdate是oracle默認時間的關(guān)鍵字 alter table t_stus add birthday date default sysdate; alter table t_stus add sex char(4) check(sex in('男','女')); -- 創(chuàng)建一張表，樹狀菜單，自身關(guān)聯(lián)表 一對多 create table t_menu ( fid number(4) primary key, --父id fmenuname varchar2(20) not null, --父菜單名稱 cmenuuname varchar2(20) not null,-- 子菜單名稱 cid number(4) references t_menu(fid) --子id 外鍵約束 ) select * from t_menu -- oracle序列機制 自增長機制 create sequence seq_stus start with 1 increment by 1 nocache; --查詢序列的當前值 dual是個系統(tǒng)表 select seq_stus.currval from dual; drop sequence seq_stus insert into t_stus values(seq_stus.nextval,'李欣'); commit; select * from t_stus delete from t_stus --mysql自增長 auto_increment 從起始編號為1，每次增加1. --sqlserver自增長 identity(10,1) -- oracle內(nèi)置函數(shù) --to_date(參數(shù)1，參數(shù)2)，把一個字符串類型的日期轉(zhuǎn)換成date類型， --參數(shù)2，按照什么日期格式 --length() 獲取結(jié)果的長度 --to_char()轉(zhuǎn)換成字符串類型 select to_char(sysdate,'yyyy') -to_char(e.ebirthday,'yyyy') age from t_employees e -- 拼接字符串concat() -- 輸出一段描述"胡瓜的職位是什么" select concat(concat(e.ename,'職位是:'),e.ejob) 描述 from t_employees e where e.ename='胡瓜'; -- ||連接 select e.ename||',職位是'||e.ejob from t_employees e --nvl()函數(shù),取值為null,給默認值 select * from t_employees e for update --增加獎金字段 alter table t_employees add ecomm number(5,2) -- 算出每個員工的收入 select e.ename,e.esalary e.ecomm from t_employees e select e.ename,e.esalary nvl(e.ecomm,0) from t_employees e --substr()截取 alter table t_employees add ephone char(11) --號段 select substr(e.ephone,0,3) from t_employees e where e.ename='胡瓜' --to_number()轉(zhuǎn)換成數(shù)值的函數(shù) --聚合函數(shù) count(),max(),min(),sum(),avg() select count(*) from t_employees select max(e.esalary) from t_employees e select min(e.esalary) from t_employees e select avg(e.esalary) from t_employees e --事務(wù)？？？？？ --手動事務(wù)，自動事務(wù)，可以設(shè)置 -- 3查詢技術(shù) 70% -- 3.1 條件查詢 where關(guān)鍵字 -- 查詢姓名是李欣的所有信息 select * from t_employees e where e.ename='李欣' --查詢姓名是李欣，密碼是12345的這個用戶是不是合法用戶 select count(*) from t_employees e where e.ename='李欣' and e.epwd='1111' select * from t_employees e where e.esex='男' or e.ename='李欣' --3.2 比較查詢 >,<,>=,<=,!= <> select * from t_employees e where e.esalary!=5000 select * from t_employees e where e.esalary<>5000 --3.3 模糊查詢 --查詢姓李的人員的信息 select * from t_employees e where e.ename like '李%' --查詢姓李的人員的，但是是兩個字的人員信息 select * from t_employees e where e.ename like '李__' --3.4 排序 --asc desc select * from t_employees e order by e.esalary asc select * from t_employees e order by e.esalary desc --3.5 分組 --group by 統(tǒng)計就是和聚合函數(shù)在一起使用。 --統(tǒng)計這個單位的男女 性別數(shù)量 分組條件是明確 select e.esex,count(e.esex) from t_employees e group by e.esex -- select * from t_employees for update -- 統(tǒng)計每個年齡員工的數(shù)量 select count(*), 分組條件是計算的 select count(*), to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age from t_employees e group by to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') --統(tǒng)計每個職位的數(shù)量 select e.ejob,count(*) from t_employees e group by e.ejob --統(tǒng)計學歷，統(tǒng)計籍貫 --統(tǒng)計每個年齡數(shù)量大于1 分組后帶有限制條件 select count(*), to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age from t_employees e group by to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') having count(*)>1 --統(tǒng)計每個年齡數(shù)量,數(shù)量按升序(先分組，后排序)分組帶排序 select count(*), to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') age from t_employees e group by to_char(sysdate,'yyyy')-to_char(e.ebirthday,'yyyy') order by count(*) -- 子查詢 一個查詢的結(jié)果作為另外一個查詢的條件 -- 找出工資最高的人的姓名 select * from t_employees select * from t_employees e where e.esalary= (select max(e.esalary) from t_employees e) --3.6 in ,not in ,between and ...any all關(guān)鍵字 -- =只能等于一個值 select * from t_employees e where e.esalary=10000.88 -- in可以是多個值 select * from t_employees e where e.esalary in(10000.88,5000.00) select * from t_employees e where e.esalary not in(10000.88,5000.00) select * from t_employees e where e.esalary between 4000 and 20000; select * from t_employees e where e.esalary >=4000 and e.esalary <=20000 --any >是比最小的大的信息，<是比最大的小信息 select e.esalary,e.ename from t_employees e where e.esalary<any (select e.esalary from t_employees e where e.ejob='職工') --all >是比最大的大的信息，<是比最小的小信息 select e.esalary,e.ename from t_employees e where e.esalary < all (select e.esalary from t_employees e where e.ejob='職工') select * from t_employees for update -- 查詢1991年到1999年出生的員工的信息 select * from t_employees select * from t_employees e where to_number(to_char(e.ebirthday,'yyyy')) between 1991 and 1999 --**** insert into t_stus values(seq_stus.nextval, '李二',default,'男') select * from t_employees for update --- oracle高級查詢技術(shù) -- 1.子查詢 -- 邏輯，就是從一個條件出發(fā)去找關(guān)聯(lián)的條件。 -- 一個查詢的結(jié)果作為另一個查詢的條件 -- 找出員工工資最高人的信息 select * from t_employees e where e.esalary in (select max(e.esalary) from t_employees e) --找出和李欣職位不同的員工的信息 select * from t_employees e where e.ejob!= (select e.ejob from t_employees e where e.ename='李欣') --找出比員工平均工資高的員工的信息 select * from t_employees e where e.esalary> (select avg(e.esalary) from t_employees e) --找出收入最高的員工的信息 select * from t_employees e where e.esalary nvl(e.ecomm,0)= (select max(e.esalary nvl(e.ecomm,0)) from t_employees e ) --oracle分頁查詢 局限性 ，一條sql語句對應(yīng)一張表，造成SQL冗余 --oracle特殊的列，偽列 rownum ,它始終在第一行，不能移動 select rownum, e.* from t_employees e --查詢前三條的數(shù)據(jù) select rownum, e.* from t_employees e where rownum<=3 select rownum, e.* from t_employees e where rownum<=2 -- 查詢第2條到第4條之間的數(shù)據(jù)，查詢出是空的 select rownum, e.* from t_employees e where rownum>=2 and rownum<=4; select rownum, e.* from t_employees e where rownum between 2 and 4 --rownum始終在第一行，導致你查詢出來的是空的。 -- 查詢第2條到第4條之間的數(shù)據(jù) --通過臨時結(jié)果集過渡查詢 select * from (select rownum rm,e.* from t_employees e where rownum<=4) tmp where tmp.rm>=2 select rownum, e.* from t_employees e --分頁查詢 -- 最大的編號:<=第幾頁*每頁條數(shù) --起始編號:>（第幾頁-1）*每頁條數(shù) select * from t_employees e --每頁的條數(shù)是2條 --第一頁數(shù)據(jù) 第一頁，2條 out:結(jié)果集 select * from (select rownum rm,e.* from t_employees e where rownum<=1*2) tmp where tmp.rm>(1-1)*2 --第二頁數(shù)據(jù) select * from (select rownum rm,e.* from t_employees e where rownum<=2*2) tmp where tmp.rm>(2-1)*2 --第三頁數(shù)據(jù) select * from (select rownum rm,e.* from t_employees e where rownum<=3*2) tmp where tmp.rm>(3-1)*2 -- 在實際的項目中的一個 --局限性 ，一條sql語句對應(yīng)一張表，造成SQL冗余 ， --比如;100張表需要100個sql嗎? --2.集合查詢 --集合查詢 -- 多個結(jié)果集的查詢 -- 工資大于3000的和職位是組長的兩個查詢結(jié)果的合并，過濾重復(fù) -- 合并不過濾重復(fù)，查詢的兩個結(jié)果合并 select e.ename,e.esalary from t_employees e where e.ejob='組長' union all select e.ename,e.esalary from t_employees e where e.esalary>3000 --合并過濾重復(fù) select e.ename,e.esalary from t_employees e where e.ejob='組長' union select e.ename,e.esalary from t_employees e where e.esalary>3000 --交集，兩個查詢結(jié)果集都有的 select e.ename,e.esalary from t_employees e where e.ejob='組長' intersect select e.ename,e.esalary from t_employees e where e.esalary>3000 --差集 select e.ename,e.esalary from t_employees e where e.ejob='組長' minus select e.ename,e.esalary from t_employees e where e.esalary>3000 --3.decode()函數(shù)查詢 分支查詢 select * from t_employees --科長工資加1.2倍，組長工資加1.1倍,其它職位保持原有， --請輸出加薪后的所有的員工的 --信息 select e.ename,e.ejob,e.esalary, decode(e.ejob,'科長',e.esalary*1.2, '組長',e.esalary*1.1, esalary) 加薪后的工資 from t_employees e -- 等同于case..when select e.ename,e.ejob,e.esalary, case e.ejob when '科長' then e.esalary*1.2 when '組長' then e.esalary*1.1 else e.esalary end 加薪后的工資 from t_employees e --4.多表查詢(2表查詢，第三范式) --之前講的都是單表查詢，進入到多表(>1)查詢 --第三范式 -- 表中的列只能參照一個主鍵字段 -- 員工表 員工id，員工姓名，部門名稱，部門id, 拆分形成兩個實體表 --實體表之間的關(guān)系，主要有三種: --1.一對一關(guān)系 員工表和角色表 --2.一對多關(guān)系 部門表和員工表 商品分類表和商品表 --3.多對多關(guān)系 學生和課程表（中間關(guān)系表，第三方表，成績表） --實體表之間的關(guān)系圖，ER圖 --這個關(guān)系的定義是根據(jù)現(xiàn)實的業(yè)務(wù)來決定的。 select * from t_employees --建立一個部門表 部門和員工表之間的關(guān)系是1對多 create table t_depts ( did int primary key, dname varchar2(20) not null, daddress varchar2(50), dphone char(11) not null ) create sequence seq_depts start with 10 increment by 2 nocache; insert into t_depts values(seq_depts.nextval,'質(zhì)量部','南京江北新區(qū)','13913321089'); insert into t_depts values(seq_depts.nextval,'技術(shù)部','南京江寧區(qū)','13913321086'); insert into t_depts values(seq_depts.nextval,'人力資源部','南京鼓樓區(qū)','13913321085'); commit; select * from t_depts for update select * from t_employees for update -- 員工和部門之間是有關(guān)系的 alter table t_employees add deptid int; --外鍵約束？？？？？？？？？？？？ alter table t_employees add constraints fk_emp_depts -- 表的設(shè)計，軟件核心來自于數(shù)據(jù) -- 查詢這兩張表的數(shù)據(jù) 笛卡爾積 表1*表2 select * from t_employees,t_depts --等值連接 select * from t_employees e,t_depts d where e.deptid=d.did --內(nèi)連接查詢inner join select * from t_employees e inner join t_depts d on e.deptid=d.did -- 查詢員工李欣的部門名稱 select d.dname,e.ename from t_employees e inner join t_depts d on e.deptid=d.did where e.ename='李欣' --子查詢 的效率比內(nèi)連接查詢要低 select d.dname from t_depts d where d.did= (select e.deptid from t_employees e where e.ename='李欣') --左連接 select d.dname,e.ename from t_employees e left join t_depts d on e.deptid=d.did --右連接 select d.dname,e.ename from t_employees e right join t_depts d on e.deptid=d.did --全連接 select d.dname,e.ename from t_employees e full join t_depts d on e.deptid=d.did -- select * from t_depts -- 統(tǒng)計技術(shù)部人員的工資的總和 select sum(e.esalary) 工資總和 from t_depts d inner join t_employees e on d.did =e.deptid where d.dname='技術(shù)部' -- 統(tǒng)計每個部門的人員的數(shù)量 select count(e.ename) 數(shù)量, d.dname from t_depts d left join t_employees e on d.did=e.deptid group by d.dname =e.deptid --統(tǒng)計每個部門的人員的數(shù)量大于1的信息 group by 限制條件 having select count(e.ename) 數(shù)量, d.dname from t_depts d left join t_employees e on d.did=e.deptid group by d.dname having count(e.ename)>1 --統(tǒng)計每個部門的人員的數(shù)量按降序排列 select count(e.ename) 數(shù)量, d.dname from t_depts d left join t_employees e on d.did=e.deptid group by d.dname order by count(e.ename) desc ------------------------------------------------------------------ ---pl-sql塊 --塊:一組SQL語句在一起運行，解決復(fù)雜的業(yè)務(wù)邏輯。 -- 是不能夠被編程語言所調(diào)用 java,python,c# --塊的基本結(jié)構(gòu) /** declare ---定義的變量 begin -- 一組sql語句 end; **/ /** 變量v_ 變量賦值:= **/ -- 計算兩個數(shù)值類型的變量的和并輸出 declare v_num1 number:=100; v_num2 number:=10; v_sum number; begin v_sum:=v_num1 v_num2; dbms_output.put_line('計算這兩個數(shù)的和為'||v_sum); end; -- 異常處理 計算兩個數(shù)值類型的變量的商并輸出 declare v_num1 number:=100; v_num2 number:=0; v_sum number; begin v_sum:=v_num1/v_num2; dbms_output.put_line('計算這兩個數(shù)的和為'||v_sum); --異常處理塊 exception --捕獲異常 when others then dbms_output.put_line('v_num2這個數(shù)作為除數(shù)不能為零'); end; -- 流程控制語句 -- 一個數(shù)判斷是奇數(shù)還是偶數(shù),并輸出信息 declare v_num number:=22; begin -- oracle不支持%取模，取模函數(shù)mod() if mod(v_num,2)=0 then dbms_output.put_line('v_num這個數(shù)是偶數(shù)'); else dbms_output.put_line('v_num這個數(shù)是奇數(shù)'); end if; end; --優(yōu)化 declare v_num number:=22; v_str varchar2(100); begin -- oracle不支持%取模，取模函數(shù)mod() if mod(v_num,2)=0 then v_str:='v_num這個數(shù)是偶數(shù)'; else v_str:='v_num這個數(shù)是奇數(shù)'; end if; dbms_output.put_line(v_str); end; -- 查詢李欣這個員工的性別，如果是男的，獎金加1000， --如果是女的，獎金加500， -- 輸出他現(xiàn)在的收入輸出 /** 1.賦值:= 直接給變量賦值 2.從sql語句查詢的結(jié)果進行賦值。2.1查詢出來的是一個值，into 2.2 如果是多個值，不能用into，要用游標遍歷 **/ declare v_esex t_employees.esex%type; --這個變量的類型參照表中的字段類型 v_usaraly t_employees.esalary%type; v_sum number(10,2); begin --1.查詢李欣這個員工的性別 select e.esex into v_esex from t_employees e where e.ename='李欣'; --2.判斷是男還是女 if v_esex='男' then v_usaraly:=1000; else v_usaraly:=500; end if; --執(zhí)行更新 update t_employees e set e.esalary=e.esalary v_usaraly where e.ename='李欣'; commit; -- 查詢出現(xiàn)在的收入 select e.esalary nvl(e.ecomm,0) into v_sum from t_employees e where e.ename='李欣'; dbms_output.put_line('李欣這個員工的性別是:'||v_esex||',他現(xiàn)在的收入為:'||v_sum); --select e.ename from t_depts d inner join t_employees e -- on d.did=e.deptid where d.dname='質(zhì)量部' end; -- 查詢李欣的部門名稱,如果是質(zhì)量部，加1000，如果是技術(shù)部加2000， --如果是人力資源部加500，其它部門加100 declare v_dname t_depts.dname%type; v_ecomm t_employees.ecomm%type; begin -- 李欣的部門名稱 select d.dname into v_dname from t_employees e inner join t_depts d on e.deptid=d.did where e.ename='李欣'; if v_dname='質(zhì)量部' then v_ecomm:=500; elsif v_dname='技術(shù)部' then v_ecomm:=600; elsif v_dname='人力資源部' then v_ecomm:=200; else v_ecomm:=100; end if; update t_employees e set e.ecomm=e.ecomm v_ecomm where e.ename='李欣'; commit; end; -- 循環(huán)結(jié)構(gòu) --1.100之和并判斷是奇數(shù)還是偶數(shù) -- for loop declare v_sum number:=0; begin for v_i in 0..100 loop v_sum:=v_sum v_i; end loop; dbms_output.put_line('和為'||v_sum); end; --while loop declare v_i number:=0; v_sum number:=0; begin while v_i<=100 loop v_sum:=v_sum v_i; v_i:=v_i 2; --改變循環(huán)變量的值 end loop; dbms_output.put_line('和為'||v_sum); end; --循環(huán)結(jié)構(gòu)使用 create table t_users ( id int primary key, tname varchar2(20), tsex char(6) ) create sequence seq_users start with 1 increment by 1 nocache; select * from t_users --for循環(huán)批量數(shù)據(jù) declare begin for v_i in 1..10000 loop if mod(v_i,2)=0 then insert into t_users values(seq_users.nextval,'李'||v_i,'男'); else insert into t_users values(seq_users.nextval,'王'||v_i,'女'); end if; end loop; commit; end; --while循環(huán)批量數(shù)據(jù) declare v_i number:=1; begin while v_i<=1000 loop insert into t_users values(seq_users.nextval,'李'||v_i,'男'); v_i:=v_i 1; end loop; commit; end; ---into是只能附一個值 -- 如果是多個值，就是游標，就是結(jié)果集，分為;cursor(顯示游標),隱式游標 -- 查詢員工表的中的數(shù)據(jù)，并輸出每個人的姓名和性別 -- while..loop declare --定義一個顯示游標 cursor v_datas is select * from t_employees; -- 定義一行 v_linedatas t_employees%rowtype; begin --打開這個游標 open v_datas; --遍歷游標 fetch v_datas into v_linedatas; --遍歷第一行 while v_datas%found loop -- 如果有數(shù)據(jù)進入循環(huán)體 dbms_output.put_line(v_linedatas.ename||',性別是:'||v_linedatas.esex); fetch v_datas into v_linedatas;--移動到下一行 end loop; -- 關(guān)閉游標 close v_datas; end; -- for..loop declare --定義一個顯示游標 cursor v_datas is select * from t_employees; begin for v_linedatas in v_datas loop dbms_output.put_line(v_linedatas.ename||',性別是:'||v_linedatas.esex); end loop; -- 關(guān)閉游標 end; -- 隱式游標 -- 查詢質(zhì)量部員工的性別，是男加1000，是女加5000 declare v_sex t_employees.esex%type; v_comm t_employees.ecomm%type; begin --質(zhì)量部的員工的信息 for v_datas in ( select e.esex ,e.ename from t_employees e right join t_depts d on e.deptid=d.did where d.dname='質(zhì)量部') loop dbms_output.put_line(v_datas.esex||v_datas.ename); if v_datas.esex='男' then v_comm:=200; else v_comm:=50; end if; --執(zhí)行更新 update t_employees e set e.ecomm=e.ecomm v_comm where e.ename=v_datas.ename; commit; end loop; end; select * from t_employees ---存儲過程 -- 存儲(是以一個名字來存儲) 過程(過程化的語句塊) -- pl-sql塊是不能夠被編程語言直接調(diào)用,只能運行在數(shù)據(jù)庫端 -- 以一個名字命名，這個名字被編程語言call，這樣形成交互 --預(yù)編譯，編譯一次，下次調(diào)用的話不需要再次編譯，性能好， --能夠處理復(fù)雜的業(yè)務(wù)邏輯, --可以有傳入和輸出參數(shù),缺點:占用存儲空間 --sql(dml語言，調(diào)用一次編譯一次)，性能沒有存儲過程好 --基本結(jié)構(gòu) create or replace procedure 存儲過程的名字 ( --傳入和輸出參數(shù) ) as begin end; ---檢查登錄 create or replace procedure p_checklogin ( v_uname in varchar2, --傳入?yún)?shù)不需要長度 v_pwd in varchar2, v_msg out varchar2 ) as v_count int; begin --檢查登錄 select count(*) into v_count from t_employees e where e.ename=v_uname and trim(e.epwd)=v_pwd; --進行判斷 if v_count>0 then v_msg:='登錄成功'; else v_msg:='登錄失敗'; end if; --記錄 insert into t_userlog values(seq_userlog.nextval,v_uname,default,v_msg); commit; end; -- 變更存儲過程 create table t_userlog ( ulid int primary key, uname varchar2(20), -- 登錄的人 logintime date default sysdate, --登錄的時間 loginresult varchar2(20) --登錄的結(jié)果 ) create sequence seq_userlog start with 1 increment by 1 nocache; select * from t_userlog -- 直接返回一個結(jié)果集(不在存儲過程內(nèi)部遍歷) --**部門的員工的數(shù)量和員工的姓名 create or replace procedure p_queryempDatas ( v_dname in varchar2, --部門名稱 v_count out int, --部門員工數(shù)量 v_namedatas out sys_refcursor --部門員工姓名 ) as begin --1.通過部門名稱得到部門的員工的數(shù)量 select count(e.ename) into v_count from t_employees e right join t_depts d on e.deptid=d.did where d.dname=v_dname; --2.員工姓名(不在存儲過程內(nèi)部遍歷) open v_namedatas for select e.ename from t_employees e right join t_depts d on e.deptid=d.did where d.dname=v_dname; end; ----**部門的員工的數(shù)量和屬于這個部門的員工的性別， --如果是男，工資加250； --女加100； create or replace procedure p_querydeptupdatesalary ( v_dname in varchar2, v_count out int ) as v_salary t_employees.esalary%type; begin --1.通過部門名稱得到部門的員工的數(shù)量 --select count(e.ename) into v_count from t_employees e --right join t_depts d --on e.deptid=d.did where d.dname=v_dname; --調(diào)用函數(shù)，減少冗余代碼 v_count:=f_querydeptempnum(v_dname); --2.這個部門的所有員工的信息 for v_linedatas in (select * from t_employees e right join t_depts d on e.deptid=d.did where d.dname=v_dname) loop if v_linedatas.esex='男' then v_salary:=250; else v_salary:=150; end if; --執(zhí)行更新 update t_employees e set e.esalary=e.esalary v_salary where e.ename=v_linedatas.ename; commit; end loop; end; select * from t_employees -- 自定義函數(shù) 特殊的存儲過程 -- 自定義函數(shù) 1.關(guān)鍵字function 2.只能返回一個值 create or replace function f_querydeptempnum ( v_dname in varchar2 ) return int --切記這個地方不能加; as v_count int; begin select count(e.ename) into v_count from t_employees e right join t_depts d on e.deptid=d.did where d.dname=v_dname; return v_count; end; -- 查詢一個表t_employees的條數(shù) 如果系統(tǒng)中的表很多，這樣會造成冗余 create or replace function f_queryempcount return int as v_count int; begin select count(*) into v_count from t_employees ; return v_count; end; --動態(tài)sql -- 一個值 create or replace function f_querytablecount ( v_tableName in varchar2 ) return int as v_sql varchar2(1000); v_count int; begin v_sql:='select count(*) from ' ||v_tableName; -- 執(zhí)行這個動態(tài)sql,是一個值 execute immediate v_sql into v_count; return v_count; end; ---多個值 create or replace function f_querytabledatas ( v_tableName in varchar2 ) return sys_refcursor as v_sql varchar2(1000); v_datas sys_refcursor; begin v_sql:='select * from ' ||v_tableName; -- 執(zhí)行這個動態(tài)sql,是一個結(jié)果集 open v_datas for v_sql; return v_datas; end; --sql語句分頁 固定2條 select * from t_employees -- sql分頁，系統(tǒng)大了，會造成SQL冗余 select * from (select rownum rm, e.* from t_employees e where rownum<=4) tmp where tmp.rm>2 --存儲過程分頁 一個存儲過程可以對系統(tǒng)中的表都可以分頁 -- tablename in 表名 --pagenum in 每頁幾條 --currentnum in 第幾頁 --datas out 每頁的結(jié)果集 --count out 總條數(shù) --pagesize out 總頁數(shù) create or replace procedure p_pagemodel ( v_tablename in varchar2, v_pagenum in int, v_currentnum in int, v_datas out sys_refcursor, v_count out int, v_pagesize out int ) as v_endindex int:= v_currentnum*v_pagenum; v_startindex int:=(v_currentnum-1)*v_pagenum; v_sql varchar2(1000); begin --1.構(gòu)建總的條數(shù)動態(tài)sql v_sql:='select count(*) from '||v_tablename; --2.執(zhí)行動態(tài)sql execute immediate v_sql into v_count; --3.獲取總頁數(shù) if mod(v_count,v_pagenum)=0 then v_pagesize:=v_count/v_pagenum; else --除不盡 v_pagesize:=floor(v_count/v_pagenum) 1; end if; --獲取分頁結(jié)果集 v_sql:='select * from '||'(select rownum rm, e.* from '||v_tablename|| ' e where rownum<='||v_endindex||') tmp' ||' where tmp.rm>'||v_startindex; dbms_output.put_line(v_sql); --執(zhí)行動態(tài)sql，返回的是一個結(jié)果集 open v_datas for v_sql; end; -- 面試的時候，什么是事務(wù) --jdbc 事務(wù) 自動事務(wù) --hiernate事務(wù) --mybatis事務(wù) --spring事務(wù) --python事務(wù) 手動事務(wù) -- rollback回滾事務(wù) commit提交事務(wù) savepoint 設(shè)置事務(wù)保存點 --數(shù)據(jù)庫事務(wù) --事務(wù)就是一個工作單元，所謂的工作單元，就是不可分割的一個或多個SQL -- (insert ,update,delete) --秦日霞借錢1000給李欣，并記錄日志。 select * from t_employees --秦日霞借錢1000給李欣 --記錄日志 create or replace procedure p_operatorsalary ( v_rname in varchar2, v_tname in varchar2, v_money in number, v_msg out varchar2 ) as begin -- 1.先把借錢人的錢扣掉 update t_employees e set e.esalary=e.esalary-v_money where e.ename=v_rname; --2.加上給借錢人 update t_employees e set e.esalary=e.esalary v_money where e.ename=v_tname; --設(shè)置一個事務(wù)保存點 給個名字 savepoint a; --3.記錄日志 insert into t_userlog values(seq_userlog.nextval,v_tname,'1997-12-30','借錢'); commit; exception when others then rollback to a; --回滾到事務(wù)保存點a，提交 commit; end; select * from t_userlog ------------- --索引，job(定時任務(wù))，視圖，觸發(fā)器 -- 索引 -- 怎么來提高查詢的性能? -- 1.索引 2. SQL優(yōu)化 -- 索引就是來提高查詢的性能。 -- 打個比方來說:書的目錄 聚集索引 ； --書的頁碼:聚集索引 -- 70W條的數(shù)據(jù)以上我們才考慮建立索引 -- 建立一張表 create table t_stuinfo ( suid number(7) primary key, sname varchar2(100), sbirthday date default sysdate, ssex char(4) ) --加載80W條數(shù)據(jù) declare begin for v_i in 1..1200000 loop if mod(v_i,2)=0 then insert into t_stuinfo values(v_i,'李'||v_i,default,'男'); else insert into t_stuinfo values(v_i,'王華'||v_i,default,'女'); end if; end loop; commit; end; delete from t_stuinfo select count(*) from t_stuinfo select * from t_stuinfo -- 白盒測試 查詢 王華67001 查這個人的信息 create or replace procedure p_queryinfo ( v_name in varchar2 ) as v_begintime varchar2(1000); v_endtime varchar2(1000); v_birthday t_stuinfo.sbirthday%type; begin v_begintime:=to_char(systimestamp,'yyyy-MM-dd hh24:mi:ss.ff'); select s.sbirthday into v_birthday from t_stuinfo s where s.sname=v_name; v_endtime:=to_char(systimestamp,'yyyy-MM-dd hh24:mi:ss.ff'); dbms_output.put_line('開始時間:'||v_begintime); dbms_output.put_line('接受時間:'||v_endtime); end; -- /** 開始時間:2018-03-23 09:29:31.490000000 接受時間:2018-03-23 09:29:31.537000000 --47000000 開始時間:2018-03-23 09:32:05.235000000 接受時間:2018-03-23 09:32:05.250000000 --15000000 -- 開始時間:2018-03-23 09:32:52.942000000 接受時間:2018-03-23 09:32:52.958000000 16000000 開始時間:2018-03-23 09:33:26.529000000 接受時間:2018-03-23 09:33:26.576000000 47 **/ -- 建立索引 create index snameindex on t_stuinfo(sname); drop index snameindex; call p_queryinfo('李966002'); -- job定時任務(wù) 數(shù)據(jù)庫定時任務(wù) --自動化定時任務(wù) 對于表的歷史數(shù)據(jù)的一個定時清理 -- 1-10 -- t_stuinfo ，每隔1分鐘自動插入一條數(shù)據(jù) delete from t_stuinfo; commit; create sequence seq_stuinfo start with 1 increment by 1 nocache; --建立一個存儲過程 create or replace procedure p_timetaskadddata as begin insert into t_stuinfo values(seq_stuinfo.nextval,'李欣',default,'男'); commit; end; --建立一個任務(wù) variable job2018 number; -- 命令行窗口 提交這個定時任務(wù) SQL> variable job2018 number; SQL> begin 2 dbms_job.submit(:job2018,'p_timetaskadddata;', sysdate,'sysdate 1/1440'); 3 end; 4 / select * from t_stuinfo SQL> begin 2 dbms_job.remove(:job2018); 3 end; 4 / **PL/SQL procedure successfully completed job2018 ---------** 來源：https://www./content-2-780751.html |
|
|
