Oracle basic operators/operating languages...

Operators in Oracle
Arithmetic Operators:
Whether in sqlserver or Java, each language has arithmetic operators, which are similar.

Oracle Arithmetic Operators (+ )(-)(*)(/)It is worth noting that :/In Oracle, it is equivalent to division in display.5/2 = 2.5

Comparison operator:

    Here, the equal sign can be replaced with other operators: (The following is a single-condition query example of this operator)

  
    ! =Not equalSelectEmpno, ename, jobFromScott. EMPWhereJob! = 'Manager'

    ^ =Not equalSelectEmpno, ename, jobFromScott. EMPWhereJob ^ = 'manager'

    <> Not equal SelectEmpno, ename, jobFromScott. EMPWhereJob <> 'manager'

    <Less SelectSalFromScott. EMPWhereSal <1, 1000

    > GreaterSelectSalFromScott. EMPWhereSAL> 1000

    <= Less than or equalSelectSalFromScott. EMPWhereSal <= 1000

    > = Greater than or equal SelectSalFromScott. EMPWhereSal & gt; = 1000

    InIn the list SelectSalFromScott. EMPWhereSalIn (1000,2000)
   Time query can use in for exampleSelect * from student where time in ('06-March-09 ', '08-May-09 ');

    NotInNot in listSelectSalFromScott. EMPWhereSalNotIn (1000,2000)

    Between... andBetween... and...

                  SelectSalFromScott. EMPWhereSal Between1000And2000

    NotBetween... andNot between... and... 

                   SelectSalFromScott. EMPWhereSalNotBetween1000And2000

    LikePattern Matching SelectEnameFromScott. EMPWhereEnameLike'M %'(% Indicates a string of any length)
                  SelectEnameFromScott. EMPWhereEnameLike'M _'(_ Represents any character)

   IsNullEmpty or not SelectEnameFromScott. EMPWhereEnameIsNull

   IsNotNullNot emptySelectEnameFromScott. EMPWhereEnameIsNotNull

 

Logical operators:

    Or (OR) SelectEnameFromScott. EMPWhereEname = 'job'OrEname = 'jacky'

    And (and)SelectEnameFromScott. EMPWhereEname = 'and'OrEname = 'jacky'

    Not (not)SelectEnameFromScott. EMPWhereNotEname = 'and'OrEname = 'jacky'
 
Set OPERATOR:

   Union (Union)     Union connects two SQL statements, and removes duplicate records for the two SQL statements.

                      (SelectDeptnoFromScott. EMP)Union(SelectDeptnoFromScott. Dept)

   Union all (union) Two SQL statements are followed, and the two SQL statements do not need to remove duplicate records.

                       (SelectDeptnoFromScott. EMP)UnionAll (selectDeptnoFromScott. Dept)

   Intersect(Intersection)Intersect connects two SQL statements to obtain the common part of the two sets.

                       (SelectDeptnoFromScott. EMP)Intersect(SelectDeptnoFromScott. Dept)

   Minus(Makeup)    Minus connects two SQL statements to obtain the difference between the two sets queried.
                       (SelectDeptnoFromScott. EMP)Minus(SelectDeptnoFromScott. Dept)

 

Join OPERATOR: (|) is used to connect multiple fields or connect multiple strings.

Operator priority: arithmetic, join, comparison, logic (not and or)

Data Types in Oracle

Data Types mainly include: character numeric date large object lob row/long row 

Character Type: (varcharVarchar2Long)
   Varchar: The length cannot be changed, and the maximum length is 2000 bytes.
   Varchar2: Variable Length, maximum of 4000 bytes
   Long: Variable LengthMaximum 2 GB

Numeric type: int double float is integratedNumber type.
   Number [P, S]: P indicates the precision, and s indicates the number of decimal places.

 

Date type: (date) (timestamp)
   Date type storage date type, including year, month, day, hour, minute, and second.
   The timestamp type stores the date type, including more precise information, including region information, year, month, day, hour, minute, and second. No second .......

   The main problem with the date data type is that its granularity cannot determine which of the two events occur first. Oracle has extended the timestamp data type in the date data type, which includes information about all date data types, such as year, month, day, hour, minute, and second, and contains information about decimal seconds. If you want to convert the date type to the timestamp type, use the cast function.
Eg. SQL> select cast (date1 as timestamp) "date" from T;

Raw: stores binary data.

Longraw: stores binary data of variable length. The maximum value is 2 GB.

Pseudo columns in Oracle
The (rowid) (rownum) pseudo column is a data field that is automatically added to each data entry when data is inserted into the data table.

The actual storage address of each record of the rowid, even if the record is repeated, this field will not be repeated.
Rownum is the field added to each record after the data is queried. It does not exist, but is equivalent to a row number automatically added after the view is queried. When you run select rowed, rownum from table name;, you can view the data.

SQL statements in Oracle. SQL statements are common database languages.
SQL commands include (DDL) (DCL) (DML) (TCL)
DDL: Data Definition LanguageInclude the create statement, drop statement, and alter statement.
DCL: Data Control LanguageIncludes the grant statement. Revoke statement
DML: Data Control LanguageIncluding adding, deleting, modifying, and querying statements.
TCL: Transaction Control LanguageIncludingCommit, rollback, savepoint
Functions in SQL

SQL functions include: (single row function) (grouping function) (analysis function)

Single Row function:

    Only one data entry is displayed for each record queried from the table. A single-row function can appear in a select clause and a where clause. Single-row functions include: (character functions) (numeric functions) (date functions) (conversion functions) (Other functions ). 

Date function: Calculate the date to display the date type or number type.

   Sysdate   Current date and time
   Current_date  Returns the current date in the current session time zone.

   Current_timestamp  With TimestampWithTimeThe zone data type returns the current date in the current session time zone.

   Dbtimezone  Return Time Zone
   Add_months  Plus the number of months
   Months_between  Returns the number of months between two dates.
   Last_day  Returns the last day of the month.
   Round  Round the number of months
   Next_day  Specify the date of the next week
   Trunc  Date Format Conversion
   Extract  Mentioned part of date 

Character functions:
   Initcap (char)Uppercase letters.
   Lower (char)Converts uppercase letters to lowercase letters.
   ...
For details, see:Http://blog.sina.com.cn/s/blog_4b3c1f950100pkhv.html

Numeric Functions
   MoD (m, n) The remainder of M/N.
   Power (m, n) N power of M.
   Round (m, n)  M is the decimal point, and N is the decimal point. Rounding
   Trunc (m, n)  M is the decimal point, and N is the decimal point. Rounding

Conversion functions
   To_char conversion character.
   To_dateConversion date.
   To_numberConvert numbers

See: http://blog.sina.com.cn/s/blog_4b3c1f950100pk54.html

Other functions
   Nvl (expression 1, expression 2)
       If expression 1 is null, expression 2 is returned; otherwise, expression 1 is returned;
       If expression types are inconsistent, Oracle converts expression 2 to expression 1.
   Nvl2 (expression 1, expression 2, expression 3)
       If expression 1 is null, return the value of expression 3. Otherwise, return the value of expression 2.
   Nullif (expression 1, expression 2)
       If the two expressions are equal, a null value is returned. Otherwise, expression 1 is returned.

Aggregate functions
   AVG () is the average.
   Count () returns the queried data.
   Sum () and
   Max () is the maximum value.
   Min () is the minimum value.

Group and having
Select p_category, max (itemrate) from itemfile group by p_category;

Select p_category, max (itemrate) from itemfile group by p_category
Having p_category not in ('accessories ');