MySQL Must Know must Know – Concepts Section
MySQL must know must – Install application section
The database files used below are available in themysql_scriptsTo find it.
Retrieve the data
Retrieve a single column, such as a column named PROD_NAME from the Products table. SELECT prod_name FROM products; Retrieve multiple columns. Note that column names must be separated by commas. Do not add a comma after the last column name, or an error will be reported. SELECT prod_id, prod_name, prod_price FROM products; Retrieve all columns. SELECT * FROM products; The DESTINCT keyword allows the command to return only different values. If the directive, there might be a total of 14 rows in the Products table, and now only a different (unique) vend_id row is returned, then only 4 rows might be returned. SELECT DISTINCT vend_id FROM products; LIMIT 5 means that no more than 5 rows will be returned. SELECT prod_name FROM products LIMIT 5; # LIMIT 5, 5 returns 5 rows starting at line 5. SELECT prod_name FROM products LIMIT 5, 5; Or use LIMIT 5, OFFSET 5, same result as above. SELECT prod_name FROM products LIMIT 5 OFFSET 5; # Notice that the number of rows returned starts at 0. So LIMIT 1, 1 will retrieve the second row, not the first. SELECT prod_name FROM products LIMIT 1,1;Copy the code
ORDER BY to retrieve data
When sorting is not used, the retrieved data is not displayed in a purely random order, but in the order in which it appears in the underlying table. This can be the order in which data is originally added to the table, but if the data is later updated or deleted, the order will be affected by MySQL reuse of reclaimed storage space. Therefore, this sort order cannot (and should not) be relied upon without explicit control.
According to relational database design theory, if the sorting order is not clearly specified, the order of retrieved data should not be assumed to be meaningful.
The ORDER BY clause sorts the data retrieved BY the SELECT statement. The ORDER BY clause takes one or more column names. Sort the output accordingly.
SELECT prod_name FROM products; SELECT prod_NAME FROM products ORDER BY prod_name; Order by multiple columns: prod_price is the first order, and prod_NAME is the only order if the same prod_price occurs. SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name; # set sort direction (default: ascending) SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC; Order multiple columns in descending order by prod_price with the most expensive at the top SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name; Use # ORDER BY and LIMIT to find the highest or lowest value in a column. SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;Copy the codeNote:
- The columns used in the ORDER BY clause do not have to be retrieved columns, and it is perfectly legal to sort with non-retrieved columns.
- If you want to sort in descending order on multiple columns, you must specify DESC for each column.
- ASC is an ascending sort. Ascending sort is the default, and DESC is not specified.
- The ORDER BY clause must come after the FROM clause, or if LIMIT is used, it must come after the ORDER BY clause.
Filtering data (WHERE)
Databases contain a lot of data, but we rarely need to retrieve all the rows in a table. Filtering criteria are specified to retrieve only the required data, and in the SELECT statement, the data is filtered according to the search criteria specified in the WHERE clause.
SELECT prod_name FROM products WHERE prod_price = 2.50; SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses'; # output + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | prod_name | prod_price | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | Fuses | | 3.42 + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + # to retrieve the SELECT vend_id vend_id is not equal to 1003, prod_name FROM products WHERE vend_id < > 1003; SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; SELECT CUST_id FROM customers WHERE CUST_email IS NULL;Copy the codeThe WHERE clause operator
| The operator | instructions |
|---|---|
| = | Is equal to the |
| <> | Is not equal to |
| ! = | Is not equal to |
| < | Less than |
| < = | Less than or equal to |
| > | Is greater than |
| > = | Greater than or equal to |
| BETWEEN | Between two specified values |
Note:
- Position of WHERE statements: When using both ORDER BY and WHERE clauses, you should place ORDER BY after WHERE, otherwise an error will occur.
- The condition used in the WHERE clause is quoted if a value is compared to a string type (such as a string), and not quoted when a value is compared to a numeric column.
- NULL no value, which is different from field 0, an empty string, or just Spaces.
Data filtering (AND, OR, IN)
MySQL allows multiple WHERE clauses to be combined. These clauses are used in two ways: as an AND clause OR as an OR clause.
SELECT * from vend_id where vend_id = 1003 AND prod_price is less than or equal to 10 prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10; SELECT prod_name from #### OR where vend_id = 1002 OR vend_id = 1003 prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003; # AND/OR Vend_id is 1003 and prod_price is greater than or equal to 10, and all rows vend_id is 1002. SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; # output + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | vend_id | prod_name | prod_price | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | 1002 | Fuses | 3.42 | | 1002 | Oil can 8.99 | | | 1003 | Detonator | | 13.00 10.00 | | 1003 | Bird seed | | 1003 | Safe 50.00 | | | 1003 | TNT (5 sticks) | | 10.00 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + # if you want to retrieve the vend_id is 1003 and prod_price and vend_id is greater than or equal to 10, 1002 and prod_price Lines greater than or equal to 10 need parentheses. SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; The ### IN operator specifies a range of conditions IN which each condition can be matched. The IN value is a comma-separated list enclosed IN parentheses. SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003); SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) SELECT vend_id FROM products WHERE vend_id NOT IN (1002, 1003);Copy the codePay attention to
- WHERE can contain any number of AND AND OR operators AND allow them to be combined for complex AND efficient filtering. However, THE SQL language processes the AND operator before the OR operator.
- Any time you use A WHERE clause with the AND AND OR operators, it is recommended that you use parentheses to group explicitly AND not rely too much on the default evaluation order.
- IN and OR have the same functionality, but the IN operator has the following advantages
- The syntax of the IN operator is clearer and more intuitive when there are too many filtered fields
- The IN operator generally executes faster than the OR operator
- The biggest advantage of IN is that it can include other SELECT statements, making WHERE clauses more dynamic.
- MySQL supports the negation of the IN, BETWEEN, and EXISTS clauses using NOT.
Filter by wildcard
Percent sign (%) wildcard
% indicates any number of occurrences of any character. The value can be 0, 1, or n times
SELECT prod_id, prod_name FROM products WHERE prod_name = 'jet%'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | JP1000 | JetPack 1000 | | JP2000 | JetPack 2000 | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # wildcards can be used anywhere in the search pattern, and can use multiple wildcards. SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | ANV01 | .5 ton anvil | | ANV02 | 1 ton anvil | | ANV03 | 2 ton anvil | +---------+--------------+Copy the codeUnderscore wildcard
Underscores can match only one character, not more or less.
SELECT prod_id, prod_name FROM products WHERE prod_name = '_ ton anvil'; +---------+-------------+ | prod_id | prod_name | +---------+-------------+ | ANV02 | 1 ton anvil | | ANV03 | 2 ton anvil | +---------+-------------+ SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | ANV01 | .5 ton anvil | | ANV02 | 1 ton Anvil | | ANV03 | 2 ton anvil | + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- less than percent + # # # the underline the wildcard mask a. 5Copy the codePay attention to
- Note that trailing whitespace, such as ‘%anvil’, will not match ‘anvil ‘because it is not easy to find a space after it. The solution is to append a % after it or use a function to remove the leading and trailing whitespace.
- % does not match NULL.
- Wildcard searches generally take longer to process than other searches, so don’t overuse wildcards and use other operators in favor of others if they serve the same purpose. When you do need to use wildcards, don’t use them at the beginning of a search pattern unless absolutely necessary.
Search with regular expressions
### Base character matching, the following statement retrieves all rows containing text 1000 in column prod_NAME. SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP BINARY '; # # # | OR matching use, can have more than two OR conditions, such as: '1000|2000|3000' SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | prod_name | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | JetPack 1000 | | 2000 | JetPack + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # to match one of the SELECT few characters Prod_name FROM products WHERE prod_name REGEXP '[1,2,3] Ton' ORDER BY prod_name; + -- -- -- -- -- -- -- -- -- -- -- -- -- + | prod_name | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 ton anvil | | 2 ton anvil | + -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # to pay attention to the difference between 1 | 2 | 3 ton, This means that we match 1,2 and 3 tons, Actually, [123] is the abbreviation of | 2 | 3 [1] the SELECT prod_name FROM products WHERE prod_name REGEXP '1 | 2 | 3 Ton ORDER BY prod_name; +---------------+ | prod_name | +---------------+ | 1 ton anvil | | 2 ton anvil | | JetPack 1000 | | JetPack 2000 | | TNT (1 stick) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # to match the special characters, \ escape the special characters SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | vend_name | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Furball Inc. | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # together four Numbers match the SELECT prod_name the FROM products WHERE prod_name REGEXP '[:digit:]{4}' ORDER BY prod_name; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+Copy the codeEnumerates metacharacters to escape and locate metacharacters
| metacharacters | instructions |
|---|---|
| \f | Change the page |
| \n | A newline |
| \r | enter |
| \t | TAB |
| \v | Vertical TAB |
| \ \ | The backslash |
| ^ | The beginning of a text |
| $ | End of text |
| [[:<:]](changed to \b after version 8) | The beginning of the term |
| [[:>:]](changed to \b after version 8) | The end of the term |
Most regular expression implementations use a single backslash to escape special characters so that the characters themselves can be used. But MySQL requires two backslashes (MySQL itself interprets one, the regular expression library interprets the other).
Enumerating character class
| class | instructions |
|---|---|
| [:alnum:] | Arbitrary characters and numbers (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 (ASCII 0 to 31 and 127) |
| [:digit:] | Any number (same as [0-9]) |
| [:xdigit:] | Any hexadecimal number (same as [A-FA-f0-9]) |
| [:lower:] | Any lowercase letter (same as [a-z]) |
| [:upper:] | Any capital letter (same as [a-z]) |
| [:print:] | Any printable character |
| [:graph:] | Same as [:print:], but without Spaces |
| [:punct:] | Any character that is neither in [:alnum:] nor [: CNTRL :] |
| [:space:] | Any whitespace character including space (same as [\f\n\r\t\v]) |
Simple regular expression tests
Use SELECT to test regular expressions without using database tables. The REGEXP check always returns 0 (no match) or 1 (match).
SELECT 'hello' REGEXP 'hello\\b';
+---------------------------+
| 'hello' REGEXP 'hello\\b' |
+---------------------------+| | + 1---------------------------+
Copy the code
Computed field
### concatenates names and countries, using Concat, Trim function, And AS keyword SELECT Concat(Trim(vend_name), '(', Trim(vend_country), ') AS vend_name FROM vendors ORDER BY vend_name; +------------------------+ | vend_name | +------------------------+ | ACME(USA) | | Anvils R Us(USA) | | Furball Inc.(USA) | | Jet Set(England) | | Jouets Et Ours(France) | | LT Supplies(USA) | +------------------------+ ### Trace all the items in order 20005, SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005; +---------+----------+------------+----------------+ | prod_id | quantity | item_price | expanded_price | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | ANV01 10 5.99 59.90 | | | | | ANV02 9.99 29.97 | | | 3 | | TNT2 | 5 10.00 50.00 | | | | 1 FB | | | | 10.00 + 10.00 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +Copy the codeTest calculation
SELECT provides a great way to test and experiment with functions and calculations by omitting the FROM clause
SELECT 3*2;
+-----+
| 3*2 |
+-----+
| 6 |
+-----+
SELECT Now();
+---------------------+
| Now() |
+---------------------+
| 2019-11-21 22:51:13 |
+---------------------+
Copy the code
Use data processing functions
Functions are not as portable as SQL, and almost every major DBMS implementation supports functions that are not supported by other implementations, sometimes with great differences. For the sake of code portability, many people discourage the use of specially implemented functionality, which, while beneficial, can have an impact on application performance. If you decide to use functions, be sure to comment your code.
Text processing function
| Number of letter | Said Ming |
|---|---|
| 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 |
SOUNDEX is an algorithm that converts any string of text into an alphanumeric pattern describing its phonetic representation. SOUNDEX takes into account similar sounding bytes and syllables, making it possible to compare strings rather than letters.
### search for Y Lee with y. Lie Because they sound like SELECT cust_name, CUST_contact FROM customers WHERE Soundex(CUST_contact) = Soundex(' Y.lie '); +-------------+--------------+ | cust_name | cust_contact | +-------------+--------------+ | Coyote Inc. | Y Lee | +-------------+--------------+Copy the codeDate and time handlers
| Number of letter | Said Ming |
|---|---|
| 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 manipulation function |
| Date_Format() | Returns a formatted date or time string |
| Year() | Returns the year portion of a date |
| Month() | Returns the month portion of a date |
| 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 |
| Second() | Returns the second portion of a time |
| Now() | Returns the current date and time |
SELECT cust_ID, ORDER_num FROM Orders WHERE order_date = '2005-09-01'; Order_date = 2005-09-01 11:30:05; order_date = 2005-09-01 11:30:05; SELECT CUST_ID, ORDER_num FROM Orders WHERE Date(order_date) = '2005-09-01'; SELECT CUST_ID, ORDER_num FROM Orders WHERE Date(order_date) = '2005-09-01'; If you want to retrieve all orders for September 2005, you have to remember how many days there are in a month. SELECT CUST_ID, order_num FROM Orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'; SELECT cust_id FROM Orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'; SELECT cust_id from Year() and Month(). order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;Copy the codePay attention to
- To use date filtering, the date must be YYYY-MM-DD to eliminate ambiguity, and the year should also use four digits to be more reliable.
Numerical processing function
| Number of letter | Said Ming |
|---|---|
| Abs() | Returns the absolute value of a number |
| Sin() | Returns the sine of an Angle |
| Cos() | Returns the cosine of an Angle |
| Tan() | Returns the tangent 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 |
| Sqrt() | Returns the square root of a number |
Summary data
| Number of letter | Said Ming |
|---|---|
| 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 |
SELECT AVG(prod_price) AS avg_price FROM products; + -- -- -- -- -- -- -- -- -- -- -- + | avg_price | + -- -- -- -- -- -- -- -- -- -- - + 16.133571 | | + -- -- -- -- -- -- -- -- -- -- - + # # # to check the total number of all the customers in the customers table SELECT COUNT (*) AS num_cust FROM customers; + -- -- -- -- -- -- -- -- -- -- + | num_cust | + -- -- -- -- -- -- -- -- -- -- + | | 5 + -- -- -- -- -- -- -- -- -- -- + # # # with E-mail address customer COUNT only SELECT COUNT (cust_email) AS num_cust the FROM customers; + -- -- -- -- -- -- -- -- -- -- + | num_cust | + -- -- -- -- -- -- -- -- -- -- + | 3 | + -- -- -- -- -- -- -- -- -- -- + # # # to calculate the total number of items the order number is 20005 SELECT SUM (quantity) AS items_ordered FROM orderitems WHERE order_num = 20005; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | items_ordered | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 19 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # multiple accumulation function combination 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; +-----------+-----------+-----------+-----------+ | num_items | price_min | price_max | price_avg | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 14 | | | | | 16.133571 55.00 2.50 +-----------+-----------+-----------+-----------+Copy the codeParameters ALL and DISTINCT
When a DISTINCT parameter is used, only rows with different values are counted. If you specify ALL or no parameter, ALL rows are counted.
### Look at how many suppliers are in the list, as it is possible that one supplier offers many products. SELECT vend_count (vend_id) AS vend_count FROM products; + -- -- -- -- -- -- -- -- -- -- -- -- + | vend_count | + -- -- -- -- -- -- -- -- -- -- -- -- + | | + 14 -- -- -- -- -- -- -- -- -- -- -- -- + # # # to heavy will know that there are 4 suppliers SELECT COUNT (DISTINCT vend_id) AS vend_count FROM products; +------------+ | vend_count | +------------+ | 4 | +------------+Copy the codePay attention to
- AVG() can only be used to determine the average value of a single specific numeric column, and the column name must be passed as a function parameter. To get the average value of multiple columns, you must use multiple AVG() functions.
- The AVG() function ignores rows with NULL column values.
- COUNT(*) counts the number of rows in a table, regardless of whether there are NULL or non-null values in the column.
- Using COUNT(column) to COUNT rows with values in a particular column ignores NULL values.
- The MAX() function ignores rows with a value of NULL (as does MIN()). It is generally used to find the largest numeric and date value, but can also be used for non-numeric data, such as returning the maximum value in a text column, where MAX() returns the last line (MIN () returns the first line).
- The SUM() function ignores rows with a value of NULL
- When representing the results of an aggregation function, you should not use the actual columns in the table, and it is better to specify an alias so that it is easy to understand and use.
Grouped data
The data packet
SELECT vend_id, prod_price FROM products GROUP BY vend_id, prod_price;
Copy the code- The GROUP BY clause can be followed BY more than one column.
- The column name in the SELECT clause must be a grouped column or column function (except for aggregate calculation statements), for example vend_id, prod_price, and the column retrieved after SELECT must be vend_id, prod_price.
- The column function returns one result for each GROUP defined BY the GROUP BY clause, for example, the maximum value of each GROUP.
- If there are NULL values in the grouping column, NULL is returned as a grouping, and if there are multiple rows of NULL values in the column, they are grouped.
- The GROUP BY clause must follow the WHERE clause and precede the ORDER BY clause.
Packet filtering
SELECT cust_ID, COUNT(*) AS Orders FROM Orders GROUP BY CUST_id HAVING COUNT(*) >= 2; SELECT vend_id, vend_id, vend_id, vend_id, vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2; ### list all orders whose total order price is 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 ordertotal; + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | order_num | ordertotal | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | 20006 | | 55.00 125.00 | | 20008 | | 1000.00 149.87 | 20005 | | 20007 | | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code- HAVING is similar to WHERE, but WHERE filters before groups and HAVING after groups.
SELECT clause order
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT
Front- end-Basics: Watch, Star, Front-End-Basics: Front-End-Basics
MySql must know must know this article