SQL reintroduction (2)-select statements, expressions, condition statements, and operations

1. Check for missing select statements

Select is the most commonly used statement in SQL. It is mainly used to query data, especially when used in combination with some conditional query statements to retrieve specific data. The following are some notes for learning select statements:

(1) All and distinct

The basic usage of select is: Select column1, column2. .. From Table1

Select All is equivalent to select, So we usually do not see such expressions as select all column1.... from Table1.

Select distinct is used to remove duplicate data in a column. If multiple columns exist, it is used to remove duplicate data with identical columns. Although distinct is not commonly used and uncommon, it is an internal SQL statement and is more efficient in removing duplicates. In addition, it has been mentioned that distinct cannot differentiate variable length fields (to be verified)

(2) alias Problems

Aliases are required, such as select column1 + 2 alias1 from Table1;

Or select (column1 + 2) alias1 from Table1;

Or select column1 + 2 = alias1 from Table1;

Different SQL interpreters have different usage.

 

 

2. Expression

SQL expressions are similar to formulas, but SQL expressions are written in Structured Query Language (SQL. SQL expressions can be used to query a specific data set from a database. You can sort, group, and select SQL expression fields. An expression can return a value with a wide range of types, including various types of data, such as numeric characters and logical types.

 

3. Operation

Operations can be classified into six groups: numeric, comparative, numeric, logical, remark, and hybrid;

(1) numeric operations

Numeric operations include addition, subtraction, multiplication, division, and modulo. The order of operation is first multiplication, then division, and then addition and subtraction.

(2) comparison:

The comparison operation compares two expressions and returns one of the three values true, false, and unknow. The unknow mainly appears when the field is empty. For empty fields, use "is null" instead of "= NULL" to judge.

Comparison includes =,>, >=. <, <=, not equal to (<> or! =) In the output result, the corresponding column is intercepted based on the length of the value to be compared on the right of the symbol, such as the column name: Bobo, Lili, Vivi;

Enter select name from table_name where name <'ww '. When the result is obtained, NA: Bo, Li, VI;

(3) character operations:

Like: for non-precise queries ("=" for exact queries ). The like expression contains wildcards "%" and "_". "%" Does not limit the location and number of matched characters. "_" is used to specify symbols at specific positions. For example, "A %" (starting with a), "% A" (ending with a), and "_ a_a" (the character length is 4, the second and fourth characters are a), "88_2_2%" (any length, the first two digits are 8, and the fourth six digits are 2 digits ). Usage: Select * from friends where firstname like '_ l % '.

|: Connector that connects fields to output. Usage: Select lastname | ',' | firstname name from friends

(4) logical operations:

Logical operations are used to combine two or more conditions in the WHERE clause of SQL.

And or not operations. Be sure to use is not null to judge that the field is not null.

(5) Set Operations

Union: returns two query results and removes duplicates. Usage:

Select name from softball Union select name from football

Union all: returns the results of two queries but does not remove the duplicates. Usage:

Select name from softball Union all select name from football

Intersect: returns the rows in the two tables. Usage:

Select * from football intersect select * from softball

Minus: The returned records exist in the first table but do not exist in the second table. Usage:

Select * from football minus select * from softball

(6) subordinate operations

In: Select * from friends where state in ('CA', 'co', 'La ') is equivalent

Select * from friends where State = 'CA' or state = 'co' or state = 'La'

Between: Select * from price where wholesale between 0.25 and 0.75 are equivalent

Select * from price where wholesale> 0.25 and wholesale <0.75