Oracle基礎(chǔ)操作

• 查看進(jìn)程
  ps –ef | grep ora

• 登陸數(shù)據(jù)庫
  sqlplus sys/123456 as sysdba
  sqlplus scott/tiger@db88

• 切換用戶
  conn username;

• 查看用戶
  show user; 查看當(dāng)前登錄用戶
  select username from user_users; 以 user開頭的：當(dāng)前用戶可以鲁沥，它只能看當(dāng)前信息
  select username from dba_users; 以 dba開頭的：只有dba權(quán)限的才能看，sys用戶和system用戶
  select username from all_users; 以 all開頭的：權(quán)限內(nèi)所有的

• 修改密碼
  password scott
  alter user scott identified by 新密碼

• 鎖定用戶
  alter user scott account lock;
  alter user scott account unlock;

• 查看當(dāng)前用戶的table
  select table_name from user_tables;

• 視圖
  視圖是基于一個表或多個表或視圖的邏輯表耕魄，本身不包含數(shù)據(jù)画恰，通過它可以對表里面的數(shù)據(jù)進(jìn)行查詢和修改。
  視圖基于的表稱為基表吸奴。視圖是存儲在數(shù)據(jù)字典里的一條select語句允扇。
  通過創(chuàng)建視圖可以提取數(shù)據(jù)的邏輯上的集合或組合。
  create view v_tt as select owner,object_id from all_objects where object_id<1000;

  視圖的優(yōu)點：
  (1)對數(shù)據(jù)庫的訪問则奥，因為視圖可以有選擇性的選取目標(biāo)表里的一部分考润。
  (2)用戶通過簡單的查詢可以從復(fù)雜查詢中得到結(jié)果。
  (3)維護(hù)數(shù)據(jù)的獨立性读处，視圖可從多個表檢索數(shù)據(jù)糊治。
  (4)對于相同的數(shù)據(jù)可產(chǎn)生不同的視圖。

• 動態(tài)性能視圖
  以v$ 開頭的叫 動態(tài)性能視圖（v$database）

• 查看服務(wù)器狀態(tài)
  select open_mode from v$database;

• 查看實例名
  select instance_name from v$instance;

• 數(shù)據(jù)字典
  select * from dict;
  X$ oracle數(shù)據(jù)庫核心部分,加載數(shù)據(jù)庫時即被使用,加密命名【一般不會更改罚舱、也不會查看井辜，當(dāng)前庫自己使用】
  加載數(shù)據(jù)庫中，得調(diào)用一些數(shù)據(jù)庫信息管闷，這些數(shù)據(jù)會駐留在內(nèi)存中粥脚，這些調(diào)用比較多，以表形式存在
  select * from dict where table_name='USER_TABLES';

• 腳本執(zhí)行
  @/home/oracle/a.sql

常用函數(shù)

• as給列以別名顯示:
  select username as 別名 from t_user;(這里的as關(guān)鍵字可以省略)
• distinct去掉重復(fù)的行：
  select distinct username from t_user;
• 使用運算符：
  select age+10 from t_user;
• in匹配集合中的任意值 (any all exists)
  select * from t_user where username in('aa','bb');
• like模糊查詢：%匹配0個或多個任意字符包个，_匹配1個任意字符刷允。
  select * from t_user where username like '%aaa%';
• null判斷某列為空
  select * from t_user where sex is null; (is not null)
• order by排序：ASC: 升序排列（可以省略）,DESC: 降序排列
  升序:select u.userid,u.username from t_user u order by u.userid;
  降序:select u.userid,u.username from t_user u order by u.userid desc;
• 系統(tǒng)函數(shù)
  AVG–求平均值，COUNT–統(tǒng)計記錄數(shù)碧囊，MAX–最大值树灶，MIN–最小值，SUM–求和
  select min(userid),max(userid) from t_user;
  select count(*) from t_user;
  select count(sex) from t_user; (不為空的記錄數(shù))
  select count(distinct sex) from t_user;（不為空且不重復(fù)）
• group by分組
  group by有一個原則,就是 select 后面的所有列中,沒有使用聚合函數(shù)的列,必須出現(xiàn)在 group by 后面
  select deptno,count() from emp group by deptno;
  select sex,age,count() from t_user group by sex,age;
• having過濾分組：
  select username from t_user group by username having count(*) >=2;
• 偽列
  rownum【代表行號糯而，來標(biāo)記一行天通，唯一的號碼的】
  SELECT * FROM all_objects WHERE rownum < 100;
• between and
  select * from t where OBJECT_ID between 51070 and 51080; 等價于下面這條語句 select * from t where OBJECT_ID >=51070 and OBJECT_ID <=51080;
• 字符串連接
  select '表名是'||TABLE_NAME||',作用是'||COMMENTS from dict where table_name='USER_TABLES';
• length：
  select length('##123##') len from dual;
• LTRIM,RTRIM,TRIM【多用于處理空格】
• TO_CHAR 是把日期或數(shù)字轉(zhuǎn)換為字符串
  select sysdate from dual;
  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
  select to_char(123,'9999.00') from dual;
  select to_char(12333,'$99,999.99') from dual;
  select to_char(123334444,'$999,999,999.99') from dual;
• substr 截取函數(shù)
  select substr('12345',2,3) from dual; (從第二個字符開始，截取3個)
  select substr('123456789',-5) from dual;（截取最后5個）
• 字符是否包含instr(string,substring,position,occurrence)
  string:代表源字符串
  substring:代表想從源字符串中查找的子串
  position:代表查找開始的位置,默認(rèn)為1
  occurrence:代表查找值第幾次出現(xiàn),結(jié)果為字符串的位置
  沒有找到歧蒋，instr函數(shù)返回0.
  select substr('12345',2,3) from dual;
  select substr('123456789',-5) from dual;
  SELECT instr('syranmo','s') FROM dual; -- 返回 1
  SELECT instr('syranmo','ra') FROM dual; -- 返回 3
  SELECT instr('syran mo','a',1,2) FROM dual; -- 返回 0
• abs 絕對值
• round 函數(shù) (四舍五入)
• trunc 取整【 截掉小數(shù)點后值土砂，不會四舍五入】
  select 3/2 from dual;
  select trunc(3/2) from dual;
• to_date 日期時間計算
  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期轉(zhuǎn)化為字符串
  select to_char(sysdate,'yyyy') as nowYear from dual; //獲取時間的年
  select to_char(sysdate,'mm') as nowMonth from dual; //獲取時間的月
  select to_char(sysdate,'dd') as nowDay from dual; //獲取時間的日
  select to_char(sysdate,'hh24') as nowHour from dual; //獲取時間的時
  select to_char(sysdate,'mi') as nowMinute from dual; //獲取時間的分
  select to_char(sysdate,'ss') as nowSecond from dual; //獲取時間的秒
  求某天是星期幾：
  select to_char(to_date('2015-05-07','yyyy-mm-dd'),'day') from dual;
  兩個日期間的天數(shù)：
  select floor(sysdate - to_date('20150501','yyyymmdd')) from dual;
  select sysdate - to_date('20150501','yyyymmdd') from dual;
  select trunc(sysdate - to_date('20150501','yyyymmdd')) from dual;
  月份差：
  select months_between(to_date('03-31-2015','MM-DD-YYYY'),to_date('01-15-2015','MM-DD-YYYY')) "MONTHS" FROM DUAL;
  一年的第幾天：
  select TO_CHAR(to_date('2015-2-11','yyyy-mm-dd'),'DDD'),sysdate from dual;
• replace替換函數(shù)
  select replace('abc','b','######') from dual;
• lpad (左添充) rpad (右填充)
  select lpad('func',7,'=') s1, rpad('func',7,'-') s2 from dual;
• decode(實現(xiàn)if ..then 邏輯)
  select ENAME,decode(deptno,10,'部門1',20,'部門2',30,'部門3','其他') "所在部門" from emp; 如果deptno是10州既，就是部門一；20就是部門二
• case when（實現(xiàn)if ..then 邏輯）
  select ename,
  case
  when sal<1000 then '掙得太少'
  when sal>1001 and sal<2000 then '掙得中等'
  when sal>2001 and sal<4000 then '掙得比較高'
  else '掙得太多'
  end
  from emp;
• wm_concat(column)和LISTAGG：字段合并
  11.2以前可以使用wmsys.wm_concat萝映，后來oracle取消了這個函數(shù),改為使用LISTAGG

create table shopping(u_id  int,goods varchar2(100),num int);
insert into shopping values(1,'蘋果',2);
insert into shopping values(2,'梨子',5);
insert into shopping values(1,'西瓜',4);
insert into shopping values(3,'葡萄',1);
insert into shopping values(3,'香蕉',1);
insert into shopping values(1,'橘子',3);
commit;
SELECT * FROM shopping;
select u_id, wmsys.wm_concat(goods) goods_sum  from shopping  group by u_id;
select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum  from shopping  group by u_id ;
select u_id,LISTAGG(goods) WITHIN GROUP (ORDER BY u_id) goods_sum  from shopping  group by u_id;
select u_id, LISTAGG(goods || '(' || num || '斤)' ) WITHIN GROUP (ORDER BY goods) goods_sum from shopping group by u_id ;

• 左吴叶、右連接、全連接
  左外連接（Left outer join/ left join）
  右外連接（right outer join/ right join）
  全連接（full join）
• 內(nèi)聯(lián)接查詢 inner join on
• 分析函數(shù)
  (1)sum函數(shù)序臂，統(tǒng)計總合
  按照月份蚌卤，統(tǒng)計每個地區(qū)的總收入
  select earnmonth, area, sum(personincome)
  from earnings
  group by earnmonth, area order by earnmonth;
  (2)rollup函數(shù)
  按照月份，地區(qū)統(tǒng)計收入
  select earnmonth, area, sum(personincome)
  from earnings
  group by rollup(earnmonth, area);
  (3)cube函數(shù)
  按照月份奥秆，地區(qū)進(jìn)行收入總匯總
  select earnmonth, area, sum(personincome)
  from earnings
  group by cube(earnmonth, area)
  order by earnmonth, area nulls last;
  (4)grouping函數(shù)
  在以上例子中逊彭，是用rollup和cube函數(shù)都會對結(jié)果集產(chǎn)生null，這時候可用grouping函數(shù)來確認(rèn)
  該記錄是由哪個字段得出來的
  grouping函數(shù)用法构订，帶一個參數(shù)侮叮，參數(shù)為字段名，結(jié)果是根據(jù)該字段得出來的就返回1悼瘾，反之返回0
  select decode(grouping(earnmonth), 1, '所有月份', earnmonth) 月份,
  decode(grouping(area), 1, '全部地區(qū)', area) 地區(qū),
  sum(personincome) 總金額
  from earnings
  group by cube(earnmonth, area)
  order by earnmonth, area nulls last;
  (5)rank() over開窗函數(shù)
  按照月份囊榜、地區(qū)，求打工收入排序

 select earnmonth 月份,
       area 地區(qū),
       sname 打工者,
       personincome 收入,
       rank() over(partition by earnmonth, area order by personincome desc) 排名
  from earnings;

(6)dense_rank() over開窗函數(shù)
按照月份亥宿、地區(qū)卸勺，求打工收入排序2

select earnmonth 月份,
       area 地區(qū),
       sname 打工者,
       personincome 收入,
       dense_rank() over(partition by earnmonth, area order by personincome desc) 排名
  from earnings;

(7)row_number() over開窗函數(shù)
按照月份、地區(qū)烫扼，求打工收入排序3

select earnmonth 月份,
       area 地區(qū),
       sname 打工者,
       personincome 收入,
       row_number() over(partition by earnmonth, area order by personincome desc) 排名
  from earnings;

rank,dense_rank,row_number的區(qū)別：
結(jié)果集中如果出現(xiàn)兩個相同的數(shù)據(jù)曙求，那么rank會進(jìn)行跳躍式的排名，
比如兩個第二映企，那么沒有第三接下來就是第四悟狱；
但是dense_rank不會跳躍式的排名，兩個第二接下來還是第三卑吭；
row_number即使兩個數(shù)據(jù)相同芽淡，排名也不一樣。
(8)sum累計求和
根據(jù)月份豆赏、地區(qū)求出各個打工者收入總和套才，按照收入由少到多排序

select earnmonth 月份,
       area 地區(qū),
       sname 打工者,
       sum(personincome) over(partition by earnmonth, area order by personincome) 總收入
  from earnings;

(9)max枪芒，min，avg和sum函數(shù)綜合運用
按照月份和地區(qū)求打工收入最高值废恋，最低值椭赋，平均值和總額

  select distinct earnmonth 月份,
                area 地區(qū),
                max(personincome) over(partition by earnmonth, area) 最高值,
                min(personincome) over(partition by earnmonth, area) 最低值,
                avg(personincome) over(partition by earnmonth, area) 平均值,
                sum(personincome) over(partition by earnmonth, area) 總額
  from earnings;

(10)lag和lead函數(shù)
lag和lead函數(shù)可以在一次查詢中取出某個字段的前N行和后N行的數(shù)據(jù)（可以是其他字段的數(shù)據(jù)抚岗，比如根據(jù)字段甲查詢上一行或下兩行的字段乙）
求出每個打工者上個月和下個月有沒有賺錢(personincome大于零即為賺錢)

 select earnmonth 本月,
       sname 打工者,
       lag(decode(nvl(personincome, 0), 0, '沒賺', '賺了'), 1, 0) over(partition by sname order by earnmonth) 上月,
       lead(decode(nvl(personincome, 0), 0, '沒賺', '賺了'), 1, 0) over(partition by sname order by earnmonth) 下月
  from earnings;

• 行列轉(zhuǎn)換

create table TEST_TB_GRADE 
( 
ID NUMBER(10) not null, 
USER_NAME VARCHAR2(20 CHAR), 
COURSE VARCHAR2(20 CHAR), 
SCORE FLOAT
);
insert into TEST_TB_GRADE values(1,'michael','語文',78);
insert into TEST_TB_GRADE values(2,'michael','數(shù)學(xué)',95);
insert into TEST_TB_GRADE values(3,'michael','英語',81);
insert into TEST_TB_GRADE values(4,'xiaoxiao','語文',97);
insert into TEST_TB_GRADE values(5,'xiaoxiao','數(shù)學(xué)',78);
insert into TEST_TB_GRADE values(6,'xiaoxiao','英語',91);
insert into TEST_TB_GRADE values(7,'zhangsan','語文',80);
insert into TEST_TB_GRADE values(8,'zhangsan','數(shù)學(xué)',55);
insert into TEST_TB_GRADE values(9,'zhangsan','英語',75);
insert into TEST_TB_GRADE values(10,'lisi','語文',87);
insert into TEST_TB_GRADE values(11,'lisi','數(shù)學(xué)',65);
insert into TEST_TB_GRADE values(12,'lisi','英語',75);
commit;
SELECT * FROM TEST_TB_GRADE;

行轉(zhuǎn)列

select t.user_name, 
sum(decode(t.course, '語文', score,null)) as CHINESE, 
sum(decode(t.course, '數(shù)學(xué)', score,null)) as MATH, 
sum(decode(t.course, '英語', score,null)) as ENGLISH 
from test_tb_grade t 
group by t.user_name 
order by t.user_name;

select t2.SCORE_GP, 
  sum(decode(t2.course, '語文', COUNTNUM,null)) as CHINESE, 
  sum(decode(t2.course, '數(shù)學(xué)', COUNTNUM,null)) as MATH, 
  sum(decode(t2.course, '英語', COUNTNUM,null)) as ENGLISH 
from ( 
  select t.course, 
         case when t.score  <60 then '00-60'
              when t.score >=60 and t.score <80  then '60-80'
              when t.score >=80 then '80-100' end as SCORE_GP, 
         count(t.score) as COUNTNUM 
  FROM test_tb_grade t 
  group by t.course,  
        case when t.score  <60  then '00-60'
              when t.score >=60 and t.score <80  then '60-80'
              when t.score >=80 then '80-100' end
  order by t.course ) t2 
group by t2.SCORE_GP 
order by t2.SCORE_GP;

列轉(zhuǎn)行

CREATE TABLE TEST_TB_GRADE2 AS
select t.user_name USER_NAME, 
sum(decode(t.course, '語文', score,null)) as CN_SCORE, 
sum(decode(t.course, '數(shù)學(xué)', score,null)) as MATH_SCORE, 
sum(decode(t.course, '英語', score,null)) as EN_SCORE 
from test_tb_grade t 
group by t.user_name 
order by t.user_name;
commit;
SELECT * FROM TEST_TB_GRADE2;

select user_name, 'CN_SCORE' COURSE, CN_SCORE as SCORE from test_tb_grade2
union
select user_name, 'MATH_SCORE' COURSE, MATH_SCORE as SCORE  from test_tb_grade2
union
select user_name, 'EN_SCORE' COURSE, EN_SCORE as SCORE  from test_tb_grade2
 order by user_name, COURSE;

DCL

• Data Control Language，數(shù)據(jù)控制語言：用于定義數(shù)據(jù)庫用戶的權(quán)限哪怔。DCL包括
  GRANT 授權(quán)
  REVOKE 回收權(quán)限
  deny 拒絕授予主體權(quán)限宣蔚。防止主體通過其組或角色成員身份繼承權(quán)限
• GRANT 賦于權(quán)限
  常用的系統(tǒng)權(quán)限集合有以下三個:
  CONNECT(基本的連接)=許多個權(quán)限, RESOURCE(程序開發(fā)), DBA(數(shù)據(jù)庫管理)
  常用的數(shù)據(jù)對象權(quán)限有以下五個:
  ALL ON 數(shù)據(jù)對象名, SELECT ON 數(shù)據(jù)對象名, UPDATE ON 數(shù)據(jù)對象名,
  DELETE ON 數(shù)據(jù)對象名, INSERT ON 數(shù)據(jù)對象名, ALTER ON 數(shù)據(jù)對象名
  操作
  GRANT CONNECT, RESOURCE TO 用戶名;
  GRANT SELECT ON 表名 TO 用戶名;
  GRANT SELECT, INSERT, DELETE ON表名 TO 用戶名1, 用戶名2;
• REVOKE 回收權(quán)限
  REVOKE CONNECT, RESOURCE FROM 用戶名;
  REVOKE SELECT ON 表名 FROM 用戶名;
  REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用戶名1, 用戶名2;
• 示例

    1向抢、創(chuàng)建用戶test2，密碼也是test2（記得最有以分胚委；號結(jié)束）：
        create user test2 identified by test2;
    2挟鸠、給test2授權(quán)：create session;（允許用戶登陸Oracle）：
        grant create session to test2;
    3、給test2分配創(chuàng)建表的權(quán)限亩冬；
        grant create table to test2;
    4艘希、給test2分配表空間的使用權(quán)限；
        grant unlimited tablespace to test2;
    5硅急、收回test2用戶的create session權(quán)限
       revoke create session  from test2;   
    6覆享、收回test2用戶的create table、unlimited tablespace權(quán)限
       revoke create table,unlimited tablespace  from test2; 

DDL

• Data Definition Language
  數(shù)據(jù)定義語言： 用于定義數(shù)據(jù)的結(jié)構(gòu)营袜，比如 創(chuàng)建撒顿、修改或者刪除數(shù)據(jù)庫對象。
  DDL包括：DDL語句可以用于創(chuàng)建用戶和重建數(shù)據(jù)庫對象荚板。下面是DDL命令：
  CREATE 創(chuàng)建
  ALTER 修改
  DROP TRUNCATE 刪除
• 創(chuàng)建表

create table t_user
(
      userid number(30),
      username varchar2(20),
      age number(3),
      sex varchar(2)
);    

• 刪除表
  drop table t_user;
• 修改字段長度
  alter table t_test modify username varchar2(10);
  desc t_test;

DML

• Data Manipulation Language
  數(shù)據(jù)操作語言：用于檢索或者修改數(shù)據(jù)凤壁。
  DML包括： SELECT：用于檢索數(shù)據(jù)；
  INSERT：用于增加數(shù)據(jù)到數(shù)據(jù)庫啸驯；
  UPDATE：用于從數(shù)據(jù)庫中修改現(xiàn)存的數(shù)據(jù)
  DELETE：用于從數(shù)據(jù)庫中刪除數(shù)據(jù)客扎。
  簡單示例
  insert into t_user (userid,username,age,sex) values (1,'老王',30,'男');
  commit;
  delete t_user;
  delete t_user where userid = 3;
  update t_user set username = 'aa',sex = '男';
  select···from···where···group by···having···order by···;

約束

(1)如果某個約束只作用于單獨的字段，既可以在字段級定義約束罚斗，也可以在表級定義約束徙鱼；但如果某個約束將作用于多個字段，必須在表級定義約束针姿。
(2)oracle中的約束通過名稱來進(jìn)行識別袱吆。在定義約束時可以通過constraint關(guān)鍵字為約束命名。

• 主鍵約束(primary key)

create table person
 (
       p_id int primary key,        --定義該字段為主鍵約束【字段級約束】
       p_name varchar2(20),
       p_age int
 );

create table person2
(
       p_id int constraint p_pk2 primary key,    --定義該字段為主鍵約束距淫，并指定約束名字【字段級約束】
       p_name varchar2(20),
       p_age int
);

create table person3
(
       p_id int,
       p_name varchar2(20),
       p_age int,
       constraint p_pk3 primary key (p_id,p_name)--定義復(fù)合主鍵,并指定名字【表級約束】
);

• 非空約束(not null)

create table person4
(
       p_id int,
       p_name varchar2(20) not null, --定義該列的非空約束
       p_age int 
 );

• 唯一約束(unique)

create table person2
(
       p_id int,
       p_name varchar2(20) constraint gulaijing unique not null, --同時定義唯一和非空約束
       p_age int
);

• 外鍵約束（foreign key）
  外鍵約束的特點：
  定義為外鍵約束的字段中只能包含相應(yīng)的其他表中引用字段的值或null值绞绒。
  可以為1個字段定義外鍵約束，也可以為多個字段的組合定義外鍵約束榕暇。
  定義了外鍵約束的字段和相應(yīng)的引用字段可以存在于同1個表中蓬衡，稱為自引用。
  對同1個字段可以同時定義外鍵和非空約束彤枢。
  主表中的被引用列狰晚，必須有主鍵約束或唯一約束。
  在定義外鍵約束時缴啡，還可以通過on關(guān)鍵字來指定引用行為的類型壁晒。當(dāng)主表中的一條記錄被刪除時，需要通過引用行為來確定如何處理子表中的外鍵列的值业栅。
  delete cascade : 指刪除父表記錄時刪除子表中所有的相關(guān)記錄
  delete set null :指刪除父表記錄時 將所有相關(guān)記錄的外部碼字段值設(shè)置為NULL
  delete no action: 指刪除父表記錄時 不做任何操作(默認(rèn)情況)
• 檢查約束（check）

create table person
(
       p_id int,
       p_name varchar2(20),
       p_age int check(p_age > 20)  
);