Getting Started with Oracle databases

First, Oracle

1. Oracle Services

1) ORACLEDBCONSOLEORCL

Oracle Database Console service, ORCL is the instance identity of Oracle, and the default instance is ORCL.

2) Oracleoradb11g_home1tnslistener

Listener service, the service is only required if the database requires remote access.

3)ORACLESERVICEORCL

A database service (DB instance), which is the Oracle Core service that is the basis for starting a database and only if the service is started, the Oracle database can start normally.

Boot order: ORACLEORACLE_HOMETNSLISTENER,ORACLESERVICEORCL,ORACLEDBCONSOLEORCL

2, Sql*plus

1) Common commands

help[?| Topic

？ Fuzzy query/topic specific commands

Desc[ribe] Querying the data structure of a specified object

such as: Desc scott.emp

Show option displays the value of the Sql*plus system variable or the value of the environment variable

such as: Show user

User Connection command:

A, Conn[ect]conn user name/password @ NETWORK SERVICE name [as Sysdba/sysoper]

B, Disc[onnect] Disconnect from the current database

C, Passw[ord] Modify the user's password

D, exit disconnects from the database and exits Sql*plus

View all users of the system:

Select Username,account_status from Dba_users;

Query all the tables under the user:

Select table_name from tabs;

Select table_name from User_tables;

Select Tname from tab;

Select?*?from?dba_tables?where?owner= ' SCOTT ';

2) User Management instructions

Forgot admin Password:

Sqlplus/nolog

Connect/as SYSDBA

ALTER USER SYS identified by SYS

Create new user : User with DBA authority to execute

Create user test identified by 123456

Default Tablespace users

Temporary Tablespace Temp

Quota 3m on users;

User Roles

Connect role: Temporary users, especially those who do not need to build a table

Resource role (Resource roles):

Provides additional permissions to the user to create their own tables, sequences, procedures, triggers, indexes, and clusters. You can create only entities, not create a database structure

DBA roles (Database Administrator role): Have all system permissions and only DBAs can create database structures

User Authorization :

Grant Sys_privi | Role to User | Role | public [with admin option]

Sys_privi: Represents the Oracle system permissions, and system permissions are a set of reserved words.

Role: Roles.

User: A specific username, or a user name for some columns.

Public: reserved word, representing all users of the Oracle system.

With admin option: Indicates that permissions can be granted to another user.

such as Grant Connet,resource to test; Grant connection and development system permissions to user test

Revoke permissions:

Revoke Sys_privi | Role from user | Role | Public

such as: revoke resource from Test;

Object permissions:

Grant Obj_privi | All column on Schema.object to user | Role | Public [with GRANT OPTION] | [with hierarchy option]

Obj_privi: Represents the permissions for an object, which can be alter, execute, SELECT, UPDATE, insert, and so on

Role: Character name

User: The username that is authorized

With admin option: Indicates that the grantee can then grant system privileges to other users

With hierarchy option: Authorizing a user on an object's sub-object (the view is re-established on the view)

Example: Grant Select,insert,delete,update on scott.emp to test; Grant user test permission to select, Insert Table Soctt.emp

Revoke object permissions

Revoke Obj_privi | All on Schema.object from user | Role | Public cascade constraints

Cascade constraints: Indicates that the permission for the associated relationship has also been revoked

such as: Revoke delete,update on scott.emp from test; undo the update and delete of the Scott.emp table from the test user

Modify User: Create a new user replace the CREATE keyword with alter

Unlock locked users:

Alter user test account unlock;

To delete a user:

Drop user username [cascade]

Cascade: Cascade Delete option, along with objects owned by the user, to delete

3) Common data types

A, character type

CHAR (n byte/char) default 1 byte n Max 2000 padding space at end to reach specified length

VARCHAR2 (n byte/char) maximum length must be specified up to 4000 above the specified length error

B, Number type

Number (P[,s]) 1-22 bytes

Binary_float 32-bit single-precision floating-point type

Binary_double 64-bit double-precision floating-point

C, Time type

Date no seconds and time zone

Timestamp no time zone

Timestamp with local time zone using the database timezone

D, large object type

Blob binaries, supporting transactional processing

CLOB Byte character data, supports transactional processing

NCLOB Unicode data to support transactional processing

bfile LOB address points to a binary file on the file system, maintains directories and file names, does not participate in transactions, supports read-only

4)sequence sequence ———— primarily used to provide primary key values

A, create the required permissions for the sequence:

Create sequence or create any sequence

B, Syntax:

Create SEQUENCE SEQUENCE//Creating a sequence name

[INCREMENT by N] //increment sequence value is n if n is a positive number increment, if negative, the default is 1

[START with N] ///Start value, increment default is MinValue decrement is MaxValue

[{MAXVALUE n | Nomaxvalue}]//maximum value

[{MINVALUE n | Nominvalue}]//Minimum value

[{CYCLE | Nocycle}] //Loop/No loop

[{CACHE n | NOCACHE}]; //Assign and enter into memory

Such as:

Create sequence Seq_test

Increment by 1--add 1 each time

Start with 1-counting starting from 1

Nomaxvalue--Do not set the maximum value

MinValue 1--Minimum value

Nocycle--keep accumulating, not looping

NoCache --Do not build buffers

C, Sequence use

Currval: Returns the current value of the sequence

Nextval: Returns the next value of sequence

D. Precautions

Currval always returns the value of the current sequence, and only after the first nextval is initialized can you use Currval, or it will be an error

The first value returned by Nextval is the initial value: The subsequent nextval automatically increases the defined increment by value and then returns the added value

Using the cache may be skipped, such as the database suddenly abnormal down, the cache sequence will be lost, you can define sequence, use NoCache to prevent this situation

E. Self-growth with triggers

Create or replace

Trigger trg_customers before insert on test for each row

Begin

Select Seq_test.nextval into:new.id from dual;

End

5) Paging

A, pseudo-column

rowID line identifier and rownum line number do not see these two columns, only used inside the database

B, three-segment paging

SELECT * FROM (

Select RowNum rn, t.* from (

SELECT * FROM T_users

) t where rownum<= page value * Number of rows per page

) where rn> (page 1) * Number of rows per page

6) Common functions

Concat (x, y): Connection string X and Y

Length (x): Returns the lengths of X

SUBSTR (X,start[,length]) returns the substring of X, starting at start, intercepting length characters, default length, and defaults to end

LTrim (X[,TRIM_STR]): Truncate X to the left of the TRIM_STR string, the default truncation of space

Rtim (X[,TRIM_STR]) truncate the right side of X to Trim_str string

Absolute value of ABS (x) x

Ceil (x) the smallest integer value greater than or equal to X

Floor (x) The maximum integer value less than or equal to X

Round (X[,y]) X rounding in the Y position

Power (x, y) X power

Add_months (D,n), on a date D, plus the specified number of months n, returns the calculated new date. D represents the date, and N indicates the number of months added.

Last_day (d), returns the last day of the month of the specified date.

Extract (Fmt from D), extracts a specific part of the date. FMT is: year, day and so on. The year, MONTH, day can match the DATE type, or it can match the TIMESTAMP type, but HOUR, MINUTE, and SECOND must match the TIMESTAMP type.

To_char (D|N[,FMT]): Converts a date and number to a formatted string. FMT is a format string

To_date (X,[,FMT]) converts a string into a date type in the FMT format

To_number (X,[,FMT]) converts a string to a number in the FMT format

NVL (X,value): Returns VALUE if X is empty, otherwise returns X

NVL2 (x,value1,value2) returns value1 if X is not empty, otherwise returns value2

3. JDBC Connection database ———— with MySQL

Driver class: Ojdbc6.jar

Class Name: Oracle.jdbc.driver.OracleDriver

Url:jdbc:oracle:thin:@192.168.0.1:1521:orcl

User:scott

Password:a1s2d3f4

Getting Started with Oracle databases