Oracle数据库SQL语句操作记录

Oracle表操作：

=========--表操作--============--新建表、删除表drop table vims_syssetcreate table vims_sysset(   id varchar2(36) primary key not null,--ID,主键   nsrsbh varchar2(20), --纳税人识别号    taxhalladdress varchar2(255), --认证地址   sxbz varchar2(10),--属性标志：0，增值税，1，货运;   typtbz varchar2(2),--是否统一受理平台标志：0，是；1，否     isverify varchar2(2), -- 是否审核  Y:是，N:否   filesize varchar2(10)  --文件大小 单位kb);--添加列，并默认值alter table vims_biz_invoice add fsbz varchar(2) default('N');--修改列的类型alter table vims_biz_invoice_inventorys modify wpsl varchar2(10)alter table vims_biz_invoice modify (fsbz varchar2(2) default 'N');--删除列alter table vims_biz_invoice drop column fsbz;--插入表insert into vims_export_template(pid,type,n_chinese,n_english) values('3','zzs', '发票代码','fpdm');--查询数据库中所有表select table_name from user_tables--清空表记录truncate table table_name

Oracle SQL技巧操作：

=========--sql技巧操作--============--递归查询:connect by prior ...start with  --递归查询本企业的所有下级企业select * from admin t connect by prior t.companyid = t.parentcompanyid start with t.companyid = '';  --左连接查询，左表的记录将会全部表示出来,而右表只会显示符合搜索条件的记录,记录不足的地方均为NULL。select * from test1 a left join test2 b on a.id = b.id;    -- == select * from dave a,bl b where a.id=b.id(+);//左连接select * from test1 a right join test2 b on a.id = b.id;    -- ==  Select * from dave a,bl b where a.id(+)=b.id;//右连接select * from test1 a full join test2 b on a.id = b.id;//全连接，不支持（+）这种写法。--查询和并列，多行转字符串 concat()：字符连接函数 或 ||select concat(xfsh,'|',xfmc) as acfield from cims_xtgl_company_gx select (xfsh||xfmc) as acfield from cims_xtgl_company_gx --wm_concat函数:可以把列值以","号分隔起来,并显示成一行select wm_concat(name) name from test;--把wm_concat结果里的逗号替换成"|"select replace(wm_concat(name),',','|') from test;--查看字符集select * from nls_database_parameters;//查看Oracle服务器的字符集select * from nls_instance_parameters;//查看Oracle客户端的字符集select * from nls_session_parameters;//查看session的字符集--日期比较，to_date: 把字符串转换为数据库中的日期类型rzsj <= to_date('" + rzqdvo.getKprq2() + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss')--格式化日期输出格式,to_char: 把日期或数字转换为字符串 to_char(w.sample_time, 'yyyy-mm-dd ')

创建主键自增长表：

=================--创建主键自增长表--====================--创建表create table student(   id integer primary key not null,--ID,主键，自动增长       name varchar2(20), --姓名    sex varchar2(100), --性别   age integer, --年龄    grade varchar2(100) --年级  );--创建序列create sequence sq_studentstart with 1 --从1开始计数increment by 1 --每次加1个minvalue 1 --最小值nomaxvalue --不设置最大值 设置最大值：maxvalue9999 nocycle --一直累加，不循环--创建触发器create or replace trigger student_trigger before insert on  student for each row /*对每一行都检测是否触发*/ beginselect sq_student.nextval into:new.id from sys.dual;end;--删除表 序列 触发器drop table studentdrop sequence sq_studentdrop trigger student_trigger --插入表insert into student(name,sex,age,grade) values('张三','男', '20','大一');

循环插入数据：

=========--循环插入数据--============-- Created on 2014/11/27 by wwm declare-- Local variables here   i integer;begin   i := 1;loop -- Test statements here --insert into TEST_INVOICE (id, fpdm, fphm,fpje,fpse,kprq,xfsh,gfsh)       values (i, '123123', '12312313','100.00','10.00',sysdate,'123012312',i);      i := i + 1;exit when i > 10000000;end loop;commit;end;

创建用户、表空间并分配权限：

#表空间、用户操作--第1步：创建数据表空间create tablespace vims_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;          --第2步：创建临时表空间  create temporary tablespace vims_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;   --第3步：创建用户并指定表空间 create user ttvims identified by ttvims default tablespace vims_data temporary tablespace vims_temp; alter user ttvims quota unlimited on vims_data ; --提示没有权限操作vims_data表空间，可以修改--第4步：给用户授予权限grant     create session,     create any table,     create any view ,    create any index,     create any procedure,    alter any table,     alter any procedure,    drop any table,    drop any view,     drop any index,     drop any procedure,    select any table,     insert any table,     update any table,     delete any tableto ttvims;