sELECT * FROM qj_test;
恢复数据
create table qj_test2
as
select * from qj_test as of TIMESTAMP to_timestamp('20120711 16','yyyymmdd hh24');
CREATE TABLE qj_test3(
autoid NUMBER(30) NOT NULL PRIMARY KEY,
NAME CHAR(30) NOT NULL,
ID NUMBER(10) NOT NULL,
address CHAR(50) NOT NULL
)
CREATE SEQUENCE temsequence
MINVALUE 1
MAXVALUE 99999999
START WITH 1
INCREMENT BY 3
NOCACHE;
DROP SEQUENCE temsequence
INSERT INTO qj_test3 VALUES('123','qijin',temseq.nextval,'123');
INSERT INTO qj_test3 VALUES(temseq.nextval,'qijin','123','123');//values这六个字母一定要打
正确否则会有不必要的麻烦
SELECT SYSDATE FROM dual
INT类型是NUMBER类型的子类型
commit 的使用
如何解决ORA-00054资源正忙，要求指定NOWAIT /////杀掉进程或者等待执行结束
CREATE SEQUENCE temyear
MINVALUE 1
MAXVALUE 999999
START WITH 2009
INCREMENT BY 1
NOCACHE;
commit 之前其他账号不能看到你更新的数据了
UPDATE qj_test3 SET birth=temyear.nextval WHERE autoid='25';
COMMIT;
CURRVAL 必须在取了NEXTVAL之后才有效。它只表示当前SESSION最后一次取值，不表示这个SEQUENCE（被多个SESSION使用）的当前值。
SELECT temyear.nextval FROM dual //查询到temyear的下一个数值每查询一次值修改一次 不同的数据库中也成立可以说是同步的
SELECT temyear.nextval-10 FROM dual 可以进行计算
SELECT temseq.currval FROM dual 不能一开始就用需要初始化一下=====第一次执行的时候要next
初始化一下（oracle规定）
第一个循环体的使用
DECLARE
COUNT NUMBER:=1;
countid NUMBER:=16;
LOOP
COUNT:=COUNT+1;
EXIT WHEN COUNT>6;
UPDATE qj_test3 SET birth= temyear.nextval WHERE birth='null';
COMMIT;
END LOOP;
DECLARE
v_COUNT NUMBER:=1;
countid NUMBER:=16;
BEGIN
WHILE v_COUNT<=6
LOOP
UPDATE qj_test3 SET birth= temyear.nextval WHERE autoid=countid;
v_COUNT := v_COUNT + 1;
countid := countid + 3;
COMMIT;
END LOOP;
END; // 为什么用exit when 不行！（没有找到解决办法）
ALTER TABLE qj_test3
ADD SNO NUMBER(20) //给qj_test3这张表添加了一个属性
ALTER TABLE qj_test3
DROP COLUMN SNO //给qj_test3这张表删除了一个属性
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON qj_test3
FOR EACH ROW
BEGIN
SELECT temseq.nextvalINTO :new.autoid FROM dual; //new. 这个很关键！！ END trg_test
SELECT * FROM All_Triggers a WHERE a.table_name='QJ_TEST3';//查询表对应的触发器 也可在my objects 里查看triggers 就可以看到所有的触发器了！
每张表最多可建立12 种类型的触发器，它们是:
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW
1. 执行 BEFORE语句级触发器;
2. 对与受语句影响的每一行：
l 执行 BEFORE行级触发器
l 执行 DML语句
l 执行 AFTER行级触发器
3. 执行 AFTER语句级
Compilation errors for TRIGGER AGENT_INTE_NEW.TRG_TEST
Error: PLS-00801: 内部错误 [ph2csql_strdef_to_diana:bind]
Line: 2
Text: FOR EACH ROW
Error: PL/SQL: ORA-06544: PL/SQL: 内部错误, 参数: [ph2csql_strdef_to_diana:bind], [], [], [], [],
[], [], []
Line: 4
Text: SELECT temseq.nextval INTO: new.autoid FROM dual;
Error: PL/SQL: SQL Statement ignored
Line: 4
Text: SELECT temseq.nextval INTO: new.autoid FROM dual;
代码一：CREATE OR REPLACE TRIGGER trg_test BEFORE INSERT ON qj_test3
FOR EACH ROW
BEGIN
SELECT temseq.nextval INTO: （空格）new.autoid FROM dual;END;
代码二：CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON qj_test3
FOR EACH ROW BEGIN
SELECT temseq.nextvalINTO:new.autoidFROM dual;
END trg_test;
代码一爆出了三个错误，经过一晚上弱弱地查找，终于把三个错误找出，就是一个没有必要的空格打错了位置！！！！！！！惨痛的教训啊！！
SQL INNER AND LEFT JOIN关键字训练！！
ALTERTABLE qj_test4
DROPCOLUMN address
ALTERTABLE qj_test4
ADDqj_sizeNUMBER(20)
UPDATE qj_test4 SET qj_size=temyear.nextval WHERE
autoid!=NULL;
COMMIT; ========有错误更新不了找不到错误所在？不知道是不是系统繁忙无法响应！
试验另外一种方法： UPDATE qj_test4 SETqj_size=temyear.nextvalWHEREautoid=37; COMMIT;
和
UPDATE qj_test4 SET qj_size=temyear.nextval WHERE
autoid=’37’;
COMMIT;此方法是错误的！因为数字不能用单引号~ 单引号好像是用来匹配字符串的~
UPDATE qj_test4
SETqj_size=temyear.nextval-1000WHEREautoid=16;
COMMIT; ================可以用来更新替换qj_size的原有值！！
按住F8 执行和按住绿色的按钮能commit！！！才
算提交成功！
SELECT *
FROM qj_test3
LEFTJOIN qj_test5
ON qj_test3.autoid=qj_test5.autoid
SELECTDistinct *
FROM qj_test3
LEFTJOIN qj_test5
ON qj_test3.autoid=qj_test5.autoid
DELETE !!!!!!!!!!! ORDER BY TOP == LIKE === IN
SELECT *
FROM qj_test3
ORDERBY birth
DELETEFROM QJ_TEST3
WHERE birth=1990
SELECT * FROMqj_test
WHERERownum<5 ===========返回前4条记录！！！
SELECT * FROM qj_test3
WHERENAMEIN ('qijin','lby')
SELECT * FROM qj_test5
WHEREautoIDBETWEEN16AND25 ====取在16到25之间的AUTOID的所有行
SELECTq.autoid
FROM qj_test3 AS q
WHEREq.autoidISNOTNULL
=====报错网上给出的解决办法是不用AS 直接用 qj_test3
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 CREATETABLE qj_test6
AS
SELECT *
FROM
(SELECT * FROM qj_test3 UNIONALL SELECT * FROM qj_test5)
W3cschool 没有标明 select into 是sql server的写法
SELECT * INTO qj_test7
FROM qj_test6
这是oracle的写法前提是 qj_test7 存在且字段一致！！！INSERT INTO qj_test7
SELECT *
FROM qj_test6
SQL PRIMARY KEY Constraint on ALTER TABLE
撤销 PRIMARY KEY 约束
除去表内的数据，但并不删除表本身TRUNCATE TABLE 表名称
TRUNCATETABLE qj_test5
DROP DATABASE 语句用于删除数据库
DROPTABLE qj_test5
第二篇：Oracle 总结
目录
Oracle总结 .............................................................................................................................. 2
一、数据类型 ................................................................................................................... 2
1.1、基本数据类型： .............................................................................................. 2
1.2、集合数据类型： .............................................................................................. 2
1.3、关系数据类型： .............................................................................................. 2
1.4、数据类型之间的转换 ...................................................................................... 2
二、Oracle自带的函数 ................................................................................................... 3
2.1、常用的函数 ...................................................................................................... 3
2.2、日期 .................................................................................................................. 4
三、建表和约束 ............................................................................................................... 5
3.1、建表 .................................................................................................................. 5
3.2、约束： .............................................................................................................. 5
四、sql语句 ..................................................................................................................... 6
五、存储过程: .................................................................................................................. 7
六、事物的使用： ........................................................................................................... 8
6.1、Oracle数据库中的事物 .................................................................................. 8
6.2、Asp.net（c#）中使用Oracle事物 ................................................................. 9
七、存在的问题 ............................................................................................................. 10
Oracle总结
一、数据类型
Oracle 9i有3种数据类型：基本数据类型、集合类型和关系类型。
1.1、基本数据类型：
Char，nchar,Varchar2,Nvarchar2,number,Date, float，Long Raw,Long Raw,Rowid,Blog,Clob,Nclob,Bfile,Urowid，Boolean字符类型：Char，Nchar, Varchar2,Nvarchar2，Long字段是固定长度的情况或字段经常改变的情况下使用Char，Nchar如果储存的字段是变长的话使用Varchar2和Nvarchar2. 存储大量的变长字符类型使用Long，最大可达2GB带N的存储的Unicode字符，即汉字占一个字符，不带N汉字占两个字符日期类型：只有Date，使用的时候需要按照数据库的日期存储格式进行数值类型： Number，Float
Number存储整数或浮点型数据，比如Number（4，2）代表4位，小数点为2位 Float可以使用Number代替
非结构化的变长字符的数据类型
Raw（L）2KB和Long Raw（L）2GB存储二进制数据，不会在字符集间转换。 L为长度，以字节为单位，作为数据库列最大2000，作为变量最大32767字节。
1.2、集合数据类型：
包括序列、数组类型和表类型3种。
序列可以实现sql server中自增长列
例子：
Create sequence序列名increment by 1 start with 1
解释：从1开始，每次增长1
使用的时候 序列名.nextval
1.3、关系数据类型：
也称作引用数据类型(REF)，它以引用的方式定义了和其他对象的关系，存储的是指向不同对象数据表的数据的指针。
1.4、数据类型之间的转换
1、Raw和Varchar2之间的转换
1）utl_raw.cast_to_raw该函数按照缺省字符集（一般为GB2312），将VARCHAR2字符串转
换为RAW。如: utl_raw.cast_to_raw(?您好！?)
utl_raw.cast_to_varchar2该函数按照缺省字符集合（一般为GB2312），将RAW转换为VARCHAR2。如: utl_raw.cast_to_varchar2(字段名或十六进制或ASCII码)
2、to_number将给出的字符转换为数字
3、日期的插入和读取：to_char和to_date的使用：
当往数据库中数据类型为date的字段插入值的时候需要使用to_date()进行类型转换 当从数据库中取出数据的时候需要使用to_char()来取出需要的部分
二、Oracle自带的函数
2.1、常用的函数
1.ASCII返回与指定的字符对应的十进制数; 如ascii(?A?)
2.CHR给出整数,返回对应的字符; chr(54740)
3.CONCAT连接两个字符串;
4.INITCAP返回字符串并将字符串的第一个字母变为大写;
5.INSTR(C1,C2) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
6.LENGTH返回字符串的长度; 需要注意汉字
7.LOWER返回字符串,并将所有的字符小写
8.UPPER返回字符串,并将所有的字符大写
9.RPAD和LPAD 语法：lpad（‘gray’，10，‘*’）结果：******gray
10. LTRIM删除左边出现的字符串,RTRIM删除右边出现的字符串
11.SUBSTR(string,start,count) ,取子字符串,从start开始,取count个
如果start位负数则从倒数开始取
12.REPLACE(?string?,?s1?,?s2?) ，在string中使用s2代替s1
13.SOUNDEX返回一个与给定的字符串读音相同的字符串
14.TRIM(?s? from ?string?) 在string中将s去掉，默认位空字符
15.ABS返回指定值的绝对值
16.CEIL返回大于或等于给出数字的最小整数 如3.1返回4，5就返回5
17.FLOOR对给定的数字取整数
18.MOD(n1,n2) 返回一个n1除以n2的余数
19.ROUND和TRUNC按照指定的精度进行舍入
Round进行四舍五入，Trunc不进行四舍五入
20.TO_CHAR(date,?format?)
21.TO_DATE(string,?format?) 将字符串转化为ORACLE中的一个日期 关于to_char和to_date的使用：当往数据库中数据类型为date的字段插入值的时候需要使用to_date()进行类型转换当从数据库中取出数据的时候需要使用to_char()来取出需要的部分 22. to_number将给出的字符转换为数字 23.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
24. 处理字段名可能出现null情况的函数
NVL(expr1, expr2) 如果expr1为null则转换为expr2
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF(expr1, expr2) ->相等返回NULL，不等返回expr1
25．COALESCE (expression_1, expression_2, ...,expression_n)返回的结果为第一个结果不为null的表达式值，如果都为null则返回null,可以替换简单的case语句
26.CONVERT(c,dset,sset) 将源字符串sset从一个语言字符集转换到另一个目的dset字符集?
2.2、日期
1、时间的取法
取年：Select to_char(sysdate,?YYY?) from dual;
Y,YY,YYY,YYYY,YEAR取年的几位，依次为：8，08,008,2008, Two Thousand Eight取月：Select to_char(sysdate,?MM?) from dual;
MM,RM,Month依次为：10，X,10月
取季度：Q select to_char(sysdate ,?Q?) from dual;
如: 1～3月为第一季度，2表示第二季度。
取周Select to_char(sysdate,?WW?) from dual;
WW当前第几周，W本月第几周
取日Select to_char(sysdate,?DDD?) from dual;
DDD, 当年第几天, DD当月第几天, D周内第几天, DY,day中文的星期几取小时：Select to_char(sysdate,?HH?) from dual;
HH，hh12，12制度，HH24 ，24制度
取分秒:
Mi为分钟，ss为秒
读取整个日期的格式：
SQL> select to_char(sysdate,?YYYY-MM-DD HH24:MI:SS?) from dual;
结果：2008-11-03 16:42:18
可以根据想要的结果进行组合
2、常用的日期函数
Add_months增加或减去月份add_months(sysdate,2) from dual;
months_between(date2,date1) 两个日期之间的月数，前面的减去后面的
也可以为-2，整数表示现在时间往后退，负数表示时间往前推
last_day返回本月的最后一天select last_day(sysdate) from dual;
next_day给出当前日期date和星期x之后的日期
trunc(date,fmt)按照给出的要求将日期截断,如果fmt=?mi?表示保留分,截断秒
current_date()返回当前会话时区中的当前日期
extract()找出日期或间隔值的字段值
如：select extract(month from sysdate) “This Month” from dual;
三、建表和约束
3.1、建表
客户表(client)
create table client
(
c_id number(4) primary key,
);
创建客户自增长的序列
create sequence seq_c_id increment by 1 start with 1;
当刚创建序列的时候不能使用currval进行操作，
需要使用nextval进行访问序列中的下一个值
select seq_c_id.currval from dual;
使用创建的序列进行表的插入
insert into client values(seq_c_id.nextval);
订单表（c_order）
create table c_order
(
o_id number(4) primary key,
c_id number(4) references client(c_id) on delete cascade,
外键约束：删除主表的时候连通子表也删除
on delete set null删除主表的时候将子表中的字段设为null
默认是不能删除有子表相关联的主表中的数据
o_invoice varchar2(6) check (o_invoice= ?需要? or o_invoice= ?不需要?), --check约束 unique (o_id,c_id) --唯一约束
);
3.2、约束：
分表级和列级，使存储的数据变得有意思，实现数据的完整性
约束可以在创建表的时候创建也可以在创建表后创建
Not null默认为null，
Unique唯一约束，有时候需要两个或两个以上的列来完成
primary key 主键约束，自动包含了unique和not null
foreign key 外键约束，有时也叫参照完整性约束
check 使数据库存储的字段按照要求进行存储
default 即默认的时候字段的值
创建表后添加约束语法：
不要写约束名（Oracle自动添加）
alter table OracleTypesTable
add unique (mynumber,MyVarchar2);
写名约束名：
alter table OracleTypesTable
add constraint de_OracleTypesTable_1
unique (mynumber,MyVarchar2);
删除约束
alter table OracleTypesTable
drop constraint de_OracleTypesTable_1;
约束的其他用法：
alter table authors
disable constraint pk_au --使约束失效
enable的使用 --使约束可用
cascade的用法：
删除列的时候将对应的约束也删除掉
alter table ss
drop column id cascade constraint
四、sql语句
1、Select username un,count(*)
From ss
Group by username
Having count(*)>1
Order by id desc;
在没有使用组函数之前order by后面可以跟select后面没有出现的字段， 使用了组函数以后，组函数之前的字段必须要在group by中出现， 使用多表操作的时候往往是先挑选后连接
2、取top n
工资最高的前5个员工
select * from
(select last_name,salary from s_emp
order by salary desc )
where rownum < 6;
3、连接：
自身连接：使用表别名连接
外连接：左外连接和右外连接
表1.id（+）=表2.id；
如果表1中的信息没有表2多，则表1的缺乏的值将用null填充
4、单行函数
Lower，upper，substr，instr，trunc
5、组函数
AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值默认位all MAX(DISTINCT|ALL) 求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
使用count（）的时候有三种情况：
Count（*） 最后的结果包括重复和null的列
Count（字段名） 不包括null，但包括重复的列
Count（distinct字段名） 不包括null和重复的列
转义的使用：在查询匹配的时候用
例如查询s开头的只带一个s的，数据库中字段有ss…. ，和s….
使用转义：like ?s\_%? escape ?\?; 转义的\可以随便指定一个字符
定义格式：使查询的结果按照预定义的格式进行读取
例如:定义：column salary format ?$999,999.00?
删除：column salary clear
五、存储过程:
5.1、基本语法：
Create or replace procedure存储过程名
(I in number, ii out number,iii in out number ) (sql server中要不是输入要不是输出) As|is
变量的声明 Begin
End存储过程名；
------------操作单个记录------------------
5.2、不带参数的存储过程
create or replace procedure p_loop
as
number1 integer:=80; --声明变量
i integer:=0;
begin
for i in 1..10 loop --循环的次数
number1:=number1+1;
end loop;
dbms_output.put_line(?number1”s value:?||to_char(number1));
end;
--修改表的不带参数的存储过程
create or replace procedure pp
as
num integer:=10;
--i integer :=0;
begin
--for i in i..10 loop
update ss set id =num;
--end loop;
end;
5.3、存储过程的创建用于将id=acc_no的id加1
--只有输入参数的存储过程
CREATE PROCEDURE credit
(acc_no IN NUMBER)
AS
BEGIN
UPDATE ss
SET id = id+1
WHERE id =acc_no;
END;
--执行的时候需要在命令窗口中执行
exec credit(2);
5.4、有输入和有输出的存储过程
create or replace procedure first_pro
(i in number,ii out number)
as
begin
ii:=i+1;
end;
--执行，同样也是在命令窗口中进行
SQL> var ii number; --定义输出的变量 SQL> exec first_pro(2,:ii); --执行存储过程
5.5、只有输出的存储过程
create or replace procedure Out_only
(i out number)
as
ii number:=100;
begin
i:=ii+1;
end;
六、事物的使用：
6.1、Oracle数据库中的事物
insert into ss values(1,?gray?); --第一条插入语句
savepoint insert1; --设置保存点，也就是还原点
insert into ss values(2,?liulangren?); --第二条插入语句
rollback to insert1; --回滚到insert1还原点，这是第二条插入语句就无效了 commit; --提交事物
rollback to insert1; --错误 在commit了以后，事物的还原点就清空了， rollback; --只写一个rollback还原最近一个commit；
6.2、Asp.net（c#）中使用Oracle事物
使用OracleTransaction类的一个对象来表示一个事务。 OracleTransaction类包含许多操控事务的方法。使用Commit() 方法永久提交SQL语句，并可以使用Rollback() 撤销这些语句。您还可以使用Save() 在事务中设置一个保存点。
导入命名空间
using System.Data.OracleClient;
第1步
创建一个OracleConnection对象连接到Oracle数据库，然后打开该连接。 在C# 中： OracleConnection myOracleConnection =new OracleConnection(
“User Id=store;Password=store;Data Source=ORCL”);
myOracleConnection.Open();
第2步
创建一个OracleTransaction对象，然后调用OracleConnection对象的BeginTransaction() 方法启动事务。 OracleTransaction myOracleTransaction =myOracleConnection.BeginTransaction();第3步 创建一个OracleCommand对象，用于存储SQL语句。 OracleCommand myOracleCommand = myOracleConnection.CreateCommand(); 因为OracleCommand对象使用OracleConnection对象的CreateCommand() 方法创建的，所以它自动使用在第2步中为OracleConnection对象设置的事务。
第4步
将OracleCommand对象的CommandText属性设为向表ss中添加一行的第一条INSERT语句。 在C# 中： myOracleCommand.CommandText =”insert into ss values ( 3, ?M?)”;
//设置事物的保存点SaveName
myOracleTransaction.Save(“SaveName”);
第5步
使用OracleCommand对象的ExecuteNonQuery() 方法运行INSERT语句。
myOracleCommand.ExecuteNonQuery();
第6和第7步
将OracleCommand对象的CommandText属性设为向表ss中添加一行的第二条INSERT语句，并运行它。 myOracleCommand.CommandText =”insert into ss values ( 4, ?N?)”; myOracleCommand.ExecuteNonQuery();第8步 使用OracleTransaction对象的Commit() 方法提交数据库中的事务。 myOracleTransaction.Commit();在完成Commit() 方法之后，由INSERT语句添加的两行将在数据库中永久记录。 //可以使用事物的回滚，是插入的第二条语句失效，回滚到SaveName myOracleTransaction.Rollback(“SaveName”);
第9步
使用Close() 方法关闭OracleConnection对象。
myOracleConnection.Close();
在 .NET程序中设置事务保存点
使用OracleTransaction类的Save() 方法在事务中设置保存点。
七、存在的问题
游标的使用，数据集的读取
使用group by的时侯select语句后面的字段必须都要在group by后面出现，否则会出现没有关联的错误
select ssfj,dss,qybm,qymc,sum (rs),sum (sre),sum (msxmhj),
sum (ynse),sum (jmse),sum(sjynse)
from bims_sbqktjb200712
group by ssfj,dss,qybm,qymc
where qybm=220102794440621
错误出现在sql语句的关键字书写是有顺序的