Oracle learning notes 3 SQL commands, oracle learning notes SQL

Oracle learning notes 3 SQL commands, oracle learning notes SQL
SQL introduction SQL supports the following types of commands: 1. data Definition Language (DDL) 2. data manipulation language (DML) 3. transaction Control Language (TCL) 4. the Data Control Language (DCL) is a detailed note of the four SQL languages:

 

When creating a table for the Oracle data type, you must specify the data type for each column. The following are Oracle Data Types: character data types.

The CHAR data type is used when a string of fixed length is required. The CHAR data type stores alphanumeric values. The length of a CHAR data column can be 1 to 2000 bytes. VARCHAR2 VARCHAR2 data type supports variable-length string VARCHAR2 data type storage letter/digit value VARCHAR2 data type size within 1 to 4000 bytes LONG data type storage variable-length character data LONG A table with a maximum data type of 2 GB can be stored only once. Primary keys cannot be created on Long columns, unique constraint. index cannot be used in Stored Procedure Parameter type. Value data type. Value data type. 1. it can store integers, floating-point numbers, and real numbers. declaration syntax with the highest precision of 38-bit numeric data type: 1. NUMBER [(p [, s])] 2. P indicates precision, and S indicates the number of digits of the decimal point. Date and Time data types are stored as date and time values, including year, month, and day, the main Date and Time types of hours, minutes, And seconds are: 1. DATE-stores the DATE and time, accurate to the entire second 2. TIMESTAMP-stores date, time, and time zone information, the second value is accurate to the six digits after the decimal point. The RAW/long rawraw data type is used to store binary data. The RAW data type can store up to 2000 bytes. The long raw data type is used to store binary data with variable length. LONG RAW up to 2 GBLOB data types can be stored. LOB is called a "big object" data type, it can store up to 4 GB of unstructured information, for example, LOB data types such as sound editing and video files allow efficient, random, and segmented access to data. pseudo columns in Oracle are like a table column, however, it does not store pseudo columns in the table and can be queried from the table, but it cannot insert, update, or delete their values. Common pseudo columns include ROWID and ROWNUM.

ROWID is the address of the row stored in the table. This address can uniquely identify a row in the database. You can use the ROWID pseudo column to quickly locate a row in the table.

ROWNUM is the number of rows in the returned result set. You can use it to limit the number of rows returned by the query.

Oracle supports the following types of SQL operators: Arithmetic Operators are used to perform numerical calculations. You can use arithmetic expressions in SQL statements, arithmetic expressions are composed of column names of numerical data types, numerical constants, and Arithmetic Operators connected to them. Arithmetic Operators include addition (+), subtraction (-), multiplication (*), Division (/) select a + B from tablename; Note: If a column is null, the result is a null comparison operator.

 

The comparison operator is used to compare the values of two expressions.

 

Comparison operators include = ,! =, <,>, <=,> =,... AND, IN, LIKE, and is null any all: Any one> any (100,200,300); example:

Select * from table name where column Name> any (100,200,300 );

 

All: Any, All;

Logical operators are used to combine multiple calculation results to generate one or more true or false results. Logical operators include AND, OR (OR) AND non (NOT ).

Or (or)

select ename from scott.emp where ename='joke' or ename='jacky'

And (and)

select ename from scott.emp where ename='and' or ename='jacky'

Not (not)

select ename from scott.emp where not ename='and' or ename='jacky'

 

The set operator combines the two query results into a result union (union): union connects two SQL statements, and the sum of the two SQL statements removes Repeated Records.

(select deptno from scott.emp) union (select deptno from scott.dept)

Union all (union): returns two SQL statements. The sum of the two SQL statements does not need to remove duplicate records.

(select deptno from scott.emp) union all (select deptno from scott.dept)

 

Intersect: Intersect connects two SQL statements to obtain the common part of the two sets.

(select deptno from scott.emp) intersect (select deptno from scott.dept)

 

Minus: connects two SQL statements to obtain the difference between the two sets.

(select deptno from scott.emp) minus (select deptno from scott.dept)

 

The concatenation operator is used to combine multiple strings or data values into one string.

SELECT (venname | 'the address is' | venadd1 | ''| venadd2 |'' | venadd3) address FROM vendor_master WHERE vencode = 'v001 ';

The join operator can be used to merge multiple columns in a table into a logical row column.

Operator priority

The order of priority of SQL operators from high to low is:

Arithmetic Operator -------- highest priority join operator comparison operator NOT logical operator AND logical operator OR logical operator -------- lowest priority