SQL Intro

Statements

Most of the actions you need to perform on a database are done with SQL statements. SQL statements consist of keywords that are easy to understand.

The following SQL statement returns all records from a table named "Customers":

SELECT * FROM Customers;

SQL keywords are NOT case-sensitive: select is the same as SELECT

In this blog post I will be writing all SQL keywords in upper-case.

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Database Tables

A database mostly contains one or more tables. Each table is identified by a name (e.g. "Books" or "Posts"), and contain records (rows) with data.

In this blog post, I will be writing the syntax for a blog database, step-by-step.

Quick Overview

• SELECT - extracts data from a database

• UPDATE - updates data in a database

• DELETE - deletes data from a database

• INSERT INTO - inserts new data into a database

• CREATE DATABASE - creates a new database

• CREATE TABLE - creates a new table

• ALTER TABLE - modifies a table

• DROP TABLE - deletes a table

SQL Commands

Creating a Database

The CREATE DATABASE statement is used to create a new SQL database.

For this exercise, I will call this database 'MyBlog'.

CREATE DATABASE MyBlog

Creating a Table

The CREATE TABLE statement is used to create a new table in a database.

The whole table syntax looks like this:

CREATE TABLE Posts (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

For more information about data types, check out this blog post.

For the blog database, I will need the following columns:

• ID
• Title
• Author
• Teaser
• Body

All of these columns are needed for our blog, so I will add the NOT NULL constraint on each column.

For this exercise, I will create a table called 'Posts':

CREATE TABLE Posts (
    ID INT NOT NULL,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Teaser TEXT NOT NULL,
    Body LONGTEXT NOT NULL
);

Altering a Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

In the Posts table, there is a column called 'ID'. An ID is always unique to one row. To fix this, you can add a PRIMARY KEY constraint.

To make the primary key, I will have to alter my database:

ALTER TABLE Posts
ADD PRIMARY KEY (ID);

Showing Tables

To check how your table structure looks like, you can use the DESCRIBE statement.

DESCRIBE Posts

And the output would be:

+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| ID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| Title  | varchar(255) | NO   |     | NULL    |                |
| Author | varchar(255) | NO   |     | NULL    |                |
| Teaser | text         | NO   |     | NULL    |                |
| Body   | longtext     | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

Inserting Data into a Table

Now that I have the right table structure, I can insert the data into the table.

The INSERT INTO statement is used to insert new records in a table. There are two ways to do this.

You can specify the column names:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Or you can leave it out:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Now I can insert my data into the table:

INSERT INTO Posts (ID, Title, Author, Teaser, Body)
VALUES 
    ('Blog post 1', 'Max', 'This is a teaser', 'This is a blog post body'),
    ('Blog post 2', 'Jane', 'Another teaser', 'This is another blog post');

Selecting Data from a Table

The SELECT statement is used to extract data from a database.

I can return all columns from the Posts table with:

SELECT * FROM Posts

Or just specific columns:

SELECT ID, Author, Teaser FROM Posts;

The output for the query above would be:

+----+--------+------------------+
| ID | Author | Teaser           |
+----+--------+------------------+
|  1 | Max    | This is a teaser |
|  2 | Jane   | Another teaser   |
+----+--------+------------------+

Updating Data in a Table

To update data in a table, you use the UPDATE statement.

For this exercise, I want to update my Posts table, to say that both blog posts were written by Jane:

UPDATE Posts
SET Author = 'Jane'
WHERE ID = 1;

Then the output for the query would be:

+----+--------+------------------+
| ID | Author | Teaser           |
+----+--------+------------------+
|  1 | Jane   | This is a teaser |
|  2 | Jane   | Another teaser   |
+----+--------+------------------+

Deleting Data from a table

The DELETE statement is used to delete existing records in a table.

The DELETE syntax is:

DELETE FROM table_name WHERE condition;

In my Posts table, I now want to delete the second row:

DELETE FROM Posts WHERE ID = 2;

Deleting a table

The DROP TABLE statement is used to drop an existing table in a database.

To drop my Posts table, I will use this query:

DROP TABLE Posts;