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?
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
CREATE DATABASE project;
Now we’ll have to state that we’ll use the database name
Now that we have created and are using a database we named
project, 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,
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,
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);
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
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 is for when things have changed or we’ve made a mistake and like for that record to reflect that.
SET 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
SET age = 19
WHERE username = 'mary';
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_name
WHERE column = value;
john decided that he no longer wants to be apart of our software and would like to delete himself from our product.
DELETE FROM users
WHERE 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;
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