Oracle review, oracle

Oracle review, oracle

After I got back, I recorded several sets of videos, but I didn't know how to record them when I started recording them. If I recorded a few sets, I had nothing to do. I could record each set for dozens of minutes, in the process, I also read this part of the Oracle database, and gained a lot. learning is a process of repeated accumulation. I also reviewed some scattered knowledge points, review it again. Some of them have already learned and some are just a little bit impressed. Remember that there is a saying: "A book is valuable if one sentence is helpful to you ", after watching this video, I learned and learned more about leaving home, and summarized it as follows for your reference.

Database understanding

Computers are like all parts of our bodies, databases = brains, facial features = programming languages, blood = Data Bus, operating systems = human bodies.

Various information is stored in the brain and input to our brain through our eyes, ears, and other facial features. Therefore, the facial features are like the programming languages we usually use, the operating system is like our body that supports the complete structure of all parts of us to ensure that they can run correctly. Computers and our bodies are the same and similar. After all, people invented computers.

Oracle users

Super administrator sys/chage_on_install

Common Administrator system/manage

Normal user scott/triger

Functions in Oracle

Single Row Function

Character Functions

Substr (), truncated string

Numeric Functions

Round () Random Function

Mod () touch function

Date Functions

Sysdate () gets the current date

For a date, add or subtract a number, which is equal to a date after or before several days.

Conversion functions

To_char (), To_char ('20140901'. '20160901 ')

To_date (), for example, To_date (SYSDATE, 'yyyy-mm-dd ')

Common functions

NVL () is used to change the value of a null field to 0. For example, select NVL (name, 0) from table1. If name is null, 0 instead of null is output.

Decode () is used to determine the value of a field and the output value. For example, name = 1. Output 3; name = 2, input 4 Li

Multi-Table query

Generally, when two tables are queried together, the Cartesian product is generated. The query result is the number of records in the two tables multiplied by the number of records. We need to use the equivalence condition to eliminate the Cartesian product, only the query results are eliminated, but they are not eliminated in essence. cartesian products exist. Therefore, multi-table queries are not recommended. Multi-table queries affect query efficiency, we do not recommend that you use joint queries when querying big data from multiple tables,

In a real big data system, before querying data, you need to first check the amount of data in it and only query the data you want.

Group Query

For complex SQL statements, you need to exercise more on your own before you can flexibly master them, and think about the relationship between them and the role of each SQL statement. As the usage deepens, you will have a deeper understanding of each SQL syntax, if similar queries appear again, there will be more ideas.

Query PS:

1. subqueries are usually written after the from or where keywords. The function is to form a new table and result set, and query or obtain conditions from the new result set.

2. The where condition is executed before group by, that is, filter the conditions first, and group the filtered results according to a field.

3. If you need to filter the results after grouping, you must use the HAVING field to set the filtering conditions.

4. The key to sorting is that order by is executed at the end of the SQL statement.

As long as you remember these principles and practice complex queries, SQL statements should have no problem, and even more difficult queries are just the concatenation and combination of these words.

Sequence usage

I have heard of sequences before, but I have never used them. I have never used it because it is always troublesome. This project was used, and the video was also described. It was very simple and practical.

Syntax:

Create sequence myseq

Increment by 1 -- add several

Start with 1 -- count from 1

NOMAXVALUE -- do not set the maximum value

NOCYCLE -- always accumulate without repeating

CACHE 10;

When used, for example, insert, you only need to assign myseq. NEXTVAL to the corresponding column.

For example

Insert into emp VALUES (myseq. nextval, 'Lewis ');

Transaction Processing

For each connection to the database, it creates a session with the database, that is, a session, although not a web layer session, is similar to that session, in a session, database operations are performed in the same transaction. Such a transaction is a local transaction. If the previous transaction is not updated, the next transaction cannot be committed or operated, the lock is released only when the update operation is complete because the database is automatically locked during the update process.

Data pseudo Column

This does not mean that we can use hidden columns instead of columns added by database designers. Two common pseudo columns in Oracle are ROWNUM and ROWID. We often use the ROWNUM column, ROWID is the physical address of the record. We can use this to achieve the paging effect. See the following code:

Select * from (select ROMNUM rm, id whereROMNUM <= 10) temwhere tem> 5

ROWNUM; each query is not fixed

ROWID: the physical address of the data record.

Flash back Technology

The database after Oracle10g provides the flash back technology, which is equivalent to a recycle bin function. The deleted data or table can be retrieved from the recycle bin. This function is sometimes quite practical, it is worth learning about.

Simple command:

View show recyclebin in the recycle bin

Retrieve the deleted table: Flashback table tablename to before drop

Clear recycle bin: Purge recyclebin

Constraints

Not empty

A field cannot be blank, not null

Uniqueness

This constraint is applicable to a specific field. If a field is set to unique, the field will not have repeated records, but it is okay to remember that null is null, as long as it is not repeated.

Primary Key

Primary Key = non-null + uniqueness

Foreign key

If the primary key of a table is used as a foreign key in another table, this key is a foreign key constraint, and the foreign key and primary key constraints exist simultaneously.

Check

That is to say, there are some restrictions on a field, such as gender: in the database, only male or female can be entered.

Joint Query

Join the query results of two tables

Union: Union query. The same part is not displayed.

Union all: displays the same part

Intsersect: only show the same part

Minus: only show different parts

View

First class on with check option

This view is a view of the conditions to be queried. It can be updated, but cannot be updated.

Class 2 with read only

This view is read-only and cannot contain any field in this view.

Index

The index is mainly used to improve the operational performance of the database.

Select * from table1 where id> 10000
I. The preceding statement is Row-by-row judgment without indexing.
Ii. After the index is added, the columns that are indexed are arranged in Binary Trees as follows:
1)
2) execute the preceding statement to query only some records.
3) if a column is a primary key constraint, an index is automatically created.
4) manually create a column
Iii. There is a problem with this index: If you want to improve the performance, you must maintain the above tree. If the data needs to be modified frequently, the code performance will decrease.
Iv. Therefore, the general index is only used in tables that are not frequently modified. If a table frequently modifies data and uses indexes, the performance will be reduced. Therefore, the performance improvement will always be relative.
V. This is one of the more than a dozen Oracle indexes, and the simplest one is the B-tree index. Bitmap indexes and function indexes

Database Backup

In the launch project, database backup is an important task.

Import and Export

Cold backup

Stop the database first, and then back up, similar to hot swapping.

Database listening

1. Listening for client connection

The listener is a database instance service. When a user logs on to an instance for the first time, the listener is used before connection. The second connection does not pass the listener.
B. Oracle was not migrated to the window at the beginning, so the listener was provided.
C. After the window is installed, the window mode can be used to operate windows.
E. The listener must determine whether it is a common user logon or sysdba logon. If it is a common user, it will go to the database for verification; if it is a dba, it will be a password file or operating system verification.
F. Operating System verification
I. For DBA, the first step is to verify the operating system. Therefore, you can log on to the DBA without a password or any other password, because it is an operating system user.
1) if the operating system authentication is canceled, the password file will be verified. It will not work if you do not write the password.

2. Manage instances

Multiple database instances can be registered to a listener at the same time. They register their Instance name and other parameter information in the listener. When the client connects to the listener, these parameters can be used to find the database instance, it serves as a bridge between the client and the instance.

3. Server Load balancer

A small listener plays a role in balancing client connections. If there is a large number of connections to one instance, the listener automatically transfers more connections to other instances.

A review of Oracle databases

Oracle Database, also known as Oracle RDBMS, or Oracle for short. It is a relational database management system of oracle.
1. Oracle is more stable than SQL server.
2.oracle's data import tool sqlload.exe is more powerful than SQL Server's Bcp. Oracle can import text file data as required.
3. Oracle has a better security mechanism than SQL server.
4. SQL server is easier to use than Oracle in terms of ease of use and friendliness.
5. Oracle will be more stable in processing big data.
6. SQL Server is more powerful in data export.
7. The processing speed is faster than that of Oracle, which is related to the two Protocols.

Which of the following statements is the most common in oracle?

INSERT statement; insert into table [(column [, column])] VALUES (values [, values...]);
UPDATE statement: UPDATE table SET column = value [, column = vallue...] [WHERE condition];
DELETE statement: delete from table [WHERE comdition];