Author: Andrew Couch

Introduction

Behind every great web application is a database of some kind. Many of the widely used database systems (e.g., MySQL, Percona, PostgressSQL, MariaDB, MSSQL) use SQL (Structured Query Language) to pull data out and push data in. A lot of modern programming languages provide an ORM (Object-Relational Mapping) layer which loads data directly into code without needing to know SQL, but sometimes you just want to talk to the database directly. While SQL has a lot of keywords and can get very complex, this article goes through the basic CRUD (Create Read Update Delete) constructs.
.

Prerequisites

– A basic understanding of what a database is and how to interact with a database system.

Using Basic SQL Syntax

Some Notes About Dialects and Data Values

Every database system that uses SQL has a slightly different implementation. The differences do not show up in the basic commands that this guide covers, but it is good to know that there are potential differences across systems.

In all of the examples here the SQL keywords are capitalized. While this is a common practice and helps make examples clear, most SQL databases don’t care. The same is true with the line breaks and spacing. SQL can be written all on one line if you prefer, but here we will write statements across multiple lines to help clarify some explanations.

Different data values need to be entered differently depending on data type. Text values need to be encased (escaped) in single quotes. If you happen to have a value that itself contains a single quote, that interior quote needs to be doubled to make sure the statement will run.

WHERE lastname = 'O''niel'

Numeric types, integers, decimals and the like do not need any extra escaping. Boolean values like True and False also do not need to be escaped.

Finally, many SQL implementations use a semicolon as a statement terminator. While this usage isn’t universal, many consider it a best-practice to ensure portabiility of SQL statements across different implementations. This article will use semicolons.

.

SELECT – Reading Data from the Database.

Reading data is the most common task and illustrates the basic structure of a SQL statement nicely. This SELECT is (almost) the most basic SQL statement there is.

SELECT <column_list>
FROM <table>
WHERE <column_name> = <value>;

SELECT, FROM and WHERE are SQL keywords. <column_list> is a comma-separated list of column names that you want to read. It can also be replaced with * to just return all columns of the table. Be aware that * can be quite slow if you have a lot of columns in a very large table.

<table> is the name of the table you want to retrieve data from. <column_name> indicates the name of the column which should contain the <value>.

The WHERE section is optional, but is so useful and idiomatic in SQL that it makes sense to include it. WHERE restricts the data set based on the criteria. Although depicted here with only a single criterion, it is possible to include several criteria with boolean operators AND and OR. See the UPDATE example below for something more complex.

Example:

SELECT common_name, genus, species, type FROM animals WHERE type = 'mammal';

.

UPDATE – Making a Change

Sometimes it isn’t enough to just see what it is in the database. Sometimes you need a change. And when you do, UPDATE is there for you.

UPDATE <table>
SET <column_name> = <value>
WHERE <column_name> = <value>
AND <column_name> = <value>;

Again, UPDATE, SET, and WHERE are SQL keywords. <table> is the name of the table you want to update. Within the SET section, you can include one or more <column_name> = <value> pairs separated by commas. This portion of the statement is where you define the change to be made. The <column_name> will be set with the given <value>.
The WHERE section is optional on UPDATE as well, but leaving it out will update the entire table. Occasionally this is what you want, but most of the time you only want to update a single row or a few rows. In these cases, updating the entire table would be disastrous, so it is good practice to always have a WHERE. This example shows using AND in the WHERE section.

Example:

UPDATE animals SET have_seen = True, common_name='River Otter' WHERE Genus = 'Lontra' AND Species = 'Canadensis';

.

DELETE – Making It Gone

Sometimes you need to remove data from a table. This action is a DELETE.

DELETE FROM <table>
WHERE <column_name> = <value>;

Once again we have our SQL keywords and a <table> defining which table will be deleted from, and a WHERE section defining which rows are to be deleted. Again the WHERE clause is technically optional, but this statement will delete all rows in a table if it is missing.

Example:

DELETE FROM animals WHERE have_seen = False;

.

Delete Tip

One best practice I find for doing manual deletes like this one is to make a SELECT first. The DELETE command shares a lot of structural similarity with SELECT and yet is far more destructive. First write the statement as a SELECT * FROM <table> WHERE <column_name> = <value>; to make sure you know exactly which rows will be affected. Once you are happy with the output, swap out the SELECT * for the DELETE keyword and run it. In the example below, the DELETE statement is commented out. Two dashes -- comment out the rest of the line. This practice can be really helpful when performing DELETEs to keep an accidental run of the query from removing data.

Example:

SELECT *
-- DELETE
FROM animals WHERE have_seen = False;

Note: The -- comments out all text to the end of the line, not the end of the statement. If the FROM animals WHERE have_seen = False were on the same line as -- DELETE, SQL would only see the SELECT * portion of the statement.

.

INSERT – Bringing New Data to the Table

The last piece of this puzzle is adding data into a table. There are two forms of INSERT in classic SQL. The one I’ll cover here allows you to add explicit data into a table.

INSERT INTO <table>(<list_of_column_names>)
VALUES (<list_of_values>);

The INSERT statement looks different than the rest. The <table> again defines the table where the data is going, though here it is followed by a comma-separated <list_of_column_names> enclosed in parentheses. You only have to list the columns for which you have data to enter; however, if the table you are working with has required fields (defined as NOT NULL constraints), you will need to give data for all of those fields to avoid an error.

The VALUES keyword introduces a <list_of_values> inside of parentheses. This list is the list of values to be inserted into the columns you defined above. The listed columns and values need to match each other’s order. The first value will be inserted into the first column name in the list, the second column to the second value and so on. This order in the statement may be different than the order of columns in the target table, but that is fine.

Example:

INSERT INTO animals(common_name, species, genus) VALUES ('Sea Otter', 'Lutris', 'Enhydra');

About Atlantic.Net

Atlantic.Net offers world-class hosting services and solutions, including HIPAA compliant cloud storage hosting. Contact us today for a quote!