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

SQL statement: structured query language: Structured Query Language

The oracle database has three levels of structure:

Relational schema, called "basic table"

Storage mode (internal mode) is called "storage file"

Subpatterns are called views (view) tuples are called rows (row) and attributes are called columns (column)

Oracle system

"instance" (instance), database file

User process (user process) and server process (server process)

Other files, such as parameter files (parameter file), password files and archive log files (archived log file)

Physical structure files refer to database files: composed of data files, control files and redo log files.

Instance: instance

Includes the memory area (SGA) and background processes. Starting an instance is to start these processes and memory.

An instance corresponds to a database, and a database can have multiple instances.

There are three connection methods:

Connection refers to the way of communication between the user process and the database server, and a connection can have multiple sessions.

The process is: the user process connects to the server process and then connects to the database instance

1. Host-Based mode: the server and the client are on the same host, and the user is directly connected to the database server.

2. Cpact S mode: the client manages the database server remotely through the network.

3. Three-tier access mode: the user accesses the application server first, and the application server connects to the database server.

SGA memory structure

Shared Pool:

Share SQL or PL/SQL code, consisting of a library cache and a data dictionary cache.

Redo log cache:

When the user performs operations such as INSERT,UPDATE,DELETE, the data changes.

The changed data is written to the redo log buffer before it is written to the database cache.

At the same time, the data before the change is also put into the redo log cache.

In this way, when the data is recovered, oralce knows what needs to be rolled forward and which needs to be rolled back.

Large pools (large pool) and Java pools-optional memory area

Large pool: in the shared server environment, oracle allocates additional space in the shared pool to store session information between user processes and server processes, but most of the user process area UGA will be allocated in the large pool, thus reducing the burden on the shared pool. Large pools are also required as cache space for large-scale input and output and backup processes.

Stream pool (streaming pool)-stream memory, data sharing, can be dynamically adjusted by parameters.

SGA PGA UGA

PGA: process global area, which stores data information and control information of server processes or separate background processes. It is allocated memory as the server process is created, and memory is freed as the process terminates. Unlike SGA, it is not a shared area, but an area specific to the server process, including the following components in the proprietary server (as opposed to the shared server concept) configuration: sort area, session information, cursor state, stack area.

UGA: user global area, which is the concept of shared server mode, refers to the user's session state, this part of the memory session is always accessible, UGA is stored in each shared server can access SGA, so that any server can use the user session data and other information. In proprietary server mode, Yonghua session state does not need to be shared, and there is an one-to-one corresponding relationship between user process and server process, so UGA is always allocated in PGA.

Background process:

System Monitoring process (SMON): database instance recovery. There are three steps: roll forward-open the database-roll back uncommitted things.

Process monitoring process (PMON): the process is responsible for some cleanup when the process fails to learn that a connection exception has occurred.

Roll back uncommitted transactions, release SGA resources occupied by processes, monitor other oracle processes, monitor other oracle background processes, and register newly started instances with oracle TNS listeners.

Database write process (DBWR): dirty data refers to the data in the database that the user has changed but not submitted, because the data file of the database is inconsistent with the data in the database cache, which is called dirty data. The function of the writing process is to write dirty data to the data file under certain conditions.

Why not just submit dirty data? In order to reduce the number of Icano. Dirty data is submitted at one time when the dirty data reaches a certain amount or condition.

Archive log process (ARCH): this is an optional process that is not automatically started when the instance is started, and is used to write data from a full redo log file to an archive log that is used for database recovery in the event of a media failure.

Checkpoint process: checkpoint is an event.

Introduction of SQL statement

It can be divided into five categories according to their functions:

Data query statements: such as select

Data manipulation statement (DML):

For example, INSERT adds records to a table, and UPDATE updates the data in the table, usually used with WHERE conditional statements.

The DELETE statement deletes the data from the table.

Data definition statement (DDL):

CREATE: create database objects, such as tables, indexes, views, etc.

ALTER: change system parameters, such as changing the size of SGA, etc.

DROP: delete an object, such as a table, index, or serial number

RENAME: renames an object.

TRUNCATE: truncate a table

Transaction control statement

COMMIT: used to commit transactions operated by DML statements.

ROLLBACK: used to roll back data that cannot be changed by the DML statement.

Data control statement (DCL):

GRANT: used to grant users privileges to access an object

REVOKE: used to reclaim a user's privileges to access an object

Null (NULL) and null value handling functions:

Null value is a special value, it cannot be said that it does not exist, nor can it be said to be zero. Null values represent a class of undefined values with uncertainty. Of course, the operation of control also has its particularity, because uncertain values can not operate logically or arithmetically with a class of deterministic values. Oracle provides a class of null value handling functions, through which the operation of null values (NULL) is realized.

Grouping function:

AVG and SUM functions

MAX and MIN functions, which can be manipulated by numeric, character and date types

The COUNT function returns the calculated number of rows returned, including blank lines and duplicate rows

GROUP BY sentence

HAVING clause: in grouping functions, you cannot use WHERE clauses to restrict grouping functions, so Oracle designed some restrictions on grouping functions by HAVING clauses.

Nesting use of grouping functions

The usage of SELECT statement for simple query

SELECT * | {[DISTINCT] column | expression [alias],.} FROM talbe

| | indicates the relationship between or, and [] indicates optional |

SELECT selects one or more columns

* Select all columns in the table

DISTINCT removes the duplicate values of the species.

Column | expression select the name or expression of the column

Alias sets different headings for the specified column

FROM table specifies the table in which the column to be selected is located, that is, to retrieve the data from that table

SQL > SELECT * FROM SCOTT.EMP; query the data used in the EMP table in SCOTT users

SQL > SELECT deptno,loc from scott.dept query these two columns of deptno,loc in this table

SQL > SELECT * FROM dept WHERE is NULL queries rows with null values in this table

Alias mechanism:

When using the select statement, SQL*PLUS uses the selected column name as the column header and capitalizes, because the column names in the table are designed by database developers or programmers for programming purposes. However, such column headings may not be descriptive and difficult to understand, and Oracle provides column aliases to change the way column headings are displayed.

How to create an Oracle database

1. Use DBCA (Database configuration Assistant)

2. Use the CREATE DATABASE instruction

3. Create a database when you install the database software

Temporary table:

The temporary table is a special table, which is only valid for the current session of the current user. the purpose of creating the temporary table is to make some operations more efficient. The data in the temporary table is only valid for the users of the current session, and it is the private data of the current session. The current session only operates its own data, and there is no contention for data locks, which greatly improves the efficiency of historical table operations.

You can do this for the data in the table: maintain columns, modify columns, and delete columns.

The difference between a deleted table and a truncated table: whether to retain the table structure.

Oracle data dictionary

The data dictionary is created during the creation of the database. It is managed by the oracle database server and stored in the system tablespace. It is a collection of read-only tables and views that provide all the information related to the database. Data dictionaries are made up of two categories.

Base table: the base table cannot be read and must be converted to a readable table through the data dictionary view.

Data Dictionary View: it is a readable data dictionary and is also a data dictionary frequently used by DBA. It consists of the following two types of objects:

Static data dictionary table: it is based on the base table and is easy to read by using a script file called catalog.sql. Such as user_tables,dba_objects, etc.

Dynamic performance view: it exists during the operation of the database. It reads the contents of memory or control files and provides dynamic performance information of the data. Such as: v$logfil, v$database, v$instance and so on.

Three main static data dictionary tables: dba_XXX, all_XXX, user_xx

Query the database object information owned by the user:

User_tables, user_indexes (Index), user_views (View)

The dynamic performance view, which starts with V$, exists only in the running database, and is a set of virtual tables.

V$instance, V$database, V$controlfile, V$log, V$logfil

The concept of views:

View is a virtual table, does not take up storage space, does not store data, only the definition of the view in the data dictionary, the view can be operated through the DML language, but there are certain limitations, because the operation view is ultimately the operation to create the underlying table of the view.

For example, we create a view for each department, so that different departments can complete the query simply by using the view, instead of using multi-table joins and WHERE conditional statements to restrict the query department.

Example: create or replace view research_view

("employee_name", "job", "hiredate", "salary", "dep_name")

As

Select e.ename,e.job,e.hiredate,e.sal, d.dname

From dept d,emp e

Where e.deptno = d.deptno

And d.deptno = 20

With read only

Use the WITH clause of the view: the with read only clause

Delete View: drop view emp_view

Verify that the deletion was successful: select statement

The concept of transactions:

The origin of affairs: an example of bank withdrawals

If user A wants to transfer 10000 yuan to user B from the bank, we consider the behavior of the ATM as a transaction. The implementation steps of the ATM are as follows:

Reduce 10,000 yuan from user A's account.

Add ten thousand yuan from user B's account.

Both of the above steps must be performed successfully, and if there is a problem with either of the two steps, we say that the ATM did not complete the transfer correctly. No one wants to lose ten thousand yuan in his account in vain. At this time, the two execution steps of the ATM are inseparable behavior, either successfully or not (rollback all changed data), the two operations of the ATM can be logically regarded as a complete transaction.

A transaction is a set of logical units of work consisting of one or more SQL statements. A transaction can perform one or more operations on the database object of the operation, and the transaction can be executed as part of the function of the program.

The transaction begins with an executable SQL statement, continues to execute the transaction body, and ends one of the following situations:

1. Show commit COMMIT: when a transaction encounters a COMMIT instruction, it will end the transaction and permanently save all changed data to the database file.

2. Display rollback ROLLBACK: when the transaction encounters this instruction, it also ends the execution of the transaction, but at this time it rolls back all changed data to its original value, that is, cancels all changes.

3. DDL statement: once a user uses a data definition language, such as CREATE, DROP, etc., all previous DML operations are committed as part of the transaction, which is called implicit commit.

4. End the program normally. If the oracle database application ends normally, if you use the SQL*PLUS tool to change the data and exit the tool program normally, oralce automatically commits the transaction.

5. abnormal termination of the program: when the program crashes or aborts unexpectedly, all data changes are rolled back, similar to displaying the results of the rollback operation, which is implicitly rolled back because there is no user participation.

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.