SQL Application and Development: (vii) Data manipulation · Check (a) conventional type

Databases are created to facilitate efficient management of information, and people want the database to provide the data information they need at any time. Therefore, for the user, the data query is the most important function of the database. After an object is created in the database and data is added to the base table, the user can retrieve specific information from the database.

1. Conventional type

Regular data queries are done through the SELECT statement. The SELECT statement retrieves data from the database as requested by the user, and returns the query results as a tabular form.

1.1SELECT Statement Overview

The SELECT clause is one of the most commonly used statements by SQL programmers and is one of the most flexible and widely used statements in the SQL standard. The SELECT statement is a query expression that begins with a keyword Select and includes a large number of elements that make up the expression. The SELECT statement can query rows from the database as requested by the user, and allows one or more rows or columns from one table or multiple tables to be selected to satisfy a given condition, and the data is collated in a user-defined format and returned to the client.

The SELECT statement can find the database exactly, and the SQL syntax for the SELECT statement is straightforward and structured. Of course, a SELECT statement can also be used for fuzzy lookups.

Because the complete statement of the SELECT statement is complex, only its main clauses are listed here. The main format of the SELECT statement is as follows:

SELECT [All | DISTINCT] {* | <select_list>}

From <table_reference> [{, <table_reference>} ...]

[WHERE <search_condition>]

[GROUP by <grouping_specifition>]

[Having <search_condition>]

[ORDER by <order_condition>]

Where [] represents an option, the SELECT clause and the FROM clause are required, and the other clauses are optional. The specific meanings are as follows:

SELECT clause [Used to specify the columns returned by the query.] The order of the columns in the SELECT clause determines their order in the result table. ]

From clause [used to specify the source table from which to query rows.] ]

WHERE clause [used to specify the search criteria for qualifying the returned rows. ]

GROUP BY clause [The grouping criteria used to specify the result of the query, which is the inductive information type.] ]

Having clause [used to specify the search criteria for a group or aggregation.] ]

ORDER BY clause [used to specify how the result set is sorted. ]

1.2 Using the SELECT clause

The SELECT clause is the first part of the query, specifying the required columns, the order of the columns, and the column names in the SELECT clause. There are three types of select clauses, namely:

The Select column is able to get the listed columns in that form and place them in the order in which they are listed, as well as rename the column names. For example

SELECT Customer Name as name, customer address as address, contact phone as Telephone_num

From Customer information

）

The name of the select * or select table. * in this form, you can get all the columns in the table, and they are placed in the same order as in the table. However, you cannot rename the column name again in the SQL statement. Also, when adding columns other than columns in the table, you need to enter the table name before the asterisk "*", separated by a period between the table name and the asterisk. For example

SELECT *

From salesman information

）

SELECT DISTINCT column in this form, in addition to completing the function in the form of select columns, you can also remove duplicate rows from the results. For example

SELECT DISTINCT Customer Address

From Customer information

）

1.3 Using the WHERE clause

Users often do not need to retrieve all the data when querying the database, but only need to query some of the information that satisfies a given condition, and then need to add a condition in the SELECT statement to select some of the records. This uses the WHERE clause to specify the criteria for the query to return rows, which is used to pick the rows of data that need to be retrieved. The basic format of a SELECT statement with a WHERE clause is as follows:

SELECT <select_list>

From <table_reference>

WHERE <search_condition>

Where,<search_condition> is the condition that should be met for the row record to be returned when the query is specified, which consists of an expression and a logical operator.

The WHERE clause gets the value returned from the FROM clause, and each row is evaluated against the search criteria. If the evaluation is true, it is returned as part of the query result, and if evaluated as unknown or false, the rows are not included in the query results. The key to the assessment is the search criteria specified by <search_condition>.

Use of comparison operators in 1.3.1WHERE clauses:

Its syntax format:

SELECT <select_list>

From <table_reference>

WHERE <expression1> <comparision operator> <expression2>

For example:

SELECT supplier name, Supplier City, contact name, contact number

From supplier name

where supplier city = ' Beijing '

Use of logical operators in 1.3.2WHERE clauses:

The format is as follows:

SELECT <select_list>

From <table_reference>

WHERE {not <expression> | <expression> <logical operator> <expression2>}

Where <logical operator> represents a logical operator, it can be any of the and, or two logical expressions, with not, to put not in front of an expression.

For example:

SELECT operator name, inbound permissions, outbound permissions, contact phone

From operator information

WHERE warehousing Personnel =1 and out of the library staff =1

The use of the in condition in the 1.3.3WHERE clause:

SELECT <select_list>

From <table_reference>

WHERE <expression> [NOT] in <value_list>

Where [not] is an option,<value_list> represents a list of values.

The In keyword is used to display membership in a group when a condition is specified in the WHERE clause using the in or not keyword. The IN keyword is used when there is a list of discrete values that satisfies the criteria.

A valid value list cannot contain null worth of data because a null value is not allowed in the data when the In keyword is used as the specified condition in the WHERE clause.

For example:

SELECT Customer Name, customer address, contact number

From Customer information

WHERE client address in (' Xuanwu District ', ' Beijing Haidian District ')

Use of the like condition in the 1.3.4WHERE clause:

The format is as follows:

SELECT <select_list>

From <table_reference>

WHERE <expression> [not] like ' string '

Where [not] is optional, ' string ' represents the string used for comparison. In the process of querying with a WHERE clause, wildcard characters can be used in string strings in order to achieve fuzzy matching of strings.

For example:

SELECT Product Name, supplier number, origin, Unit price

From product information

WHERE commodity name like ' [a-m]% '

Use of IS NULL in a 1.3.5WHERE clause:

The format is as follows:

SELECT <select_list>

From <table_reference>

WHERE <column_name> is [not] NULL

where,<column_name> represents the column name, [NOT] is optional.

For example:

SELECT consumer name, consumer spending amount, consumer city, contact number

From Customer information

WHERE Contact Phone is NULL

1.4 Using the ORDER BY clause

The data obtained by the SELECT statement does not have any sort of order according to certain rules. That is, the sorted order of the data groups extracted this time can be quite different from the sort order of the next extracted data group. To facilitate reading and use, it is best to sort the results of the query. In the SQL language, the ORDER BY clause is used to specify whether the query results are sorted in ascending order (using the keyword ASC) or descending (using the keyword desc). The basic syntax format for the ORDER BY clause is:

SELECT <select_list>

From <table_reference>

ORDER by[{<order_by_expression> []ASC | DESC} ...]

Where,<order_by_expression> represents the column name or column alias and expression for the sorted column, or a negative integer that represents the location of the name, alias, or expression within the selection list. If you want to sort by multiple columns, the priority of the sort is determined by the order of the columns after the order by; ASC specifies that the query results are sorted in ascending order, and DESC specifies that the query results are sorted descending.

The ORDER by clause is an optional clause. When using the ORDER BY clause, you only need to specify one or more columns and an optional keyword ASC or DESC (no column uses only one keyword). If no keyword is specified, ASC is the default keyword.

For example, from the database sales management system, the descending order of the outbound date is returned in the form of "out of stock Number", "Customer number", "Inventory number", and "Outbound date".

SELECT out of stock order number, customer number, inventory number, outbound date

From out of stock list information

ORDER by Outbound Date DESC

The column name after the ORDER by clause is usually the column name in the select list, while the other simple method is to replace the column name with a number that specifies the position of the sorted column in the select list, with a value of "1" starting from left to right, plus 1. For example, in the query statement above, change the column name "Outbound Date" after the ORDER BY clause to the number "4" because the "outbound date" position in the select list is "4".

After executing the statement, its query results are exactly the same as the result query for the above statement.

As another example, from the database sales management system, return the revenue details in the form of "revenue detail number", "Revenue date", "revenue amount", and "profit Amount", and the results of the query are sorted by the descending order of "revenue amount" and then by the "profit amount" in ascending order.

SELECT revenue Detail number, revenue date, revenue amount, profit amount

From revenue details

ORDER by revenue amount DESC, profit amount ASC

1.5 using the GROUP BY clause

When using a SELECT statement for a data query, if you need to classify the values of a column of data and then query on the basis of the classification, you can use the group by child. The GROUP by clause is used to summarize the information type, summarizing the relevant data. The GROUP by clause can be included in the SELECT statement, regardless of whether the WHERE clause is used. The syntax format for the GROUP by clause is as follows:

SELECT <select_list>

From <table_reference>

GROUP by <grouping_specificition>

Where,<grouping_specificition> can be decomposed into the following format:

<column_name> [{, <column_name> ...}

| {ROLLUP | CUBE} (<column_name> [{, <column_name>} ...])

The cube in the preceding statement indicates that rows that are counted by group are returned in addition to the columns specified by the GROUP BY clause. The results returned are sorted first by the first conditional column of the grouping, then sorted by the second condition column, and so on. The statistics row includes data statistics for various combinations of columns specified by the GROUP BY clause. Rollup differs from cube in that it returns only the statistic rows for the column specified by the first grouping condition. Changing the order of the columns changes the number of result rows that are returned.

For example, from the Sales Information table in the database reseller, in the Salesperson number group, you query the sales amount for each salesperson and sort by the descending order of the sales amount.

SELECT salesperson number, sum (amount) as Sales amount

From Sales information

GROUP by salesperson Number

ORDER by 2 DESC

The WHERE clause can still be used in SELECT statements that include the GROUP BY clause. For example, if in the previous example, if only the column "Amount" is greater than 100, you can add the "Sales Amount", you can use the following statement to achieve:

SELECT salesperson number, sum (amount) as Sales amount

From Sales information

WHERE Amount > 100

GROUP by salesperson Number

ORDER by 2 DESC

in the previous two examples, the GROUP BY clause specifies a column, but you can also specify additional columns as needed, so that the sentence can create subgroups that group data within the primary key range. For example, from the Sales Details table in the database "Jewelry sales System", the "unit price" and "total profit" of the sales are grouped by the "detail number" and "Unit Price", and are sorted in ascending order of "detail number".

SELECT detail number, Unit price, SUM (profit) as total profit

From sales details

GROUP by detail number, Unit price

ORDER by detail number

The operator rollup and Cube have been briefly introduced in the syntax of the GROUP BY clause. The two operators are functionally similar, and when they are added to the GROUP BY clause, they return additional digest values in the query results.

For example, in the following SELECT statement, the GROUP by clause applies the ROLLUP operator to the column "detail number" and "Unit Price":

SELECT detail number, Unit price, SUM (profit) as total profit

From sales details

GROUP by detail number, unit price with ROLLUP

When the above statement is executed, a row is added for each value in the Detail Number column in the query results. Three additional rows provide the total quantity corresponding to each value in the Detail Number column. The last added line represents the total number of all worth in the "detail number" column.

The CUBE operator returns the same data as the rollup operator, but more information than the ROLLUP operator returns. In the following SELECT statement, only the rollup is replaced with the operator cube:

SELECT detail number, Unit price, SUM (profit) as total profit

From sales details

GROUP by detail number, unit price with CUBE

As you can see, three additional rows are added to the query results, and each row corresponds to each of the different values in the Detail number column. Unlike the rollup operator, Cube summarizes the values for each self-set.

1.6 Using the HAVING clause

a HAVING clause is a search condition that specifies a group or aggregation. The HAVING clause is usually used with the GROUP BY clause, and after the query and statistics of the data results are completed, you can use the HAVING clause to further filter the results of the grouping, and if you do not use the GROUP BY clause, the HAVING clause has the same function as the WHERE clause. The HAVING clause is in the format:Having <search_condition>Having clauses can refer to the select list, follow the "detail number" and "Unit Price" groups, query the sales "unit price" and "total profit", and require that only "total profit" in the query results is greater than 150 information. SELECT Detail number, Unit price, SUM (profit) as total profitFrom sales details GROUP by detail number, Unit priceHaving SUM (profit) >If there is more than one condition in the HAVING clause, the search criteria for the HAVING clause can be combined by the keyword and, or, or not. For example, in the previous example, the result of the request returned was "unit price" greater than 300 or "total profit" greater than 150 information. SELECT Detail number, Unit price, SUM (profit) as total profitFrom sales details GROUP by detail number, Unit priceHaving a unit price > OR SUM (Profit) >
about the database check operation, not to be continued ...

SQL application and Development: (vii) Data operation • Check • (i) Regular type