Back To Basics: CRUD Operations with SQL

I was recently going through my old projects trying to refactor them for inclusion into my portfolio website when I came across one of my first-ever full-stack projects. Back then, I mostly used structural PHP as my main back-end programming language. Being new to programming, I was excited, I mean, why not, programming is so cool. One of the best moments of my life was when I had my first programming 'aha' moment. It was amazing. Since then, I have created lots of projects, some finished, others unfinished, all with one goal in mind, building something.

Since I mainly used PHP (structural) for back-end, most of the time I wrote raw SQL queries, from database connections to creating tables and to be honest, it was quite refreshing. I gained a bunch of experience in SQL until I decided to shift to Node.js and the MERN stack for web development.

With the introduction of packages like Eloquent, it is easier to perform CRUD operation on a table without having any idea what happens under the hood. I decided to write this article to share my knowledge on the matter and in the process also remind myself how it actually works

For compatibility, I will use sqlfiddle to write the queries. Feel free to use any SQL editor. Possible options include XAMPP which comes with MySQL, Apache and PHP, VSCode's SQLite extension.

Creating a table

    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
        name VARCHAR(50),
        surname VARCHAR(50),
        email VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

The code above creates a table called users if it does not exist and then adds columns as specified

Data Types

INT - Accepts whole numbers only.
VARCHAR - Accepts strings with length specified in brackets
TEXT - Accepts long string such as descriptions
TIMESTAMP - Accepts timestamps

Attributes

PRIMARY_KEY - Sets column as primary key
AUTO_INCREMENT - Increases column value by specified increment value, 1 by default
UNIQUE - Column stores unique values, like email, values that are not the same
NOT NULL - Required values
DEFAULT - Default column value such as CURRENT_TIMESTAMP - For the current date and time

C - Adding data to users table

    INSERT INTO users (name, email, password, description) VALUES ('John', 'Doe', '[e-mail]', 'JohnDoe1', 'The name is Doe. John Doe');

Since we are using auto_increment, id is automatically added and incremented on addition of a new row, created_at and updated_at fields are also automatically filled because we set the default as current_timestamp.

R - Read data from users table

-- Select everything from users table
SELECT * FROM users;

-- Select some from users table
SELECT name, email FROM users;

-- Select from users table where
SELECT * FROM users WHERE email='[e-mail]';

-- Select from users table where ... and ...
SELECT * FROM users WHERE name='John' AND surname='Doe';

-- Select from users table where ... or ...
SELECT * FROM users WHERE name='John' OR surname='Doe';

Select has a variety of options. Where is used to select and filter based on specification.

U - Update existing data in users table

-- Update name to jane doe using email[unique]
UPDATE users SET name='Jane' WHERE email='[e-mail]';

-- Update email to janedoe using id[assume = 1]
UPDATE users SET email='[e-mail]' WHERE id=1;

D - Delete data from users table

-- Deleting entire table data[beware]
DELETE FROM users;

-- Deleting certain row data
DELETE FROM users WHERE email = '[e-mail]';

When writing the delete query be careful not to delete all data from the table by using where. I remember messing up a lot when I was starting out.

Incase I wrote anything wrongly, feel free to constructively criticize in the comments section.