18  MySQL

MySQL, pronounced either as “My S-Q-L” or “My Sequel,” is a robust open-source relational database management system (RDBMS). It is widely used for managing the data in relational databases and operates by using SQL (Structured Query Language) for data management, manipulation, and retrieval tasks.

18.1 Installation

18.1.1 Windows

1. Download the Installer
   • Navigate to the MySQL Downloads page.
   • Select Windows as the platform and download the MySQL Installer for Windows, specifically version 8.0.37.
2. Run the Installer
   • Execute the downloaded file. If a security dialog appears, click ‘Yes’ to permit the installer to make changes to your system.
3. Choosing Setup Type
   • When prompted, select “Custom” as the setup type. This option allows you to choose specific components to install, such as MySQL Server, MySQL Workbench, and MySQL Shell.
4. Select Products and Features
   • Make sure to select ‘MySQL Server’ and ‘MySQL Workbench’ among other components you find necessary. These are crucial for a full installation.
5. Installation
   • Follow the on-screen instructions to install the components you have chosen. This process may take a few minutes.
6. Configure MySQL Server
   • Post-installation, you will be guided through a series of configuration steps including setting up the root password, creating user accounts, and configuring network options and Windows Service.
7. Complete Installation
   • Finish the setup to finalize the installation of MySQL on your system.

18.1.2 Mac

1. Download the DMG Archive
   • Visit the MySQL Downloads page and select macOS as the operating system.
   • Download the DMG file for macOS, corresponding to version 8.0.36.
2. Install MySQL
   • Open the downloaded DMG file and follow the on-screen installation instructions.

Now install MySQL Workbench

1. Download MySQL Workbench
   • Access the MySQL Workbench download page.
   • Select the appropriate version for your operating system and download it.
2. Install the application and follow the setup instructions.

18.2 MySQL Workbench

MySQL Workbench provides a graphical interface for database management and SQL development. It integrates visual tools for data modeling, SQL development, and comprehensive database administration.

18.2.1 New Connection

The next step is to establish a connection to your server through MySQL Workbench:

1. Launch MySQL Workbench
   • Open MySQL Workbench from your applications list. When you start the application, you will see the main interface.

Tip for Windows Users

If MySQL Workbench was not included during the initial setup of MySQL Server, it can be installed separately:

• Access the MySQL Workbench download page.
• Select the appropriate version for your operating system and download it.

2. Create a New Connection
   • At the home screen, you will see an area labeled “MySQL Connections.” Click on the “+” symbol, which is used to create a new connection.
   • In the setup dialog:
     • Connection Name: Give your connection a recognizable name like local-connection.
     • Hostname: Enter localhost if your database server is on the same machine as the Workbench.
     • Port: The default MySQL port is 3306.
     • Username: Enter the username you set during the MySQL Server installation (usually ‘root’).
     • Password: Click on “Store in Vault” and enter the password for the user account.
   • After filling out these fields, click “Test Connection” to ensure all settings are correct and the connection can be established.
3. Save the Connection
   • If the connection test is successful, save the configuration by clicking “OK.” This connection will now appear on the MySQL Workbench home screen for easy access.

18.3 Using MySQL Workbench

MySQL Workbench provides various tools and shortcuts to facilitate database management:

• Toolbar: At the top, you will find the toolbar which includes buttons like the new query tab, execute query, and others.
• Sidebar: On the left, the sidebar provides access to instance management, performance monitoring, and more.
• Query Tab: This is where you will write and execute your SQL. You can open a new query tab by clicking on the “+” icon near the existing tabs.
• Output Area: Below the query editor, this area displays the results of executed queries or any messages from the database server.

18.3.1 Create Database

1. Create a Database
   • Connect to your MySQL instance using the connection you set up.
   • Open a new query tab.
   • Enter and execute the following

SQL commands:

DROP DATABASE IF EXISTS `db_data`;
CREATE DATABASE `db_data`;
USE `db_data`;

2. Refresh Schema List
   • After running the commands, refresh the schema list on the left side of the interface to see your new database.

18.3.2 Create Table

To structure data about marketing campaigns, you’ll need a table:

1. Open a New Query Tab
   • Click the “+” icon at the top to start a new query.
2. Define the Table
   • Run the following SQL to create your table:

CREATE TABLE campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(10, 2),
    channel VARCHAR(255)
    );

Table Definition Explanation

The SQL command above creates a new table named campaigns. Here’s a breakdown of each column definition:

• id: A column named id that uses the BIGSERIAL data type, which is an auto-incrementing integer suitable for a primary key.
• name: A column for storing campaign names as text, defined to hold a string of up to 255 characters. The NOT NULL constraint ensures that every row must have a name.
• start_date and end_date: These columns store the start and end dates of the campaigns, respectively, with the DATE data type.
• budget: A column for the budget associated with each campaign, using DECIMAL(10, 2) to allow for up to 10 digits with 2 decimal places.
• channel: A column to specify the medium used for the campaign, capable of storing strings up to 255 characters.

3. Execute the Command
   • Click the lightning bolt icon (⚡) to execute. Check the schema view to confirm the creation of the table.

18.3.3 Insert Values

To populate the table with data:

1. Insert Data
   • In a new query tab, use this SQL command:

INSERT INTO campaigns (name, start_date, end_date, budget, channel) VALUES
('Winter Sale', '2023-12-01', '2023-12-31', 5000.00, 'Email'),
('Summer Festival', '2023-06-01', '2023-06-15', 10000.00, 'Social Media'),
('Back to School', '2023-08-01', '2023-08-20', 7500.00, 'TV');

Insert Statement Explanation

This SQL command inserts data into the campaigns table. Each row of values corresponds to a different marketing campaign:

• First Row: Inserts a campaign called “Winter Sale” running from December 1 to 31, 2023, with a budget of $5000, using the “Email” channel.
• Second Row: Inserts “Summer Festival”, a shorter campaign from June 1 to 15, 2023, with a higher budget of $10000, and utilizing “Social Media”.
• Third Row: Adds the “Back to School” campaign from August 1 to 20, 2023, with a budget of $7500, marketed through “TV”. Each set of values is enclosed within parentheses and separated by commas to delineate different campaigns.

18.3.4 Retrieve Values

To review the data:

1. Run a SELECT Query
   • Execute the following in a new query tab:

SELECT * FROM campaigns;

SELECT Query Explanation

This SQL command retrieves all columns from all rows in the campaigns table. It is useful for viewing all the data that has been entered into the table, ensuring that entries are correct and to facilitate further data analysis or reporting.

This should output:

id	name	start_date	end_date	budget	channel
1	Winter Sale	2023-12-01	2023-12-31	5000.00	Email
2	Summer Festival	2023-06-01	2023-06-15	10000.00	Social Media
3	Back to School	2023-08-01	2023-08-20	7500.00	TV