如需要电子书，联系我。
- oracle只显示前几行
select prod_name from products where rownum <=5 ;
表的一个默认字段
第一课创建表
字段后有空格，数据类型需要空格来补全，空格是有函数可以去掉的，第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
将值与字符串进行比较需要单引号，与数值比较则不需要单引号
between 5 and 10 条码需要两个值，开始值与结束值，包括开始值与结束值
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;
第五课高级数据过滤
or操作符只要前面的满足后面的就不执行了
有or又有and，and的优先级更高，大多时候会借助括号解决
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
通配符使用起来很慢，应该放在最后
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%';
/*只有微软件的数据库才能用，可以在中括号里加^号来否定，其他的只能用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()函数用于去除右边空格
ltrim()函数用于去除左边空格
trim()函数用于去除两边的空格
计算列，对列进行计算，创建是新的列，可以重命名
select 可以用于运算，但是在这里不行
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';
第八课使用函数处理数据
upper()将小写字母转换成大写字母
lower()将大写字母转换成小写字母
rtrim()除去右边空格
ltrim()除去左边空格
length()字符串的长度
soundex()找出列类似的读音
处理日期oracle没有datepart()函数，只能用其他的了
to_char()函数取日期的部分
to_number()函数将取出的字符串类型日期转换为数字类型
借助between and ，to_date()函数可以将字符串转换成日期类型
还有比较多的数值处理函数
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') ;
第九课聚集函数
avg()返回某列的平均值
count()返回某列的行数,参数为*\1则返回所有列，参数为某列则返回某列的非null值的个数
max()返回某列最大值,忽略null
min()返回某列最小值,忽略null
sum()返回某列值之和,忽略null
distinct参数用于指定不同的值,distinct后面应该跟的是列名
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';
第十课分组函数，group by\having
分组后使用聚集函数
group by可以对多个列进行分组，select中使用了非计算列，都需要在group by中出现
oracle允许使用位置来指定分组
having与where使用相同，作用不同，where是作用在所有的行，having是作用在分组内
另一种说法，where是在分组前进行过滤，group by是在分组后进行过滤
使用group by时也一定要使用order by才能确认顺序
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课使用子查询
子查询总是从内向外进行处理
计算字段是子查询语句的时候
下面使用的是完全限定列名，由表名与列名组合而成，意思是比较orders表中的cust_id与当前正从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'));
/*对一个顾客执行订单数目及对全部顾客执行订单数目*/
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课联结表join
关系数据库的设计：一类数据一个表
用from联结两表的时候，如果没有where则将第一个表的每一行与第二个表的每一行进行联结（叉联结\笛卡儿积）
等值联结\内联结
select vend_name,prod_name,prod_price from products,vendors where vendors.vend_id=products.vend_id ;
/*内联结，指明联结的类型*/
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';
/*只需要用到多个表的数据都可以用联结查询*/
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课创建高级联结
自联结、自然联结inner join、外联结left outer join\right outer join
全外联结full outer join
别名的另一个作用是自联结
可以多个表联结，况且联结时可以使用多种不同的联结
联结时一定要使用正确的联结条件
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';
/*自联结与子查询对比，自联结会更快*/
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' ;
/*自然联结，至少存在一列出现在两个表中*/
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';
/*外联结，联结在相关的表中没有对应行的行，外联结与内联结对比。外联结将没有对应的行的数据也进行输出*/
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 ;
/*全外关联，两个表的没有对应的行的数据也进行关联*/
select customers.cust_id, orders.order_num from customers full outer join orders on customers.cust_id=orders.cust_id ;
/*内联与聚集函数一起用*/
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可以返回重复的匹配值
多个union的列要相同，顺序没有要求，有聚集函数也要相同
union的排序只需要在最后一个select语句中使用order by语句*/
对比两种实现方法
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课插入数据insert
插入完整的行
插入行的一部分
插入某些查询的结果
insert select
select into
最简单的insert语句，不安全，因为表结构变化将不能使用，应该指定列名,对比以下语句
指定了全部列名，这样安全性高些，列名与值的内容可以按照任意次序出现，保证前后对应即可
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);
/*插入查询结果,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;
/*复制表，第一种情况不适用于oracle*/
select * into custcopy from customers ;
create table custnew as select * from customers ;
第16课更新和删除数据update/delete
使用update与delete时一定要注意，不要漏了where语句
若要清除一格的值，可以将其值设置为null
delete 删除一行至所有行，不删除表
如果需要删除所有行有更快的语法truncate table
drop才可以删除表结构
使用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课创建和操纵表
列名唯一，数据类型难点，后面的描述复杂
防止意外覆盖表，在创建表之前需要对表进行手动删除
default 1是用于设置默认值
default sysdate用于设置默认时间
更新表(添加列)alter table,有数据时候更新不好，应该在设计时就考虑好
如果表的结构更改过大，可以创建新的表存放旧内容，替换掉
drop table无法恢复
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课使用视图
一个虚拟表，可以很方便地通过查到数据
视图可能禁止使用order by
create view创建视图
drop view viewname删除视图
创建出来的视图就相当于一个表
视图是处理表后，得到想要的数据的新的表
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 ;
