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 or , 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. Mongoose Active Record CRUD The basic operations of SQL is , which is an acronym for reate, ead, pdate, elete. 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. CRUD C R U D 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 named , we need to start adding some tables to it so we can save some records to it. project 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 in our database. The following line of code should help us accomplish this: users 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 table: users SELECT * FROM users; We can then specify so that we don’t get everything by using the clause in SQL. WHERE SELECT * FROM table_name WHERE column = value; So if we’re to get in from our table , we’d do something like this. john users 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 in our database has gotten one year older and we’d like to update that mary 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 decided that he no longer wants to be apart of our software and would like to delete himself from our product. john 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 our database. DROP 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 and database relationships! JOIN Thanks for reading! Please share and recommend to other interested readers! Check out my portfolio http://mohammedchisti.com