SSH: Basic Oracle database, sshoracle Database

SSH: Basic Oracle database, sshoracle Database
Oracle Database basics 1: Basic Oracle knowledge

(1) supports multi-user, large transaction volume Transaction Processing

(2) Excellent database security and integrity.

(3) supports distributed data processing. Connect the databases distributed in different physical locations to a communication network, and form a logically unified database under the control of the Distributed Database Management System to complete data processing tasks.

(3) portability. Oracle can be used on windows, Linux, and other operating systems, while SQL server can only run on Windows platforms.

Ii. Basic concepts of Oracle 3: Start a database in Windows

The following describes three common Oracle services.

4. Configure the database

Listener is a server-based network service of oracle. It is mainly used to listen for connection requests from clients to servers.

Common Oracle client configurations use the local network service name and oracle name server.

V. Database Connection

(1) system is the existing user name of the database.

(2) orcl password for this user

(3.) @ is just a unified symbol, which separates the user name, password, and the following network service name.

(4) orcIDB is the name of a network service, also known as a local network service.

2. PL/SQL Developer Tool

Vi. Oracle Data Types

(1) CHAR data type: When a string of fixed length is required, the char data type is used.

(2) VARCHAR2 data type: This data type supports variable-length strings

(3) NCHAR Data Type: this type is a national string set. The usage is the same as that of char.

2. Numeric data type:

(1) The NUMBER data type can store positive integers, negative numbers, zero, fixed points, and 38-bit floating point numbers with precision.

Syntax: NUMBER (p, s): where P is the precision, S is the range, indicating the NUMBER on the rightmost of the decimal point

3. date, time, and data type:

(1) DATA Type:

(2) TIMESTAMP Data Type

4. LOB data type:

LOB is also called a "big object" data type. It can store up to 4 GB of unstructured information, including audio and video clips. LOB allows efficient, random, and segmented access to data. LOB can be external or internal, depending on its database location

(1) CLOB: can store a large amount of character data

(2) BLOB: Large binary objects can be stored.

(3) BFILE: stores binary files in operating system files outside the database ..

(4) NCLOB: used to store large NCHAR character data.

5. pseudo columns in Oracle:

(1) ROWID: each row in the database has a row address, and the ROWID pseudo column returns the row address.

(2) ROWNUM: for each row returned by a query, the ROWNUM pseudo column returns a value representing the order of rows. The returned rows represent several rows.

VII. SQL speech Introduction

(1) create table command: create table [schema.] table

(Column datatype {, column datatype [,…]});

Table: indicates the Table name.

Column: indicates the Column name.

Datatype: indicates the data type and width of the column.

When creating a table, you must specify the unique table name, unique column name, data type, and width of the column.

Select: Query

Insert: Insert

Update: Update

Delete: Delete

Commit: Submit a transaction

Rollback: roll back the transaction

Savepoint: Create a storage point in a transaction

Rollback to <SavePoint_name> rolls back the transaction to the storage point.

(1) Arithmetic Operator: a query statement that uses traditional Chinese medicine for numerical calculation. arithmetic expressions can be used in SQL commands.

(2) comparison operator: The comparison operator is used to compare the values of two expressions.

(3) logical operator: used to combine the results of multiple operations to generate a true or false result.

(4) set operator: combines the two query results into a result set.

UNION: (UNION) operator returns the query of all two selected rows.

Union all: (union all) operator combines two queries for ALL selected rows, including duplicate rows.

INTERSECT: (intersection) operator returns only the rows of both queries.

MINUS: (reduce set) operator returns only the rows selected by the first query but not selected by the second query, that is, the rows excluded from the second query result in the first query result.

(1) Oracle divides functions into single-row functions, Aggregate functions, and analysis functions. Single-row functions can be roughly divided into character functions, date functions, numeric functions, conversion functions, and other functions.

(2) There are several types of conversion functions:

TO_CHAR (): to_char (sysdate, 'yyyy "-" fmMM "-" fmDD "-" HH24: MI: ss') from dual;

TO_DATE (): select to_char (1210.7, '$9,999.00') from dual;

TO_NUMBER () select to_date ('1970-07-13 ', 'yyyy-mm-dd') from dual;

(3) analysis function: Syntax: function name ([parameter]) over ([partition clause] [sort clause]);

Fow_number: The function returns a unique value. When the same data is encountered, the ranking increases sequentially according to the record sequence in the record set.

Dense_rank: The function returns a unique value. When the same data is encountered, all the same data rankings are the same.

Rank: The function returns a unique value. When the same data is encountered, the ranking of all the same data is the same, and the ranking of the last record and the next record is empty.