SQL - Create
A database is nothing more than an empty shell, like a vacant warehouse. It offers no real functionality what so ever, other than holding a name. Tables are the next tier of our tree offering a wide scope of functionality. If you follow our warehouse example, a SQL table would be the physical shelving inside our vacant warehouse. Depending on the situation, your goods may require reorganization, reshelving, or removal. SQL tables can be manipulated in this same way or in any fashion the situation calls for.
SQL - Create Database
We first need to build our warehouse, or create our database. The create clause is straight forward. Here we have a one line script using the create clause to create a database we named business. The exact number of databases a SQL program can handles is entirely up to the manufacturer, visit your sites manufacturer if you would like to have specifics.
SQL Code:
CREATE DATABASE business;
We will be using this database for the remainder of the tutorial.
SQL - Create Table
Our emtpy shell of a database will do nothing standing alone, next we create our tables to store and organize our data. We use the same create clause and follow the same syntax above. Tables, however, are fairly complex. In our script we must also include parameters for each table column as well as name each one. Naming your table columns can be as simple or complicated as desired. SQL programs are case sensitive, keep this in mind as you will be calling on our table columns by name a great deal as you enhance your SQL knowledge. Also, most programs do not support spaces in column names, you must use the underscore (_).
Column types specify what type of data can be placed inside a table column ranging from numbers, paragraphs, or brief strings. For example, setting a column type to an int value means your database will ONLY accept an interger value for this column type.
Column Types:
| Column Type | Description | Syntax |
| int | Accepts integer values only | tinyint, int |
| varchar | Accepts any symbol, character, or number | varchar(char limit value) |
| text/blob | lots of text including line breaks | text, blob |
Int, varchar, and text are the 3 most common types of columns. Text and int columns have 3 flavors tiny, medium, and large. Every SQL program has its unique sizes, but for this tutorial we will be using medium sized column fields for each exercise. Later on as your database exapands it becomes extremely important to not overdue the size of your column fields. Using the correct size field will dramatically increase performance including query speeds.
Now create the table.
SQL Code:
CREATE TABLE employees
(
id INT(3) NOT NULL AUTO_INCREMENT,
Lastname VARCHAR(50),
Firstname VARCHAR(25),
Title VARCHAR(10) DEFAULT 'crew' NULL
);
Above is our table, the first column simply numbers each row that will be added to the table up to a maximum of 3 digits wide (999) automatically(auto_increment). Our second line is a varchar meaning it will hold numbers, digits, or symbols, which is perfect for short names of people. The last column has a specified default value of "crew" so that if we add a new crew member we can simply place a default value.
We do not always have to specify a default value or state weather each column may have a NULL value, By default, most table columns will allow null values to be recorded but certain table columns will not allow a null value to be inserted. We recommend reading up on each column type available to you from your database manufacturer.
SQL - Primary Key
A primary key is a property given to a table column that distinguishes that record apart from each. For each record in the table the primary key acts like a driver's license number, only one number exists for each person. The same principle applys here. Any table can only be given one auto increment field and as such it is forced to be the primary key of the table. Therefore in our following examples we use the 'id' field as our primary key.
The alter clause is used to add or drop primary keys and indexes. To change a primary key you must drop the first one, then add your desired primary as shown below. More about the alter clause later.
SQL Code:
ALTER TABLE 'employees' DROP PRIMARY KEY,
ADD PRIMARY KEY ('id');
SQL - Indexes
SQL automatically creates some indexes based on column types and attributes. An index can also be given to a table column to optimize speeds. When a query is executed searching for a specific column value, SQL will start at the top of the table and search each and every record until it finds matches. This becomes a performance issue when a table holds a vast amount of records. By adding an index to columns, SQL will no longer search the entire table, it will pinpoint your index columns and search those first. The downside to indexing is that it enlarges the disk space consumed by a table on your webserver. Use indexes when you notice a drop in your query speeds.
SQL Code:
ALTER TABLE 'employees' ADD INDEX ('id');
or
ALTER TABLE `employees` DROP INDEX `Lastname`;
| 
