MySQL MAX, MIN Functions

In MySQL, the MAX and MIN functions are used to return the maximum and minimum values, respectively, from a column or expression in a table. Here's how these functions work:

MAX function: The MAX function returns the maximum value from a column or expression. 

SELECT MAX(price) AS max_price FROM Stores;

Query will return a single row with a single column "max_price" that contains the highest value found in the "price" column of the "Stores" table.

MIN function: The MIN function returns the minimum value from a column or expression. 

SELECT MIN(price) AS min_price FROM Stores;

This query will return a single row with a single column "min_price" that contains the lowest value found in the "price" column of the "Stores" table. 

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

MAX function example:

mysql> SELECT MAX(price) AS MostExpensive FROM Stores;
+---------------+
| MostExpensive |
+---------------+
|           120 |
+---------------+
1 row in set (0.00 sec)

mysql>

Our query selects the maximum value of the "price" column in the "Stores" table and assigns it an alias "MostExpensive". The MAX function is used to calculate the maximum value of the "price" column.

The result is a single row with a single column "MostExpensive" which contains the highest value found in the "price" column of the "Stores" table. The value returned in this case is 120, which means that the highest value found in the "price" column of the "Stores" table is 120. 

MIN function example:

mysql> SELECT MIN(price) AS CheapestOne FROM Stores;
+-------------+
| CheapestOne |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

mysql>

Here we selects the minimum value of the "price" column in the "Stores" table and assigns it an alias "CheapestOne". The MIN function is used to calculate the minimum value of the "price" column.

MAX and WHERE combination:

mysql> SELECT MAX(price) AS ExpensivePSU FROM Stores WHERE product='Gold PSU';
+--------------+
| ExpensivePSU |
+--------------+
|          100 |
+--------------+
1 row in set (0.00 sec)

mysql>

The result is a single row with a single column "ExpensivePSU" which contains the highest value found in the "price" column of the "Stores" table where the "product" column is "Gold PSU". 

The value returned in this case is 100, which means that the highest value found in the "price" column of the "Stores" table where the "product" column is "Gold PSU" is 100. 

Also, we can search for a substring:

mysql> SELECT MAX(price) AS MaxPriceCables FROM Stores WHERE product LIKE "%Cable%";
+----------------+
| MaxPriceCables |
+----------------+
|             25 |
+----------------+
1 row in set (0.00 sec)

mysql>

This MySQL query selects the maximum value of the "price" column in the "Stores" table where the "product" column contains the word "Cable". 

MySQL COUNT Function

The MySQL COUNT function is a built-in aggregate function used to count the number of rows in a table that meet a specified condition. 

The syntax for the COUNT function is: 

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Here, column_name is the name of the column you want to count the number of rows for. If you want to count all the rows in the table, you can use the asterisk symbol (*) instead of the column name.

The table_name is the name of the table you want to count rows from.

The condition is an optional parameter that specifies a condition that must be met for the rows to be counted.

For example, to count the number of rows in a table called "employees" where the employee's age is greater than 30, you can use the following query: 

SELECT COUNT(*)
FROM employees
WHERE age > 30;

If you only want to count the number of rows where the age is greater than 30 and the department is "Sales," you can modify the query as follows: 

SELECT COUNT(*)
FROM employees
WHERE age > 30 AND department = 'Sales';

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

COUNT(*) example:

mysql> SELECT COUNT(*) AS NumOfRows FROM Stores;
+-----------+
| NumOfRows |
+-----------+
|         8 |
+-----------+
1 row in set (0.04 sec)

mysql>

The result of the query is a single row with a single column "NumOfRows" which contains the total number of rows in the "Stores" table. In this case, the result is 8.

mysql> SELECT COUNT(s_id) AS NumOfRows FROM Stores;
+-----------+
| NumOfRows |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)

mysql>

Our instruction selects the total number of non-null values in the "s_id" column of the "Stores" table and assigns it an alias "NumOfRows". 

Since all rows in the "Stores" table have a non-null value in the "s_id" column, the result of the query is the same as the previous query, i.e., a single row with a single column "NumOfRows" which contains the total number of rows in the "Stores" table.

COUNT(*) and WHERE combination: 

mysql> SELECT COUNT(*) AS GoldReport FROM Stores WHERE product LIKE "%ol%";
+------------+
| GoldReport |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql>

Query selects the total number of rows in the "Stores" table where the "product" column contains the string "ol" (with any characters before or after "ol"), and assigns it an alias "GoldReport". The LIKE operator with the "%" wildcard is used to match any string that contains "ol" (with any characters before or after it).

COUNT(*), WHERE and LIKE combination:

mysql> SELECT COUNT(*) AS ZeroReport FROM Stores WHERE price LIKE "%0%";
+------------+
| ZeroReport |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql>

This MySQL query selects the total number of rows in the "Stores" table where the "price" column contains the number "0" (with any characters before or after "0"), and assigns it an alias "ZeroReport". 

The LIKE operator with the "%" wildcard is used to match any string that contains "0" (with any characters before or after it).

NOT Like example:

mysql> SELECT COUNT(*) AS ZeroReport FROM Stores WHERE price NOT LIKE "%0%";
+------------+
| ZeroReport |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql>

The NOT LIKE operator with the "%" wildcard is used to match any string that does not contain "0" (with any characters before or after it).

The result is 4, which means that there are 4 rows in the "Stores" table where the "price" column does not contain the number "0" (with any characters before or after "0").

MySQL AVG Function

The AVG() function in MySQL is used to calculate the average value of a given set of values. 

The function takes a single argument, which is the column or expression to be averaged, and returns the average as a decimal value. 

Our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

AVG example:

mysql> SELECT AVG(price) AS AvgPrice FROM Stores;
+----------+
| AvgPrice |
+----------+
|  56.2500 |
+----------+
1 row in set (0.05 sec)

mysql>

Very simple.

MySQL Tutorial - UNION & UNION ALL

UNION and UNION ALL are used to combine the results of two or more SELECT statements into a single result set. 

The main difference between the two is that UNION eliminates duplicates in the result set, whereas UNION ALL does not.

What is in our table:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
6 rows in set (0.00 sec)

mysql>

Table structure:

mysql> DESCRIBE Documents;
+-----------------+------+------+-----+---------+----------------+
| Field           | Type | Null | Key | Default | Extra          |
+-----------------+------+------+-----+---------+----------------+
| d_id            | int  | NO   | PRI | NULL    | auto_increment |
| internal_number | int  | YES  |     | NULL    |                |
| s_id            | int  | YES  |     | NULL    |                |
+-----------------+------+------+-----+---------+----------------+
3 rows in set (0.05 sec)

mysql>

We will create one table:

mysql> CREATE TABLE internalcontrol (
    -> d_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> internal_number INT,
    -> s_id INT);
Query OK, 0 rows affected (1.19 sec)

mysql>

The d_id column is defined as an integer and set to be the primary key for the table using the NOT NULL, PRIMARY KEY, and AUTO_INCREMENT keywords. 

This means that every row in the table will have a unique d_id value, and the value for d_id will be automatically incremented for each new row inserted into the table.

The internal_number and s_id columns are also defined as integers, but no additional constraints or settings are specified. These columns will be used to store data related to the internal control documents and the stores that they are associated with. 

mysql> INSERT INTO internalcontrol
    -> VALUES (NULL, 1235547, 1);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO internalcontrol
    -> VALUES (NULL, 6665555, 7);
Query OK, 1 row affected (0.12 sec)

mysql>

These two MySQL commands are inserting data into the internalcontrol table:

Content of our table now: 

mysql> SELECT * FROM internalcontrol;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
+------+-----------------+------+
2 rows in set (0.00 sec)

mysql>

UNION example:

mysql> SELECT * FROM internalcontrol
    -> UNION
    -> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
7 rows in set (0.02 sec)

mysql>

The resulting output shows all of the columns from both tables concatenated together into a single result set, with the d_id column from internalcontrol being used as the primary key column for the combined result set. 

UNION ALL Example:

mysql> SELECT * FROM internalcontrol
    -> UNION ALL
    -> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
8 rows in set (0.00 sec)

mysql>

UNION ALL returns all rows from both tables, even if there are duplicate rows.

Note that in this case, the two tables being combined have the same column names and data types, but this is not required for using UNION ALL. 

In the results of the UNION ALL query, we see NULL in the s_id column for rows with an internal_number value that does not have a matching s_id value in the internalcontrol table. 

This is because the Documents table does not have a corresponding s_id value for those rows, so the value is NULL.

mysql> SELECT * FROM Documents
    -> UNION ALL
    -> SELECT * FROM internalcontrol;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
|    1 |         1235547 |    1 |
|    2 |         6665555 |    7 |
+------+-----------------+------+
8 rows in set (0.00 sec)

mysql>

This code is performing a UNION ALL operation between the Documents table and the internalcontrol table. The resulting table contains all the rows from both tables, with duplicates included.

In this case, the resulting table has 8 rows, with the same rows as the previous example. The only difference is the order in which the rows appear, since UNION ALL does not sort the results.

MySQL RIGHT JOIN Explained

RIGHT JOIN is a type of join operation in MySQL that returns all the records from the right table and matching records from the left table. 

If there are records in the right table that do not have a match in the left table, these records will still be returned. 

What is in our Store table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

What is in Documents:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
+------+-----------------+------+
4 rows in set (0.00 sec)

mysql>

We need more inserts, for testing purposes:

mysql> INSERT INTO Documents (d_id, internal_number)
    -> VALUES (NULL, 6584568);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO Documents (d_id, internal_number)
    -> VALUES (NULL, 9988559);
Query OK, 1 row affected (0.17 sec)

mysql>

In both statements, the NULL value for d_id indicates that the database should automatically generate a unique identifier for the new row.

Documents now:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
6 rows in set (0.00 sec)

mysql>

RIGHT JOIN example:

mysql> SELECT stores.city, stores.store_name, documents.internal_number
    -> FROM Stores
    -> RIGHT JOIN Documents
    -> ON stores.s_id = documents.s_id;
+--------+------------+-----------------+
| city   | store_name | internal_number |
+--------+------------+-----------------+
| London | London_1   |         1235547 |
| Berlin | Berlin_1   |         2223371 |
| Berlin | Berlin_2   |         6687451 |
| London | London_2   |         6548529 |
| NULL   | NULL       |         6584568 |
| NULL   | NULL       |         9988559 |
+--------+------------+-----------------+
6 rows in set (0.00 sec)

mysql>

The SELECT statement selects the columns stores.city, stores.store_name, and documents.internal_number.

The FROM clause specifies that data should be selected from the Stores table and a RIGHT JOIN should be performed with the Documents table.

A RIGHT JOIN returns all rows from the right table (Documents) and matching rows from the left table (Stores). In this case, it returns all the rows from the Documents table and only the matching rows from the Stores table.

The ON clause specifies the condition for the join, which is that the s_id column in the Stores table should match the s_id column in the Documents table.

There are also two rows with NULL values for the city and store_name columns, which correspond to the new documents that were inserted without an associated store ID.

What is s_id ?

s_id is a column in the Stores table, which is used as a foreign key in the Documents table to establish a relationship between the two tables. 

The s_id column in the Documents table references the primary key of the Stores table, which is usually called id or store_id. By using a foreign key constraint, we can ensure that the values in the s_id column of the Documents table always refer to an existing record in the Stores table. 

What are foreign keys ?

A foreign key is a column or a combination of columns in a relational database table that provides a link between data in two tables. It refers to the primary key of another table.

The purpose of foreign keys is to maintain referential integrity in a database.

This means that data in one table must match data in another table. When data is inserted or updated in a table, the foreign key ensures that the corresponding data is present in the referenced table.

If there is no matching data in the referenced table, the foreign key constraint will prevent the operation from being completed, ensuring the integrity of the database.

Foreign keys are an important part of relational database design, as they help to establish and maintain relationships between tables.

MySQL LEFT JOIN Explained

MySQL LEFT JOIN is a type of join operation in MySQL that returns all the rows from the left table (the table mentioned first in the JOIN statement) along with the matched rows from the right table (the table mentioned second in the JOIN statement).

If there are no matches in the right table, the result will still include all rows from the left table with NULL values for the columns from the right table.

LEFT JOIN same as INNER JOIN ?

No, LEFT JOIN and INNER JOIN are not the same.

INNER JOIN returns only the matched rows from both tables, based on the join condition, while LEFT JOIN returns all the rows from the left table and the matched rows from the right table, and if there is no match on the right table, the columns for the right table will be filled with NULL values.

So, if you want to include all the rows from the left table, even if there is no matching row in the right table, you can use LEFT JOIN. 

However, if you only want to retrieve the matched rows from both tables, then INNER JOIN is the appropriate option.

Table Stores:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

Table Documents:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
+------+-----------------+------+
4 rows in set (0.00 sec)

mysql>

LEFT JOIN example:

mysql> SELECT stores.city, stores.store_name, documents.internal_number
    -> FROM Stores
    -> LEFT JOIN Documents
    -> ON stores.s_id = documents.s_id;
+--------+------------+-----------------+
| city   | store_name | internal_number |
+--------+------------+-----------------+
| London | London_1   |         1235547 |
| Berlin | Berlin_1   |         2223371 |
| Berlin | Berlin_2   |         6687451 |
| London | London_2   |         6548529 |
| Moscow | Moscow_1   |            NULL |
| Moscow | Moscow_2   |            NULL |
| Miami  | Miami_1    |            NULL |
| Paris  | Paris_1    |            NULL |
+--------+------------+-----------------+
8 rows in set (0.00 sec)

mysql>

We are performing a LEFT JOIN between the Stores and Documents tables based on the s_id column. 

The resulting table shows all records from the Stores table, and any matching records from the Documents table.

Since this is a LEFT JOIN, if there are no matching records in the Documents table for a particular store, the value in the internal_number column will be NULL. 

This is why the last 4 rows of the resulting table show NULL for the internal_number column.

MySQL INNER JOIN Explained

INNER JOIN is a type of join in MySQL that combines rows from two or more tables based on a matching condition specified in the JOIN clause. 

The resulting table includes only the rows that have matching values in both tables.

The syntax of an INNER JOIN statement in MySQL is as follows: 

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Here, table1 and table2 are the names of the tables you want to join, and column is the column name that you want to match in both tables.

For example, suppose you have two tables, customers and orders, and you want to retrieve the names of all customers who have placed orders. You can use INNER JOIN as follows: 

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

In this example, customers and orders are the names of the two tables, and customer_id is the column that links the two tables. 

The resulting table will include only the rows where a matching customer_id exists in both tables, and it will show the name of the customer and the order date.

Table Preparations

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'London', 'London_1', 'Gold PSU', 153, 100);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'London', 'London_2', 'Gold PSU', 75, 100);
Query OK, 1 row affected (0.16 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'Berlin', 'Berlin_1', 'Green PSU', 50, 120);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'Berlin', 'Berlin_2', 'XYZ Motherboard', 5, 75);
Query OK, 1 row affected (0.20 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'Moscow', 'Moscow_1', 'Extension Cable', 50, 25);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'Moscow', 'Moscow_2', 'LPT Cables', 500, 10);
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'Miami', 'Miami_1', 'COM Cables', 1450, 5);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'Paris', 'Paris_1', 'NIC', 350, 15);
Query OK, 1 row affected (0.10 sec)

mysql>

The purpose of this data is to provide examples for using JOINs here and in future tutorials. 

What is in Stores:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

New data in Documents table:

mysql> INSERT INTO Documents
    -> VALUES (NULL, 1235547, 1);
Query OK, 1 row affected (0.16 sec)

mysql> INSERT INTO Documents
    -> VALUES (NULL, 2223371, 3);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO Documents
    -> VALUES (NULL, 6687451, 4);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO Documents
    -> VALUES (NULL, 6548529, 2);
Query OK, 1 row affected (0.14 sec)

mysql>

What is in Documents now:

mysql> SELECT * FROm Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
+------+-----------------+------+
4 rows in set (0.00 sec)

mysql>

INNER JOIN Example 

mysql> SELECT stores.city, stores.store_name, documents.internal_number
    -> from stores
    -> INNER JOIN documents
    -> ON stores.s_id = documents.s_id
    -> ORDER BY stores.city;
+--------+------------+-----------------+
| city   | store_name | internal_number |
+--------+------------+-----------------+
| Berlin | Berlin_1   |         2223371 |
| Berlin | Berlin_2   |         6687451 |
| London | London_1   |         1235547 |
| London | London_2   |         6548529 |
+--------+------------+-----------------+
4 rows in set (0.00 sec)

mysql>

The JOIN is performed by matching the "s_id" column in the "documents" table to the "s_id" column in the "stores" table. 

The result set includes only those rows for which there is a match in both tables. 

What these lines mean ?

    -> from stores
    -> INNER JOIN documents
    -> ON stores.s_id = documents.s_id
    -> ORDER BY stores.city;

These lines of code are a SQL query that performs an inner join between two tables, 'stores' and 'documents', based on a common column 's_id'.

The first line, "FROM stores", specifies the primary table being queried, in this case 'stores'.

The second line, "INNER JOIN documents", specifies the secondary table being joined with the primary table, in this case 'documents'.

The third line, "ON stores.s_id = documents.s_id", specifies the join condition that the two tables are being joined on the common column 's_id'.

The fourth line, "ORDER BY stores.city", specifies the ordering of the results based on the 'city' column of the 'stores' table.

This query will retrieve all the rows from the two tables where the 's_id' value matches in both tables, and then return the 'city', 'store_name' and 'internal_number' columns of these matched rows, ordered by 'city'.

MySQL JOINS - Preparations

JOIN is a clause that is used to combine rows from two or more tables based on a related column between them.

When you have data stored across multiple tables in your database, you may want to retrieve data that involves information from both tables.

There are different types of JOINs in MySQL, including:

1. INNER JOIN: returns only the matched rows from both tables based on the common column.

2. LEFT JOIN: returns all the rows from the left table and only the matched rows from the right table.

3. RIGHT JOIN: returns all the rows from the right table and only the matched rows from the left table.

4. FULL OUTER JOIN (or simply OUTER JOIN): returns all the rows from both tables, including the non-matched ones.

Preparations

We will create database Corp:

mysql> CREATE DATABASE Corp;
Query OK, 1 row affected (0.20 sec)

mysql>

After creating the Corp database, you can use it to create tables, insert data, and perform other database-related operations. 

We will activate it:

mysql> USE Corp;
Database changed
mysql>

This will change the default database to Corp, and any subsequent commands will be executed in the context of that database.

Time for table Stores:

mysql> CREATE TABLE Stores (
    -> s_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> city VARCHAR (30),
    -> store_name VARCHAR (30),
    -> product VARCHAR (50),
    -> available INT,
    -> price INT);
Query OK, 0 rows affected (0.56 sec)

mysql>

This MySQL query creates a new table named Stores with six columns.

The NOT NULL constraint on the s_id column ensures that it's always populated with a value, and the PRIMARY KEY constraint specifies that it's the main identifier of the table.

The AUTO_INCREMENT option for s_id tells MySQL to automatically generate a unique value for this column for each new row added to the table.

Now, table Documents:

mysql> CREATE TABLE Documents (
    -> d_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> internal_number INT,
    -> s_id INT);
Query OK, 0 rows affected (0.70 sec)

mysql>

The absence of a REFERENCES clause in the s_id column definition means that it's not a strict foreign key, and it doesn't enforce any referential integrity constraints.

However, you can still use it as a reference to the s_id column of the Stores table to retrieve data from both tables together using JOIN operations.

Tables are there:

mysql> SHOW TABLES IN Corp;
+----------------+
| Tables_in_corp |
+----------------+
| documents      |
| stores         |
+----------------+
2 rows in set (0.00 sec)

mysql>

Structure of table Stores:

mysql> DESCRIBE stores;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| s_id       | int         | NO   | PRI | NULL    | auto_increment |
| city       | varchar(30) | YES  |     | NULL    |                |
| store_name | varchar(30) | YES  |     | NULL    |                |
| product    | varchar(50) | YES  |     | NULL    |                |
| available  | int         | YES  |     | NULL    |                |
| price      | int         | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

Structure of table Documents:

mysql> DESCRIBE documents;
+-----------------+------+------+-----+---------+----------------+
| Field           | Type | Null | Key | Default | Extra          |
+-----------------+------+------+-----+---------+----------------+
| d_id            | int  | NO   | PRI | NULL    | auto_increment |
| internal_number | int  | YES  |     | NULL    |                |
| s_id            | int  | YES  |     | NULL    |                |
+-----------------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

What we did in this tutorial is important preparation for tutorials on JOINs. Let's go.

MySQL DROP TABLE, DROP Database

DROP TABLE is a MySQL command used to delete a table and its associated data, indexes, and privileges permanently. 

The syntax of the command is as follows: 

DROP TABLE table_name;

Where table_name is the name of the table you want to delete.

When you execute the DROP TABLE command, it removes the specified table from the database permanently, and you cannot retrieve the data or the structure of the table after that. 

You can use this command to delete any table, including those with primary key and foreign key constraints.

Before using the DROP TABLE command, ensure that you have a backup of the table or database, or you have exported the table data to a file, in case you need to retrieve the data later.

DROP DATABASE is a MySQL command used to delete a database and its associated tables, data, indexes, and privileges permanently. The syntax of the command is as follows: 

DROP DATABASE database_name;

Where database_name is the name of the database that you want to delete.

What is in table friends:

mysql> SELECT * FROM friends;
Empty set (0.00 sec)

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

We will delete it:

mysql> DROP TABLE friends;
Query OK, 0 rows affected (1.04 sec)

mysql>

After executing the DROP TABLE command, if you try to select data from the friends table, you will receive an "Empty set" message, indicating that the table no longer exists in the database.

mysql> SHOW TABLES IN test;
Empty set (0.00 sec)

mysql>

We will delete database test:

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.16 sec)

mysql>

Now we have default situation in MySQL:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

mysql>

Do BACKUPS of important data bafore using DROP command !!!

MySQL TRUNCATE TABLE

The TRUNCATE command is a MySQL statement used to remove all data from a specified table in a database. 

The TRUNCATE command works by deleting all the rows in the table, but it does not delete the table structure itself, including the columns, indexes, and constraints. 

The syntax for the TRUNCATE command is as follows:

TRUNCATE TABLE table_name;

In this syntax, table_name is the name of the table that you want to truncate. 

Once the TRUNCATE command is executed, all data in the table will be permanently removed, and the table will have zero rows. 

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

Deleting specific id:

mysql> DELETE FROM friends WHERE id=18;
Query OK, 1 row affected (0.10 sec)

mysql>

The statement DELETE FROM friends specifies the table where the data will be deleted from. The WHERE clause is used to specify the condition that must be met for a row to be deleted. In this case, the condition is that the id value must be 18.

After the execution of this code, the row with id=18 will no longer be present in the friends table.

mysql> TRUNCATE TABLE friends;
Query OK, 0 rows affected (1.47 sec)

mysql>

The statement TRUNCATE TABLE friends specifies the table where the data will be deleted from. 

Unlike the DELETE statement, the TRUNCATE statement does not require a WHERE clause to specify the condition for deleting data. Instead, it deletes all rows in the specified table.

mysql> SELECT * FROM friends;
Empty set (0.03 sec)

mysql>

Structure is there:

mysql> DESCRIBE friends;

+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

MySQL BETWEEN Range

The BETWEEN command is an operator in MySQL that is used to filter query results based on a range of values.

It is often used in the WHERE clause of a SQL statement to specify a range of values for a particular column.

The syntax for the BETWEEN operator is as follows: 

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

The BETWEEN operator checks if the value of column_name is within the range of value1 and value2, including the endpoints. If the value of column_name is within the range, it will be included in the query results.

For example, the following SQL statement retrieves all rows from the students table where the value of the age column is between 18 and 25:

SELECT * 
FROM students 
WHERE age BETWEEN 18 AND 25;

In this example, only the rows with an age value between 18 and 25, inclusive, will be returned by the query.

The BETWEEN operator can also be used with dates and times. 

For example, the following SQL statement retrieves all rows from the orders table where the order_date column is between January 1st, 2022 and December 31st, 2022: 

SELECT * 
FROM orders 
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

In this example, only the rows with an order_date between January 1st, 2022 and December 31st, 2022, inclusive, will be returned by the query.

Default listing:

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

BETWEEN usage:

mysql> SELECT * FROM friends
    -> WHERE id BETWEEN 16 AND 18;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

The WHERE clause in the query specifies the condition for the BETWEEN operator to filter the rows based on the id column.

The output shows the 3 rows that were returned by the query, along with their corresponding values for each column. Only the rows with an id value between 16 and 18, inclusive, were returned by the query.

NOT BETWEEN example:

mysql> SELECT * FROM friends
    -> WHERE id NOT BETWEEN 16 AND 17;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

Another example output of a MySQL query that uses the BETWEEN operator, but this time with the NOT keyword to retrieve rows from the friends table where the value of the id column is not between 16 and 17.

MySQL LIMIT - Number of Rows

The LIMIT command is a clause in the SQL language that is used to limit the number of rows returned from a query.

In MySQL, the LIMIT command is used to specify the number of records to return in a result set.

The syntax of the LIMIT command is as follows: 

SELECT column1, column2, ...
FROM table_name
LIMIT number;

The number parameter specifies the maximum number of rows to return. For example, if you want to retrieve only the first 10 rows from a table, you can use the following command: 

SELECT *
FROM my_table
LIMIT 10;

This will return only the first 10 rows from the my_table table.

In addition to the number parameter, the LIMIT command can also take an optional offset parameter. 

This is used to specify the starting point for the result set. For example, if you want to retrieve rows 11 to 20 from a table, you can use the following command:

SELECT *
FROM my_table
LIMIT 10 OFFSET 10;

This will skip the first 10 rows and return the next 10 rows starting from the 11th row.

Content of our table:

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

LIMIT example:

mysql> SELECT * FROM friends LIMIT 2;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

The query selects all columns (*) from the friends table and limits the result set to 2 rows. 

The output shows the 2 rows that were returned by the query, along with their corresponding values for each column. 

We can target specific columns:

mysql> SELECT id, Name, Telephone FROM friends LIMIT 2;
+----+------+-----------+
| id | Name | Telephone |
+----+------+-----------+
| 15 | Ana  |    111222 |
| 16 | Ana  |    555666 |
+----+------+-----------+
2 rows in set (0.00 sec)

mysql>

MySQL WHERE IN - NOT IN

MySQL WHERE IN and NOT IN clauses are used to filter query results based on a list of specific values or a subquery.

The WHERE IN clause allows you to specify a list of values to be matched, and the query will return all rows where the specified column value matches one of the values in the list.

On the other hand, the WHERE NOT IN clause returns all rows where the specified column value does not match any of the values in the list.

Initial situation:

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

WHERE example:

mysql> SELECT * FROm friends WHERE Lastname IN ('Gatez', 'Snow');
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

This instruction selects all rows from the friends table where the Lastname column matches either 'Gatez' or 'Snow', using the IN operator.

 It returns a table with the columns id, Name, Lastname, and Telephone. In this case, two rows are matched and returned in the result set. 

If in requests is something that is not in a table, like "XXX":

mysql> SELECT * FROm friends WHERE Lastname IN ('Gatez', 'XXX');
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

Usage of OR in combination with WHERE:

mysql> SELECT * FROm friends WHERE Lastname='Gatez' OR Lastname='Snow';
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

NOT IN example:

mysql> SELECT * FROM friends WHERE Telephone NOT IN (555666, 666777);
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

This MySQL query selects all rows from the friends table where the Telephone column is not equal to 555666 or 666777. The NOT IN operator is used to exclude specific values from the result set. 

We can target specific columns:

mysql> SELECT id, Name, Telephone FROM friends WHERE Telephone NOT IN (555666, 666777);
+----+------+-----------+
| id | Name | Telephone |
+----+------+-----------+
| 15 | Ana  |    111222 |
| 18 | John |    999888 |
+----+------+-----------+
2 rows in set (0.00 sec)

mysql>

MySQL LIKE - NOT LIKE

LIKE and NOT LIKE are operators used in MySQL for pattern matching in SQL queries.

LIKE is used to match patterns in a given string column.

NOT LIKE is similar to LIKE, but it matches all records that do not match the specified pattern. 

Wildcards are special characters used in MySQL to represent one or more characters in a string. They are used with the LIKE operator to search for patterns in strings. The two most commonly used wildcards in MySQL are the percent sign (%) and the underscore (_).

• % (percent sign) - represents any number of characters, including zero characters.
• _ (underscore) - represents a single character.

mysql> SELECT * FROM friends;
Empty set (0.00 sec)

mysql>

mysql> INSERT INTO friends
    -> VALUES(NULL, 'Ana', 'Smith', 111222);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO friends
    -> VALUES(NULL, 'Ana', 'Gatez', 555666);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO friends
    -> VALUES(NULL, 'John', 'Snow', 666777);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO friends
    -> VALUES(NULL, 'John', 'Smith', 999888);
Query OK, 1 row affected (0.12 sec)

mysql>

This code inserts four new rows into the "friends" table in a MySQL database.

Each row represents a person with their name, last name, and telephone number. The code uses the INSERT INTO command to add a new row into the table with the VALUES keyword to provide the data for each column of the row.

Since the first column of the table is an auto-incremented id, the value NULL is used to let the database generate a unique id for each new row. 

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

mysql> SELECT * FROM friends WHERE Telephone LIKE "1%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
+----+------+----------+-----------+
1 row in set (0.03 sec)

mysql>

This code selects all rows from the "friends" table where the "Telephone" column starts with the digit "1".

The wildcard symbol "%" after the "1" means that any sequence of characters can follow the "1" digit. In this case, it only returns one row where the "Telephone" value is 111222 and the person's name is Ana Smith. 

mysql> SELECT * FROM friends WHERE id LIKE "1%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

The query selects all rows from the "friends" table where the "id" column starts with the digit "1".  

mysql> SELECT * FROM friends WHERE Lastname LIKE "%z";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

This MySQL query selects all rows from the "friends" table where the "Lastname" column ends with the letter "z". The '%' symbol is used as a wildcard, meaning it matches any sequence of zero or more characters.

Therefore, "%z" matches any string that ends with "z". In this case, only one row with id=16 matches the condition. 

mysql> SELECT * FROM friends WHERE Telephone LIKE "%777";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

This query selects all rows from the "friends" table where the "Telephone" column ends with "777". 

mysql> SELECT * FROM friends WHERE Lastname NOT LIKE "Sm%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

This SQL command selects all rows from the "friends" table where the value of the "Lastname" column does not start with "Sm". The resulting table contains the ID, Name, Lastname, and Telephone of the two rows where the condition is satisfied. 

mysql> SELECT * FROM friends WHERE Telephone LIKE "%12%";
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
+----+------+----------+-----------+
1 row in set (0.00 sec)

mysql>

This command selects all rows from the "friends" table where the "Telephone" column contains the string "12" anywhere in the phone number. In this case, it returns only the row where the telephone number is "111222", which matches the search pattern.

MySQL DELETE From Table

Initial situation in our table: 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111111 |
|  2 | Anastasia | TheGreat |    111111 |
|  3 | Samantha  | Fox      |    111111 |
| 10 | Larry     | TheFirst |    111111 |
| 11 | Bill      | Gatezzz  |    111111 |
| 12 | Bill      | Gatezzz  |    111111 |
| 13 | John      | Smith    |    111111 |
| 14 | Anastasia | Some     |    111111 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Delete example targeting one column with specific id:

mysql> DELETE FROM friends WHERE (id=1);
Query OK, 1 row affected (0.10 sec)

mysql>

The above MySQL query deletes the row from the friends table where the id is equal to 1.

The DELETE statement is used to delete one or more rows from a table.

In this case, the WHERE clause is used to specify the condition that the row with id=1 should be deleted. Once the query is executed, the row is removed from the table, and the query returns a message indicating the number of affected rows, which in this case is 1. 

mysql> DELETE FROM friends WHERE (Name='Anastasia');
Query OK, 2 rows affected (0.22 sec)

mysql>

This command will delete all rows from the table "friends" where the Name column has a value of "Anastasia". 

Situation after delete command:

mysql> SELECT * FROM friends;
+----+----------+----------+-----------+
| id | Name     | Lastname | Telephone |
+----+----------+----------+-----------+
|  3 | Samantha | Fox      |    111111 |
| 10 | Larry    | TheFirst |    111111 |
| 11 | Bill     | Gatezzz  |    111111 |
| 12 | Bill     | Gatezzz  |    111111 |
| 13 | John     | Smith    |    111111 |
+----+----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

We can use OR with DELETE:

mysql> DELETE FROM friends WHERE (id=3 OR id=13);
Query OK, 2 rows affected (0.12 sec)

mysql>

This command is deleting rows from the friends table where the id column equals either 3 or 13.

The syntax of the command is as follows:

• DELETE FROM friends: This specifies the table to delete rows from.
• WHERE (id=3 OR id=13): This specifies the condition that must be met in order for rows to be deleted. In this case, rows will only be deleted if their id value is either 3 or 13. 

mysql> SELECT * FROM friends;
+----+-------+----------+-----------+
| id | Name  | Lastname | Telephone |
+----+-------+----------+-----------+
| 10 | Larry | TheFirst |    111111 |
| 11 | Bill  | Gatezzz  |    111111 |
| 12 | Bill  | Gatezzz  |    111111 |
+----+-------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

mysql> DELETE FROM friends WHERE Telephone=111111;
Query OK, 3 rows affected (0.28 sec)

mysql>

This command deletes all rows from the "friends" table where the "Telephone" column has a value of 111111. The output message shows that three rows have been affected, indicating that three rows have been deleted from the table. 

mysql> SELECT * FROM friends;
Empty set (0.00 sec)

mysql>

The output shows that the SELECT query returned an empty set, indicating that there are no more rows in the friends table after deleting the rows where the Telephone value is 111111. 

mysql> DESCRIBE friends;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

mysql>