web-201

MySQL Workbench Walkthrough

MySQL is an open-source relational database management system (RDBMS). MySQL Workbench is a tool that allows database maintainers to interact with the data in a MySQL database.

Connecting to MySQL

1. Open MySQL Workbench
2. Under “MySQL Connections,” select the existing Local instance
   

The important panes in the instance view are the Navigator, the Query, and the Output. Note where each of these is located:

Most of the work in the Workbench will take place in the Query pane, which allows developers to execute SQL queries.

Creating a Database

The connection currently exists, but there is no database! Follow the steps below to create a new MySQL database.

1. In the Query pane, enter the following SQL:

    CREATE DATABASE IF NOT EXISTS db;
   

2. Click the lightning bolt icon () to execute the query
3. Check the Output pane to make sure the query executed successfully
4. In the Navigator, flip to the “Schemas” view, and click the refresh icon to verify that the new db database appears!

Creating a Table

Now the database exists, but it does not have any tables. Create a table so that the data in the database is organized and meaningful.

1. In the Query pane, enter the following SQL:

    USE db;
   

2. Highlight the USE db; text and click the lightning bolt to execute the statement
   • This statement tells the engine to use the specified database for all subsequent queries
3. In the Query pane, enter the following SQL:

    CREATE TABLE IF NOT EXISTS movies (
        title TEXT,
        year INTEGER,
        genre TEXT,
        director TEXT
    );
   

4. Highlight the newly added text and click the lightning bolt to execute the statement
   • This statement creates a new table in the database named movies with title, year, genre, and director as columns
5. In the Navigator, in the “Schemas” view, click the refresh icon and check that the movies table appears!

Inserting Rows

The table exists, but it currently contains no data! Follow the steps below to add some data to the table.

1. In the Query pane, enter the following SQL:

    INSERT INTO movies
    VALUES ("Going Overboard", 1989, "Comedy", "Valerie Breiman");
   

2. Highlight the newly added text and click the lightning bolt to execute the statement
3. Use SELECT * FROM movies to check and make sure the movie was successfully added to the table
4. Insert another new movie with the following properties:
   • Title: Sandy Wexler
   • Year: 2017
   • Genre: Comedy
   • Director: Steven Brill
5. Highlight the SELECT * query and re-execute it to make sure both movies appear!

Adding a Column

In addition to tracking all of the other Movie properties, this table should store the runtime of the movies in minutes.

1. In the Query pane, enter the following SQL:

    ALTER TABLE movies
    ADD runtime INTEGER;
   

2. Highlight the newly added text and click the lightning bolt to execute the statement
   • This statement will add a column to the table
3. In the Navigator, in the “Schemas” view, click the refresh icon and check that the runtime column appears!

Updating Rows

Now that a new column exists, it is possible to update the existing rows with the more information.

1. In the Query pane, enter the following SQL:

    SET SQL_SAFE_UPDATES = 0;
   

2. Highlight the newly added text and click the lightning bolt to execute the statement
   • This statement allows developers to update rows without constraint. It is not recommended to use this setting in a real database
3. In the Query pane, enter the following SQL:

    UPDATE movies
    SET runtime = 97
    WHERE title = "Going Overboard";
   

4. Execute the UPDATE statement to set the runtime of “Going Overboard” to 97
5. Use SELECT * FROM movies again to verify that the “Going Overboard” row has the proper runtime

Deleting a Row

Rather than updating the row for “Sandy Wexler,” it is possible to simply delete it.

1. In the Query pane, enter the following SQL:

    DELETE FROM movies
    WHERE title = "Sandy Wexler";
   

2. Execute the DELETE statement to remove the row
3. Use SELECT * FROM movies again to verify that the “Sandy Wexler” row no longer appears