pl/sql入门

SELECT prod_name,prod_id FROM products;
SELECT * FROM products;
SELECT DISTINCT vend_id FROM products;--只显示不同的id
SELECT DISTINCT vend_id,prod_price FROM products;--只显示不同的id,选择两种组合是不同的
SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price;--由价格排序用order语句，默认升序
SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price,prod_name;--由价格，名字排序用order语句，有优先级
SELECT prod_name,prod_id,prod_price FROM products ORDER BY 3,1;--由价格，名字排序用order语句，有优先级，1，3简化，跟上面的一样
SELECT prod_name,prod_id,prod_price FROM products ORDER BY 3 DESC,1 DESC;--由价格，名字排序用order语句，有优先级,DESC降序descend,
SELECT prod_name,prod_id,prod_price FROM products ORDER BY 3 DESC,1;--由价格，名字排序用order语句，有优先级,DESC降序descend
SELECT prod_name,prod_price FROM products WHERE prod_price!=2.5;--过滤数据，选出子集。精度无所。同时用order by，要放在where后面
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 2.5 AND 8.99;--过滤数据，选出子集。逻辑运算符：=，>,<,!=.<=,>=等,BETWEEN···AND···
SELECT prod_name,prod_price FROM products WHERE prod_name='Fuses';
SELECT prod_name,prod_price FROM products WHERE prod_name='fuses';--区分大小写,无法找到fuses
SELECT prod_name,prod_price FROM products WHERE lower(prod_name)='fuses';--用lower来转换
SELECT prod_name FROM products WHERE prod_price IS NULL;--返回没有价格的表
SELECT cust_id FROM customers WHERE cust_email IS NULL;
SELECT * FROM customers WHERE cust_email IS NULL;
SELECT prod_name,prod_price ,prod_id FROM products WHERE vend_id=1003 AND prod_price<=10;--语句结合到一起只选供应商为1003的产品
SELECT prod_name,prod_price ,prod_id FROM products WHERE vend_id!=1003 AND prod_price<=10;
SELECT prod_name,prod_price ,prod_id FROM products WHERE vend_id!=1003 OR prod_price<=10;--这样！=1003是否没用？（错）好好考虑逻辑关系
SELECT prod_name,prod_price ,prod_id FROM products WHERE  prod_price<=10;
SELECT prod_name,prod_price ,prod_id FROM products WHERE (vend_id!=1003 OR prod_price<=10) and prod_price>=10 ;
SELECT prod_name,prod_price ,prod_id FROM products WHERE vend_id!=1003 OR prod_price<=10 and prod_price>=10 ;--与上面比较运算的优先级
SELECT prod_name,prod_price FROM products WHERE vend_id IN(1002,1003);--in与or语句是一样的，但是语法更清晰，求值顺序更容易管理，运算更快
SELECT prod_name,prod_price FROM products WHERE vend_id  NOT IN(1002,1003);
SELECT prod_name,prod_price FROM products WHERE prod_name LIKE'Jet%';--字段检索
SELECT prod_name,prod_price FROM products WHERE prod_name LIKE'%Jet%';
SELECT prod_name,prod_price FROM products WHERE prod_name LIKE'%Jet';--防止后面空格无法读取，要在末尾加%
SELECT prod_name,prod_price FROM products WHERE trim(prod_name)='Jet';--trim移除空格ltrim左，rtrim右
-----7月3号----------
SELECT order_num FROM orderitems WHERE prod_id='TNT2';
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;
-----7月4号----------
SELECT cust_name,cust_contact FROM customers WHERE Soundex(cust_contact)=Soundex('Y Lie');--soundex函数表示发音相同的模糊字
SELECT AVG(prod_price) AS avg_price FROM products;--求平均值average
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products;--求平均值average,是有区别的价格（相同的价格不会加两次）
SELECT AVG(prod_price) AS avg_price FROM products where vend_id=1003;--求特定行的平均值
SELECT count(*) as num_cust from customers; --统计表的行数，包括null
SELECT count(cust_email) as num_cust from customers; --统计特定某列的行数，不包括null
SELECT MAX(prod_price) AS max_price from products;--统计最大值/最小值（min）
SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;--group by 函数指的是对后面跟的函数进行分组。然后在调用函数
SELECT vend_id,Max(prod_price) AS num_prods FROM products GROUP BY vend_id;--尝试一下组合计算
SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>2;
SELECT vend_id,Max(prod_price) AS MAX_prods FROM products GROUP BY vend_id HAVING Max(prod_price)>50;--HAVING语句做删选
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;
--注意group与order之间的关系

-----视图的练习--------
SELECT cust_name,cust_contact FROM customers c,orders o,orderitems oi where c.cust_id=o.cust_id AND oi.order_num=o.order_num
AND prod_id='TNT2'-----缩写，where语句，表的连接

CREATE or replace VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id
FROM customers c,orders o,orderitems oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num;
-----创建视图，但是不会有任何输出-------
SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='TNT2';
SELECT * FROM productcustomers ---WHERE prod_id='TNT2';
SELECT RTrim(vend_name)||',('||RTrim(vend_country)||')'AS vend_title from vendors order by vend_name;
create view vendorlocations AS SELECT RTrim(vend_name)||',('||RTrim(vend_country)||')'AS vend_title from vendors order by vend_name;
SELECT * FROM vendorlocations
-----视图与表的区别，表建好以后就与原表无关了，而视图每次要执行一遍，会更新。速度慢，但是不用执行select语句。

-----7月5号-----
-----游标的使用------
DECLARE
     CURSOR c_venders IS
     SELECT vend_id,vend_name,vend_

/*tablespace *****--定义表空间
  pctfree 10--块保留10%的空间留给更新该块数据使用
  initrans 1 --初始化事务槽的个数
  maxtrans 255--最大事务槽的个数
  storage--存储参数
  (
    initial 64K--区段(extent)一次扩展64k
    minextents 1--最小区段数
    maxextents unlimited---最大区段无限制  
  )*/


-------注释的添加--------
comment on table products is '产品'--给文件添加注释，会出现在properties里
comment on column products.prod_id is '产品编号'--给列添加注释，会在describe里
------清空表------------
truncate table customers

-------以上为7月的练习，接着为8月份-----------
--------连接，自连接，自然连接，外连接，左关联，右关联--------
SELECT prod_name,prod_price FROM products WHERE vend_id  NOT IN(1002,1003) union all SELECT prod_name,prod_price FROM products WHERE prod_price >10;
SELECT prod_name,prod_price FROM products WHERE vend_id  NOT IN(1002,1003) union SELECT prod_name,prod_price FROM products WHERE prod_price >10;
--union是把两个查询结果拼接。查询的对象必须一致。union自动消除重复的行，而union all 则不会。如果用order排序，则是对所有的选出对象排序。
-----自连接
SELECT prod_id,prod_name,vend_id from products where vend_id=(SELECT vend_id from products where prod_id='DTNTR')
SELECT p1.prod_id,p1.prod_name,p1.vend_id from products p1,products p2 where p1.vend_id=p2.vend_id and p2.prod_id='DTNTR'--目的是选出含有prod_id的vend_id,然后找出同一vend_id的所有记录。
SELECT p1.prod_id,p1.prod_name,p1.vend_id from products p1,products p2 where p1.prod_id=p2.prod_id and p2.vend_id='1003'--与上面是一样的，因为prod_id都不相同
select * from products p1,products p2--笛卡尔积，共有14*14=196
select * from products
SELECT p1.prod_id,p1.prod_name from products p1,products p2 where p1.vend_id=p2.vend_id order by p2.prod_id
SELECT * FROM orderitems
SELECT * FROM products
SELECT * FROM orderitems
SELECT * from products p1,products p2 where p1.vend_id=p2.vend_id

--nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value
----------8/15----------------
/*使用现有的表创建一个新表的结构不带数据(创建一个表的副本不带数据)
      create table copy_route_header as select * from route_header where 1=2;
    
      如不带条件相当于复制一张表
      create table copy route_header as select * from route_header ; */
--decode的用法
/*ecode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

decode(字段或字段的运算，值1，值2，值3）

       这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
*/

1、内联接（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。     
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   
    
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     

1）LEFT  JOIN或LEFT OUTER JOIN     
左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       

2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       
3）FULL  JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   
    
3、交叉联接   
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    

FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。     
    
例子：   

-------------------------------------------------
  a表     id   name     b表     id   job   parent_id   
              1   张3                   1     23     1   
              2   李四                 2     34     2   
              3   王武                 3     34     4       
  a.id同parent_id   存在关系   

--------------------------------------------------    
 1） 内连接   
  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
    
  2）左连接   
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  3   王武                  null   

 

 3） 右连接   
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  null                       3     34     4   
    
 4） 完全连接   
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   

  结果是     
  1   张3                  1     23     1   
  2   李四                 2     34     2   
  null               　　  3     34     4   
  3   王武                 null