In this MySQL tutorial we’ll look into creating databases, creating tables and some query examples.
MySQL short for Structured Query Language is an open-source relational database management system (see Wikipedia) and is used in millions of companies as the main database.
Applications / Companies that use the MySQL database include: WordPress, Drupal, Facebook, Twitter, Flickr, and YouTube.
It is very likely that the company that you work for or started yourself uses MySQL for some use case.
What would I do with my knowledge?
Most marketers use their knowledge of MySQL to do some kind of data-analysis. If you don’t want to be dependent on an IT-guy for every bit of data-analysis of your application – you start teaching yourself MySQL. The alternative would be to grab the data (which is really just table data like you’d find in excel) and copy it to excel and do your analysis there.
Why isn’t copying to Excel an option?
One of the biggest reasons is, that you can work with live data. You don’t have to copy a snapshot of the data – you’ll work right there live on your database.
You’ll still export the result of your query (more on that later) to your data analysis software later but with mysql you can join data from multiple tables beforehand which saves you a ton of time and hundreds of VLOOKUPS or INDEX-MATCHES in your excel files.
Start with the most important part: The Test-Environment
In my opinion the most important step of learning how to work with MySQL is the test-environment and some test-date.
You’ll do thousands of iterations of selects and it’s fine that most of them fail as your software will tell you where you failed and you can improve on that.
Software vs. Web Services
Although there are some web services like sqlfiddle.com where you can try some selects but the performance is really slow and getting a big dataset in there is not very practical.
Recreating a real scenario
My approach is to recreate a real case as close as possible. Normally we’d connect to a web-server with some databases with a local software.
The mysql software
I chose Sequel Pro to do my selects but you could go with any other software out there.
The database / server
The only real database that I could connect to would be my running wordpress installation and its backend database – but I don’t want to mess with the live date.
So I use MAMP to set up a locally running webserver which comes with a MySQL server.
MySQL Tutorial – Install the Software to connect to a database
1) Install Sequel Pro
2) Install MAMP
3) Start the Server using MAMP
4) Connect to the server using Sequel Pro
1) and 2) and even 3) are pretty straightforward, you should be able to do that without any help. If something fails, just google the error – there is loads of help on those topics.
Talking about 4) This is the connection data that you have to enter into Sequel Pro to connect.
(You find the port number in MAMP -> Preferences -> Ports; but the standard port is 8889 so you can try my settings first. The PW is “root” as well)
Now you should be greeted with an empty window of Sequel Pro
Creating the database schema
Now that we have successfully connected to a sequel server we’re ready for databases & tables and some data. The process of creating the database and tables is called “creating a database schema”. I’ve created a google sheet with some sample data that I want to get into my database, if you want to follow along.
Not interested in how to create databases & tables? Just want to get to the sample data? Scroll down to “Shortcut – Just give me the sample data”.
Add a database
To get started we create an empty database by selecting that option form the dropdown in the top left corner.
Create a table
You now have an empty database without any tables in it. Head over into Sequel Pro and click the small (+) in the left lower corner to create a table.
Then add all the columns of the data that you want to import and also the data types.
Data Types?
There are quite a lot of data types – these can be thought of kind of like cell types in excel. You find a https://www.w3schools.com/sql/sql_datatypes.asp.
Common ones are INT, VARCHAR, TEXT or DECIMAL.
The whole process could have also be done with a mysql command like so:
CREATE TABLE orders (
order_id int default 0,
total_price decimal(10,2),
cust_id int,
PRIMARY KEY (order_id)
);
If that confuses you, just use the software for now as shown in the gif above.
Importing the data
Now that the database scheme is ready – we’re ready to import some data into it.
Import from a CSV File
A CSV-File (Comma Separated Values) is a very common file-format to transfer database-data from one place to another. (In this case from google sheets to my database)
I’ve exported the orders sheet from my google spreadsheet as csv (File -> Download as -> .csv) to import it into my Database.
To do so we need a run mysql command. Copy the command into the “Query” tab of Sequel Pro and hit CMD + R to execute it.
LOAD DATA LOCAL INFILE '/Users/Daniel/Downloads/orders.csv' INTO TABLE orders
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
If everything worked the data is now in your table. Check the “Content” tab of Sequel Pro and it should now look like this:
Repeat the steps for the “customers” sheet from the sample google spreadsheet. Just adapt the commands / data types.
Shortcut – Just give me the sample data
If you’re not interested in the whole process of creating databases and tables and importing data just use my sample database. Download it, double click it and it should open in Sequel Pro and ask you if you want to import the data.
→ Download the Sample Database
[chatbot]
Finally some examples with the example data
I’m going to show you a few examples – that you should be able to recreate. After you lost your fear of getting started head to one of the resources provided at the end of the guide to get in-depth knowledge of MySQL.
Select All
select * from orders;
This is as simple as it gets:
select
As long as we’re doing data analysis and not altering table date your query almost always starts with “select”.
*
The added asterisk basically just says “everything” – so we’ve just expanded our “select …” to “select all”
from orders
Answers the question what table we’re selecting from.
Execute by hitting CMD + R and you should end up with this result:
Select the sum of money spent per customer
SELECT cust_id,
sum(total_price)
FROM orders
GROUP BY cust_id
ORDER BY sum(total_price) DESC;
After the select keyword we add the two columns that we want to see. First cust_id and then the of the values in total_price.
We also add a GROUP BY cust_id to let the server know that we’re interested in one row per customer.
The ORDER BY sum(total_price) DESC just reorders the values.
Revenue per Customer-Country
We have two tables so we actually can and want to join those tables. We have a common identifier (cust_id) which we’re going to use here. You can only join two tables if you have some (preferably unique) identifier in both tables.
SELECT Country,
sum(total_price)
FROM orders,
customers
WHERE orders.cust_id = customers.cust_id
GROUP BY Country
ORDER BY sum(total_price) DESC;
In this MySQL tutorial for beginners I cant’t go into detail describing this one as it get’s a bit complex. But head over to the resources provided below and start experimenting – you’ll quickly get the hang of it.
Where to become an expert
I can’t teach you the how to become a mysql expert – there are way better resources out there. But you now have a test environment and even some test data, which in my opinion is the most important part as you now can test your knowledge anytime and expand your datasets to fit your personal use-cases.
Where to learn MySQL – MySQL Tutorials to become an Expert
1) Codecademy – Check out this course
2) Udemy – There are many courses