Ultimate Web Guide to SQL Database Language

General Information

SQL stands for Structured Query Language and is a computer language that is used to interact with and manage a database. It is the most widely used language used to manage databases. One thing that is important to note is that SQL is not a specific database system, there are many database systems that use SQL. The most commonly used is MySQL, however there are dozens of different systems, including Microsoft SQL, and Oracle as well as many lesser known systems.

SQL tutorial from W3Schools
SQL tutorials with a lot of help with query commands
SQL tutorials from 1keydata
SQL Course

Installing SQL

When installing SQL, it is important to first decide which database system you want to install. MySQL is likely a good place to start as it is both powerful and easy to work with. When using Windows as your operating system it is easiest to install an entire AMP stack. An AMP stack will install Apache, a web server, MYSQL, your database, and PHP, a web programming language. Popular AMP stacks include XAMPP and WAMP. When using Linux, you'll want to head over to mysql.com and download the latest version as a tar.gz and install it.

XAMPP for Windows/Linux/Mac
WAMP for Windows
How to build a LAMP server
How to install LAMP on Ubuntu

Creating a Database

Creating a database with SQL is incredibly easy. If you are using an AMP stack, you may have PHPMyAdmin installed which is a web interface for interacting with databases. Creating a database with PHPMyAdmin is as simple as typing in a database name and hitting the submit button. Without using PHPMyAdmin you'll have to use straight SQL code. The following code with create a database: "create database databasename;"

SQL create database statement
Using the create command

Creating a Database Table

Creating a table within a database is the next step in the process. A table can be loosely compared to an Excel spreadsheet and is the place where the data is actually stored. To create a table, the following example code can be used:

"CREATE TABLE tablename ( id INT, data VARCHAR(100));"

In the example, there is an column named 'id' that can contain integers, and a column named 'data' that can contain variable characters up to a length of 100.

Create table statement
Create table statement help from W3Schools
Creating a table from another table
Creating a table with PHP

Primary and Foreign Key

A primary key is a field that is used to unique identify data within your database tables. When processing and displaying data from your database, the primary key is likely the field that your relational database will depend on to operate. A foreign key is used when tying together two related tables. A foreign key is used when a second table references a piece of information within the first table. Foreign keys can begin to get a little complicated. Foreign keys are a more advanced feature of SQL and it is likely you won't be using them in your first databases.

Database design tutorial - primary keys
How to create and use primary keys
Foreign key reference
Referential integrity and foreign keys

Adding data with SQL

When inserting data into SQL, you'll be using the INSERT INTO statement. 

"INSERT INTO tablename VALUES (value1, value2, value3...);

When inserting data into a database it is important to make sure that you are inserting the data into the correct fields. In the first example the data is not told which columns to fill, only their values. It is usually best to specify the column names to ensure the data is being stored correctly. This second example will specify the column name.

"INSERT INTO tablename (column1, column2, column3) VALUES (value1, value2, value3);"

Insert into SQL query
SQL insert query
SQL insert tutorial
Multiple inserts in one query

Deleting data with SQL

When deleting data with SQL it is absolutely vital that you are deleting the correct data. It is best if you know what the primary key value is for the data you want to delete. Because the primary key is unique, when you delete data matching the primary key it should only match the one row of data. It is also a good idea to put a limit of 1 on the query just in case. A limit of 1 will ensure that your query will only delete one row of data. An example is:

"DELETE * FROM tablename WHERE id=1 LIMIT 1;"

Emptying an entire table can be done with

"DELETE * FROM tablename;"

SQL deletetu
SQL delete query
Delete query using PHP
SQL delete command

Updating Data with SQL

The update query is used when you want to change existing data within the database. Again you should use the primary key to limit your query to only one row of data. An example of an update query is:

"UPDATE tablename SET column1=value1, column2=value2... WHERE id=1"

SQL update statement
SQL update tutorial
The update statement
Update tutorial

Retrieving Data with SQL

The query used to withdraw data from the database is called the select query and is where the magic of SQL happens. Select statements can be incredibly simple or deviously complex. To select information from a database you should use the following query:

"SELECT * FROM tablename

You can select specific columns by substituting column names for the * in the example query.

SQL select tutoral
SQL joins
Extended SQL select capabilities
speed tips for advanced SQL commands
Advanced SQL tutorials

PHP and SQL

SQL is a great language, however it doesn't interact with the web at all. In order to build a web page that uses a SQL database you need to use a language that interacts with the SQL and can print out HTML. The most commonly used language for this purpose is PHP. Unfortunately this guide is not long enough to teach you how to interact with your SQL server, however the following links should help you.

PHP MySQL introduction
MySQL information from PHP.net
MySQL tutorial with PHP
PHPMyAdmin - a MySQL administration tool written in PHP

Sign Up for a Free Trial

Get started in QuickBase today.

30-day free trial. No credit card required.