(6) Oracle Basics-Simple Query

. Basic query Statements

SELECT [DISTINCT] column_name1,... | *

From table_name

[WHERE conditions];

　　The role of the P:DISTINCT keyword is to filter out duplicate records.

. Querying all fields in a table and specifying fields

　　<1> querying all fields in a table

SELECT * FROM table_name;

　　<2> querying specified fields

SELECT column_name1,column_name2 ... from table_name;

. setting aliases for a field

　　<1> use spaces to set aliases

SELECT column_name new_column_name from table_name;

　　<2> using as to set aliases

SELECT column_name as new_column_name from table_name;

　　　　( Note: Setting an alias to a field does not change the name of the field, just the display name of the query result )

. Operators and expressions

　　expression = operator + operand

Operator Classifications:

<1> arithmetic operator +, -, *, /

　　<2> comparison Operators >, >=, <, <=, <>

<3> logical operators and, or, not

. Query with a condition

SELECT column_name1,column_name2 ... From table_name WHERE EXPRESSIONS;

. Fuzzy query

<1> use of wildcard characters (_,%)

① a _ represents a character

② % can represent 0 to any number of characters

<2> using syntax

SELECT column_name1,column_name2 ... From table_name WHERE column_name like ' string ';

. Scope queries

<1> using multiple expressions

　　　　　SELECT column_name1,column_name2 ... From table_name

WHERE column_name > value1 and column_name < value2;

<2> use between ... and　

　　　　　SELECT column_name1,column_name2 ... From table_name

WHERE column_name between value1 and value2;

<3> using In/not in

SELECT column_name1,column_name2 ... From table_name

WHERE column_name in | Not in (value1,value2, ...);

　　<4> using Exists/not EXISTS

SELECT column_name1,column_name2 ... From table_name

WHERE EXISTS | Not EXISTS (select_expression);

　　　　The difference between p:in and exists

1. In the following collection contains the value in front of the return true, the result set after EXISTS is not empty returns true.

2. In-boot words can only return one field, while EXISTS-guided words may return multiple

. Sorting the results of a query

SELECT column_name1,column_name2 ... From table_name

[WHERE conditions] ORDER by Column1 desc/asc,column2 desc/asc ...;

　　P:1. DESC = Descending, ASC denotes ascending;

2. Prioritize multiple fields by descending order of precedence.

. Case ... When statements are used

Function: You can return different results based on the different values of the fields in the query results

The first form of <1>

　　　　SELECT case column_name when value1 then result1 ... [ELSE result] END from table_name;

The second form of <2>

SELECT case when column_name operator value1 then result1 ... [ELSE result] END from table_name;

　　　P:operator for operators, such as +-*/> < etc.

Use of the. Decode function

The Decode function is similar to the first form of case

SELECT DECODE (column_name,value1,result1,..., DEFAULT value) from table_name;

P: If no default value is specified, NULL is returned if there is no matching value.

(6) Oracle Basics-Simple Query