MySQL must know reading notes-2 (weight, limit, result sort, wildcard, regular expression)

DISTINCT (de-weight) and limit

Retrieving data that is not duplicated

Using the DISTINCT keyword, note that this keyword is for all columns

#查找所有不同的vend_id
SELECTDISTINCT  vend_id from Products
#查找所有不同的vend_id, Prod_priceSELECTDISTINCT  vend_id, Prod_price from Products

Limit on the number of result rows

Use limit

SELECT Prod_name  34;
#输出第三行之后的四行 (i.e. 4567 rows)

MySQL5 also supports this writing, with the same effect as before

4 3

*************************************************************************************************************** **************************************************************

Sorting of results

By using order BY, the default ascending

SELECT Prod_name  from  Products ORDER  by Prod_name

(This article is not necessarily the complete result)

Sort by multiple columns

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by Prod_price, Prod_name
#先按价格排序, prices are sorted by name

Specify collation

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by DESC;
#指定降序

Multiple columns specify a different collation

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by DESC, Prod_name;
#先按降序排列价格, the price is the same by product name in ascending order

NOTE: The DESC keyword only acts on the previous column, and DESC corresponds to ascending ASC, which is the default, so do not write

Exercise: Find the most expensive items

SELECT Prod_price, Prod_name  from  Products ORDER  by DESC  1

*************************************************************************************************************** **************************************************************

Where clause filters data

SELECT Prod_price, Prod_name  from  Products where = ' fuses '

Results are case-insensitive

Between keywords

SELECT Prod_price, Prod_name  from  Products where between 5  and Ten

Range contains boundary values

Null value Check

SELECT cust_id, Cust_email
From customers
WHERE Cust_email is NULL;

And operator

SELECT prod_id, Prod_price, Prod_name  from  Products WHERE = 1003  and <= Ten

Or operator

SELECT prod_id, Prod_price, Prod_name, vend_id  from  Products WHERE = 1003 OR = 1002

In operator

SELECT prod_id, Prod_price, Prod_name, vend_id  from  Products WHERE inch (10021003)

Not in operator

Just the opposite of the in operator

*************************************************************************************************************** **************************************************************

Filtering with wildcards

You must use the LIKE keyword when you use a wildcard character

% wildcard character

Find all items whose name starts with Jet (the letters are not case-sensitive and are determined by the MySQL configuration method)

SELECT prod_id, Prod_name  from  Products WHERE  like ' jet% ';

SELECT prod_id, Prod_name  from  Products WHERE  like ' %anvil% ';

SELECT prod_id, Prod_name  from  Products WHERE  like ' s%e ';

Note: Null values are not matched by%

_ Wildcard Characters

Match multiple characters to%, _ matches only one character

SELECT prod_id, Prod_name  from  Products WHERE  like ' % ton Anvil ';

SELECT prod_id, Prod_name  from  Products WHERE  like ' _ Ton Anvil ';

Caution: Do not overuse wildcard characters

*************************************************************************************************************** **************************************************************

Using regular expressions to search

A regular expression is a special string used to match text.

Search for a string containing ' 1000 ' in the product name

SELECT prod_id, Prod_name  from  Products WHERE '  + '

. Represents a match for one character

SELECT prod_id, Prod_name  from  Products WHERE ' . '

Match is case insensitive, binary keyword is used for case sensitivity

SELECT prod_id, Prod_name  from  Products WHERE BINARY ' JetPack. '

or match

SELECT prod_id, Prod_name  from  Products WHERE ' 1000|2000 '

Match one of several characters

Use [] to surround the characters you want to match

SELECT prod_id, Prod_name  from  Products WHERE ' [123] ton'

[123] ton equivalent to [1|2|3] ton or 1 ton|2 ton|3 ton

[^123] means that the match is in addition to these characters

SELECT prod_id, Prod_name  from  Products WHERE ' [^123] ton '

Match Range

SELECT prod_id, Prod_name  from  Products WHERE ' [1-5] ton'

Match Special characters

Due to [], | , . , such as special words regular is given a specific meaning in the expression, so matching these characters need to be preceded by \ \

SELECT prod_id, Prod_name  from  Products WHERE ' \\. '

Character

Match multiple instances

Match four numbers that are linked together

SELECT prod_id, Prod_name  from  Products WHERE ' [[:d igit:]]{4} '

Can also be written like this

SELECT prod_id, Prod_name  from  Products WHERE ' [0-9][0-9][0-9][0-9] '

Locator characters

Find all products starting with a number (including the number of decimal points)

SELECT prod_id, Prod_name  from  Products WHERE ' ^[0-9\\.] '

To test the regular expression

-------------------------------------------------------------------------------------------------------

Yes, you are right, I copied the book again ~ ~ ~

MySQL must know reading notes-2 (weight, limit, result sort, wildcard, regular expression)