Oracle basic commands

 

Set linesize 300; SET the Row Height

Set pagesize 50; SET the number of entries displayed on each page

Conn system/manager as sysdba; Log On AS SYSTEM

Show user; displays the current USER

SELECT * FROM tab; displays all tables of the current database

DESC table name; displays the table structure

Clear scr; CLEAR screen

/Last Query

 

 

String functions:

UPPER () converts all letters to uppercase select upper (ENAME) from emp;

LOWER () converts lowercase letters to select lower (ENAME) from emp;

INITCAP () converts all the first letters to the size, and the other letters to the lower case select initcap (ENAME) from emp;

CONCAT () string connected select concat ('hello', 'World') from dual;

SUBSTR () truncates the string select substr ('hello', 1, 3) from dual;

LENGTH () returns the string LENGTH. select length ('hello') from dual;

REPLACE () string replace select replace ('hello', 'l', 'x ');

 

 

Numeric Functions

ROUND () rounded to ROUND (789.536) --- [790] ROUND (789.536, 2) --- [789.54]

TRUNC () truncates decimal places TRUNC (789.536) --- [789] TRUNC (789.536, 2) --- [789.53]

MOD () modulo select mod (10, 3) from dual; --- [1]

 

 

Date Functions

SYSDATE: extract the current date

MONTHS_BETWEEN () is used to obtain the number of months in the specified date range.

ADD_MONTHS () on the specified date plus the specified number of months SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 5), 'yyyy-mm-dd') from dual;

Which date is next today next to NEXT_DAY ()?

LAST_DAY () is used to obtain the date of the last day of the given date.

 

 

Conversion functions

TO_CHAR () converts a number to a string SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd') from dual;

TO_NUMBER () converts a string to a number SELECT TO_NUMBER ('2017-01-11 ') from dual;

TO_DATE () converts a string to the date SELECT TO_DATE ('1970-12-31 ', 'yyyy-mm-dd') from dual;

 

Common functions

NVL () converts a specified NULL value to the specified content (the Null value must be NVL) select nvl (NULL, 0) from dual;

 

DECODE ()

 

 

 

Common data types:

NUMBER (M) -- INT | NUMBER (M, N) -- float m: indicates the integer N: indicates the NUMBER of decimal places

VARCHAR () | VARCHAR2 () indicates a string with a length limit of 255 characters.

DATE () indicates the DATE type.

CLOB indicates a large object (meaning: Text data can generally store 4 GB of text)

BLOB indicates a large object (indicating that binary data can generally store 4G text movies, songs, and images)

 

 

 

Create a table

 

Create table myemp as select * FROM emp CREATE a copy of the emp TABLE

 

Create table table_name (

Column_name1 DATA_TYPE [DEFAULT]

Column_name2 DATA_TYPE [DEFAULT]

Columb_name3 DATA_TYPE [DEFAULT]

); Create a new table

 

 

Delete table

Drop table table_name; delete a TABLE

 

Modify Table

Alter table table_name ADD (column_name DATA_TYPE DEFAULT); ADD a column

 

Alter table modify (column_name DATA_TYPE DEFAULT); MODIFY columns (type length can only be increased, not less)

 

RENAME oldTable TO newTable RENAME a table

 

Truncate table table_name truncate table (no logs are generated)

 

 

Data constraints

I. Primary Key constraints:

1. primary key (system allocation constraints)

Create table person (

Pid NUMBER (18) primary key,

Name VARCHAR2 (20) NOT NULL

);

 

2. primary key (specify the constraint name)

Create table person (

Pid NUMBER (18) primary key,

Name VARCHAR2 (20) not null,

CONSTRAINT person_id_pk primary key (pid)

);

 

Alter table person add constraint person_pid_pk primary key (pid); Modify the person TABLE and ADD the person_pid_pk primary key to the TABLE

 

2. Non-NULL constraint (not null)

1. create table person (

Pid NUMBER (5 ),

Name VARCHAR2 (20) not null,

CONSTRAINT person_pid_pk primary key (pid)

);

 

2. alter table person MODIFY (name VARCHAR2 (20) not null );

 

 

 

 

Iii. UNIQUE Constraint)

1. create table person (

Pid NUMBER (5 ),

Name VARCHAR2 (20) not null unique,

);

 

2. create table person (

Pid NUMBER (5 ),

Name VARCHAR2 (20) not null unique,

CONSTRAINT person_name_uk UNIQUE (name)

);

 

3. alter table person add constraint person_name_uk UNIQUE (name); Modify the UNIQUE CONSTRAINT

 

Iv. CHECK Constraints)

1. create table person (

Pid NUMBER (5 ),

Name VARCHAR2 (20) not null unique,

Age NUMBER (2) not null check (age BETWEEN 0 AND 100 ),

Sex VARCHAR2 (2) not null default 'male' CHECK (sex IN ('male', 'female '))

);

 

2. create table person (

Pid NUMBER (5 ),

Name VARCHAR2 (20) not null unique,

Age NUMBER (2) not null,

Sex VARCHAR2 (2) not null default 'male ',

CONSTRAINT person_age_ck CHECK (age BETWEEN 0 AND 100 ),

CONSTRAINT person_sex_ck CHECK (sex IN ('male', 'female '))

);

 

3. alter table person add constraint person_age_ck CHECK (age BETWEEN 0 AND 100 );

Alter table person add constraint person_sex_ck CHECK (sex IN ('male', 'female '));

 

 

5. Primary-foreign key constraint (foreign key)

1. create table book (

Bid NUMBER (3) not null,

Person_id NUMBER (5) not null,

Name VARCHAR2 (50) not null,

Price NUMBER (5, 2) not null,

CONSTRAINT person_book_personid_fk foreign key (price) REFERENCES person (pid) ON DELETE CASCADE

);

 

 

6. Delete Constraints

Alter table table_name drop constraint constraint_name Delete the constraint_name CONSTRAINT according to the provided table_name TABLE

 

 

 

ROWNUM Oracle each table has a ROWNUM Column

 

 

View Management:

Create view view_name as select * FROM emp; CREATE a VIEW

 

Drop view view_name Delete VIEW

 

Create or replace view view_name as select... create or replace a VIEW

 

Create or replace view view_name as select ....

With check option, you cannot update the column conditions when creating a view.

With read only allows the view to READ

 

Sequence management:

Create sequence myseq;

Increment by 2 Specify the step create sequence myseq increment by 2; step size: 2

Start with 0 specifies the START position

MAXVALUE 10 specifies the maximum value.

CYCLE while

 

Drop sequence myseq; delete a SEQUENCE

 

Synonym:

Create synonym name FOR username. Table Name; CREATE SYNONYM

Create synonym emp for scott. emp;

Drop synonym emp;

 

 

User Management:

 

Create a user:

Create user Username identified by password create user and password

Create user lixing identified by lixing create user and password

 

User authorization:

GRANT permission 1, permission 2... TO user name authorization

Grant create session to lixing; CREATE session permissions for lixing users

Grant connect, resource to lixing; GRANT the connect resource permission TO the lixing user

Grant select, DELETE, update on scott. emp TO lixing; GRANT the permissions TO add, DELETE, and modify the scott. emp table TO the lixing user.

Revoke select, DELETE, update on scott. emp FROM lixing; REVOKE the permissions assigned to the user by the scott. emp table.

 

 

Modify user:

Alter user Username identified by new password; Change Password

Alter user lixing identified by newlixing; change the USER lixing password to newlixing

 

Alter user Username password expire; the USER needs to change the PASSWORD upon First Login

Alter user lixing password expire; requires the USER to change the PASSWORD for the First Login

Alter user lixing account lock; lock user lixing

Alter user lixing account unlock; UNLOCK USER

 

 

 

 

Data backup and Restoration

Database Backup: exp

Database Restoration: imp

This article is from "Li Xin's blog"