Create database and perform CRUD operations in MySQL server
In this chapter, we will discuss in detail about MySQL data types, create tables for the travel application and then perform CRUD operations on those tables. As we have already discussed in our previous chapter, MySQL is based on Structured Query Language (SQL). Let's start our chapter with the most commonly used data types in CRUD applications.
MySQL Data Types
A data type indicates the kind of data, it will be storing when defining a table column. MySQL supports several SQL data types in the category of numeric, date & time, string, spatial and JSON. While creating a table, appropriate selection of data types is also very important as it can influence the performance of the MySQL server to some extent. Some of the most commonly used data types in SQL are as follows:
|Numeric - Integer||TINYINT||A very small integer value
(0 to 255) for unsigned
(-128 to 127) for signed
|Numeric - Integer||INT||A default integer value
(0 to 4294967295) for unsigned
(-2147483648 to 2147483647) for signed
|Numeric - Integer||BIGINT||A very large integer value
|Numeric - Floating Point||FLOAT||A single precision floating point number
|Numeric - Floating Point||DOUBLE||A double precision floating point number
(upto 53 decimal precision)
|Numeric - Floating Point||DECIMAL||To store exact numeric values, we can use decimal.
(DECIMAL(M, D) -> M = digits and D = decimal point)
Maximum number of digits for DECIMAL is 65 and for decimal point is 30
Ex: for storing monetary values
|Date||DATETIME||Used for storing both the date and time information
(stores in YYYY-MM-DD hh:mm:ss format)
(range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59)
When we display value for datetime, it is not converted to current timezone
it stores and displays the server timezone
|Date||TIMESTAMP||Used for storing both the date and time information
(stores in YYYY-MM-DD hh:mm:ss format)
(range is 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC)
When we display value for timestamp, it is converted to current timezone
|Date||DATE||Used for storing only the date information
(stores in YYYY-MM-DD format)
|Date||TIME||Used for storing only the time information
(stores in hh:mm:ss format)
|String||CHAR(N)||A fixed length string as defined with N
(upto 255 characters)
By default, it uses N as 1.
After defining a length with CHAR, even if data stored is less than specified length, it still takes up the specified length.
|String||VARCHAR(N)||A variable length string.
Length must be defined.
Length defined is the maximum limit for the characters. Based on the stored data, length of the field changes.
|String||TEXT||Used for storing large amounts of data. Use only if a large data (sizes in KBs, MBs or higher) needs to be stored|
|String||ENUM||Used for storing one of the values from a list of pre-defined options
Can list upto 65535 values in an ENUM
|Boolean||BOOLEAN/BOOL||Synonym for TINYINT(1) for storing boolean values.
Here, for true, it stores 1 and for false, it stores 0
|json||JSON||For storing json data|
Entity Relationship Diagram for Database
Below is the Entity Relationship Diagram (ERD) for our travel application. ER Diagrams are most commonly used to model and design relational databases in the field of software engineering. It is composed of entities, relationships and attributes. It has several benefits. Some of the benefits are as follows:
- Since it represents the visual presentation of the database design, just by seeing it, we can easily understand the database system along with the relationship between different entities.
- There is saying "A picture is worth a thousand words", ER Diagrams are a great way to communicate the database system to a wide group of personnels in a software engineering team. Since it's very easy to understand, most of the people are already familiar with it.
- From ER Diagram tools, we can easily generate scripts to create the database quickly.
Following ER Diagram is created using Lucid Charts.
Now that, we have gone through ER Diagram, we can start creating databases and interact with it.
Connect to the mysql server using following command (It will prompt for the password for username administrator):
mysql -h localhost -P 3306 -u administrator -p
Refer to the following link if the user with username administrator does not exists:
To show the list of existing databases:
To create a new database:
CREATE DATABASE databaseName;
Let's create travel_app database if it is not created already.
CREATE DATABASE travel_app;
If database already exists, it will throw an error as shown in the above image. To create a new database only if it doesn't exists already:
CREATE DATABASE IF NOT EXISTS databaseName;
Now that the database is already created, we can directly connect to the travel_app database using following command:
mysql -h localhost -P 3306 -u administrator travel_app -p
Since by default, localhost is used as host and port 3306 is used as default port, we can skip both of these values and issue the following command to connect to the travel_app database.
mysql -u administrator travel_app -p
Enter the password and we are ready to start interacting with the database. To show the list of tables associated with a database:
Since we haven't created any tables for travel_app database, it will give us following result in the response:
Empty set (0.00 sec)
We can use CREATE TABLE statement to create a new table in the database. Following is the basic syntax:
CREATE TABLE table_name ( column_name column_definition_1, ..., ..., column_name column_definition_n, );
To create a table only if it doesn't exists already:
CREATE TABLE IF NOT EXISTS table_name ( column_name column_definition_1, ..., ..., column_name column_definition_n, );
When creating a column in the table, column_definition can contain data type, constraint, index name and others. We can specify data type as one of the type from the list defined in above data types table, as well as, also assign the NULL or NOT NULL keyword to the column. NULL allows null values to be stored in a column while NOT NULL requires some kind of data to be stored in the column as per the data type. If both are not specified, then the column is treated as the one defined with NULL option. Also, we can specify the default values for the column using DEFAULT keyword.
In the above ER diagram, we can see two keywords in all of the entities - PK and FK. PK stands for Primary key and FK stands for Foreign key. They are two types of constraints that can be used to enforce data integrity in database tables.
Primary Key Constraint
A primary key is a column or group of columns that contain values which uniquely identifies each row in the table. When we associate primary key constraint to a column, it applies the UNIQUE constraint by default as well as makes the column not nullable meaning we have to supply some kind of data for that column.
Foreign Key Constraint
A foreign key is used to a create relationship or references between two tables. Usually, we use primary keys for creating a foreign key constraint in another table but it doesn't always have to be the primary key constraint for creating a relationship with another table. Any columns with UNIQUE constraint applied to it can also be used.
There is another constraint which can be used to maintain the uniqueness in non primary key columns.
If we want to make sure that all the values are unique in a table column, we can use UNIQUE constraint. It allows for the null value if there is NULL keyword assigned to it or both NULL and NOT NULL keywords are not specified. In our below CREATE TABLE statement, we have assigned a UNIQUE constraint to email column.
Let's create all the tables for our travel application, as shown in above ER Diagram, using following commands:
CREATE TABLE `users` ( `user_id` varchar(36), `first_name` varchar(64) NOT null, `last_name` varchar(64) NOT null, `email` varchar(48) UNIQUE NOT null, `password` varchar(128) NOT null, `added_on` datetime DEFAULT NOW(), PRIMARY KEY (`user_id`) ); CREATE TABLE `hotels` ( `hotel_id` varchar(36), `name` varchar(180) NOT null, `description` varchar(2000) NOT null, `cover_image_url` varchar(180), `amenities` varchar(220), `address` varchar(220) NOT null, `is_published` bool DEFAULT false, `guest_capacity` int NOT null, `hotel_type` varchar(48) NOT null, `avg_rating` float, `added_by` varchar(36) NOT null, `added_on` datetime DEFAULT NOW(), PRIMARY KEY (`hotel_id`), FOREIGN KEY (`added_by`) REFERENCES `users`(`user_id`) ); CREATE TABLE `bookings` ( `booking_id` varchar(36), `date_from` date NOT null, `date_to` date NOT null, `added_on` datetime DEFAULT NOW(), `total_amount` decimal(6,4) NOT null, `user_id` varchar(36) NOT null, PRIMARY KEY (`booking_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ); CREATE TABLE `room_bookings` ( `room_booking_id` varchar(36), `booking_id` varchar(36) NOT null, `room_id` varchar(36) NOT null, `quantity` int NOT null, PRIMARY KEY (`room_booking_id`), FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`booking_id`) ); CREATE TABLE `rooms` ( `room_id` varchar(36), `title` varchar(180) NOT null, `price` decimal(6,4) NOT null, `description` varchar(1200) NOT null, `amenities` varchar(220), `added_by` varchar(36) NOT null, `added_on` datetime DEFAULT NOW(), `hotel_id` varchar(36) NOT null, PRIMARY KEY (`room_id`), FOREIGN KEY (`hotel_id`) REFERENCES `hotels`(`hotel_id`), FOREIGN KEY (`added_by`) REFERENCES `users`(`user_id`) );
To verify that the tables are created successfully, we can use SHOW TABLES statement as shown below:
We can view detailed information about the table using following syntax:
Let's see detail info about the hotels table:
Now that, all of our tables are created, we can connect to the MySQL server using writer username, which we created in our previous chapter. It has all the permissions to perform CRUD operations in the travel_app database.
mysql -u writer travel_app -p
Creating records in a table
We can use INSERT INTO statement to create a new records in the table. There are multiple ways in which we can use INSERT INTO statement.
We specify both the column names and the values associated with those columns in the same exact order. This is the recommended approach as it's more readable and readability is also one of the most important aspects of software engineering.
INSERT INTO tableName (column_name_1, ..., column_name_n) VALUES (column_value_1, ..., column_value_n);
To insert multiple records using a single INSERT INTO statement:
INSERT INTO tableName (column_name_1, ..., column_name_n) VALUES (column_value_1, ..., column_value_n), (column_value_2, ..., column_value_2n);
If we are inserting values into all the columns defined in a table, then we can skip the column names part and directly specify only the values in the SQL query. Here, we need to make sure that values should match the order of column names defined in the table as shown below:
INSERT INTO tableName VALUES (column_value_1, ..., column_value_n);
Let's insert some data in our tables:
In users table:
INSERT INTO `users` (`user_id`, `first_name`, `last_name`, `email`, `password`) VALUES ("056a3ce0-dc51-4c26-88fe-809a5a7a48b4", "John", "Doe", "email@example.com", "this_is_secure_password");
In hotels table:
INSERT INTO `hotels` (`hotel_id`, `name`, `description`, `cover_image_url`, `amenities`, `address`, `guest_capacity`, `hotel_type`, `avg_rating`, `added_by`) VALUES ("d347d745-c10a-4aa6-9d88-110be9145f93", "Hyatt Regency", "This is detailed description", "https://www.nodexplained.com/sample.webp", "amenities description", "Kathmandu, Nepal", 200, "5*****", 0, "056a3ce0-dc51-4c26-88fe-809a5a7a48b4");
We can skip all the columns with DEFAULT values assigned to it. You can now insert data in rooms table in the same way as we did above.
Selecting records from a table
The SELECT statement is used to select data from a table. If we want to select all the columns defined in a table, then we can do it in the following way:
SELECT * FROM tableName WHERE condition
If we want to select only the certain columns of a table, then we can specify those column names in a SELECT statement. Most of the time, we only need to select certain fields and in those scenarios, this way is the recommended approach, as it enhances the speed of the SELECT queries.
SELECT column_name_1, ..., column_name_n FROM tableName WHERE criteria
Let's select all the columns from users table.
SELECT * FROM users;
Let's select certain columns from hotels table.
SELECT hotel_id, name, description, cover_image_url, address, hotel_type FROM hotels;
We can use WHERE clause to filter data. Let's filter the users list by email in the following way:
SELECT * FROM users WHERE email="firstname.lastname@example.org";
The above SELECT statement will return only the records having email value equals to email@example.com
We can also search for records using multiple fields in the WHERE clause. Let's search for users record for whom both email = firstname.lastname@example.org and first_name = John matches. It can be done in following way:
SELECT * FROM users WHERE email="email@example.com" AND first_name="John";
To select all users for whom either email = firstname.lastname@example.org or first_name = John matches. If either of those column matches with the specified values, then it will return results.
SELECT * FROM users WHERE email="email@example.com" OR first_name="John";
To select all users whose email is not firstname.lastname@example.org:
SELECT * FROM users WHERE email!="email@example.com";
Modifying records in a table:
The UPDATE statement is used to modify existing data in a table. Following is the syntax:
UPDATE tableName SET column_field_1_name = column_field_1_value, ..., ..., column_field_n_name = column_field_n_value WHERE condition
Let's update the certain fields of a hotel table using hotel_id in the WHERE clause. This will always update a single record as we are using primary key hotel_id in the WHERE clause.
UPDATE `hotels` SET `name` = "Hyatt Regency Nepal", `is_published` = true, `guest_capacity` = 5000, `avg_rating` = 0.9 WHERE `hotel_id` = "d347d745-c10a-4aa6-9d88-110be9145f93";
Removing records from a table:
The DELETE statement is used to delete existing data from a table.
DELETE FROM table_name WHERE condition;
Let's delete a record from hotels table.
DELETE FROM `hotels` WHERE hotel_id = "d347d745-c10a-4aa6-9d88-110be9145f93";
If we need to delete all records from a table, we can skip WHERE clause as following:
DELETE FROM `hotels`;
Now to verify the delete action, let's issue following command:
SELECT * FROM hotels;
Result is as follows:
Empty set (0.00 sec)
That's it for the CRUD operations part.
Adding/Removing columns from a table
Suppose, we want to add some additional columns to the existing table. To do that, we need to use ALTER TABLE statement with ADD keyword to add columns. To use ALTER TABLE statement, we need to connect to the server using administrator username, writer username do not have enough permissions to perform the action. Following is the syntax:
ALTER TABLE tableName ADD column_definition
Let's add two more additional columns in hotels table:
ALTER TABLE hotels ADD hotel_chain VARCHAR(128) NOT NULL;
ALTER TABLE hotels ADD policies VARCHAR(264) NOT NULL;
We can combine both of the above ALTER TABLE statements into a single place in the following way:
ALTER TABLE hotels ADD hotel_chain VARCHAR(128) NOT NULL, ADD policies VARCHAR(264) NOT NULL;
What if we want hotel_chain to occur after certain field say - description column, we can do it using AFTER keyword in the following way:
ALTER TABLE hotels ADD hotel_chain VARCHAR(128) NOT NULL AFTER description, ADD policies VARCHAR(264) NOT NULL;
To remove a column from an existing table, we can use ALTER TABLE statement with DROP keyword in the following way:
ALTER TABLE tableName DROP column_name
Let's drop cover_image_url column from hotels table:
ALTER TABLE hotels DROP cover_image_url;
Let's verify the addition of new columns and deletion of existing columns:
Since we are using foreign key constraints, to drop a table, we need to first remove the foreign key constraint or drop the table having foreign key. Following is the syntax:
DROP TABLE tableName; or DROP TABLE IF EXISTS tableName;
To drop a database, following is the syntax:
DROP DATABASE databaseName; or DROP DATABASE IF EXISTS databaseName;
In our next chapter, we will discuss about the various tools that will help us massively when interacting with the MySQL databases.