SQL Application and Development: (7) Data Operations & #183; query & #183; (1) Regular and SQL

SQL Application and Development: (7) Data Operations · query · (1) Regular and regular SQL

Databases are created to facilitate the effective management of information. people hope that the database can provide the required data information at any time. Therefore, for users, 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, you can retrieve specific information from the database.

1. Regular

Regular data queries are performed using SELECT statements. The SELECT statement can retrieve data from the database according to user requirements and return the query results in tables.

1.1SELECT statement Overview

The SELECT clause is one of the most commonly used statements by SQL programmers. It is also one of the most flexible and widely used statements in SQL standards. A select statement is a query expression that starts with the keyword SELECT and contains a large number of elements that constitute the expression. SELECT statements can be used to query rows from the database according to user requirements, and one or more rows or columns that meet the given conditions can be selected from one or more tables, sort the data in the format specified by the user and return it to the client.

The SELECT statement can precisely search the database, and the SQL syntax of the SELECT statement is intuitive and structured. Of course, the SELECT statement can also perform fuzzy search.

The complete SELECT statement is complex, so only the 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>]

[] Indicates an option. The SELECT clause and the FROM clause are required. Other clauses are optional. The description is as follows:

SELECT clause [used to specify the columns returned by the query. The order of 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 conditions for the returned rows.]

The group by clause [used to specify the grouping conditions for query results, that is, the inductive information type.]

HAVING clause [used to specify search conditions for a group or aggregation.]

The order by clause [used to specify the sorting method of the result set.]

1.2 use the SELECT clause

The SELECT clause is the first part of the query. In the SELECT clause, specify the sequence of the required columns and column names. The SELECT clause has three forms:

SELECT columnIn this form, you can obtain the listed columns and place them in the order of the listed columns. You can also rename the column names. (For example:

SELECT customer name as name, customer address as address, contact number AS TELEPHONE_NUM

FROM customer information

)

SELECT * or SELECT table name .*In this form, all columns in the table can be obtained, and their placement order is the same as that in the table. However, you cannot rename a column name in an SQL statement. In addition, when adding columns other than the columns in the table, you must enter the table name before the asterisk (*). The table name and asterisk are separated by a period. (For example:

SELECT *

FROM salesman Information

)

Select distinct ColumnIn this form, in addition to the SELECT column form function, you can also remove duplicate rows in the results. (For example:

Select distinct customer address

FROM customer information

)

1.3 Use the WHERE clause

When you query a database, you do not need to retrieve all the data. Instead, you only need to query some of the information that meets the given conditions. In this case, you need to add conditions to the SELECT statement, to select some of the records. This uses the WHERE clause to specify the conditions for querying the returned rows. The WHERE clause is used to select the rows 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>

<Search_condition> is the condition that the row record to be returned when the query is specified. This condition consists of expressions and logical operators.

The WHERE clause obtains the value returned by the FROM clause. Each row must be evaluated based on search conditions. If the evaluation is true, it is returned as part of the query results. If the evaluation is unknown or false, these rows are not included in the query results. The key to evaluation is the search condition specified by <search_condition>.

Use of comparison operators in 1.3.1WHERE clauses:

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'

1.3.2use of logical operators in the WHERE clause:

The format is as follows:

SELECT <select_list>

FROM <table_reference>

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

<Logical operator> indicates a logical operator. It can be any one of the two logical expressions, "AND" OR ". If NOT is used, NOT must be placed before the expression.

For example:

SELECT Operator name, warehouse receiving permission, warehouse picking permission, contact number

FROM operator information

WHERE warehouse receiving personnel = 1 AND warehouse picking personnel = 1

Use of the IN condition IN the 1.3.3WHERE clause:

SELECT <select_list>

FROM <table_reference>

WHERE <expression> [NOT] IN <value_list>

[NOT] is optional. <value_list> indicates the Value List.

When the IN or not in keyword is used IN the WHERE clause to specify a condition, the IN keyword is used to display the member relationships IN a group. When there is a list of discrete values that meet the condition, the IN keyword is used.

The valid value list cannot contain NULL value data, because 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 customer address IN ('xuanwu district, Beijing', 'haidian district, Beijing ')

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'

[NOT] is optional. 'string' indicates the string used for comparison. To implement fuzzy match of strings, wildcards can be used in string strings during query using the WHERE clause.

For example:

SELECT Product Name, supplier ID, origin, unit price

FROM Product Information

WHERE item name LIKE '[A-M] %'

Use of is null in 1.3.5WHERE clause:

The format is as follows:

SELECT <select_list>

FROM <table_reference>

WHERE <column_name> IS [NOT] NULL

<Column_name> indicates the column name. [NOT] is optional.

For example:

SELECT consumer name, consumer consumption amount, city of the consumer, contact number

FROM customer information

WHERE contact number IS NULL

1.4 Use the order by clause

The data obtained by the SELECT statement is not sorted according to certain rules. That is to say, the sorting order of the extracted data group can be different from that of the next extracted data group. To facilitate reading and using, it is best to sort the query results. In SQL, the ORDER BY clause is used to specify whether the query results are sorted in ascending ORDER (using the keyword ASC) or descending ORDER (using the keyword DESC. The basic syntax format of the order by clause is:

SELECT <select_list>

FROM <table_reference>

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

<Order_by_expression> indicates the column name or alias and expression of the Column Used for sorting, or specifies a negative integer representing the position of the name, alias, or expression in the selection list. If you want to sort BY multiple columns, the sorting priority is determined based on the ORDER of each column after order by. ASC specifies that the query results are sorted in ascending ORDER, DESC specifies that the query results are sorted in descending order.

The order by clause is an optional clause. When using the order by clause, you only need to specify one or more columns and the Optional keywords 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, return the descending order of "warehouse picking date" in the form of "warehouse picking ticket number", "customer number", "inventory number", and "warehouse picking date.

SELECT warehouse picking ticket number, customer number, inventory number, and warehouse picking date

FROM warehouse picking ticket information

Order by warehouse date DESC

The column name after the order by clause is usually the column name in the SELECT list. Another simple method is to replace the column name with numbers, the value of this number is the position of the specified sorting column in the SELECT list. The value is changed to 1 and 1 is added from left to right. For example, in the preceding query statement, change the column name "warehouse date" after the order by clause to the number "4 ", because the "date of delivery" is "4" in the SELECT list ".

After the statement is executed, the query result is exactly the same as that of the preceding statement.

For example, from the Database "Sales Management System, return "income details" in the form of "income details", "income date", "income amount", and "profit amount ", the query results are first sorted in descending order of "income amount", and then in ascending order of "profit amount.

SELECT revenue detail number, income date, income amount, profit amount

FROM revenue details

Order by revenue amount DESC, profit amount ASC

1.5 Use the group by clause

When you use the SELECT statement to query data, you can use the group by sub-statement if you want to classify data BY the value of a column and then query the data BY category. The group by clause is used to summarize the relevant data. Whether or not the WHERE clause is used, the group by clause can be included in the SELECT statement. The syntax format of the group by clause is as follows:

SELECT <select_list>

FROM <table_reference>

Group by <grouping_specificition>

<Grouping_specificition> can be divided into the following formats:

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

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

The CUBE in the preceding statement indicates that in addition to returning the columns specified BY the group by clause, the rows by group are also returned. The returned results are sorted by the first part column of the group, sorted by the second condition column, and so on. The statistical row includes the statistics of various combinations of columns specified BY the group by clause. The difference between ROLLUP and CUBE is that it only returns the statistical row of the column specified by the first grouping condition. Changing the column order will change the number of returned results rows.

For example, in the "sales information" table of the Database "dealers", the sales amount of each salesperson is queried by grouping by "sales personnel number" and sorted in descending order of the sales amount.

SELECT sales personnel number, SUM (amount) AS sales amount

FROM sales information

Group by sales personnel no.

Order by 2 DESC

You can still use the WHERE clause in the SELECT statement that includes the group by clause. For example, if in the previous example, you can add "sales amount" only when the column "amount" is greater than 100, you can use the following statements:

SELECT sales personnel number, SUM (amount) AS sales amount

FROM sales information

WHERE amount> 100

Group by sales personnel no.

Order by 2 DESC

In the preceding two examples, the group by clause specifies a column, but other columns can also be specified as needed. In this way, the clause can create a sub-GROUP for grouping data within the primary key range. For example, in the "sales details" table of the Database "jewelry sales system", query the "unit price" and "total profit" of the sales by grouping the "detail number" and "unit price ", in ascending order of the "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

In the group by clause syntax, We have briefly introduced the operators ROLLUP and CUBE. These two operators are similar in functionality. When they are added to the group by clause, they will all return additional abstract 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

After the preceding statement is executed, a row is added for each value in the "detail number" column in the query result. The three additional rows provide the total number of values corresponding to each value in the "detail number" column. The last added row indicates the total number of values in the "detail number" column.

The data returned by the CUBE operator is the same as that returned by the ROLLUP operator, but more information than that returned by the ROLLUP operator. In the following SELECT statement, the ROLLUP is replaced by the cube operator:

SELECT Detail number, unit price, SUM (profit) AS total profit

FROM sales details

Group by detail number, unit price WITH CUBE

It can be seen that the three additional rows are added to the query results, and each row corresponds to each different value in the "detail number" column. Unlike the ROLLUP operator, CUBE summarizes the values of each group.

1.6 use HAVING clause

HAVING clause is a search condition that specifies a group or aggregation. The HAVING clause is usually used with the group by clause. After querying and counting data results, you can use the HAVING clause to further filter the grouping results. If the group by clause is not used, HAVING clauses have the same functions as WHERE clauses. The HAVING clause is in the format of HAVING <search_condition> HAVING clause. You can reference the HAVING clause in the selection list and query the "unit price" and "total profit" of sales by grouping the "detail number" and "unit price ", it is required that only information with "total profit" greater than 150 be displayed in the query results. SELECT Detail number, unit price, SUM (profit) AS total profit FROM sales details Group by detail number, unit price Having sum (profit) & gt; 150If a HAVING clause contains multiple conditions, you can combine the keyword AND, or not into a HAVING clause's search condition. For example, in the previous example, the returned result must be "unit price" greater than 300 or "total profit" greater than 150. SELECT Detail number, unit price, SUM (profit) AS total profit FROM sales details Group by detail number, unit price HAVING unit price> 300 or sum (profit)> 150
The database query operation is pending ......