Next: {Using MySQL SELECT}
This tutorial aims to outline the usage of SQL CREATE, intended for the creation of the databases and tables in which your information will be stored. So lets cut straight through the jibber jabber and take a look at the basics of our first CREATE string to see just what we can do.
The first thing we'll look at is the creation of a database to later store our tables within. Without a database structure alongside the appropriate tables required to store and maintain our data, we wouldn't fare too well when attempting to warehouse masses of information in a place which can be easily accessed and manipulated upon request. It's worth noting that server side software packages such as cPanel, directAdmin the infamous phpMyAdmin, employ simple to use interfaces to perform SQL interactions as opposed to thier hand written equivalent in code. All in all, very handy tools, but the pen (or keyboard) is mightier than the sword!
The following code snippet shows just how simple the creation of a new database really is. It's elegant, to the point and it's easy to remember. Simply substitute the 'database_name' var with the desired name of your newly created database and your off to a flying start. However, depending on your webhost or your local setup, database creation is often disabled via this method and is instead either performed through the aforementioned phpMyAdmin or is automatically setup by your web host. So don't panic too much if you run into any problems, check up on phpMyAdmin (or equivalent) as chances are, your database may already exist.
CREATE DATABASE `database_name`
Having a database is all fine and well, but we still need to create some tables to enable us to start storing all our data. Before we start hammering out code, lets catch up on some handy information we'll need to know before we can get started.
Database tables are two dimensional structures comprising of multiple columns and rows. The columns identify the information which is to be stored in any subsequent rows, so you end up with something that should look a litle bit like this...
id | name | gender | age ------------------------------------------- 1 | Zachy | Male | 22 2 | Kneeley | Female | 21 3 | Roy | Male | 24
To help your SQL queries perform, you can provide specific lookup 'keys' which allow SQL to index the information you intend to store inside your database tables. The most common index to provide is a unique 'primary key', such as an 'id' number. This number is generally an incremental integer value, allowing each row of a table to have a unique lookup key by which it can be later found. As we can see above, the id numbers 1, 2 and 3 point to the individual rows containing our information about Zachy, Kneeley and Roy respectivly. Should we later need to access those rows, we can easily find them again by simply looking up their unique id numbers.
When creating a new table, we can specify the type of data we expect to be storing within each column, such as DECIMAL, INT, TEXT, DATE and TIME to name but a few. While it is entirely possible to store each column as TEXT or VARCHAR, its generally not good practice to do so. So lets take a look at an example to create the structure outlined in the above table.
CREATE TABLE `table_name` ( `id` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` TINYTEXT NOT NULL, `gender` TINYTEXT NOT NULL, `age` TINYINT NOT NULL )
Huzzah! Hopefully by this point we have a table sitting in our database waiting to be filled full of joyous data. As we can see from the above snippet, creating a new table isn't too much hard work. Some of the keywords in the query may look confusing, such as NOT NULL and PRIMARY KEY, but fear not, all will be explained...
Firstly, we start off with the opening statement CREATE TABLE `table_name` which will allocate a new table within our previously created database. The values which follow between the two parantesis '()' are the keywords used to create the columns within said table. Our first column 'id' is a key field for all tables, as mentioned previously, so it's vital that we know just whats going on. In this example, our id will be stored as a TINYINT, which should AUTO_INCREMENT upon creation of a new row, thus being a unique PRIMARY KEY. Rolling all that into one gives us a tasty description which we can pass on to create each column in our table. Including NULL or NOT NULL within the query allows us to specify our tollerance of missing fields when we begin storing data within our table. The majority of the time, NOT NULL is the path i choose.
Lets take a look at our id again. So far we have `id` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY. It's a bit of a mouthful, so lets start from the beginning. We start with `id` which is the name we'd like to call our column, followed by TINYINT which will allow us to store an integer value ranging from 0 - 255. Again, we meet NOT NULL which demands that there must be a value for this column when later creating or modifying data. Our final two friends are what makes this id column so special. With the inclusion of AUTO_INCREMENT, our table will automatically generate our unique id numbers by steadily incrementing the id with each row we store. And finally, seeing that we're using this as our lookup field, we make it a PRIMARY KEY to allow SQL to index our data which will help speed up any requests we will later make.
With the id out of the way, doesn't it make the remaining three columns look fairly trivial? Our name and gender columns are exactly the same as we only really need to be storing a small amount of text, so natually i've chosen TINYTEXT. And to round it all off, we use TINYINT for our age column, for simplicities sake. I recommend you read 'Data Type Overview', to learn more about SQL's data types such as BIGINT and LONGTEXT.
So we're off to a great start, we have a database and now we can start churning out tables to hold all our data. But what if we want to relate information between one or more tables? Perhaps you want to add another table so you can keep track of time spent with your friends. How would we go about such an endeavour? Lets take a look see...
CREATE TABLE `friends` ( `id` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` TINYTEXT NOT NULL, `gender` TINYTEXT NOT NULL, `age` TINYINT NOT NULL ) CREATE TABLE `planner` ( `id` TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `friend_id` TINYINT NOT NULL, `where` TINYTEXT NOT NULL, `when` BIGINT NOT NULL )
I'm not going to go in depth with relational databases as your probably starting to tire and your brain starting to melt, but i'll attempt to fill you in on the basics for now. From the snippet above, we can see that we now have two tables to work with; 'friends' and 'planner'. Our original friends table remains the same whilst our newly created planner table features slight modifications. The four fields of the planner table, 'id', 'friend_id', 'where' and 'when' will allow us to store a place and a time to meet up with a specific friend. There is a clear link between the two tables with the columns friends.id and planner.friend_id which will help us to create a bond between our data.
The example below attempts to portray the links between our data. The planner table holds our link in the friend_id column which will allow us to maintain bonds between our friends and the events set in our planner. By Looking up the id of Zachy (#1), we can see that i'm penciled in for some fun and games at the cinema at a specific time. You may be wondering about the 'when' values such as '123456789'? Our 'when' column is set to store BIGINT's, appropriately large enough to save Unix timestamps, which are my prefered medium, but thats another story... Alternativly, we can also reverse our lookup by using the friend_id column to find out just who's who. If we look up the friend_id #2 in the friends table, we can see that it's Kneeley we're set to meet on two specific occassions.
Friends Table id | name | gender | age ------------------------------------------- 1 | Zachy | Male | 22 2 | Kneeley | Female | 21 3 | Roy | Male | 24 Planner Table id | friend_id | where | when ------------------------------------------- 1 | 1 | Cinema |123456789 2 | 3 | Park |123456798 3 | 2 | Town |123456987 4 | 2 | Cinema |123456879 5 | 3 | Home |123456978
Ok, i'll come clean. It's not so grand, but it's a finale at least... For those of you who have made it this far, thanks for reading and i hope i've made at least a small amount of sense in my futile attempts to narrow down and expain such broad topics. In such a short time we've covered datbase creation, table creation and SQL's data types (breifly) as well as touching on a small amount of relational databases, which i will be covering in more depth in the tutorials that follow. It's been a journey of twists and turns, but i hope you have enjoyed reading at least half as much as i've enjoyed writing. Thanks again and be sure to check out the next tutorial for more SQL fun and games!
Next: {Using MySQL SELECT}
{1} comments - {View} | {Post}