Mysql review notes - Basic chapter * 3 [common additions, deletions and modifications]
Statement: there will be no problems in this note, such as the development history of Mysql. Most of them are practical commands and necessary knowledge in use. Please forgive me
In this document, we will not review the query. We will put the query operation part into the following query document. We will review the cascade query, sub query, link query and so on
Insert into
Overview: use INSERT statement to INSERT data into table
$ mysql> CREATE TABLE Customers $ -> ( $ -> cust_id char(10) NOT NULL , $ -> cust_name char(50) NOT NULL , $ -> cust_address char(50) NULL , $ -> cust_city char(50) NULL , $ -> cust_state char(5) NULL , $ -> cust_zip char(10) NULL , $ -> cust_country char(50) NULL , $ -> cust_contact char(50) NULL , $ -> cust_email char(255) NULL $ -> ); $ Query OK, 0 rows affected (0.06 sec)
Insert complete row
Writing SQL statements that depend on a specific column order can sometimes make mistakes, but it is convenient to write.
Develop the habit of inserting data in the specified order. Although it is cumbersome to write, it is not easy to make mistakes. Note that a value must be provided for each column.
Explanation: insert a new customer into the Customers table and store the data in each table column. A value must be provided for each table. If a column has no value, NULL value should be used.
$ mysql> INSERT INTO Customers $ -> VALUES('1000000006', $ -> 'Toy Land', $ -> '123 Any Street', $ -> 'New York', $ -> 'NY', $ -> '11111', $ -> 'USA', $ -> NULL, $ -> NULL); $ Query OK, 1 row affected (0.01 sec)
Insert partial row
Specify that a column provides a value and others do not
Insert partial values if the table allows:
-
Change column definition to allow NULL values
-
Change the table to the default value. If not, the default value will be used
If there are no two conditions of the day before yesterday, the service inserts some values.
Explanation: ignore cust in the table_ Contact and cust_email value
$ mysql> INSERT INTO Customers(cust_id, $ -> cust_name, $ -> cust_address, $ -> cust_city, $ -> cust_state, $ -> cust_zip, $ -> cust_country) $ -> VALUES('1000000008', $ -> 'Toy Land', $ -> '123 Any Street', $ -> 'New York', $ -> 'NY', $ -> '11111', $ -> 'USA'); $ Query OK, 1 row affected (0.01 sec)
Insert retrieved data
Insert the output result of the SELECT statement into the table, and INSERT SELECT combines the two.
Explanation: import all data of CustomersNew into Customers
$ mysql> CREATE TABLE CustomersNew $ -> ( $ -> cust_id char(10) NOT NULL , $ -> cust_name char(50) NOT NULL , $ -> cust_address char(50) NULL , $ -> cust_city char(50) NULL , $ -> cust_state char(5) NULL , $ -> cust_zip char(10) NULL , $ -> cust_country char(50) NULL , $ -> cust_contact char(50) NULL , $ -> cust_email char(255) NULL $ -> ); $ Query OK, 0 rows affected (0.06 sec) $ mysql> INSERT INTO CustomersNew(cust_id, $ -> cust_contact, $ -> cust_email, $ -> cust_name, $ -> cust_address, $ -> cust_city, $ -> cust_state, $ -> cust_zip, $ -> cust_country) $ -> SELECT cust_id, $ -> cust_contact, $ -> cust_email, $ -> cust_name, $ -> cust_address, $ -> cust_city, $ -> cust_state, $ -> cust_zip, $ -> cust_country $ -> FROM Customers; $ Query OK, 1 row affected (0.01 sec)
Copy from one table to another
Copy only the table structure, not the data.
$ mysql> CREATE TABLE Customers_New like Customers; $ Query OK, 0 rows affected (0.03 sec)
Create a CustCopy table and copy the data from the Customers table.
$ mysql> CREATE TABLE CustCopy AS $ -> SELECT * $ -> FROM Customers; $ Query OK, 8 rows affected (0.03 sec) $ Records: 8 Duplicates: 0 Warnings: 0
See if a table was created
$ mysql> SHOW CREATE TABLE Customers
View table structure
$ mysql> DESC Customers
be careful:
-
Any SELECT clause can be used. Where, group by, etc
-
You can use links to insert data from multiple tables
-
However, the data retrieved from many tables can only be inserted into one table.
SELECT INTO can be used to copy a table for testing before testing SQL statements to avoid affecting the original table.
Delete data
Here we have summarized the basic contents of insert into. Before deleting and modifying, we need to talk about a specification rule and the principles of updating and deleting:
- Both UPDATE and DELETE statements have a WHERE clause. If the WHERE clause is ignored, it will be applied to all rows, so unless all rows are updated
- Ensure that each table has a primary key, and WHERE is applied to the primary key.
- Test SELECT before using UPDATE and DELETE statements to ensure that the written WHERE clause is correct.
- Use to enforce referential integrity database to prevent accidental deletion of rows.
- Now MYSQL does not execute UPDATE or DELETE clauses with WHERE clauses.
Let's start with a functional overview
Use DELETE
-
Delete data from the database,
-
Delete a specific row from the table delete all rows from the table
$ mysql> DELETE FROM Customers WHERE cust_id = '1000000009';# Specify to delete the data with data id 1 in the table Customers $ Query OK, 1 row affected (0.01 sec)
Explanation: specifies to delete the data in the table Customers
$ mysql> DELETE FROM Customers;# Specify to delete all data in the table Customers. This is not recommended. Be careful and think twice before deleting $ Query OK, 1 row affected (0.01 sec)
-
Generally, our business logic generally uses logical deletion (and adding deletion flag). Instead of physical deletion, we can set one of the fields to whether it is valid. Let's give an example here
-- auto-generated definition create table movies ( Movie_id int auto_increment comment 'type;type' primary key, Movie_name varchar(255) not null comment 'Movie name;Movie name', Movie_Name_second varchar(255) not null comment 'English name;English name', Movie_director varchar(255) not null comment 'director;director', Movie_starring varchar(255) not null comment 'to star;to star', Movie_price decimal(24, 2) not null comment 'Ticket Price;Ticket Price', Movie_Is_show tinyint(1) default 0 not null comment 'Whether to sell', Movie_create datetime not null comment 'Creation time' ) comment 'movies_Table;Movie information table';
Explanation: movie of our data model_ Is_ Show can be understood as our delete tag
be careful:
- The DELETE statement deletes rows, but cannot DELETE the table itself
- To delete all rows in a table, use the TRUNCATE TABLE statement
Modify data
Use UPDATE and DELETE statements to manipulate table data.
UPDATE is used to UPDATE and modify the data in the table
-
Update specific rows in the table
update user_copy set user_copy.User_Address = '3441292862@qq.com';
Explanation: we put the user_ User of copy database_ Change the value of the address data table field to 3441292862@qq.com
-
Update all rows in the table
Note: if the WHERE clause is omitted, all rows are updated.
update user_copy set user_copy.User_Address = '3441292862@qq.com' where user_copy.User_id=1;
Explanation: we put the user_ User of copy database_ Address data table field and user_ User ID of record with value 1_ Change the field value of address to 3441292862@qq.com
The UPDATE statement has a combination of three parts
- Table to update
- Column names and their new values
- Determine which rows of filter criteria to update
Update multiple values in a column
$ mysql> UPDATE Customers SET cust_contact = 'Sam Roberts',cust_email = 'sam@toyland.com' WHERE cust_id = '1000000006'; $ Query OK, 1 row affected (0.00 sec) $ Rows matched: 1 Changed: 1 Warnings: 0
Explanation: using the SET command, each 'column = value' is separated by commas to distinguish multiple columns.
When updating a column NULL, you can set the column to NULL if the table allows NULL
$ mysql> UPDATE Customers SET cust_contact = NULL,cust_email = 'sam@toyland.com' WHERE cust_id = '1000000006'; $ Query OK, 1 row affected (0.00 sec) $ Rows matched: 1 Changed: 1 Warnings: 0
Retrieve data
It is the most commonly used SQL statement to index one or more table information.
- Keywords
- For fields that are part of SQL, keywords cannot be used as the names of tables or columns.
Using SELECT to index data, you must give at least two pieces of information. What do you want? From where
Check individual columns
SELECT prod_name FROM Products;
Explanation: use the SELECT statement to retrieve a product named prod FROM the Products table_ Name column, FROM keyword FROM the specified label index.
Output results
SELECT prod_name FROM Products;
+---------------------+ | prod_name | +---------------------+ | Fish bean bag toy | | Bird bean bag toy | | Rabbit bean bag toy | | 8 inch teddy bear | | 12 inch teddy bear | | 18 inch teddy bear | | Raggedy Ann | | King doll | | Queen doll | +---------------------+ 9 rows in set (0.00 sec)
- How well SQL statements are divided, easy to read and debug,
- If the statement is long, the SQL statement must end with (;).
- SQL statements are not case sensitive. Except for table names and values, SQL keywords are capitalized for easy reading
Index multiple columns
Compared with single index column, the only difference is that multiple column names must be given after the SELECT keyword. The column names are directly separated by, and the last column is not required.
SELECT prod_id, prod_name, prod_price FROM Products;
Explanation: use SELECT to SELECT data from the table Products, and specify three column names, prod_id, prod_name, prod_price
Output:
+---------+---------------------+------------+ | prod_id | prod_name | prod_price | +---------+---------------------+------------+ | BNBG01 | Fish bean bag toy | 3.49 | | BNBG02 | Bird bean bag toy | 3.49 | | BNBG03 | Rabbit bean bag toy | 3.49 | | BR01 | 8 inch teddy bear | 5.99 | | BR02 | 12 inch teddy bear | 8.99 | | BR03 | 18 inch teddy bear | 11.99 | | RGAN01 | Raggedy Ann | 4.99 | | RYL01 | King doll | 9.49 | | RYL02 | Queen doll | 9.49 | +---------+---------------------+------------+ 9 rows in set (0.01 sec)
Retrieve all columns
SELECT * FROM Products;
Use the wildcard * to return all the columns in the table
+---------+---------+---------------------+------------+--------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc| | BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it | | BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included | | BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots | | BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket | | BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket | | BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket | | RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll | | RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown | | RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown | +---------+---------+---------------------+------------+-----------------------+ 9 rows in set (0.00 sec)
Do not use the * wildcard unless each column in the table is required or the column is not explicitly specified.
Advanced data filtering
Operator
It is used to change the clause keyword in the WHERE clause, which is also a logical operator.
AND operator
Attach conditions to the WHERE clause by using AND.
Let's take an example
Ask for the names and prices of all products manufactured by supplier DLL01 with a price less than or equal to US $4.
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
Explanation: the clause WHERE in the select statement contains two conditions. If the supplier specifies DLL01 and the price is higher than US $4, it will not be displayed. If the price is less than US $4, neither will the term DELL01.
OR operator
Retrieve matches any criteria.
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
Explanation: index the product names and prices of all products of the supplier, and match any condition DLL01 or BRS01
Calculation order
WHERE runs AND in combination with RO for complex operation AND advanced filtration.
Search for more than $10 and made by DLL10 or BRSO1.
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;
The returned price is less than $10. The reason is that AND has priority. SQL processes AND before processing OR, AND directly retrieves BRS01 OR DLL01, ignoring the price.
The solution is to group with parentheses.
SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
() parentheses have a higher operation calculation order than AND, RO.
Note: WHERE sentences using AND and OR operations should be clearly grouped in parentheses.
NOT operator
The NOT operator is always used in conjunction with other operators before the filter.
SELECT vend_id, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
Explanation: list all products without DLL01.
IN operator
The IN operator is used to specify the range, and each item IN the range is matched. The IN value rule is separated by commas and placed IN parentheses.
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01' ORDER BY prod_name;
Explanation: use SELECT to retrieve all products manufactured by DLL01 and BRS01. The IN operator is followed by a comma separated list of legal values.
IN is equivalent to completing the same function as OR. The following results are the same as the above output results
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name
Advantages of using IN:
- The syntax is clear, especially when the syntax is long, there are few operators
- The calculation sequence is easy to manage
- IN performs faster than OR
- The biggest advantage is that it can include other SELECT statements and can more dynamically establish WHERE clauses.
Fuzzy query LIKE and REGEXP operators
When you need to search all products with a specific keyword in the product text, use wildcards to create a more specific data search pattern.
- A wildcard is used to match some special characters of a value.
- A search pattern is a combination of letter values and wildcards.
Wildcards are characters with special meanings in the WHERE clause of SQL. If wildcards are used in the clause, the LIKE operator must be used.
Percent sign% wildcard
Indicates that any match occurs any number of times. Multi character comparison
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
Explanation: retrieve words beginning with Fish. Any words after Fish are case sensitive.
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
Explanation: check the words containing bean bag. Any words after Fish are case sensitive.
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%Fish';
Explanation: retrieve words ending with Fish. Any words after Fish are case sensitive.
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'F%y';
Explanation: match all products beginning with F and ending with y
Underline_ wildcard
Underscores differ from% in that they match a single character, not multiple characters.
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '_ inch teddy bear';
Explanation: a_ Match a string.
Square brackets [] wildcards
select count(*) from movies.movies where Movie_name regexp '[a,b]';
A column that matches any string with JM.
It has the same function as the following statement
select count(*) from movies.movies where Movie_name like '%a%' or Movie_name like '%b%';
Wildcard skills
- If other operations can achieve the same effect, do not use wildcards.
- Use wildcards to narrow the search scope as much as possible.
- Location of primary wildcards
Create calculated field
The data stored in the database table is generally not in the format required by the application, for example:
- Two messages are displayed, but not in one table
- Different columns, but the program needs to retrieve them as a formatted field
- The column data is mixed in size, but the program needs to capitalize all the data. The price and quantity of items are stored in the item order table, but the total price of items is not stored. When printing, the total price of items is required. Calculate the total number and average according to the data in the table. In the above cases, we need to convert from the database, calculate and format, rather than index it for calculation.
Calculated fields are created within a runtime SELECT statement.
Field
Similar to the meaning of column, it is often used in mutual conversion. Fields are usually used in the link of calculated fields.
Converting and formatting in SQL is faster than in client applications.
Concatenate field
The vendors table contains the supplier id and location information. Now you need to generate a supplier table. You need to format the name and list the supplier location. This report requires a single value, and the data in the table is stored in, vend_name and vend_ In the country, you also need to create a vend_country is enclosed.
-
Join values together to create a single value.
In SQL, a special envoy operator is used to splice two columns. The + operator is represented by a plus sign (+) and two vertical bars (|).
In mysql, use the CONCAT() function to link the item tables, AND | is equal to the operator OR AND & & is equal to the AND operator.
The following is the result of execution in mysql
select concat('name is',movies.movies.Movie_name,',director is', movies.movies.Movie_director) from movies.movies;
Alias (as)
The spliced address field does not have a name and cannot be applied to the client, so it needs a field alias, another name is derived column
Aliases can be assigned with the AS keyword.
select concat('name is',movies.movies.Movie_name,',director is', movies.movies.Movie_director)as movies_info from movies.movies;
Explanation: use as movie in this way_ Name specifies the column name of the splicing output result,
Another use of aliases is column renaming
select movies.movies.Movie_director as movies_info from movies.movies;
Put vend_ Rename the name output to movies_info.
Perform arithmetic calculations
Used to retrieve data for calculation.
Retrieve all Orders received in the Orders table, and the OrderItems table contains items in each order,
select movies.orders.Orders_price*10000 as ones from movies.orders;
Explanation: query orders_ Result of price field value * 10000
Supported operation characters + add, - subtract, * multiply, divide/
Using data processing functions
Function mainly provides convenience for data processing and conversion.
Most SQL implemented functions
- Used to process text strings (delete, recharge, case conversion)
- It is used to perform arithmetic (return absolute value, algebraic operation) operations on numerical data.
- Used to process date time values and extract specific components from these values.
- Returns the special information (user login information) that the DBMS is using.
String function
Function name | Description | Example |
---|---|---|
SUBSTRING | String interception | Select substring('1234567 ', 4,2) Returns:' 45 ' |
CONCAT | String splicing | Select concat('JBNS', 'My Jbns') Returns: JBNSMy Jbns |
LENGTH | Returns the length of the string passed to it | Select length('mySQL course ') return: 11 |
INSTR(str,substr) | Returns the first occurrence of a substring of the string str | **SELECT INSTR(‘foobarbar’, ‘bar’);** Return: 4 |
LTRIM/RTRIM/TRIM | Clear left / right / left + right spaces | SELECT LTRIM ('zhou Zhiyu ') Returns: Zhou Zhiyu (the following space is reserved) |
UPPER/ LOWER | Convert to uppercase/ | SELECT upper ('ssss') Returns: SSSS |
RIGHT | Returns a specified number of characters from the right side of a string | SELECT RIGHT('tuersong ', 3) return: tuersong |
REPLACE | Replace characters in a string | SELECT REPLACE('mo Leche. Yang Ke ',' Ke ',' LAN ') Returns: Mo Leche. Yang Lan |
Date function
Function name | Description | Example |
---|---|---|
NOW | Gets the current system time | SELECT NOW() returns: '2016-09-15 18:30:51' |
CURDATE | Gets the current system date | SELECT CURDATE(); Return: 'September 15, 2016' |
DATE_ADD | Date plus subsequent time | SELECT DATE_ADD('1998-01-02 ', INTERVAL 31 DAY) Returns:' 1998-02-02 ' |
DATE_SUB | Time before minus time after | SELECT DATE_SUB('1998-01-02', '1998-01-03') |
DATEDIFF | The interval between two dates for the specified date portion | SELECT DATEDIFF(‘1997-12-31 23:59:59’,‘1997-12-30’); Return: 1 |
DATE_FORMAT | Date formatted output | (recommended) 24-hour system SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%H:%i:%s’); Back: '22:23:00' (not recommended) twelve hour system SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%h:%i:%s’); Back: '11:23:00' (not recommended) date display format - SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%y:%m:%d’); Return: '97-10-04' (recommended) date display format 2: SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%H:%i:%s’); Return: '1997-10-04 |
Mathematical function
Function name | Description | Example |
---|---|---|
RAND | Returns a random float value from 0 to 1 | SELECT RAND() return: 0.79288062146374 |
ABS | Take the absolute value of the numeric expression | SELECT ABS(-43) return: 43 |
CEILING/CEIL | Take the smallest integer greater than or equal to the specified value and expression | SELECT CEILING(43.5) return: 44 |
FLOOR | Gets the maximum integer less than or equal to the specified expression | SELECT FLOOR(43.5) return: 43 |
POWER | Take the power of the numerical expression | SELECT POWER(5,2) Returns: 25 |
ROUND | Rounds a numeric expression to the specified precision | SELECT ROUND(43.543,1) return: 43.500 |
SIGN | Returns + 1 for positive numbers, - 1 for negative numbers, and 0 for 0 | SELECT SIGN(-43) Returns: - 1 |
MOD | Remainder | SELECT MOD(8, 3) Returns: 2 |
System function
FORMAT | Format output | **SELECT FORMAT(12332.1,4);** Return: '12332.1000' |
---|---|---|
CURRENT_USER | Returns the name of the current user | SELECT CURRENT_USER returns: the user name you logged in to |
CAST(value as type) | Gets a value of one type and produces a value of another type | CAST('234 'as unsigned) returns an integer |
CONVERT(value, type) | CONVERT('234 ', unsigned) returns an integer | CONVERT('234',unsigned) |
Return: integer |
More function content
Aggregate function
- Determines the number of rows in the table
- Gets the sum of the row groups in the table
- Find the maximum, minimum and average values of table columns (all rows, specific rows).
The above example needs to summarize the data in the table rather than the actual data itself, so there is no need to return time data and waste resources
Mysql common aggregate functions
function | effect |
---|---|
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 a column |
A function that runs on a row group and calculates and returns a single value.
- AVG(): returns the average value of all columns or a column.
Calculate the number of rows in the table and calculate the sum of specific column values to obtain the average value of the changed column.
mysql> SELECT AVG(prod_price) AS avg_price -> FROM Products; +-----------+ | avg_price | +-----------+ | 6.823333 | +-----------+ 1 row in set (0.01 sec)
Explanation: calculate the average price of all Products in the Products table.
Calculate the average value of a specific row,
mysql> SELECT AVG(prod_price) AS avg_price -> FROM Products -> WHERE vend_id = 'DLL01'; +-----------+ | avg_price | +-----------+ | 3.865000 | +-----------+ 1 row in set (0.01 sec)
Explanation: the WHERE clause filters out the average value of DELL01 and returns the average value of the supplier's products.
-
The COUNT() function calculates the number of rows in the table or the number of culverts that meet certain conditions.
-
Ignore the null and non null values contained in the table column, and calculate the number in the table.
-
Use COUNT(column) to calculate rows with values in a specific column, ignoring NULL values.
mysql> SELECT COUNT(*) AS num_cust -> FROM Customers; +----------+ | num_cust | +----------+ | 5 | +----------+ 1 row in set (0.01 sec)
-
Returns the total number of customers in custoemrs, regardless of the value of each column in the row.
mysql> SELECT COUNT(cust_email) AS num_cust -> FROM Customers; +----------+ | num_cust | +----------+ | 3 | +----------+ 1 row in set (0.00 sec)
Value returns the count of customers with email addresses. The result is 3, indicating that only 3 customers have email addresses.
MAX() returns the largest value in the specified column
mysql> SELECT MAX(prod_price) AS max_price -> FROM Products; +-----------+ | max_price | +-----------+ | 11.99 | +-----------+ 1 row in set (0.01 sec)
Explanation: returns the price of the most expensive item in the Products table.
-
MIN() returns the minimum value
mysql> SELECT MIN(prod_price) AS min_price -> FROM Products; +-----------+ | min_price | +-----------+ | 3.49 | +-----------+ 1 row in set (0.00 sec)
-
SUM() returns the sum of the specified column values
mysql> SELECT SUM(quantity) AS items_ordered -> FROM OrderItems -> WHERE order_num = 20005; +---------------+ | items_ordered | +---------------+ | 200 | +---------------+ 1 row in set (0.01 sec)
Explanation: returns the sum of the calculated quantity values. The WHERE clause limits the value of an order.
Combine calculated values with SUM()
mysql> SELECT SUM(item_price*quantity) AS total_price -> FROM OrderItems -> WHERE order_num = 20005; +-------------+ | total_price | +-------------+ | 1648.00 | +-------------+ 1 row in set (0.01 sec)
Explanation: total all order items_ The total number of price multiplied by the sum of quantity. WHERE clause an order item.
Aggregate different values
-
Performs calculations on ALL rows, specifying the ALL parameter or not changing the parameter (the default is the ALL behavior)
-
Contains only different values. Specify the DISTINCT parameter
mysql> SELECT AVG(DISTINCT prod_price) AS avg_price -> FROM Products -> WHERE vend_id = 'DLL01'; +-----------+ | avg_price | +-----------+ | 4.240000 | +-----------+ 1 row in set (0.02 sec)
Explanation: unlike the previous example, prod is excluded_ For the same value in price, only different values are calculated. The number is less, so the average value is higher.
Combined aggregate function
Use SELECT to combine aggregate functions.
mysql> 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 | +-----------+-----------+-----------+-----------+ | 9 | 3.49 | 11.99 | 6.823333 | +-----------+-----------+-----------+-----------+ 1 row in set (0.00 sec)
- Aggregate functions are used to aggregate data. SQL supports five aggregate functions, and the calculation speed is much faster than that on the client.
Grouping data
Use the GROUP BY and HAVING clauses to group data to summarize the table content subset.
Create group
Grouping is established in the GROUP BY clause of the SELECT statement.
mysql> SELECT vend_id, COUNT(*) AS num_prods -> FROM Products -> GROUP BY vend_id; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | BRS01 | 3 | | DLL01 | 4 | | FNG01 | 2 | +---------+-----------+ 3 rows in set (0.01 sec)
Explanation: the SELECT statement specifies two columns, vend_ The ID contains the supplier ID, which is num_prods calculates the field result, and the GROUP BY clause indicates vend_id sorts and groups data,
Important rules of GROUP BY clause:
- Contains any number of columns,
- If a group is nested in the GROUP BY clause, the data will be summarized on the last specified group.
- All columns listed in the GROUP BY clause must be retrieved columns. Valid expressions cannot aggregate functions.
- Most SQL do not allow GROUP BY with variable length data types (text, comment fields)
- Except for the aggregate calculation statement, each column in the SELECT statement must be given in the GROUP BY clause.
- If a group has a NULL value, it will be returned as a group. If more than one group, it will be grouped.
- The GROUP BY clause must appear after the WHERE clause,
Filter grouping
Filtering grouping specifies which groups to include and which groups to exclude. The HAVING clause is similar to the WHERE clause. The only difference is that WHERE is used to filter rows and HAVING is used to filter grouping. It can also be said that HAVING filters after data grouping and WHERE filters before data grouping.
HAVING supports all WHERE operators.
mysql> SELECT cust_id, COUNT(*) AS orders -> FROM Orders -> GROUP BY cust_id -> HAVING COUNT(*) >= 2; +------------+--------+ | cust_id | orders | +------------+--------+ | 1000000001 | 2 | +------------+--------+ 1 row in set (0.00 sec)
Explanation: filter the grouping of more than two orders
WHERE is used in conjunction with the HAVING clause
mysql> SELECT vend_id, COUNT(*) AS num_prods -> FROM Products -> WHERE prod_price >= 4 -> GROUP BY vend_id -> HAVING COUNT(*) >= 2; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | BRS01 | 3 | | FNG01 | 2 | +---------+-----------+ 2 rows in set (0.00 sec)
Explanation: the first line uses the aggregate function, and the WHERE clause filters out all prods_ For lines with price less than 4, press vend_id group, HAVING clause filters groups with a count of more than 2.
Remove WHERE filter
mysql> SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id HAVING COUNT(*) >= 2; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | BRS01 | 3 | | DLL01 | 4 | | FNG01 | 2 | +---------+-----------+ 3 rows in set (0.01 sec)
Filter out 4 products sold and the price is below 4.
Grouping and sorting
Difference between GROUP BY and ORDER BY
- GROUP BY
- Output from sorting
- Any column can be used
- You can choose whether to use with aggregate functions
- ORDER BY
- Grouped rows, the output may not be grouped sequentially
- Only selection columns or expressions can be used, and each Lister expression must be used
- If used with an aggregate function, you must use
Note: there is no need to rely on GROUP BY sorting. When GROUP BY should be used, the ORDER BY clause should also be used.
Retrieve the order number and number of ordered items except 3 or more items:
mysql> SELECT order_num, COUNT(*) AS items -> FROM OrderItems -> GROUP BY order_num -> HAVING COUNT(*) >= 3; +-----------+-------+ | order_num | items | +-----------+-------+ | 20006 | 3 | | 20007 | 5 | | 20008 | 5 | | 20009 | 3 | +-----------+-------+ 4 rows in set (0.00 sec)
Sort the output by the number of items ordered.
mysql> SELECT order_num, COUNT(*) AS items -> FROM OrderItems -> GROUP BY order_num -> HAVING COUNT(*) >=3 -> ORDER BY items, order_num; +-----------+-------+ | order_num | items | +-----------+-------+ | 20006 | 3 | | 20009 | 3 | | 20007 | 5 | | 20008 | 5 | +-----------+-------+ 4 rows in set (0.00 sec)
Explanation: the GROUP BY clause is used to group data. The COUNT(*) function returns the number of items in the order. The HAVING clause filters the data and returns 3 or more item orders. ORDER BY is the last sort output.
SELECT clause order
clause | explain | Must I use |
---|---|---|
SELECT | The column or expression to return | yes |
FROM | Retrieve data from | Used only when selecting data from a table |
WHERE | Row level filtering | no |
GROUP BY | Group description | Aggregate usage by group only |
HAVING | Group level filtering | no |
ORDER BY | Output sort order | no |
Please look forward to more queries------