Basic MySQL Commands in Linux

MySQL is one of the most well-known relational database management systems developers use worldwide. It is used to store data for applications and websites that contain large amounts of data. It is essential to understand the basic MySQL commands in order to handle and manipulate the database efficiently in the Linux environment.

In this article, we will learn how to master basic MySQL commands in Linux. This article will provide you with the knowledge and skills you need to effectively interact with MySQL databases, whether you are a beginner, looking for a refresher, or a professional.

Before diving into MySQL commands, make sure that MySQL is installed on your Linux computer. So, the very first step is to install MySQL on your Linux computer, log in to MySQL as a root user and connect to MySQL database to run commands.

Following are the basic MySQL command in Linux that you can use to do interaction with the database:

1. Accessing MySQL:

To access the MySQL command-line interface, open the MySQL command-line client and write the given command:

In place of 'username', you need to write the username of your MySQL. After executing the above statement, it will ask to enter the MySQL password.

2. Creating a Database:

To create a new database, we will use the given command:

In place of 'databaseName', you have to write the name of the database you want to create.

3. Selecting a Database:

To select a database and start working within it, we will use the given command:

In place of 'databaseName', you have to write the name of the database you want to select.

4. Creating a table:

To create a new table in the selected database, use the given command:

CREATE TABLE tableName (
    Column1 dataType,
    Column2 dataType,
    Column3 dataType,
    ...
);

In place of 'tableName', you need to write the name of the table you like to create. Set the column names and their data types within parentheses.

5. Inserting Data into a Table:

To insert data into the table, use the given command:

INSERT INTO tableName (Column1, Column2, Column3, Column4, ...)
VALUES (Value1, Value2, Value3, Value4, ...);

In place of 'tableName', you should write the name of the table you like to create. Specify the column names and their associated values.

6. Selecting data from the table:

To receive data from a table, use the given command:

SELECT Column1, Column2, ...
FROM tableName;

In place of 'Column1, Column2, ...', you have to write the name of the columns from which you like to get the data, and in place of 'tableName' write the name of the table.

7. Show tables:

To display the list of tables in the database, use the given command:

8. Describe a table:

To view the column names and data types of a table, use the following command:

In place of 'tableName', you should write the name of the table you want to describe.

9. Updating data from a table:

To update existing data in the table, use the following command:

UPDATE tableName
SET Column1 = Value1, Column2 = Value2, Column3 = Value3, ...
WHERE condition;

In place of 'tableName', you must type the name of the table which you want to update, specify the column names and their new values, and set the condition to identify the rows to update.

10. Deleting data from a table:

To delete data from the table, use the given command:

In place of 'tableName', you have to write the name of the table and define the condition to identify the rows to delete.

11. Dropping a database:

To delete the database, use the given command:

In place of 'databaseName', you have to write the name of the database.

12. Dropping a table:

To drop the table, use the following command:

In place of 'tableName', you have to write the name of the table.

13. Adding a new column in a table:

You can use the command below to change the structure of the table:

ALTER TABLE tableName
ADD columnName datatype;

In place of 'tableName', write the name of the table you want to alter and specify the new column name and data type.

14. Renaming a table:

To rename a table, use the command given below:

In place of 'oldTableName' write the current name of the table, and in place of 'newTableName' write the new name of the table you want.

15. Modifying Data Types of Columns:

To modify the data type of a column in a table, use the command given below:

ALTER TABLE tableName
MODIFY COLUMN column_name newDatatype;

Write the name of the table in place of 'tableName', write the name of the column in place of 'columnName', and write the data type you want in place of 'newDatatype'.

16. Limiting the number of rows in a table:

To limit the number of rows in an existing table, use the command given below:

SELECT Column1, Column2, ...
FROM tableName
LIMIT numberOfRows;

You must write the name of the table in place of 'tableName' and the desired limit in place of 'numberOfRows'.

17. Sorting of data in a table:

To sort the data in the table in ascending order, use the following command:

SELECT Column1, Column2, ...
FROM tableName
ORDER BY columnName ASC;

And to sort the data in the table in descending order, use the given command:

SELECT Column1, Column2, ...
FROM tableName
ORDER BY columnName DESC;

In place of 'tableName', write the name of the table, and in place of 'columnName', write the name of the column you want to sort on.

18. Primary Key Constraint:

A primary key distinctively identifies each record in a table. To define a primary key constraint, use the given command:

You must write the name of the table in place of 'tableName', and specify the column that you want to set as the primary key.

19. Adding a foreign key to a table:

To add a foreign key constraint to a table, use the given statement:

ALTER TABLE tableName
ADD CONSTRAINT constraintName FOREIGN KEY (columnName) REFERENCES referencedTable (referenceColumn);

You have to write the name of the table in place of 'tableName', write the name for the constraint in place of 'constraintName', write the name of the column to be referenced in place of 'columnName', write the name of the table being referenced in place of 'referencedTable', and write the corresponding column in the referenced table in place of 'referencedColumn'.

20. Creating a Backup:

To backup a database, use the given command:

You have to write the username of your MySQL in place of 'username', write the name of the database that you want to backup, and write the desired name for the backup file in place of 'backupFile.sql'.

21. Restoring a Backup:

To restore a database from a backup file, use the given command:

You have to write the username of your MySQL in place of 'username', write the name of the database you want to restore in place of 'databaseName', and write the path to the backup file in place of 'backupFile.sql'.

22. Counting rows in a table:

To count the number of rows in a table, use the given command:

You have to write the name of the table you want to count the rows in place of 'tableName'. The above statement will return the total count of rows in the specified table.

23. Joining tables:

To join rows from multiple tables based on a related column, use the given command:

SELECT Column1, Column2, ...
FROM Table1 JOIN Table2
ON Table1.Column = Table2.Column;

You must write the names of the tables you want to join in place of 'Table1' and 'Table2', and specify the related columns using the ON keyword.

24. Creating a new database user:

To create a new user in MySQL, use the given command:

You must write the desired username in place of 'username' and write the desired password in place of 'password'.

25. Truncating a table:

To delete all the data from the table, use the given command:

You have to write the name of the table you like to truncate in place of 'tableName'. The above statement will delete all the data from the table.

26. Creating an index:

To construct an index on one or more columns of the table, use the given command:

Write the desired name for the index in place of 'indexName', write the name of the table in place of 'tableName', and write the columns you desire to index.