首页 > 数据库开发 > oracle自学笔记

oracle自学笔记

第一天:

1.查询语句

[sql][/sql] view plaincopyprint?

  1. select * from TF_B_TERM_PREPMG_LOG;
  2. select * from TD_M_TERM_PRICE;
  3. select * from TF_B_CEN_TERM_MANAGER_LOG,TF_B_CEN_TERM_MANAGER_DETAIL;    //两张表一起查
  4. select * from TF_B_CEN_TERM_MANAGER_DETAIL;
  5. select * from TF_B_CEN_TERM_MANAGER_LOG;
  6. select log_id*(1+0.1)newLog_id from  TF_B_CEN_TERM_MANAGER_LOG;      //将log_id上调10%展示出来,通过newlog_id展示出来
  7. select distinct log_id from TF_B_CEN_TERM_MANAGER_LOG;              //distinct :过滤掉关键字相同的数据
  8. select * from TF_B_CEN_TERM_MANAGER_LOG where log_id is null;            //查询条件是某个字段为null时,语句
  9. select * from TF_B_CEN_TERM_MANAGER_LOG where log_id is not null;
  10. select * from TF_B_CEN_TERM_MANAGER_LOG where log_id > 300 order by log_id ;     //order by 默认的是升序排列
  11. select * from TF_B_CEN_TERM_MANAGER_LOG where log_id > 300 order by log_id desc;    //order by 默认的是升序排列,如果需要降序排列,在后面加上desc
  12. select * from TF_B_CEN_TERM_MANAGER_LOG where log_id > 300 order by log_id,res_trade_id desc ;      //order by 可以带上多个参数排列,原则是排完第一个,在排序第二个
  13. //常用的函数
  14. select count(*) from TF_B_CEN_TERM_MANAGER_LOG;        //统计表中所有的记录数 count(*)
  15. select avg(log_id) from TF_B_CEN_TERM_MANAGER_LOG;    //统计表中字段的平均数 avg(keyword)
  16. select min(log_id) from TF_B_CEN_TERM_MANAGER_LOG;    //统计表中某个字段的最小值
  17. select max(log_id) from TF_B_CEN_TERM_MANAGER_LOG;     //统计表中某个字段的最大值
  18. select sum(log_id) from TF_B_CEN_TERM_MANAGER_LOG;     //统计表中某个字段的和
  19. select avg(log_id) from TF_B_CEN_TERM_MANAGER_LOG group by log_id;          //关联所有的logid相同的记录,通常和上面的几个函数一起使用
  20. select avg(log_id) from TF_B_CEN_TERM_MANAGER_LOG group by log_id having avg(log_id) > -1;     //having 语句通常和 group by 一起使用

 

2.

 

[sql][/sql] view plaincopyprint?

  1. create index index_name on table_name (cloumn_names);
  2. drop table table_name;    //删除表,表中的数据和索引等一并删除掉,oracle中的,以此表简历的视图,不会随着删除表而删除,但是使用的时候会报错
  3. drop view view_name;
  4. drop index index_name;
  5.                                                    //语句是没有问题,但是问题如下:
  6.                                                    //由于test 表巨大 估计有1亿  在增加 字段时 就不那么容易了,因为default 会锁表
  7. alter table test add nf_quota integer default 0;   //现在想问一下高手,是否可以对已有的记录 不指定缺省值,
  8.                                                    //对新增的记录指定缺省值,这样的语句该如何写?
  9.                                                    //这样的语句是否可以在巨型表上执行?
  10. alter table test drop column_name;  //如果表中只有一个字段,直接删除表
  11. alter table test modify column_name long;
  12. alter table table_name rename to new_table_name;
  13. alter table table_name rename column_name to new_column_name;

第二天:

  1. //表关联查询
  2. //1.简单关联查询
  3. select s.subscriberno,c.customerid from subscribern s,customer c where s.customerkey =c.customerkey and subscriberno = 'cxm';
  4. //2.Join连接
  5. //内连接
  6. select s.subscriberno,c.customerid from subscriber s inner join customer c on s.customerkey = c.customerkey where s.subscrinerno = 'cxm';
  7. //自然连接
  8. select s.subscriberno,c.customerid from subscriber s natural join customer c on s.customerkey = c.customerkey where s.subscriberno = 'cxm';
  9. //外连接
  10. (1)左外链接
  11. select s.subscriberno,c.customerid from subscriber s left outer join customer c on s.customerkey = c.customerkey where s.subscriberno = 'cxm';
  12. (2)右外连接
  13. select s.subscriberno ,c.customerid from subscriber s rigth outer join customer c on s.customerkey = c.customerkey where s.subscriberno = 'cxm';
  14. (3)全外连接
  15. select s.subscriberno ,c.customerid from subscriber s full outer join customer c on s.cusomterkey = c.cusomterkey where s.subscrinerno = 'cxm';
  16. //集合操作
  17. (1)union :可以将第一个查询的结果行和第二个查询的结果行累加起来,并且能够消除重复行
  18. select subscriberno from subscriber where last_name like 'c%' or last_name like 'b%'
  19. union
  20. select subscriberno from subscriber where last_name like 'v%' or last_name like 'n%';
  21. (2)union all : 可以将第一个查询的结果行和第二个查询的结果行累加起来,但是不能排除重复的行数据
  22. select subscriberno from subscriber where last_name like 'c%' or last_name like 'b%'
  23. union all
  24. select subscriberno from subscriber where last_name like 'v%' or last_name like 'n%';
  25. //子查询
  26. (1)in 关键字
  27. select * from subscriber where customerkey in (select customerkey from customer where customerid = 'cxm');
  28. (2)exists 关键字 : 有些情况下,只需要判断是否存在数据,是否满足条件,并不关心数据本身,这时候就可以使用 exists 做子查询
  29. select * from subscriber where exists (select * from customer where customerid ='cxm');
  30. (3)比较运算符 : 在使用比较运算符连接子查询时,必须保证子查询的返回结果只包含一个值,否则整个查询语句就会失败
  31. select * from subscriber where subscriberkey = 'cxm'and salary>=(select avg(salary) from salary_table where salary_id = 'cx');
  32. //数据操作
  33. //增
  34. (1)一般的插入语句
  35. insert into table_name (column_name1,column_name2,column_name3)values (value1,value2,value3);
  36. (2)批量插入语句
  37. insert into table_name  ( column_name1,column_name2,column_name3) Subquery;(subquery为子查询语句);
  38. 比如:
  39. insert into subscriber ( column_name1,column_name2,column_name3) select column_name1,column_name2,column_name3 from subscriber1;
  40. 这样让我想起了以前的一个面试题,当时面试官的题目是这样的:新建一个表把A表中的数据全部搞进去?
  41. 现在想想这个也不实一个办法;
  42. step 1 :
  43. 创建表B,其表结构和A表的表结构是一样的。
  44. create table B(...);
  45. step 2 :
  46. insert into B (...) select ... from A;
  47. //改
  48. (1)简单更新
  49. update table_name set column_name = '' where condition ;
  50. (2)组合查询语句更新
  51. update table_name set column_name = (select Avg(salary) from salary where salaryid = 'cxm') where condition ;
  52. 比如:
  53. update subscriber set salary = (select avg(salary) from salary where salaryid = 'cxm') where subscriberid = 'cxm';
  54. 需要注意的是在通过 select 提供 set 值的时候必须保证 select 语句返回的值是单一的。
  55. //删
  56. delete from table_name where condition ;
  57. 如果要删除表中的所有记录:
  58. 建议使用 truncate ; truncate 的效率比 delete 高,因为他不会产生回滚信息,所以执行 truncate 后不能回滚
  59. truncate table table_name;
  60. //数据控制
  61. (1)付权限
  62. grant 权限 on 对象 to 用户 with grant option ;(其中的 with grant option 是否允许用户传播权限)
  63. 比如;
  64. grant select on table subscriber to user1;//将表的查询权限赋给user1
  65. grant all privileges on table to user1;//将表的所有的权限赋给user1;
  66. grant select on table subscriber to public ;//将表的查询权限赋给所有的用
  67. grant update(column_name),select on table_name to user1;//将表的修改某个字段的,和查询的权限赋给某个用户
  68. grant createtab on database database_name to user1;//将建表的权限赋给user1;
  69. (2)回收权限
  70. revoke 权限 on 对象 from 用户;
  71. 比如:
  72. revoke select on table subscibre from user1;

第三天:

  1. pl/sql语言和编程:
  2. declare
  3. ---申明变量
  4. begin
  5.   ---执行语句
  6.   except10n
  7. end;
  8. 比如:
  9. declare
  10. v_department char(3);
  11. v_course number;
  12. begin
  13.   insert into classes (department,course) values(v_department,v_course);
  14. end;
  15. pl/sql 语句控制结构
  16. 1.选择结构
  17. if 条件表达式 then
  18.  语句1
  19. else if 条件表达式 then
  20.  语句2
  21. else
  22.  语句3
  23. end if;
  24. 这一点和shell语法是一样的
  25. 显式游标
  26. declare
  27. teacher_id number(5);
  28. teacher_name varchar(10);
  29. teacher_sex varchar(2);
  30. teacher_age number(3);
  31. cursor teacher_cur is
  32. select tid,tname,sex,age from teacher_teacher where age >30 and age <40;
  33. begin
  34.   open teacher_cur;
  35.   fetch teacher_cur into teacher_id,teacher_name,teacher_sex,teacher_age;
  36.   loop
  37.     exit when not teacher_cur%found;
  38.     if teacher_sex='M'then
  39.       insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  40.     else
  41.       insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  42.     end if ;
  43.   end loop;
  44.   close teacher_cur;
  45. end;
  46. //使用显示游标注意事项
  47. 1.使用前需要使用 %isopen 检查其是否已经打开.
  48. 2.使用游标过程中都要使用 %found %notFound 检查是否还存在可操作行
  49. 3.从游标中取数据,对应的变量个数和数据类型要一致
  50. 4.游标使用完要将其关闭
  51. declare
  52. type teacher_record is record
  53. (
  54. teacher_id number(5);
  55. teacher_name varchar(10);
  56. teacher_sex varchar(2);
  57. teacher_age number(3);
  58. );
  59. cursor teacher_cur is
  60. select tid,tname,sex,age from teacher_teacher where age >30 and age <40;
  61. begin
  62.  -- open teacher_cur;
  63.   for teacher_record in teacher_cur loop
  64.     if teacher_sex='M'then
  65.       insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  66.     else
  67.       insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  68.     end if ;
  69.   end loop;
  70.  -- close teacher_cur;
  71. end;
  72. 隐式游标
  73. begin
  74.   select tid,tname,,sex,age into teacher_id,teacher_name,teacher_sex,teacher_age from teacher_table where tid = 13;
  75. end
  76. //1.隐式游标的注意事项:对于隐式游标而言,必须有一个into语句,因此使用隐式游标的 select 语句必须只选中一行数据或者只能产生一条数据;
  77. 游标属性:
  78. 1.是否找到游标(%found %notfound)
  79.   open teacher_cur;
  80.   fetch teacher_cur into teacher_id,teacher_name,teacher_sex,teacher_age;
  81.   loop
  82.     exit when not teacher_cur%found; -- exit when teacher_cur%notfound
  83.     if teacher_sex='M'then
  84.       insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  85.     else
  86.       insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  87.     end if ;
  88.   end loop;
  89.   close teacher_cur;
  90.   delete from teacher_table where tid = teacher_id
  91.   if sql%found then
  92.     insert into success values (tid);
  93.   else
  94.     insert into fail values(tid);
  95.   end if;
  96. 2.游标的行数(%rowcount)
  97.   open teacher_cur;
  98.   fetch teacher_cur into teacher_id,teacher_name,teacher_sex,teacher_age;
  99.   loop
  100.     exit when teacher_cur%rowcount=10;
  101.     if teacher_sex='M'then
  102.       insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  103.     else
  104.       insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  105.     end if ;
  106.   end loop;
  107.   close teacher_cur;
  108.  -- open teacher_cur;
  109.   for teacher_record in teacher_cur loop  ---用for循环时,系统隐含的定义了一个数据类型为 %rowcount 的记录,作为循环计数器 ,并将隐式的打开和关闭游标,所以上面的写法是多余的
  110.     if teacher_sex='M'then
  111.       insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  112.     else
  113.       insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  114.     end if ;
  115.   end loop;
  116.  -- close teacher_cur;
  117. 3.游标是否打开(%isopen)
  118.   if teacher_cur%isopen then
  119.    fetch teacher_cur into teacher_id,teacher_name,teacher_sex,teacher_age;
  120.   else
  121.     open teacher_cur;
  122.   end if;
  123.   loop
  124.     exit when teacher_cur%rowcount=10;
  125.     if teacher_sex='M'then
  126.       insert into man_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  127.     else
  128.       insert into female_teacher(....) values (teacher_id,teacher_name,teacher_sex,teacher_age);
  129.     end if ;
  130.   end loop;
  131.   close teacher_cur;
  132. //存储过程:
  133. set serveroutput  on format wrapped
  134. create or replace procedure drop_table(table_name varchar(20))
  135. as
  136.   table_count:=0;
  137. begin
  138.   select count(*) into table_count from all_tables where table_name = upper(table_name);
  139.   if table_count > 0  then
  140.    drop table ni;
  141.   end if;
  142. end;
  143. /

本文固定链接: http://www.devba.com/index.php/archives/1852.html | 开发吧

报歉!评论已关闭.