MySQL 5-9

5. sort and retrieve data

5.1 sort data

ORDER

SELECT prod_name FROM products ORDER BYprod_name; // sort search

SELECT prod_id, prod_price, prod_name

FROM products ORDER BYprod_price, prod_name; // sort by multiple columns

5.2 specify the sorting direction

DESC sort in descending order

SELECT prod_id, prod_price, prod_name FROMproducts order by prod_price DESC; // sort in descending ORDER

SELECT prod_id, prod_price, prod_name FROMproducts order by prod_price DESC, prod_name; // only the specified columns are in descending ORDER.

5.3 The combination of order by and LIMIT can find the highest or lowest value

SELECT prod_price FROM products ORDER BYprod_price desc limit 1; // obtain the most expensive price

6. filter data

6.1 use the WHERE clause

SELECT prod_name FROM products WHEREprod_price = 2.5; // only the rows whose prod_price value is 2.5 are returned.

WHERE clause position: Before ORDER

6.2WHERE clause OPERATOR:

=, <> (Not equal ),! = (Not equal to), <, <=,>, >=, BETWEEN (BETWEEN the specified two values)

SELECT prod_name, prod_price FROM productsWHERE prod_price BETWEEN 3 AND 10;

6.3 null check

When creating a table, the table designer can specify whether the column can contain no value. When a column does not contain a value, it is NULL. Is null clause to check columns with NULL values

Input SELECT prod_name FROM products WHERE prod_price is null;

Output Empty set (0.00 sec) // the price must be blank

Enter mysql> SELECT cust_id FROM customers WHERE cust_email is null;

Output: + ------------- +

| Cust_id |

+ --------------- +

| 1, 10002 |

| 1, 10005 |

+ --------------- +

2 rows in set (0.01 sec)

It indicates that the cust_email column has rows with null values. The values of these rows are 10002 and 10005.

7. Data Filtering

7.1 combination WHERE clause

MySQL allows multiple WHERE clauses. These clauses are combined in two ways: and or.

7.1.1 AND operator

SELECT prod_id, prod_price, prod_name FROM products

WHERE vend_id = 1003 AND prod_price <= 10; // both conditions must be met

7.1.2 OR operator

SELECT prod_id, prod_price, prod_name FROM products

WHERE vend_id = 1003 OR vend_id = 1002; // you can set either of the two conditions.

7.1.3 calculation order

SELECT prod_id, prod_price, prod_name FROM products

WHERE vend_id = 1003 OR vend_id = 1002 AND prod_price> = 10; // The AND operator has a higher priority than OR

Therefore, the above understanding is: Any product made by the supplier at a price of more than 10 US dollars, or any product made by the supplier by 1003, regardless of the price

SELECT prod_id, prod_price, prod_name FROM products

WHERE (vend_id = 1003 OR vend_id = 1002) AND prod_price> = 10; // () the highest priority

The preceding statement assumes that all products are made at a price of more than 10 US dollars and 1002 or 1002.

7.2 IN Operator

The IN operator is used to specify the condition range. It acts on the OR operator rather than the where operator, but is more clear and intuitive. It can contain other SELECT statements and can create a where clause more dynamically.

SELECT prod_price, prod_name FROM products

WHERE vend_id IN (1001,1002, 1003) order by prod_name;

7.3 NOT operator

Not operator: denies any conditions followed by it.

SELECT prod_price, prod_name FROM products

WHERE vend_id not in (1002,1003, 1001) order by prod_name;

8. Filter common matches

8.1 LIKE Operator

Wildcard (wildcard): a special character used to match a part of a value

Searchpattern: A Search Condition consisting of a nominal value, wildcard character, or a combination of two.

LIKE is only MySQL, followed by the search mode that uses wildcard matching instead of direct equality matching for comparison

8.1.1 percent (%) wildcard

% Indicates any occurrence of any number of characters, but cannot match NULL

SELECT prod_price, prod_name FROM products WHERE prod_name LIKE 'Jet % ';

SELECT prod_price, prod_name FROM products WHERE prod_name LIKE '% andevil % ';
8.1.2 underline (_) wildcard

Underline matches a single character

8.2 wildcard skills

1) do not over-use wildcards

2) Put the wildcard at the beginning, with the slowest search speed

9. Search Using Regular Expressions

A regular expression is a string (character set and) used to match the special protection of text ).

9.1 use mysql Regular Expression

9.1.1 basic character matching

SELECT prod_name FROM products WHERE prod_name REGEXP '. 000' ORDERBY prod_name;

+ -------------- +

| Prod_name |

+ -------------- +

| Jetpackage 1000 |

| Jetpackage 2000 |

+ -------------- +

The preceding statement uses the zezheng expression. 000 .. It is a special character in the Regular Expression Language. It indicates matching any character. Therefore, both 1000 and 2000 match and return.

Regular Expression matching is case-insensitive. You can use the BINARY keyword after REGEXP.

9.1.2 or matching

SELECT prod_name FROM products WHERE prod_name REGEXP '2017 | 100 ';

Here | is the OR operator of the Regular Expression

9.1.3 match one of several characters

SELECT prod_name FROM products

WHERE prod_name REGEXP '[123] Ton 'order BY prod_name;

[123] indicates Matching 1, 2, or 3.

9.2.4 matching range

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton ';

[1-5] defines a range.

9.2.5 match special characters

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\. 'order BYvend_name;

To match special characters, \ must be used as the leading character.

\-Indicates-

\. Indicates.

\ Represents \

\ F indicates page feed

\ N indicates line feed

\ R indicates carriage return

\ T indicates tabulation

\ V indicates vertical tabulation

9.2.6 matching character class

[: Alnum:] Any letter and number (same as [a-zA-Z0-9])

[: Alpha:] any character (same as [a-zA-Z])

[: Blank:] spaces and tabs (same as [\ t])

[: Cntrl:] ASCII control characters (ACII 0 to 31 and 127)

[: Digit:] Any number (same as [0-9])

[: Graph:] is the same as [: print:], but does not contain spaces.

[: Lower:] Any lowercase letter

[: Print:] any printable character

[: Punct:] any character neither [: alnum:] nor [: cntrl :]

[: Space:] any blank characters including spaces (same as [\ f \ n \ r \ t \ v])

[: Upper:] any capital letter (same as [A-Z])

[: Xdigit:] Any hexadecimal number (same as [a-fA-F0-9])

9.2.6 Matching Multiple instances

SELECT prod_name FROM products

WHERE prod_name REGEXP '\ ([0-9] sticks? \) 'Order BY prod_name; // The result is as follows:

+ ---------------- +

| Prod_name |

+ ---------------- +

| TNT (1 stick) |

| TNT (5 sticks) |

+ ---------------- +

Regular Expression \ ([0-9] sticks? . \ (MATCH (, [0-9] matches any number (1 and 5 in this example), sticks? Match stick and sticks (after s? Make s optional, because? Match 0 or 1 occurrence of any character of the year), \) Match ).

SELECT prod_name FROM products

WHERE prod_name REGEXP '[[: digit:] {4} 'order BY prod_name;

+ -------------- +

| Prod_name |

+ -------------- +

| Jetpackage 1000 |

| Jetpackage 2000 |

+ -------------- +

2 rows in set (0.00 sec)

In [[: digit:] {4}, {4} requires that the character (any number) before it appear four times, so [[: digit:] {4} indicates any 4-digit join

9.2.8 positioning character

^ Start of Text

$ End of Text

[[: <:] Start of a word

[[: >:]] End of a word

SELECT prod_name FROM products

WHERE prod_name REGEXP '^ [0-9 \.] 'order BY prod_name;

^ [0-9 \.] indicates matching only when. Or any number is the first character in the string.