This post will not explain how SQL databases work or how they are structured. If you are unsure about databases or what SQL is check out the below posts first to get some background.
All of the examples in this post will be targetted around a single example and a single table. Let’s imagine we have one table called ‘Customers’ that contains three fields, ‘CustomerName’, ‘City’ and ‘Country’. All of the code snippets will be to do with this example and interact with this imaginary table and its field.
If we want to retrieve data from a table we use the ‘SELECT’ keyword. The below example shows retrieving all the data within the Customers table. The asterisks ‘*’ will return all the columns from the table. This will return a list of data each will have a CustomerName, City, and Country field with its representative value. If you used an ORM this would return a list of objects from the table.
SELECT * FROM Customers;
We can limit the columns we want to retrieve. For example, we could return just a list of the countries from the table. The below code snippet will return a list of all the countries that are within the table, you can add more columns by separating them with commas. If we had 5 entries in our database this would return 5 different strings with whatever the respective country is per entry.
SELECT Country FROM Customers;
In the previous example, you would be able to see the countries the customers are from within the table. If two, or more, customers are from the same country then you would have duplicates within the list. This could useful if you want to count the number of customers per country. However, if you just wanted a list of all the countries your customers are from, a distinct list of countries without any duplicates. You can use the ‘DISTINCT’ keyword with a select statement to remove any duplicates from the returned list. The below code snippet will, therefore, return a list of countries from the Customers table without any duplicates.
SELECT DISTINCT Country FROM Customers;
You can also add conditions to your select queries. For example, we might want to see all our customers from a specific country. The ‘WHERE’ clause is used to add a condition to the returned results, the below code snippet will return all the columns and their data from the Customer table, similar to the first example, but it will only return customers whose Country value is equal to ‘Mexico’.
SELECT * FROM Customers WHERE Country='Mexico';
Let’s say we get another customer, we will need to insert the new customer and their data into the database table. The ‘INSERT INTO’ clause is used to add data into a database table. The below code snippet shows adding a new customer to the Customers table with a customerName of ‘Antonio Moreno’, and City of ‘Stavanger’, and a Country of ‘Norway’. It is important to understand how the ‘INSERT INTO’ clause works, otherwise, you will never be able to add data to your table and therefore will just have an empty database.
INSERT INTO Customers (CustomerName, City, Country) VALUES ('Antonio Moreno', 'Stavanger', 'Norway');
People constantly change their details, such as moving house or changing their preferences. We just added a new customer called ‘Antonio Moreno’ to our database table, if he moves country or city we would have to update his record within our database. The ‘UPDATE’ keyword is used for just this purpose. The below code snippet will update every record within the Customers table and change it’s Country value to ‘Mexico’ if the CustomerName field is equal to ‘Antonio Moreno’. If we had two, or more, entries with the ContantName ‘Antonio Moreno’ then the Country would be updated for all these entries.
UPDATE Customers SET Country='Mexico' WHERE CustomerName='Antonio Moreno';
Now imagine a customer deletes their account or requests to be removed from your database, you need a way to delete data from your database too. Fortunately, SQL has a ‘DELETE’ keyword that does exactly that. The below code snippet will delete all entries from the Customers table that have a CustomerName of ‘Antonio Moreno’. It is important to understand the DELETE keyword and use the WHERE clause, as you can accidentally delete all records from a table.
DELETE FROM Customers WHERE CustomerName='Antonio Moreno';
There are more keywords in SQL, and much more you can achieve by using SQL. You can combine tables into a single result, create in-memory views, etc. These are just the basic commands to help you get started with SQL and understand the basics of the language. Another important to note is that all the SQL keywords are case insensitive, which means that you could write “SELECT” or “select”.