MariaDB Must Know Commands

MariaDB provides a wide range of tools and utilities to manage the database, and one of the most powerful tools is the command-line interface (CLI). In this blog post, we will discuss some must-know MariaDB commands that will help you manage your database more efficiently.

Logging into MariaDB:

To log into MariaDB, you can use the following command:

mysql -u username -p

Creating a New User:

To create a new user in MariaDB, you can use the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Creating a Database:

To create a database in MariaDB, you can use the following command:

CREATE DATABASE database_name;

Creating a Table:

To create a table in MariaDB, you can use the following command:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
);

For example, to create a table called customers with columns id, name, and email, you can use the following command:

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

Inserting Data into a Table:

To insert data into a table in MariaDB, you can use the following command:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

For example, to insert a record into the customers table, you can use the following command:

INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]');

Updating Data in a Table:

To update data in a table in MariaDB, you can use the following command:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

For example, to update the email of the customer with id 1 in the customers table, you can use the following command:

UPDATE customers SET email = '[email protected]' WHERE id = 1;

Deleting Data from a Table:

To delete data from a table in MariaDB, you can use the following command:

DELETE FROM table_name WHERE condition;

For example, to delete the record with id 1 from the customers table, you can use the following command:

DELETE FROM customers WHERE id = 1;

Querying Data from a Table:

To query data from a table in MariaDB, you can use the following command:

SELECT column1, column2, ... FROM table_name WHERE condition;

For example, to select all the records from the customers table, you can use the following command:

SELECT * FROM customers;

Listing Databases:

To list all the databases in MariaDB, you can use the following command:

SHOW DATABASES;

Deleting a Database:

To delete a database in MariaDB, you can use the following command:

DROP DATABASE database_name;

Renaming a Database:

To rename a database in MariaDB, you can use the following command:

ALTER DATABASE old_database_name RENAME TO new_database_name;

Granting Database Privileges to a User:

To grant a user privileges on a database in MariaDB, you can use the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Revoking Database Privileges from a User:

To revoke privileges from a user on a database in MariaDB, you can use the following command:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

Listing Users:

To list all the users in MariaDB, you can use the following command:

SELECT * FROM mysql.user;

Renaming a User:

To rename a user in MariaDB, you can use the following command:

RENAME USER 'old_username'@'localhost' TO 'new_username'@'localhost';

Changing a User’s Password:

To change a user’s password in MariaDB, you can use the following command:

ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

To exit MariaDB: Use either exit; or quit; then press enter.