Shulou(Shulou.com)06/03 Report--

This article mainly explains "the way MySQL operates the datasheet". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the way MySQL operates data tables".

Preface

A series of operations such as data display and modification that we see on the page are actually query modifications to the data table. So to learn the basic programming, we must master the operation of the data table!

Query (select)

The basic format is as follows:

SELECT

{* |}

[

FROM,...

[WHERE

[GROUP BY

[HAVING [{} …]]

[ORDER BY]

[LIMIT [,]]

]

The meanings of nouns are as follows:

{* |} contains a list of fields with asterisk wildcards, indicating the name of the field to be queried.

,... Tables 1 and 2 represent the sources of query data, which can be single or multiple

WHERE is optional, and if selected, the query data must meet the query criteria.

GROUP BY

< 字段 >

That tells MySQL how to display the queried data and group it by the specified field

[ORDER BY

< 字段 >

This clause tells MySQL in what order the queried data is displayed, which can be sorted in ascending (ASC) and descending (DESC) order, which is ascending by default.

[LIMIT [,]], this clause tells MySQL to display the number of pieces of data that are queried each time.

Query all fields in the table

SELECT uses "*" to find data for all fields in the table, or to list all field names, in the following syntax format:

SELECT * FROM table name

Note: when using the "*" query, you can only arrange the fields in the order of the data table, not change the order of the fields.

Query the fields specified in the table

The syntax format for a field in the query table is:

SELECT

< 列名 >

FROM

< 表名 >

Weight removal (DISTINCT)

The basic syntax format is as follows:

SELECT DISTINCT FROM

Field name is the name of the field for which duplicate records need to be eliminated, and multiple fields are separated by commas.

Note the following when using the DISTINCT keyword:

The DISTINCT keyword can only be used in SELECT statements.

When deduplicating one or more fields, the DISTINCT keyword must be at the top of all fields.

If there are multiple fields after the DISTINCT keyword, multiple fields will be deduplicated by combining them, that is, they will be deduplicated only if the multiple fields are exactly the same.

Specify an alias for the table

The basic syntax format is as follows:

[AS]

The meanings of each clause are as follows:

The name of the data table stored in the database

The new name of the table specified when querying

The AS keyword can be omitted, after which the table name and alias need to be separated by a space.

Note: the alias of the table cannot have the same name as other tables in the database. The alias of a field cannot have the same name as other fields in the table. Aliases for fields cannot be used in conditional expressions, or an error message such as "ERROR 1054 (42S22): Unknown column" will appear.

Specify an alias for the field

The basic syntax format is as follows:

[AS]

Among them, the grammatical meaning of each clause is as follows:

The name defined for the field in the data table

The new name of the field

The AS keyword can be omitted, after which you need to separate the field name from the alias with a space.

Limit the number of query results (limit)

LIMIT is a special keyword in MySQL that specifies which record the query results are displayed from and how many records are displayed. The LIMIT keyword can be used in three ways, namely, specifying the initial position, not specifying the initial position, and using it in combination with OFFSET.

Specify initial location

The basic syntax format is as follows:

LIMIT initial position, number of records SELECT * FROM table name LIMIT 3 5; / / start position 3, end 5

Do not specify the initial position

The basic syntax format is as follows:

Number of LIMIT records SELECT * FROM table name LIMIT 4; / 4 records

Combined use of LIMIT and OFFSET

The basic syntax format is as follows:

Number of LIMIT records OFFSET initial position SELECT * FROM tb_students_info LIMIT 5 OFFSET 3; / / returns the sort of query results for records with a number of rows of 5 starting from the fourth record (ORDER BY)

The syntax format is as follows:

ORDER BY [ASC | DESC]

The syntax is explained as follows.

Field name: indicates the name of the field that needs to be sorted. Multiple fields are separated by commas.

ASC | DESC:ASC: fields are sorted in ascending order; DESC: fields are sorted in descending order. Where ASC is the default value.

Note:

The ORDER BY keyword can be followed by a subquery (the subquery will be explained in more detail in later tutorials, which you can learn here).

When there is a null value in the sorted field, ORDER BY treats the null value as a minimum.

Single field sorting

SELECT * FROM table name ORDER BY field name

Multi-field sorting

SELECT field 1, field 2 FROM table name ORDER BY field 1, field 2

Note: when sorting multiple fields, the first field of the sort must have the same value before the second field is sorted. If all the values in the first field data are unique, MySQL will no longer sort the second field.

By default, query data is sorted in ascending alphabetical order (Ascending Z), but the sorting of the data is not limited to that. You can also use DESC in ORDER BY to sort query results in descending order.

Conditional query data (where)

The basic syntax format is as follows:

WHERE query condition

The query criteria can be:

Query conditions with comparison operators and logical operators

Query conditions with BETWEEN AND keyword

Query conditions with IS NULL keyword

Query conditions with IN keyword

Query conditions with LIKE keyword

Query statement with single condition

A single condition means that there is only one query condition after the WHERE keyword.

Multi-conditional query statement

There can be more than one query condition after the WHERE keyword, which makes the query results more accurate. Multiple query conditions are separated by logical operators AND (& &), OR (| |), or XOR.

AND: the record is queried only when all the query conditions are met.

OR: the record will be queried only if any of the query conditions are met.

XOR: the record is queried only when one of the conditions is met and the other condition is not met.

Fuzzy query (like)

The basic syntax format is as follows:

[NOT] LIKE 'string'

Where:

NOT: optional parameter that satisfies the condition when the content of the field does not match the specified string.

String: specifies the string to be used to match. "string" can be a complete string or contain wildcards.

The LIKE keyword supports percent sign "%" and underscore "_" wildcards.

Wildcards are special statements that are mainly used to obfuscate queries. When you don't know the real characters or don't bother to enter the full name, you can use wildcards instead of one or more real characters.

Query with "%" wildcard

"%" is the most commonly used wildcard character in MySQL. It can represent a string of any length, and the length of a string can be 0. For example, a% b represents a string of any length that begins with the letter an and ends with the letter b. This string can represent ab, acb, accb, accrb, and so on.

Query with "_" wildcard

_ "can only represent a single character, and the length of the character cannot be 0. For example, aqb can represent strings such as acb, adb, aub, and so on.

LIKE is case sensitive

By default, the LIKE keyword is case-insensitive when matching characters. If you need to be case sensitive, you can add the BINARY keyword.

Notes and techniques for using wildcards

Note:

Pay attention to the case. MySQL is case-insensitive by default. If you are case-sensitive, data like "Tom" cannot be matched by "t%".

Pay attention to trailing spaces, which will interfere with the matching of wildcards. For example, "T%" does not match "Tom".

Note NULL. The "%" wildcard can match any character, but not NULL. That is, "%" does not match a record with a value of NULL in the tb_students_info data table.

Use skills:

Don't overuse wildcards. If other operators can achieve the same goal, you should use other operators. Because MySQL generally takes longer to process wildcards than other operators.

After determining the use of wildcards, do not use them at the beginning of a string unless absolutely necessary. Putting wildcards at the beginning of the search pattern is the slowest to search.

Pay close attention to the position of wildcards. If misplaced, the desired data may not be returned.

Range query (BETWEEN AND)

The basic syntax format is as follows:

[NOT] BETWEEN value 1 AND value 2

Where:

NOT: an optional parameter that represents a value outside the specified range. If the field value does not meet the value within the specified range, these records are returned.

Value 1: represents the starting value of the range.

Value 2: represents the end value of the range.

The BETWEEN AND and NOT BETWEEN AND keywords are useful when querying records within a specified range. For example, inquire about the age of the student, date of birth, salary level of the employee, and so on.

Null value query (IS NULL)

MySQL provides the IS NULL keyword to determine whether the value of the field is null (NULL). A null value is different from a 0 or an empty string. If the value of the field is null, the query condition is met and the record is queried. If the value of the field is not null, the query condition is not met.

The basic syntax format is as follows:

IS [NOT] NULL

"NOT" is an optional parameter, indicating that the condition is met when the field value is not null.

Group query (GROUP BY)

The basic syntax format is as follows:

GROUP BY

Field name represents the name of the field that needs to be grouped, separated by commas when multiple fields are grouped.

GROUP BY is used alone

When the GROUP BY keyword is used alone, the query results show only the first record for each group.

GROUP BY and GROUP_CONCAT ()

The GROUP BY keyword can be used with the GROUP_CONCAT () function. The GROUP_CONCAT () function displays the field values for each group.

GROUP BY and aggregate function

The GROUP BY keyword is often used with aggregate functions in data statistics.

Aggregate functions include COUNT (), SUM (), AVG (), MAX (), and MIN (). Where COUNT () is used to count the number of records; SUM () is used to calculate the sum of field values; AVG () is used to calculate the average of field values; MAX () is used to query the maximum value of the field; and MIN () is used to query the minimum value of the field.

GROUP BY and WITH ROLLUP

The WITH POLLUP keyword is used to add a record to the end of all records, which is the sum of all the records above, that is, the number of records.

Filter packets (HAVING)

In MySQL, you can use the HAVING keyword to filter the grouped data.

The basic syntax format is as follows:

HAVING

Both the HAVING keyword and the WHERE keyword can be used to filter data, and HAVING supports all operators and syntax in the WHERE keyword.

However, there are also several differences between the WHERE and HAVING keywords:

In general, WHERE is used to filter rows of data, while HAVING is used to filter packets.

Aggregate functions cannot be used in WHERE query conditions, while aggregate functions can be used in HAVING query conditions.

WHERE filters before the data packet, while HAVING filters after the data packet.

WHERE filters database files, while HAVING filters query results. That is, WHERE filters directly based on the fields in the data table, while HAVING filters based on the fields that have been queried earlier.

Field aliases cannot be used in WHERE query conditions, while field aliases can be used in HAVING query conditions.

Regular expression (REGEXP)

Regular expressions are mainly used to query and replace text content that conforms to a certain pattern (rule). For example, regular expressions can be used to extract phone numbers from a file, find repetitive words in an article, replace sensitive words in an article, and so on. Regular expressions are powerful and flexible and are often used in very complex queries.

The basic syntax format is as follows:

Attribute name REGEXP 'match method'

Where "attribute name" represents the name of the field that needs to be queried, and "match method" indicates how to match the query. There are many pattern matching characters in "matching method", which have different meanings. The following table lists the matching methods commonly used in the REGEXP operator.

The option shows that the example match value example ^ matches the start character of the text'^ b 'matches the strings that begin with the letter b book, big, banana, bike$ matches the end character of the text' st$' matches the strings test, resist, persist that ends in st. Match any single character 'b.t' match any character between b and t there is one character bit, bat, but, bite* match zero or more characters in front of it' fancin' match character n is preceded by any character ffn, fan, faan, abcn+ match 1 or more characters before 'ba+' match begins with b Followed by at least one aba, bay, bare, battle match text containing the specified character 'fa' match text containing' fa' [character set] matches any character'[xz]'in the character set matching x or zdizzy, zebra, x-ray, extra [^] matches any character'[^ abc] 'that is not in parentheses, matches any string that does not contain a, b or c desk, fox, F8ke string {n } match previous string at least n times'b {2} 'match 2 or more bbbb, bbbb, bbbbbbb strings

{n bbbb m} matches the previous string at least n times, m times at most'b {2pm 4} 'matches a minimum of 2, and a maximum of 4 bbbb and bbbb

Query records that begin with a specific character or string

The character ^ is used to match records that begin with a specific character or string.

Query records that end with a specific character or string

The character $is used to match records that end with a specific character or string.

Replace any character in a string

Characters. Used to replace any character in a string.

Match multiple characters

Both the characters * and + can match multiple characters before the symbol. The difference is that + represents at least one character, while * can represent 0 characters.

Matches the specified string

Regular expressions can match strings. When a record in the table contains this string, the record can be queried. When you specify multiple strings, you need to use | to separate them. Just match any of these strings.

Matches any of the specified strings

Use square brackets [] to form a collection of characters that need to be queried. As long as the record contains any character in square brackets, the record will be queried. For example, you can query records that contain any of the three letters a, b, and c through "[abc]".

Match characters other than the specified character

[^ character set] is used to match any characters that are not in the specified collection.

Specify the number of consecutive occurrences of a string using {n,} or {nrecom}

The string {n,} indicates that the string appears n times in a row; the string {n ·m} indicates that the string appears at least n times in a row and m times at the most.

For example, a {2,} means that the letter an appears at least 2 times in a row, or more than 2 times, and a {2pj4} means that the letter an appears at least 2 times in a row and no more than 4 times in a row.

Insert data / add data (INSERT)

There are two grammatical forms of INSERT statements, which are INSERT. VALUES statement and INSERT... SET statement.

INSERT... VALUES statement INSERT INTO [,... ]]

VALUES (value 1) [… , (value n)]

The syntax is explained as follows.

Specifies the name of the table being manipulated

Specifies the name of the column in which the data needs to be inserted If you insert data into all the columns in the table, all column names can be omitted and directly use INSERTVALUES (...) That's it.

VALUES or VALUE clause: this clause contains the list of data to insert. The order of the data in the data list corresponds to the order of the columns.

INSERT... SET statement INSERT INTO

SET =

=

...

This statement is used to directly assign the corresponding column value to some columns in the table, that is, the column name of the data to be inserted is specified in the SET clause, col_name is the specified column name, the equal sign is followed by the specified data, and for unspecified columns, the column value is specified as the default value for that column.

You can see from the two forms of the INSERT statement:

Use INSERT... The VALUES statement can insert a row of data into a table or multiple rows of data

Use INSERT... The SET statement can specify the value of each column in the inserted row, or you can specify the value of some columns

INSERT... The SELECT statement inserts data from other tables into the table.

Adopt INSERT … The SET statement can insert values of some columns into the table, which is more flexible.

INSERT... VALUES statements can insert more than one piece of data at a time.

In MySQL, it is faster to handle multiple inserts with a single INSERT statement than with multiple INSERT statements.

When inserting multiple rows of data using a single INSERT statement, you only need to enclose each row in parentheses.

Modify data / update data (UPDATE)

Use the UPDATE statement to modify a single table. The basic syntax format is as follows:

UPDATE SET field 1 = value 1 [, field 2 = value 2... ] [WHERE clause]

[ORDER BY clause] [LIMIT clause]

The syntax is as follows:

Used to specify the name of the table to update

SET clause: used to specify the column name to be modified in the table and its column value. Where each specified column value can be an expression or a default value for that column. If the default value is specified, the DEFAULT keyword is available to indicate the column value.

WHERE clause: optional. Used to limit the rows in the table to be modified. If not specified, all rows in the table are modified.

ORDER BY clause: optional. Used to limit the order in which rows in the table are modified.

LIMIT clause: optional. Used to limit the number of rows to be modified.

Note: when you modify multiple column values for a row of data, each value of the SET clause can be separated by a comma.

Delete data (DELETE)

The basic syntax format is as follows:

DELETE FROM [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

Specifies the name of the table to delete the data

ORDER BY clause: optional. When you delete, the rows in the table are deleted in the order specified in the clause.

WHERE clause: optional. Indicates that the deletion condition is qualified for the delete operation, and if the clause is omitted, all rows in the table are deleted.

LIMIT clause: optional. Used to tell the server the maximum value of the deleted row before the control command is returned to the client.

Note: when the WHERE condition is not used, all data will be deleted.

At this point, I believe you have a deeper understanding of "the way MySQL operates the datasheet". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.