Mysql review notes - Basic Chapter 3 [common additions, deletions and modifications]

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

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------

Tags: Database MySQL

Posted on Fri, 12 Nov 2021 09:55:47 -0500 by StealthRider

Programmer Help

Mysql review notes - Basic Chapter 3 [common additions, deletions and modifications]