Oracle's Select Uses

1.select:

Syntax:

SELECT [DISTINCT] {*,column [alias],...}

From table

Note: [] the contents of the content represent the optional

* represents all Columns

the DISTINCT keyword can only be followed by the select keyword

Specify the column to query behind the SELECT clause

the table behind the FROM clause followed by the query

My name is first_name.last_name;

The 2.select statement can perform arithmetic operations on all values of the specified column.

Grammar:

Select Col_name operator numbers

From Tb_name;

Note: The SELECT statement never modifies the original data.

3. distinguish names of columns for queries

Grammar:

Select Old_column [As] New_column_name

From Tb_name;

4. Use | | you can make multiple columns of values or columns and special strings merge into one column for display

Grammar:

Select Col_name| | ' Spe_char ' | | Col_name

From Tb_name

' Spe_char ': Use this syntax if the value of a column is to be displayed with a special string connection.

5. null is worth the substitution operation

Grammar:

Select NVL (Col_name,change_value)

From Tb_name;

NVL2 (col_name, value not shown as empty, empty displayed );

6. using the distinct keyword, you can display the duplicate records only one

Grammar:

SELECT DISTINCT Col_name,col_name ...

From Tb_name;

Note 1:distinct keyword can only be placed behind the select keyword

Example:Select Id,distinct Title

From S_emp;

The statement is syntactically incorrect !!!!!

Note 2: If More than one column appears after the DISTINCT keyword, it is considered a duplicate record when multiple columns are combined to go heavy, that is, when the values of multiple columns are the same .

Test table:

ID ID2

1 2

1 3

2 4

3 4

3 4

SELECT DISTINCT Id,id2

From Test;

The results shown are:

ID ID2

1 2

1 3

2 4

3 4

7.sqlplus Command

A: Append content to the command line of the current operation

A test

C: modify content at the command line of the current operation

C/old_char/new_char

Clear buffer: clears the current cached command

Del: Delete the current action row

Del line_num Specifies to delete the first few rows

I: The next line of the current action command line inserts content

L: View Cache Commands

L Line_num: View the specified command line

N Text: replace The entire line of the nth row

! : Back to terminal command ------Linux In the environment

!clear: Clear Screen //

$: followed by terminal commands

$CLS---------> Windows Environment

/: Execute cache SQL command

Save file_name: saves cache commands to file_name (append replace)

Get file_name: Extract the contents of a file to sqlplus

Start and @ file_name: Execute sql command in file

Edit file_name: editing files using the VI Editorset up by the terminal ( for windows Notepad)

Spool file_name saves the next SQL statement and SQL Run results to a file (append)

Sql1

Result1

Sql2

Result2

...

Spool off spool function

Exit: Exit

8.select Id,last_name,first_name, salary, dept_id

From S_emp;

The results are not good-looking, and through column makes our display interface look good.

Colu last_name format A15;

Colu first_name format A15;

Column has no change in the data table data Ah, no, it just changes the display. Is it a SQL command? No, it is the sqlplus command. In addition to the effect just now,

Let's see how it works.

COLUMN last_name HEADING ' employee| Name ' FORMAT A15

. Give last_name Take the alias employee| Name, the vertical bar represents the line break.

. A15 means 15 bytes long, a short bar is one byte long

COLUMN salary JUSTIFY left FORMAT $99,990.00

. Salary JUSTIFY Left: Just change the column name to show as Easy

. Format $99,990.00: control display format for front plus $ , "," for delimiter , 0 or 9 represents a number ( wildcard character ),0 is the replacement alignment value , The number of bits is not enough to be replenished and can be mixed .

COLUMN start_date FORMAT A8 NULL ' not hired '

. if start_date If the value is empty, it is displayed as ' Not hired ' ;

. Format cannot be directly followed by null, first A8 or A10;

. NULL ' not hired ' and NVL a bit different, NVL requires type matching

Column displays all formatting for the column format

Column last_name shows the settings for the last_name column display

Column Last_Name Clear removes the case for last_name column formatting

Clear column clears formatting from all column

Column formatting, where the column is not specific to a table .

Ex

1234 Column 99.99--> ######// can not be displayed when error , just show # # # #

Column Columname Show restrictions on Columns

Oracle's Select Uses