oracle笔记 _生活知道

oracle笔记mysql —>oracle 全部要大写
int PRIMARY KEY NOT NULL AUTO_INCREMENT
&34; int NOT NULL AUTO_INCREMENT—->&34; NUMBER NOT NULLPRIMARY KEY创建序列及触发器自增ID
varchar —->NVARCHAR2
int—->NUMBER
text—->NVARCHAR2CLOB
datetime —-> DATE
时间比较
to_date
—————————————————————————————————————-
添加字段
ALTER TABLE T_IDS_APPAUTH ADDDEFAULT &39; );
ALTER TABLE T_IDS_APPSYNCDETAIL ADD OPERCOUNT NUMBER DEFAULT 1;
alter table T_USER add WeChat NVARCHAR2;
insert into T_IDSCONFIG values ;
设置主键
ALTER TABLE test_tab ADD CONSTRAINT pk_test_tab PRIMARY key;
修改字段名：
alter table Student rename name to StuName;
修改数据类型：
alter table Student modify );
ALTER TABLE T_ORG MODIFY FIRSTLETTERS VARCHAR2 DEFAULT NULL;
在oracle中，如果已经存在的数据的某些列，假如要更换字段类型的话，有的时候会出现
错误：ORA-01439: column to be modified must be empty to change datatype
解决方法：把列数据复制出来，列置空后修改再恢复数据
例如：
alter table tablename add tempcolumn varchar2;–添加临时字段
tempcolumn update tablename set tempcolumn=colname;–将原字段数据复制到临时字段中
update tablename set colname=null;–将原字段数据清空
alter table tablename modify colname xxx ;–修改原字段类型为xxx
update tablename set colname= tempcolumn;–将临时字段数据复制到修改后的原字段
alter table tablename drop column tempcolumn;–删除临时字段
oracle创建外键约束有两种方法：
1、创建表时直接创建外键约束
create table books not null primary key,
bookName varchar2 not null,
price number,
categoryId number not null references Category–外键约束
);
2、先创建表，表创建成功后，单独添加外键约束
create table books not null primary key,
bookName varchar2 not null,
price number,
categoryId number not null
);
ALTER TABLEbooks ADD CONSTRAINT FK_Book_categoryid FOREIGN KEY REFERENCES Category;
三种外键约束的建立语法如下：
例如有两张表父表T_INVOICE主键ID 。子表T_INVOICE_DETAIL外键字段INVOICE_ID
1、普通外键约束：
ALTER TABLE T_INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_ID FOREIGN KEY REFERENCES T_INVOICE;
2、级联外键约束：
ALTER TABLE T_INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_ID FOREIGN KEY REFERENCES T_INVOICE ON DELETE CASCADE;
3、置空外键约束：
ALTER TABLE T_INVOICE_DETAIL ADD CONSTRAINT FK_INVOICE_ID FOREIGN KEY REFERENCES T_INVOICE ON DELETE SET NULL;
alter table unique_test add constraint email_unique unique;
—————————————————————————————————————-
一、调整oracle表中字段显示顺序：用系统用户
调整oracle表中字段显示顺序此操作要在系统用户下执行，否则未授权错误 [Err] ORA-01031: insufficient privileges
1、查询出指定用户下的指定表的object_id
select object_id from all_objects where owner=&39; and object_name=&39;
2、根据object_id查询出表字段实际的顺序
select obj,name from sys.col$ where obj=7 where obj39;字段名&=4 where obj39;RESPONSEIMPL&34;T_USER_EXTRAINFO&34;FIELD1&34;FIELD2&34;FIELD3&34;FIELD4&34;FIELD5&34;FIELD6&34;FIELD7&34;FIELD8&34;FIELD9&34;FIELD10&34;USERID&34;FIELD1&34;FIELD2&34;FIELD3&34;FIELD4&34;FIELD5&34;FIELD6&34;FIELD7&34;FIELD8&34;FIELD9&34;FIELD10&34;BASEDB&34;MONITOR&34;MONITORNOTICEUSER&34;REMARK&创建序列
create sequence t_user_id_seq start with 1 increment by 1;
39;T_IDS_APPAUTH_SEQ&删除序列
DROP SEQUENCE T_IDS_APPAUTH_SEQ;
查看触发器
select * from user_triggers;
select * from user_triggers where TRIGGER_NAME=&39;;
39;SYS_C00185187';
禁用约束
ALTER TABLE T_ORGUSER DISABLE CONSTRAINT constraint_SYS_C00185187;
启用约束
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
https://www.cnblogs.com/bingo1717/p/7792134.html
分页排序
　select rownum rn ,a.* from USER_INFO a order by A.USERAGE desc;
SELECT * FROM
A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
oracledt = session.query;
mysqldt = session.query;
create sequence HIBERNATE_SEQUENCE start with 1 increment by 1;
进程报错maximum number of processesexceeded ：
sqlplus /nolog
conn /as sysdba;
show parameter processes;
alter system set processes = 2000scope = spfile;
shutdown immediate;
startup;
—————————————————————————————————————-
select * from dba_directories;
SYS DATA_PUMP_DIR/opt/oracle/admin/orcl/dpdump/
sqlplus saiwen_imp/saiwen_imp
将 ids-2.0.oracle.dmp 文件上传到 /opt/saiwentech/imp_oracle/data目录，进入容器（不需要登录oralc）执行导入导出命令。
导入ids-2.0.oracle.dmp
cp /opt/dbdata/local/ids-2.0.oracle.dmp /opt/oracle/admin/orcl/dpdump/
impdp saiwen_imp/saiwen_imp DIRECTORY=DATA_PUMP_DIR DUMPFILE=ids-2.0.oracle.dmp REMAP_SCHEMA=saiwen_imp:saiwen_imp
— impdp account/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=ids-2.0.oracle.dmp REMAP_SCHEMA=form:to
导出ids-2.0.oracle.dmp
expdp saiwen_imp/saiwen_impschemas=saiwen_imp dumpfile=ids-2.0.oracle.dmp directory=DATA_PUMP_DIR;
cp /opt/oracle/admin/orcl/dpdump/ids-2.0.oracle.dmp /opt/dbdata/local/
导入 Iframework_V4.2_scott_exp.dmp
cp /opt/dbdata/local/Iframework_V4.2_scott_exp.dmp /opt/oracle/admin/orcl/dpdump/
imp saiwen_imp/saiwen_imp BUFFER=64000 FILE=/opt/oracle/admin/orcl/dpdump/Iframework_V4.2_scott_exp.dmp FROMUSER=SCOTT TOUSER=saiwen_imp
说明： DATA_PUMP_DIR 为oralce创建的目录，可用如下命令查询：
select * from dba_directories;
登录
sqlplus /nolog
conn / as sysdba
sqlplus saiwen_imp/password
——————————
【oracle笔记】以上就是朝夕生活（www.30zx.com）关于“oracle笔记”的详细内容，希望对大家有所帮助！

oracle笔记

猜你喜欢