oracle最简单的学习笔记增删改查PLSQL基本语法游标函数存储过程的实现.doc

-----?创建序列 creat?e seq?uence? book?_id I?NCREM?ENT B?Y 1 -?- 每次加几个 　 　START WIT?H 001? -- 从1开始计数 　 　NOMAXV?ALUE ?-- 不设置最大值 　 　NOCYCLE? -- 一直累加，不循环 　 　CACHE 10;? ?-----?-创建books表 creat?e tab?le bo?oks( ?books?_id v?archa?r2(10?00), ?books?_name? varc?har2(?100),? pric?e num?ber, ?qty n?umber?, pub? varc?har2(?200) ?); ?-----?-修改books表的字段 alter t?able ?books? modi?fy(bo?oks_i?d num?ber) ? ----?-----?----往books?表中插入数据 insert i?nto b?ooks ?value?s(boo?k_id.?nextv?al,中国文学1,39,1?2,人民文学); inse?rt in?to bo?oks v?alues?(book?_id.n?extva?l,中国文学2,30,32?,人民文学); inser?t int?o boo?ks va?lues(?book_?id.ne?xtval?,中国文学3,59,22,?清华大学); i?nsert? into? book?s val?ues(b?ook_i?d.nex?tval,?中国文学4,33?,52,?清华大学); in?sert ?into ?books? valu?es(bo?ok_id?.next?val,?中国文学5,99,?62,电子工业); --?-----?----跟新books中的信息 upda?te bo?oks s?et pr?ice=1?00 wh?ere b?ooks_?id=1 ? -?-----?----按出版社分组查询每个出版社金额的情况 select ?pub,s?um(pr?ice*q?ty) f?rom b?ooks ?group? by p?ub ?-----?-----?按出版社、书籍名称分组查询每个出版社金额的情况 select? pub,?books?_name?,sum(?price?*qty)? from? book?s gro?up by? pub,?books?_name? ---?-----?--按出版社、书籍名称分组查询每个出版社金额的情况 5?0 sel?ect p?ub,bo?oks_n?ame,s?um(pr?ice*q?ty) f?rom b?ooks ?group? by p?ub,bo?oks_n?ame h?aving? sum(?price?)50 ? ----?-----?-查询相同出版社的记录数 select ?pub,c?ount(?pub) ?from ?books? grou?p by ?pub h?aving? coun?t(pub?) 1 ? ---?--标的内链接 select ?eid ,?ename?,six,?name ?from ?e,d w?here ?a.id=?d.id ? sele?ct ei?d ,en?ame,s?ix,na?me fr?om e ?join ?d on ?a.id=?d.id ? ----?-做外连接 sele?ct ei?d ,en?ame,s?ix,na?me fr?om e ?join ?d on ?a.id=?d.id(?+) --?--右外连接 select e?id ,e?name,?six,n?ame f?rom e? join? d on? a.id?(+)=d?.id ?----无关子查询 selec?t * f?rom e? wher?e id ?in (s?elect? eid ?from ?d) -?---相关子查询 select? * fr?om e ?where? id i?n (se?lect ?eid f?rom d? wher?e id=?d.id ?and i?d=00?3) ?selec?t * f?rom e? wher?e id ?not i?n (se?lect ?eid f?rom d? wher?e id=?d.id ?and i?d=00?3) ? ----?-存在则显示 select *? from? e wh?ere e?xists?(sele?ct id? from? d wh?ere i?d=d.i?d) -?----不存在则显示 sele?ct * ?from ?e whe?re no