How to perform CRUD operations with MySQL using Arctype your favourite SQL client
Introduction
Arctype is the fast and easy-to-use database client for writing queries, building dashboards, and sharing data with your team, it supports not only MySQL; it also supports Postgres and PlanetScale.
MySQL Database Service is a fully managed database service to deploy cloud-native applications.
MySQL gives developers the privilege of interacting with databases, in this article we are going to be considering the widely used operations regarding databases.
Explaining CRUD Operations
Organizations that keep track of customer data, accounts, payment information, health data, and other records require data storage hardware and applications that provide persistent storage. This data is typically organized into a database, which is simply an organized collection of data that may be viewed electronically.
There are many types of databases: hierarchical databases, graph databases, and object-oriented databases to name a few. The most commonly implemented type of database is a relational database, which consists of data tabled in rows and columns and connected to other tables with complementary information by a system of keywords that includes primary keys and foreign keys.
Why is CRUD so popular?
CRUD is constantly used for anything related to database and database design. Software developers can’t get anything done without CRUD operations. Website development, for example, uses REST (Representational State Transfer), which is a superset of CRUD used for HTTP resources.
On the other hand, CRUD is just as important for you as the end-users. Without it, things like registering and logging for websites, creating blogs, subscribing to Arctype or bookmarks would be impossible. Most applications we use let us add or create new entries, search for existing ones, make changes to them or discard them.
Some of the benefits CRUD offers includes:
- It facilitates security control by satisfying the various access requirements
- It simplifies and facilitates application design making it more scalable
- It has better performance compared to ad-hoc SQL statements
The CRUD acronym identifies all of the major functions that are inherent to relational databases and the applications used to manage them, which include MySQL, Postgre, PlanetScale, Oracle Database, Microsoft SQL Server, etc.
CREATE
Create allows us to add new rows to tables or create new tables. We can do that using the command, INSERT INTO. The command starts with the INSERT INTO keyword CREATE TABLE, followed by the table name, column names, and the values to be inserted.
When using INSERT INTO, you have two options:
INSERT INTO tbl_name VALUES (value1, value2, value3, …);or
INSERT INTO tbl_name (column1, column2, column3, …)VALUES (value1, value2, value3, …);
And when using CREATE TABLE, you use this:
CREATE TABLE tbl_name (col_name col_type constraints);Note:
If you have knowledge of Arctype before skip the link before else try reading the content before proceeding with this tutorial
How to establish a connection using Arctype your favorite SQL client
You may want to read about creating and running a Query with Arctype
use blog;CREATE TABLE articles (title Varchar(100) NOT NULL, commentCount Integer);
The Created Table Output
READ
The read function is similar to a search function, as it allows you to retrieve specific records and read their values. Read refers to as SELECT
For instance, let’s take a look at the articles we have at our blog. To do that, we have to display all the data in our menu table using:
use blog;SELECT * FROM articles;
The above Query will select or read(display) all the data from the database without making any changes to the data present on the database.
READ OPERATION OUTPUT
UPDATE
Update is how we change an existing record in the table. We can use this to modify existing records that exist in the database. When performing UPDATE, you need to define the target table and columns to be updated. You also need the associated values and sometimes the rows.
Two commands are widely used for Update data in the database –
Alter Table Command is also referred to as the DDL command (Data Definition Language) used to change the structure of the table while the Update Table Command is also referred to as the DML command (Data Manipulating Language) used to alter the records.
To update an existing record in our database, use the following:
Let’s say we want to update the article title and commentCount. We would use:
This will update the table so that the previous record whose id is 12 will now be replaced with ‘daddy is good’ with 400 commentCount.
DELETE
Delete is used to remove a record from the table. SQL has a built-in delete function for deleting one or more records from the database at a time. Some relational database applications may permit a hard delete (permanent delete) or soft delete (update row status).
The delete command is as follows:
DELETE FROM tbl_name WHERE condition;If we want to remove one article from our blog, we use:
DELETE FROM articles WHERE id = 12;This will remove the row with the article with id number 12 from the table. If you want to delete all the records from the table, you can use:
DELETE FROM articles;Conclusion
With Arctype SQL client you can easily manage all your databases with a click just by logging with your database credentials.
Try downloading arctype today and make your development faster
