SQL Primer — Basic Operations

I’ve been doing a lot of coding on the front-end lately; actually I’ve been doing so much that I might need to create a quick primer about it. If you’re just like me and need a refresher or are just getting the hang of SQL and need a quick reference, you might appreciate this one. For this primer, I’ll use MySQL syntax.

Where Does SQL Fit Into My Project?

SQL in laymen terms is a language we use to save data permanently with. To better understand this so it makes a bit more sense, let me further explain a little more. You usually have a project that usually consists of a front-end maybe written with HTML/CSS/JavaScript and then you usually have a back-end like NodeJS or Rails. Where SQL comes in, is that your back-end usually through an ORM (Object Relational Mapping), like Mongoose or Active Record, connects to your database of stored data, where this data is created using SQL. This is how any software remembers your username and password so that you can log back in again at a later time.

CRUD

The basic operations of SQL is CRUD, which is an acronym for Create, Read, Update, Delete. You’re mostly always either creating, reading, updating or deleting a table inside you database. Usually you have one database that you connect to and in that database you create tables that you store data in through records. If you’ve worked on Excel sheets, then imagine a database being the Excel file itself, the tables you create in your database being the table pages in your Excel sheet file and a record is the row in your Excel sheet page.

To create a new database for your project run:

CREATE DATABASE name_of_database;

For this example we’ll create a database named project

CREATE DATABASE project;

Now we’ll have to state that we’ll use the database name project:

USE project

Create

Now that we have created and are using a database we namedproject, we need to start adding some tables to it so we can save some records to it.

This will create a new table inside of your database.

CREATE TABLE table_name (table_name_id INT AUTO_INCREMENT PRIMARY KEY,column_name VARCHAR(20));

For our example, let’s create a table named users in our database. The following line of code should help us accomplish this:

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20),age INT);

Now that we have a table, we can now save some type of data, which in SQL we call a record. The way you would insert a record is through the following:

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

So let insert two records to our table, the way we would do this is by:

INSERT INTO users (username, age) VALUES ('john', 34);INSERT INTO users (username, age) VALUES ('mary', 18);

Read

Usually once you have a table, you’d like to retrieve information from it. The way you’d retrieve all records from a table is by the following:

SELECT * FROM table_name;

Let’s retrieve everything from our users table:

SELECT * FROM users;

We can then specify so that we don’t get everything by using the WHERE clause in SQL.

SELECT * FROM table_name WHERE column = value;

So if we’re to get john in from our table users, we’d do something like this.

SELECT * FROM users WHERE username = 'john';

Update

Update is for when things have changed or we’ve made a mistake and like for that record to reflect that.

UPDATE table_nameSET column = value, column1 = value1, ...WHERE another_column = another_value;

So lets say mary in our database has gotten one year older and we’d like to update that

UPDATE usersSET age = 19WHERE username = 'mary';

Delete

Delete is pretty self explanatory, but it allows us to delete records that we no longer need or remove tables we no longer use.

DELETE FROM table_nameWHERE column = value;

Let’s say john decided that he no longer wants to be apart of our software and would like to delete himself from our product.

DELETE FROM usersWHERE username = 'john';

Since we’re almost done with our SQL Primer Pt. 1, lets remove our table from our database and then also DROP our database.

To remove our table

DROP TABLE table_name;

For us it’ll be

DROP TABLE users;

And now to end it, lets drop our no longer needed database itself.

DROP DATABASE database_name;

For us.

DROP DATABASE project;

Hope you guys liked that primer and had a good time going through it. Follow me for Pt. 2 ! I’ll go through JOIN and database relationships!

Thanks for reading! Please share and recommend to other interested readers!

Check out my portfolio http://mohammedchisti.com