Oracle SQL开发笔记（持续更新中...）

冰镇可乐

Oracle SQL开发笔记　　作者：熔岩
　　日期：2006-11-2
　　MSN ：leizhimin@126.com
　　声明：原创作品，未经授权，谢绝转载!
　　[说明]：这篇文章是以《SQL基础第二版》这本书的内容为主线，以学习笔记的形式写整理出其中的精华。其中也不乏加入我从别的方面学习到的Oracle知识。这篇文章以前在我blog上发了一次，但是内容和排版都没有做过认真的审阅，文章的名字也以本书的读书笔记命名。本次不但对该文章的名字进行了修改外，还对内容进行了补充，排版也更美观易于查找阅读。
　　第一章：在表中存储信息
　　本章的内容太简单，对数据库有过一点点了解都知道，就不写了。
　　第二章：从表中获取信息
　　
　　1、select语句
　　select t.id,t.name form ttt t;      --t为表的别名，也可以写作form ttt as t;
　　select * form ttt;                  --*表示所有列
　　select distinct * from ttt          --distinct表示除去结果中的重复记录
　　select * from ttt
　　where t.id>10                   --where表示条件
　　order by t.id ASC,t.name DESC;  --order by指定按照哪些字段排序，ASC为升序，DESC为降序
　　select t.name as '姓名' from ttt;   --as更改查询结果标题
　　2、 可用的where比较条件有：
　　等于：=、=、
　　包含：in、not in
　　范围：between、not between
　　匹配测试：like、not like
　　Null测试：is null、is
　　布尔链接：and、or、not
　　3、通配符：在where子句中，通配符与like条件一起使用。在Oracle中：
　　%(百分号)用来表示任意数量的字符，或者可能根本没有字符。
　　_(下划线)表示确切的未知字符。
　　?(问号)用来表示确切的未知字符。
　　#(井号)用来表示确切的阿拉伯数字，0到9。
　　[a-d](方括号)用来表示字符范围，在这里是从a到d。
　　

(方括号包含通配符)表示字符本身，没有通配符的性质。
　　第三章：where子句中的符合条件
　　
　　1、标点符号
　　名字中的空格：尽量避免他们，可以用下划线代替空格
　　逗号：分隔列表项。
　　单引号：在Oracle中，应该只使用单引号将文本和字符和日期括起来，不能使用引号（包括单双引号）将数字括起来。
　　双引号：在Oracle中，单双引号含义不同。双引号被用来将包含特定字符或者空格的列别名括起来。双引号还被用来将文本放入日期格式。
　　select first_name as "first name"
　　from l_employees
　　order by "first name"
　　#字号：Access使用＃字号将数字括起来。
　　分号：用来结束SQL语句。
　　保留字：避免使用他们。
　　撇号：在Oracle中，撇号也可以写成彼此相邻的两个单引号。为了在供应商名字中间查找所有带撇号的供应商名字，可以这样编写代码：
　　select * from l_suppliers where supplier_name like '%''%'
　　空白行：Oracle通常不允许SQL语句中有任何的空白行。可以在SQLplus中设置一个选项来允许SQL语句中出现空白行。
　　&符号：在Oracle中,&符号常用来指出一个变量。例如，&fox是一个变量，稍微有点不同的一种&&fox。每当&fox出现在Oracle脚本中时，都会要求您为它提供一个值。而使用&&fox，您只需要在&&fox第一次出现时为它提供变量值。如果想将&符号作为普通的符号使用，则应该关闭这个特性。要想关闭这个特性，可以运行以下的命令： set define off ，这是一个SQLplus命令，不是一个SQL命令。SQLplus设置了SQL在Oracle中运行的环境。
　　双竖线：Oracle使用双竖线表示字符串连接函数。
　　星号：select *意味着选择所有的列，count（*）意味着计算所有的行，表示通配符时，表示0个或任意多个字符。
　　正斜杠：在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。
　　多行注释：/*......*/。
　　不等于：有多种表达方式：!=、^=、、not xxx=yyy、not(xxx=yyy)
　　第四章：保存结果
　　
　　1、在新表或者视图中保存结果
　　
　　1.1、从select语句的结果中创建一个新表
　　Oracle中：
　　create table _book as
　　select * from t_book b
　　where b.number like '0001'
　　Access中：
　　select * from t_book b
　　into _book
　　from book
　　where b.number like '0001'
　　1.2、从select语句的结果中创建一个新的视图
　　Oracle中：
　　create view v_book as
　　select * from t_book b
　　where b.number like '0001'
　　2、表和视图
　　2.1、表和视图的相似之处
　　非常相似，一般不区分，常常将表成为基表或者数据表。
　　2.2、表和视图的不同之处
　　表直接将数据存储到磁盘中。而视图是将select语句存储到磁盘上，不会存储任何数据。
　　基本上，表存储数据库中的数据。视图显示已经在表中数据的外观。
　　表是静态的，而视图是动态的。
　　3、常见的SQL语句
　　
　　3.1、删除表
　　drop table t_book
　　3.2、删除视图
　　drop view v_book
　　3.3、一个视图可以建立在另一个视图之上
　　create view vv_book as
　　select * from v_book b
　　3.4、插入数据
　　插入一行所有列数据：
　　insert into t_book
　　values(777,12,'java类脂');
　　插入一行部分列数据
　　insert into t_book (id)
　　values(23434)
　　3.5、commit和rollback命令
　　3.6、添加若干新行到包含select语句的表中
　　insert into t_book
　　select b.id,b.kind,b.bookname
　　from book b
　　where b.kind !=11
　　insert into t_book (id,bookname)
　　select b.id,b.bookname
　　from book b
　　where b.kind !=11
　　3.7、更改已经存在于表中的数据
　　update t_book t
　　set t.bookname='哈哈哈哈'
　　where t.id=19
　　[注意]：where子句很重要，要是没有，则会更新所有的列
　　3.8、从表中删除行
　　delete from t_book

　　where>　　4、在Oracle中，可以通过命令
　　set autocommit on|off来设定自动发布commit命令。
　　第五章：通过视图修改数据
　　
　　1、使用With Check Option的视图
　　通过视图可以对数据进行更改，尽管新的行或者修改过的行没有出现在最终视图中。有时候我们不想进行这些修改。可以通过使用With Check Option定义视图以防止这些更改。在Oracle和大部分其他类型的SQL产品中都可以这样做。
　　select first_name,last_name,age,sex
　　from l_employees
　　where dept_code='shp'
　　with check option;
　　2、SQLplus的使用
　　3、数据字典概述
　　数据字典（Data Dictionary）是包含关于数据库结构所有信息的表的集合。他包括所有表的名字、列、主键、视图的名字、定义这些视图的select语句等等。数据字典有时又被成为系统目录。大部分SQL产品都有数据字典。
　　这些表是通过数据库系统本身创建和维护的。它们包含数据库系统支持其自身处理和了解自身所需要的所有信息。
　　Orcle数据字典：关于表和视图的信息
　　将获得的信息        数据字典表                          数据字典列
　　表的名字            user_tables或all_tables             table_name
　　视图的名字          user_views或all_views               view_name
　　视图的定义          user_views或all_views               text
　　表和视图的列        user_tab_column或all_tab_column     column_name
　　表的主键            user_constraints和user_cons_columns或all_constraints和all_cons_columns
　　3.1、如何查找有所表的名字
　　select * from table_name
　　第六章：创建自己的表
　　1、创建表
　　-- 创建l_employees表[1]
　　drop table l_employees cascade constraints;     -- 预防性删除[2]
　　create table l_employees(
　　employee_id number(3),
　　first_name, varchar2(5),
　　last_name,varchar2(10),
　　dept_code,varchar2(3),
　　manager_id number(3))
　　strorage(initial 2k next 2k pctincrease 0)      -- 可选的[3]
　　tablespace &users;                              -- 可选的[4]

　　>　　add constraint pk_l_employees
　　primary key(employee_id)
　　using index                                     -- 可选的[6]
　　strorage(initial 2k next 2k pctincrease 0)      -- 可选的
　　tablespace &indx;                               -- 可选的[7]
　　comment on table l_employees is '员工表';                           -- 可选的[8]
　　comment on column l_employees.employee_id is '员工编号';            -- 可选的[9]
　　comment on column l_employees.first_name is '姓';                   -- 可选的
　　comment on column l_employees.last_name is '名';                    -- 可选的
　　comment on column l_employees.dept_code is '部门代号';              -- 可选的
　　comment on column l_employees.manager_id is '经理代号';             -- 可选的
　　-- 建表过程完成，下面是插入数据
　　insert into l_employees values(201,'Jim','Fking','A32',451);        -- 可选的[10]
　　......
　　insert into l_employees values(999,'Aix','Tom','B51',222);
　　analyze table l_employees compute statistics;   -- 可选的[11]
　　说明：
　　-- [1]：对sql脚本的一个注释。在Oracle和其他大部分SQL产品中，注释行通常以两条短横线开始，并且后面有一个空格。
　　-- [2]：这是个预防性删除。短语cascade constraints确定了将在所有条件下删除表。没有这个短语，就不会在特定的条件下删除表。
　　-- [3]：storage字句告诉Oracle为这个表分配了多少磁盘空间。这通常是由DBA处理的，并且不能被应用程序员所处理。如果在此省略此行，那么数据库使用默认值。在这个storage字句中，initial 2k参数告诉Oracle在最初创建表时，为这个表分配了2kB的磁盘空间。下一个参数告诉Oracle，当最初分配的磁盘空间填满时，为这个表分配另外一个2kb磁盘空间。pctincrease 0参数告诉Oracle，为以后填满磁盘空间的每一个表一直分配2kb的磁盘空间。处理方法是分配平均大小的磁盘空间。例如，pctincrease 50将每个连续的分配增加50%，因此，下一个分配将是3KB，然后是4.5KB。这个参数有时用于增长迅速的表。
　　-- [4]：tablespace子句告诉Oracle应该以什么样的表空间（tablespace）建立新表。在这里，将以用户表空间来创建新表，或者以赋给&users变量的表空间来创建新表。表空间是存放表的地方，它是有名字的磁盘空间区域。这是一个DBA的概念。实际应用的产品数据库通常有许多磁盘驱动器。这些磁盘驱动器上的空间被划分为表空间，因此可以更容易的管理它们。通常一个Oracle数据库至少有4个表空间：system、users、indx和temp。system表空间被数据字典所使用，并且不应该将它用于其他地方。users表空间用来保存大部分表。indx表空间用来保存大部分索引。temp表空间被用作完成排序的区域。DBA可以创建其他表空间。要想查看表空间的名称，可以使用有DBA权限的用户id，并且输入以下命令：select tablespace name from dba_tablespace;
　　-- [5]：alter table命令让employee_id列成为表的主键
　　-- [6]：在创建主键后，也会自动创建主键的索引，using index字句为索引设置了表空间和磁盘空间。
　　-- [7]：将以index表空间创建主键的索引，或者以赋予&indx变量的表空间来创建主键索引。
　　-- [8]：给表添加注释。
　　-- [9]：给表的字段添加注释。
　　-- [10]：插入数据。
　　-- [11]：在创建新表并加载数据于其中之后，应该运行analyze table命令。还应该在任意表添加一连串数据之后运行这个命令，该命令会将有关表的信息（如，表的大小和其他特性）放入数据字典中。
　　2、更新表
　　向表添加主键：主键是约束条件的一种类型，是限制可以输入到表中的数据的规则。一个表只能有一个主键，主键不能为NULL，主键可能由几个列组合构成。没有必要在alter table命令之后发布一个commit命令。通过alter table命令进行的更改会即刻成为永久性更改。实际上，从不需要在“数据定义语言（DDL）”命令之后使用一个commit命令，DDL命令创建了一个数据库对象，或者更改一个对象的结构。只有在“数据修改语言（DML）”命令只有需要使用commit命令，这些DML命令有insert、update和delete，他们更改了表中的数据。

　　>　　add constraint pk_l_foods                   -- 注释[2]
　　primary key (supplier_id,product_code);     -- 注释[3]
　　说明：
　　-- [1]：通过这个命令更改l_foods表。
　　-- [7]：给这个约束条件起一个名字。
　　-- [7]：单词primary key指出这是一个主键约束条件。列的列表中允许包含形成主键的列。这个列表可以包含任意数量的列，甚至可以包含表中的所有列，但是通常限制它只包含一个列或者两个列。
　　更改表的主键：一个表只能有一个主键，在创建新的主键之前，必须删除原有的主键。

　　>　　drop constraint pk_l_foods;                 -- pk_l_foods是约束条件的名字

　　>　　add constraint pk_l_foods
　　primary key (menu_item);
　　向表添加一个新列：添加的新列总是表的最后一列。

　　>　　add supply_date date;       -- supply_date（供应日期）为新添加的一列，数据类型为date。
　　扩展列的长度：可以更改文本列或者数字列的长度，包括更改数字的精度，但数据类型不能更改。所有的日期都有相同的数据类型，因此，更改日期列的数据类型是没有意义的。

　　>　　modify food_name varchar2(24);

　　>　　modify price mumber(7,2);
　　从表中删除一列：

　　>　　drop column price_increase;
　　3、重复行问题
　　
　　3.1、如何删除(表a)重复行：通过去掉重复的查询查询结果创建新表。
　　drop table a1;
　　create table a1 as
　　select distinct *
　　from a;
　　3.2、如何区别重复行：通过向表中添加一个数字列来区分表的重复行。并将这个列设为表的第一个列。
　　dorp table a1;
　　create table a1 as
　　select rownum as row_id,name,price;
　　4、从文件中加载大量数据
　　4.1、从文件中加载大量的数据：insert语句是添加单个行或者适当数量的行到表中的好工具。如果想添加大量的行到表中，可以将数据放到一个平面文件中，操作起来会更容易一些。所谓的平面文件是没有特殊结构的普通文件。可以用记事本等文本编辑器来创建一个平面文件，平面文件中数据之间用“tab”键隔开，如果要输入null，只需将数据留为空白即可。导入的原理是调用Oracle的系统工具sqlldr.exe。下面是一个导入平面文件的批处理脚本（批处理中的“^”符号表示将一个长命令分成几行，是批处理文件中的延续符号）。
　　sqlldr.exe ^                            -- 注释[1]
　　control = 'C:\temp\load_file.ctl' ^     -- 注释[2]
　　log = 'C:\temp\log.txt' ^               -- 注释[3]
　　bad = 'C:\temp\bad.txt' ^               -- 注释[4]
　　rows = 50                               -- 注释[5]
　　-- [1]: 在BAT文件中，被用于行的延续，它表示一个行是当前行的延续。计算机会将整个BAT文件看作一个单行代码。如果不使用^，必须在单行中写下所有的参数。
　　-- [2]: Control 文件包含数据和加载数据的指令。它是一个输入文件。
　　-- [3]: Log文件是一个输出文件，并且将包含来自加载的消息。
　　-- [4]: Bad文件是一个输出文件，它将包含所有被拒绝的数据。
　　-- [5]: 这告诉加载程序执行每次commit加载50个行。
　　4.2、加载在Oracle中带分隔符的数据：在上面介绍了通过平面文件加载数据，需要指定每一个字段的确切位置，很麻烦。有一种更简单的方法就是加载在Oracle中带分隔符的数据。使用了分隔符的数据通常更方便，因为这样就无需总是将数据完美地排列在列中。在准备数据文件的时候，首先必须选一些数据中没有出现的字符作为界定符。在这里，我们选用了逗号，它被放置于每两个字段之间，标志着一个字段的结束和另一个字段的开始。通常数据都被堆放在一起，字段之间没有空白。
　　5、Oracle中的analyze table命令：该命令会告诉数据字典中表的行数和其他信息。优化器使用这个信息来优化select语句的处理。没有数据字典中的这个信息，处理将不会特别有效。
　　analyze  table  a_organ compute statistics
　　-- 告诉数据字典a_organ表的行数，并将关于表的其他资料放入字典中。