Select statement of Oracle

First, the function of the select statement:

Projection: gets one or more columns of data in a table

Select (selection): get one or more rows of data in a table

Join: multi-table federated query

II. SQL sentence Writing Standard

1. Case insensitive

2. You can change the line to write

3. Use a semicolon to indicate the end of a line

4. Usually one clause on one line

5. Keywords cannot be abbreviated or wrapped.

III. The grammatical structure of select sentences

SQL > select * | {[distinct]} column | expression [alias],. From tables

Select: specify the displayed column, followed by wildcards, enumerated columns, expressions, and four operations

From: specifies the selected column source, followed by the result set

1. Query all rows and columns in the table

SQL > select * from dept

SQL > select * from emp

2. Query the specified columns of interest in the table

SQL > select empno,ename,sal from emp;-enumerate the columns to be queried

3. Query all the data under the current user

SQL > select * from tab

Fourth, use arithmetic expressions in select statements

SQL > select ename,sal+300 from emp

About null values: null is an uncertain value, it is not equal to 0 or a space

Question: do four operations with null values, what is the result?

5. Alias the column with select statement

1. Used to rename columns

SQL > select ename name from emp

SQL > select ename as name from emp

SQL > select ename "new name" from emp

6. The "| |" connection operation of select statement

1. Concatenate: columns and strings columns and column strings and strings

SQL > select ename | | 'sal is' | | sal from emp

2. Compress duplicate values

SQL > select distinct job from emp

7. The use of spool in select statements

1. Output the structure level to a file

Spool xxx

Select * from emp

Spool off

Generate scripts and modify

1. Use @ to run

2. Run in shell

What's the use of using shell? At work, the process needs to call sqlplus in the shell environment to enter the database to execute some sql, which is available so that we do not need manual intervention (no @)