Oracle Database 04

First, user

1. Create user

CREATE USER QIGX identified by TIGER;

The QIGX here is the account number, Tiger is the password.

2. Assigning Permissions

GRANT connect,resource,dba to QIGX;

CONNECT: temporary user; RESOURCE: a more reliable and formal user; DBA: the database management role with the highest permissions to administer the database.

3. Cancel Permissions

REVOKE DBA from QIGX;

Ii. Database Tables Table

　　

Third, sequence sequence

The sequence is an Oracle-specific object used to generate an automatically incrementing sequence

To create a sequence:

CREATE SEQUENCE Seq_name

INCREMENT by N

START with N

MAXVALUE n| Nomaxvakue 10^27 OR-1

MINVALUE n| Nominvalue

cycle| Nocycle

cache| NOCACHE

Example:

To create a sequence:

CreateSequence seq_empcopy_id START with 1 INCREMENT by 1;

Use sequence:

SELECT seq_empcopy_id. Nextval from DUAL INSERT into Empcopy (Empno,enmane) VALUES (seq_empcopy_id. NEXT, ' TEST ');

To view the status of a sequence:

SELECT seq_empcopy_id. Currval from DUAL;

To delete a sequence:

DROP SEQUENCE seq_empcopy_id;

Iv. Index

Indexes like dictionaries and book directories are created to speed up the search for data. The index has its own dedicated storage space, which is stored separately from the table.

The index type uses the B-tree data structure by default and the data is all centered on the leaf node.

　　

Used in the database to speed up queries against tables, you can quickly locate data by using the fast path access method and reduce the disk I/O.

After the index is created, the name of the index is not used until the user revokes it, but the index automatically works when the user queries.

Two scenarios for index creation:

Automatic: When a primary key or UNIQUE constraint is defined on a table, the Oracle database automatically creates a corresponding unique index.

Manual: Users can create indexes to speed up queries.

To create an index:

CREATE INDEX Index_stu_sanme on t_student (SNAME);

To delete an index:

DROP INDEX Index_stu_sname;

v. Views View

Defined:

A view is a table that is constructed from several basic tables and/or other views.

When a view is created, it is simply the definition of the view that is being stored, that is, retrieving the data query statements dynamically rather than storing the data for the view.

When the user uses the view, the corresponding data is only asked. (views are also referred to as "virtual tables")

Role:

You can restrict access to data by giving users access to specific parts of the table rather than the entire table.

Complex queries can be made simple. After writing a query, it is easy to reuse it without having to know its basic query details.

Provides a different display of the same data.

Create a View

CREATE OR REPLACE VIEW view_stu as (SELECT sno,sname,gender from t_student);

View_stu is the view name

Query view

SELECT * from View_stu;

Delete a view

DROP VIEW View_stu;

Vi. Other

1. Business

A transaction (Transaction) is a sequence of operations. These operations are either done or not, an inseparable unit of work and a logical unit of work in a database environment.

The transaction is to ensure the integrity of the database.

In Oracle, a transaction does not have a start statement. A transaction starts with a DML (Insert, update, and DELETE) statement, concluding the following scenarios:

The user displays the Execute COMMIT statement or ROLLBACK statement fallback.

When executing DDL (Create, Alter, and drop) statement transactions are automatically committed.

The transaction is automatically committed when the user disconnects normally.

The transaction is automatically rolled back when the system crashes or loses power.

A commit indicates that the transaction ended successfully, telling the system that the database is entering a new, correct state, and that all updates to the database are implemented with delivery. Each commit statement can be seen as the end of a successful transaction, and also the beginning of another transaction.

Rollback indicates that the transaction was unsuccessful, telling the system that an error has occurred, that the database may be in an incorrect state, that the transaction's update to the database must be revoked, and that the database should restore the transaction to its original state. Each ROLLBACK statement is also the beginning of another transaction.

Once the commit statement is executed, the current operation of the database is submitted to the database and cannot be revoked later with rollback.

Executing a DDL, DCL statement, or exiting gracefully from Sql*plus automatically executes the commit command.

Status of data before committing or rolling back:

The previous data can be recovered.

The current user can see the results of the DML operation.

Other users cannot see the results of DML operations.

The data being manipulated is locked and other users cannot modify the data.

Status of data after submission:

Changes to the data are permanently written in the database.

The previous state of the data is permanently lost.

All users can see the results after the operation.

Record locks are released and other users can manipulate these records.

Status of data after rollback:

Statement will discard all data modifications

The modified data is returned

Restore data to a previous state

Row-level locks are released

　　　

2, rowID and rownum

ROWID is generated at record creation and is immutable, pointing directly to the storage location on the hardware.

With rowID direct access when the fastest, but also the human can not do.

As long as the record has not been moved, ROWID is unchanged.

RowNum is a pseudo-column, which is not displayed unless specifically specified at the time of the query.

Its main use is to control the number of rows returned by the query

Use only: <,<=

When RowNum and order by are used together, the records that meet the RowNum criteria are selected first, and then sorted, which makes our query difficult.

Example:

SELECT Rowid,rownum,emp.*from EMP;

　　　　　　

SELECT rowid,rownum,emp.* from EMP ORDER by SAL;

　　　　　　

SELECT rowid,rownum,emp.* from WHERE ROWNUM <= 5;

　　　　　　

3. Paging

Show the highest paid money in an EMP table five employee information

Step 1: Show all employees sorted by payroll

SELECT * from EMP ORDER by SAL DESC;

Step 2: Consider step 1 results as a table and query the first 5 data of the table

Select rownum,t.* from (SELECT * from EMP ORDER by SAL DESC) T WHERE ROWNUM <= 5;

4. Import and Export

Import and Export data using exp

Import and export data using PL/SQL Developer

Oracle Database 04