1.查看所有序列
select * from all_sequences;
2.创建序列
create sequence HST_SEQ_MESSAGE_CONSULTING --序列名
minvalue 1 --最小从1开始
NOMAXvalue --没有最大的上限
start with 1 --从1开始
increment by 1 --每次增加1
nocache;
3.创建索引
create unique index "HSTSOUTH"."HST_SEQ_MESSAGE_CONSULTING" on "HSTSOUTH"."HST_MESSAGE_CONSULTING"("hmcuuid");
4.删表
drop table HST_MESSAGE_CONSULTING(表名);
5.模糊查询
select * from HST_NEWS where TITLE like '%心(查询的关键字)%' and DEPTMENTID = 62;
6.提交
commit
7.删除序列
DROP SEQUENCE HST_SEQ_MESSAGE_ADVISORY;
8.删除表数据
delete from HST_MESSAGE_CONSULTING;
9.过滤掉重复
--可以使用group by,也可以使用distinct
10.创建两张带有关联字段的表sql
create table HST_MESSAGE_ADVISORY
(
HMAUUID NUMBER not null,
HWUUUID NUMBER,
HCONTENT VARCHAR2(500),
HFTIME DATE,
AUTH NUMBER,
STATUS NUMBER,
constraint PK_HST_MESSAGE_ADVISORY primary key (HMAUUID)
);
/*==============================================================*/
/* Table: HST_MESSAGE_CONSULTING */
/*==============================================================*/
create table HST_MESSAGE_CONSULTING
(
HMCUUID NUMBER not null,
HMAUUID NUMBER,
HWUUUID NUMBER,
STARTTIME DATE,
TIMEEND DATE,
SPEED INTEGER,
STATUS INTEGER,
HWCUUID NUMBER,
constraint PK_HST_MESSAGE_CONSULTING primary key (HMCUUID)
);
alter table HST_MESSAGE_CONSULTING
add constraint FK_HST_MESS_REFERENCE_HST_MESS foreign key (HMAUUID)
references HST_MESSAGE_ADVISORY (HMAUUID);
(
HMAUUID NUMBER not null,
HWUUUID NUMBER,
HCONTENT VARCHAR2(500),
HFTIME DATE,
AUTH NUMBER,
STATUS NUMBER,
constraint PK_HST_MESSAGE_ADVISORY primary key (HMAUUID)
);
/*==============================================================*/
/* Table: HST_MESSAGE_CONSULTING */
/*==============================================================*/
create table HST_MESSAGE_CONSULTING
(
HMCUUID NUMBER not null,
HMAUUID NUMBER,
HWUUUID NUMBER,
STARTTIME DATE,
TIMEEND DATE,
SPEED INTEGER,
STATUS INTEGER,
HWCUUID NUMBER,
constraint PK_HST_MESSAGE_CONSULTING primary key (HMCUUID)
);
alter table HST_MESSAGE_CONSULTING
add constraint FK_HST_MESS_REFERENCE_HST_MESS foreign key (HMAUUID)
references HST_MESSAGE_ADVISORY (HMAUUID);
11.向oracle中导入数据
drop user hstsouth cascade;
create user hstsouth identified by 123456;
grant dba to hstsouth;
grant sysdba to hstsouth;
create directory dir_dp as 'F:\oracle';
Grant read,write on directory dir_dp to hstsouth;
Impdp hstsouth/123456 DIRECTORY=dir_dp DUMPFILE=EXPDP_20161031_ALL.DMP
--1.create tablespace hst datafile 'F:\oracle\hst.dbf' size 100m autoextend on next 32m maxsize 20048m extent management local;
--2.create user hstsouth identified by 123456 default tablespace hst temporary tablespace temp;
create user hstsouth identified by 123456;
grant dba to hstsouth;
grant sysdba to hstsouth;
create directory dir_dp as 'F:\oracle';
Grant read,write on directory dir_dp to hstsouth;
Impdp hstsouth/123456 DIRECTORY=dir_dp DUMPFILE=EXPDP_20161031_ALL.DMP
--1.create tablespace hst datafile 'F:\oracle\hst.dbf' size 100m autoextend on next 32m maxsize 20048m extent management local;
--2.create user hstsouth identified by 123456 default tablespace hst temporary tablespace temp;
12.oracle服务启动监听改为手动
13、oracle异常处理
使用oracle总结
如果oracle连接不上某个数据库的时候,并且报THE NETWORK ADADPTERE错误的时候,用oracle新建数据库连接老是说 THE NETWORK ADAPTER COULD NOT ESTABLISH THE CONNECTION!
答:监听没有启动,命令行用下面的命令启动监听:
lsnrctl start
netca命令是用来进行监听程序配置
如果oracle连接不上某个数据库的时候,并且报THE NETWORK ADADPTERE错误的时候,用oracle新建数据库连接老是说 THE NETWORK ADAPTER COULD NOT ESTABLISH THE CONNECTION!
答:监听没有启动,命令行用下面的命令启动监听:
lsnrctl start
netca命令是用来进行监听程序配置