SQL Basic Query statement

V sentence for query statement: where (conditional query), having (filter), GROUP by (group), order by (sort), limit (limits the number of results)

A single-table query

1, query the specified column:Select column name from the table name;

2. Query all columns:SELECT * from table name (* denotes all columns in the query table)

3. Remove duplicate rows of data:select DISTINCT column name from table name

Two specified condition query--where

Querying a tuple that satisfies a condition is typically implemented by a WHERE statement: Select column name from table name where condition

Example:

1. Comparison: SELECT * from Lu_order where sku = ' 123 '

2, determine the scope: SELECT * from Lu_order where stage between and

3. Determine the Set: SELECT * from Lu_order where SKU in (' 123 ', ' 1232 ', ' 1232323 ')

4, character matching: select *from lu_order where sku like '%123_ ' (%_ refers to wildcard characters,% can represent any character length, _ represents a single character)

5. Null value: SELECT * from Lu_order where SKU is NULL

6. Multiple conditional logic operations: SELECT * from Lu_order where sku= ' 123 ' and type= ' 234 '

Three ORDER by use

Sorts an isolated tuple by the specified column (one or more) in ascending (ASC) or descending (DESC)

1. Ascending: SELECT * from Lu_order ORDER by ID ASC

2. Descending: SELECT * from Lu_order where sku= ' 122 ' ORDER BY sku DESC

Four GROUP BY

Groups the results of a query by one or more columns, with values equal to one group. In general, in order to refine the function of the aggregate function of the object, if not grouped, the aggregate function is the effect of the entire query results; If grouped, it is the function of one aggregate function per group.

The common aggregation functions are:

For example:

1, ungrouped, using aggregate functions: SELECT COUNT (*) from Lu_order---> calculates the number of tuples in the table

2, grouping, using aggregate functions: Select Ordernumber,count (*) from Lu_order group by ordernumber--->ordernumber the same as a group, To derive the number of tuples contained in each group

Five HAVING clauses

The grouped results are filtered to get the eligible groups. Note: If you use having, you must use GROUP by in the query statement, or you will get an error.

For example:

1, select Ordernumber,count (*) from Lu_order GROUP by OrderNumber have ordernumber in (' 1 ', ' 2 ', ' 3 ')--->orde Rnumber the same group, the number of tuples contained in each group

Six limit words

Limits the number of bars the result displays.

For example:

1, query the first 3 rows of data: SELECT * from Lu_order limit 0,3

Seven Multi-table query

The table is connected to the table by certain conditions.

1. Natural connection: Select a.*,b.* from Lu_order a,lu_order_detail b where A.ordernumber=b.ordernumber

2. Self-Connection: Select a.*,b.* from Lu_order a,lu_order b where A.ordernumber=b.ordernumber

3. Left join: Select a.*,b.* from Lu_order left out join Lu_order_detail

4. Right connection: Select a.*,b.* from Lu_order-out join Lu_order_detail

Eight nested queries

In Select-from-where. Called a query block, nesting a query block in the where or HAVING clause of another query block is called a nested query.

For example:

1. Select Ordernumber,sku,carrier from Lu_order where OrderNumber in (select OrderNumber from Lu_order_detail where O Rdernumber= ' 1 ')

SQL Basic Query statement