Important MySQL Commands | How to Use MySQL Explained with Examples

Do you want to learn MySQL programming?

This is a complete tutorial where you will learn to create MySQL tables, inserting values into the tables and selecting values from the table.

For this tutorial, we are interested in the MySQL database. This tutorial is curated to understand the basics of MySQL programming.

At the end of this tutorial, you will learn to create your own MySQL database, to write different MySQL commands for performing various operations like inserting and retrieving information from the MySQL database.

Each of the operations is explained with examples so that you can execute the same SQL queries on your system.

Let’s start with the basics.

What is a MySQL Database?

We use a database for storing and retrieving data from the database.

MySQL is the most popular and widely used database management system.

Characteristics of MySQL Database:

• It is a relational database management system (RDBMS).
• In a relational database, data is stored in multiple tables. And each table has multiple columns and rows. Information in the table is stored in the row and column format.
• MySQL is an open-source database management system. It is free to use for personal and commercial projects.
• This database has its own syntax and programming languages for storing and retrieving information from the MySQL database table.
• It is used in many of the web application projects as a backend to store the data.

Before proceeding for practical, you have to install MySQL software on your local system. If you don’t have MySQL installed on your system, you can download and install it from its official website.

Let’s dive into practical.

How to use MySQL Commands and Queries?

Now we will see how to write MySQL database Queries to create a new database, inserting and selecting data from the table.

For this practical, we are taking an example of a database schema for a shoe store.

The database consists of three tables.

Shoes (Table 1)

Shoes(id, name, description, color1, color2, d_id)
Candidate Key: name
Foreign Key: d_id references Department(id)

Department (Table 2)

Department(id, name, description)
Candidate Key: name

Orders (Table 3)

Orders(id, shoe, size, quantity, has_arrived)
Foreign Key: shoe references Shoes(id)

Candidate key and foreign key are the two most important database keys.

Each table in the database has a candidate key. It helps to identify the row entry uniquely.

Each row from one table forms a relationship with the rows from another table using the foreign key.

Problem Statement: Create a MySQL database for Shoe store’s database schema.

How to create a MySQL database?

Follow the steps below to create a MySQL database.

To execute MySQL queries, you have to log in MySQL system with the database user.

mysql -u root -p

Enter the password.

Note: You can create different users with different privilege like reading, writing and updating MySQL database.

Here we are using the root user which has all the privileges.

Once you have logged in, you can create the database with a simple “CREATE DATABASE” command.

CREATE DATABASE shoes;

Here, “shoes” is the database name. You can use any valid name.

There can be multiple databases created on your system.

To use the particular database, you have to tell the system which database you are interested in.

USE shoes;

Now all the operations and commands will be performed on “shoes” database.

How to create MySQL Table?

Now we have created a database. Let’s see how to create a table in your database.

The table can be created using the “CREATE TABLE” MySQL query.

Syntax:

CREATE TABLE <table_name> (<column 1, column 2, ...>);

For every column, we can specify the data types and other properties. Datatypes can be INT, date, varchar…

How to create a MySQL table with a primary key?

Example: Creating Department Table

CREATE TABLE Department(
id INT NOT NULL PRIMARY KEY, 
name varchar(100) NOT NULL UNIQUE, 
description varchar(100));

This table has three columns named id, name, and description.

• The column ‘id’ is of integer type (INT). It is the primary key.
• The column ‘name’ and ‘description’ are varchar type. Every value in the column can hold up to 100 characters.
• The value for the column “name” should not be null and should be unique.

Note: Similar to varchar, there are char and varchar2 datatypes. You can read more about the difference between char, varchar and varchar2 data types in MySQL.

How to create a MySQL table with Foreign key?

Example: Let’s creating Shoes Table which has a foreign key referring to the primary key of the Department table.

CREATE TABLE Shoes(
id INT NOT NULL PRIMARY KEY, 
name varchar(100) NOT NULL UNIQUE, 
description varchar(100), 
color1 varchar(100), 
color2 varchar(100), 
d_id INT, 
FOREIGN KEY (d_id) REFERENCES Department(id));

Example: Creating Orders Table which has a foreign key referring to the primary key of table Shoes.

CREATE TABLE Orders(
id INT NOT NULL PRIMARY KEY, 
shoe INT, 
size varchar(100), 
quantity INT, 
has_arrived varchar(100), 
FOREIGN KEY (shoe) REFERENCES Shoes(id));

This is how the output looks like on MySQL command prompt for creating MySQL tables.

How to show the created tables?

As I told you earlier, there can be multiple tables inside the database. We have created three tables (Shoes, Orders, Departments) inside the database “shoes”.

Use “show tables” command.

show tables

It will list out all the tables created under the current database.

How to insert values in MySQL table?

You have created tables. Awesome!

All the tables are empty unless you insert values into them.

Use the “INSERT INTO” command to add values in the MySQL table.

Example: Inserting values in the Department table we created earlier

INSERT INTO Department
VALUES
(501,"dept1","desc dept1"),
(502,"dept2","desc dept2"),
(503,"dept3","desc dept3");

With the above single query, you are adding three rows in the Department table. You can add as many rows as you want.

Note: The first column ‘id’ is a primary key. The values in the first columns (501, 502, 503) should be unique.

Example: Inserting values in the “Shoes” table

INSERT INTO Shoes
VALUES
(701,"shoe1","desc shoe1", "red", "blue", 501),
(702,"shoe2","desc shoe2", "red", "orange", 502),
(703,"shoe3","desc shoe3", "yellow", "red", 502),
(704,"shoe4","desc shoe4", "white", "black", 503),
(705,"shoe5","desc shoe5", "blue", "red", 502),
(706,"shoe6","desc shoe6", "balck","white", 501),
(707,"shoe7","desc shoe7", "yellow", "white", 502);

Note: All the values you are adding for the foreign key (last column) should be present in the candidate key column (id) of the parent table (Department).

Example: Inserting values in the Orders table

INSERT INTO Orders
VALUES
(901,701,"size L", 10, "YES"),
(902,702,"size M", 9, "YES"),
(903,704,"size L", 10, "NO"),
(904,704,"size L", 10, "NO"),
(905,705,"size S", 1, "YES"),
(906,701,"size XL", 10, "YES"),
(907,705,"size M", 10, "NO"),
(908,705,"size L", 3, "YES"),
(909,707,"size M", 3, "YES"),
(910,702,"size XL", 2, "NO"),
(911,701,"size S", 4, "YES"),
(912,704,"size XL", 4, "YES"),
(913,704,"size L", 11, "NO"),
(914,705,"size M", 1, "NO"),
(915,706,"size L", 10, "YES");

Now all the three tables are populated with some data in the form of rows.

SELECT MySQL Queries

Your application has to read the data from the table to use.

You need a “SELECT” command to fetch the data from the table.

How to use MySQL commands to select and display entries in the MySQL table?

Example: Write MySQL command to list all of the information stored in the “Department” table.

SELECT * from Department;

Start sign ‘*’ is used to print all the columns from the table.

Similar commands you can write for the other two tables.

Write MySQL command to list all of the information stored in the “Shoes” table.

SELECT * from Shoes;

Write MySQL command to list all of the information stored in the Orders table.

SELECT * from Orders;

These are the simple SELECT queries. If you are working on any database project, 90% of the time you will be using SELECT queries.

Along with basic SELECT queries, you can use different conditions, clauses, filters, and functions. Learn more about SELECT queries.

How to list values of a particular column?

Instead of selecting all columns, you can also select specific columns. Mention column names instead of * in the “SELECT” query.

Query: List the shoe, size, and quantity for all orders in the Order table.

SELECT shoe, size, quantity from Orders;

Let’s see some examples where we are adding constraints and filters to the “SELECT” query.

MySQL commands for select with constraint queries:

Query: List all the order information for shoe orders that have a quantity of at least 2.

SELECT * from Orders having quantity>2;

Here,

• We are using a “having” clause.

Query: Show all the shoes that have “blue” as color1 or color2.

SELECT * from Shoes where color1 like "blue" or color2 like "blue";

Here,

• The “like” keyword is used to compare the strings.
• The “or” is a logical operator.

Query: Show all the shoes having color blue and white.

SELECT * from Shoes where color1 IN ('blue', 'white')

Here we are using IN operator to filter the rows.

Query: Print the number of departments that exist in the database.

SELECT COUNT(*) FROM Department;

COUNT() is an aggregator function that can be used with the MySQL select query.

There are many aggregate functions available in SQL as below.

• Max() – Returns the maximum value.
• Min() – Returns the minimum value.
• Avg() – Returns the average value.
• Count() – Returns the number of rows.
• Sum() – Returns the sum of all values.

How to drop or delete the MySQL table?

While working on the database project, you create multiple tables. As you progress, you realize there are some unwanted tables that are not required.

To delete any unwanted table, you can use the “DROP Table” command.

DROP TABLE table_name

The table “table_name” is no longer exist in your current database.

How to drop or delete the MySQL database?

Similar to dropping table, you can also drop the complete database.

Run the following the command.

DROP DATABASE database_name

Note: Once you drop the database, all the tables in the database will be deleted and all the information from the table will vanish. Once you deleted the database, you can not restore it back.

Whether you are dropping table or database, be more careful while using DROP commands.

In this practical guide, we have seen all the basic MySQL commands and queries. You can integrate these SQL commands with server-side programming languages like PHP, Java, Python…

If you have any query related to MySQL, let’s discuss in the comment section.

Happy Programming!

Aniruddha Chaudhari

I am complete Python Nut, love Linux and vim as an editor. I hold a Master of Computer Science from NIT Trichy. I dabble in C/C++, Java too. I keep sharing my coding knowledge and my own experience on <b>CSEstack.org</b> portal.