Oracle Learning Note Three SQL commands

SQL Introduction SQL supports the following categories of commands: 1. Data definition Language (DDL) 2. Data Manipulation Language (DML) 3. Transaction Control Language (TCL) 4. Data Control Language (DCL) Here are the detailed notes for these four SQL languages: Oracle learning note Three SQL life Order (ii): SQL Action Language category when you create a table for an Oracle data type, you must specify the data type for each column the following is the category of the Oracle data type: Character data type

Char Type the char data type is used when a fixed-length string is required. The CHAR data type stores alphanumeric values. The column length of the CHAR data type can be between 1 and 2000 bytes. The VARCHAR2 type VARCHAR2 data type supports variable-length strings VARCHAR2 data types store alphanumeric values VARCHAR2 data types within 1 to 4,000 byte ranges long type Long data type stores variable-length character data long Data types can store up to 2GB a table can only come out once. Cannot establish primary key, unique constraint on a long column, index cannot be used for stored procedure parameter type numeric data type 1. You can store integers, floating point numbers, and real numbers 2. The declaration syntax for the highest precision 38-bit numeric data type: 1. Number [(p[, S])]2.P represents the precision, s represents the digit of the decimal point date Time type datetime data type stores the date and time value, including the year, month, day, hour, minute, and second the main date time type is: 1. Date-stores the day and time portion, accurate to the entire second of 2. TIMESTAMP-Stores the date, time, and time zone information, the seconds value is accurate to 6 bits after the decimal point raw/long rawraw data type for storing binary data raw data types can store up to 2000 bytes long raw data type used to store variable-length binary data long The RAW data type can store up to 2 Gblob type lobs, known as "large object" data types, can store up to 4GB of unstructured information, such as LOB data types such as sound clips and video files that allow efficient, random, segmented access to data pseudo-columns in Oracle, like a table column, But it is not stored in a table where pseudo-columns can be queried from a table, but cannot be inserted, updated, and deleted by their values commonly used pseudo-columns have ROWID and rownum

ROWID is the storage address for rows in a table that uniquely identifies a row in the database, and can be used to quickly locate a row in a table using the ROWID pseudo-column

ROWNUM is the ordinal of the row in the result set returned by the query, which you can use to limit the number of rows returned by the query

SQL operator The SQL operator supports the following classifications: arithmetic operator arithmetic operators are used to perform numeric computations you can use arithmetic expressions in SQL statements, arithmetic expressions consist of column names of numeric data types, numeric constants, and arithmetic operators that concatenate them include plus (+), minus (-) , Multiply (*), except (/) Select a+b from TableName;Note: The result will be null if one column is nullcomparison Operators

Comparison operators are used to compare the values of two expressions

Comparison operators include =,! =, <, >, <=, >=, between ... And, in, like, and is NULL, any, and so onany: one > any (+, +); example : 　　

Select *  from where >  any (a);

All: any one, all ;

logical OperatorsLogical operators are used to combine the results of multiple calculations to produce a result that is either true or false. Logical operators include with (and), or (or), and non (not).

or (OR)

Select  from where ename='joke'or ename='Jacky  '

and (with)

Select  from where ename='and'or ename='Jacky  '

Not (non)

Select  from where  not ename='and'or ename='Jacky  '

The set operator set operator combines the results of two queries into a single result, Union (Union): Unions concatenate two SQL statements, two SQL statements, and a duplicate record.

(SelectfromUnion (select from Scott.dept)

UNION ALL: Two SQL statements, two SQL statements, and no duplicate records are removed.

(SelectfromUnion All (select from Scott.dept)

Intersect (intersection): Intersect joins two SQL statements to fetch a common part of the two sets of queries.

(Selectfromintersect (select from Scott.dept)

Minus (complement): minus connect two SQL statements, take the query out of the two sets of the difference.

(Select from Scott.emp) minus (select from Scott.dept)

The Join operator join operator is used to combine multiple strings or data values into a single string

SELECT (venname| | ' The address is ' | | venadd1| | ' ' | | venadd2 | | ' ' | | VENADD3) address from Vendor_master WHERE vencode= ' V001 ';

Multiple columns in a table can be combined into logical rows by using the Join operator

Precedence of Operators

The order of precedence of SQL operators from highest to lowest is:

Arithmetic operator--------Highest priority join operator comparison operator not logical operator and logical operator or logical operator--------Lowest priority

Oracle Learning Note Three SQL commands