MySQL CLI and Workbench

Introduction

MySQL CLI and Workbench help you manage databases easily. CLI lets you type commands directly, and Workbench gives a friendly visual way to work with your data.

You want to quickly run a simple database command.

You prefer typing commands to control your database.

You want to design or visualize your database structure.

You need to run complex queries with help from a graphical interface.

You want to export or import data using easy tools.

Syntax

MySQL

mysql -u username -p

-- or --

Open MySQL Workbench and connect to your database server

In CLI, '-u' means username and '-p' asks for your password.

Workbench provides buttons and menus to run queries and manage databases visually.

Examples

Open MySQL CLI as user 'root' and enter password when asked.

MySQL

mysql -u root -p

Run this query in CLI or Workbench to see all data in the 'employees' table.

MySQL

SELECT * FROM employees;

This lets you save connection details and open a visual interface.

MySQL

Use the 'New Connection' button in Workbench to connect to your database server.

Sample Program

This shows all databases on your MySQL server after you log in using CLI.

MySQL

mysql -u root -p
-- After login, run:
SHOW DATABASES;

Important Notes

CLI is fast and good for quick commands but needs you to remember commands.

Workbench is easier for beginners because it shows tables and data visually.

Always keep your password safe and never share it.

Summary

MySQL CLI lets you type commands directly to manage your database.

MySQL Workbench gives a visual way to work with databases and run queries.

Both tools help you see and change your data, but Workbench is more beginner-friendly.

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