What You Need

SQL (Structured Query Language) is a language designed for interacting with relational database management systems (RDBMS), like MySQL, Oracle, Sqlite etc… To perform the SQL queries in this article, I suggest that you have MySQL installed. I also recommend phpMyAdmin as a visual interface to MySQL.

The following applications make it very easy to install both MySQL and phpMyAdmin on your local machine:

We are going to use command line for queries. WAMP already comes with a MySQL Console. For MAMP, you may want to read this article.

If you would like to follow the examples in this article on your own development server, do the following:

  1. Open MySQL Console and login.
  2. If you haven’t already, create a database named “my_first_db” with a CREATE query.
  3. Switch to the database with the USE statement.

When creating a database, common sense dictates that we use separate tables for different types of entities. Some examples are: customers, orders, items, messages etc… But we also need to have relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need.

There are several types of database relationships. Today we are going to cover the following:

  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships
  • Self Referencing Relationships

When selecting data from multiple tables with relationships, we will be using the JOIN query. There are several types of JOIN’s, and we are going to learn about the the following:

  • Cross Joins
  • Natural Joins
  • Inner Joins
  • Left (Outer) Joins
  • Right (Outer) Joins

We will also learn about the ON clause and the USING clause.

Catch Up All Here

Read the rest of entries here »