The FROM clause of the SQL statement

How to query data from a table for a word end

Select field name from table name;

Demo:Check out the monthly salary from the S_emp table

Select  salary from  s_emp; (semicolon stands for end)

How to query the contents of multiple fields from a table

Select field Name 1, Field Name 2 from table name;

Demo:Query the ID first_name salary data in the S_emp table.

Select  ID, first_name, SALARY from  s_emp;

How to query values for all fields in a table

Standard notation: one list to

Simple wording: * can replace all field names (but you don't know what fields are in it)

SELECT * from table name;

Mathematical operations of fields (mathematical operations of SQL)

+ -  *  /

Demo:Show the wages and wages minus 100 in the S_emp watch.

Select salary, salary-100  from  s_emp;

After this output, the salary of the underlying record is unchanged.

Calculate a year's annual salary with salary+100:

Select salary, 12* (Salary +) from  s_emp;

One months according to the 22-day calculation, the daily salary:

Select salary, Salary/22  from s_emp; (divisor is not rounded, not the same as C)

Alias a field

Select field Name 1, Field Name 2 alias from table name;

A field or expression can have only one alias (or not), and the alias is automatically processed in uppercase

Alias Displays the alias you have taken as-is

Demo:Calculates each person's annual salary according to the 13-month calculation in a year, and lists each month's monthly salary.

Select salary, salary*13  yearsal from  s_emp;

Two aliases will cause an error:

How to express a string in SQL

SQL use the string in the single quotation marks expression (C use double quotes to express string literals)

' A ' ' Hello World ' is a string

Select  first_name  

How to stitch a string

string concatenation characters: | | (only for Oracle , others can check)

Demo:

Select First_Name, last_name  from  s_emp;

to stitch together a name:

Select First_Name | | Last_Name  name from  s_emp;

to stitch a single quotation mark between names :

(in C expression in language '% ' is the percent , a% is the placeholder,%d ; Here is the same, with two ' represents single quotation marks)

Select First_Name | | "' | | Last_Name name  from  s_emp;

Stitching two single quotes between names :

Select First_Name | | "' | | "' | | Last_Name  name from  s_emp;

Or:

Select First_Name | | "'" | | Last_Name name  from  s_emp;

Processing of NULL values (Null values)

null value (null value) and any value to the result of the operation is null

Demo:One year according to 12 months to calculate, consider the Commission, the total annual salary is the monthly salary multiplied by 12, plus the Commission (the table has the Commission is commission_pct)

salary*12+salary*12* (commission_pct/100)

Or:

salary*12* (1+commission_pct/100)

Null handler function: NVL (PAR1, PAR2)

Can handle any type of data, but requires consistency of PAR1 and PAR2 types

This function returns the value of the par2 when Par1 is empty, and par1 is not NULL when the value of PAR1 is returned

NULL to deal with it early

Demonstrate:

Select  salary, commission_pct, NVL (salary*12* (1+commission_pct/100), 0)  from  s_emp;

No, we have to deal with it as soon as possible, and we can't wait to finish the operation.

Correct wording:

Select  salary, commission_pct, salary*12* (1+NVL (commission_pct, 0)/100)  from  s_emp;

Demo: Display the ID of each employee first_name manager_id, if manager_id is null, show manager_id as-1

Select ID, first_name, manager_id, NVL (manager_id,-1)

Data distinct (encapsulation algorithm we don't need to know)

dstinct will eliminate all duplicate data.

Combined row weight (multi-field row weight, two fields are the same will be excluded)

Demo:

Select  salary from  s_emp; Find out all the salary

Select  distinct  salary  from s_emp; Remove duplicates

Select  Distinct  title, salary from  s_emp; combined row weight

The FROM clause of the SQL statement