Two, basic SQL statements and functions

Two, basic SQL statements and functions

  • Master Oracle basic SQL statements
  • Master Oracle single value and group functions
  • Master Oracle multi-table query and set operations

SQL is an acronym for Structured Query Language

SQL is a database language, Oracle uses this language to store and retrieve information

Table is the main database object, used to store data

Communication with Oracle server can be achieved through SQL

Insert picture description here

SQL supports the following categories of commands

Data Definition Language (DDL)

Insert picture description here

Data Manipulation Language (DML)

Insert picture description here

Transaction Control Language (TCL)

Insert picture description here

Data Control Language (DCL)

Insert picture description here

Oracle data type

When creating a table, you must specify the data type for each column

The following are the categories of Oracle data types:

Insert picture description here

Character data type

Insert picture description here
char
  • When a fixed-length character string is required, use the CHAR data type
  • CHAR data type stores alphanumeric values
  • The column length of the CHAR data type can be 1 to 2000 bytes
varchar2
  • VARCHAR2 data type supports variable-length character strings
  • VARCHAR2 data type stores alphanumeric values
  • The size of the VARCHAR2 data type is in the range of 1 to 4000 bytes
long
  • When a fixed-length character string is required, the CHAR data type is used.
  • The CHAR data type stores alphanumeric values.
  • The column length of the CHAR data type can be 1 to 2000 bytes

Numerical data type

Numerical data type

  • Can store integers, floating point numbers and real numbers
  • The highest precision is 38 digits, and the range: minus 10 to the 38th power of 10 to the 38th power of 10.

The declaration syntax of numeric data types:

  • NUMBER [( p[, s])]
  • P represents precision, S represents the number of decimal places (p represents a total of several digits including decimal places)

Date and time data type

  • Date and time data type stores date and time values, including year, month, day, hour, minute, and second
  • The main date and time types are:
  • DATE-stores the date and time part, accurate to the entire second
  • TIMESTAMP-store date, time and time zone information, the second value is accurate to 6 decimal places

RAW/LONG RAW

  • RAW data type is used to store binary data
  • RAW data type can store up to 2000 bytes
  • The LONG RAW data type is used to store variable-length binary data
  • LONG RAW data type can store up to 2 GB

LOB

  • LOB is called "Large Object" data type, which can store up to 128TB of unstructured information, such as sound clips and video files, etc. (The capacity of LOB type has been increased from the original 4G to the maximum 128T)
  • LOB data types allow efficient, random, and segmented access to data
Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here

Pseudo column in Oracle is like a table column, but it is not stored in the table

Pseudo columns can be queried from the table, but their values ​​cannot be inserted, updated, or deleted

Commonly used pseudo columns are ROWID and ROWNUM

Insert picture description here

Data definition language

  • Data definition language is used to change the database structure, including creating, changing and deleting database objects
  • The data definition language commands used to manipulate the table structure are:
  • CREATE TABLE
  • ALTER TABLE
  • TRUNCATE TABLE
  • DROP TABLE

Data manipulation language

Data manipulation language is used to retrieve, insert and modify data

Data manipulation language is the most common SQL command

Data manipulation language commands include:

SELECT

Insert picture description here
Insert picture description here
Insert picture description here

Use having to limit the results after grouping

Insert picture description here
Insert picture description here
Insert picture description here
Insert picture description here

INSERT

Insert picture description here
Insert picture description here

UPDATE

DELETE

Data control language

  • Data control language provides users with authority control commands
  • The commands used for permission control are:
  • GRANT grant permissions
  • REVOKE revokes granted permissions

SQL operator

The SQL operators supported by Oracle are classified as follows:

Insert picture description here

Arithmetic Operator

  • Arithmetic operators are used to perform numerical calculations
  • Arithmetic expressions can be used in SQL statements. Arithmetic expressions consist of column names of numeric data types, numeric constants, and arithmetic operators that connect them
  • Arithmetic operators include addition (+), subtraction (-), multiplication (*), division (/)

Comparison operator

  • Comparison operators are used to compare the value of two expressions
  • Comparison operators include =,!=, <, >, <=, >=, BETWEEN...AND, IN, LIKE and IS NULL, etc. LIKE can use matching symbols _,%

Logical operator

  • Logical operators are used to combine the results of multiple calculation operations to generate a true or false result
  • Logical operators include AND, OR, and NOT
Insert picture description here

Concatenation operator

Concatenation operator is used to combine multiple strings or data values ​​into one string

Insert picture description here

Operator precedence

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

  • Arithmetic operators--------highest precedence
  • Concatenation operator
  • Comparison operator
  • NOT logical operator
  • AND logical operator
  • OR logical operator--------lowest precedence

Oracle functions

Oracle provides a series of functions for performing specific operations

SQL function takes one or more parameters and returns a value

The following is the classification of SQL functions:

Insert picture description here

One-line function classification

  • Single row function returns only one value for each row queried from the table
  • Can appear in SELECT clause and WHERE clause
  • One-line functions can be roughly divided into:
  • Character function
  • Date and time functions
  • Numerical function
  • Conversion function
  • Mixed function
Character function
Insert picture description here

Here are some other character functions:

  • CHR and ASCII
  • LPAD and RPAD
  • TRIM
  • LENGTH
  • DECODE
Insert picture description here
Date and time functions

Date functions operate on date values ​​and generate results of date data type or numeric type

Date functions include:

  • ADD_MONTHS
  • MONTHS_BETWEEN
  • LAST_DAY
  • ROUND
  • NEXT_DAY
  • TRUNC
  • EXTRACT
Numerical function

Numeric functions accept numeric input and return numeric results

Insert picture description here

​ (round) rounded to the nearest month by year

Insert picture description here
Insert picture description here
Insert picture description here

-1 Keep the decimal point forward

Insert picture description here

(Trunc) truncated

Insert picture description here
Insert picture description here
Conversion function

Conversion function converts a value from one data type to another

The commonly used conversion functions are:

  • TO_CHAR
  • TO_DATE
  • TO_NUMBER
Insert picture description here
Insert picture description here
Mixed function

DECODE() function

The following are several functions used to convert null values:

  • NVL, the first is empty, return two; otherwise, return one
  • NVL2, return two if the first one is not empty; otherwise return three
  • NULLIF, if two expressions are equal, it returns empty; otherwise the first

Grouping function

  • Grouping function returns results based on a set of rows
  • Return a value for each set of rows
Insert picture description here
GROUP BY and HAVING clauses
  • GROUP BY clause
  • Used to divide information into smaller groups
  • Each group of rows returns a single result for that group
  • HAVING clause
  • Used to specify the conditions of the GROUP BY clause to retrieve rows
Insert picture description here

Multi-table query

  • Equivalent connection
  • Outer join
  • Self-connection
  • Subqueries

How to write equal join

//相等连接(第一种写法):
  select table1.column,table2.column
  from  table1, table2 
  where  table1.column1=table2.column2
//  可以使用表的别名,为了书写的简化。

//相等连接(第二种写法):
  select table1.column,table2.column
  from  table1 inner join table2 
  on   table1.column1=table2.column2
 // 可以使用表的别名,为了书写的简化。 

How to write left outer join

//左外连接(第一种写法):
  select table1.column,table2.column
 from  table1 left  outer  join table2
  on   table1.column1=table2.column2
//  可以使用表的别名,为了书写的简化。

//左外连接(第二种写法):
 select table1.column,table2.column
  from  table1, table2 
  where  table1.column1=table2.column2(+)
 // 可以使用表的别名,为了书写的简化。 

Set operator

The set operator combines the results of two queries into one result

Insert picture description here

The MINUS operator returns to exclude rows that appear in the second query from the results of the first query

Insert picture description here

Union removes weight, union all does not remove weight

Insert picture description here

Rename

重命名表:
rename table_name1 to table_name2;

重命名列:
alter table table_name rename column col_oldname to colnewname ;