Basic concepts of database and Oracle BASIC statement

I. Classification of databases

Usually according to the characteristics of the data model, the traditional database system is divided into three kinds of mesh database, hierarchical database and relational database.

1. Mesh Database

As the name implies, the mesh database adopts the mesh data model with the record type as the node, and it is a kind of navigation (Navigation) database-The user should not only indicate the object (data) to be accessed but also specify its access path when manipulating the database.

The mesh database model is a natural simulation for both hierarchical and non-hierarchical information, and the network DBMS is more common than the hierarchical DBMS before the relational database appears. In the history of the database, the mesh database occupies an important position.

2. Hierarchical database

followed by a network-based database, it uses a hierarchical data model (similar to a "tree" in data structures) to simulate things organized hierarchically in the real world. Hierarchical database management system is also recorded to access data, hierarchical data model of the most basic data relationship is the basic hierarchical relationship, which represents two records between a one-to-many relationship, also known as parent-child relationship (Parent-child RELATIONSHIP,PCR). There is only one record type with no parents in the database, called the root node. Other record types have and have only one parent.

The advantages of the hierarchical data model are simple structure, clear hierarchy, easy to understand, and the ability to provide good integrity support. Its disadvantages include: the processing of non-hierarchical links such as many-to-many linked data is cumbersome, can only be achieved by introducing redundant nodes or virtual nodes; the query sub-nodes must be accessed through the parent node, that is, from the root, which reduces the efficiency of data access, and the hierarchical database has more restrictions on data insertion and deletion.

3. Relational databases (Relationship database, RDB)

The two-dimensional table structure is used to store and manage data, and the dependence of data between tables and tables is defined. It can be simply understood that the table structure in the relational database corresponds to the data tables often used in our daily life (such as those in office Office software Word/excel), and of course the abstract data table structure in the relational database can support more data types and attach the required constraints. It also conforms to the essence of technology from the real world and serves the real world production activities.

The relational database uses the Structured Query Language (structured query language,sql) as the bridge between the client program and the database server-the client sends the SQL instruction to the server side, the server executes the relevant instruction and returns the result of its query. The relational model has simple structure, single concept and easy to learn and use. The query efficiency of relational model is often inferior to that of non-relational model, which needs to be optimized to improve performance, which will increase the burden of users, and have higher requirements for the overall knowledge of users in the database.

II. Classification of SQL statements

According to its function, Oracle supports SQL directives into data manipulation language statements, data definition language statements, transaction control statements, session control statements, and several other types.

1. DML

Data manipulation language statements (manipulation LANGUAGE,DML) are used for data retrieval and update operations.

(1) SELECT

The order of execution and main characteristics of each clause:
1. FROM clause
You must specify the query destination table.
2. WHERE clause
Optional, limit the query condition/pre-filter the records that do not meet the criteria, by default, do not filter.
3. GROUP BY clause
Optional, explicitly set up grouping, default does not group/all the data in the table as a set of processing.

4. HAVING clause

Optional, filter the results of the grouped query, by default, do not filter.

5. SELECT clause
You must perform a query operation/extract some content that matches the condition record.

6. ORDER BY clause
Optionally, the query results are sorted by default in the physical storage/Add order of the records.

The wildcard characters involved in an Oracle fuzzy query are two:% (percent sign)--a wildcard character that represents 0 or more arbitrary characters, _ (underscore)--a wildcard character that identifies a single character, which can match a single character.

The WHERE clause is used to specify the query condition (which filters out rows of records that do not meet the criteria), and the GROUP BY clause is used to specify grouping based on that field, and the ORDER BY clause is used to sort the results after a grouped query (last execution).

It is important to note that when you use a grouping function, the field that appears in the select list, if it is not included in the group function, must also appear in the groups by clause. Conversely, the fields contained in the roup by clause do not have to appear in the select list. In other words, if there is no GROUP BY clause, the field (one-line function) is not allowed to be mixed with the grouping function in the select list.

Also, it is important to note that grouping functions are not allowed in the WHERE clause. Because the WHERE clause is first executed, it is not grouped at this time. You can use the grouping function in the having.

By the way, it is similar to not allowing grouping functions in the WHERE clause, because the alias in the Select field/Expression list is not allowed in the where, GROUP by, and having clauses because of the order of processing.

(2) INSERT

A. Inserting single-line data

B. Data copy between tables

Where subquery is used to specify a subquery that provides data for the insert operation, the number of fields, the type, and the order in which the results are returned must exactly match the target table (or the specified assignment field list).

C. Multi-table insertion

Where subquery is used to specify a subquery that provides data for the insert operation, the number of fields, the type, and the order in which the results are returned must match the target table (Table1,table2 ... ) matches exactly. When a row in the subquery result satisfies a check condition (Condition1,condition2 ... ), the row record is inserted into the corresponding target table (Table1,table2 ... ), the last remaining record (which does not conform to the insert condition of any one branch) is inserted into the Table_else table, and the ELSE into clause is optional (similar to the default branch of the switch branch structure in the Advanced programming language).
The optional parameter all is used to specify that all records in the subquery result set are always in the conditional insert operation-even if a row of records already satisfies the preceding condition and has been inserted into the corresponding data table, the row record will still be used again in the judgment and insert operation of the subsequent branch. That is, it may also be inserted into the corresponding data table of other branches; The parameter first is different-if a row record has already met the previous condition and has been inserted into the corresponding data table, the row record will still not be used again in subsequent insert operations. If this setting is default, all is used by default.

(3) UPDATE

A. Updating data with an expression

B. Updating data using subqueries

(4) DELETE

(5) MERGE

The merge operation is used to implement a data merge--modify or insert the data in the table according to the criteria, perform an update operation if the record to be inserted already exists in the target table, or perform an insert operation. The syntax format is as follows:

Where dest_table is used to specify the target table to which the data is to be merged, Source_table (view,sub_query) is the source table (view or subquery) that provides the data, and the ON clause is used to specify the connection criteria for the data in the merge operation. For each record in the source table, if it finds its corresponding record in the target table (a record that conforms to the join condition), then executes the UPDATE statement of the when matched and then branch, modifies the corresponding record in the target table, otherwise executes when is not matched Then branch of the INSERT statement that inserts the current record of the source table into the target table.

2. DDL

Data definition language,ddl is used to define the format and morphology of data, such as defining database objects such as data tables, views, and indexes.

Database objects are the basic units of information storage, organization and management in a database, including tables, constraints, views, indexes, sequences, stored procedures, triggers, database users, and many other common Oracle database objects

(1) Building a table

The list of field names for the new table can be defaulted by default, using the field names or aliases from the results in the subquery. If an expression appears in the Query field list in a subquery, you should specify its expression alias, or you can only display

Type specifies the field name of the new table (because the expression cannot be used by default as the field name for the new table.)

Create temporary table: used to hold temporary data, according to its timeliness of saving data can be divided into transaction-level temporary tables and session-level temporary tables. Temporary tables do not have problems locking data when DML operations exist.

Where you create a transaction-level temporary table using the "on commit DELETE Rows" option, and you create a session-level temporary table using the "on commit PRESERVE rows" option.

(2) Modify table structure

A. Adding a field

B. Modifying an existing field property

C. Deleting a field

D. Disable the field:

Use the SET unused clause in the ALTER TABLE statement to make the field in the table useless and to hide/disable the field, whose primary purpose is to delete the field directly during peak business hours (drop COLUMN) may be risky, first temporarily through set Unused hides the field, and then actually deletes it when the system is idle in the future.

(3) Delete a table

(4) Clear the data in the table (TRUNCATE table)

The difference between truncate and the previous delete operation is that delete is a DML operation, can be rolled back, and truncate is a DDL operation, so it is more efficient once the execution is irrevocable; Delete operation can choose to delete all or part of the data in the table. The truncate operation can only delete all the data in the table, and if the data in the table is no longer used, but the structure of the table needs to be preserved, you can use the TRUNCATE TABLE operation, or you can use the drop table operation to remove the table completely if the structure of the table is required.

(5) Renaming a table

Data Dictionary
Tables in an Oracle database can be categorized into user-defined tables and data dictionary tables: User-defined tables are created and maintained by the user themselves, and the data dictionary table is a set of tables that are automatically created and maintained by the Oracle database, which holds information about the database itself. In this section we briefly introduce the knowledge of data dictionaries:
A data dictionary is the core of an Oracle database and is used to describe a database and all its objects. A data dictionary consists of a series of read-only tables and views that are owned by the SYS user and are maintained by Oracle Server and include the following:
-Physical and logical structure of the database
-Definition and spatial allocation of objects
-Integrity constraint conditions
-User
-Character
-Permissions
-Audit Records
The contents of the data dictionary are dynamically and automatically updated, and the user can access them through the SELECT statement. Data dictionary tables/views can be divided into three main categories:
-DBA-The object information contained in all scenarios (viewers need DBA authority)
-all– All object information that the current user can access (including objects in other scenarios that have access)
-user– object information for current user scenarios
For example:

3. DCL

Data Control LANGUAGE,DCL is used to authorize or revoke permissions for users, or to use roles to implement volume or revoke permissions on users, which is less useful at the application development level.

4. Transaction control Statements

Transaction control statements (Transaction control Statement) are used to implement database transaction management.

(1) Submit a transaction

The so-called commit transaction is to end the operation of the current transaction and confirm the data modifications (making it permanent and irrevocable). You can use the commit command to commit a transaction.

The database programming interface supports manual submission and automatic submission of two modes of operation.

A. Manual submission

In manual commit mode, when a commit, DDL, or DCL statement is encountered, or the client program exits normally (such as Sqlplus), the current transaction is committed (committing all currently made modifications).

B. Auto-commit

In autocommit mode, each statement (even a DML statement) is treated as a separate transaction-a sentence is executed, an auto-commit sentence, which is equivalent to appending a commit statement after each DML statement. Autocommit mode affects the performance and transaction logic of your application, and automatic commits cannot be used if the application requires transactional integrity.

SQL plus executes the SQL statement by default to non-auto-commit, or you can explicitly set whether to commit automatically by using the following directives:

The above settings are only valid for this database connection session and will revert to their default settings after SQL Plus is restarted. You can also view the current submission method by executing the following command:

If the returned result "autocommit OFF" is not automatically committed, such as return "Autocommit IMMEDIATE" is automatically committed.

Data state before commit/rollback
-Changes to the state of the data in the current transaction can be resumed;
-The results of DML operations in the current transaction are only visible to the current user (session) and other users (sessions) do not see changes in the state of the data in the current transaction until the current transaction has ended;
-The record row that is involved in the DML statement in the current transaction is locked (record-level lock), during which other users (sessions) can only read but not modify the operation;
-The data table that is involved in the DML statement in the current transaction is locked (table-level lock), during which other users (sessions) do not allow the table's structure to be modified (add or remove fields).

Data state after transaction commit
-The modification of the data in the transaction shall be permanent and irrevocable;
-Permanent loss and unrecoverable status before data modification;
-All users (sessions) will see the results after the operation;
-Record lock/table level lock is released, other users (sessions) can modify these records, or modify the structure of the table;
-The Save Point (savepoints) in the transaction is cleared;

(2) rolling back a transaction

Rolling back a transaction is the end of undoing the data modifications made by the current transaction, restoring the data to the state before the start of transactions, releasing the associated record and table-level locks, and clearing the savepoint within the rollback range (see below). The rollback of a transaction can be achieved using the rollback directive.

(3) Save point
If you want, you can also use the savepoint in a transaction (savepoint) to create a tag at a key point in the current transaction, and in the future you can roll back to the specified token (savepoint) to implement a partial rollback of the transaction. Save points can have more than one. For example: SavePoint p1; ROLLBACK to P1;

(4) Conceptual knowledge

A. What is a transaction? What is a lock?

A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails, the entire operation is failed, and later the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass acid testing, that is, atomicity, consistency, isolation, and persistence.

Lock: In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

B. What are the isolation levels for transactions? (Acid, for short)

Atomicity (atomicity), or indivisibility, in which a transaction is either fully executed or not executed;

Consistency (consistency) or string, the execution of a transaction makes the database transition from a correct state to another correct state;

Isolation (isolation), which does not allow any changes to the data to be made available to any other transaction until the transaction is properly committed;

Persistence (durability), after the transaction is committed correctly, its results are persisted in the database, even if there are other failures after the transaction commits, the transaction results are saved

C. A running transaction will end up running in the following cases:

-Encountered execution commit statement COMMIT TRANSACTION
-Encountered rollback statement rollback
-encountered DDL or DCL statements
-User disconnects from the database (session end)
-When the database system crashes

5. Session Control statements

Session control Statement is used to dynamically modify the properties of the current user session and is rarely used at the application development level.

III. Functions in Oracle

Oracle functions can be divided into two categories: single-line function and multi-line function.

1. Single-line function

A single-line function is a function that each row of input records can get a row of results (output a row of results), such as querying the employee Information table, according to which the Payroll field calculates the taxable amount of each employee, and how many rows of records should be processed.

According to its function, single-line function can be divided into five classes: character function, numerical function, date function, conversion function and general function.

(1) Character function

(2) Numerical functions

(3) Date function

(4) Conversion function

(5) General functions

A.NVL(EXP1, EXP2) is used to convert a null value to the specified specific value-the value of the parameter/expression EXP1 is evaluated first, and if its value is null, the value of EXP2 is returned, otherwise the value of EXP1 is returned.

B. Thedecode function is used to implement a multi-branch structure with syntax formatting:

The logic is that the value of the first expression (col|expression) is computed first, followed by the search word expression (search1, Search2,... ), if the match returns the corresponding result (RESULT1, result2,.. ); Returns the value of the expression default if none is matched, or null if no default expression is specified.

C. The case expression is also used to implement a multi-branch structure, similar to the function of the Decode function, with the following syntax:

For example:

2, grouping function (also multiprocessing line function)

A multiline function is a multi-line input record that only outputs a single row of results, such as the average salary of all employees in the Employee Information table, or the payroll, and multiple rows of records (multiple employees) can only get one statistic result.

(1) NULL is worth handling

All grouping functions except count (*) ignore null values.

The AVG (COMM) effect is equivalent to sum (COMM)/count (comm) instead of sum (COMM)/count (*).

If the parameter of the grouping function is an expression (not a single field), the null value in the expression result is ignored, for example:
SELECT sum (SAL), SUM (comm), SUM (Sal+comm), sum (SAL) +sum (comm) from EMP;
where sum (Sal+comm) counts the total income of employees with non-null wages and benefits (if there is a null value in Sal, Comm, the row is ignored)

There is no limit to the nesting level of a single function, grouping functions can be nested using up to two layers, because the query results (multiple values) of the inner-layer grouping function can only get a single value after the outer grouping function is processed, and no further packet processing is necessary.