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.
Table of Contents
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:
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.
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.
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.
Now all the operations and commands will be performed on “shoes” database.
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.
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…
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.
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.
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.
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.
It will list out all the tables created under the current database.
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.
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;
Query: Show all the shoes that have “blue” as color1 or color2.
SELECT * from Shoes where color1 like "blue" or color2 like "blue";
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.
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.
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.