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
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
INT - Accepts whole numbers only.
VARCHAR - Accepts strings with length specified in brackets
TEXT - Accepts long string such as descriptions
TIMESTAMP - Accepts timestamps
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
INSERT INTO users (name, email, password, description) VALUES ('John', 'Doe', 'firstname.lastname@example.org', '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.
-- 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@example.com'; -- 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.
-- Update name to jane doe using email[unique] UPDATE users SET name='Jane' WHERE firstname.lastname@example.org'; -- Update email to janedoe using id[assume = 1] UPDATE users SET email@example.com' WHERE id=1;
-- Deleting entire table data[beware] DELETE FROM users; -- Deleting certain row data DELETE FROM users WHERE email = 'firstname.lastname@example.org';
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.