Basic MySQL Commands

⚠️This article is applicable to MySQL 8.0.X version. Please use the command mysql --version to confirm if MySQL is installed before use.

Log in to the MySQL database#

Use the command mysql -h localhost -u root -p
Where:

-u is followed by your MySQL username.
-p indicates that you intend to enter a password. Note that there is no space between -p and the password. If no password is entered, the system will prompt you to enter a password when you press Enter.
-h parameter is used to specify the MySQL server we want to connect to, which is located at localhost. If the MySQL server is on a different host or remote server, you can replace localhost with the hostname or IP address of that server.

Backup and restore MySQL databases#

Backup database#

Use the mysqldump tool to backup the database. The basic syntax is as follows:

mysqldump -u [username] -p[password] [database_name] > [backup_file.sql]

[username]: Your MySQL username.
[password]: Your MySQL password. Note that there is no space between -p and the password.
[database_name]: The name of the database you want to backup.
[backup_file.sql]: The name of the backup file.

For example: mysqldump -u root -p123456 mydatabase > mydatabase_backup.sql

The above command will backup the mydatabase database to the mydatabase_backup.sql file.

Backup and restore MySQL databases#

For example:
mysql -u root -p123456 mydatabase < mydatabase_backup.sql
The above command will restore the data from the mydatabase_backup.sql file to the mydatabase database.
Please note that when restoring a database, the database should already exist. If it has not been created yet, you need to create the database first.

Create and view databases#

Create a database#

In MySQL, you can use the CREATE DATABASE statement to create a new database. For example, to create a database named "my_database", you can use the following command:
CREATE DATABASE my_database;

View databases#

To view all databases, you can use the SHOW DATABASES command:
SHOW DATABASES;
To view a specific database, you can first use the USE command to select the database, and then use the SHOW TABLES command to view all tables in that database:

USE my_database;
SHOW TABLES;

Create, view, and delete tables#

Create a table#

Use the CREATE TABLE statement to create a new table. For example, to create a table named "my_table" in the "my_database" database, you can use the following command:

USE my_database;
CREATE TABLE my_table (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (id)
);

View a table#

To view the structure of a table, you can use the DESCRIBE or DESC command:

DESCRIBE my_table;

Delete a table#

Use the DROP statement: (This will completely delete the table without preserving the structure)

DROP TABLE table_name;

Use the DELETE statement:

DELETE FROM table_name;

Use the TRUNCATE statement:

TRUNCATE TABLE table_name;

Here, table_name is the name of the table from which you want to delete data. Both of these commands have the same effect, which is to delete all data in the table while preserving the table structure. However, TRUNCATE is usually faster and resets the AUTO_INCREMENT counter of the table. When deleting a large amount of data, TRUNCATE is the better choice.
Constraints are used to specify the allowed data in a table. When creating or modifying the structure of a table, you can add constraints to each column. The following are common constraints in MySQL:

MySQL Constraints#

PRIMARY KEY#

Column-level primary key constraint
Define a column as the primary key when defining the table columns:

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Table-level primary key constraint
Define a column or multiple columns as the primary key after defining all table columns:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

If you want to create a composite primary key (a primary key composed of two or more columns), you can only define it at the table level:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID, LastName)
);

UNIQUE#

The unique constraint ensures that each row in a column must have a unique value.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL UNIQUE,
    FirstName varchar(255),
    Age int
);

NOT NULL#

The NOT NULL constraint ensures that a column cannot have a NULL value.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

FOREIGN KEY#

The foreign key constraint is used to prevent breaking the link between two tables.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(ID)
);

MySQL CRUD, Grouping and Aggregation Functions#

SQL Operations#

Create a table#

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

The CREATE TABLE statement is used to create a new table in the database. It allows you to specify the name of the table and the name of each column in the table.

Insert data#

Insert directly

INSERT INTO table_name
VALUES (value1, value2);

Insert with specified columns

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

Insert multiple data at once

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
(value1, value2, value3, ...),
(value4, value5, value6, ...),
(value7, value8, value9, ...);

Modify table structure in MySQL#

In MySQL, we use the ALTER TABLE command to modify an existing table structure. Here are some commonly used ALTER TABLE commands:

Add a column

ALTER TABLE table_name
ADD column_name datatype;

Drop a column

ALTER TABLE table_name
DROP COLUMN column_name;

Modify column type

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Rename a column

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

Add a primary key

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Drop a primary key

ALTER TABLE table_name
DROP PRIMARY KEY;

Add an index

ALTER TABLE table_name
ADD INDEX index_name (column_name);

Drop an index

ALTER TABLE table_name
DROP INDEX index_name;

Rename a table

ALTER TABLE old_table_name
RENAME TO new_table_name;

Delete records#

DELETE FROM table_name
WHERE some_column = some_value;

The DELETE statement is used to delete records (rows) from a table. The WHERE clause specifies which record or records should be deleted. If the WHERE clause is omitted, all records will be deleted.

Update records#

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

The UPDATE statement is used to edit records (rows) in a table. It includes a SET clause that specifies which columns to edit and a WHERE clause to specify the records.