编程知识 cdmana.com

MySQL must know and must know notes -- basic knowledge of query

Query the basics

Chapter vii. Data filtering

Combine where Clause

MySQL Allow multiple WHERE Clause . These Sentences can be used in two ways : With AND The manner of clause or OR Clause to use .

AND The operator

You can use AND Operator to WHERE Clause conditions

--  Retrieved by the 1003 Manufactured at a price less than or equal to 10 Names and prices of all products in US dollars 
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

OR The operator

OR Used to represent a row that matches any given condition .

--  Retrieved by the 1002 and 1003 The name and price of the product made 
SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 or vend_id = 1003;

Order of calculation

WHERE Can contain any number of AND and OR The operator . Allow a combination of the two for complex analysis And advanced filtering .

AND Has a higher priority than OR

--  List prices as 10 dollar ( contain ) Above and by 1002 or 1003 All products manufactured 
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

You can use brackets to give priority to a certain part .

IN The operator

Used to specify the condition range , Comma separated lists of legal values are all in parentheses .

--  Search for suppliers 1002 and 1003 All products manufactured .
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

In the above sentence WHERE vend_id IN (1002, 1003) Equate to WHERE vend_id=1002 OR vend_id=1003)

IN Than OR Faster execution , The biggest advantage is that it can include other SELECT sentence , Be able to build more dynamically WHERE Clause .

  • When using a long list of legal options ,IN Operator syntax is clearer and more intuitive .
  • In the use of IN when , The order of calculations is easier to manage ( Because fewer operators are used ).
  • IN Operator general ratio OR Faster operator list execution .
  • IN The biggest advantage is that it can contain other SELECT sentence , Make it possible to build more dynamically state WHERE Clause .

NOT The operator

--  In addition to the listed 1002,1003 All the products from other suppliers 
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

Filter with wildcards

wildcard (wildcard) A special character used to match a part of a value .

LIKE The operator

LIKE instructions MYSQL, The following search patterns are compared using wildcard matching instead of direct equal matching .

Percent sign (%) wildcard

Means any character appears any number of times

--  example : Find out all jet The first product 
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
--  example : Use multiple wildcards , Match any location that contains anvil Value , No matter what character appears before or after it 
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
--  example : find s At first e All products at the end 
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

Although it seems % Wildcards can match anything , With one exception , namely NULL. Even if it's WHERE prod_name LIKE '%' It doesn't match with the value NULL Line as product name .

Underline (_) wildcard

--  Match only a single character, not multiple characters 
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

The technique of using wildcards

  • Don't overuse wildcards , If other operators can achieve the goal, other operators should be used
  • When you do need to use wildcards , Unless absolutely necessary , Otherwise, don't use them at the beginning of the search . Putting wildcards at the beginning of a search pattern is the slowest way to search .
  • Pay close attention to the location of wildcards

Use MySQL Regular expressions

Basic character matching

MySQL Use in REGEXP Keyword specifies the character matching pattern of the regular expression

--  example : retrieval prod_name Include text 1000 All of the line 
SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

. To match any character

SELECT prod_name FROM products WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
  • LIKE and REGEXP The difference between : LIKE '1000' Match the entire column value , be equal to '1000' Then the corresponding line will be returned , and REGEXP '1000' Match within column values , If you include '1000' The corresponding line is returned .

in other words ,LIKE Match the entire column , If the matched text appears in the column value ,LIKE It will not be found , The corresponding row is not returned ( Unless you use wildcards ). and REGEXP Match within column values , If the matched text appears in the column value ,REGEXP It will be found , The corresponding line is returned , also REGEXP Can match the entire column value ( And LIKE Same effect ).

It's easy to understand by looking at a practical example .

This is a orders surface :

image-20210608205727457

We use like To query :

select * from orders where cust_id like "100";

The result is as follows , It's empty :

image-20210608213851428

Use regexp experiment :

select * from orders where cust_id REGEXP "100";

The result is as follows :

image-20210608213822110

MySQL Regular expression matching ( since 3.23.4 After version ) Case insensitive ( That is, uppercase and lowercase match ). For case sensitivity , have access to BINARY keyword

WHERE prod_name REGEXP BINARY 'JetPack .000';

Conduct OR matching

| For regular expressions OR The operator , Represents one of the matches

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

You can give more than two OR Conditions 1000|2000|3000

Match one of several characters

[] Represents a match [] Any single character in , It can be understood as another form of OR sentence .

in addition ,[123] yes [1|2|3] Abbreviation

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

--  result 

+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
+-------------+

Remember to add square brackets , Here is the result without square brackets

SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name

--  result 

+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
|JetPack 1000 |
|JetPack 2000 |
|TNT (1 stick)|
+-------------+

Without square brackets, it matches to mean 1 OR 2 OR 3 Ton, So it will also retrieve JetPack 1000 And so on does not meet the requirements of the line .

Character sets can also be negated , To negate a word set , Place... At the beginning of the collection ^, for example [^123] Match anything but these characters

Match range

matching 0 To 9, It can be used [0123456789], In order to simplify the , It can be used - To define the scope , It can be written. [0-9]. Empathy ,a To z You can write the scope of [a-z].

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

--  result 

+-------------+
|  prod_name  |
+-------------+
| .5 ton anvil|
| 1 ton anvil |
| 2 ton anvil |
+-------------+

Match special character

Special characters , For example, as we mentioned earlier

  • .: Match any character
  • []: Match one of several characters
  • -: Specified scope

Like the following example :

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.'
ORDER BY vend_name;

--  result 

+---------------+
|   vend_name   |
+---------------+
| ACME          |
| Anvils R Us   |
| Furball Inc.  |
| Jet Set       |
| Jouets Et Ours|
| LT Supplies   |
+---------------+

because '.' To match any character , So the result of the match is not what we want .

If you just want to match the tape . Result , Must use \\ As the leader . Empathy , Match other special characters with \\ As the leader .

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

--  result 

+---------------+
|   vend_name   |
+---------------+
| Furball Inc.  |
+---------------+

All characters with special meaning in regular expressions are escaped in this way \\ Also used to refer to metacharacters

Metacharacters explain
\\f Change the page
\\n Line break
\\r enter
\\t Tabulation
\\v Vertical tabulation
To match \ In itself , Need to use \\\

Match character class

class explain
[:alnum:] Any letter and number ( Same as [a-zA-Z0-9])
[:alpha:] Any character ( Same as [a-zA-Z])
[:cntrl:] Spaces and tabs ( Same as [\t])
[:digit:] ASCII Control characters (ASCII)0 To 31 and 127
[:graph:] Arbitrary number ( Same as [0-9])
[:lower:] Any lowercase letter ( Same as [a-z])
[:print:] Any printable character
[:punct:] Neither in [:alnum:] No more [:cntrl:] Any character in
[:space:] Any whitespace character, 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])

Match multiple instances

Metacharacters explain
* 0 One or more matches
+ 1 One or more matches ( be equal to {1,})
0 Or 1 A match ( be equal to {0,1})
{n} A specified number of matches
{n,} No less than the specified number of matches
{n.m} The range of the number of matches (m No more than 255)

example :

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name

--  result 

+---------------+
|   prod_name   |
+---------------+
| TNT (1 stick) |
| TNT (5 sticks)|
+---------------+

explain :

  • \\( Indicates a matching left parenthesis
  • [0-9] Represents a match 0 To 9 Any number of
  • stick? matching 'stick' and 'sticks'
  • \\) It means to match the right bracket

example : Match the connected 4 Digit number

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

--  result 

+---------------+
|   prod_name   |
+---------------+
| JetPack 1000  |
| JetPack 2000  |
+---------------+
--  Or you could write it as  '[0-9][0-9][0-9][0-9]'

Locator

Metacharacters explain
^ Start of text
$ End of text
[:<:] The beginning of words
[:>:] Ending of words
example : Find a number ( Including the beginning of the decimal point ) All the products in the beginning
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

--  result 

+---------------+
|   prod_name   |
+---------------+
| .5 ton anvil  |
| 1 ton anvil   |
| 2 ton anvil   |
+---------------+

Chapter ten Create calculated fields

Splicing field

Splicing : Join values together to form a single value

stay SELECT In the sentence , You can use Concat() Function to splice two columns .Concat() Function requires one or more specified strings , The strings are separated by commas .

SELECT Concat(vend_name, ' (',vend_country,')') FROM vendors
ORDER BY vend_name;

--  result 

+-----------------------------------------+
| Concat(vendname,' (',vend_country,')') |
+-----------------------------------------+
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
+-----------------------------------------+

Remove extra spaces from fields

function explain
Trim() Remove the space on both sides
LTrim() Remove the space on the left
RTrim() Remove the space on the right

Example : Use RTrim() Function to remove the extra space on the right .

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;

Use the alias

It can be used AS Keywords give aliases

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;

--  result 

+----------------------------+
| vend_title                 |
+----------------------------+
| ACME (USA)                 |
| Anvils R Us (USA)          |
| Furball Inc. (USA)         |
| Jet Set (England)          |
| Jouets Et Ours (France)    |
| LT Supplies (USA)          |
+----------------------------+

Perform arithmetic calculations

--  Sum up the price of the item ( Unit price times order quantity )
SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;

--  result 

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

In the fourth line of the above code quantity * item_price, We can do arithmetic calculations on the retrieved data , The commonly used operators are as follows :

The operator explain
+ Add
- reduce
* ride
/ except

Chapter 11 Using data processing functions

Text processing function

Common text processing functions

function explain
Left() Returns the character to the left of the string
Length() Returns the length of the string
Locate() Find a substring of a string
Lower() Convert string to lowercase
LTrim() Remove the space to the left of the string
Right() Returns the character to the right of the string
RTrim() Remove the space to the right of the string
Soundex() Return string SOUNDEX value
SubString() Return the character of the substring
Upper() Convert string to uppercase
--  Example : Convert text to uppercase 
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

The above table Soundex() Additional instructions for :

SOUNDEX It's an algorithm that converts any text into an alphanumeric pattern that describes its speech representation , Enables comparison of sounds rather than letters in strings .MySQL Provide right SOUNDEX Support for .

Take a look at the following example

There is a user in the table whose name is Y.Lee, adopt select Input error when querying contact is Y.Lie. here Y.Lee This line will not be retrieved , But use SOUNDEX retrieval , Can match sounds similar to Y.Lie The contact name of :

SELECT cust_name, cust_contact FROM customers
WHERE Soundex(cust_contact)= Soundex('Y Lie');

--  result 

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

Date and time processing functions

function explain
AddDate() Add a date ( God 、 Zhou et al )
AddTime() Add a time ( when 、 Grading )
CurDate() Return current date
CurTime() Return current time
Date() Return date part of date time
DateDiff() Calculate the difference between the two dates
Date_Add() Highly flexible date calculation function
Date_Format() Returns a formatted date or time string
Day() Returns the days part of a date
DayOfWeek() For a date , Returns the corresponding day of the week
Hour() Returns the hour part of a time
Minute() Returns the minute part of a time
Month() Returns the month part of a date
Now() Returns the current date and time
Second() Returns the second part of a time
Time() Returns the time part of a date time
Year() Returns the year part of a date

Note on using date format :

  • The date must be in the format yyyy-mm-dd

  • About datetime

    • SELECT cust_id, order_num FROM orders
      WHERE order_date = '2021-01-02';
      
    • sql Statement order_date The type is datetime, It has a time value 00:00:00. When generating time data , For example, generate "2021-01-02", In addition to the date, the generated data will also automatically generate hours, minutes and seconds , The default is 00:00:00.

    • image-20210611162750885

    • here , If you retrieve a value of 2021-01-02 14:06:29, The top WHERE order_date = '2021-01-02' This line won't be retrieved

    • To use Date() function , To find a value of 2021-01-02 14:06:29 The line of

    • SELECT cust_id, order_num FROM orders
      WHERE Date(order_date) = '2021-01-02';
      

Example : Retrieve 2005 year 9 All orders placed in the month

SELECT cust_id, order_num FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

perhaps

SELECT cust_id, order_num FROM orders
WHERE Year(roder_date) = 2005 AND Month(order_date) = 9;

Numerical processing function

function explain
Abs() Returns the absolute value of a number
Cos() Returns the cosine of an angle
Exp() Returns the index value of a number
Mod() Returns the remainder of the division operation
Pi() Return the PI
Rand() Returns a random number
Sin() Returns the sine of an angle
Sqrt() Returns the square root of a number
Tan() Returns the tangent of an angle

Chapter 12 Aggregate function

Aggregation function

Aggregation function (aggregate function): Run on line group , Functions that calculate and return individual values .

function explain
AVG() Returns the average of a column
COUNT() Returns the number of rows in a column
MAX() Returns the maximum value of a column
MIN() Returns the minimum value of a column
SUM() Returns the sum of values in a column

VG() function

example : return products The average price of all the products in the table

SELECT AVG(prod_price) AS avg_price FROM products;

example : Returns the average price of a product offered by a specific supplier

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

COUNT() function

example : return customer The total number of customers in the table

SELECT COUNT(*) AS num_cust FROM customers;

example : Count only customers with email addresses

SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX() function

example : return products The most expensive item on the list

SELECT MAX(prod_price) AS max_price
FROM products;

Use... For non numerical data MAX() MySQL Allows it to be used to return the maximum value in any column , Includes returning the maximum value in the text column . When used for text data , If the data is sorted by the corresponding column , be MAX() Go back to the last line .MAX() Function ignores column values as NULL The line of .

MIN() function

example :

SELECT MIN(prod_price) AS min_price FROM products;

SUM() function

Returns the sum of the specified column values ( A total of ) example : Retrieve the total number of items ordered

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

example : Add up the calculated value , Add up each item item_price*quantity, Get the total amount of the order

SELECT SUM(item_price*quantity) AS total_price
FORM orderitems
WHERE order_num = 20005;

Gather different values ( Apply to 5 Later version )

The above five aggregation functions can be used as follows :

  • Perform calculations on all rows , Appoint ALL Parameters or no parameters (ALL As the default )
  • Contains only different values , Appoint DISTINCT Parameters
--  The average price of goods 
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

If you specify a column name , be DISTINCT It can only be used for COUNT().DISTINCT Cannot be used for COUNT(*), So it's not allowed to use COUNT(DISTINCT), Otherwise, an error will occur . Similarly ,DISTINCT Column names must be used , Cannot be used for calculations or expressions .

Combining aggregate functions

SELECT Statements can contain multiple aggregate functions as needed

SELECT COUNT(*) AS num_items;
	   MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

--  result 

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.50 | 16.133571 |
+-----------+-----------+-----------+-----------+

Chapter 13 Grouped data

The data packet

Grouping allows data to be divided into multiple logical groups , So that we can aggregate each group .

Create grouping

example : according to vend_id grouping , Calculate the total for each group

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

--  result 

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

It turns out that vend_id by 1001 Yes 3 individual ,vend_id by 1002 Yes 2 individual ,vend_id by 1003 Yes 7 individual ,vend_id by 1005 Yes 2 individual .

In specific use GROUP BY Before clause , You need to know some important rules .

  • GROUP BY Clause can contain any number of columns , Enables nesting of groups , Provide more detailed control for data grouping
  • If GROUP BY Group is nested in clause , The data will be aggregated on the final defined group . let me put it another way , When creating a group , All columns specified are calculated together ( Therefore, data cannot be retrieved from individual columns ).
  • GROUP BY Each column listed in clause must be a retrieval column or a valid expression ( But not an aggregate function ). If in SELECT Using expressions in , Must be in GROUP BY Clause specifies the same expression . Cannot use alias .
  • Except for aggregate calculation statements ,SELECT Each column in the statement must be in the GROUP BY Given in Clause .
  • If there are NULL value , be NULL Will be returned as a group . If there are more than one row in the column NULL value , They will be divided into groups .
  • GROUP BY Clause must appear in WHERE After Clause ,ORDER BY Before clause .

Filter grouping

WHERE It specifies the line , Not in groups ,WHERE No concept of grouping

Use HAVING Filter grouping

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

--  result 

+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

WHERE It doesn't work , Because filtering is based on grouped aggregate values rather than specific row values .

--  List with 2 individual ( contain ) above 、 The price for 10( contain ) Suppliers of the above products 
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2

--  result 

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

Grouping and sorting

although GROUP BY and ORDER BY Do the same job often , But they are very different .

ORDER BY And GROUP BY The difference between :

ORDER BY GROUP BY
The output from sorting Group lines . But the output may not be in the order of grouping
Any column can use ( even to the extent that Unselected columns can also use ) Only selection columns or expression columns can be used , And every choice has to be used List expression
Not necessarily If you use columns with aggregate functions ( Or expressions ), Must be used

Generally in use GROUP BY When clause , It should also be given ORDER BY Clause . This is the only way to ensure that the data is sorted correctly . Don't just rely on GROUP BY Sorting data .

--  Retrieve total order price greater than or equal to 50 The order number and total order price of the order 
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertital;

SELECT Clause order

SELECT Clauses and their order

Clause explain Whether it is necessary to use
SELECT Column or expression to return yes
WHERE A table from which to retrieve data Use only when selecting data from a table
GROUP BY Group description In group computing aggregation is used
HAVING Group level filtering no
ORDER BY Output sort order no
LIMIT Number of rows to retrieve no

The above clauses must be used in this order

Chapter 14 Use subquery

SELECT order_num FROM orderitems
WHERE prod_id = 'TNT2';

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);

You can use the following methods

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

One more condition :

SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)

Merge into one sentence sql

SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders
				 WHERE order_name IN(SELECT order_num FROM orderitems
                 					 WHERE prod_id ='TNT2'));
  • stay WHERE The use of subqueries in a clause should ensure that SELECT Sentence has and WHERE Same number of columns in clause .
  • The code given here works and gets the desired results . however , Using subqueries is not always the most effective way to perform this type of data retrieval .( That is to say, the performance of write execution is not necessarily the best )

Use subqueries as calculated fields

#  To customers 10001 Count the orders of 
SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
#  In order to perform COUNT(*) Calculation , Should be COUNT(*) As a subquery 
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;

版权声明
本文为[kylinwms]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/06/20210612001405402a.html

Scroll to Top