SQL Primer — Basic Operations

Mohammed Chisti
HackerNoon.com
4 min readOct 23, 2017

--

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:

For this example we’ll create a database named project

Now we’ll have to state that we’ll use the database name 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.

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

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:

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

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:

Let’s retrieve everything from our users table:

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

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

Update

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

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

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.

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.

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

For us it’ll be

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

For us.

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

--

--