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

This article mainly introduces what are the common functions of mysql. It is very detailed and has a certain reference value. Friends who are interested must finish it!

First, the basic part 1, use MySQL1.1, SELECT statement 1.2, sort and retrieve data 1.3, filter data 1.4, data filter 1.5, filter 1.6with wildcard characters, Search with regular expressions 1.6.1 basic character matching 1.6.2 do OR matching 1.6.3 match one of several characters 1.6.4 match range 1.6.5 match Special character 1.6.6 match character Class 1.6.7 match multiple instances 1.6.8 Locator 1.7, create calculated Field 1, use MySQL

Connecting to MySQL will not be opened by the database, so the first thing to do is to open a database:

USE user

USE is followed by the database you want to open. If you don't know the name of the database, use SHOW DATABASES to view it. You can use SHOW TABLES to view the table in a database. Of course, you can also view the column SHOW user_id FROM user in the table, which returns a row for each field. The row contains field name, data type, whether NULL is allowed, key information, default value and other information (DESCRIBE user is a shortcut to the above statement)

1.1.The SELECT statement

In order to retrieve table data using SELECT, you must give at least two pieces of information-- what you want to choose and where to choose.

The required column name is given after the SELECT keyword, which indicates the table name from which the data is retrieved.

To retrieve multiple columns from a table, use the same SELECT statement. The only difference is that multiple column names must be given after the SELECT keyword, and column names must be separated by commas.

Using the DISTINCT keyword, you can retrieve only rows with different values, and duplicate ones will not be displayed again (note: the DISTINCT keyword applies to all columns, not just the columns that precede it).

The SELECT statement returns all matching rows, which may be each row in the specified table. To return the first row or the first few rows, use the LIMIT clause.

LIMIT 5,5 instructs MySQL to return five lines starting at line 5. The first number is the start position, and the second number is the number of rows to retrieve.

1.2. Sort and retrieve data

To explicitly sort the data retrieved with the SELECT statement, you can use the ORDER BY clause. The ORDER BY clause takes the name of one or more columns and sorts the output accordingly.

To sort by multiple columns, simply specify column names, separated by commas.

Data sorting is not limited to ascending sorting (from A to Z). This is just the default sort order, and you can also use the ORDER BY clause to sort in descending (Z to A) order. In order to sort descending, the DESC keyword must be specified. (the DESC keyword applies only to column names that precede them directly)

1.3. Filter data

This chapter will teach you how to specify search criteria using the WHERE clause of the SELECT statement. In the SELECT statement, the data is filtered based on the search criteria specified in the WHERE clause. The WHERE clause is given after the table name (FROM clause). When using both the ORDER BY and WHERE clauses, ORDER BY should be placed after WHERE. The WHERE clause operator is shown in the following figure:

1.4. Data filtering

This chapter teaches you how to combine WHERE clauses to create more powerful and advanced search conditions. We will also learn how to use the NOT and IN operators.

The AND operator calculates in higher order than the OR operator

1.5. Filter with wildcards

This chapter describes what wildcards are, how to use wildcards, and how to use the LIKE operator for wildcard search for complex filtering of data.

To use wildcards in the search clause, you must use the LIKE operator. LIKE instructs MySQL that the followed search patterns are compared using wildcard matching rather than direct equality matching.

The most commonly used wildcard character is the percent sign (%). In the search string,% means that any character appears any number of times. Depending on how MySQL is configured, searches can be case-sensitive. If case sensitive).

Another useful wildcard is the underscore (_). The purpose of the underscore is the same as the%, but the underscore matches only a single character rather than multiple characters.

As you can see, MySQL wildcards are useful. But this feature comes at a price: wildcard searches generally take longer to process than other searches discussed earlier. Here are some tips to remember when using wildcards.

1. Don't overuse wildcards. If other operators can achieve the same purpose, other operators should be used.

two。 When you really need to use wildcards, don't use them at the beginning of the search pattern unless absolutely necessary. Putting wildcards at the beginning of the search pattern is the slowest to search.

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

1.6. Search with regular expressions 1.6.1 basic character matching

Let's start with a very simple example. The following statement retrieves all rows of column prod_name that contain text 1000:

SELECT prod_name

FROM products

WHERE prod_name REGEXP '1000'

ORDER BY prod_name

It tells MySQL:REGEXP what follows is treated as a regular expression (a regular expression that matches the text body 1000).

SELECT prod_name

FROM products

WHERE prod_name REGEXP '.000'

ORDER BY prod_name

. Is a special character in the regular expression language. It means that any character is matched, so both 1000 and 2000 match and return.

Regular expression matching in MySQL (since version 3.23.4) is case-insensitive (that is, both uppercase and lowercase matches). To be case-sensitive, use BINARY keywords, such as WHERE prod_name REGEXP BINARY 'JetPack. 000'.

1.6.2 OR matching

To search for one of the two strings (either this string or the other string), use |, as follows:

SELECT prod_name

FROM products

WHERE prod_name REGEXP '1000 | 2000'

ORDER BY prod_name

1.6.3 matches one of several characters

What if you only want to match specific characters? This can be done by specifying a set of characters enclosed in [and], as follows:

SELECT prod_name

FROM products

WHERE prod_name REGEXP'[123] Ton'

ORDER BY prod_name

Character sets can also be negated, that is, they will match anything except the specified character. To negate a character set, place a ^ at the beginning of the collection. So although [123] matches characters 1, 2, or 3, [^ 123] matches anything except those characters.

1.6.4 matching range

SELECT prod_name

FROM products

WHERE prod_name REGEXP'[1-5] Ton'

ORDER BY prod_name

1.6.5 match special characters

Regular expression languages are made up of special characters with specific meanings. We have seen. , [], | and -, etc., as well as other characters. Excuse me, what should you do if you need to match these characters? For example, if you want to find out that contains. The value of the character, how to search? In order to match special characters, you must use\\ as the leading. \\-means to find -,\. It means to find. .

SELECT vend_nameFROM vendorsWHERE vend_name REGEXP'\. ORDER BY vend_name

All characters with special meaning within a regular expression must be escaped in this way. This includes. , |, [], and other special characters used so far.

1.6.6 matching character class

There are matches to find your own frequently used numbers, all alphabetic characters, or all numeric alphanumeric characters. To make it easier to work, you can use predefined character sets called character classes (characterclass).

1.6.7 match multiple instances

All regular expressions used so far have tried to match a single occurrence. If there is a match, the row is retrieved, and if it does not exist, no rows can be retrieved. But sometimes there needs to be stronger control over the number of matches. For example, you may need to look for all the numbers, no matter how many numbers are contained in the number, or you may want to find a word and be able to adapt to a trailing s (if present), and so on.

SELECT prod_nameFROM productsWHERE prod_name REGEXP'\ ([0-9] sticks?\) 'ORDER BY prod_name; regular expression\ ([0-9] sticks?\) needs to be explained. \\ (match (, [0-9] matches any number (1 and 5 in this case), sticks? Match stick and sticks (after s? Make s optional, because? Match 0 or 1 occurrence of any character before it),\) match). No, it will be very difficult to match stick and sticks.

Here is another example. This time we are going to match the four digits connected together:

SELECT prod_nameFROM productsWHERE prod_name REGEXP'[[: digit:]] {4} 'ORDER BY prod_name;1.6.8 locator

All examples so far match text anywhere in a string. To match the text at a specific location, you need to use the locators listed in the following table.

For example, what if you want to find all products that start with a number (including those starting with a decimal point)? Simply search for [0-9\.] (or [[: digit:]\. ]) No, because it will look for a match anywhere within the text. The workaround is to use the ^ locator, as follows:

SELECT prod_nameFROM productsWHERE prod_name REGEXP'^ [0-9\\.] 'ORDER BY prod_name;1.7, create calculated field

None of the data stored in the table is required by the application. We need to retrieve converted, calculated, or formatted data directly from the database; instead of retrieving the data and then reformatting it in the client application or report program. This is where the calculated field works. Unlike the columns described in the previous chapters, calculated fields do not actually exist in the database table. Calculated fields are created at run time within the SELECT statement.

In the SELECT statement of MySQL, you can use the Concat () function to concatenate two columns.

SELECT Concat (vend_name,'(', vend_country,')) FROM vendorsORDER BY vend_name

Clean up the data by deleting the extra spaces on the right side of the data, which can be done using the RTrim () function of MySQL, as shown below:

SELECT Concat (RTrim (vend_name),'(', RTrim (vend_country),') FROM vendorsORDER BY vend_name

An alias (alias) is an alternative to a field or value. Aliases are given with the AS keyword. Take a look at the following SELECT statement:

SELECT Concat (RTrim (vend_name),'(', RTrim (vend_country),') ASvend_titleFROM vendorsORDER BY vend_name First, the basic part 1, use MySQL1.1, SELECT statement 1.2, sort and retrieve data 1.3, filter data 1.4, data filter 1.5, filter 1.6with wildcard characters, Search with regular expressions 1.6.1 basic character matching 1.6.2 do OR matching 1.6.3 match one of several characters 1.6.4 match range 1.6.5 match Special character 1.6.6 match character Class 1.6.7 match multiple instances 1.6.8 Locator 1.7, create calculated Field 1, use MySQL

Connecting to MySQL will not be opened by the database, so the first thing to do is to open a database:

USE user

USE is followed by the database you want to open. If you don't know the name of the database, use SHOW DATABASES to view it. You can use SHOW TABLES to view the table in a database. Of course, you can also view the column SHOW user_id FROM user in the table, which returns a row for each field. The row contains field name, data type, whether NULL is allowed, key information, default value and other information (DESCRIBE user is a shortcut to the above statement)

1.1.The SELECT statement

In order to retrieve table data using SELECT, you must give at least two pieces of information-- what you want to choose and where to choose.

The required column name is given after the SELECT keyword, which indicates the table name from which the data is retrieved.

To retrieve multiple columns from a table, use the same SELECT statement. The only difference is that multiple column names must be given after the SELECT keyword, and column names must be separated by commas.

Using the DISTINCT keyword, you can retrieve only rows with different values, and duplicate ones will not be displayed again (note: the DISTINCT keyword applies to all columns, not just the columns that precede it).

The SELECT statement returns all matching rows, which may be each row in the specified table. To return the first row or the first few rows, use the LIMIT clause.

LIMIT 5,5 instructs MySQL to return five lines starting at line 5. The first number is the start position, and the second number is the number of rows to retrieve.

1.2. Sort and retrieve data

To explicitly sort the data retrieved with the SELECT statement, you can use the ORDER BY clause. The ORDER BY clause takes the name of one or more columns and sorts the output accordingly.

To sort by multiple columns, simply specify column names, separated by commas.

Data sorting is not limited to ascending sorting (from A to Z). This is just the default sort order, and you can also use the ORDER BY clause to sort in descending (Z to A) order. In order to sort descending, the DESC keyword must be specified. (the DESC keyword applies only to column names that precede them directly)

1.3. Filter data

This chapter will teach you how to specify search criteria using the WHERE clause of the SELECT statement. In the SELECT statement, the data is filtered based on the search criteria specified in the WHERE clause. The WHERE clause is given after the table name (FROM clause). When using both the ORDER BY and WHERE clauses, ORDER BY should be placed after WHERE. The WHERE clause operator is shown in the following figure:

1.4. Data filtering

This chapter teaches you how to combine WHERE clauses to create more powerful and advanced search conditions. We will also learn how to use the NOT and IN operators.

The AND operator calculates in higher order than the OR operator

1.5. Filter with wildcards

This chapter describes what wildcards are, how to use wildcards, and how to use the LIKE operator for wildcard search for complex filtering of data.

To use wildcards in the search clause, you must use the LIKE operator. LIKE instructs MySQL that the followed search patterns are compared using wildcard matching rather than direct equality matching.

The most commonly used wildcard character is the percent sign (%). In the search string,% means that any character appears any number of times. Depending on how MySQL is configured, searches can be case-sensitive. If case sensitive).

Another useful wildcard is the underscore (_). The purpose of the underscore is the same as the%, but the underscore matches only a single character rather than multiple characters.

As you can see, MySQL wildcards are useful. But this feature comes at a price: wildcard searches generally take longer to process than other searches discussed earlier. Here are some tips to remember when using wildcards.

1. Don't overuse wildcards. If other operators can achieve the same purpose, other operators should be used.

two。 When you really need to use wildcards, don't use them at the beginning of the search pattern unless absolutely necessary. Putting wildcards at the beginning of the search pattern is the slowest to search.

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

1.6. Search with regular expressions 1.6.1 basic character matching

Let's start with a very simple example. The following statement retrieves all rows of column prod_name that contain text 1000:

SELECT prod_name

FROM products

WHERE prod_name REGEXP '1000'

ORDER BY prod_name

It tells MySQL:REGEXP what follows is treated as a regular expression (a regular expression that matches the text body 1000).

SELECT prod_name

FROM products

WHERE prod_name REGEXP '.000'

ORDER BY prod_name

. Is a special character in the regular expression language. It means that any character is matched, so both 1000 and 2000 match and return.

Regular expression matching in MySQL (since version 3.23.4) is case-insensitive (that is, both uppercase and lowercase matches). To be case-sensitive, use BINARY keywords, such as WHERE prod_name REGEXP BINARY 'JetPack. 000'.

1.6.2 OR matching

To search for one of the two strings (either this string or the other string), use |, as follows:

SELECT prod_name

FROM products

WHERE prod_name REGEXP '1000 | 2000'

ORDER BY prod_name

1.6.3 matches one of several characters

What if you only want to match specific characters? This can be done by specifying a set of characters enclosed in [and], as follows:

SELECT prod_name

FROM products

WHERE prod_name REGEXP'[123] Ton'

ORDER BY prod_name

Character sets can also be negated, that is, they will match anything except the specified character. To negate a character set, place a ^ at the beginning of the collection. So although [123] matches characters 1, 2, or 3, [^ 123] matches anything except those characters.

1.6.4 matching range

SELECT prod_name

FROM products

WHERE prod_name REGEXP'[1-5] Ton'

ORDER BY prod_name

1.6.5 match special characters

Regular expression languages are made up of special characters with specific meanings. We have seen. , [], | and -, etc., as well as other characters. Excuse me, what should you do if you need to match these characters? For example, if you want to find out that contains. The value of the character, how to search? In order to match special characters, you must use\\ as the leading. \\-means to find -,\. It means to find. .

SELECT vend_nameFROM vendorsWHERE vend_name REGEXP'\. ORDER BY vend_name

All characters with special meaning within a regular expression must be escaped in this way. This includes. , |, [], and other special characters used so far.

1.6.6 matching character class

There are matches to find your own frequently used numbers, all alphabetic characters, or all numeric alphanumeric characters. To make it easier to work, you can use predefined character sets called character classes (characterclass).

1.6.7 match multiple instances

All regular expressions used so far have tried to match a single occurrence. If there is a match, the row is retrieved, and if it does not exist, no rows can be retrieved. But sometimes there needs to be stronger control over the number of matches. For example, you may need to look for all the numbers, no matter how many numbers are contained in the number, or you may want to find a word and be able to adapt to a trailing s (if present), and so on.

SELECT prod_nameFROM productsWHERE prod_name REGEXP'\ ([0-9] sticks?\) 'ORDER BY prod_name; regular expression\ ([0-9] sticks?\) needs to be explained. \\ (match (, [0-9] matches any number (1 and 5 in this case), sticks? Match stick and sticks (after s? Make s optional, because? Match 0 or 1 occurrence of any character before it),\) match). No, it will be very difficult to match stick and sticks.

Here is another example. This time we are going to match the four digits connected together:

SELECT prod_nameFROM productsWHERE prod_name REGEXP'[[: digit:]] {4} 'ORDER BY prod_name;1.6.8 locator

All examples so far match text anywhere in a string. To match the text at a specific location, you need to use the locators listed in the following table.

For example, what if you want to find all products that start with a number (including those starting with a decimal point)? Simply search for [0-9\.] (or [[: digit:]\. ]) No, because it will look for a match anywhere within the text. The workaround is to use the ^ locator, as follows:

SELECT prod_nameFROM productsWHERE prod_name REGEXP'^ [0-9\\.] 'ORDER BY prod_name;1.7, create calculated field

None of the data stored in the table is required by the application. We need to retrieve converted, calculated, or formatted data directly from the database; instead of retrieving the data and then reformatting it in the client application or report program. This is where the calculated field works. Unlike the columns described in the previous chapters, calculated fields do not actually exist in the database table. Calculated fields are created at run time within the SELECT statement.

In the SELECT statement of MySQL, you can use the Concat () function to concatenate two columns.

SELECT Concat (vend_name,'(', vend_country,')) FROM vendorsORDER BY vend_name

Clean up the data by deleting the extra spaces on the right side of the data, which can be done using the RTrim () function of MySQL, as shown below:

SELECT Concat (RTrim (vend_name),'(', RTrim (vend_country),') FROM vendorsORDER BY vend_name

An alias (alias) is an alternative to a field or value. Aliases are given with the AS keyword. Take a look at the following SELECT statement:

SELECT Concat (RTrim (vend_name),'(', RTrim (vend_country),') ASvend_titleFROM vendorsORDER BY vend_name; is all the contents of this article entitled "what are the Common functions of mysql?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.