如需要電子書，聯(lián)系我。
- oracle只顯示前幾行
select prod_name from products where rownum <=5 ;
表的一個默認(rèn)字段
第一課創(chuàng)建表
字段后有空格许饿，數(shù)據(jù)類型需要空格來補(bǔ)全，空格是有函數(shù)可以去掉的宽档，第8課有
select * from Customers ;
select * from OrderItems ;
select * from Orders ;
select * from Products ;
select * from Vendors ;
第二課rownum
select prod_id,prod_name,prod_price from products ;
select distinct vend_id from products;
select * from products ;
select distinct vend_id,prod_name from products;
select prod_name from products where rownum <=5 ;
第三課order by
order by 子句是一個select語句最后一個子句
order by的列可以是非檢索的列
select prod_name from products order by prod_name ;
select prod_name from products order by vend_id ;
select prod_id,prod_price,prod_name from products order by prod_price,prod_name;
select prod_id,prod_price,prod_name from products order by 2,3;
select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;
第四課where
where子句用的符號還有between\is null
將值與字符串進(jìn)行比較需要單引號图谷，與數(shù)值比較則不需要單引號
between 5 and 10 條碼需要兩個值，開始值與結(jié)束值逐哈，包括開始值與結(jié)束值
is null條碼判斷值是否為空
select prod_name,prod_price from products where prod_price = 3.49;
select prod_name,prod_price from products where prod_price < 10 ;
select prod_name,prod_price from products where prod_price <= 10 ;
select vend_id,prod_name from products where vend_id <> 'DLL01';
select prod_price,prod_name from products where prod_price between 5 and 10 ;
select cust_name from customers where cust_email is not null;
第五課高級數(shù)據(jù)過濾
or操作符只要前面的滿足后面的就不執(zhí)行了
有or又有and芬迄，and的優(yōu)先級更高，大多時候會借助括號解決
not用于否定后面的列
select prod_id,prod_price,prod_name from products where vend_id='DLL01' and prod_price <=4 ;
select prod_id,prod_price,prod_name from products where vend_id='DLL01' or vend_id='BRS01' ;
select prod_id,prod_price,prod_name from products where vend_id='DLL01' or vend_id='BRS01' and prod_price >= 10 ;
select prod_id,prod_price,prod_name from products where (vend_id='DLL01' or vend_id='BRS01') and prod_price >= 10 ;
select prod_name,prod_price from products where vend_id in ('DLL01','BRS01') order by prod_name ;
select prod_name,prod_price from products where vend_id='DLL01' or vend_id='BRS01' order by prod_name ;
select prod_name from products where not vend_id = 'DLL01' order by prod_name;
select prod_name from products where vend_id <> 'DLL01' order by prod_name ;
第六課like與通配符
like是謂詞
%不能匹配null
通配符使用起來很慢昂秃，應(yīng)該放在最后
select prod_id,prod_name from products where prod_name like 'Fish%';
select prod_id,prod_name from products where prod_name like '%bean bag%';
select prod_id,prod_name from products where prod_name like 'F%y%';/*后面有空格*/
select prod_id,prod_name from products where prod_name like '__ inch teddy bear%';
/*只有微軟件的數(shù)據(jù)庫才能用禀梳，可以在中括號里加^號來否定，其他的只能用not了*/
select cust_contact from customers where cust_contact like '[JM]%' order by cust_contact ;
select prod_id,prod_name from products where not prod_name like 'F%';
第七課計算字段
oracle用||拼接兩個列
rtrim()函數(shù)用于去除右邊空格
ltrim()函數(shù)用于去除左邊空格
trim()函數(shù)用于去除兩邊的空格
計算列肠骆，對列進(jìn)行計算算途，創(chuàng)建是新的列，可以重命名
select 可以用于運(yùn)算蚀腿，但是在這里不行
select rtrim(vend_name) || '(' || rtrim(vend_country) || ')' from vendors order by vend_name ;
select rtrim(vend_name) || '(' || rtrim(vend_country) || ')' as vend_title from vendors order by vend_name ;
select prod_id,quantity,item_price from orderitems where order_num='20008';
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num='20008';
第八課使用函數(shù)處理數(shù)據(jù)
upper()將小寫字母轉(zhuǎn)換成大寫字母
lower()將大寫字母轉(zhuǎn)換成小寫字母
rtrim()除去右邊空格
ltrim()除去左邊空格
length()字符串的長度
soundex()找出列類似的讀音
處理日期oracle沒有datepart()函數(shù)嘴瓤，只能用其他的了
to_char()函數(shù)取日期的部分
to_number()函數(shù)將取出的字符串類型日期轉(zhuǎn)換為數(shù)字類型
借助between and ，to_date()函數(shù)可以將字符串轉(zhuǎn)換成日期類型
還有比較多的數(shù)值處理函數(shù)
select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name ;
select vend_name, length(rtrim(vend_name)) as vend_name_length from vendors order by vend_name ;
select upper(vend_name) as vend_name_upcase,lower(upper(vend_name)) as vend_name_lower from vendors order by vend_name ;
select cust_contact from customers where soundex(cust_contact) = soundex('John Smith1');
select cust_name, cust_contact from customers where soundex(cust_contact) = soundex('Michael Green');
select order_num "訂單號",to_char(order_date,'MM')||'月'||to_char(order_date,'dd')||'日' as "日期" from orders where to_number(to_char(order_date,'YYYY')) = 2012 ;
select order_num from orders where order_date between to_date('2012/01/01','yyyy/MM/dd') and to_date('2012/12/31','yyyy/MM/dd') ;
第九課聚集函數(shù)
avg()返回某列的平均值
count()返回某列的行數(shù),參數(shù)為*\1則返回所有列莉钙，參數(shù)為某列則返回某列的非null值的個數(shù)
max()返回某列最大值,忽略null
min()返回某列最小值,忽略null
sum()返回某列值之和,忽略null
distinct參數(shù)用于指定不同的值,distinct后面應(yīng)該跟的是列名
select avg(prod_price) avg_price,count(*) num_items,max(prod_price) max_price,min(prod_price) min_price,sum(prod_price) total_price from products ;
select count(*) num_items,count(cust_email) email_items from customers ;
select sum(quantity) from orderitems where order_num='20005';
select sum(quantity*item_price) as total_price from orderitems where order_num='20005';
select avg(distinct prod_price) as avg_pric from products where vend_id='DLL01';
第十課分組函數(shù)廓脆，group by\having
分組后使用聚集函數(shù)
group by可以對多個列進(jìn)行分組，select中使用了非計算列磁玉，都需要在group by中出現(xiàn)
oracle允許使用位置來指定分組
having與where使用相同狞贱，作用不同，where是作用在所有的行蜀涨，having是作用在分組內(nèi)
另一種說法瞎嬉，where是在分組前進(jìn)行過濾，group by是在分組后進(jìn)行過濾
使用group by時也一定要使用order by才能確認(rèn)順序
select vend_id,count(*) as num_prods from products group by vend_id ;
select prod_id,prod_name from products order by 1,2 ;
select * from orders
select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;
select vend_id,count(*) as num_prods from products where prod_price>=4 group by vend_id having count(*)>=2;
select order_num,count(*) as items from orderitems group by order_num having count(8)>=3 order by items,order_num;
第11課使用子查詢
子查詢總是從內(nèi)向外進(jìn)行處理
計算字段是子查詢語句的時候
下面使用的是完全限定列名厚柳，由表名與列名組合而成氧枣，意思是比較orders表中的cust_id與當(dāng)前正從customers表中檢索的cust_id
子查詢常用于in操作符與填充計算列中
購買RGAN01的顧客的信息
select order_num from orderitems where prod_id='RGAN01';
select cust_id from orders where order_num in ('20007','20008') ;
select cust_id from orders where order_num in (select order_num from orderitems where prod_id='RGAN01');
select cust_name,cust_contact from customers where cust_id in ('1000000004','1000000005');
select cust_name,cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id='RGAN01'));
/*對一個顧客執(zhí)行訂單數(shù)目及對全部顧客執(zhí)行訂單數(shù)目*/
select count(*) as orders from orders where cust_id='1000000001';
select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders from customers order by cust_name ;
(重要)第12課聯(lián)結(jié)表join
關(guān)系數(shù)據(jù)庫的設(shè)計：一類數(shù)據(jù)一個表
用from聯(lián)結(jié)兩表的時候，如果沒有where則將第一個表的每一行與第二個表的每一行進(jìn)行聯(lián)結(jié)（叉聯(lián)結(jié)\笛卡兒積）
等值聯(lián)結(jié)\內(nèi)聯(lián)結(jié)
select vend_name,prod_name,prod_price from products,vendors where vendors.vend_id=products.vend_id ;
/*內(nèi)聯(lián)結(jié)别垮，指明聯(lián)結(jié)的類型*/
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id ;
select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id=vendors.vend_id and products.prod_id=orderitems.prod_id and orderitems.order_num='20007';
/*只需要用到多個表的數(shù)據(jù)都可以用聯(lián)結(jié)查詢*/
select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and prod_id='RGAN01';
select cust_name,cust_contact from customers inner join orders on customers.cust_id=orders.cust_id inner join orderitems on orders.order_num=orderitems.order_num where orderitems.prod_id='RGAN01';
第13課創(chuàng)建高級聯(lián)結(jié)
自聯(lián)結(jié)便监、自然聯(lián)結(jié)inner join、外聯(lián)結(jié)left outer join\right outer join
全外聯(lián)結(jié)full outer join
別名的另一個作用是自聯(lián)結(jié)
可以多個表聯(lián)結(jié)，況且聯(lián)結(jié)時可以使用多種不同的聯(lián)結(jié)
聯(lián)結(jié)時一定要使用正確的聯(lián)結(jié)條件
select rtrim(vend_name)||'('||rtrim(vend_country)||')' as vend_title from vendors order by vend_name ;
select cust_name,cust_contact from customers C ,orders O,orderitems OI where C.cust_id=O.cust_id and O.order_num=OI.order_num and OI.prod_id='RGAN01';
/*自聯(lián)結(jié)與子查詢對比烧董，自聯(lián)結(jié)會更快*/
select cust_id,cust_name,cust_contact,cust_email from customers where cust_name = (select cust_name from customers where cust_contact='Jim Jones');
select C1.cust_id,C1.cust_name,C1.cust_contact,C1.cust_email from customers C1,Customers C2 where C1.cust_name=C2.cust_name and C2.cust_contact='Jim Jones' ;
/*自然聯(lián)結(jié)毁靶，至少存在一列出現(xiàn)在兩個表中*/
select C.*,O.order_date,O.cust_id,OI.prod_id,OI.quantity,OI.item_price from customers C,orders O,orderitems OI where C.cust_id=O.cust_id and O.order_num = OI.order_num and OI.prod_id='RGAN01';
/*外聯(lián)結(jié)，聯(lián)結(jié)在相關(guān)的表中沒有對應(yīng)行的行逊移，外聯(lián)結(jié)與內(nèi)聯(lián)結(jié)對比预吆。外聯(lián)結(jié)將沒有對應(yīng)的行的數(shù)據(jù)也進(jìn)行輸出*/
select C.cust_id, O.order_num from customers C inner join orders O on C.cust_id=O.cust_id ;
select C.cust_id, O.order_num from customers C left outer join orders O on C.cust_id=O.cust_id ;
/*全外關(guān)聯(lián)，兩個表的沒有對應(yīng)的行的數(shù)據(jù)也進(jìn)行關(guān)聯(lián)*/
select customers.cust_id, orders.order_num from customers full outer join orders on customers.cust_id=orders.cust_id ;
/*內(nèi)聯(lián)與聚集函數(shù)一起用*/
select C.cust_id,count(O.order_num) num_order from customers C inner join orders O on C.cust_id=O.cust_id group by C.cust_id ;
select C.cust_id,count(O.order_num) num_order from customers C left outer join orders O on C.cust_id=O.cust_id group by C.cust_id ;
第14課組合查詢union
union all可以返回重復(fù)的匹配值
多個union的列要相同胳泉，順序沒有要求拐叉，有聚集函數(shù)也要相同
union的排序只需要在最后一個select語句中使用order by語句*/
對比兩種實(shí)現(xiàn)方法
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI');
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
union all
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI') or cust_name='Fun4All';
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
union
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All' order by cust_name desc;
第15課插入數(shù)據(jù)insert
插入完整的行
插入行的一部分
插入某些查詢的結(jié)果
insert select
select into
最簡單的insert語句，不安全扇商，因?yàn)楸斫Y(jié)構(gòu)變化將不能使用凤瘦，應(yīng)該指定列名,對比以下語句
指定了全部列名，這樣安全性高些案铺，列名與值的內(nèi)容可以按照任意次序出現(xiàn)蔬芥，保證前后對應(yīng)即可
insert into customers values('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',null,null);
insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',null,null);
/*插入查詢結(jié)果,selecr后的列名不重要，只會把select后的按位置插入*/
create table custnew as select * from customers ;
select * from custnew for update ;
insert into customers
(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
select * from custnew;
/*復(fù)制表控汉，第一種情況不適用于oracle*/
select * into custcopy from customers ;
create table custnew as select * from customers ;
第16課更新和刪除數(shù)據(jù)update/delete
使用update與delete時一定要注意笔诵，不要漏了where語句
若要清除一格的值，可以將其值設(shè)置為null
delete 刪除一行至所有行暇番，不刪除表
如果需要刪除所有行有更快的語法truncate table
drop才可以刪除表結(jié)構(gòu)
使用update\delete語句前先用select語句選出所有值檢查沒問題后再更改
update customers set cust_email='kim@thetoystore.com' where cust_id='1000000005' ;
update customers set cust_contact='Sam Roberts',cust_email='sam@toyland.com' where cust_id='1000000006';
update customers set cust_email=null where cust_id='1000000005';
delete from customers where cust_id='1000000012';
truncate table custnew ;/*0.046S*/
delete from custnew2 ;/*0.016S*/
drop table custnew ;
第17課創(chuàng)建和操縱表
列名唯一嗤放，數(shù)據(jù)類型難點(diǎn)思喊，后面的描述復(fù)雜
防止意外覆蓋表壁酬，在創(chuàng)建表之前需要對表進(jìn)行手動刪除
default 1是用于設(shè)置默認(rèn)值
default sysdate用于設(shè)置默認(rèn)時間
更新表(添加列)alter table,有數(shù)據(jù)時候更新不好，應(yīng)該在設(shè)計時就考慮好
如果表的結(jié)構(gòu)更改過大恨课，可以創(chuàng)建新的表存放舊內(nèi)容舆乔，替換掉
drop table無法恢復(fù)
rename to 重命名表名
CREATE TABLE Products
(
prod_id char(10) NOT NULL ,
vend_id char(10) NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL default 1,
prod_desc varchar(1000) NULL
);
insert into pdm values(to_char(sysdate,'yyyy\MM\dd'));
alter table vendors add vend_phone char(20);
alter table vendors drop column vend_phone ;
drop table custnew ;
create table custnew as select * from customers ;
rename custnew to custcopy ;
select * from custcopy ;
第18課使用視圖
一個虛擬表，可以很方便地通過查到數(shù)據(jù)
視圖可能禁止使用order by
create view創(chuàng)建視圖
drop view viewname刪除視圖
創(chuàng)建出來的視圖就相當(dāng)于一個表
視圖是處理表后剂公，得到想要的數(shù)據(jù)的新的表
select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and prod_id='RGAN01';
create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num;
select cust_name,cust_contact from productcustomers where prod_id='RGAN01';
select rtrim(vend_name)||'('||rtrim(vend_country)||')' as vend_title from vendors order by vend_name ;
create view vendorlocation as select rtrim(vend_name)||'('||rtrim(vend_country)||')' as vend_title from vendors order by vend_name ;
select * from vendorlocation ;
create view customeremaillist as select cust_id,cust_name,cust_email from customers where cust_email is not null ;
select * from customeremaillist ;
create view orderitemsexpanded as select order_num,prod_id,quantity,item_price,quantity*item_price as expanded_proce from orderitems;
select * from orderitemsexpanded where order_num=20008 ;
