Structured Query Language (SQL)

SQL is a language used exclusively inside of relational databases (MySQL, H2, Postgres, etc.) and helps us manage data stores. Even though we often abstract this interaction away with Object Relational Mappings (ORMs), it is important to know how to operate a relational database without that sort of abstraction. So let's dive in. You can play around with the commands we explore here.

Basic Operations

Operations inside of a relational DB are similar to other operations we are used to. They can be summed up in the CRUD operations, so that's how we will talk about them.

Creating

The first stop on our SQL operations journey is to create some sort of data. The first thing we need to create is a database, then we will create an entry in that database. Let's take a look at how that works and then discuss it:

CREATE

CREATE DATABASE pets;

Simple right? That command alone will give us a new database to manipulate data in. Next we need to use it:

USE

USE pets

Equally simple! With this statement, our DB environment know that any commands we execute following these commands should happen in the context of the pets database. So let's get some pets! The next thing we need is a table. You can think of a table as representing a collection of specific entities. For example, let's make a table to keep track of cats.

TABLE

CREATE TABLE cats(
  id INT NOT NULL,
  name VARCHAR(32)
  favorite_food VARCHAR(100)
  PRIMARY KEY(id)
);

This is a little more complicated... First, we're using the CREATE keyword again. This time we're making a TABLE and naming it cats. Notice that we have parentheses following the table name. Inside of them are the columns (also called fields, attributes, properties, etc.) that this table will be responsible for tracking. We're also telling our DB environment what types of data to expect. For example, name VARCHAR(32) says that the column should be named name and be keeping track of collections of characters no longer than 32 characters long. (You can look at all of the types you can manage in a relational database here)

The last important piece there is PRIMARY KEY(id). This tells your DB what the primary identifier for your entries is going to be. In our case this is going to be an id value that we will pass. It MUST be unique.

INSERT & VALUES

Let's finally look at creating a new entity:

INSERT INTO cats (id, name, favorite_food) VALUES (1, 'Wallace', 'Salmon');

We're now creating an entry for a cat named Wallace whose id is 1 and favorite food is salmon. Notice how we have to identify the order of the data first and then we declare the values after using the VALUES keyword.

Reading

Next we need the ability to read data from our database. This part is fairly straight forward. We retrieve existing data from the database to be used in whatever application we're working with.

SELECT

SELECT * FROM cats;

This is a very general read command that will get every entity from a table. In our case this will return us all cats in our cats table. But what if we want to get more specific?

WHERE

SELECT * FROM cats WHERE name = 'Wallace';

This query will return us only entities where the name of the cat is 'Wallace'. You can do this with any field in the entity. Usually you will use ids.

Updating

We also need the ability to update existing entities in our tables.

UPDATE & SET

UPDATE cats SET favorite_food = 'Tuna' WHERE id = 1;

This command is going to change the value of favorite_food on whichever cat has the id 1. In this case, Wallace. We can also change as many fields as we like at once. For example:

UPDATE cats SET favorite_food = 'Mac and Cheese', name = "Maurice" WHERE id = 1;

Deleting

Finally, we want to be able to remove entities from out tables

DELETE

DELETE FROM cats WHERE id = 1;

This command will delete our furry friend with the id of 1 from our database.

Conclusion

This wraps up our intro to SQL syntax. There is A LOT more that you can do with SQL commands. Get some more practice here and here.