My Web Training

📖 MySQL Workbench

This is an overview of the MySQL Workbench. The Workbench is a tool provided by the MySQL Developers to help in the creation, design, and administration of MySQL databases.

Install the MySQL Server and MySQL Workbench

Students that decide to use the Dallas College Virtual Desktop environment, you can skip down to the next section on using the MySQL Workbench. The password for the virtual desktop MySQL Workbench is 'root'.

You can find links for MySQL downloads at the MySQL Community Server. MySQL also provides instructions on using MySQL Workbench and a YouTube channel. There are a number of good videos, though some of the basic installation videos are a little dated, showing older versions of MySQL.

I run a Windows 10 computer, and from all the products available in the MySQL Installer, the only ones I needed were the MySQL Server, MySQL Workbench, and MySQL Shell. The other options for things like the connectors, docs, or samples are probably not needed. You should be able to save space on your computer by not selecting these additional items.

** I recommend you use the username 'root' and password 'root' for this local test bench. If you decide to use something more secure, please make sure you write down your username and password you choose for this installation. It can be difficult to completely remove and reinstall MySQL if you forget your login.

Using the MySQL Workbench

The Workbench Dashboard

Three main sections of the dashboard: SQL Development, Data Modeling, Server Administration.

Using the Workbench to Work with a Database

The SQL Development section of the dashboard allows you to visually manage your databases, view table structures, and execute queries. You first open a connection from the Workbench to the server. Your local installation needs to have a connection set up. Click on the plus icon (+) and supply your login username and password.

If you are using the VDI client from Dallas College, you will need to get your server credentials from your instructor.

Schema

In MySQL, a schema is another term for a database. Select the schema tab in the Navigator on the left of the screen to view all databases installed on the server.

Navigating Through the Database Objects

• Double-clicking expands the schema (database) and displays the tables.
• Double-clicking a table expands the table to display its column names.
• Double-clicking the column inserts the column name in the current cursor position in the query tab of the editor.

Query Windows

The Query tab is used for writing and executing SQL queries, while the Output section displays the results.

The right section of the editor allows access to SQL additions such as snippets. I normally minimize this part of the user interface to provide more room for writing queries.

Remember that this is a graphical user interface (GUI); almost all of the functionality of clicking on icons can be accomplished by issuing native MySQL commands in the editor.

To Create and Run Queries

1. Open a new SQL editor tab.
2. Select a database.
3. Type your commands. You have two options after composing a query: run a single command based on the current cursor position or run everything in the editor.
4. Terminate each SQL statement with a semicolon (;).

-- This is a MySQL Script --
USE classdb;

SELECT Name FROM Genre;
SELECT * FROM track WHERE GenreId = 5;
SELECT * FROM track WHERE GenreId = 5 ORDER BY Milliseconds;

Adding Comments

• Single line comments are offset by --
• Block comments (more than one line) are offset by beginning the comment with /* and ending with */

-- This is a single line comment
/* This is a multi-
line comment */

Open and Save SQL Scripts

• A script is a series of related SQL instructions. For example, a lab assignment or a backup of an entire database may be stored in a .sql script.
• Open a script file in a new query tab from your local machine as you would any file using the File menus.
• Save a script file for use outside of the current installation (such as your lab submissions) by saving the current editor content as a SQL script.

Practice Using the MySQL Workbench

Setup the MySQL Database Server and Workbench

• Access the MySQL Workbench using the Dallas College Virtual Desktop Interface (VDI) using your browser.

OR

• Download and install the MySQL Server and Workbench on your local computer.

Setting Up Your Database

Depending on the assignment, you may either be provided with a database script file or need to create a sample database using a provided script. Follow the instructions below based on your assignment:

If You Have a Database Script File

Some assignments will provide a practice database in a script file. You can install this database on your MySQL server using MySQL Workbench by following these steps:

1. Download a copy of the database from the link in the assignment.
2. Unzip the compressed (zip) file on your local computer to access the database script file.
3. Start MySQL Workbench and open a connection for managing the server.
4. Check whether the MySQL server is running. If it isn't, start it. You may need to start your MySQL server using Windows Services. If so, you will need to close the MySQL Workbench connection and reopen it after the server is running.
5. Open the script file by clicking the Open SQL Script... button in the SQL Editor toolbar. Use the file navigation dialog box to locate and open the file.
6. Execute the entire script by clicking the Execute SQL Script button in the code editor toolbar or by pressing Ctrl + Shift + Enter. When you do, the Output window will display messages indicating whether the script executed successfully. You should see a list of messages with green check marks. If you see any red X's, you have errors.

Creating a Sample Database

To get started with practicing SQL queries, let's create a sample database. Copy and paste the following script into a new SQL editor tab in MySQL Workbench and run it.

-- Create the database
CREATE DATABASE IF NOT EXISTS SampleDB;
USE SampleDB;

-- Create tables
CREATE TABLE Employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    Position VARCHAR(100)
);

CREATE TABLE Departments (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Projects (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    ProjectName VARCHAR(100),
    DepartmentID INT
);

-- Insert sample data
INSERT INTO Departments (DepartmentName) VALUES ('HR'), ('IT'), ('Marketing');
INSERT INTO Employees (Name, DepartmentID, Position) VALUES ('Alice', 1, 'Manager'), ('Bob', 2, 'Developer'), ('Charlie', 3, 'Analyst');
INSERT INTO Projects (ProjectName, DepartmentID) VALUES ('Project A', 1), ('Project B', 2), ('Project C', 3);

Using MySQL Workbench to Enter and Run SQL Statements

Once you have set up your database, you can begin interacting with it by entering and executing SQL statements. Follow these steps:

1. Select the Database

   In the Schemas menu on the left side of the MySQL Workbench interface, locate your database. Double-click on the database name (e.g., SampleDB) to set it as the default database. When you do this, MySQL Workbench will display the database name in bold, indicating it is selected.

2. Open a New Query Tab

   Click the SQL + button in the MySQL Workbench toolbar to open a new SQL editor tab. This is where you will enter your SQL commands.

3. Enter Your SQL Statements

   Type or paste your SQL statements into the open query tab. For example, you could try running a simple query to retrieve all employees in the Employees table:

   SELECT * FROM Employees;

4. Execute the SQL Statements

   To run your SQL commands, you have two options:

   • Execute a Single Statement

     Place your cursor on the line of the SQL statement you want to run, then click the Execute Current Statement button (a lightning bolt icon with a cursor) or press Ctrl + Enter.

   • Execute All Statements

     If you have multiple SQL statements and want to run them all at once, click the Execute Script button (a plain lightning bolt icon) or press Ctrl + Shift + Enter.

5. View the Results

   After executing the SQL statements, the results will be displayed in the Results Grid at the bottom of the SQL editor. Here, you can view the data returned by your query, see how many rows were affected, and check for any errors.

6. Experiment with Queries

   You can try modifying your queries or writing new ones to explore the database further. For example, you could filter results, join tables, or calculate aggregates. Don't worry if you don't know how to do this yet. We will cover these techniques in a future lesson. The important thing now is to make sure you can run these basic commands without errors.

By following these steps, you can interact with your database directly, making changes, retrieving data, and exploring the structure and content of your tables. This hands-on practice is essential for becoming proficient with SQL and MySQL Workbench.

Reference Manual

There are many web-based resources available for finding "how to" syntax for MySQL. If you familiarize yourself with how the official reference manual is structured, it can be a great resource for locating the structure of particular statements and their clauses.

https://dev.mysql.com/doc/

MySQL monitor: the Windows command line or Mac terminal tool for executing MySQL commands. Just as almost every clickable icon in the Workbench can be done by typing a command in the editor, almost everything a user can accomplish in the Workbench can be done from a command line.