Connecting to MySQL using MySQL Workbench

The purpose of creating a connection in MySQL Workbench is to establish a secure

communication link between MySQL Workbench (the client) and a MySQL database server.
This connection allows you to interact with the MySQL server to create, modify, and manage
databases, tables, and other objects.

When you create a connection, you specify the following key details:

1. Hostname/IP Address: Specifies the location of the MySQL server, which could be local
or remote.
2. Port: Usually 3306 for MySQL, this tells Workbench where to access the MySQL
service.
3. Username and Password: Used for authentication to ensure you have permission to
access and manage the database server.
4. Other Authentication Details: Options like SSL certificates may be required for secure
remote connections.
Creating a Schema

Once connected, MySQL Workbench uses this information to run SQL queries and
commands on the server, making it an essential step for database administration, design, and
query execution tasks within MySQL Workbench.

You can easily add (create) a new database, also known as a schema, using either the
graphical interface or SQL commands.

Method 1: Using the Graphical Interface

1. Open MySQL Workbench and connect to your MySQL server.

2. In the Navigator panel on the left, go to the Schemas tab.
3. Right-click in the Schemas panel and select "Create Schema..." from the context menu.
4. In the dialog box that appears, enter a name for your new database.
5. Optionally, set the character set and collation (the defaults are usually fine).
6. Click Apply to create the database, then confirm the action in the following windows.

After these steps, your new database (schema) will appear in the Schemas panel.

Method 2: Using an SQL Command

1. In MySQL Workbench, open a new SQL editor tab.

 Run the following SQL command:

CREATE DATABASE database_name;

2. Press Execute to create the database.

After running this command, you should see the new database listed under Schemas
once you refresh the list.

Both methods will successfully add a new database in MySQL Workbench.

Ways to Add Data to a Table in MySQL Workbench

In this lecture, we’ll cover three different methods for adding data to a table in MySQL
Workbench. We'll explain each step-by-step so that beginners can easily follow along.

Here’s the SQL command to create the students table, which we used as an example in
the lecture:

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

Using the SQL INSERT Statement

The SQL INSERT statement is a direct and simple way to add data to a table. This
method is helpful when you need to add a few specific records manually.

Step-by-Step Guide
 1. Open MySQL Workbench: Start MySQL Workbench and connect to your MySQL
server.
2. Open a New SQL Editor: In the MySQL Workbench toolbar, click on the SQL Editor
tab, or open an existing SQL editor.

Write the SQL Command: Type your INSERT command using this format:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example: Let’s say we have a table called students with three columns: student_id,
name, and age. To add a new student to this table, we would write:

INSERT INTO students (student_id, name, age)

VALUES (1, 'Alice', 20);

Here, students is the table we’re adding data to student_id, name, and age
are the columns in the table. (1, 'Alice', 20) are the values being inserted
for each column, in the same order.

3. Execute the Command: Click the Execute button (lightning bolt icon) in the toolbar to
run your command.

Verify the Inserted Data: To make sure the data has been added, run the
following command:

SELECT * FROM students;

4. You should see the new row with Alice’s information.

Using the Table Data Editor

The Table Data Editor in MySQL Workbench is a convenient way to add or edit records
directly in a table without using SQL commands. It provides a spreadsheet-like view of your
data, making it intuitive to enter data one row at a time.

Step-by-Step Guide

1. Open the Table Data Editor:

 ○ In the Schemas tab on the left side, expand your database and locate the table
you want to add data to (e.g., students).

○ Right-click the table and select Select Rows - Limit 1000. This opens the Table
Data Editor, where you can view and modify data in a table format.

2. Add a New Row:

○ Scroll down to the bottom of the table, where you’ll see an empty row labeled
Null.

○ Click on each cell to enter data for the columns. For example:

■ student_id: 4

■ name: Diana

■ age: 23

3. Apply the Changes:

○ After entering data for the new row, click Apply at the bottom right of the editor. A
preview window will show the INSERT command that will run.

○ Click Apply again to confirm the insertion.

4. Verify the Inserted Data:

○ After applying the changes, you should see the new row in the Table Data Editor.

To confirm, you can run:

SELECT * FROM students;

Using SQL Dump Files to Import Data

If you have a file that contains SQL commands (such as INSERT statements), you can
import it into MySQL Workbench. This is helpful if you have a large dataset already structured in
SQL format, often saved as a .sql file.

Step-by-Step Guide

1. Prepare the SQL Dump File:

○ Ensure you have an SQL dump file ready. The file should contain INSERT
commands structured similarly to what we’ve covered in Method 1.

2. Import the SQL File:

○ In MySQL Workbench, go to Server > Data Import.

3. Choose Your Import Source:

 ○ Select Import from Self-Contained File.

○ Click Browse and choose your SQL file.

4. Select the Target Database:

○ Choose the database where you want to load the data. Ensure that this database
contains tables matching the structure of the data in the SQL file.

5. Start the Import:

○ Click Start Import to load the data into the selected database. MySQL
Workbench will execute each SQL command in the file to add data to your
table(s).

6. Verify the Imported Data:

○ After the import finishes, you can verify by running a SELECT query on the table
to confirm the data has been added.

SQL ANY Operator

The ANY operator in SQL is used in conjunction with comparison operators (like =, <, >,
etc.) and subqueries. It allows you to compare a value to any value in a list or subquery. In
MySQL, ANY is useful when you want to check if a certain condition is met by at least one value
in a subquery.

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (subquery);

Explanation

● The ANY operator requires a subquery to produce a list of values.

● If any one of these values meets the condition, the overall condition is considered
TRUE.
● Commonly used with comparison operators such as =, <, >, !=.
Database and Setup

We'll create a database and tables to use in our examples:

Database Schema

CREATE DATABASE CompanyDB;

USE CompanyDB;

CREATE TABLE Employees (

employee_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

CREATE TABLE Departments (

department_id INT PRIMARY KEY,
department_name VARCHAR(50),
budget DECIMAL(10, 2)
);

Sample Data

INSERT INTO Employees (employee_id, name, department, salary) VALUES

(1, 'Alice', 'Sales', 45000.00),
(2, 'Bob', 'Marketing', 60000.00),
(3, 'Charlie', 'Sales', 50000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'IT', 80000.00);

INSERT INTO Departments (department_id, department_name, budget) VALUES

(1, 'Sales', 100000.00),
(2, 'Marketing', 200000.00),
(3, 'IT', 150000.00);
Queries Using ANY

Find employees whose salary is greater than any employee in the Sales department.

SELECT name, salary

FROM Employees
WHERE salary > ANY (SELECT salary FROM Employees WHERE department = 'Sales');

Sample Output:

This query retrieves the names and salaries of employees who earn more than any
employee in the Sales department.

Find departments where the budget is less than the salary of any employee in the IT
department.

SELECT department_name, budget

FROM Departments
WHERE budget < ANY (SELECT salary FROM Employees WHERE department = 'IT');
Sample Output:

This query retrieves department names and their budgets where the budget is less than
the salary of any employee in the IT department. But if we use greater than:

Seatwork:

1. List the names of employees whose salaries are higher than any employee in the
Marketing department.
2. List all departments where the department budget is greater than any employee's salary
in the Sales department.