SQL, Standard Query Language, is a language used to query relational databases efficiently and manage data in an effective manner. CRUD operations form the backbone or core of MySQL.
MySQL is a relational database management system (RDBMS) that is open source and quite popular around the world because it is fast, reliable, flexible, quick processing, and has great community support backing it.
MySQL is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications. The most common use for MySQL, however, is in web databases, or WordPress (wp) to be more precise..
In addition, we have recently posted an article on MySQL Master-Slave Server Replication, so don't forget to read it if you like and follow this one. Since we know some of the major benefits and uses for context, let's move on. Today, we'll look at the CRUD operations in MySQL
This post is MySQL specific, but I also wore a post about how to connect to mysql with python and execute the crud operations.
What are CRUD Operations in MySQL?
CRUD stands for Create, Read, Update, Delete; in other words, CRUD operations refer to the most important operations in MySQL-- basic data manipulation in the database. We implement these foundational queries to work with data from the database. In this article, we'll look at the procedure to writing CRUD queries in MySQL with an IDE called POPSQL (pronounced pop-sicle). We consider CRUD queries a form of the DML (Data Manipulation Language), when working with MySQL.
Setting up the MySQL Workspace with PopSQL
First of all, let's decide on the workspace editor/ide. There are a plethora of options available like MySQL Workbench, DataGrip, VS Code etc, but I have chosen the ide that I love for this post. Therefore, to get started with the workspace, head to the PopSQL website and download the ide. However, you can use any editor/ide you wish because the queries itself won't change.
Before moving on, I'd like to let you know why we chose this particular ide for this blog. In other words, I want to share a few benefits of PopSQL: it is not only fast and efficient, but also extremely powerful with a beginner friendly UI.
We'll have to establish connection with localhost and use a database to start working.
What is the Create Operation in MySQL and What is its Role?
The Create operation is responsible for creating data objects in SQL like databases, tables, views etc. In addition, it allows the RDBMS to store data by providing the containers-- tables. Similarly, this data can later be accessed by a different operation. Create command itself is only responsible for creating objects like tables and the columns under which you may later enter data to your pleasing.
Understanding the CREATE Operation and its Syntax
Here's the syntax.
CREATE DATABASE database_name;
CREATE TABLE table_name (column_name data_type constraint(s));
CREATE TABLE mytable(
id int PRIMARY KEY,
first_name varchar(50),
last_name varchar(50) NOT NULL
);Let me try and break down this syntax in a way that will be easier to digest for you, the beginners. The key things to notice here... the casing of the words, the text outside the bracket, the text inside the bracket and a semi colon at the end.
The only thing you need to understand about the case-sensitivity of SQL or MySQL is that it doesn't care what case you use like C or Java or other programming languages. However, it is tradition to write SQL keywords like the one we are studying-- CREATE- in caps to differentiate the keywords and the table and column names at a glance, to make the code more readable.
The second thing to takeaway is the word table_name. For instance, when we write CREATE TABLE, it makes sense to provide an appropriate name for the new table for the database storage.
After this, we open parentheses and start defining the columns in our new table. Similarly, there is a proper format/syntax for the column definition as well --- column_name column_datatype constrains. In addition, we write down a name for the column, its datatype (date, char, varchar, number, int etc) and the character limit. After that, you write down any constraints you may want to implement on your column(s).For instance, if you want your salaries to be higher than the minimum wage or if you want your column to not accept blank or NULL values.
Here is how to create a database:
Here is how to write the query to create a table in MySQL.
What is the READ Operation and What Does It Do?
The Read operation is perhaps the most used one for database retrieval purposes. It primarily makes use of the SELECT keyword. Read retrieves data contents from the database objects, like tables. Read command only affects data in the output; it doesn't change the data in the database itself.
What is the SELECT keyword?
Select is a keyword in MySQL that retrieves the list of columns from a table in the database. It has six clauses:
- select: specify the column name(s)
- from: specify the table name(s)
- where: specify the condition to apply on individual records
- group by: specify the column name(s) to group query results
- having: specify the condition on the grouped records
- order by: specify the column name(s) to sort query results
Syntax:
SELECT col_name FROM table_name WHERE (condition) GROUP BY col_name HAVING (condition2 - only on x columns) ORDER BY col_name ASC/DESC
The Update Statement
Update is a SQL keyword that modifies the data of one or more rows in a table. In addition, it modifies the data in a table or the structure of the table(ALTER keyword). Let's look at two keywords involved with Update:
- UPDATE: modifies the data contents in database objects, like tables.
- ALTER: modifies the object structure. For instance, if you wish to add a new column to an existing table later on... Alter allows you to make that change. In addition, it is especially effective when you start working on a database that pre-exists and you need to make some changes.
However, we'll mainly focus on the Update keyword today.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
ALTER table_name
ADD COLUMN column_name data_type;
The DELETE statement
The Delete operation in MySQL removes content from a database objects, like tables or views. It deletes the data in one row or the whole table if a condition is not specified. It employs two relevant keywords:
- DELETE: used for the contents of the table i.e. to remove the data existing.
- DROP: used for the structure of objects like tables i.e. to drop a table means to delete it from the database, including all its contents.
The main difference also arises in memory storages, whereby delete only removes the content of a table, drop completely erases the table from the database, leading to memory optimization.
Syntax:
DELETE FROM table_name;
DELETE col_name FROM table_name;
DROP table_name;
Conclusion to Understanding CRUD operations in MySQL:
Maintaining a sound understanding of CRUD operations in MySQL, or SQL itself, is one of the most important things you can do as a beginner because this proves fruitful down the road... when you have this major concept stored in the back of your heads, you will not be confused writing these fundamental queries.
The best way to use CRUD operations in MySQL is with stored procedures, which are automated processes. This allows us to increase the efficiency and lower the time spent writing the same queries repeatedly.
In conclusion, I hope you find this post useful. Please share your thoughts in the comments below, and like the post if you learned something new. Subscribe to our blog to stay updated on all things related.