MySQL Tutorial: the beginner's guide to using MySQL 
Tags: mysql, sql, database, tutorial
Published: 2020-07-21 22:34:18

## What is MySQL?
MySQL is an open-source relational database management system used throughout the biggest companies in modern tech.  

Since its creation, MySQL has established itself as the industry standard for relational database creation and manipulation, due to its applicability across multiple languages, intuitiveness, and scalability. But how does it work?

To understand the MySQL database, we must break down the underlying branches of tools it belongs: database management systems and SQL Tables.

Afterwards, we’ll move to an example of how to construct, populate, and manipulate our very own MySQL program using standard SQL!
<br>

Today, we will discuss:
* [Overview of Database Management Systems](#overview) 
* [Intro to SQL Tables](#tables) 
* [Breakdown of MySQL Parts](#parts)
* [Advantages of MySQL](#advantages) 
* [Common Data Types in MySQL](#types)
* [Examples and Further Practice](#example) 
* [Resources](#resources)

Let's get started! 

<br>


![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ne3o3a9f7ah5elt6vpjn.png)

<br>
## Overview of Database Management Systems  <a name="overview"></a>

Broadly speaking, database management systems are a branch of tools used by developers to create, maintain, and delete multiple databases. By streamlining the process of database creation, these systems enable developers a quick and user-friendly way of creating databases, specifying subsets of data through tables, and manipulating the data within each.

They also come with many quality-of-life benefits such as: 
* Automatic indexing which updates after the addition of a new item and frameworks for working with multiple tables at once,
*	Referential integrity assurance, which ensures all indexes are logged and linked if the same data appears multiple times across different tables,
*	The ability to combine data from various separate tables for completion of a single user request.

<br>

## Intro to SQL Tables  <a name="tables"></a>

Another important aspect to understanding MySQL is to become familiar with [Structural Query Language Tables (SQL Tables)](https://www.educative.io/edpresso/what-is-sql), the underlying component of any MySQL program from which it is built. 

In short, SQL Tables are a way of storing data in digital tables. These are organized into columns with a rigidly defined specification that determines the type and size of all data within that column, acting as a vital method of implementing each [database’s schema](https://www.educative.io/edpresso/what-is-a-database-schema). This SQL Table system is used not only by MySQL but by many other relational database management systems available today.

This is called a **relational database** as the table columns organize data points based on the shared attributes of the data, in which specific indexes can then be found.

For more information about database design, see our recent article on [Database Design](https://www.educative.io/blog/database-design-tutorial) or our Edpresso Shot on the differences between [SQL and NoSQL systems](https://www.educative.io/edpresso/whats-the-difference-betweensql-and-nosql).

<br>

## Breakdown of mySQL Parts  <a name="parts"></a>

Like other relational database management systems (RDBMS), every aspect of MySQL is broken into tiers based on relationships. Each tier lower gets more and more specific, allowing developers to finely tune each subset’s required accessibility throughout the rest of the program.

<br>

#### mySQL Server Layer
The core of MySQL is the MySQL server, which is accessible as a separate library and program, which can be embedded into your applications to handle database commands. All aspects of a MySQL program interact with the MySQL server in some way.


<br>

#### Database
This is the highest and broadest of our tiers, acting as a container for all tiers below it. It is a structured set of data, without which MySQL would not function. Multiple databases can be present in any given program, allowing developers to include widely separate subsets of tables within the same program.

For example, one household could create a single MySQL program containing separate databases for each member, organizing all later data tables to pertain to their defined family member.


<br>

#### Tables
Our second largest tier, tables are the storage locations for related data subsets. Multiple tables can exist under a single database, with one table per user-defined category. Queries must specify in which table they are working, ensuring accidental crossover or prolonged runtime. This is also helpful for privacy, as sometimes we may want to print one of our tables, but keep the other hidden for the current operation.

Continuing our example, each family member database in our household program may have one table for requested birthday gifts, `TABLE GIFTS`, and another for private passwords, `TABLE PASSWORDS`.

<br>

#### Columns
Columns further break down tables as each defined column includes a data type, which all values within that column must be. Each column in a table can be a different data type, and further splits the data into contextual subsets. Columns are especially helpful for specifying what information all rows in the table should have (even if it’s just `NULL`) and storing that row’s value separate from all other rows.

For example, our previous table `GIFTS` could have three columns: one which lists the product name, `ProductName`; one which lists the product seller, `ProductSeller`; and the other which lists the price, `Price`. The first two of these columns would contain string values while the latter would contain a numeral value. 

<br>

#### Row
Rows are the smallest tier and, in many ways, act similar to columns; whereas columns denote a relationship within a type, rows are grouped based on less explicit ways. Each row in a table has a specific key that points to it.

This is best understood through an example. Within `GIFTS`, we may insert a row, which has determined values for each column: “Mug” for `ProductName`, “Mug Co.” for `ProductSeller`, and “15.50” for `Price`. Without these being on the same row, the user would have no way to see a connection between any of these three variables as they are different values and different types. However, since they are listed under the same row index, it is easy to see that all of these values pertain to the same object.

<br>

#### Query
In MySQL, a query is any command which retrieves data from a table. This is most commonly done through the `SELECT` command and is the most common type of command used for table operation.

<br>

> ## Keep the learning going.
> 
> Learn MySQL and SQL without  Lambda without scrubbing through videos or documentation. Educative's text-based courses are easy to skim and feature live coding environments - making learning quick and efficient. 
> 
> <b>[Introductory Guide to SQL](https://www.educative.io/courses/introductory-guide-to-sql) </b>

<br>

## Advantages of mySQL  <a name="advantages"></a>

As a relational database management system, MySQL comes with all the above advantages of Database Management Systems and SQL Tables built-in, but also innovates upon that foundation with some unique features. Below is a breakdown of MySQL's biggest strengths:

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/zpibgugesulj07w9t3yd.PNG)

<br>
## Common Data Types in mySQL  <a name="types"></a>

Before jumping into the code example, let’s first look at the syntax for MySQL’s basic data types, what makes each unique, and layout when each would be used.

<br>

### String Types
#### CHAR(x)
Our first string type is `CHAR`, which has a decided character requirement, x, specified when defined. Should the string be less than the specified length, MySQL will pad the string with a number of spaces until the character limit is reached. This is the most memory-efficient string type but can be restrictive given the rigidity of the character requirement.

For example:

If column `ProductName` was created as type `CHAR(10)` then the stored value of “Mug” would include seven spaces after the word, “Mug“.

#### VARCHAR(x)
This string type is remarkably similar to `CHAR` except that it can allow for varied character length and does not pad with spaces. The string, however, cannot contain more characters than the amount, x, specified when defined; if a string does exceed the character limit, it is truncated to fit appropriately.

`VARCHAR` uses slightly more memory than equal length `CHAR` types, however, the difference is so small (5 bytes as opposed to 4 for the same four-character string) that it would hardly be noticed unless on a large scale project. As a result, `VARCHAR` is more widely used as it stores data in a cleaner form without a considerable downside.

<br> 

### Numeral Types
#### Integer
For those familiar with other programming languages, `INTEGER` type will be quite familiar. This data type accepts a whole number between -2147483648 and 2147483647.

Adding onto this SQL standard, MySQL also allows for subtypes of `TINYINT`, `MEDIUMINT`, and `BIGINT`, which each have different minimum and maximums, as well as different storage requirements:

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/eznsq847h9ghxq42abfr.PNG)
<br>
As we can see from the table, each of these types of integers has a different storage requirement. To maximize the efficiency of programs, it is best to use the smallest applicable integer type for any given column.

#### Numeric(M,D)
The `NUMERIC` type stores exact numeric data values in decimal form. When a column of this type is defined, it can be given a specification for the max number of significant figures possible, M, and, D, the number of digits to the right of the decimal place to keep. These affect the range of the possible numbers, as with `NUMERIC(4,2)`, the range would be -9999.99 to 9999.99. It also allows the If left unspecified, 10 is the default value for M and 30 for D.

As with `INTEGER`, it is best, generally speaking, to keep the range as small as is applicable to the situation.


For example, `NUMERIC(4,4)` column `Price`, would store 15.50 as “15.5000”, as there are four significant figures and two to the right of the decimal. If `Price` were instead `NUMERIC(5,4)`, 15.50 would instead be stored as “015.5000”.
 

<br>

### Special Types
Special types refer to data that is neither string nor numeral in form, making the category broad in range. While there are dozens of special data types in MySQL, we’ll just be looking at some of the most simple and readily used, being `DATE` for storing dates and `ENUM` for data with only select valid responses.
#### DATE and DATETIME
The `DATE` type stores date information in a “yyyy-mm-dd” form, from years between “1000-01-01” and “9999-12-31”. 

For more precise timestamps, we can also use the `DATETIME` which includes all information of the `DATE` type but also includes a timestamp in hh:mm:ss form. This time is then automatically converted to UTC for storage and will be converted back to the local time zone at retrieval. 

The advantage of these forms is that they can be sorted correctly by date as well as by select aspects of the date, such as month or year, without considering other unwanted aspects of the date.

#### ENUM(a, b, c)
The `ENUM` type accepts a string value chosen from a set of allowed answers defined at column creation, shown here as `a`, `b`, and `c`. 

If an invalid value is entered, MySQL instead stores a blank string as a special error case. This is most commonly used when creating fields that must both be filled and have only certain acceptable options.

For example, if we made another table, `MUGS`, to store information about our favorite mugs from Mug Co. we could have a column which specifies the color of the mug from their three available options:

`ENUM('Red', 'Blue', 'Yellow')`

Here, our valid choices are ‘Red’, ‘Blue’, and ‘Yellow’; should a row be inserted with this column’s value as ‘Purple’ MySQL, will instead save a blank string.

<br>

## Example: Storing Support Tickets  <a name="example"></a>
Up until now, we’ve been working with a more personal use of a MySQL problem used as a collection of written lists. Now, we’re going to move to an example of how a standard SQL MySQL program may be constructed to handle support tickets for our small website management company called Websites Incorporated, working from database construction to data insertion and access.
 
To further hone your MySQL skills beyond this exercise, take a look at the  [Introductory Guide to SQL](https://www.educative.io/courses/introductory-guide-to-sql), fit for those of you just starting your MySQL journey looking to work towards mastery!

<br>


### Creating our Database, Table, and Columns
First, we create our database `CustomerService`, in which we’ll store all tables related to that department. We then use the `USE` to choose `CustomerService` as the target of our later commands.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/tbumoendq94dohicehuo.PNG)

<br>
Due to the small size of our business and the power of MySQL, we could theoretically have databases for each department in Websites Incorporated, however, we’ll be focusing on `CustomerService` for this exercise.

Once we have our database for the Customer Service branch, we create a table to store all the support tickets we receive, called `Tickets`.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/poqfy5mqy33jtwcempyl.PNG)

<br>
In this table, we define five columns of data which every ticket will have:
*	`Username` which will store the customer’s account username as a string (of 25 maximum characters).
*	`Email` which stores the customer’s email as a string (of 50 maximum characters) for later reply.
*	`DateRecieved` to keep track of when each ticket was received by the program (in SQL DATE form, YYYY-MM-DD).
*	`Device` to note which of our device mediums, either ‘Mobile’ or ‘Computer’, the problem was encountered on.
*	`Description` which stores the customer’s written problem report.

<br>
To verify that these have been created, we issue the `SHOW TABLES` command.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/5ejt5fxg7010apbu5boi.PNG)

<br>
If we wanted to learn more about our columns, we use `DESC TICKETS`.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/9fxhbwla90v2hdd44mwx.PNG)

<br>
Here we see the name and type of each column with `Field` and `Type` sections. We also see whether the column can accept NULL via the `Null` section, if the column is currently indexed with `Key`, the default value for each column with `Default` and any special information, such as if the column was created with the `Auto-Increment` option, with the `Extra` section.

<br>
### Inserting Rows
Now that we have our table created and columns defined, let’s populate it with some data. To do this, we’ll use the `INSERT` command to add a row and specify it’s value for each of our five columns.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/41dd8sjmwxyq46bkko5t.PNG)

<br>
Here we add a single row for a ticket by user “PURPLEPANDA31”, first listing the columns which will be populated, then defining values for each column field on this row.

We can also add multiple rows with a single use of the `INSERT` command, by including another row in the `VALUES` section.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/9rj0o6gklis2p7cz5c23.PNG)

<br>
The `INSERT` command can be used to simultaneously add a number of rows up to 65535 ÷ number of columns, over 13,000 for this example! This means that for most applications, entire tables can be filled with just a single `INSERT` execution.

<br>


### Querying Values
Now that we have our table populated, we need a way to read, or query, the values within. For this, we use the `SELECT` command, which can query an entire table, select columns within the table, or just the rows which have certain values in a specified column.

For our first query, we’ll print the whole table specifying that we want all columns printed with `*` in the columns field of `SELECT`.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dc95lzgpds3pr4t7s4yn.PNG)

<br>
We can also query only certain columns of the table, for example, if we are only concerned about the date of each of these problems we could print a table with only the `Username` and `DateRecieved` columns.


<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jnbsgsjunoqrfbp419hj.PNG)

<br>
Finally, if we want to only print the tickets which occurred on the computer version of our site, we could use the `WHERE` modifier in conjunction with `SELECT` to both search and print our desired rows at the same time! Here, we’ll only be interested in tickets of issues on the computer, so we specify that our `Device` column must equal “Computer” to be queried.

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hzjsgy9iez86s9qw7d2b.PNG)

<br>
### Ending our Program
Now that we’ve walked through the basics of creation, insertion, and selection within our MySQL program, all that’s left is to end the program and clear your workspace for bigger and better MySQL projects to come! 

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/16j7t6p2vnaeqaekk83n.PNG)

<br>
## Further Practice  
If you’d like more practice, try creating a MySQL program of your own in the terminal below, using the above example as a guide.

<br>


### Prompt

Create a database for another branch of Websites Incorporated, `DatabaseDesign`. In this database, make a two-column table, `EmployeeOps`, to store information about which operating system each employee works on. The first column will store the employee’s name, and the second will store if they are using either “macOS”, “Windows” or “Linux”. Insert rows, one for each employee, try at least one employee for each operating system. Finally, query all columns of any row where the employee uses “Linux”.

Try it yourself first then check against the solution shown below!

<br>
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/afvdousmtvg61uclo6i6.PNG)
<br>

## In Closing and Resources  <a name="resources"></a>

From the example above, you can see how simple and effective MySQL is at creating highly structured collections of data. Due to its ease of use, applicability, and prevalence throughout the modern tech world, it’s no wonder why MySQL has and will continue to be an essential tool for aspiring developers and entrepreneurs alike!

**Interactive Course:**

* [Introductory Guide to SQL](https://www.educative.io/courses/introductory-guide-to-sql)

**Articles:**
* [Database Design Tutorial](	https://www.educative.io/blog/database-design-tutorial )
* [What is SQL?](https://www.educative.io/courses/introductory-guide-to-sql)
* [MongoDB vs PostgreSQL: what to consider when choosing a database
](https://www.educative.io/blog/mongodb-versus-postgresql-databases)