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!
Today, we will discuss:
- Overview of Database Management Systems
- Intro to SQL Tables
- Breakdown of MySQL Parts
- Advantages of MySQL
- Common Data Types in MySQL
- Examples and Further Practice
- Resources
Let's get started!
Overview of Database Management Systems
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.
Intro to SQL Tables
Another important aspect to understanding MySQL is to become familiar with Structural Query Language Tables (SQL Tables), 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. 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 or our Edpresso Shot on the differences between SQL and NoSQL systems.
Breakdown of mySQL Parts
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.
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.
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.
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.
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.
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.
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.
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.
Advantages of mySQL
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:
Common Data Types in mySQL
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.
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.
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:
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”.
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.
Example: Storing Support Tickets
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, fit for those of you just starting your MySQL journey looking to work towards mastery!
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.
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.
In this table, we define five columns of data which every ticket will have:
-
Usernamewhich will store the customer’s account username as a string (of 25 maximum characters). -
Emailwhich stores the customer’s email as a string (of 50 maximum characters) for later reply. -
DateRecievedto keep track of when each ticket was received by the program (in SQL DATE form, YYYY-MM-DD). -
Deviceto note which of our device mediums, either ‘Mobile’ or ‘Computer’, the problem was encountered on. -
Descriptionwhich stores the customer’s written problem report.
To verify that these have been created, we issue the SHOW TABLES command.
If we wanted to learn more about our columns, we use DESC TICKETS.
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.
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.
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.
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.
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.
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.
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.
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!
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.
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!
In Closing and Resources
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:
Articles:
Top comments (0)