Oracle 10G中关于表操作常用语句详解以及一点随想

最近利用业余时间一直在看Oracle 10g开发这方面资料, 由于以前企业级项目对数据库操作都是基于微软的SQL2000与2005 版本偏多. 所以这段时间也是一个转换过程.特意在网上订购一本Oracle Database 10G SQL Master SQL and PL/SQL 这本书重新完整的 系统的进行梳理一遍.总体给我感觉是其实我就是在重新梳理结构化查询语言(Structured Query Language/SQL) SQL .虽然做了一遍重复性工作. 但体会也是不同的. 类似这本书并没有费了我很大精力 但给我感觉它就是一条细线. 把我以前遗留的片段全部关联起来. 视野上更加趋于开阔.我慢慢才体会到. 做程序开发的工作其实可粗可细. 我们常常感慨一个技术大牛所作的创造性思维(类似Robert C.Martin关于敏捷实践) 其实殊不知他们也是在逐渐积累过程中才有了创造. 就想一句话说的没有任何事情是凭空而来.那么对于一个做技术出生的人一个完善的知识体系结构是相当重要的. 所以我更愿意倾向于一个科班出生的技术人员. 目前情况是社会上关于软件开发培训的快餐打乱了行业内这种格局, 格局我们没法控制. 无论这种格局乱还是没乱但归咎到底还是一个技术人员在实际工作自我完善自己知识体系这种能力和意识. 对于他人的指导这种"外力的鞭子"抽的我们都有惰性. 自觉的意识才能真正力量. 这种并非一朝一夕的能够成就的. 它是一个长时间 同时具有一定量积累才能沉淀出来的. 只有不断完善自己知识体系 不断学习.才是一个真正技术人员发展上要走长远子路. 这也是我考察一个技术人员非常重要的标准.

这也是很多经历了入门级别痛苦后程序员 最为迷茫一个问题. 以上最近这本书给我一点联想. 转入正题

Oracle Database 10G SQL Master SQL and PL/SQL :书样 随便推荐给各位. 内容虽然很简单 但给我感觉细节上讲的很全面 完全可以做为一个日常工作中查询手册也是没有问题的.一方面它详细而通用讲述了SQL标准每个细节. 同时也有两章是Oracle公司自己扩展语法详细讲解.直接形成比对.及时将来遇到与SQL标准不同变更. 一眼就能看出来.对于即将转入Oracle 10G朋友还是非常值得收藏的一本书.推荐一下:.定价是59.8; 价格适中.

(A)SQL标准与三层.

又重新翻看这个结构化查询语言(简称SQL)的"来世于前生"后.期间不经让我联想到到以前项目中对于底层数据访问层三层架构思想 他们之间联系. 先来看看SQL标准来历:

1970年时有E.F.Cold博士提出关系型数据库这个概率. 听起来早就不新鲜了.但是SQL就是在E.F.Cold博士工作基础自上发展而来. 其第一个实现就是有IBM在20世纪70年代中期开发的.当时IBM开展了一个成为System R的研究项目.SQL 就是从这项目中诞生的. 到了1979年底.一家当时成为Relational Software INc.的公司(其实大家应该都知道就是现在Oracle公司)发布了第一个商业版本的SQL. 随着发展SQL已经被完全标准化了.而且获得美国国家标准组织(American Standards Institute ANSI)认证.SQL可以用来访问Oracle. SQL Server ,DB2.和MySQL 数据库.直至发展到现在.

SQL在发展的实现标准化.也为三层分工实现编码与数据库依赖关系解耦.  编程语言不在依赖数据库. 主要归功于SQL访问数据库的标准化. 目前架设一下主流的数据Oracle. SqlServer DB2.MySQL每个都有自己一套数据库访问方式规则. 那是多么可怕一件事.当切换一个数据库 我们就彻底扔掉了一个数据访问层. 那么编码应用程序就死死绑定数据库上.而从严格三层定义来说. 拿掉任何一层都是可以重用. 显示层. 业务逻辑层 与数据访问层是没有任何实际依赖的 层于层之间实现时一种独立关系. 如果没有SQL语言标准化.也就没有实际意义上三层分工实现. 现在项目中如果实现底层数据库切换可以做一个简单工厂模式 只需在修改简单配置. 代价极小. 如果没有SQL. 这一切就无从谈起了.

(B)附Oracle 10G中关于表操作

附Oracle 10G下编码: 内有详细说明..其中操作非常简单 不做赘述 对于其他一些关于Oracle 10G特有属性将在后续.后有详细说明.

  1 ----------------------创建数据库表成功-----------------------------
  2 --------插入数据操作详细SQL记录--
  3 --------Author:chenkai Date:2010年2月21日15:50:58--
  4 --oracle中显示日期格式为:DD-MON-YY dd是代表日 mon是月份前三个字母大写.yy 年份最后两位实际上存储年是4位 显示的为2位
  5  
  6 select * from customers
  7 
  8 insert into customers(customer_id,first_name,last_name,phone)
  9 values(1,'chenkai','ge','15895426521');
 10 
 11 insert into customers(customer_id,first_name,last_name,phone)
 12 values(2,'marik','陈凯','13854642541');
 13 
 14 update customers set first_name='chenkaiunion' where customer_id=1
 15 
 16 delete from customers where customer_id=2
 17 
 18 --如果误删除了数据库记录 可以回滚数据
 19 rollback;
 20 
 21 insert into customers(customer_id, first_name, last_name,phone)
 22 values(4,'再次插入数据','fuck Again','13654215875')
 23 
 24 --插入数据进行单引号和双引号
 25 insert into customers(customer_id, first_name,last_name)
 26 values( 5,'测试单引号','Bei''Jing-单引成功了')
 27 
 28 
 29 insert into customers(customer_id, first_name,last_name)
 30 values( 6,'测试双引号','The "Great Wall LED"')
 31 
 32 select * from customers
 33 
 34 --从一个表向另一个表复制行 (快速的植入数据注意修改了主键为10) 能用select union 测试不成功 可以使用 9i新增的merge语句来快速转移
 35 insert into customers(customer_id,first_name,last_name)
 36 select 10,first_name,last_name from customers where customer_id=4
 37 
 38 --使用merger来合并行数据 Oracle 9i版本引入了Merge关键之来合并数据
 39 --可以用来将一个表的行合并到另一个表中(如果在转换中需要处理等等 在合并数据)
 40 create table product_change
 41 (
 42   product_id integer constraint change_pk primary key,
 43   product_typeid integer constraint product_type_fkid references product_type(product_id),
 44   name varchar2(130) not null,
 45   description varchar(130),
 46   price number(5,2)
 47 );
 48 
 49 --需求是这样:对于Product 和product_change两个表中product_id相同的行，将Product中各列里值修改成Product_change对应的值. 如果product_id存在
 50 --并匹配 进行更新修改 如果不存在则在Product表插入记录数据 即可 使用merge来操作
 51 merge into products pro
 52 using product_change pc on(
 53 pro.product_id=pc.product_id
 54 )
 55 --当匹配时修改
 56 when matched then
 57 update set
 58 pro.product_typeid=pc.product_typeid,
 59 pro.product_name=pc.name,
 60 pro.product_content=pc.description,
 61 pro.product_price=pc.price
 62 --当不匹配是 插入数据
 63 when not matched then
 64 insert(pro.productid,pro.product_typeid,pro.product_name,pro.product_content,pro.product_price)
 65 values
 66 (
 67   pc.product_id,
 68   pc.product_typeid,
 69   pc.name,
 70   pc.description,
 71   pc.price
 72 );
 73 --操作完成
 74 --merger into 子句指明了合并操作的目标表（要合并到的表） 命名成一个别名 pro 下面都用这个来替代
 75 --using -on子句指定了一个表连接 上面指定的Product表中Product——id和Product——change表中Product_id建立连接
 76 --when matched then 当一行数据满足了Using...on条件时执行操作 同理而言下面操作
 77 
 78 --使用update语句修改行
 79 --定义一个变量
 80 variable average_product_prices number
 81 update products set price=prices*0.75 returning avg(price) into:average_product_prices;
 82 
 83 
 84 --使用默认值 测试成功
 85 create table userdefaultdemo 
 86 (
 87   demo_id integer constraint demo_pk primary key,
 88   datestatus varchar(200)  default 'no placed it''s take' not null,
 89   last_modifieddate date default sysdate not null
 90 );
 91 
 92 drop table userdefaultdemo
 93 
 94 insert into userdefaultdemo(demo_id)
 95 values(1)
 96 
 97 --在更新或插入数据使用Default关键字来设置修改列的值
 98 update userdefaultdemo
 99 set datestatus=default where demo_id=1
100 
101 select * from userdefaultdemo
102 
103 --Oracle 10g中创建指定用户并连接数据到数据库
104 --默认在chendb数据库中创建一个用户
105 create user testuser identified by testpass;
106 --testuser为创建用户用户名
107 --testpass为创建用户登录密码
108 
109 --对用户授权限
110 --登录数据库 connect权限 、创建类似一些诸如表结构的数据 resource权限. 权限由特权用户(例如ＤＢＡ)使用Grant语句授予的
111 grant connect,resource to testuser;
112 --to 指定为授权的用户
113 
114 --新用户连接数据库chendb
115 connect  testuser/testpass--有语法错误
116 
117 --新用户创建表(完整的简化版本的创建表语法)
118 create [global temporary] table table_name
119 (
120   colum_name type [constraint constraint_def default default_exp]
121 )
122 [on commit {delete | preserve} rows]--控制临时表的有效期 delete说明这些行在事务的末尾就要被删除. preserve说明要在会话末尾删除这些行 默认值为Delete
123 tablespace tab_space;--设定数据库占用空间大小
124 --global temporary 指定说明当前表的行都是临时的. 称之为临时表. 临时表对当前所有会话都是可见的.但是这些行只是特定于某个会话.
125 --在Oracle官方上真正完整语法远远比这个要复杂  简化只是常用的设置 以上述语法创建表
126 create table order_status
127 (
128   id integer constraint order_status_pk primary key ,
129   status varchar(120) ,
130   last_modified date default sysdate
131 );
132 --下面创建一个临时表
133 create global temporary table test_orderstatus
134 (
135   id integer constraint order_statustest_pk primary key,
136   status varchar(120),
137   last_modifieddate date default sysdate
138 )
139 on commit preserve rows;--只针对临时表设置当会话结束就删除临时表行数据（Ｏｒａｃｌｅ会话如何定义？）
140 
141 --向临时表插入数据
142 insert into test_orderstatus(id,status)
143 values(1,'chenkaiunion 测试临时表数据')
144 
145 select * from test_orderstatus
146 
147 --当我们断开当前测试用户Testuser 关于数据库chendb连接时 会话就消失 那么关于这个临时表自动被删除
148 disconnect --断开
149 connect testuser/testpass--再次连接查看临时表是否存在
150 select * from test_orderstatus
151 
152 
153 --获得关于表自身一些信息
154 --查询上面刚刚创建两个表
155 select  table_name,tablespace_name,temporary from user_tables
156 where table_name in ('order_status','test_orderstatus')
157 --上述查询时一个系统字典表user_tables[其中列 table_name 表名 、tablespace_name-存储该表的表空间(数据库用来存储诸如表子类对象的地方)名. ]
158 --[temporary 说明该表是否是临时表 如果是则Y  不是则为N]
159 select * from user_tables
160 
161 --获取表中列的信息
162 --从user_tab_colums中获取
163 select table_name, column_name,data_type,data_length,data_precision,data_scale from user_tab_columns where table_name='CHENTEST' --[表名为全大写]
164 --对User_tab_colums系统字典表中 Data_precision-【如果为数字列指定了精度 该列就是查询出精度】 data-scale-【数字列小数部分的位数】
165 
166 --修改表信息
167 --alert table 主要用于 添加/删除/修改列
168 --添加/删除 约束  
169 --启用、禁用约束
170 --查看对表或列建立的约束信息
171 
172 --添加列
173 alter table test_orderstatus add operator_name varchar(120);--报错【试图访问已经交由事务处理的临时表】 临时表不能别修改 
174 alter table order_status add operator_name varchar(120);
175 select * from order_status--成功
176 
177 --修改列【列长度/ 但前提是该列的长度是可以修改的例如 char/Varchar】
178 --【修改数字列的精度】
179 --【修改列数据类型】
180 --【修改列的默认值】
181 alter table order_status modify status varchar(200);--[长度由120增加成200] --成功
182 
183 alter table order_status modify status varchar(20);--【长度由200缩小成20 注意当前表没有任何数据】 --成功
184 
185 --插入数据
186 insert into order_status(id, status) values(2,'chenkai');
187 select * from order_status
188 
189 --再次缩小列status长度
190 alter table order_status modify status varchar(10);--成功了 怪哉！
191 --【只有在表中没有任何行货所有列都为空值是才可以减少列的长度】 但成功了
192 
193 alter table order_status add newnumber number(10);--添加新列
194 
195 --【修改新添加数字列的精度】
196 alter table order_status modify newnumber number(5);--成功
197 --同上【只有在表中没有任何行货所有列都为空值是才可以减少数字列的精度】
198 
199 --【修改列数据类型】
200 alter table order_status modify newnumber char(15);--【由number类型修改char】
201 --【如果表中还没有任何行或列都为空值 就可以将列修改为任何一中数据类型【包括更短的数据类型】否则只能修改一种兼容的类型类似Varchar 修改成Varchar2】
202 --【但前提是不能缩短列的长度 才能转换  类似不能将date修改成number类型】
203 
204 alter table order_status add newdefault varchar(50) default 'chenkaiunion 默认数据';--【如果第一次修改 新列中没有值全部添加了默认值】
205 
206 insert into order_status(id,status) values(3,'测试')
207 
208 --【修改列默认值】
209 alter table order_status modify newdefault default 'chenkaiHome@live.cn';--【修改后只对新添加的列 起了新的默认值作用】
210 
211 select * from order_status
212 
213 --【删除列】
214 alter table order_status drop column operator_name ;--成功
215 
216 --【添加约束】
217 --【表示出Oracle中所有约束控制如下:】
218 --check 【指定一列或一组列必须满足的约束】
219 --primary key /foreign key /unique/readonly /not null
220 --check option 指定对视图执行的DML操作必须满足子查询的条件. 后有详解

 A：qizhong从Oracle 9i版本开始独立引入了一个Merge语句.用来快速简单将一个表的合并到另外一个表中.实现的是跨表间数据库合并操作.值得注意 Merge into子句指明了合并操作的目标表. Using........on子句其实实现的是一个表连接. 上面例子能看出. 而When Matched then  当匹配Using.....on子句条件时操作 同理When not Matched then 实在不匹配是操作.

B:Oracle 10G 数据库基本同SQL 其中有个Check Option指定对视图执行的DML操作必须满足子查询条件. 详细请查看官方的Oracle SQl手册不在赘述.

C:在表修改中默认值 数据类型 数字类的精度等 控制上有详细说明. 同SQL雷同出较多. Oracle 10G 注意已经注明. 参考上编码.