Get started with MySQL | Mini-Guide

A QUICK REMINDER

What's a database ?

A database is an organized collection of data, usually stored and accessed electronically from a computer system. When databases are more complex, they are often developed using formal design and modeling techniques.

A database will simply record data for us. With a database, you can create data, read data, update data and delete data, etc.
For example, when you are shopping on Amazon, all the items you have seen are saved in a database referencing Amazon items and it is a huge database. A database is a container of data.

There is basically 2 types of database where you will see the word SQL : SQL, NoSQL

SQL databases are relational database.

• SQL database will scale vertically.
• They use structured query language. A Structured Query Language is a language created to manage data inside a Database Management System, like MySQL for example.
• SQL database use tables with rows and columns.

NoSQL databases are non-relational.

• NoSQL scale horizontally.
• They have a dynamic schema for unstructured data.
• NoSQL databases are document, key-value, graph etc..

What is MySQL

MySQL is a fully managed database service using the query language known as ** SQL **. If you've never heard of MySQL, I recommend you take a look at their homepage.

Database management systems are very important, they will help us to: manage security, import and export data, manage a larger set of data and allow us to interact with other software applications such as a language of programming. MySQL is probably the most well-known database management system.

Using SQL / MySQL, we will be able to perform different types of actions and queries in the database.
The different types of actions are sometimes called ** CRUD **.

WHAT IS CRUD

CRUD means:

Create Read Update Delete

These are the four most basic functions that you will perform in a database system.

*For example:
*

Creating a user account.

Accessing an user account.

Updating a user profile picture.

Deleting a old profile picture.

MySQL Download and installation

Let's download and install MySQL, since the MySQL documentation is very good, we will use it.

Windows

Download & Install

MacOS

Download & Install .

Linux(Ubuntu & Ubuntu based distro)

Download and install

Let's connect to MySQL & creating a database

You just installed MySQL and ready to dive into SQL.

On linux you might need to start SQLService

sudo service mysql start

Let's create a simple MySQL database, we will need to connect to the MySQL server from our command prompt/terminal.

*Let's connect as root with the command:
*

mysql -u root -p

** Note: A password will be required to connect to the MySQL server**, you had to set-up a password during the installation, if you forgot your password go check the documentation here .

We will create a new user:

mysql> create user 'testuser' identified by 'testuser';

Note: Instead of testuser, you are free to choose any username you want.

*Now we can create a new database
*

mysql> create database testdatabase;

mysql> grant all on testdatabase.* to 'testdatabase';

Note: Instead of testdatabase, you are free to choose any database name you want.

To be sure that we created a database we can type:

mysql> show databases;

You should be able to see your database

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdatabase       |
+--------------------+

First table & queries

A query is a set of statements using SQL, which will search the database management system for the information you are looking for.

When Googling something you simply perform queries. If you Google space cat, you will get a space cat, it's a search query.

With SQL we will be able to make queries to find data in a large sets of data. With MySQL data are stored inside tables.

Tables

As mentioned above with MySQL our data are store inside tables, tables contains rows and columns.
Below you can a table that contain 3 columns and 3 rows.
Columns are read vertically.
Rows are read horizontally and contains all the information about an individual or a case.
The row for John would contain: John, 24, 67

In our table we defined a primary key(colored in pink) which is NAME. A primary key is an attribute which uniquely define a row.
So we can identify more easily each row.
We couldn't have 2 Johns or 2 Katie's but we could have Katie's height equal to John's height since the HEIGHT column is not a primary key and does not need to be unique; In the name, we could only have a single entry.

Datatype

Inside our tables we can stock different kind of data, in our example below you can see different data types: strings and integers but table can contain:

• Note: param = parameter

*String data types:
*

• CHAR(param) : String with a fixed length that can contain (letters,numbers, special characters) EX: if CHAR(10), CHAR will contain 10 characters and if left empty CHAR will be filled with 10 blank character.

• VARCHAR(param) : String with a variable length that can contain (letters,numbers, special characters) EX: if VARCHAR(10), VARCHAR can contain between 1 and 10 characters. Ex: VARCHAR(40) max number of characters is equal to 40 it's convenient to save space inside your database to limit the number of characters of a string.

• BINARY(param) Store binary bytes type, the parameter specifies the column length in bytes.

• TEXT(param) Store a long string that can hold 21,844 characters.

*Numeric data types:
*

• INT(param) : Integer with a range from -9223372036854775808 to 9223372036854775807. Ex: INT(345)

• BIT(param) : Hold a bit value type with a range from 1 to 64. Ex: BIT(32)

• BIGINT(param) : Big integer with a range from -9223372036854775808 to 9223372036854775807, Ex: BIGINT(3465242552342453)

*Date and Time data types:
*

• DATE(param) : Store a date with the format YYYY-MM-DD, EX: DATE("2017-06-15");

• DATETIME(param): Store a date with the time, format YYYY-MM-DD hh:mm:ss. EX: DATETIME("2017-06-15 10:20:45")

• TIME(param): Store the time, format hh:mm:ss. Ex: TIME("10:20:45")

Creating a table

It's time to create a table using SQL for that we will need an SQL editor.

Let's download an SQL editor and database manager software called Beekper Studio. Site
It's free and open-source <3

Launch the software and click on quick connect, then you'll end up with this window.

Connection type: MySQL
Host: localhost
Port: 3306
User: the username you created.
Password: the password you created before
Default Database: The name of your database, i used testdatabase.

Before to connect click on Test to see if your credentials are the good one, then let's connect.

You'll end up on an empty window with a tab query#1, the magic will happens here.

Before to start check quickly the documentation of Beekeeper Studio.

Let's create a new table that describe products from an e-commerce.

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(40),
    price INT,
    bar_code INT,
    creation_date DATETIME,
    type VARCHAR(40)
);

DESCRIBE product;

Let's go step-by-step:

• CREATE TABLE my_table_name(); will create a table, pretty explicit right?
  Note: At the end of each query you should use a ";" semicolon. 1 semicolon = 1 query

• Inside the bracket () we will define each columns.

product_id is the name, INT(integers) is the type of data, PRIMARY KEY define product_id as the primary key of the table. Primary key are unique and very important, choosing an adequate name for the PRIMARY key is important.

product_name, VARCHAR which is a string and inside the bracket you can specify the number maximum of characters to save space inside the database.

We repeat the operation every time for each columns; DATETIME(YYYY-MM-DD HH:MM:SS.SSSSS) correspond to a type of data that take the date and time.

Click on your query and then click on the yellow button Run Selection at the bottom right of your editor.
You now created your first table but yet we can't see anything.

let's run DESCRIBE product to show the table, click on the query and click on Run Selection.

DESCRIBE product;

Here you are, we should be able to see our table.

• We can see each columns field; Type,(Null: Null mean that the column is not defined, the primary key need to be defined so Null = NO); Default(value), Extra(comments for example)

Note: SQL Convention

For now we will keep it simple. See CREATE, TABLE, INT,VARCHAR, DATETIME, DESCRIBE. It's the SQL reserved syntax and need to be in CAPITAL letter, it's not necessary and even non-capitalized it will works, it's simply a convention.

There is a tons of convention for SQL and actually the convention might differ depending of the company/project, the best resource that I found about SQL convention is here.

Delete a table

Let's delete our table with:

DROP TABLE product;

Now our table is deleted. Don't delete the previous queries that we wrote. You can 1 query each time, so feel free to delete and re-creating the table.

Altering a table

We will use the 'ALTER' statement which is used to delete,add, modify columns in a table. 'ALTER' can also be used to ADD and DROP.

Let's add a column in our table.

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE product
ADD weight INT;

then

DESCRIBE product;

You should see the new column in the table located at the last position.

Now let's delete the column weight.

ALTER TABLE product
DROP COLUMN weight;

Let's check it:

DESCRIBE product;

The column has been deleted.

The 'ALTER' statement is very useful, we also could change the data type of a column like this:

ALTER TABLE product
MODIFY COLUMN bar_code VARCHAR(60);

Now if you run DESCRIBE table_name you should see that bar_code is now a VARCHAR limited to 60 characters.

DESCRIBE product;

Constraints

A SQL constraint is a rule that is specified inside a column.

• PRIMARY KEY is a constraint it specify that the column will be a PRIMARY KEY.

• UNIQUE It make sure that all values in a column are different. A PRIMARY KEY is also a unique value by default. If you want your value to be UNIQUE in a column, you'll use the constraint UNIQUE.

• FOREIGN KEY is a constraint that link a column or a group of columns to another table. It will basically link multiple tables from the specified column with the constraint FOREIGN KEY. The table that contain the FOREIGN KEY is called the child table. The FOREIGN key is a way to link a parent table to a child table.
  *With foreign keys, databases can become relational databases.
  *

We will link the column product_name to a child table that we will create but before we need to do something.
When you link two tables with a FOREIGN KEY make sure that the column that will have the FOREIGN KEY constraint is either a PRIMARY KEY or has the constraint UNIQUE. In our case we need to choose a column that will link our 2 tables and modify the column.

Let's choose the column: product_name VARCHAR(40)
We need to make this column UNIQUE:

ALTER TABLE product
MODIFY COLUMN product_name VARCHAR(40) UNIQUE;

Let's see if your column took the constraint UNIQUE with.

DESCRIBE product;

in the column key we can see now that the column is having the constraint UNIQUE.
*Note: In my screenshot the VARCHAR is (60) due to a typo, keep (40) like in the code sample above.

Let's make a child table called product_type and define a FOREIGN KEY.

CREATE TABLE product_type (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(40),
    sub_category VARCHAR(20),
    FOREIGN KEY (product_name) REFERENCES product(product_name) ON DELETE CASCADE
);

We define:

• A primary key called category_id ( Don't mind the AUTO_INCREMENT)
• A product_name column that will be our foreign key.
• A sub_category column

Then we define the foreign key with this query :

FOREIGN KEY (product_name) REFERENCES product(product_name) 
ON DELETE CASCADE;

FOREIGN KEY (my_column) REFERENCES parent_table(parent_table_column);

First you specify FOREIGN KEY in the column that will be the FOREIGN KEY inside the child table (product_type),
REFERENCES the parent table and inside the bracket the (column) you want to link from the parent table.

The column name in the parent table and in the child table don't need to have the same name but need to be the same data type. You can't link 2 tables with a different type of data, it doesn't make sense and wouldn't work.

ON DELETE CASCADE delete means that if a record in the parent table is deleted the linked records in the child table will be deleted.

ALTER TABLE product
ADD FOREIGN KEY (product_name) REFERENCES product(product_name);

• NOT NULL The column cannot have a null value. When you'll fill the row with data you will need to fill the column with some data, if you tried to let the column without data(null), you won't be able to fill the row with data.

In the child table product_type I'd like to make the column sub_category always filled with a value, for this I will need to use the constraint NULL.

Note: PRIMARY KEY are NOT NULL by default.

ALTER TABLE product_type
MODIFY sub_category VARCHAR(20) NOT NULL;

DESCRIBE product_type; 

In the column sub_category, NULL became 'NO' instead of 'YES'.
It means that this column will need to be fill with data when we will fill the table.

You can also specify a column with the constraint NOT NULL while creating the table. In fact you can specify in kind of constraint when creating a table.

CREATE TABLE example_table (
    id INT  PRIMARY KEY,
    name VARCHAR(40) DEFAULT 'User',
    age INT NOT NULL,
        account_creation DATE
);

• DEFAULT gives a default value to the column, above you can see that name VARCHAR(40) DEFAULT 'User', has the DEFAULT constraint and give to the name column a default value of 'User'.

Example:

ALTER TABLE example_table
ALTER account_creation SET DEFAULT (2000-01-01);

AUTO_INCREMENT

Previously you saw AUTO_INCREMENT. It's very a very useful tool, it increment by 1 automatically when a new record is inserted into a table.

Example:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

The result will be :

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Each time we inserted a value inside the column name the id incremented automatically by 1.

SQL Syntax and QUERIES

Now you should be able to create and modify tables and specify constraints but yet we didn't inserted data inside our table.

Let's insert values inside the product table.

INSERT INTO

INSERT INTO product VALUES(1,'TV', 35, 11109474, '2020-01-01 10:10:10');

INSERT INTO my_table VALUES(id, product_name, price, bar_code, creation_date)

The INSERT INTO statement is used to insert new data into a database.

VALUES() is the place where you will fill the values that you want to put inside your database, they should correspond to the columns you made and to the constraint of the column.

If i try to insert a product with the same product_name it won't work because the column product_name as a constraint of UNIQUE.
Try it.

INSERT INTO product VALUES(2,'TV', 23, 863544, '2020-01-01 10:10:10');

Inserting values only in certain columns is also possible.

INSERT INTO product (product_id, product_name)
VALUES (7, 'Microwave')

SELECT

We will need to select values from our database for that we will use the SELECT statement.

SELECT * FROM product;

SELECT * (all) FROM my_table.

We can also select certain columns from the table.

SELECT product_id, price FROM product;

Here some data for your table product.

INSERT INTO product VALUES(2,'Video-Games', 135, 11103474, '2020-01-01 10:10:10');
INSERT INTO product VALUES(3,'Blue T-Shirt', 14, 11209474, '2020-01-01 10:10:10');
INSERT INTO product VALUES(4,'Asus Computer', 700, 21105474, '2020-01-01 10:10:10');
INSERT INTO product VALUES(5,'Desktop Monitor', 210, 11409474, '2020-01-01 10:10:10');
INSERT INTO product VALUES(6,' Apple Keyboard', 63, 11789474, '2020-01-01 10:10:10');

We can be more specific using the WHERE statement. I'd like to select a product where the column is equal to 'video-games'.

SELECT * FROM product
WHERE product_name='Video-Games';

The query can be even more precise with the AND, OR, NOT statement.

SELECT * FROM product
WHERE NOT product_name='Video-Games' AND NOT product_name ='TV';

We will select all the row except the ones where the column product_name contain 'Video-Games' OR product_name ='TV'

Here what result you should have.

ORDER BY

We can get the result of a query ordered in a an alphabetic way or numeric with ORDER BY.

Example.

SELECT * FROM product
ORDER BY product_name;

The table is know ordered alphabetically from product_name

We can also reverse the order with DESC.

SELECT * FROM product
ORDER BY product_name DESC;

We can specify 2 columns with an ascendant and a descent order.

SELECT * FROM product
ORDER BY product_name ASC,  product_id DESC;

MIN() and MAX() with GROUP BY

MIN will return the smallest value in the selected column and MAX will return the largest value.

If we want to know the most expensive product in the table we could do.

SELECT MAX(price)
FROM product; 

Return the largest value in price.

We can return a GROUP, where we find the max value for every group. It's a little confusing so let's try.

SELECT product_id, MAX(price)
FROM product
GROUP BY product_id 
ORDER BY MAX(price);

Let's decompose the QUERY SELECT column, MAX(column)
from table
GROUP BY (first_column shown in the result)
ORDER BY (second_column that you want to be ordered by value)

It will works the same way with MIN().

IN OPERATOR

IN is an operator useful to specify multiple values with WHERE.

SELECT * FROM product
WHERE product_name IN ('TV', 'Video-Games', 'Desktop Monitor');

AVG(), SUM(), COUNT()

*COUNT()
*
Count will return the number of column that match a condition.

SELECT COUNT(price)
FROM product
WHERE price < 500 ;

Will return the count of all columns that have a price less than 500.

We used < as an operator but you can also use <, >, <=, >= ,<>(Not equal to).

SUM

We could make the sum of the price column.

SELECT SUM(price)
FROM product;

You can also specify a condition.

SELECT SUM(column)
FROM table
WHERE condition; 

AVG

AVG stand for average, let's make the average of the price in the column product.

SELECT AVG(price)
FROM product;

You can also put a condition in your QUERY with WHERE.

SELECT AVG(column)
FROM table
WHERE Condition;

LIKE

LIKE is very useful it helps use to find a specified pattern in a column.

For example you want to find a certain brand in the product table like an Apple keyboard then we can use LIKE.

SELECT * FROM product
WHERE product_name LIKE '%Apple%';

Underscore _ is be used for 1 single character, % sign is used for 0, 1 and multiple characters.

UNION

UNION can link 2 results from 2 tables.

First insert data your child tables with data.

INSERT INTO product_type VALUES(2,'Video-Games', 'games');
INSERT INTO product_type VALUES(3,'Blue T-Shirt', 'clothing');
INSERT INTO product_type VALUES(4,'Asus Computer', 'computer');
INSERT INTO product_type VALUES(5,'Desktop Monitor', 'pc monitor');
INSERT INTO product_type VALUES(6,' Apple Keyboard', 'Apple accessories');

SELECT product_name FROM product
UNION
SELECT sub_category FROM product_type
ORDER BY product_name;

We linked the column product_name from the parent table to the column sub_category from the child table.

UPDATE

Sometimes you will need to update some data or columns

You need to use WHERE to specify which row otherwise all the records will be updated.

UPDATE product
SET product_name = 'Green-shirt', price = 16
WHERE product_id = 3;

DELETE

We can delete one value at a time.

DELETE FROM product WHERE product_name=''Microwave 

It's also possible to DELETE all the records and row without deleting the table itself .

DELETE FROM product;

Conclusion

With all of this you should be able to do some queries, making tables . MySQL is very popular and i encourage to go deeper than this article and to check more about, in the official documentation. I didn't followed the standard convention but you can find more about SQL convention here.
This article cover a very limited part of MySQL but will give you the very basic. It's just the beginning of the journey into DMBS.

Feel free to @ me on Twitter with your opinion & feedback about my article;
It's the first time that I'm publishing an article be indulgent with your feedback.