Understanding How SQL Databases work

01
of 04

Understanding MySQL

MySQL is a relational database often used to store data for web sites working in conjunction with PHP. Relational means that different tables of the database can be cross referenced to one another. SQL stands for "Structured Query Language" which is the standard language used to interact with databases. MySQL was built using the SQL base and released as an open source database system. Because of its popularity, it is highly supported with PHP. Before you start learning to make databases it is important to understand more about what tables are.

02
of 04

What are SQL tables?

SQL Table
A SQL table is made of intersecting rows and columns.
A database can be made up of many tables, and a table in a database is made up of intersecting columns and rows that form a grid. A good way to think about this is to imagine a checker board. Along the top row of the checkerboard there are labels for the data you wish to store, for example Name, Age, Gender, Eye Color, etc. In all the rows below, information is stored. Each row is one entry (all the data in a single row, belongs to the same person in this case) and each column contains a specific type of data as indicated by its label. Here is something to help you visualize a table:
03
of 04

Understanding SQL Relational Databases

So what is a 'relational' database, and how does it use these tables? Well, a relational database lets us 'relate' data from one table to another. Let's say for example we were making a database for a car dealership. We could make one table to hold all of the details for each of the cars we were selling. However, the contact information for 'Ford' would be the same for all of the cars they make, so we do not need to type that data more than once.

What we can do is create a second table, called manufacturers. In this table we could list Ford, Volkswagen, Chrysler, etc. Here you could list the address, phone number and other contact information for each of these companies. You could then dynamically call the contact information from our second table for every car in our first table. You would only ever have to type this information once despite it being accessible for every car in the database. This not only saves time but also valuable database space as no piece of data need be repeated.

04
of 04

SQL Data Types

Each column can only contain one type of data which we must define. An example of what this means is; in our age column we use a number. We could not change Kelly's entry to "twenty-six" if we had defined that column to be a number. The main data types are numbers, date/time, text, and binary. Although these have many subcategories, we will just touch on the most common types that you will use in this tutorial.

INTEGER- This stores whole numbers, both positive and negative. Some examples are 2, 45, -16 and 23989. In our example, the age category could have been integer.

FLOAT- This stores numbers when you need to use decimals. Some examples would be 2.5, -.664, 43.8882, or 10.00001.

DATETIME- This stores a date and time in the format YYYY-MM-DD HH:MM:SS

VARCHAR- This stores a limited amount of text or single characters. In our example, the name column could have been varcar (short for variable character)

BLOB- This stores binary data other than text, for example file uploads.