Oracle Database most complete note-Basic concept (continuous update)

Objective:

1. Why do you want to learn a database?

1.1 Concept of the database

A database is a warehouse that organizes, stores, and manages data in accordance with its data structure.

The database, in a nutshell, is an electronic file cabinet-the place where electronic files are stored, and users can add, intercept, update, delete, and so on to the data in the file.

It stores data in a way that can be shared with multiple users, with the smallest possible redundancy, and is a collection of data that is independent of the application.

1.2 Data model is the storage method of data in database, and it is the base of database system.

The data model went through:

1.2.1 Hierarchy Model: Hierarchical model is the first model used in database system, and his data structure is a "direction tree"

1.2.2 Mesh Model: The mesh model represents the connection between entities and entities in a mesh structure. Each node in the network represents a record type, which is implemented by linking pointers. A mesh model can represent relationships between multiple dependencies, or it can represent a cross-relationship between data, that is, the horizontal and vertical relationships between data, which is an extension of the hierarchical model. The mesh model can easily represent the various types of connections, but the structure is complex, the algorithm is difficult to standardize

1.2.3 Relationship: The basic data structure in a relational model is a two-dimensional table, without a link pointer like a hierarchy or mesh. The connection between records is achieved by using the same name attribute in different relationships.

Relational database Benefits:

Single Data structure

Relationship normalization, and based on a rigorous theory

Simple concept and convenient operation

Composition

A. A single data structure-----relationship

B. Relational operations collection

C. Data integrity, accuracy and consistency of relationships

1.3 Relational database:

A relational database can be easily understood as a two-bit database, with tables formatted like Excel, with rows and columns. The so-called relational database refers to the database which is organized by the relational model. The relational model was first proposed by IBM's Research Institute, Dr. E.f.codd, in the following decades, when the concept of relationship was fully developed and gradually became the mainstream model of database structure. Simply put, the relationship refers to a two-dimensional tabular model, and a relational database is a data organization composed of two-dimensional tables and their connections.

1.4 Common relational databases and their basic concepts:

1.4.1 Common relational database:

MySQL SQL Server Oracle DB2 Sybase

1.4.2 Basic Concepts

A. Relationship: A relationship corresponds to a two-dimensional table, a two-dimensional table is a relationship name, each table consists of rows and columns

B. Tuple: A row in a two-dimensional table that becomes a tuple. You can use a Structured query statement (structured query Language) to manipulate tuple statements

C. Properties: one attribute value (component) in a tuple

D. Code: If there is such a property in a relationship, his value can be used to uniquely identify a row, it is said that the surname attribute is the key or code of the relationship

E. Primary key (main code): A property selected from several candidates to uniquely identify a row of data

F. Foreign keys: Data information that is used to logically correlate multiple tables

　　　　　　

2.Oracle cognition (A cognitive)

Oracle is the name of a company (Oracle), one of the world's largest giant IT companies, the world's biggest enterprise software company, headquartered in Redwood Beach, California, USA. 1989 officially entered the Chinese market. In 2013, Oracle surpassed IBM to become the world's second largest software company after Microsoft, the most famous product is Db,database,rdbms, relational database system. The world's largest database provider, Shanghai Research and Development Center, Wujiaochang, near Fudan University. Main Oracle database software, the world's second largest ERP provider, large enterprise-level internal management system, including human resources management and so on. Oracle is the first and most successful product of Oracle, and the development has been a long process, and by June 1997, Oracle release eighth. Oracle supports object-oriented development and new multimedia applications, and this version has become the foundation for supporting Internet and Network computing. At the same time, this version began to have the characteristics of processing a large number of users and massive data. Oracle 8i was officially released in September 1998. "I" stands for the internet, which adds a number of features designed to support the Internet, which provides a full range of Java support for database users, and Oracle 8i becomes the first database to fully complete the local Java runtime environment. Then there is the 9i,10g, "G" stands for "grid", the grid. The biggest feature of this version is the inclusion of grid computing. Then the 11g, now developed to 12c,c represents cloud, which is now the very fire of the cloud concept.

3. Three nouns:

SQL: Structured query statement to manipulate the language of the Oracle database

Sqlplus:oracle software comes with a terminal that can enter SQL and display the results of SQL execution

PL/SQL: A programmatic statement that adds a logical operation to a SQL statement, such as if for, makes it a SQL block that accomplishes a certain function

4. Four types of objects

Table: tables, consisting of rows and columns, are also called fields, and each row is full data for the day of the table.

View: A table or part of a table or a complete map, like a table in the mirror, the virtual image in the mirror is the view

Out of common table and view two objects, Oracle also supports the following four types of objects

Sequence: Sequence

Index: Indexes to improve the efficiency of data access

Synonym: Synonymous, easy-to-object operation

Program unit: The object for the PL/SQL operation

5. Five types of classification

Five categories of SQL

Data retrieval: Query

Select

DML: Data Manipulation language (row-level operation language) operates on one piece of data in a table

Insert Update Delete

DDL: The content of the Data Definition language (table-level action language) operation is a table (object)

Create alter DROP TRUNCATE rename

Transaction control (transaction controls): Commit rollback savepoint

DCL: Data Control Language

Grant Revoke

Delete differs from truncate:

Delete: Deletes one or more records in a table and does not return the initialization state of the table

Truncate: Clears the table and returns the initialization state of the table

Use of 6.oracle

Start:

6.1 win+r------->cmd-------->sqlplus "as SYSDBA"//Log in as SYSDBA (you can create users, assign permissions, etc.)

Win+r------->cmd-------->sqlplus username/password//Login with specified username/password

Locate the installation directory directly and open the Sqlplus terminal

6.2 Create User:

Create user username identified by password

6.3 Permission grants:

Grant Resource,connect to user;

Resource

Create Trigger

Create sequence

Create type

CREATE PROCEDURE

Create cluster

Create operator
Create Indextype
CREATE table
Connect
Create session

6.4 Switching users:

Conn Username/password

6.5 because the default locale for the data file is English, the current Windows system is Chinese, the English time display is inconsistent, the import fails, and the locale needs to be modified first

Alter session set Nls_date_language=english;

Alter session set Nls_language=english;

Import tables to

@ e:\oracle\summit2.sql

Start E:/oracle/summit2.sql

6.6 View Table Structure

DESC table_name;

Model 1 Select Rows

1.select:
Grammar:
SELECT [DISTINCT] {*,column [alias],...} from table

Note: [] the contents of [] represent the optional, * represents all columns, the DISTINCT keyword can only follow the Select keyword, after the SELECT clause specifies the column to query, followed by the table to query.

(English name First_name+last_name): My name is first_name.last_name;

The 2.select statement can perform arithmetic operations on all values of the specified column.
Grammar:
Select Col_name operator numbers

From Tb_name;

Note: The SELECT statement never modifies the original data.

3. Column-area aliases for queries
Grammar:
Select Old_column [As] New_column_name
From Tb_name;

4. Use | | You can make multiple columns of values or columns and special strings merge into one column for display
Grammar:
Select Col_name| | ' Spe_char ' | | Col_name
From Tb_name
' Spe_char ': Use this syntax if the value of a column is to be displayed with a special string connection.

5. Null is worth the substitution operation
Grammar:
Select NVL (Col_name,change_value)
From Tb_name;

NVL2 (col_name, value not shown as empty, empty displayed);
6. Using the DISTINCT keyword, you can display the duplicate records only one
Grammar:
SELECT DISTINCT Col_name,col_name ...
From Tb_name; Note 1:DISTINCT keyword can only be placed behind the SELECT keyword

such as: Select Id,distinct title
from S_emp;
The statement is syntactically incorrect!!!!!
Note 2: If more than one column appears after the DISTINCT keyword, it is considered a duplicate record if multiple columns are combined to go heavy, that is, if the values of multiple columns are the same.　　　　　　　　　　　　　　　　　　
Test table:
Idid2

+
　　　　　　　　　


Select distinct Id,id2
from test;
  Displays the result as:
Idid2


   ,
                      &NB           Sp              34

7.sqlplus command
A: Append content to the command line of the current operation
A test
C: Modify content at the command line of the current operation
C/old_char/new_char
Clear buffer: Clears the current cached command
Del: Delete the current action row
Del line_num Specifies to delete the first few rows
I: The next line of the current action command line inserts content
L: View Cache commands
L Line_num: View the specified command line
N Text: Replace the entire line of the nth row
!: followed by terminal commands------Linux environment
!clear: Clear Screen//
$: followed by terminal commands
$CLS---------> Windows environment
/: Execute Cache SQL command

Save file_name: Saves cache commands to file_name (append replace)
Get file_name: Extract the contents of a file to Sqlplus
Start and @ file_name: Execute SQL command in file
Edit file_name: Editing files using the VI editor set up by the terminal (for Windows using Notepad)
Spool file_name saves the next SQL statement and SQL run results to a file (append)
Sql1
Result1
Sql2
Result2
...
Spool off spool function
Exit: Exit

8.select Id,last_name,first_name, salary, dept_id
From S_emp;

The results are not good-looking, and through column makes our display interface look good.

Colu last_name format A15;
Colu first_name format A15;

Column has no change in the data table data Ah, no, it just changes the display. Is it a SQL command? No, it is the Sqlplus command. In addition to this function, let's look at what it does in the next part.

COLUMN last_name HEADING ' employee| Name ' FORMAT A15
. Alias Last_Name to Employee| Name, the vertical bar represents the line break.
. A15 means 15 bytes long, a short bar is one byte long

COLUMN salary JUSTIFY left FORMAT $99,990.00
. Salary JUSTIFY left: Just change the column name to show as easy
. Format $99,990.00: Control display format for the front plus $, "," for the delimiter, 0 or 9 for the number (wildcard), 0 for the replacement of the alignment value, the number of bits will be up, can be mixed use.

COLUMN start_date FORMAT A8 NULL ' not hired '
. If the start_date value is empty, it is displayed as ' not hired ';
. Format cannot be directly followed by NULL, first A8 or A10;
. NULL ' not hired ' and NVL a bit different, NVL requires type matching

column displays all formatting for the column format
Column last_name shows the settings for the last_name column display
Column Last_Name Clear removes the case for last_name column formatting
Clear column clears formatting from all column

column formatting, where the column is not specific to a table.

Ex
1234 Column 99.99--> ######//Can not be displayed when error, just show # # # #
Column Columname Show restrictions on columns

Model 2 sorting & limiting Selected Rows
1. Use of Order by clause
1) The Order by clause in the entire
Location in the SELECT statement:
Always in the last
2) What to do with ORDER by:
Column name, alias of column, expression,
List now after the SELECT keyword
Order (column number);
3) Order by can be followed by multiple columns,
Sort by first column first,
If the first column has the same value and then press
The second column is sorted, such as the first two columns
Have the same value, they are sorted by the third column ...
4) ASC table Ascending, can be omitted.
Desc table Descending.
5) The null value is always the largest.
When sorting in ascending order, the null value is placed in the last
When sorting in descending order, the null value is placed in the front

2. Use of the WHERE clause
Grammar:
Select Col_name,...
From Tb_name
Where Col_name comparison action expression
logical operators
Col_name comparison operation Expressions
1) Function: Filter the data set returned by SQL statement;
2) Location: Immediately after the FROM clause
3) Content: consists of one or more qualifying conditions, which are composed of expressions, comparators, and literals.
4) All strings and dates are enclosed in single quotes, and the values do not require single quotes.
The date in Oracle has a specific format, ' Dd-mon-yy ' (depending on the date display format),
Otherwise, as a string.
5) Several common operators:
1 "Logical comparison operator
= > < >= <=! =
2 "SQL comparison operator
Between and: within what range
In (list): In a list
Like: used when a fuzzy query is a value that is not an exact value
Wildcard, which is a symbol that can replace any content
%: Pass with 0 to more characters
_: When and only if a character is wildcard
Escape characters:
The default is \, you can specify when the escape symbol is indicated, the escape character can only escape one of the following characters
Between 1 and 4: includes the starting and ending values. The content is limited to 1 to 4.
In (1,2,4): limited content is 1,2,4.
3 "Logic operator
Use when the condition has multiple
And: And Logical
Or: Or logical
Note: And logic is higher than or logic
Not: Non-logical

Oracle Database most complete note-Basic concept (continuous update)