For a long time, I’ve wanted to published guides to the programming languages that I use. Last week, I started by writing up quick notes on SQL but it occurred to me that you will need a SQL database to follow along. So I found a free SQL database system that you can download right now.
The program that you need is called MAMP and you can download that here. Take care to download the correct version for your operating system. MAMP makes it easy to install these three open-source products: Apache, MySQL, and PHP. MySQL is the free SQL database, but you need all three working set this database up.
After you download MAMP, open the app. You should see a screen that looks like this:
Click the “Start Servers” text to start the three open-source programs that we need to run our database. A browser will automatically launch and open this page on your desktop:
You will interact with MAMP through your web browser since both PHP and MySQL are server-based technologies. The two most important links are at the very top of this web page labeled MY WEBSITE and TOOLS.
MY WEBSITE serves up any website content that you have in your server’s root directory. You probably don’t have anything that like that yet and don’t really need to use this feature for the SQL tutorials. But, it’s a nice feature to have if you want to play with web data visualization in the future.
The stuff in TOOLS is what we want today. When you click on TOOLS, you are presented with a drop-down menu that includes an option labeled PHPMYADMIN. Click on that to get a web-based admin screen for the MySQL databases that you just installed. You will see a screen that looks something like this:
Your screen will look like mine, but without the WordPress database I have for my blog. You can use PHPMyAdmin to create new databases that can be shared with other apps on your desktop. You also write SQL code from this console.
Run First SQL Query
Before I wrap up, let’s run a simple SQL query that will give you a list of the tables that you have in MySQL right now. Click on the SQL tab and type this query into the form labeled “Run SQL query/queries on server “localhost”:“
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
Click the Go button that’s in the middle-right area of your screen to run the query to get something like this:
So that’s it, you have a working and free SQL database!
MySQL is a powerful SQL database that you can use in your projects. The PHPMYADMIN interface is good enough to work through some tutorials, but at some point you will want a better app to work with this tool and there are options out there. At some point, I will dig in and find a few. Personally, if I were doing serious data science work I would connect to these databases through RStudio. But, that’s to involved for people who are just starting out.
Next week, I will write up some notes about how to get data and populate your database with fresh data that you can analyze. Feel free to post any questions or comments down in the comment section below and I will answer them.