SQL data retrieval operations _ MySQL

SQL data retrieval 1. use the Select clause to retrieve records

The Select clause is the core of each data query. It tells the database engine what fields are returned.

The common form of Select clauses is:

Select *

This clause indicates "returning all fields that can be found in the specified record source ". This command is convenient because you do not need to know the name of the field retrieved from the table. However, retrieving all columns in a table is inefficient. Therefore, because only the required fields are retrieved, the query efficiency can be greatly improved.

2. use the From clause to specify the record source

The From clause describes the record source for query and retrieval. The record source can be a table or another storage query. You can also retrieve records from multiple tables. Example:

Select * From students to retrieve all records in the students table

3. use the Where clause to describe conditions

The Where clause tells the database engine to limit the records it retrieves based on one or more provided conditions. A condition is an expression that can be true or false. Example:

Select * From students Where name = "shadow"

The name field in students is returned as the shadow list. the returned results do not have a specific Order unless you use the Order By clause.

Note: the text string delimiter in the Where clause is a double quotation mark. in VB, it is changed to a single quotation mark because the string delimiter in VB is a double quotation mark.

Supplement: The And Or logic can be used to link two Or more conditions to create a more advanced Where clause.

For example, Select * From students Where name = "shadow" And number> 100

Returns the list of shadow numbers with names greater than 100.

Example: Select * From students Where name = "shadow" And (number> 100 Or number <50)

Returns a list with name as shadow, number greater than 100, or less than 50.

Operators used in the Where clause

Operator functions

<Less

<= Less than or equal

> Greater

> = Greater than or equal

= Equal

<> Not equal

Between is within a certain value range.

Like matches a pattern

In is included In a value list.

The equals and non-equals operators in SQL have the same meaning and use as those in VB.

Example: (1). Between operator

Use cust

Select * From students

Where number Between 1 and 100

The Between operator returns all record values within the defined boundary. In this example, all records in the range from 1 to 100 are returned.

(2). Like operator and wildcard

Use cust

Select * From students

Where name Like "% %"

The Like operator matches the record to a pattern you specify. In this example, any string containing "shadow" is returned.

Meanings of the four wildcards

Wildcard description

% Represents zero or multiple arbitrary characters

_ (Underline) represents any character

[] Any single character in the specified range

[^] Any single character not within the specified range

All examples are as follows:

Like "BR %" returns any string starting with "BR"

Like "br %" returns any string starting with "Br"

Like "% een" returns any string ended with "een"

Like "% en %" returns any string containing "en"

Like "_ en" returns the three strings ended with "en ".

Like "[CK] %" returns any string starting with "C" or "K"

Like "[S-V] ing" returns a four-character string ending with "ing" starting from S to V.

Like "M [^ c] %" returns any string starting with "M" and the second character is not "c.

4. sort the results using Order

The Order By clause tells the database engine to sort the records it retrieves. You can sort any field or multiple fields in ascending or long order.

After a formal Select query, it contains an Order By clause, followed By the field to be sorted (multiple can be entered.

Example:

Use cust

Select * From students

Where name Like "% %"

Order By number

Sorts the returned results by number.

Sort in descending order

To sort in the long order, you only need to use the Desc keyword after the sorted field.

Example:

Use cust

Select * From students

Where name Like "% %"

Order By number Desc

5. use Top to display the first or last record in a range

You can use the Top keyword to display only a few records before or after a large record. In a query, the Top keyword and the sorting clause limit the result set to a few records or display part of the entire result set by a certain percentage.

Example:

Select Top 3 * From students return the first three records in the students table

Select Top 10 Percent * From students return the first 10% records in the students table

Select Top 3 * From students Order By number desc return the three records with the maximum number (last) in the students table

6. use As to alias the field name

There are two reasons for this:

The field name of the table involved is very long, and it is easier to process the field in the result set.

The created query generates some computing or aggregate columns and needs to be named.

No matter what the reason for the field to be aliased, the As clause can be easily used in SQL.

Example: Select number As student ID, name As name From students

7. merge query

Union Query is used to merge the content of two tables with the same field structure. it is useful if you want to display irrelevant records in multiple record sources in a single result set.

Example: Select *

From students

Union

Select *

From students1

This query result set combines the records in students and students1 into a single result, and the output is exactly the same as before the original table is archived.

Note: By default, duplicate records are not returned for merged queries (this function is useful if the record archiving system does not delete the corresponding record after copying the record to the archive table ), you can add the All keyword to the merge query to display duplicate records.

Example: Select *

From students

Union All

Select *

From students1

Duplicate records are not processed when the students table and students1 table are displayed in the merged query.

Supplement: The Union operator allows two or more query results to be merged into one query result set. If the Union and Join operators are compared, the Union operator increases the number of rows while the Join operator increases the number of columns. When using Union, note that the structure of the columns in the two results must match, and the data type must be compatible.

The syntax of the Union operator is as follows:

Select select_list

From clause

Where clause

Group By clause

Having clause

Union [All]

Select select_list

From clause

Where clause

Group By clause

Having clause

Order By clause

Compute clause

For the Union operator, the following points need to be explained:

· By default, the Union operator deletes all redundant rows. If the All option is used, redundant rows are not deleted.

· All select_list columns in the Union statement must have the same number of columns, compatible data types, and appear in the same order.

· In the result set, the column name comes from the first Select statement.

8. connection query

In actual use, you often need to retrieve data from two or more tables at the same time. The connection allows you to retrieve data from two or more tables at the same time and specify one or more columns in these tables as the connection condition. In SQL Server, two connection syntaxes can be used, one is the Ansi connection syntax, which is used in the From clause and the other is the SQL Server connection statement, this is the connection used in the Where clause.

The Ansi connection syntax is as follows:

Select table_name.column_name, table_name.column_name ,...

From {table_name [join_type] Join table_name On search_conditions}

Where [search_conditions]

In the Ansi syntax format, you can use the Ansi connection keyword to determine the connection format used. For example:

☆Use the Inner Join keyword, and the result set contains only rows that meet the conditions.

☆Use the Cross Join keyword. The result set contains a combination of all rows in two tables.

☆Using the Outer Join keyword, the result set contains both the rows that meet the conditions and all the rows in a specific table.

The SQL Server connection syntax is as follows:

Select table_name.column_name, table_name.column_name ,...

From [table_name, tab