This is the second day of my participation in Gwen Challenge

Query Basics

Chapter 7 Data Filtering

Combine the WHERE clause

MySQL allows multiple WHERE clauses. These clauses can be used in two ways: as an AND clause OR as an OR clause.

The AND operator

You can use the AND operator to attach conditions to the WHERE clause

Retrieves the name and price of all products made by 1003 that cost less than or equal to $10
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price < = 10;
Copy the code

The OR operator

OR is used to retrieve rows that match any given condition.

Retrieve the name and price of products made by 1002 and 1003
SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 or vend_id = 1003;
Copy the code

Calculate the order

WHERE can contain any number of AND AND OR operators. Allow a combination of the two for complex and advanced filtering.

AND has a higher priority than OR

List all products that cost $10 or more and are made by 1002 or 1003
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price > = 10;
Copy the code

I can put it in parentheses and let some of it go first.

The IN operator

The comma-separated list of valid values used to specify the range of conditions is all enclosed in parentheses.

Retrieve all products made by suppliers 1002 and 1003.
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002.1003)
ORDER BY prod_name;
Copy the code

WHERE vend_id IN (1002, 1003) WHERE vend_id=1002 OR vend_id=1003

IN executes faster than OR, and the biggest advantage is that it can include other SELECT statements, making it possible to establish WHERE clauses more dynamically.

• The syntax of the IN operator is clearer and more intuitive when using a long list of legal options.
• When IN is used, the order of calculations is easier to manage (because fewer operators are used).
• The IN operator is generally faster than the OR operator list.
• The biggest advantage of IN is that it can include other SELECT statements, making it possible to build WHERE clauses more dynamically.

The NOT operator

-- List all the items divided by 1002, SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;Copy the code

Chapter 8 Filters with wildcards

A wildcard is a special character that matches part of a value.

The LIKE operator

LIKE instructs MYSQL, followed by the search pattern, to use wildcard matching instead of direct equality matching for comparison.

Percent sign (%) wildcard

Represents any number of occurrences of any character

Example: Find all of JET's products
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
Example: Use multiple wildcards to match any value containing anvil, regardless of what characters appear before or after it
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
Example: Find all the products beginning with S and ending with e
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
Copy the code

Although it seems that the % wildcard can match anything, there is one exception, NULL. Even WHERE prod_name LIKE ‘%’ does not match a row with the value NULL as the product name.

Underscore (_) Wildcard

-- Matches only one character, not multiple characters
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
Copy the code

The technique of using wildcards

• Do not overuse wildcards; use other operators if they will do the trick
• When you do need to use wildcards, don’t use them at the beginning of a search unless absolutely necessary. Placing wildcards at the beginning of a search pattern is the slowest.
• Pay close attention to the wildcard positions

Chapter 9 uses regular expressions to search

Use MySQL regular expressions

Basic character matching

The REGEXP keyword is used in MySQL to specify character matching patterns for regular expressions

Example: Retrieve all lines with prod_name containing text 1000
SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
Copy the code

. Matches any character

SELECT prod_name FROM products WHERE prod_name REGEXP '000'
ORDER BY prod_name;
Copy the code

• The difference between LIKE and REGEXP: LIKE ‘1000’ matches the entire column value and returns the row if it is equal to ‘1000’, whereas REGEXP ‘1000’ matches the column value and returns the row if it contains ‘1000’.

That is, LIKE matches the entire column, and if the matched text appears in the column value, LIKE will not find it, and the corresponding row will not be returned (unless wildcard characters are used). REGEXP matches within a column value. If the matched text appears in a column value, REGEXP will find it, the corresponding row will be returned, and REGEXP can match the entire column value (same effect as LIKE).

If you look at a real example, you’ll see.

Here is the Orders table:

We use like to query:

select * from orders where cust_id like "100";
Copy the code

The result is empty as follows:

Using the RegEXP experiment:

select * from orders where cust_id REGEXP "100";
Copy the code

The results are as follows:

MySQL’s regular expression matching (since version 3.23.4) is case insensitive (that is, both upper and lower case matches). You can use the BINARY keyword for case sensitivity

WHERE prod_name REGEXP BINARY 'JetPack .000';
Copy the code

Perform OR matching

| for regular expressions OR operator, means to match one of them

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

Can give more than two 1000 | 2000 | 3000 OR conditions

Matches one of several characters

[] represents any single character in the match [], which can be understood as another form of AN OR statement.

In addition, [123] is the abbreviation of | 2 | 3 [1]

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

Results -

+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
+-------------+
Copy the code

Be sure to put square brackets, here’s what happens without square brackets

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

Results -

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

Without square brackets, it matches meaning 1 OR 2 OR 3 tons, so it also retrieves lines like JetPack 1000 that don’t meet the requirements.

Character sets can also be negated. To negate a character set, place ^ at the beginning of the set, e.g. [^123] matches anything except these characters

Match the range

For matches from 0 to 9, we can use [0123456789]. For simplicity, we can use – to define the range. We can write [0-9]. Similarly, the range from a to z can be written as [a-z].

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

Results -

+-------------+
|  prod_name  |
+-------------+
| . 5 ton anvil|
| 1 ton anvil |
| 2 ton anvil |
+-------------+
Copy the code

Match special characters

Special characters, like the ones we talked about earlier

• .: Matches any character
• []: Matches a character among several characters
• -: Specified range

Here’s an example:

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

Results -

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

Because ‘.’ matches any character, the result of the match is not what we want.

If you just want to match bands. The result of must be preceded by \\. In the same way, other special characters are matched with \\.

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

Results -

+---------------+
|   vend_name   |
+---------------+
| Furball Inc.  |
+---------------+
Copy the code

All characters with special meaning in regular expressions are escaped in this way. \\ is also used to reference metacharacters

metacharacters	instructions
\\f	Change the page
\\n	A newline
\\r	enter
\\t	TAB
\\v	Vertical TAB
In order to match\Itself, need to use\ \ \

Matching character class

class	instructions
[:alnum:]	Any letters and numbers (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:]	Any number (same as [0-9])
[:lower:]	Any lowercase letter (same as [a-z])
[:print:]	Any printable character
[:punct:]	Any character that is neither in [:alnum:] nor [: CNTRL :]
[:space:]	Any whitespace character including space (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])

Matching multiple instances

metacharacters	instructions
*	Zero or more matches
+	1 or more matches (equal to {1,})
?	Zero or one match (equal to {0, 1})
{n}	Specifies the number of matches
{n,}	Not less than a specified number of matches
{n.m}	Range of matches (m does not exceed 255)

Ex. :

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

Results -

+---------------+
|   prod_name   |
+---------------+
| TNT (1 stick) |
| TNT (5 sticks)|
+---------------+
Copy the code

Description:

• \ \ (Matches the open parenthesis
• [0-9]Matches any number from 0 to 9
• stick?Match ‘stick’ and ‘sticks’
• \ \]Matches the close parenthesis

Example: Match four connected digits

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

Results -

+---------------+
|   prod_name   |
+---------------+
| JetPack 1000  |
| JetPack 2000  |
+---------------+
[0-9][0-9][0-9][0-9] [0-9]
Copy the code

locator

metacharacters	instructions
^	The beginning of a text
$	The end of a text
[: < :]	The beginning of the term
/ : > :	At the end of the term
Example: Find all products that start with a number, including a decimal point

SELECT prod_name FROM products WHERE prod_name REGEXP '^ [0-9 \ \]'
ORDER BY prod_name;

Results -

+---------------+
|   prod_name   |
+---------------+
| . 5 ton anvil  |
| 1 ton anvil   |
| 2 ton anvil   |
+---------------+
Copy the code

Chapter 10 Creating computed fields

Concatenate field

Splicing: Joining values together to form a single value

In a SELECT statement, you can use the Concat() function to concatenate two columns. The Concat() function requires one or more specified strings, separated by commas.

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

Results -

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

Delete extra Spaces from fields

function	instructions
Trim()	Remove the Spaces on both sides
LTrim()	Remove the Spaces on the left
RTrim()	Remove the Spaces on the right

Example: Use the RTrim() function to remove extra Spaces on the right.

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

Use the alias

Aliases can be given using the AS keyword

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

Results -

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

Perform arithmetic calculations

-- Sum up the price of the item (unit price multiplied by quantity ordered)
SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;

Results -

+---------+----------+------------+----------------+
| 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 |
+---------+----------+------------+----------------+
Copy the code

Quantity * item_price appears in the fourth line of the above code. We can perform an arithmetic calculation on the retrieved data by using the following operators:

The operator	instructions
+	add
–	Reduction of
*	take
/	In addition to

Chapter 11 uses data processing functions

Text processing function

Commonly used text processing functions

function	instructions
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()	Converts the 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 Spaces to the right of the string
Soundex()	Returns the SOUNDEX value of the string
SubString()	Returns the character of a substring
Upper()	Converts the string to uppercase

-- Example: Convert text to uppercase
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
Copy the code

Additional notes to Soundex() in the table above:

SOUNDEX is an algorithm that converts any text into an alphanumeric pattern that describes its phonetic representation, enabling strings to be compared phonetically rather than alphabetically. MySQL provides support for SOUNDEX.

Take a look at the following example

In the table, the name of a user is Y.lee. When you use the select command to query the contact information, the input error is Y.li. The y.lee line will not be retrieved at this point, but a SOUNDEX search will match contact names that sound like Y.Lee:

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

Results -

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
Copy the code

Date and time handlers

function	instructions
AddDate()	Add a date (day, week, etc.)
AddTime()	Add a time (hour, grade)
CurDate()	Return current date
CurTime()	Return current time
Date()	Returns the date portion of the 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 number of days of a date
DayOfWeek()	For a date, return the day of the week
Hour()	Returns the hour portion of a time
Minute()	Returns the minute portion of a time
Month()	Returns the month portion of a date
Now()	Returns the current date and time
Second()	Returns the second portion of a time
Time()	Returns the time portion of a date-time
Year()	Returns the year portion of a date

Notes for using date formats:

• The date must be in the format YYYY-MM-DD

• About the datetime

  • SELECT cust_id, order_num FROM orders
    WHERE order_date = '2021-01-02';
    Copy the code

  • The order_date type in the SQL statement is datetime, which has the time value 00:00:00. When you generate time data, for example, “2021-01-02”, the generated data will automatically generate hour, minute and second in addition to year, month and day. The default value is 00:00:00.

  • 

  • At this point, if you retrieve the value 2021-01-02 14:06:29, the above order_date = ‘2021-01-02’ will not retrieve this row

  • You need to use the Date() function to find the row with the value 2021-01-02 14:06:29

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

Example: Retrieve all orders placed in September 2005

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

or

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

Numerical processing function

function	instructions
Abs()	Returns the absolute value of a number
Cos()	Returns the cosine of an Angle
Exp()	Returns the exponent value of a number
Mod()	Returns the remainder of the division operation
Pi()	Returns 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 Summary functions

Aggregation function

Aggregate function: A function that runs on a row group and calculates and returns a single value.

function	instructions
AVG()	Returns the average value 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 the values of a column

VG () function

Example: Return the average price of all products in the Products table

SELECT AVG(prod_price) AS avg_price FROM products;
Copy the code

Example: Return the average price of a product offered by a particular vendor

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

The COUNT () function

Example: Return the total number of customers in the Customer table

SELECT COUNT(*) AS num_cust FROM customers;
Copy the code

Example: Count only customers with E-mail addresses

SELECT COUNT(cust_email) AS num_cust
FROM customers;
Copy the code

MAX () function

Example: Return the price of the most expensive item in products

SELECT MAX(prod_price) AS max_price
FROM products;
Copy the code

Using MAX() for non-numeric data MySQL allows it to be used to return the maximum value in any column, including the maximum value in a text column. When used for text data, MAX() returns the last row if the data is sorted by the corresponding column. The MAX() function ignores rows with a NULL column value.

The MIN () function

Ex. :

SELECT MIN(prod_price) AS min_price FROM products;
Copy the code

The SUM () function

Returns the sum of the specified column values example: Retrieves the total number of items ordered

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

Example: Add up the calculated values, add up item_price*quantity for each item, and get the total amount of the order

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

Aggregate different values (for versions 5 and later)

All five of the above aggregation functions can be used as follows:

• Evaluate ALL rows with or without ALL arguments (ALL is the default)
• Contains only DISTINCT values, specifying the DISTINCT parameter

-- the average price of an item
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
Copy the code

If you specify a column name, DISTINCT can only be used with COUNT(). DISTINCT cannot be used with COUNT(*). Therefore, COUNT(DISTINCT) is not allowed. Otherwise, an error will occur. Similarly, DISTINCT must use column names and cannot be used for calculations or expressions.

Combinatorial aggregation function

SELECT statements can contain as many aggregation functions as required

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;

Results -

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.50 | 16.133571 |
+-----------+-----------+-----------+-----------+
Copy the code

Chapter 13 Grouped Data

The data packet

Grouping allows you to group data into multiple logical groups so that you can perform aggregation calculations on each group.

Create a group

Example: Calculate the total for each group separately according to vend_id grouping

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

Results -

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

The results show that there are 3 vend_id 1001, 2 vend_id 1002, 7 vend_id 1003, and 2 vend_id 1005.

Before using the GROUP BY clause specifically, there are some important rules to know.

• The GROUP BY clause can contain any number of columns, making it possible to nest groups and provide finer control over data grouping
• If groups are nested in the GROUP BY clause, the data is summarized at the last specified GROUP. In other words, when a group is created, all columns specified are evaluated together (so no data can be retrieved from individual columns).
• Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not an aggregation function). If you use an expression in SELECT, you must specify the same expression in the GROUP BY clause. Aliases cannot be used.
• Every column in a SELECT statement must be given in the GROUP BY clause, except for the aggregate calculation statement.
• If there is a NULL value in the grouping column, NULL is returned as a grouping. If there are multiple rows of NULL values in a column, they are grouped.
• The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

Packet filtering

WHERE specifies rows, not groups, and WHERE has no concept of groups

Use HAVING to filter groups

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

Results -

+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
Copy the code

WHERE doesn’t work because filtering is based on grouped aggregate values rather than specific row values.

-- List the suppliers with 2 or more products priced at 10 or more
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price > = 10
GROUP BY vend_id
HAVING COUNT(*) > =2

Results -

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
Copy the code

Grouping and sorting

Although GROUP BY and ORDER BY often do the same job, they are very different.

ORDER BY and GROUP BY

ORDER BY	GROUP BY
The output produced by sorting	Grouping. But the output may not be the order of the grouping
Any column can be used (even non-selected columns can be used)	Only select or expression columns can be used, and each select column expression must be used
Not necessarily	Must be used if columns (or expressions) are used with an aggregate function

When the GROUP BY clause is used, the ORDER BY clause should also be given. This is the only way to ensure that the data is sorted correctly. Never rely solely on GROUP BY sorting data.

Retrieves order numbers and total order prices for orders with total order prices greater than or equal to 50
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) > = 50
ORDER BY ordertital;
Copy the code

SELECT clause order

SELECT clause and its order

clause	instructions	Is it mandatory to use
SELECT	The column or expression to return	is
WHERE	A table from which data is retrieved	Used only when selecting data from a table
GROUP BY	Group show	All is used in calculating aggregation by group
HAVING	Group level filter	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 using subqueries

SELECT order_num FROM orderitems
WHERE prod_id = 'TNT2';

SELECT cust_id
FROM orders
WHERE order_num IN (20005.20007);
Copy the code

You can use the following methods

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

Add one more condition:

SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001.10004)
Copy the code

Merge into one SQL sentence

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'));
Copy the code

• Using a subquery in a WHERE clause should ensure that the SELECT statement has the same number of columns as in the WHERE clause.
• The code presented here works and gets the desired result. However, using subqueries is not always the most efficient way to perform this type of data retrieval. (that is, write execution this way is not necessarily the best performance)

Use subqueries as computed fields

# to customer10001To count the ordersSELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001; # to execute for each customerCOUNT(*) calculation, should beCOUNT(*) as a subquerySELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
Copy the code