Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the maintenance instructions for Oracle data. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1. SQL

1.SQL-Structured Query Language structured query language, database query and programming language, released by the ISO Standardization Organization.

2.SQL is a set-oriented operation, which only describes the requirements and does not consider the process. DBMS is responsible for solving how to complete it.

3.SQL includes DDL language and DML language

① create table, alter table, drop table and truncate table are DDL languages.

② insert, update, delete and select are DML languages.

Second, the most commonly used operators and functions

1. Arithmetic operator: +, -, *, /

two。 Assignment operator: =

3. String hyphen: |

4. Modular operation (remainder operation) function: mod (divisor, divisor).

5. Logical operator

Operator

Meaning

AND

Returns TRUE when concatenating two Boolean expressions and both are TRUE.

OR

Returns TRUE when two Boolean expressions are concatenated and one of them is TRUE.

NOT

Reverse any Boolean expression.

IN

The Operand equals one of the expressions list and returns TRUE.

BETWEEN

Returns TRUE when the Operand is within a range.

LIKE

Returns TRUE when the Operand matches the string pattern.

EXISTS

Returns TRUE when the query result is not empty.

Third, SQL wildcards

String wildcard

Wildcard character

%

The specified position has zero or more characters.

_

The specified position has 1 character.

Example:

① searches for all names that start with 'tom', and you can specify the search criteria LIKE' tom%'

② searches for records with the third character'k'in the title of the book. You can specify the search condition LIKE'_ _ k%'

③ searches for records that include 'West' in place names, and you can specify the search condition as LIKE'% West%'.

IV. Select statement

1.select statements are SQL statements that extract records from a relational database, and SELECT statements do not update any data in the table.

The 2.select statement can be a simple "select * from table name", querying all fields of all records from a table or view, or appending a large number of query clauses.

For example: select [distinct] from

[where]

[group by]

[having]

[order by [desc | asc]]

[for update]

3. You can use the direct values of fields or the calculated results of column values in the list of result fields, query conditions, and grouping filters in the select statement.

5. Insert statement

The 1.insert statement adds a new row to the table in the following syntax format:

Insert into table_or_view [(column_list)] values (value_list)

The 2.insert statement adds multiple new rows to the table in batch, in the following syntax format:

Insert into table_or_view [(column_list)] select clause

The 3.insert statement inserts one or more rows into the specified table or view. Column_list is a list of column names separated by commas to specify the column for which the data is provided. If not specified, all columns in the table or view will receive data. When column_list does not specify all the columns in the table or view, the default value (if a default value is defined for the column) or NULL is inserted into any column that is not specified in the list. All columns that are not specified in the list must allow null values or be assigned default values. The 4.insert statement does not specify values that identify type columns because the Oracle database instance generates values for those columns.

VI. Update sentence

The 1.update statement can change the data values of a single row, row group, or all rows in a table or view. UPDATE statements that reference a table or view can change data in only one base table at a time.

Update table_or_view set column_name = {expression | DEFAULT | NULL}, column_name = {expression | DEFAULT | NULL},. Where search_condition

The 2.update statement includes the following main clauses:

① set: a comma-separated list of columns to update and new values for each column, in the format column_name = expression. The value provided by an expression contains multiple items, such as constants, values selected from columns in other tables or views, or values calculated using complex expressions.

② where: specifies a search condition that defines the rows in the source table and view that can provide values for expressions in the SET clause.

VII. Delete statement

The ① delete statement deletes one or more rows in a table or view in the form of delete syntax:

Delete table_or_view where search_condition

The ② parameter table_or_view specifies the table or view from which you want to delete rows. All rows in table_or_view that meet the WHERE search criteria will be deleted. If no WHERE clause is specified, all rows in the table_or_view are deleted.

③ any tables that have deleted all rows remain in the database. The delete statement deletes only rows from the table, and to delete the table from the database, you can use the drop table statement.

VIII. Merge statement-merge record line statements

The merge statement updates the data in the target table with the data in the source table, that is, fields that can be updated or inserted by setting the specified matching criteria.

IX. Truncate statement

The 1.truncate statement belongs to the DDL statement

Function: delete all rows in the table without recording the operation log.

2.truncate table is functionally the same as a delete statement without a where clause; however, truncate table is faster and uses fewer system and transaction log resources.

Syntax: truncate table table_name

Example: delete all records without recording the operation log truncate table report.

This is the end of this article on "what are the maintenance instructions for Oracle data". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.