How to interact with MySQL using command line

Introduction

Interacting with MySQL through the command line is a fundamental skill for any database administrator or developer. This tutorial will walk you through the basics of using the MySQL command line interface (CLI) to advanced operations. We’ll start with how to access the CLI and end with some sophisticated commands for database management. Make sure you have MySQL installed on your machine before beginning.

Accessing MySQL CLI

$ mysql -u username -p

When prompted, enter your MySQL user password. You will then be logged into the MySQL monitor where you can start running commands.

Basic MySQL Commands

The following are fundamental commands to manage databases and tables:

Showing databases:

SHOW DATABASES;

This lists all databases on the MySQL server instance.

Creating a database:

CREATE DATABASE example_db;

This command creates a new database named ‘example_db’.

Selecting a database:

USE example_db;

This command switches to the ‘example_db’ database for subsequent operations.

Creating a table:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

This command creates a ‘users’ table with an auto-incrementing ‘id’ and a ‘username’ column.

Showing tables:

SHOW TABLES;

This lists all tables in the current database.

Working with Data

Once your table is ready, you can start inserting and querying data.

Inserting data:

INSERT INTO users (username) VALUES ('Alice');

This adds a new row to the ‘users’ table with the username ‘Alice’.

Selecting data:

SELECT * FROM users;

This selects all rows and columns from the ‘users’ table.

Updating data:

UPDATE users SET username = 'Alicia' WHERE id = 1;

This updates the username to ‘Alicia’ for the row where the id is 1.

Deleting data:

DELETE FROM users WHERE id = 1;

This deletes the row from the ‘users’ table where the id is 1.

Batch Operations and File Import/Export

MySQL allows for batch operations and data import/export through the CLI.

Loading data from a file:

LOAD DATA INFILE 'data.txt' INTO TABLE users;

This command loads data from ‘data.txt’ into the ‘users’ table.

Exporting data to a file:

SELECT * FROM users INTO OUTFILE 'data_backup.txt';

This exports data from the ‘users’ table into a file named ‘data_backup.txt’.

Advanced Operations

MySQL provides various advanced operations such as transactions, indexing, and user management.

Transactions:

START TRANSACTION;
INSERT INTO users (username) VALUES ('Bob');
ROLLBACK;

This example demonstrates a transaction where the insertion is rolled back and hence not committed to the database.

Indexing:

CREATE INDEX username_index ON users (username);

Creating an index on the ‘username’ column can improve the performance of queries involving the username field.

User Management:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON example_db.* TO 'new_user'@'localhost';

This creates a new user with full privileges on the ‘example_db’ database.

Conclusion

Understanding how to interact with MySQL from the command line allows for quick database management without a GUI. With the basics covered in this tutorial, you should feel more comfortable executing common database tasks through the CLI.