Oracle 資料庫常用sql語句及知識

標籤：負數   sysdba   date   關鍵字   upper   平均數   ons   外鍵   number   

《Oracle 資料庫》一、常用sql語句：1.串連系統管理員帳號：conn system/system(安裝時口令);2.建立新帳號：create user Leo(帳號名) identified by Leo(密碼)；3.給新帳號授權：grant connect,resource to Leo;4.從某個賬戶收權：revoke resource from Leo; 5.串連新帳號：conn Leo/Leo;6.顯示當前客戶：show user;7.查看錶結構：desc s_emp(表名);8.無需密碼以dba身份登陸：conn / as sysdba;  //為所欲為的方式，危險9.增刪改查基本文法： 增：insert into e_product(id,name,price) values(10001,‘紅米手機‘,599); 刪：delete from e_product where id = 10001; 改：update e_product set name=‘小米電視‘,price = 2999 where id=10001; 查：select id,name,price from e_product where id = 10001; 二、資料庫函數 1.NVL(commission,0)；這個函數可以將一個欄位（例：commission）中的null值替換成0，以便於運算。例如計算提成的時候，有些員工沒有提成。案例：列出每個員工的一年的總收入？(NVL)select (NVL(commission_pct,0)/100+1)*12*salary from s_emp; 2.to_char(sysdate,‘yy‘):以自訂格式顯示日期；案例：找出入職時間是 90年的所有員工資訊？select first_name,start_date,to_char(start_date, ‘yy‘)from s_emp where to_char(start_date, ‘yy‘)=90; 3.lower(欄位)/upper(欄位): 將某個欄位的值變成全小寫/全大寫，以匹配未知大小寫字元；案例：當不知道‘Carmen’在資料庫是大小寫時，找出‘Carmen’的工資？select first_name ,salary from s_emp where lower(first_name) = ‘carmen‘; 4.substr(attribute，indexFrom,length):截取屬性值為字串的部分字元，從indexFrom開始，截取長度為length。indexFrom為負表示倒數第幾個。案例：列出每個員工名字（last_name）的最後兩個字元？select id,substr(last_name, -2, 2) from s_emp; 5.round(attribute,index): 求出某個屬性的近似值， 四捨五入第index位。index為負數的時候表示小數點向左第幾位，整數向右第幾位。案例：列出員工的工資的近似值，精確到十位？select id,first_name, round(salary,-1) from s_emp;  6.trunc(attribute,index);求出某個屬性的近似值，去掉index位及之後的零頭。index為負數的時候表示小數點向左第幾位，整數向右第幾位。案例：列出員工工資的近似值，去掉100元以下的零頭？select id，first_name,trunc(salary,-2) from s_emp; 7.sysdate:擷取當前系統時間。當前系統時間的格式收到本地化的嚴格影響，因此想要擷取自己需要的格式必須通過tochar(sysdate,‘yy-mm-dd hh:mi:ss‘)獲得；案例：查出下一天、下一分鐘、下一秒的時間select to_char(sysdate,‘yy-mon-dd hh24:mi:ss‘) as now, to_char(sysdate+1,‘yy-mon-dd hh24:mi:ss‘) as nextday, to_char(1/(24*60)+sysdate ,‘yy-mon-dd hh24:mi:ss‘) as nextmin,to_char(1/(24*60*60)+sysdate,‘yy-mon-dd hh24:mi:ss‘)as nextSec from dual; 8.day:在日期中表示星期幾。案例：求某(今)天是星期幾select to_char(sysdate,‘day‘) from dual; 9.to_date(‘2015-11-09‘,‘yyyy-mm-dd‘):將字串格式的日期轉換成日期。 10.last_day(sysdate):獲得給定日期的當月的最後一天的日期。9和10案例：找出今年的天數select  last_day(to_date(‘1215‘,‘mm/yy‘))+1 - to_date(‘010115‘,‘dd/mm/yy‘) from dual; 11.avg(attribute):某個欄位值得平均數。案例：列出提成的平均值？select avg(commission_pct) from s_emp; 12.count(attribute) : 某個欄位記錄的個數；案例：求出有提成的員工個數？select count(*) from s_emp where commission_pct is not null; 13.group by :按照...分組。by後面可以跟多個欄位，即按照多個欄位分組。案例1：求出各部門的平均工資？（單個欄位）select dept_id,avg(salary) from s_emp group by dept_id;案例2：求出各部門不同職位的平均工資？（多個欄位）select dept_id,title,avg(salary) from s_emp group by dept_id,title; 14.having：過濾掉分組以後不想要的結果。案例：求出各部門不同職位的平均工資且只顯示平均工資高於2000的結果？select dept_id,title,avg(salary) from s_emp group by dept_id,title having avg(salary)>=2000; 三、資料庫關鍵字 1.distinct ：寫在select中欄位前面用於去重；案例：select distinct name from s_dept; 2.between ... and ... : 某欄位的值在一個閉區間【】之間；案例：select * from s_emp where salary between 1500 and 2000; 3.in:某欄位的值在一個枚舉範圍之中；案例：select first_name,salary,dept_id from s_emp where dept_id in(31,42,43); 4.like：模糊比對字串，與_和%連用；案例：select * from s_emp where first_name like ‘_e%‘; 5.escape：字串中有些符號具有特殊含義，如底線_表示模糊比對中單個字元，但是如果僅想表示底線則需要使用escape設定逸出字元。案例：select table_name from dba_tables where table_name like‘[email protected]_%‘ escape ‘@‘; 6. is null:獲得某個欄位的值是空的記錄；案例：列出哪些員工沒有提成？select id,first_name from s_emp where commission_pct is null; 7.desc:按照某一欄位降序排列，與order by連用。案例：按工資降序顯示員工的資訊？select * from s_emp order by salary desc; 8.asc:按照某一欄位升序排列，與order by 連用。案例：按提成升序顯示員工的資訊？select * from s_emp order by commission_pct asc;注意：null值按照“無窮大”位置排列； 9.order by ：排序。by後面可並列多個欄位，優先順序按照先後順序。案例：先工資降序，再按提成升序顯示員工？select * from s_emp order by salary desc,commission_pct asc;  四、多表查詢1.join...on...:將兩個表連起來。on後面是兩個表的關聯條件。案例：列出員工名字和部門名字？方法1.select first_name||‘ ‘||last_name , name from s_emp e,s_dept d where e.dept_id = d.id ;方法2.select e.first_name||‘ ‘||e.last_name ,d.name from s_emp e join  s_dept d on e.dept_id = d.id;注釋：Oracle中|| 表示加號，‘‘表示字串。 2.三表關聯查詢（兩種方法）案例：列出‘Carmen’在哪個地區上班？--方法一：（Oracle、sqlserver文法）select r.name  from s_emp e,s_region r,s_dept dwhere e.dept_id = d.id and d.region_id = r.id and e.first_name = ‘Carmen‘;--方法二：（資料庫通用文法）select r.namefrom s_emp e join s_dept d on e.dept_id = d.id join s_region r on d.region_id = r.idwhere e.first_name= ‘Carmen‘; 五、DDL資料定義語言 (Data Definition Language)1.建立表：CREATE TABLE E_CATEGORY (  ID NUMBER(12, 0) NOT NULL , T_CATEGORY VARCHAR2(25) , CONSTRAINT TABLE1_PK PRIMARY KEY   (    ID   )  ENABLE ); 2.向表中插入資料INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘1‘, ‘智能手機‘)INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘2‘, ‘數位相機‘)INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘3‘, ‘膝上型電腦‘)INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘4‘, ‘襯衫‘)INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘5‘, ‘外套‘)INSERT INTO "SYSTEM"."E_CATEGORY" (ID, T_CATEGORY) VALUES (‘6‘, ‘休閑褲‘)  3.修改表結構：添加一個欄位；alter table e_category add bigcate varchar2(25); 4.修改表結構：添加一個外鍵；alter table e_product add constraint fk_product_bigcate  foreign key(p_category) references e_bigcate(id); 5.修改表結構：修改一個欄位：alter table e_product modify  p_cdate timestamp(8); 6.修改表結構：修改欄位名；alter table e_product rename column p_name to name; 7.修改表結構：刪除外鍵：alter table e_product drop constraint fk_product_category;8.修改表結構：修改外鍵：思路：先刪除後添加 視圖：create view 視圖名 as select ...  序列（sequence）：min:max:increm://sequenc:currval:nextval; index:索引：1.加快檢索速度  資料庫的邏輯結構：資料庫-表-段-區-塊； 鎖：for update---行級鎖  當關閉串連或提交的時候釋放鎖； 

Oracle 資料庫常用sql語句及知識