MySQL BACKUP, RESTORE Database

You are strongly advised to check corresponding Youtube video.

How to backup MySQL database

1. Open the Command Prompt or PowerShell on your Windows 10 machine.

2. Navigate to the directory where the MySQL installation is located. 

3. Once you are in the MySQL installation directory, execute the following command to backup the database to a SQL file: 

   mysqldump -u [username] -p [database_name] > [backup_file_name.sql]
   

   Replace [username] with your MySQL username, [database_name] with the name of the database you want to backup, and [backup_file_name.sql] with the desired name of the backup file.

   You will be prompted to enter your MySQL password after executing the command.

4. The backup file will be created in the MySQL installation directory. You can copy the backup file to a different location to store it. 

Backup, Step by Step

We will backup "corp" database.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| corp               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

In CMD, go to location where MySQL is installed. 

Microsoft Windows [Version 10.0.18363.959]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>cd "C:\Program Files\MySQL Server 8.0\bin\"

C:\Program Files\MySQL Server 8.0\bin>mysqldump -u root -p corp > CORPARCH.sql
Enter password: *****

C:\Program Files\MySQL Server 8.0\bin>

cd "C:\Program Files\MySQL Server 8.0\bin\": This command changes the current directory to C:\Program Files\MySQL Server 8.0\bin\ where the mysqldump utility is located.

mysqldump -u root -p corp > CORPARCH.sql: This command runs the mysqldump utility with the username root and the database name corp. It then redirects the output to a file named CORPARCH.sql. This creates a backup of the corp database as a SQL script file.

Enter password: *****: This prompts the user to enter the password for the root user. The password is not visible when typing for security reasons.

C:\Program Files\MySQL Server 8.0\bin>: This is the command prompt after the mysqldump command has completed. 

C:\Program Files\MySQL Server 8.0\bin>dir CORP*
 Volume in drive C is New Volume

 Directory of C:\Program Files\MySQL Server 8.0\bin

07/24/2020  05:22 PM             5,920 CORPARCH.sql
               1 File(s)          5,920 bytes
               0 Dir(s)  78,735,020,032 bytes free

C:\Program Files\MySQL Server 8.0\bin>

Directory of C:\Program Files\MySQL Server 8.0\bin : This line indicates that the directory of the command prompt is "C:\Program Files\MySQL Server 8.0\bin".

07/24/2020 05:22 PM 5,920 CORPARCH.sql : This line shows the details of the file "CORPARCH.sql" which was created on July 24, 2020 at 5:22 PM and has a size of 5,920 bytes. 

How to Drop and restore MySQL Database 

mysql> DROP DATABASE Corp;
Query OK, 4 rows affected (1.04 sec)

mysql>

The code is dropping the "Corp" database in MySQL. 

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

mysql>

Database "Corp" is no more. 

Restoring Database "Corp"

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

mysql>

Previous code creates a new database called "Corp" in MySQL. 

mysql> USE Corp;
Database changed
mysql> SHOW TABLES FROM Corp;
Empty set (0.00 sec)

mysql>

The code above switches to the database named "Corp" using the command USE Corp.

Then, it tries to display the tables in the currently selected database using the command SHOW TABLES FROM Corp.

Since the database is newly created and no tables have been created yet, the output is an empty set. 

C:\Program Files\MySQL Server 8.0\bin>mysql -u root -p corp < CORPARCH.sql
Enter password: *****

C:\Program Files\MySQL Server 8.0\bin>

This code runs the mysql client from the command line and connects to a MySQL server as the root user, prompting for the user's password.

It then takes a backup file called "CORPARCH.sql" located in the same directory and uses it to restore the "corp" database.

The "<" character is a shell command for input redirection, which means the contents of the backup file will be used as input for the mysql command.

Database Restored 

mysql> SHOW TABLES FROM Corp;
+------------------------+
| Tables_in_corp         |
+------------------------+
| documents              |
| internalcontrol        |
| prices_higher_than_100 |
| stores                 |
+------------------------+
4 rows in set (0.04 sec)

mysql>

Restoring worked. The command SHOW TABLES FROM Corp is used to display all the tables in the "Corp" database. 

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+-----------------+
| s_id | city   | store_name | product         | available | price | ShipCorp        |
+------+--------+------------+-----------------+-----------+-------+-----------------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | Speedy Gonzales |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | Speedy Gonzales |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | Speedy Gonzales |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | Speedy Gonzales |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | Speedy Gonzales |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | Speedy Gonzales |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | Speedy Gonzales |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | Speedy Gonzales |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 | Speedy Gonzales |
|   10 | xxx    | yyy        | ppp             |      5421 |   500 | Speedy Gonzales |
|   11 | bla    | bla        | bla             |      1234 |   350 | Daffy Duck      |
+------+--------+------------+-----------------+-----------+-------+-----------------+
11 rows in set (0.00 sec)

mysql>

All data preserved.

You are strongly advised to check corresponding Youtube video:

Python & MySQL Tutorial

 

1. Python & MySQL - Connector Installation and Login to MySQL Server
2. Python & MySQL - Automatic Database Creation
3. Python & MySQL - Terminal Client Emulation in Python
4. Python & MySQL - Create Table and Insert Values
5. Python & MySQL - Custom MySQL Client in Python - ( Insert New Values )
6. Python & MySQL - Delete From Table
7. Python & MySQL - Update Table
8. Python & MySQL - ADD and DROP Columns, and DEFAULT Values in Columns
9. Python & MySQL - UNION DISTINCT and UNION ALL
10. Python & MySQL - Find & Delete Duplicate Values
11. Python & MySQL - INSERT Multiple Rows - executemany()
12. Python & MySQL - CREATE and DROP MySQL VIEWs

MySQL Tutorial

1. MySQL Introduction
2. MySQL Server Installation
3. MySQL SHOW, USE, DESCRIBE
4. MySQL CREATE and DROP
5. MySQL CREATE TABLE
6. MySQL NULL and NOT NULL
7. MySQL Data Types in Tables
8. MySQL INSERT Values Into Table
9. MySQL INSERT Alternatives
10. MySQL Custom Reports
11. MySQL SELECT DISTINCT
12. MySQL WHERE Clause
13. MySQL AND & OR Conditions
14. MySQL NOT Condition
15. MySQL ORDER BY - ASC & DESC
16. MySQL UPDATE Data
17. MySQL DELETE From Table
18. MySQL LIKE - NOT LIKE
19. MySQL WHERE IN - NOT IN
20. MySQL LIMIT - Number of Rows
21. MySQL BETWEEN Range
22. MySQL TRUNCATE TABLE
23. MySQL DROP TABLE, DROP DB
24. MySQL JOINS - Preparations
25. MySQL INNER JOIN Explained
26. MySQL LEFT JOIN Explained
27. MySQL RIGHT JOIN Explained
28. MySQL UNION & UNION ALL
29. MySQL AVG Function
30. MySQL COUNT Function
31. MySQL MAX, MIN Functions
32. MySQL SUM Function
33. MySQL UCASE, LCASE Functions
34. MySQL MID Function
35. MySQL LENGTH Function
36. MySQL VIEWs ?
37. MySQL HAVING Clause
38. MySQL ALTER TABLE
39. MySQL DEFAULT Values
40. MySQL BACKUP, RESTORE Database

Python & MySQL - CREATE and DROP MySQL VIEWs

Python & MySQL - INSERT Multiple Rows - executemany()

Python & MySQL - Find & Delete Duplicate Values

Python & MySQL - UNION DISTINCT and UNION ALL

Python & MySQL - ADD and DROP Columns, and DEFAULT Values in Columns

Python & MySQL - Update Table

Python & MySQL - Delete From Table

Python & MySQL - Custom MySQL Client in Python - ( Insert New Values )

Python & MySQL - Create Table and Insert Values

Python & MySQL - Terminal Client Emulation in Python

Python & MySQL - Automatic Database Creation

Python & MySQL - Connector Installation and Login to MySQL Server

MySQL DEFAULT Values

A default value is a value that is assigned to a column if no explicit value is provided during insertion.

It is useful if the user does not know what value to provide. 

The default value can be specified during the column definition, or it can be added later using the ALTER TABLE statement.  

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 |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 |
+------+--------+------------+-----------------+-----------+-------+
9 rows in set (0.00 sec)

mysql>

Now we will add a new column called ShipCorp to the Stores table and sets the default value to 'Speedy Gonzales'.

mysql> ALTER TABLE Stores ADD ShipCorp VARCHAR (50) DEFAULT 'Speedy Gonzales';
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

This means that if a new row is inserted into the Stores table without specifying a value for ShipCorp, the default value 'Speedy Gonzales' will be used instead. 

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

mysql>

New row:

mysql> INSERT INTO Stores(s_id, city, store_name, product, available, price)
    -> VALUES (NULL, 'xxx', 'yyy', 'ppp', 5421, 500);
Query OK, 1 row affected (0.07 sec)

mysql>

Our MySQL code inserts a new row into the "Stores" table with the following values for each column:

• "s_id": NULL (since it is an auto-increment column, the database will generate a unique value for this column automatically)
• "city": 'xxx'
• "store_name": 'yyy'
• "product": 'ppp'
• "available": 5421
• "price": 500

The statement is successful and affects 1 row, as confirmed by the message "Query OK, 1 row affected". 

mysql> SELECT * FROM Stores WHERE s_id=10;
+------+------+------------+---------+-----------+-------+-----------------+
| s_id | city | store_name | product | available | price | ShipCorp        |
+------+------+------------+---------+-----------+-------+-----------------+
|   10 | xxx  | yyy        | ppp     |      5421 |   500 | Speedy Gonzales |
+------+------+------------+---------+-----------+-------+-----------------+
1 row in set (0.00 sec)

mysql>

Our select instruction code gets all columns from the "Stores" table where the value of the "s_id" column is equal to 10. 

The result is a table with one row that matches the condition, showing the values of all columns in that row. 

mysql> ALTER TABLE Stores ALTER ShipCorp SET DEFAULT 'Daffy Duck';
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

We can change the default value of the "ShipCorp" column in the "Stores" table to 'Daffy Duck'. 

This means that if a new row is inserted into the "Stores" table without specifying a value for the "ShipCorp" column, it will be automatically set to 'Daffy Duck'.

Note that this change does not affect existing rows in the table - it only affects future inserts.

If you want to update the "ShipCorp" value for existing rows that do not have a value specified, you can use the UPDATE statement with a WHERE clause that checks for NULL values in the "ShipCorp" column. 

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       |                |
| ShipCorp   | varchar(50) | YES  |     | Daffy Duck |                |
+------------+-------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)

mysql>

New row:

mysql> INSERT INTO Stores(s_id, city, store_name, product, available, price)
    -> VALUES (NULL, 'bla', 'bla', 'bla', 1234, 350);
Query OK, 1 row affected (0.09 sec)

mysql>

The above code is inserting a new row into the "Stores" table with the following values:

• s_id: NULL (which will auto-increment since s_id is an auto-increment column)
• city: 'bla'
• store_name: 'bla'
• product: 'bla'
• available: 1234
• price: 350

Since the "ShipCorp" column has a default value set as 'Daffy Duck', it will automatically be set to that value for this new row. 

Check row 11, it's there.

mysql> SELECt * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+-----------------+
| s_id | city   | store_name | product         | available | price | ShipCorp        |
+------+--------+------------+-----------------+-----------+-------+-----------------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | Speedy Gonzales |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | Speedy Gonzales |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | Speedy Gonzales |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | Speedy Gonzales |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | Speedy Gonzales |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | Speedy Gonzales |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | Speedy Gonzales |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | Speedy Gonzales |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 | Speedy Gonzales |
|   10 | xxx    | yyy        | ppp             |      5421 |   500 | Speedy Gonzales |
|   11 | bla    | bla        | bla             |      1234 |   350 | Daffy Duck      |
+------+--------+------------+-----------------+-----------+-------+-----------------+
11 rows in set (0.00 sec)

mysql>

MySQL ALTER TABLE and DELETE Columns

The ALTER TABLE statement is used to modify the structure of an existing table, such as adding or dropping columns, changing column data types or constraints, renaming the table, or changing the table engine.  

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>

Our ALTER statement is adding a new column named "Manager" to the "Stores" table in the MySQL database, with a data type of VARCHAR (variable character) and a maximum length of 50 characters. 

mysql> ALTER TABLE Stores ADD COLUMN Manager VARCHAR (50);
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

The "ALTER TABLE" command allows you to modify the structure of an existing table in a database.

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

mysql>

Ok, now our MySQL query uses the ALTER TABLE statement to modify the Stores table by changing the data type of the Manager column to VARCHAR(100). 

mysql> ALTER TABLE Stores CHANGE Manager Manager VARCHAR (100);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

The new data type specification indicates that the Manager column can now store character strings up to 100 characters in length. 

The query reports that 0 rows were affected, which means that the table structure was updated successfully without modifying any existing data. 

Structure:

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    |                |
| Manager    | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.06 sec)

mysql>

We will use the INSERT INTO statement to add a new record to the Stores table. 

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'XXX', 'XXX_1', 'Some', 4562, 500, "John Smith");
Query OK, 1 row affected (0.09 sec)

mysql>

The NULL value in the s_id column indicates that this value will be automatically generated by MySQL. 

The remaining values represent the data for the new record, including the city, store name, product, available quantity, price, and manager name. 

The query reports that 1 row was affected, indicating that the new record was successfully added to the table.

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

mysql>

Drop column:

mysql> ALTER TABLE Stores DROP COLUMN Manager;
Query OK, 0 rows affected (2.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

This MySQL query uses the ALTER TABLE statement to remove the Manager column from the Stores table. 

Situation after drop column command:

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 |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 |
+------+--------+------------+-----------------+-----------+-------+
9 rows in set (0.00 sec)

mysql>

Add column:

mysql> ALTER TABLE Stores ADD COLUMN BLABLA CHAR(100);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Drop column:

mysql> ALTER TABLE Stores DROP COLUMN BLABLA;
Query OK, 0 rows affected (1.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Table structure at the moment:

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>

MySQL HAVING Clause

The MySQL HAVING clause is used to filter the results of an SQL query that includes a GROUP BY clause. 

It allows you to specify a condition that must be met by the groups that are created by the GROUP BY clause.

The HAVING clause is similar to the WHERE clause, but while the WHERE clause is used to filter individual rows, the HAVING clause filters groups of rows. 

The HAVING clause is always used after the GROUP BY clause in a SELECT statement.

The GROUP BY clause in MySQL is used to group together rows in a table based on one or more columns - it is typically used in combination with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(), which are used to perform calculations on the grouped data. 

What we have in a 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>

Simple "HAWING" and  "WERE" combination: 

mysql> SELECT * FROM Stores
    -> WHERE price > 50
    -> HAVING available > 50;
+------+--------+------------+----------+-----------+-------+
| s_id | city   | store_name | product  | available | price |
+------+--------+------------+----------+-----------+-------+
|    1 | London | London_1   | Gold PSU |       153 |   100 |
|    2 | London | London_2   | Gold PSU |        75 |   100 |
+------+--------+------------+----------+-----------+-------+
2 rows in set (0.02 sec)

mysql>

Our query selects all columns from the "Stores" table where the "price" column is greater than 50. 

Then, the HAVING clause is used to further filter the results to only include records where the "available" column is greater than 50.

So, WHERE clause is used to filter individual rows based on a specific condition, and the HAVING clause is used to filter groups of rows based on a specific condition.

mysql> SELECT * FROM Stores
    -> WHERE store_name LIKE "%on%"
    -> HAVING price > 25;
+------+--------+------------+----------+-----------+-------+
| s_id | city   | store_name | product  | available | price |
+------+--------+------------+----------+-----------+-------+
|    1 | London | London_1   | Gold PSU |       153 |   100 |
|    2 | London | London_2   | Gold PSU |        75 |   100 |
+------+--------+------------+----------+-----------+-------+
2 rows in set (0.00 sec)

mysql>

The query selects all columns from the "Stores" table where the "store_name" column contains the string "on". 

Then, the HAVING clause is used to further filter the results to only include records where the "price" column is greater than 25.

MySQL VIEWs

MySQL views are virtual tables that are created based on the result of a SELECT query. 

In other words, a view is a stored query that you can treat as a table. When you create a view, you define the SELECT statement that will be used to populate the view.

The main purpose of views is to simplify the complexity of a database by allowing users to access only the information they need, without giving them direct access to the underlying tables. 

Views can also be used to combine data from multiple tables or to restrict access to sensitive data.

Once a view is created, you can query it just like any other table, using SELECT statements

MySQL views can be created using the CREATE VIEW statement, and can be modified or dropped using the ALTER VIEW and DROP VIEW statements, respectively. 

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>

CREATE VIEW example:

mysql> CREATE VIEW Prices_Higher_Than_100 AS
    -> SELECT store_name, product, available, price
    -> FROM Stores
    -> WHERE Price > 100;
Query OK, 0 rows affected (0.42 sec)

mysql>

The query creates a view named "Prices_Higher_Than_100" that selects data from a table named "Stores". 

The view includes four columns: "store_name", "product", "available", and "price". 

The view only includes records where the "price" column is greater than 100.

Once this view is created, it can be queried like any other table in the database. 

Tables in our database, with our view:

mysql> SHOW TABLES FROM Corp;
+------------------------+
| Tables_in_corp         |
+------------------------+
| documents              |
| internalcontrol        |
| prices_higher_than_100 |
| stores                 |
+------------------------+
4 rows in set (0.02 sec)

mysql>

In this case, there are four tables: "documents", "internalcontrol", "prices_higher_than_100", and "stores".

This command is useful to quickly get an overview of the tables in a database, and to ensure that a particular table or view exists before executing queries or commands that reference it.

When we run our view:

mysql> SELECT * FROM prices_higher_than_100;
+------------+-----------+-----------+-------+
| store_name | product   | available | price |
+------------+-----------+-----------+-------+
| Berlin_1   | Green PSU |        50 |   120 |
+------------+-----------+-----------+-------+
1 row in set (0.00 sec)

mysql>

The query retrieves all records from the view, which only includes records where the "price" column is greater than 100.

The result set returned by the query includes one record that matches the criteria specified in the view's SELECT statement. The values in these columns correspond to the data in the underlying "Stores" table, but only for records where the "price" column is greater than 100.

Our query demonstrates how views can be used to simplify database access and retrieve only the data that meets specific criteria.

MySQL LENGTH Function

The MySQL LENGTH function returns the number of characters in a given string.

It takes a string as an argument and returns an integer value that represents the length of the string.  

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>

We need to give name to our LENGTH instruction:

mysql> SELECT product, LENGTH(product) AS NumberOfCharacters FROM Stores;
+-----------------+--------------------+
| product         | NumberOfCharacters |
+-----------------+--------------------+
| Gold PSU        |                  8 |
| Gold PSU        |                  8 |
| Green PSU       |                  9 |
| XYZ Motherboard |                 15 |
| Extension Cable |                 15 |
| LPT Cables      |                 10 |
| COM Cables      |                 10 |
| NIC             |                  3 |
+-----------------+--------------------+
8 rows in set (0.00 sec)

mysql>

Instruction will select the product column and calculate the number of characters in each value using the LENGTH() function. 

The result shows the name of each product in the product column and the length of the name in the NumberOfCharacters column.

Simple.