SQL and MySQL Tutorial - Cheat Sheet Version

To view the youtube video for this tutorial, please click here.

Databases, SQL, and MySQL.

Databases are type of data persistence: information is stored somewhere, to be accessed at a later time. Databases typically store content for websites and apps, such as user information, or even item listings for websites like Amazon, or website URLs and keywords for websites like Google. 

MySQL is a relational database type where the data is stored in tables (with columns and rows), and tables can be related to each other and so certain information is correlated. 

SQL (structured query language) is a programming language that allows operations to be performed on structured databases.  There are a few keywords in SQL to perform the queries. It's very important to be able to query for what you need using SQL. ​

MySQL - Setting Up A Local Database

Obviously, the point of a database is to be able to access information easily. Most of the time, however, the database must be accessible over the internet. (Or at least, the APIs that access the database need to access it over the internet.) 

However, for testing purposes, we should be able to create a local MySQL database on our computer to test our programs out. We should also be able to export data from a database to give to someone else (called a MySQL dump). We're going to see how we're able to do that in this section. In the Ubuntu 16 command line...

First install SQL from the command line.

sudo apt-get install mysql-server

Choose the password for the root SQL user. 
Next, we're going to log in to SQL as root. (Enter the root password when prompted.)

mysql -u root -p

Inside of the MySQL prompt, create a database. We're going to name it "srcmakedb".

create database srcmakedb;

Next, we're going to create a user that has access to this database, so that we don't always have to use root. The user will be named "srcuser" and the password will be "password".

grant all on srcmakedb.* to 'srcuser' identified by 'password';

Now exit SQL, as we don't want to be logged in as the root user anymore.

exit

Next, we log in to SQL as the user that we just created. 

mysql -u srcuser -p

Then, we're going to use the database that we created before. 

use srcmakedb;

So we're finally inside of a database logged in as a user account. Now we want to make some actual data.

Let's create a table named "customers", that has columns for a customer id (that is a unique integer that acts as the primary key, and auto increments for new customers), a first name, and a last name.

create table customers (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT);

Enter some data in this table by using an SQL INSERT command. 

INSERT INTO customers (first_name, last_name) VALUES ('Bill', 'Johnson');

Add as many customers as you'd like, and make as many tables as you'd like. Honestly, now you can create whatever you want in this database.

​To see our table, use the SQL SELECT command.

SELECT * FROM customers;

Make your database as big as you want.

If you ever need to share the database data or upload it somewhere useful, then put all of the SQL data into an "SQL Dump" file. 

mysqldump -u srcuser -p srcmakedb > srcmakedb.sql

(Make sure you use this from the command line, not inside of the MySQL prompt. "exit" MySQL before doing this.)

We use the mysqldump command, our username and password, the name of the db, and the name of the file to output too.

Additionally, if someone gave us a MySQL dump file, then we could upload it by changing the square bracket to point "in" to the database.

mysqldump -u srcuser -p srcmakedb < srcmakedb.sql

That command will import an SQL dumped in the file named "srcmakedb.sql". 

And like that, you can locally use MySQL on your computer to simulate a database. However, to get the best results, you should learn SQL to properly query your database's data.

SQL Commands - Cheat Sheet Version

A good resource for learning SQL commands is this one. I use it as a cheat sheet, myself. Read up on what each keyword does, when you need to construct an SQL query, look at the resource to decide which keywords you need.

However, for each SQL keyword, I'm going to give a brief description of what it does, and if you want to see an example, go to that resource that I linked above to see how it's used. 

• SELECT - Get an entire table, or specific columns from a table.
• WHERE - Cmon, it's where.
• INSERT INTO - Adds a row to a table.
• UPDATE - Updates rows in a tablet.
• DELETE - Deletes a row or table.
• ORDER BY - Picks a column to be the order. ASC and DESC specify lexigraphy.
• SELECT TOP - Only picks the top selected items.
• OFFSET - Removes the first selected items.
• SELECT DISTINCT - Selects only the unique values of a column.
• MIN, MAX - Min and Max...
• COUNT, SUM, AVG - You know.
• AND, OR, NOT - Modifiers.
• BETWEEN - Modifier.
• IN - Used instead of OR ranges...
• LIKE - If a string pattern matches.
• NULL - no value.
• GROUP BY - groups the rows by a certain column.
• HAVING - combine with GROUP BY to specify conditions for grouping. Used with COUNT, AVG, etc.
• ALIAS - Change a column name.
• JOIN - Join two tables. LEFT, RIGHT, INNER, OUTER specify the type.
• UNION - Join the results of two queries. 

Conclusion

We've gone over MySQL commands and SQL commands, very briefly. Just use this article as a cheat sheet in case you ever need to set a database up, or need to quickly remember how to make a query by determining which keywords matter. 

As usual, practice helps learn this, but honestly it's also easy to forget if you haven't used it in a while. That's why this article exists.

Here's the video on this topic where I go into great detail.

Like this content and want more? Feel free to look around and find another blog post that interests you. You can also contact me through one of the various social media channels. 

Twitter: @srcmake
Discord: srcmake#3644
Youtube: srcmake
Twitch: www.twitch.tv/srcmake
​Github: srcmake

References:
1. www.dofactory.com/sql/intro