What are CRUD operations in SQL?

Overview

CRUD is a popular acronym for create, read, update, and delete. It’s the foundation of any programming language or framework.

In this shot, we’ll cover:

• Prerequisites
• How to work with tables
• How to work with table content
• Wrap up

Let’s get started!

Prerequisites

• Install a database management system (MySQL, PostgreSQL…)
• Connect to the database management system and create a database. This process is shown below:

-- connect to MySQL
mysql -uroot -p
-- enter password, hit Enter

-- create a database
CREATE DATABASE test_crud;

-- select the database
USE test_crud;

How to work with tables

Create at least one database and select it as explained above.

Syntax

The syntax looks like this:

CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
);

Here, datatype can be serial, VARCHAR, etc., and constraints can be PRIMARY KEY, UNIQUE, and so on.

Example

Let’s look at a real example:

Read

To see the list of all tables, we use:

SHOW TABLES;

Update

There are many possibilities here. We can:

• Rename a table or a column
• Add or delete a column

Rename

1. Table

ALTER TABLE table_name
   RENAME TO new_table_name;

2. Column

ALTER TABLE table_name
   CHANGE COLUMN old_name TO new_name;

How to add or delete a column

1. Add a new column

ALTER TABLE table_name
   ADD column_name column_definition
      [ FIRST | AFTER column_name ];

• column_definition: The data type and definition of the column (NULL or NOT NULL).

• FIRST | AFTER column_name: This is optional. It tells MySQL where in the table to create the column. If not specified, the new column is added to the end of the table.

2. Delete a column

ALTER TABLE table_name
  DROP COLUMN column_name;

Delete a table

DROP TABLE IF EXISTS table_name;

How to work with table content

In this section, we’ll learn how to work with entries (data) in a table. CRUD operations are equivalents to the following statements:

• INSERT
• SELECT
• UPDATE
• DELETE

INSERT INTO statement

The syntax to insert data into a table is:

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);

Let’s insert five entries into our table (users):

INSERT INTO users(username, password, email)
VALUES
('bam', 'B@m:321', 'bam@ulikis.cd'),
('salimas', 'sal1989', 'salimas@gmail.com'),
('pati', 'kav87', 'reseacher@unikis.cd'),
('yves', '1v3s:321', 'yves@yves.com'),
('val', 'valnas123', 'valnas@gmail.com')
;

Here, we insert multiple entries at once. We ignored the id column because SQL handles it automatically.

SELECT statement

To read entries, use the SELECT command:

SELECT * FROM users;

To display all entries from the users table:

SELECT * FROM users ORDER BY username;

This command will order the output by username within an existing column in the table.

UPDATE statement

Imagine that a user named pati changes their email address. We would then want to modify it in our database too.

How can we proceed? We cannot use the INSERT command because the user is already in the database. Instead, we use the UPDATE command:

UPDATE table_name
  SET column_name = "new value"
  WHERE condition;

DELETE statement

Use the DELETE command like this:

DELETE FROM table_name
WHERE condition;

To illustrate this, let’s say we want to delete the fourth entry, id 4:

DELETE FROM users
WHERE id=4;

Here, we use the id column. If we want to use the username column, we can write it like this:

DELETE FROM users
WHERE username='yves';

Wrap up

Let’s recall some of the essential points:

• CRUD is equivalent to CREATE TABLES, SHOW TABLES, ALTER TABLE, and DROP TABLE if we work with tables in general.
• CRUD is equivalent to INSERT, SELECT, UPDATE, and DELETE if we work with table content.

Happy coding!