Shulou(Shulou.com)06/01 Report--

1. Oracle

1. Oracle service

1) OracleDBConsoleorcl

Oracle database console service. Orcl is the instance ID of Oracle. The default instance is orcl.

2) OracleOraDb11g_home1TNSListener

Listener service, which is needed only when the database needs remote access.

3) OracleServiceORCL

Database service (database instance) is the core service of Oracle, which is the basis of database startup. Only when this service is started, Oracle database can be started normally.

Boot sequence: OracleOracle_homeTNSListener,OracleServiceORCL,OracleDBConsoleORCL

2 、 sql*plus

1) Common commands

Help [? | topic]

? Fuzzy query / topic specific command

Desc [ribe] queries the data structure of the specified object

Such as: desc scott.emp

Show option displays the value of either the SQL*Plus system variable or the environment variable

Such as: show user

User connection command:

A, conn [ect] conn username / password @ network service name [as sysdba/sysoper]

B. Disc [onnect] disconnects from the current database

C, passw [ord] change the user's password

D, exit disconnects from the database and exits sql*plus at the same time

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

Forget the administrator password:

Sqlplus/nolog

Connect/as sysdba

Alter user sys identified by sys

Create a new user: a user with DBA privileges executes

Create user test identified by 123456

Default tablespace users

Temporary tablespace temp

Quota 3m on users

/ / user role

Connect Role (connection role): temporary users, especially those who do not need to create tables

Resource Role (Resource role):

Provide users with additional permissions to create their own tables, sequences, procedures, triggers, indexes, and clusters. You can only create entities, not database structures

Dba Role (Database Administrator role): with all system permissions, only DBA can create database structures

User authorization:

Grant sys_privi | role to user | role | public [with admin option]

Sys_privi: indicates Oracle system permissions, which are a set of agreed reserved words.

Role: role.

User: a specific user name, or some column of user names.

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

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

For example, grant connet,resource to test; grants user test access to connect and develop the system.

Revoke the authority:

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: indicates the permissions of an object, which can be alter, execute, select, update, insert, etc.

Role: role name

User: authorized user name

With admin option: indicates that the authorized user can grant system permissions to other users

With hierarchy option: authorize users on sub-objects of the object (and then create views on the view)

For example, grant select,insert,delete,update on scott.emp to test; grants permissions of select and insert table soctt.emp to user test.

Take back the permissions of the object

Revoke obj_privi | all on schema.object from user | role | public cascade constraints

Cascade constraints: indicates that the permissions related to the association relationship have also been revoked

For example, revoke delete,update on scott.emp from test; revokes update and delete of scott.emp table from test user

Modify the user: create a new user and replace the create keyword with alter

Unlock locked users:

Alter user test account unlock

Delete a user:

Drop user user name [cascade]

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

3) Common data types

A, character type

Char (n byte/char) defaults to 1 byte n up to 2000 with blanks at the end to reach the specified length

Varchar2 (n byte/char) maximum length must be specified. If the maximum length exceeds the specified length, an error will be reported.

B, numeric type

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

Binary_float 32-bit single precision floating point type

Binary_double 64-bit double precision floating point type

C, time type

Date has no minute, second and time zone.

Timestamp has no time zone

Timestamp with local time zone uses database time zone

D, large object type

Blob binary files, supporting transaction processing

Clob byte character data, supporting transaction processing

Nclob unicode data, supporting transaction processing

Bfile lob address points to binaries on the file system, maintains directories and file names, does not participate in transactions, and supports read-only

4) sequence sequence-mainly used to provide primary key values

A. Permissions required to create a sequence:

Create sequence or create any sequence

B, grammar:

CREATE SEQUENCE sequence / / create sequence name

[INCREMENT BY n] / / the sequence value increasing is n. If n is positive, it is incremented. If it is negative, the default is 1.

[START WITH n] / / starting value. Incrementing defaults to minvalue decreasing is maxvalue.

[{MAXVALUE n | NOMAXVALUE}] / / maximum

[{MINVALUE n | NOMINVALUE}] / / minimum

[{CYCLE | NOCYCLE}] / / Loop / No Loop

[{CACHE n | NOCACHE}]; / / allocate and store it in memory

Such as:

Create sequence seq_test

Increment by 1-one at a time

Start with 1-count from 1

Nomaxvalue-do not set the maximum

Minvalue 1-minimum

Nocycle-- accumulate all the time, not cycle

Nocache;-No buffer is built

C, sequence usage

Currval: returns the current value of sequence

Nextval: returns the next value of sequence

D, points for attention

Currval always returns the value of the current sequence. Currval can be used only after the first nextval initialization, otherwise an error will occur.

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

Using cache may jump, for example, if the database suddenly fails to down, the sequence in cache will be lost. You can use nocache to prevent this situation when defining sequence.

E. Use triggers to achieve self-growth

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) pagination

A, pseudo column

The rowid line identifier and the rownum line number cannot see these two columns and are only used within the database.

B, three-stage paging

Select * from (

Select rownum rn, t.* from (

Select * from t_users

) t where rownum (page number-1) * lines per page

6) Common functions

Concat (XQuery Y): concatenate strings X and Y

Length (X): returns the length of X

Substr (XQuery start [, length]) returns the substring of X, starting at start, intercepting length characters, default length, default to the end

Ltrim (X [, TRIM_STR]): truncate the trim_str string to the left of X and truncate spaces by default

Rtim (X [, TRIM_STR]) truncates the trim_str string to the right of X

Absolute value of abs (X) X

Ceil (X) is greater than or equal to the minimum integer value of X

Floor (X) is less than or equal to the maximum integer value of X

Round (X [, Y]) X is rounded in position Y

The Y Power of power (Xmai Y) X

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

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

Extract (fmt FROM d) to extract a specific part of the date. Fmt: YEAR, DAY, etc. Where YEAR, MONTH, and DAY can match DATE types or TIMESTAMP types, but HOUR, MINUTE, and SECOND must match TIMESTAMP types.

To_char (d | n [, fmt]): converts dates and numbers into formatted strings. Fmt is a format string

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

To_number (X, [, fmt]) converts a string into a number in fmt format

Nvl (X _ ther _ value): if X is empty, return value, otherwise return X

Nvl2 (x _ value _ 1m _ value2) returns value1 if x is not empty, value2 otherwise

3. Jdbc connects to the database-the same as 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.