oracle入門(一)

標籤：ace   dcl   空格   ==   ddr   alter   base   運算子   isa   

### 一、體繫結構
1. 資料庫 ： 只有一個資料庫2. 執行個體 : 後台啟動並執行一個進程3. 資料表空間: 邏輯儲存單位4. 資料檔案: 實體儲存體單位     5. 使用者:面向使用者管理，由使用者來管理資料表空間,使用者向資料表空間中添加資料，最終儲存到資料檔案中使用者：sys 超級管理員 system:普通的管理員```### 二、資料表空間```1. 建立資料表空間的文法create tablespace 資料表空間名稱datafile ‘路徑/xxx.dbf‘size 200Mautoextend on  開啟自動擴充 / offnext 30M;   每次的擴充量2. demo:　　　　create tablespace 666      datafile ‘c:/666‘      size 200M      autoextend on        next 30M;  3. 刪除的文法 drop  tablespace 資料表空間名稱; -- 只能刪除邏輯概念 drop  tablespace 資料表空間名稱 including contents and datafiles;  -- 邏輯,物理同時刪除```### 三、使用者```1. 建立使用者create user 使用者名稱 identified by 密碼default tablespace 資料表空間名稱;2. democreate user rose identified by rosedefault tablespace 666;3. 許可權 connect ：基本許可權：create sessionresource :開發人員許可權dba：管理select * from session_privs;--查詢目前使用者的所有許可權4. 賦予許可權grant 許可權列表 to 使用者列表;5. 回收許可權revoke 許可權列表 from  使用者列表;```### 四、表的管理```1. 資料類型字串char（4000）: 定長字串 ,  char(10) ,    取出的時候,不顧的用空格補齊varchar：變長字串  varchar(10)varchar2()：變長字串, 向後相容long : 最大儲存量：2G ，使用clob取代日期date:mysql 中的datetimetimestamp: 時間戳記類型, 精確到秒後9位元值number(11) : 整數number(m,n) : 浮點數:m ：總的位元 n： 小數點後的位元   555.55大資料類型clob: 字元大資料類型,  最大儲存量 4Gblob：位元組大資料類型, 2. 建立表create table 表（欄位名  類型  約束,....）3. 修改表添加列     alter table 表名  add 列名  類型 約束;     alter table customer add address varchar2(200);    刪除列     alter table 表名 drop column  列名;     alter table customer drop column address;    修改列屬性     alter table 表名 modify 列名  類型 約束;     alter table customer modify custName varchar2(400);    重新命名列名     alter table 表名 rename column 原列名 to 新列名;     alter table customer rename column custName to cname;4. 約束主鍵，外鍵，非空，預設，唯一，檢查create table customer(       custId number primary key ,       custName varchar2(200) not null unique,       age number default 18 check(age between 1 and 120),       sex char(2) check(sex in (‘男‘,‘女‘)))5. 事務特性： 原子性，隔離性，一致性，持久性隔離等級：read uncommited    read commited    repeatable read    serializablemysql： 支援四個隔離等級， 預設的是 repeatable readoracle: 支援的read commited， serializable， read only,預設的是：read commited6. sql語句的分類 DCL: 資料控制語言 ：grant  ， revokeDQL：資料查詢語言 ：selectDDL：資料定義語言 (Data Definition Language) : create drop  , alterDML: 資料操作語言： insert update ,delete7. DML語句insert into 表名 values(值,....)insert into 表(欄位,...) values(值,....)update 表 set 欄位=值, 欄位=值,...  where 條件delete from 表 where 條件;truncate table 表; -- 刪除表中的所有資料, 效率極高,8. 序列最常用文法create  sequence 序列名;屬性： nextval : 下一個值      currval： 當前值:  必須先執行一次nextval，才能擷取當前值  dual --虛表,為了完善文法結構            select c_seq.nextval from dual;            select c_seq.currval from dual;    完整的文法     create sequence 序列名     start with 1  起始值     increment by 1 自增量     maxvalue 9999 /nomaxvalue /minvalue 1  /nominvalue     cycle  迴圈     cache 20;```### 五、scott下的表```1. soctt : oracle公司的第一位程式員, pointbase2. 預設有一個使用者scott ，密碼是tiger3. 解鎖：alter user  使用者名稱 account unlock;  / lockalter user scott account unlock;4. 重設密碼：alter user 使用者名稱 identified by  密碼；alter user scott identified by tiger;5. emp ,dept ,salgrade```### 六、函數(單行，多行)```1. 單行函數一行記錄返回一行結果字串函數upper  lowerselect * from emp where upper(ename) like upper(‘%Ia%‘);日期函數兩個日期相減 == 天select sysdate - hiredate from emp兩個日期相減/7 == 周select (sysdate - hiredate) /7 from emp月差select months_between(sysdate ,hiredate) from emp;數值函數round: 四捨五入select round(2.777) from dual;trunc: 截取select trunc(2.777, 1) from dual;mod : 模運算子select mod(3 , 10) from dual;通用函數--在Oracle中空+任何數值=空nvl(p1, p2)select sal * 12 +  nvl(comm,0),sal , comm from emp;轉換函式to_char(p1 ,p2)  將日期轉換為字串        p1 : 將要轉換的日期        p2 : 格式to_date(p1 ,p2)    將字串轉換為日期p1 : 將要轉換的字串         p2 : 格式                insert into emp(empno ,hiredate) values(5000,to_date(‘2018-8-24‘,‘yyyy-mm-dd‘))       select sysdate,to_char(sysdate ,‘yyyy-mm-dd  hh24:mi:ss day‘) from dual;        select ‘2018-08-24  11:16:45 friday‘ ,        to_date(‘2018-08-24  11:16:45 friday‘,‘yyyy-mm-dd  hh24:mi:ss day‘)         from dual;2. 多行函數(彙總函式)多行記錄返回一行結果select count(*) from emp;count ,sum ,avg ,max ,min```### 七、分組舉例```0. 分組後能查詢的列彙總函式, group by中出現列1. 查詢人數大於5的部門資訊select * from dept where deptno in       (select deptno from emp group by deptno having count(1) > 5)       --select中 deptno, count(1)不能顛倒    select t.deptno ,t.num,d.dname ,d.locfrom dept d ,(select deptno,count(1) num from emp group by deptno) t    where d.deptno = t.deptno and num > 5        select e.deptno ,count(1),d.dname,d.loc    from emp e,dept d    where e.deptno = d.deptno    group by e.deptno ,d.dname,d.loc    having count(1) > 5;```### 八、多表查詢```1. 內串連: 只能顯示合格記錄-- 查詢員工的領導資訊    select e.empno,e.ename ,m.empno ,m.ename     from emp e,emp m     where e.mgr = m.empno;    -- 在上面的基礎上,查詢員工的部門名稱    select e.empno,e.ename,d.dname ,m.empno ,m.ename     from emp e,emp m ,dept d    where e.mgr = m.empno    and e.deptno  = d.deptno;    -- 在上面的基礎上,查詢領導的部門名稱    select e.empno,e.ename,d1.dname ,m.empno ,m.ename ,d2.dname    from emp e,emp m ,dept d1,dept d2    where e.mgr = m.empno    and e.deptno  = d1.deptno    and m.deptno  = d2.deptno;    -- 在上面的基礎上，查詢領導和員工的工資等級    select e.empno,e.ename,d1.dname,s1.grade ,m.empno ,m.ename ,d2.dname,s2.grade    from emp e,emp m ,dept d1,dept d2,salgrade s1 ,salgrade s2    where e.mgr = m.empno    and e.deptno  = d1.deptno    and m.deptno  = d2.deptno    and e.sal between s1.losal and s1.hisal    and m.sal between s2.losal and s2.hisal;2. 外串連: 能顯示基準表中所有的記錄左外串連： left join on右外串連 : right join  on特殊外串連 : (+)：以對方表為基準表demo：查詢員工的領導資訊（沒有領導也要顯示）select e.empno,e.ename ,m.empno ,m.ename     from emp e left join emp m     on e.mgr = m.empno;    select e.empno,e.ename ,m.empno ,m.ename       from emp m right join emp e    on e.mgr = m.empno;        select e.empno,e.ename ,m.empno ,m.ename     from emp e,emp m     where e.mgr = m.empno(+);```### 九、子查詢```1. 什麼是子查詢一個查詢語句包含另一個查詢語句外查詢，內查詢2. demo-- 查詢與7369工作的一致的員工    select job from emp where empno = 7698;    select * from emp where job = (select job from emp where empno = 7698);    -- 查詢高於本部門平均薪水的員工    select e.ename ,e.sal ,e.deptno ,t.avgsal ,t.deptno    from emp e,(select avg(sal) avgsal,deptno from emp group by deptno) t     where e.deptno = t.deptno and e.sal > t.avgsal    -- 查詢存在員工的部門資訊       -- 只要在員工出現過的部門編號    select * from dept where deptno in (select distinct deptno from emp); 3. 小結 返回一行一列： = > < != <>  返回多行多列： 作為表使用 返回一列多行： in =any =some```### 十二、分頁查詢```1. rownum : 偽列, 行號，從1開始，依次遞增 , 在載入時產生行號2. select rownum ,e.* from emp e;-- 查詢前三條記錄select rownum ,e.* from emp e where rownum <= 3-- 查詢4 - 6 條記錄select rownum ,e.* from emp e where rownum between 4 and 6;       先產生行號，作為表,再條件式篩選select t.*  from (select rownum  rn,e.* from emp e) t  where t.rn between 4 and 6;-- 查詢工資最高的前三名  nulls last nulls first   先排序，取前三名   select t.*,rownum from (select e.* from emp e order by sal desc)t   where rownum <= 3;-- 查詢工資較高的4-6名   select m.*   from (select t.*,rownum rn from (select e.* from emp e order by sal desc)t) m   where m.rn between 4 and 6;```

oracle入門(一)