--基礎:select * from area select * from cgfpmxb/*可以包含任意運算式*//*指定別名可以直接寫 也可以 as aa *//*將兩個欄位合并成一個欄位的方法 用||*//*可以在語句中加一個字串 用單引號括起來*/select itemid || itemname || amount || 'hello'taxamount,amount*taxratio/10.00 "aa" from cgfpmxb /*null的使用*/ select * from djzhbwhere ca is not null/*在計算運算式的時候 只要運算式中有null 整個結果都是NULL*/select amount , amount+null from cgfpmxb/*為防止運算式因出現空值而 影響運算 可以為空白值指定一個值,當在計算運算式的時候,如果遇到空值,就會用指定的數值替代*/select amount,amount+nvl(null,0) from cgfpmxb/*nvl在日期中的處理,很重要*//*如何將字元日期,轉換成日期格式*/select nvl(checkdate, to_date('1900-01-01','yyyy-mm-dd')) from djzhb where(ca is null)/*擷取系統時間函數*/select sysdate --可以將查詢結果重複記錄消滅掉,只要我們選取的兩列相同,就被視作重複select distinct distcode ,distname from djzhb/*擷取前兩行資料*//*rownum式系統函數,可以為表產生一個id列,從而在where中指定id來判斷行數*/select * from (select rownum id, djzhb. * from djzhb) where id<3 /*排序*//* 升序:asc 降序:desc*//*可以在排序指定別名*/ select distcode code,distname from djzhb order by distcode asc select distcode code,distname ,ca from djzhb order by ca desc /*可以指定多列來排序,*//* distinct可以去掉重複,你選幾列就按照幾列來判斷是否相同,只有選出的列的欄位值都相同才被視為相同*/ select distinct distcode code,distname ,ca from djzhb order by distcode,distname desc -- 一般對一列使用distinct,,在MSSQL中直接就會按照一列進行排序 select distinct distcode from djzhb order by distcode /*where用法*/ select distinct distcode,distname,ordertype from djzhb where distcode='63345' /*where處理日期*/ select distcode , distname,ordertype, uploaddate from djzhb where uplorddate= to_date('2006-09-25','yyyy-mm-dd') /* where可以使用<> <= >= 等符號*/ select * from area where di >=4 /* 可以使用between和in*/ select * from area where id between 1 and 4 /* 離散的數字 和SQL一樣*/ select * from area where id in (1,4,5) /*模糊查詢*/-- %表示任一字元 select * from province where name_c like '%海' /*表示尋找最後一個字式海的 */ /* _表示單個字元*/ select * from province where name_c like '北_'/* 轉意字元,必須自己定義 如果欄位中含有 %這個符號 ,要尋找 含有這個符號的那行,就必須使用轉意符號,*/ select * from area where name_en like '%\%' escape '\' /*可以指定任意的轉義符號*/ select * from area where name_en like '%+%' escape '+'/* 指定 +為轉意符號,表示查詢以%結束的欄位 */ -- 可以使用 and 或 or 來 串連 多個查詢條件 select * from area where id< 4 or name_cn like '%華' /*函數*//* 1*/ /*字串函數:*/-- lower -- upper-- initcap-- concat-- substr-- length-- nvl /*轉換成 小寫 大寫 substr取出某欄位值,從第一個字元開始,連續取出10個字元 取出字串的長度 判斷其是否包含GUIDE 如果包含就返回第一次找到的在欄位中的起始位置 將兩個字串合并成一個新的字串*/ select item_number, lower(name_e), initcap(name_e), substr(name_e,1,5), length(name_e), instr(name_e,'GUIDE') concat(name_e,name_c) from item_master_tb /* 2*/ /*數值型函數 round 小數點後取兩位,最後一位根據四捨五入來確定大小(如果式負數 會影響到整數部分) trunc 取小數點後指定的位元,把小數點後面的直接截掉,不會四捨五入*/ /*dual 是假表*/ select round(43.345,2), round(43.345,1), round(43.345,-1), trunc(45.546,2), trunc(45.3455,-1) from dual /*3*//* 時間函數 sysdate擷取系統時間*/ select sysdate from dual select sysdate+600 from dual /*預設加的式天數*/ /* 連個時間的相減 表示加六個月 擷取下個星期的第一天式幾號 當前月的最後一天,很重要,可以在編程的時候用到 按照月份四捨五入 只顯示到月份 按照天 四捨五入 只顯示到天 時間就被捨棄了 按照年 四捨五入 只顯示年 year表示對年四捨五入 dd表示明天*/ select months_between( to_date('2010-01-01','yyyy-mm-dd'),sysdate), add_months(sysdate,6), next_day(sysdate,1), last_day(sysdate), last_day(to_date('2008-02-11','yyyy-mm-dd')), round(sysdate,'MONTH'), round(sysdate,'day'), round(sysdate,'year'), round(sysdate,'dd'), from dual /* 4 時間的轉換 YYYY年 英文表示 YEAR 數字表示 MM 月 英文表示 MONTH月 英文 DY星期 中文 DD星期 英文 DAY日期 MI 分鐘 SS 秒 HH24 24小時時間表示 HH預設的小時表示*/ select to_char(sysdate,'YEAR-MONTH-DAY'), tO_char(sysdate,'YEAR-MONTH-DY'), tO_char(sysdate,'YEAR-MONTH-ddspth'), to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual /* 5 將數字轉換成字串 第二個參數 轉換的格式 9表示0-9的任意一個數字,超過就無法顯示, 0表示佔位,如果不夠用0補齊 1顯示1234 2第二個數位位元超過格式規定的位元,所以無法顯示 3少於規定的格式兩個 ,所以只顯示12 4 0表示佔位 5 轉換小數,要在小數點前後定義格式 6 L表示轉換成 人民幣 $表示美元 */ select to_char(1234,'9999'), to_char(1245677,'9999'), to_char(12,'9999'), to_char(234,'0000'), to_char(23.234,'99.0000'), to_char(334455.33,'L999,999,999.00') from dual /* 6 to_date函數 將字串轉換成日期 前面已經使用*/ select to_date('10 9月 1992','dd Month yyyy') from dual /* 7 to_number函數 將字串轉換成數字格式, 字串最好式數字*/ select to_number('123') from dual /* 8 函數可以嵌套*/ select to_char( next_day( add_months(sysdate,6) ,1) ,'DAY,MONTH DDTH,YYYY' from dual /* 多表查詢*/ select * from city /* 外串連*/ select c.city_no,p.name_c pname,c.name_c cname from city c inner join province p on c.province_no=p.province_no selcet * from item_master_tb /* 內串連*/ select a.item_number,a.description,d.department_name from item_master_tb a inner join dept_master d on a.franchise=d.department_code select count(*) from item_master_tb where itemgroup6 is null select count(*) from item_master_tb group by group_code having count(*)>1 where itemgroup6 is null /*左串連*/ /*資料要區分大小寫*/ /* 和sql語言一樣的*/ select a.item_number,a.description,g.description from item_master_tb a left join itemgroup_master g on a.itemgroup6 =g.group_code where g.group_type='S6' t /*另一種寫法:*//* 要能看懂 +放在右邊是左串連 放在左邊是右串連*/ select a.item_number,a.description,g.description from item_master_tb a , itemgroup_master g where a.itemgroup6=g.group_code(+) and g.group_type= 'S6' select *from itemgroup_master select s7.group_code, s7.description,s6.description from itemgroup_master s7 inner join itemgroup_master s6 on s7.parent_code=s6.group_code where s7.parent_code is not null /* 分組查詢 重要 select group by having order by count(*)計算所有的記錄的行數 count(ca) 計算某個欄位的行數 只有出現在group by 後面的才能出現在select,oreder by中,這是規定 having 和 order by 是可選的 having 用於進一步過濾條件,就是對分組好之後 再進一步篩選 分組查詢經常用到的函數:*/ avg sum min max count() /* count()函數*/ select count(*) total ,count(ca) from djzhb select * from item_master_tb /* 一般都是先分組再計算平均 或 總值 */select franchise , avg(dist_price), max(dist_price), min(dist_price), count(item_number) from item_master_tb group by franchise having avg(dist_price)>1000 order by franchise /* 子查詢 超級重要/* 先執行嵌套在裡面的 裡面的先把資料進行第一步篩選 外層 以 裡層為 基礎 進行 查詢*/ 單行資料 *//*直接用 '='*/ select item_number, last_updated_time from item_master_tb where last_updated_time = (select max(last_updated_time) from item_master_tb) /* 多行資料 要用 in 也就說說 嵌套在裡面的查詢會返回多條記錄 這種情況下要用in */ select item_number, franchise from item_master_tb where franchise in ( select deparment_code from dept_master where department_type=0) create user wangshukui identified by "wangshukui" profile DEFAULT;grant connect to wangshukui with admin option;grant dba to wangshukui with admin option;grant resource to wangshukui with admin option;grant unlimited tablespace to wangshukui with admin option;select * from customersselect * from sales/*串連三個表*/select s.productid,s.customerid,s.saleprice,p.productname,c.firstname,c.lastname,s.saledatefrom sales s inner join customers con c.customerid=s.customeridinner join products pon p.productid=s.productidwhere s.saledate >= to_date('2005-10-01','yyyy-mm-dd') and s.saledate <= to_date('2005-10-30','yyyy-mm-dd') select sales s select s.customerid from sales s group by s.customerid having count (customerid)>=2 /* 必須要學好 uml 與 設計模式 重點中的重點 把自己心裡想說的話 用工具來表達 做成功的軟體開發 充分調動思維 資料庫設計概念 建立表*/ /* 刪除表*/ drop table S_dept /* 建立*/ create table S_dept ( /*定義主鍵*/ id number(7) constraint s_dept_pk primary key, /*定義約束,式針對列的*/ /*not null 約束*/ name varchar2(25) constraint s_dept_name_nn not null, region_id number(7), /* 下面的約束式表級的,不是在列上,是定義好之後 在作用到表的列上,可以作用多個列*/ constraint s_dept_name_region_id_uk unique (name,region_id) /*定義表級的約束*/ ); create table s_emp ( id number(7) primary key, last_name varchar(25) not null, first_name varchar(25), /* 兩個約束 not null 也是約束*/ userid varchar2(8) constraint s_emp_userid_nn not null constraint s_emp_userid_uk unique, /*預設約束,會直接使用系統預設的時間*/ start_date date default sysdate, comments varchar2(25), manager_id number(7), title varchar2(25), /*參考條件約束*/ dept_id number(7) constraint s_emp_dept_id_fk references S_dept(id), salary number(11,2), /*check約束*/ commission number(4,2) constraint s_emp_commission_ck check(commmision in(10,12.5,15,20)) ); /*拷貝另一個使用者的表*/ /*只能拷貝資料,表的主鍵資訊沒有*/ create table distributor_mastor as select * from interface.distributor_master select* from distributor_mastor /* 在已有的表上面添加約束*/ add constraint distributor_masterpk primary key (dist_id) /* oracle中的約束: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY check*/ /* default不是約束 修改一個表的列的預設值的方法:*/ alter table s_emp /*modify 是修改列的*/ modify start_date default sysdate /* 第九章 資料字典 瞭解 系統資料表 User tables*/ select distinct object_type from user_objects select * from user_objects /* 查詢系統中的所有對象名字*/ select object_name from user_objects select object_name from user_objects where object_type='TABLE' /* 使用者表*/ Select * from user_tables select constraint_name,constraint_type,search_condition, r_constrinte_name from user_constraints where table_name='s_emp' /* 操縱資料 insert update delete commit savepoint rellback*/ /* 建表 只有批處理的時候需要分號,一般處理可以不用分號*/ create table lesson_test ( std_id int not null, msg_id int not null, msg_text varchar2(200) not null, updatetime date default sysdate, primary key (std_id,msg_id) )/* 插入值:*/ insert into lesson_test values(48,1,'my first message',sysdate) select * from lesson_test /* 指定null 可以直接寫出來*/ insert into lesson_test values(48,2,'myu',null) /* 向表中插入部分段的資料 沒有指定則用預設值來代替*/ insert into lesson_test(std_id, msg_id ,msg_text ) values(48,3,'myu') insert into lesson_test values(48,2,'myu',null,sysdate)/* 插入日期,通過轉換 轉換成日期*/ insert into lesson_test values(48,4,'dfdf', to_date('2008-10-10','yyyy-mm-dd')) select * from lesson_test /* 把一個表中的資料全部提取出來賦給另一個表, 第二個表必須要和第一個表的欄位的類型一樣*/ create table lesson_test_history ( std_id int not null, msg_id int not null, msg_text varchar2(200) not null, updatetime date default sysdate, primary key (std_id,msg_id) ) /* 複製語句:*/ insert into lesson_test_history select * from lesson_test select * from lesson_test_history /* 修改資料 update set*/ /* ||相當於 c sharp 中的 + 運算子*/ update lesson_test set msg_text=msg_text|| 'Modified' where std_id=48 and msg_id=4 select * from lesson_test /* 刪除資料 刪除表中的相關資料*/ delete from /* 可以通過where指定要輸出的行*/ delete from lesson_test where updatetime< to_date('2008-05-01','yyyy-mm-dd') /* 事務 先刪除表,此時資料為空白*/ delete from lesson_test/* 對這個表設定一個復原點*/ savepoint A; /* 插入一條記錄*/ insert into lesson_test(std_id,msg_id,msg_text,updatetime) values(48,6,'dddddd',to_date('2008-04-04','yyyy-mm-dd')) select * from lesson_test/* 復原到A點 A點是沒有資料的*/ rollback to A /* 查看發現復原到A的時候表的資訊*/ select * from lesson_test /*再次復原,會把上面一條語句的資訊 復原 上面是 delete 語句 */ rollback/* 查詢後發現 以前的資料又找回來了 */ select * from lesson_test /* commit 會把資料提交給表 真正的修改表,執行查詢之後 只有點擊commit 才能真正的改變表中的資料 因為所有的資料 都會 利用日誌 來 儲存我們的任何操作,只有 點擊上面的commit 才真正寫入到表中 我們刪除表中資料的時候 其實是在虛擬表中操作的 ,不會影響真正的表,只有點擊上面的commit按鈕 才會寫入到表中*/ insert into lesson_test(std_id,msg_id,msg_text,updatetime) values(48,5,'dddddd',to_date('2008-04-04','yyyy-mm-dd')) commit /* 是提交上面的操作 資料提交後 前面定義的 復原點都會被刪除*/ select * from lesson_test order by msg_id /* 修改表*/ /* 前面是怎麼建立表的 現在是對已經存在的表 修改 修改列 約束 增加列 約束 刪除列 約束 刪除表*/ alter table /*表*/ add modify drop /*列*/ enabled /* 添加列*/ select * from lesson_test alter table lesson_test add comments varchar(255) null;/*添加列*/ /* 刪除列*/ drop column comments /* 修改列 只能向大的方向修改 例如 原來列的欄位式100個字串 類型 ,只能向大的方向修改 例如200*/ alter table lesson_test modify msg_text varchar2(200) select * from lesson_test /* 主鍵 外鍵*/ drop table lesson_test create table lesson_test ( std_id int not null, msg_id int not null, msg_text varchar2(200) not null, updatetime date default sysdate, primary key (std_id) ) create table lesson_test_type ( msg_type int not null, msg_typename varchar(200), primary key (msg_type) ) insert into lesson_test_type values(2,'dfdff') select * from lesson_test_type /* 添加外鍵*/ alter table lesson_test add constraint s_lesson_text_id_fk foreign key (msg_type) references lesson_test_type (msg_type) /* 禁用約束*/ alter table lesson_test disable constraint s_lesson_text_id_fk /* 啟用約束*/ alter table lesson_test enable constraint s_lesson_text_id_fk /* 刪除表,會級聯 把一切的約束 以及與表有關的都串聯刪除*/ drop table lesson_test cascade constraints /* 修改表名*/ rename lesson_test to lessonabc select* from lessonabc delete table lessonabc rollback/* 不會真正刪除,復原會回到原資料,式刪除暫存資料表*/ truncate table lessonabc/* 真正刪除資料,刪除的式實際表中資料*/ /* sequences 專門用於產生一個不重複的數字序列 用於使得列唯一 */ create table dept(id int not null,name varchar2(200),primary key (id))insert into dept values(wangshukui_test_sql.nextval,'test')select * from dept /* 建立sequence的語句:*/ -- Create sequence create sequence WANGSHUKUI_TEST_SQLminvalue 1maxvalue 9999999start with 201increment by 1cache 200cycle;--練習:自己建立一個sequencecreate sequence sq_first1increment by 1start with 6534maxvalue 34356667777888cache 200cycle; insert into dept values(sq_first1.nextval,'test')select * from dept/*視圖:好處:可以防止 直接存取 資料庫, 可以保護資料 只讓員工訪問基礎層級的資料 把其封裝在試圖中*//*簡單試圖:如果涉及的表 沒有 函數 只有一張表 等複雜的內容 複雜試圖 : 多表 還有函數 等 多種資訊*//*建立視圖*/create or replace view vw_lwsson_testas select std_id,msg_id,msg_text from lessonabc with read only --表示建立唯讀視圖 select * from vw_lwsson_testselect * from lessonabcinsert into lessonabc values(1,2,'dff',sysdate)--修改視圖update vw_lwsson_testset msg_text=msg_text || 'mod by view' where std_id=48 and msg_id=1select * from vw_lwsson_test--複雜視圖--有多個表create or replace view vw_lesson_test2 asselect rownum no,l.std_id,l.msg_id,l.msg_text,t.msg_typenamefrom lesson_test_history l,lesson_test_type twhere l.msg_id= t.msg_type--指定別名 :--也可以看做一個視圖--如果其他使用者的某個表經常使用 可以給其指定一個簡單的名稱 關鍵字 synonymcreate synonym province for interface.province select * from province select * from lessonabc --當然也可以給目前使用者的某個表在起一個名字 --查詢這兩個名字都可以得到這個表的資訊 create synonym lesson for lessonabc select * from lesson select * from lessonabc /* 嘿嘿 擷取別的使用者的表*/ select * from liushuai.lesson_test_type /* 索引 就是資料結構裡面的B+ 型樹狀結構 index 通過索引查詢資料 可以縮短時間 有些索引 系統自動建立*/ automatically unique non-unique single column concatenated /* 為某個表的一列建立索引*/ create index temp_lesson_test on lesson_test_type(msg_type)/* 刪除索引*/ drop index temp_lesson_test /* 建立索引的原則 : 一般為哪些列建立索引: 哪些列需要建立索引 如果一列經常出現在where條件裡面, 如果一列的取值範圍很大 如果一列的大部分資料為NULL 因為:索引會把有資料的值 集中一起, 這樣就會先查詢有資料的 如果兩個或者多個列 經常組合出現在 where條件中 可以將其組合 為其建立索引 如果表的資料很龐大*/ /* 什麼情況下不用建立索引: 如果 表很小 如果一列不經常使用 如果每次查詢 返回的行數 少於總行數的2-4% 也不用建立索引 如果一個表需要經常更新 也不用建立索引 */
