Back To Basics: CRUD Operations with SQL
Tags: sql
Published: 2020-03-10 19:46:34

I was recently going through my old projects trying to refactor them for inclusion into my [portfolio website](https://margaiwangara.me) 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](http://sqlfiddle.com/) to write the queries. Feel free to use any SQL editor. Possible options include [__XAMPP__](https://apachefriends.org) which comes with MySQL, Apache and PHP, VSCode's SQLite extension.

## Creating a table
```sql
	
	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
```sql

	INSERT INTO users (name, email, password, description) VALUES ('John', 'Doe', 'johndoe@app.com', '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
```sql

-- 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='johndoe@app.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.

## U - Update existing data in users table
```sql

-- Update name to jane doe using email[unique]
UPDATE users SET name='Jane' WHERE email='johndoe@app.com';

-- Update email to janedoe using id[assume = 1]
UPDATE users SET email='janedoe@app.com' WHERE id=1;

```

## D - Delete data from users table
```sql

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

-- Deleting certain row data
DELETE FROM users WHERE email = 'janedoe@app.com';

```

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.