SQL query Statement's select detail and view concepts

SELECT statement

The core function of learning database is to master the use of commands such as adding, deleting, checking, and changing, in which query statements are the most complex and most important. This article focuses on the use of command related to query statements.

There are three types of query statements: Simple query, multi-table query, subquery, union query (Union)

1. Simple query

SELECT [DISTINCT] * from tb_name [WHERE clause | Orger by words | Limit Words | GROUP by sentence];

where [Distinc] indicates that duplicate field values are displayed only once

*: Represents all the fields in the table, this is a wildcard, and of course you can specify the fields you want to display

Multiple tables and other SELECT statements can be followed from the FROM clause.

You can also create table aliases after the from statement, such as

From student as S

WHERE Qualification: Indicates a matching condition or filter condition

You can follow the following command after the WHERE clause:

Boolean relationship expressions, expression operators are: >=, <=, >, <, =,! =, or <>

A logical relationship expression with an expression operator: and (&&), or (| | ), not (!)

Between ... And ...: Represents a range, including a boundary value

Like ' string ': Fuzzy match, you can use wildcard characters "%" and "_"

%: Any character that represents any length

_: Represents any single character

Regexp,rlike ' pattern ': indicates that a regular expression can be used to match a field

In: Indicates whether a data is in a collection

Is null: Indicates whether the value of a field is empty

is NOT NULL: Indicates whether the value of a field is not empty

GROUP BY clause

GROUP by Col_name

Group BY groups the specified columns, using aggregate functions on the grouped data;

The specified column returns a value after the group query is complete, so the group by statement is typically used with the aggregate function

Common aggregation functions
SUM (): Find the sum of a field

Max (): Find the maximum value in a field

Min (): Find the minimum value in a field

AVG (): Find the average of the field

Count (): Find the total number of the field or row number

HAVING clause

Having usage is roughly the same as where, the only difference is where is used for the first query, and the having statement is used for two queries.

Order clause

ORDER by Col_name [asc| DESC]

Col_name indicates which field to sort

[asc| The DESC]:ASC represents a sort in ascending order, and Desc is sorted in descending order. The default is to sort in ascending order.

Limit clause

LIMIT [Offset,] count

Limit at the end of the statement, play the role of restricting entries

Offset: Represents the offset. That is, ignoring the previous entry, starting from offset+1

Count: Indicates the entry to be taken out or displayed

If offset is not written, it is equivalent to limit 0,count

The SQL statements are executed in the following order:

Start→from statement →where clause →group BY clause →having clause →order BY clause →select statement →LIMIT clause →end

If some of the above statements are executed, there is no need to execute them.

2. Multi-Table Query

A multi-table query is a query in multiple tables and displays the results of the query. Multiple tables need to be concatenated because they need to be queried in more than one table. There are several ways to connect here:

Cross-linking: Multiple tables are concatenated using the Cartesian product. General mates with the WHERE clause

Natural joins: Joins the same field with the same value row

Outer joins: Includes left and right outer connections

Left outer connection: take left table as the standard, go to right table to find matching data, find no match, use NULL to make up

Tb1_name1 left JOIN tb2_name on ... (on followed by match condition)

Right outer connection: to the right table, to the left table to find matching data, find no match, with null

Tb1_name1 right JOIN tb2_name on ... (on followed by match condition)

Self-linking: A table is created by setting aliases to produce 2 alias tables, which are connected by a certain condition.

3, sub-query

In a comparison operation, if a subquery is used, the subquery can return only one single value.

If the subquery returns a result with multiple values or a collection, you can use the IN keyword instead of the operator

Sub-queries are divided into the following three types:

Where sub-query: The comparison condition of the inner query result as the outer query

From sub-query: The query results of the inner layer as a temporary table for the outer SQL query again

Exists sub-query: The outer query results to the inner layer, see if the inner layer of the query is set up

4. Union (Union) query

Combines the results of two queries and multiple queries, so the two queries display the same number of columns, preferably with the same data type.

Views View

A view is a virtual table formed by the results of a query. It is a table-based query result. Therefore, when certain fields or properties of a table change, the view may also change.

Creation syntax for views

CREATE View name as SELECT statement

Syntax for deleting views

DROP View Name

The role of the view

1. Can simplify the query

2. Permission control is possible

Closed the permissions of the table, but open the corresponding view permissions, the view only open part of the data

3. Large Data sub-table can be used

Changes to the View

1. View additions and deletions will also change the table's

2. Views are not always changed or deleted

3, when the data of the view corresponds to the table data one by one, you can modify

4. For view Insert also note that the view must contain columns that do not have default values in the table

This article is from the "Linux Learning path" blog, so be sure to keep this source http://xslwahaha.blog.51cto.com/4738972/1576786

SQL query Statement's select detail and view concepts