MySQL CLI and Workbench - Step-by-Step Execution

Concept Flow - MySQL CLI and Workbench

Start MySQL CLI or Workbench

↓

Connect to MySQL Server

↓

Enter SQL Query

↓

Send Query to Server

↓

Server Executes Query

↓

Receive Results

↓

Display Results in CLI or Workbench

↓

Repeat or Exit

This flow shows how you start MySQL CLI or Workbench, connect to the server, run queries, and see results.

Execution Sample

MySQL

mysql -u root -p
SHOW DATABASES;
USE testdb;
SHOW TABLES;

This example connects to MySQL CLI, lists databases, switches to 'testdb', and lists its tables.

Execution Table

Step	Action	Input/Command	Server Response	Output Displayed
1	Start MySQL CLI	mysql -u root -p	Prompt for password	Password prompt shown
2	Enter password	******	Connected to MySQL Server	Welcome message and prompt shown
3	List databases	SHOW DATABASES;	List of databases	Databases displayed in table format
4	Select database	USE testdb;	Database changed	Confirmation message shown
5	List tables	SHOW TABLES;	List of tables in testdb	Tables displayed in table format
6	Exit CLI	exit	Connection closed	CLI exits
7	Start Workbench	Open MySQL Workbench app	Workbench interface loads	GUI ready for queries
8	Connect to server	Select connection and connect	Connected to MySQL Server	Server schemas shown
9	Run query	SHOW DATABASES;	List of databases	Databases shown in Results tab
10	Run query	USE testdb;	Database changed	Status message shown
11	Run query	SHOW TABLES;	List of tables	Tables shown in Results tab
12	Close Workbench	Close app	Disconnected	Workbench closes

💡 User exits CLI or closes Workbench, ending the session.

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	After Step 9	After Step 10	After Step 11	Final
Current Database	None	None	testdb	testdb	None	testdb	testdb	None
Connection Status	Disconnected	Connected	Connected	Connected	Connected	Connected	Connected	Disconnected

Key Moments - 3 Insights

Why do I need to enter a password after starting MySQL CLI?

The password prompt appears at Step 1 because MySQL requires authentication to connect to the server, ensuring only authorized users access the database.

What does the 'USE testdb;' command do?

At Step 4 and Step 10, 'USE testdb;' changes the current database context so that subsequent queries run on 'testdb'. Without this, queries might run on the default or previous database.

Why do results appear differently in CLI and Workbench?

Steps 5 and 11 show that CLI displays results as text tables in the terminal, while Workbench shows results in a graphical Results tab, making it easier to read and interact with.

Visual Quiz - 3 Questions

Look at the execution table at Step 3. What is the server response after running 'SHOW DATABASES;'?

A List of databases

B Database changed

C Connection closed

D Password prompt

💡 Hint

Check the 'Server Response' column for Step 3 in the execution table.

At which step does the current database change to 'testdb' in the variable tracker?

A After Step 3

B After Step 4

C After Step 5

D After Step 9

💡 Hint

Look at the 'Current Database' row in the variable tracker after each step.

If you skip entering the password at Step 2, what would happen?

A You would connect without password

B Server would list databases anyway

C Connection to MySQL Server would fail

D CLI would exit automatically

💡 Hint

Refer to Step 1 and 2 in the execution table about authentication.

Concept Snapshot

MySQL CLI and Workbench let you connect to MySQL Server.
Start CLI with 'mysql -u user -p' and enter password.
Use 'SHOW DATABASES;' to list databases.
Use 'USE dbname;' to select a database.
Workbench provides a GUI for running queries and viewing results.
Exit CLI with 'exit' or close Workbench to disconnect.

Full Transcript

This lesson shows how to use MySQL CLI and Workbench to connect to a MySQL server, run queries, and see results. First, you start the CLI or open Workbench. Then you connect by entering your username and password. After connection, you can list databases with 'SHOW DATABASES;'. To work in a specific database, use 'USE databasename;'. You can then list tables with 'SHOW TABLES;'. CLI shows results as text, while Workbench shows them graphically. Finally, you exit the CLI with 'exit' or close Workbench to end the session.

Practice

1. What is the main difference between MySQL CLI and MySQL Workbench?

easy

A. MySQL CLI is command-line based, while Workbench provides a graphical interface.

B. MySQL CLI can only read data, Workbench can only write data.

C. MySQL CLI is used for backups, Workbench is used for queries only.

D. MySQL CLI requires internet, Workbench works offline.

Solution

1. Step 1: Understand the interface types

   MySQL CLI uses text commands typed in a terminal, while Workbench shows windows and buttons.

2. Step 2: Compare their usage

   CLI is purely text-based; Workbench is graphical and easier for beginners.

3. Final Answer:

   MySQL CLI is command-line based, while Workbench provides a graphical interface. -> Option A

4. Quick Check:

   CLI = command-line, Workbench = GUI [OK]

Hint: CLI = text commands, Workbench = visual tool [OK]

Common Mistakes:

• Thinking CLI can only read data
• Believing Workbench requires internet
• Confusing backup and query roles

2. Which of the following is the correct way to start MySQL CLI from a terminal?

easy

A. mysql start

B. start mysql

C. mysql -u root -p

D. run mysql

Solution

1. Step 1: Recall MySQL CLI startup command

   The standard command to start MySQL CLI is 'mysql' with user and password options.

2. Step 2: Identify correct syntax

   'mysql -u root -p' means login as user root and prompt for password.

3. Final Answer:

   mysql -u root -p -> Option C

4. Quick Check:

   Start CLI with 'mysql -u user -p' [OK]

Hint: Use 'mysql -u username -p' to start CLI [OK]

Common Mistakes:

• Using 'mysql start' which is invalid
• Typing 'start mysql' which is not a command
• Forgetting the '-p' to prompt password

3. What will be the output when you run this command in MySQL CLI?
SHOW DATABASES;

medium

A. List of all tables in the current database

B. List of all databases on the MySQL server

C. Error: Command not found

D. Empty result set

Solution

1. Step 1: Understand the command purpose

   'SHOW DATABASES;' lists all databases available on the MySQL server.

2. Step 2: Differentiate from other commands

   'SHOW TABLES;' lists tables in current database, so not correct here.

3. Final Answer:

   List of all databases on the MySQL server -> Option B

4. Quick Check:

   'SHOW DATABASES;' = list databases [OK]

Hint: 'SHOW DATABASES;' lists all databases [OK]

Common Mistakes:

• Confusing databases with tables
• Expecting an error from a valid command
• Thinking it returns empty if no database

4. You try to run this command in MySQL CLI:
SELECT * FROM users
But you get an error. What is the most likely cause?

medium

A. The table 'users' does not exist in the current database

B. Missing semicolon at the end of the statement

C. You forgot to start MySQL Workbench

D. You typed 'SELECT' in lowercase

Solution

1. Step 1: Check syntax correctness

   Missing semicolon usually causes error, but MySQL CLI can accept multi-line input until semicolon is entered.

2. Step 2: Consider table existence

   If 'users' table is missing in current database, query fails with error.

3. Final Answer:

   The table 'users' does not exist in the current database -> Option A

4. Quick Check:

   Missing table = query error [OK]

Hint: Check if table exists before querying [OK]

Common Mistakes:

• Assuming lowercase SELECT causes error
• Thinking Workbench must be open for CLI
• Ignoring missing semicolon impact

5. You want to export a database using MySQL CLI and then open it in MySQL Workbench. Which command correctly exports the database named shopdb to a file backup.sql?

hard

A. mysqlexport -u root -p shopdb backup.sql

B. mysql -u root -p shopdb > backup.sql

C. mysqldump shopdb -u root -p backup.sql

D. mysqldump -u root -p shopdb > backup.sql

Solution

1. Step 1: Identify the export tool

   To export a database, 'mysqldump' is the correct command, not 'mysql'.

2. Step 2: Check command syntax

   'mysqldump -u root -p shopdb > backup.sql' exports 'shopdb' to 'backup.sql' file.

3. Final Answer:

   mysqldump -u root -p shopdb > backup.sql -> Option D

4. Quick Check:

   Use mysqldump with output redirection [OK]

Hint: Use mysqldump with > filename to export [OK]

Common Mistakes:

• Using 'mysql' instead of 'mysqldump' to export
• Wrong order of arguments in command
• Using non-existent 'mysqlexport' command