4.SQL Language Basics

4.1language Classification and user mode4.1.1Language Classification

1) Data Query Language

The term retrieves the data in the database, primarily the select statement, which is most frequently used when manipulating the database.

2) Data Manipulation language

The term changes the data in the database, mainly including insert,update , and delete 3 Clause.

3) Transaction Control language

The terminology maintains the consistency of the data, including the 3 Statements of commit,rollback , and savepoint . Where the commit statement is used to commit changes to the database,therollback statement is used to cancel the changesto the databasesavepoint statement is used to set the save point.

4) Data Definition language

Used to create, modify, and delete database objects. For example, you can create a table using the CREATE TABLE statement, Modify the table structure with thealter table statement, anddrop the table.

5) Data Control language

Used to perform permission grant and permission recall operations, mainly including the grant and revoke two commands. Where the grant command is used to grant permissions to users and roles, the revoke command is used to reclaim user and role permissions.

4.1.2User Mode

In an Oracle Database, schema concepts are introduced to facilitate the management of database objects created by users, so that the database objects created by a user belong to that user pattern.

A schema is a collection of database objects. The schema is owned by a database and has the same name as the user, common with SYSTEM mode,SCOTT mode, and so on. To put it simply, the relationship between a pattern and a schema object is a relationship that owns and is owned, that is, the pattern owns the schema object, and the schema object is owned by the pattern.

4.2Retrieving Data4.2.1go to re-query

In a select statement, you can use the DISTINCT keyword to limit the display of non-repeating data in the query results, which is used in the select Before the list of clauses.

such as:select distinct user_id from User_cont;

4.2.2Filter Queries

1) Where to delete the selection.

2) like keyword (ex plus not, negative judgment).

3) in keyword (preceded by not, denotes negative judgment).

4) between keywords (usually using between. and and not...between...and Specify range criteria)

5) is null keyword

4.2.3Group Queries

The purpose of a data grouping is to summarize data or to display summary information for a single row for the entire group, often using group by words in a query result set to group records, often with aggregate functions. Syntax format:

Select columns_list from table_name [where Conditional_expression] GROUP by columns_list

4.2.4Sort Queries

In a select statement, you can use the order by clause to sort the result set, which is after the from clause, by default ascending ASC arranged. Syntax format:

Select Columns_list from table_name

[Where Conditional_expression]

[GROUP BY Columns_list]

Order by{order_by_expression[asc| DESC]}

4.3manipulating Databases

Insert statement:INSERT INTO table_name[(column_name1,column_name2)]...values (values1,values2)

Update statement:update table_name set COLUMN_NAME1=EXPRESS1 ... Where condition

Delete statement:DELETE from table_name [WHERE condition]

4.4Transaction Processing4.4.1Transaction Overview

A transaction is a logical unit of work composed of a series of statements. Batch processing, such as transactions and stored procedures, is somewhat similar, usually to "encapsulate" one or more statements in order to complete a certain business logic, so that they appear in a logical boundary with other statements. And form a relatively independent unit of work.

There are only two results of transaction processing: one is that in the process of transaction processing, if some kind of error occurs, the whole transaction is rolled back, all operations on the data are undone, the transaction is stepping on the database, and can be rolled back at any time when an error is encountered. The other is that if no errors occur and each step succeeds, the entire transaction is committed. It can be seen that the efficient use of transactions can improve the security of data, but also enhance the efficiency of data processing.

Transactions contain four important properties:acid(atomicity, consistency, isolation, and persistence), and a transaction must pass acid.

1) atomicity (Atomic): A transaction is a whole unit of work, and the operations performed on the database are either all executed or revoked. If a statement fails to execute, all statements are rolled back.

2) Consistency (condemoltent): When a transaction is complete, all data must be kept in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. If the transaction succeeds, all data becomes a new state, and if the transaction fails, all data is in its original state.

3) Isolation (Isolated): Changes made by the firm must be isolated from changes made by other things. When a transaction views the data, the state of the data is either modified by another concurrent transaction, or the state after which another transaction modifies it, and the transaction does not view the data in the middle state.

4) Persistence (durability): Once a transaction is committed, the operations on the database are persisted.

4.4.2Transactional Operations

Oracle ends a transaction, either executes a commit statement, or executes a rollback statement.

1) Commit TRANSACTION (commit statement)

Committing a transaction means persisting all the operations of the database to the database, which is usually done using a commit statement. Execution order:

A. Records that the current transaction has been committed within the rollback segment and claims a unique system number (SCN) to uniquely identify the transaction.

B. Start the background log write process (LGWR), write the data in the redo log buffer of the SGA area and the SCN of the current transaction into the redo log file.

The C.oracle server begins releasing the system resources used by the transaction.

D. Display a notification telling the user that the transaction was successfully committed.

2) ROLLBACK TRANSACTION (rollback statement)

Rolling back a transaction means undoing all operations on the database, andOracle uses a rollback segment to store the pre-modified data and record the changes by redo the log. If you want to roll back the entire transaction,the following procedures will be performed internally on theOracle system:

A. Use the data in the rollback segment to undo changes made to the database.

the b.oracle background service process frees the system resources used by the transaction.

C. Display a notification telling the user that the transaction was rolled back successfully.

4.SQL Language Basics