List common Oracle commands

The following articles mainly introduce common Oracle commands, including Oracle data types, views, and instances. If you are a beginner in common Oracle commands, if you know more about the applications of common Oracle commands, click the following article.

Oracle Data Type:

 
 

  
  
Create table test1 (name char (10), sex char (1 ));
  
  
Insert into test1 values ('att Beijing', 'F ');
  
  
Create table test2 (name nchar (10), sex nchar (1 ));
  
  
Insert into test2 values ('att Beijing', 'male ');
 
 

Drop table Name of the table to be deleted;

 
 

  
  
Create table test3 (name varchar2 (10), sex varchar2 (2 ));
  
  
Insert into test3 values ('att Beijing', 'F ');
 
 

The inserted value is too large.

 
 

  
  
Insert into test3 values ('tomcat Beijing', 'F ');
  
  
Create table test4 (name varchar2 (10), age number (3 ),
Salary number (8, 2 ));
  
  
Create table test5 (name varchar2 (10), birth date );
  
  
Insert into test5 values ('Tom ', '28-January 1, February-08 ');
  
  
Insert into test5 values ('allen ', sysdate );
 
 

DDL:

Create a table

 
 

  
  
create table scott.test6(  
  
  
eid number(10),  
  
  
name varchar2(20),  
  
  
hiredate date default sysdate,  
  
  
salary number(8,2) default 0  
  
  
)  
 
 

If hiredate and salary are not specified during data insertion, the default value is used.

The following describes Oracle data dictionary in common Oracle commands:

Dba-object information contained in all solutions

All-object information that users can access

User-object information of the User Solution

 
 

  
  
Select * from user_tables;  
  
  
Select * from all_tables;  
 
 

Constraints:

Domain integrity constraints: not null check

Entity Integrity Constraint: unique primary key

Integrity constraints: foreign key

View:

 
 

  
  
Create or replace view v1(eid,name,salary) as select 
empno,ename,sal from emp where deptno = 30; 
 
 

Sequence: sequence

 
 

  
  
Create sequence mysequence1 increment by 1 start
 with 1 nomaxvalue nocycle;  
  
  
Insert into test values(mysequence1.nextval,’tom’);  
  
  
Create sequence student_sequence start with 1
 increment by 1;  
  
  
Insert into student values
(student_sequence.nextval,’john’);  
 
 

Copy data between tables:

 
 

  
  
Insert into dept1(id,name) select deptno,
dname from dept; 
 
 

Instance creation table ID field auto-increment ):

 
 

  
  
create table test2(id char(10) primary key not null,
 name char(10));  
  
  
create sequence test2_sequence increment by 1 start
 with 1 nomaxvalue nocycle;  
  
  
insert into test2 values(test2_sequence.nextval,'john');  
  
  
insert into test2 values(test2_sequence.nextval,'allen');  
  
  
insert into test2 values(test2_sequence.nextval,'candy');  
  
  
insert into test2 values(test2_sequence.nextval,'aaaa');  
  
  
insert into test2 values(test2_sequence.nextval,'bbbbb');  
  
  
insert into test2 values(test2_sequence.nextval,'cccccc');  
  
  
insert into test2 values(test2_sequence.nextval,'ddddd');  
  
  
insert into test2 values(test2_sequence.nextval,'eeeee');  
  
  
insert into test2 values(test2_sequence.nextval,'ggggg');  
  
  
insert into test2 values(test2_sequence.nextval,'jowwwwhn');  
  
  
insert into test2 values(test2_sequence.nextval,'aaaadd');  
  
  
insert into test2 values(test2_sequence.nextval,'ggghhh');  
  
  
insert into test2 values(test2_sequence.nextval,'eeettt');  
  
  
insert into test2 values(test2_sequence.nextval,'wwwttt');  
  
  
select * from test2;  
 
 

View table structure

EDITDATA table name;

Modify Table fields:

Alter table name modify (field name type constraints );

Alter table test modify (addd varchar2 (10) null );

Alter table name add (field name type constraints );

Alter table test add (age varchar2 (5 ));

The above content is a description of common Oracle commands, hoping to help you in this regard.