MySQL Getting Started (ix)--Querying data

Querying data is getting the data you need from the database.

1. Basic Query Statement

That is, the SELECT statement

Where the list of attributes represents the field name to query, the table name and view list indicates the query data from the table or view specified here, there can be more than one; the condition expression 1 makes the condition of the query; the property name 1 refers to the data in the field, and the conditional expression 2 means that the data that satisfies the expression can be output Property Name 2 refers to sorting by the data in that field, in ascending ASC, which is ordered from small to large.
The Group by clause is typically used with aggregate functions such as count (), Sum ().

2. Single-table query

A single-table query, by definition, queries the required data from a single table.

2.1 Querying all fields

Can be divided into the following two ways
(1) List all fields of the table
That is, all the fields in the table you want to query are listed in the list of properties for the SELECT statement.
(2) Query all fields using *
Even if the * symbol is used instead of the attribute list in the SELECT statement.
Obviously this method is simple and convenient. However, this method can only be arranged in the order of the fields in the table, and the order of the fields cannot be changed.

2.2 Querying a specified field

As the name implies, the specified field of the query is listed in the attribute list.

2.3 Querying a specified record

That is, set the query condition statement WHERE clause. The common query conditions in the WHERE clause include

(1) query with keyword in
The In keyword can determine whether a field's value is in the specified collection, and its syntax rules are

[NOT] In (element 1, Element 2,, Element N)

where element n represents the element in the collection, note that the character element needs to be enclosed in single quotation marks.
(2) Range query with between and
The between and keyword can be used to interpret whether a field's value is within a specified range, and the syntax rules are as follows

[NOT] Between value 1 and take value 2

That is, the selection satisfies a range greater than or equal to 1, and two is less than or equal to the value 2.
(3) Character matching query with like
The LIKE keyword can match strings for equality, and the syntax rules are as follows

[NOT] Like ' string '

The value of a string parameter can be either a full string or a wildcard character that contains a % or _ . Where% can represent any length of string, the length can be zero;_ can only represent a single character.
Note that strings that need to be matched need to be quoted, and can be single quotes or double quotes. If you want to match Chinese, note that a Chinese character is two characters, so use _ words should be used two, such as matching Jiang, the matching string should be "Jiang _ _".
(4) Query null value
The IS NULL keyword can be used to determine whether a field's value is a null value, and the syntax rule is

is [NOT] NULL

(5) Multi-criteria query with and
The and keyword can be used to federate multiple criteria for querying, and the syntax rules are

Conditional expression 1 and conditional expression 2 [... An and conditional expression N]

(6) Multi-criteria query with OR
The OR keyword can be used to federate multiple conditions for querying, and the syntax rules are

Conditional expression 1 OR conditional expression 2 [... An OR conditional expression n]

2.4 Query results are not duplicated

If there are no uniqueness constraints on some of the fields in the table, there may be duplicate values for those fields. The SELECT statement can use the DISTINCT keyword to eliminate duplicate records, and the syntax rules are

Select DISTINCT property name

2.5 Sorting the query results

Even if the order by child statement in the SELECT statement is used.
Note that if a field with a record has a null value, in the case of the ASC parameter, the record will be displayed as the first record, while Desc is displayed as the last.

2.6 Group Queries

Even if the query results are grouped by a field or multiple fields with the group by keyword, the values in the field are equal to one group. specifically for

Group by property name [having conditional expression] [with ROLLUP]

Where the WITH rollup keyword will add a record at the end of all records, which is the sum of all the records above, note that for numeric class types, this sum is added, and for string types, it is a collection, and much more remains to be experimented with.

2.7 Limiting the number of query results by using limit

Limit when a special keyword in MySQL can be used to specify which record the query results from, or to specify how many records to display altogether.
(1) Do not specify the initial position
The record is displayed at the beginning of the first record, the number of bars for the display record is specified by the limit, and the syntax is

Limit shows the number of records

This clause is used after the from table name and view list for the SELECT statement.
(2) Specify the initial position
This is where the display starts and you can specify how many records to display, with the syntax

Limit initial position, number of records

Note in the MySQL system, the position of the first record is 0.

3. Query using aggregate function

The aggregate functions include count (), sum (), AVG (), Max (), and Min (), respectively, to count the number of records, the sum of the values of the calculated field, the average of the values of the calculated field, the maximum value of the query field, and the minimum value of the query field.
These aggregate functions, in addition to being used in conjunction with group by, can also be used directly in the list of attributes after select as properties.
Note that the latter two apply not only to numeric types, but also to character types, that is, the letter A is the smallest, the letter Z is the largest and is compared by ASCII code.

4. Connection Query

A connection query is a condition in which you connect multiple tables to select the data you want.
In two tables, for example, when a field representing the same meaning exists in two tables, the field can be used to connect the two tables, and when the value of the field is equal, the record is queried.
Connection queries include inner JOIN queries and outer join queries.

4.1 Internal Connection Query

The most common way to connect queries.
For example, suppose that the table employee and the D_ID field in table department represent the department number, so that you can use this field to make an internal connection query for two tables. Suppose we look up the fields of NUM, name, d_id, age, and sex from table employee and query the fields of D_name and function from table department, the SELECT statement is as follows

4.2 External Connection Query

Compared to the internal connection query, the outer join query can not only query the value of the field when the record is equal, you can also query the field value is not equal when the record, specifically divided into the left connection query and the right connection query, the basic syntax for

Where the parameter on is followed by the join condition.
(1) When you make a left connection query, you can query out all the records in the table referred to in table name 1, and table name 2 refers to a table that can only query for matching records.
(2) When making a left connection query, the reverse.
The maximum or intra-connection query used in a connection query.

5, sub-query

A subquery is a query that is nested in another query statement that implements a query between multiple tables. The query result of the inner query statement, which provides the query condition for the outer query statement.
A subquery may include keywords such as in, not in, no, all, exists, and not exists, and may also contain comparison operators.

5.1 Sub-query with in keyword

In the above example, the process of querying is to first determine all the d_id in the table department, and then display the same records as D_ID and previous d_id in the table employee.

5.2 Sub-query with comparison operator

The comparison operators <> and! = are equivalent.

In the previous example, the query process was to entertain the d_id of an employee aged 24 from table employee, and then display d_id and d_name information for Depatment employees who are not 24 in the table.

5.3 Sub-query with exists keyword

When using the EXISTS keyword, the inner query statement does not return a record of the query, but instead returns a true or False value. If the inner query statement queries the result that satisfies the condition, returns a true, the outer query statement will query, if the inner layer is not found to return a false, the outer query statement will not query or query no record.
The EXISTS keyword can also be used with other query criteria, and the conditional expression is connected with and or or with the EXISTS keyword.

5.4 Sub-query with any keyword

The Any keyword means that any of these conditions, that is, when using the Any keyword, can be used to execute an outer query statement as long as either of the results returned by the Inner query statement is satisfied.
The Any keyword is typically used with comparison operators, such as >any, which represents more than one value.

5.5 subqueries with the ALL keyword

The all keyword is for all conditions, that is, when the ALL keyword is used, the outer query statement can be executed only if all the results returned by the Inner query statement are satisfied.
Similarly, the All keyword is typically used with comparison operators, such as >all, which represents greater than all values.

6. Merge Query Results

The result of the merge query is to merge the query results of multiple SELECT statements into one display.
The Union and UNION ALL keywords can be used for a merge operation, and when union is used, the database system merges all the query results together and then removes the same records, similar to the collection, and when you use union all, simply merge all the query results. Does not exclude the same record.

7. Alias tables and fields

At query time, you can take an alias for the table and the field, which can replace the table and field it specifies.

7.1 Aliases for tables

The basic form is

Alias of table Name table

In this way, the table alias can be used instead of the table name in this query.

7.2 To alias a field

The basic form is

property name [as] Alias

Where the property name is the original name of the field, the AS keyword is optional, the effect of the implementation is the same.
Note that the alias of the field is only used to replace the name of the field when it is displayed, and the alias of the definition cannot be used in the query condition, that is, the alias of the field cannot be used in the conditional expression.

8. Query using regular expressions

Regular expressions are a way to match a class of strings with a pattern that is much more powerful and flexible than the previously mentioned wildcard character query capability.
MySQL uses the RegExp keyword to match the query regular expression in the basic form of

Property name Regexp ' Match method '

Here's how regular expressions are matched. Common pattern characters

Let's take a few examples below
(1) query for records beginning with the string ' LLL ' from the Name field in the info table

Select * from info where name regexp ' ^lll ';

(2) query for records ending with the letter ' a ' from the Name field in the info table

Select * from info where name regexp ' a$ ';

(3) From the Name field of the info table, the query begins with the letter ' L ', ends with the letter ' Y ', and has a record of two arbitrary characters in the middle.

Select * from info where name RegExp ' ^l. Y$ ';

(4) query for records containing numbers or letters A, B, and C from the name field in the info table

Select * from info where name regexp ' [0-9a-c] ';

If you need to match the letters, A, B, and C, then [ABC] does not need a symbol between each character; If you want to match all the letters, you can apply [a-za-z], that is, between A and Z, separated by a symbol between Z and a.
(5) query for records without letters A to W and numbers from the Name field in the info table

Select * from info where name regexp ' [^a-w0-9] ';

(6) From the Name field of the Info table, query for records containing any of the three strings of IC, UC, and AB

Select * from info where name regexp ' Ic|uc|ab ';

Note that there can be no spaces between each string and, otherwise, spaces will be treated as one character during the query.
(7) Query the Name field of the info table for records where the letter ' a ' appears between the letters ' C '

Select * from info where name regexp ' A+c ';

If you use * instead of the above +, you cannot target the result because it can represent 0.
(8) Query from the Name field of the info table for a record that has ' ab ' at least three times

Select * from info where name regexp ' ab{1,3} ';

MySQL Getting Started (ix)--Querying data