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

The following mainly brings you what are the main methods of MySQL database SQL query, I hope these words can bring you practical use, which is also the main purpose of this article when I edit MySQL database SQL query. All right, don't talk too much nonsense, let's just read the following.

1. Single table query 1. Query all fields

Use the asterisk "" wildcard to query all fields in the SELECT statement

Specify all fields in the SELECT statement

Select from TStudent

2. Query the specified fields

Query multiple fields

Select Sname,sex,email from TStudent

3. Query the specified record

In the SELECT statement, the data is filtered through the WHERE clause. The syntax format is:

SELECT field name 1, field name 2, … , field name n FROM table name WHERE query condition

Select Sname,sex,email,Class from TStudent where class='java'

4. Query with IN keyword

Query the records that meet the conditions within the specified range, use the IN operator to enclose all the retrieval conditions in parentheses, separate the retrieval conditions with commas, and as long as a value within the range of conditions is a match.

Search for new students surnamed Wang Liu Shi

Select * from TStudent where left (sname,1) in ('Wang', 'Liu', 'Shi')

5. Scope query with BETWEEN AND

Query values within a range, which requires two parameters, the start and end values of the range, and these records are returned if the field values meet the specified range query criteria.

For the following query conditions, query students with student numbers 100 to 150, including 100 and 150

Select from TStudent where convert (studentid,signed) between 100 and 150

Equivalent to

Select from TStudent where convert (studentid,signed) > = 100

And convert (studentid,signed) 20 and studentid80 order by m

6. Use WITH ROLLUP in the GROUP BY clause

More group aggregation information can be retrieved by using the WITH ROLLUP clause of GROUP BY, not only the aggregation information of each group, but also the aggregate information of the group as a whole.

Select class,subJectName,AVG (mark) from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID group by class,subJectName with rollup

Can count the average score of each class, the average score of each class can also be counted, and the average score of all classes can also be counted.

5. Subquery 1. Subquery with IN keyword

When the IN keyword runs a subquery, the inner query statement returns only one data column, and the values in the data column are provided to the outer query statement for comparison operation.

Select * from TStudent where studentid in (select distinct studentid from TScore where mark > 98)

2. Subquery with EXISTS keyword

The parameter after the EXISTS keyword is an arbitrary subquery, and the system operates on the subquery to determine whether the subquery returns rows. If at least one row is returned, the result of EXISTS is true, and the outer query statement will query; if the subquery does not return any rows, the result returned by EXISTS is false, and the outer statement will not query.

Select from TStudent where studentid='01001' and exists (select from TScore where studentid='01001')

3. Subquery with ANY and SOME keywords

The ANY and SOME keywords are synonyms that either of these conditions are met, allowing you to create an expression to compare the list of return values of a subquery, and to return a result as a condition for an outer query as long as any comparison condition in the inner subquery is met.

Select from TStudent where studentid=any (select distinct studentid from TScore where mark > 98)

Equivalent to

Select from TStudent where studentid=some (select distinct studentid from TScore where mark > 98)

Equivalent to

Select from TStudent where studentid in (select distinct studentid from TScore where mark > 98)

You can also use other comparison operators for subqueries, such as some (select distinct studentid from TScore where mark > 98)

The following SQL sentence query finds the studentid of students whose test scores are greater than 98. For example, there are three '00010' students with a score of 00021, while the external query will search for students with a student number smaller than 00061.

Select * from TStudent where studentid98)

4. Subquery with ALL keyword

Unlike ANY and SOME, the ALL keyword requires that all inner query conditions be met at the same time when using ALL.

The following SQL sentence query finds the studentid of students whose test scores are greater than 98. For example, there are three '00010' students with a score of 00021, while the external query will search for students with a student number smaller than 00010.

Select * from TStudent where studentid98)

The following SQL sentence query finds the studentid of students whose test scores are greater than 98. For example, there are three '00010' students with a score of 00021 minutes. The external query will query students with a student number larger than 00061.

Select * from TStudent where studentid > all (select distinct studentid from TScore where mark > 98)

Use regular expressions to query

The role of a regular expression is to match text and compare a pattern (regular expression) with a text string. MySQL provides initial support for regular expressions with the WHERE clause, allowing you to specify regular expressions to filter data retrieved by SELECT.

In a SQL query statement, what is followed by the query condition REGEXP is treated as a regular expression.

1. Query records that begin with a specific character or string

The character'^ 'matches text that begins with a specific character or string.

Select * from TStudent where sname regexp'^ Liu Ping'

2. Query records that end with a specific character or string

The character'$'matches text that ends with a specific character or string.

Select * from TStudent where cardid regexp'36 $'

3. Use symbols. To replace any character in the string

The character'.' Matches any character.

Select * from TStudent where sname regexp'. Kang.'

4. Use "*" and "+" to match multiple characters

The asterisk''matches the preceding characters any number of times, including 0.

The plus sign'+ 'matches the preceding character at least once.

Find out the students who start with 19 and end with 6

Select from TStudent where cardid regexp'^ 19.6 $'

Find out that there are 123 students in the × ×.

Select from TStudent where cardid regexp '.123 +.'

5. Match the specified string

A regular expression can match a specified string as long as the matching string is in the query text. If you want to match multiple strings, they are separated by the delimiter'|'.

Select * from TStudent where sname regexp'Wu | Yin | Luo'

6. Match any of the specified characters

Square brackets "[]" specify a set of characters that match only one of the characters, that is, the text you are looking for. Chinese characters are not supported.

Select from TStudent where email regexp'[wmurz]'

Select from TStudent where cardid regexp'^ [1-3pr 7]'

7. Match characters other than the specified characters

"[^ character set]" matches any characters that are not in the specified collection.

Select * from TStudent where cardid regexp'^ [^ 1-7]'

8. Specify the number of consecutive occurrences of a string by using {M} or {MJ N}

"string {n,}" means to match at least the first character of n times. "string {nrecoery m}" means that the previous string is matched no less than n times and no more than m times.

Look for students who appear in 138 and followed by 8 digits 0-9.

Select * from TStudent where cardid regexp '138 [0-9] {15}'

For the above about MySQL database SQL query what are the main methods, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.