Working with MySQL Database from the Command Line in Linux

Step 1: Opening the Terminal

Open your terminal. This is where you’ll enter your commands to interact with MySQL.

Step 2: Logging into MySQL

1. Login Command: Use the following command to log into MySQL. Replace username with your MySQL username (usually root) and password with your actual password.mysql -u username -pAfter pressing Enter, you’ll be prompted to enter the password.

Step 3: Basic MySQL Commands

Show Databases

To view all databases:

SHOW DATABASES;

Create a Database

To create a new database named exampledb:

CREATE DATABASE exampledb;

Use a Database

Select the exampledb database to perform operations on it:

USE exampledb;

Create a Table

Create a table named users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Show Tables

To list all tables in the current database:

SHOW TABLES;

Describe a Table

Get details about the structure of a table:

DESCRIBE users;

Step 4: Working with Data

Insert Data

Add a new record into the users table:

INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');

Select Data

Retrieve all records from the users table:

SELECT * FROM users;

Retrieve specific columns:

SELECT name, email FROM users;

Retrieve records with conditions:

SELECT * FROM users WHERE name='John Doe';

Update Data

Update existing records:

UPDATE users SET email='john.doe@example.com' WHERE name='John Doe';

Delete Data

Remove records from a table:

DELETE FROM users WHERE name='John Doe';

Step 5: Exiting MySQL

To exit the MySQL command line, type:

EXIT;

or

QUIT;

Additional Tips

• Backup and Restore: Use the mysqldump command to back up databases and the mysql command to restore.
• Help: Use HELP in the MySQL command line for assistance or \h for a list of commands.
• Security: Never expose your passwords on the command line directly. Use secure methods or configuration files to handle credentials safely.

By following these steps and examples, you should be able to manage and manipulate MySQL databases directly from the Linux command line effectively.