Sling Academy
Home/MySQL/How to interact with MySQL using command line

How to interact with MySQL using command line

Last updated: January 25, 2024

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.

Next Article: How to Install and Configure MySQL on Ubuntu 23.04

Previous Article: How to use MySQL with Docker and Docker Compose

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples