Online Class: MySQL

If you've decided to build a website or need a database, MySQL is an open-source option that's very cost effective and affordable. Without knowing how to install, configure, design and maintain the database, you risk having a poor performing environment that can be detrimental to your application. This MySQL course helps new and intermediate level database administrators and programmers understand how to set up run, and maintain MySQL.

We first discuss very basics of MySQL and why the database is popular over other options. We then move on to table design. Table design is the heart of your application, because it determines the way your data is stored. It also determines the effectiveness of data integrity, which protects your data from corruption and inaccuracies. A good table design is paramount for quality software, and we help you understand data design and modeling.

This course covers database maintenance and configuration as well. We explain good architecture and habits that allow you to build scalable solutions instead of configuring your database for a small application that must be then reconfigured for a more scalable project. Scalability should be a primary concern if you want your application and database to grow without issues.

Course Motivation

MySQL has a large portion of the online market. Where Microsoft SQL Server and Oracle were once dominant, MySQL's free architecture and improved interface through the years have made it one of the top three databases used across the world. It's used by some of the largest websites including Facebook and Pinterest. Small startups that use WordPress inherently use MySQL since the application is integrated with the database. Webmasters, ecommerce startups, bloggers, and programmers can all benefit by understanding and learning MySQL administration.

What is MySQL?

There are two regions in a website design: the front-end and the back-end. The front-end is the site design and interface users see when they open the site. The back-end is the database. The database is the power horse for an application. It houses all the data and information needed by the front-end. Webmasters have several options when choosing a database, and one of these options is MySQL.

Before you understand MySQL, it helps to understand database functionality. A database is made up of tables, stored procedures, and functions. These three parts drive the back-end of your application.

Tables are the database engine's storage components. Architecture of database storage is extremely technical, but an easy way to envision a database table is to visualize a spreadsheet. Spreadsheets have columns and rows. Where these columns and rows intercept are fields. The fields contain one item of data.

When you set up a spreadsheet, your columns make up the components of your data. For instance, a spreadsheet that contains customer information has a first name, last name and address column. Each row has a column of information. These rows represent your records. Using the customer example, each row is a customer.

This is a simplified way to explain a MySQL table, but it helps new database users envision how data is stored and represented. If you look at a MySQL table, it's laid out like a spreadsheet of information. The trick is to understand how to retrieve, filter, edit and delete data. This is what we do throughout this course.

Stored procedures are the programming objects within your database. For instance, suppose you want to select the last customer who ordered, and then send that customer an email. You could write a stored procedure. You can view stored procedures as small functions for your application. They perform certain functionality, which usually results in displayed, editing, inserting or updating data.

Functions are similar to regular programming functions. They act as standard procedures that you use regularly in your SQL programming. For instance, suppose you need to add two numbers each time you query your MySQL tables. Instead of writing stored procedure code each time you add the numbers, you can create a function and use it to save you programming time.

Why is MySQL So Popular?

MySQL isn't popular just because it's free. Most free software isn't associated with high-end enterprise solutions, but MySQL is an exception to this rule. MySQL can be used with small startups or high-traffic enterprise applications. Banks, social networks, universities and government agencies use MySQL as their database solution.

MySQL is also secure. Security is a sensitive issue, since the protection of your customer and employee records should be a major concern. With MySQL, administrators can encrypt data and set up authentication to protect all company assets.

There are several more open-source database applications on the market that were deployed after MySQL. MySQL's success is partly due to the numerous hosting companies that offer services with unlimited MySQL database services. Oracle and SQL Server are expensive platforms, and most hosting companies only offer 1 SQL Server if you choose to work with it instead of an open-source database solution. Since MySQL is free and open-source, hosting companies offer unlimited databases with a lower cost than Microsoft platforms. This type of marketing gave MySQL its strong foundation in application development in global cloud and desktop applications.

Relational Data Integrity

MySQL is a relational database. These systems offer data integrity. They are distinct from systems such as Mongo that relies on document style records. Relational databases are used in numerous applications, but the major reason they are used to create applications is for data integrity and transactional style record manipulation.

Relational databases work on the concept of a primary and foreign key relationship. The primary key is the main unique field that identifies a record. This unique identifier is then stored in other tables to build a relationship between a main table and a secondary related table. In this course, we use the concept of a customer and an order. Each customer has its own unique identifier, and then this identifier is stored in the order table. When you need to query MySQL with a list of customers and related orders, you join the two tables using specific SQL syntax.

MySQL is also a transactional database, which means that you can roll back changes to your database. For instance, suppose you want to delete a customer but you don't want to delete the customer if there are active order records associated with the customer. You attempt to delete the customer, but MySQL's relational foreign key constraints give you an error when you attempt to delete it. You can then roll back any changes you made based on commit and rollback features. You commit changes if there were no errors found, or you can roll back previous statements if an error is thrown by the database server.

Data integrity is what sets MySQL and other relational databases apart from more modern databases such as NoSQL. NoSQL databases such as Mongo are used for analytical data and capturing any number of unstructured data. MySQL requires your data to be more structured, so it's a reliable database system for people who want to secure the structure and relationship between tables.

In this course, we review relationships such as primary and foreign keys, transactional changes, committing and rolling back changes, and how structured data is stored in the MySQL database. It's important to understand relationships and relational data to effectively build and maintain a MySQL database architecture. If it's engineered incorrectly, you can have difficulties with performance and maintaining your data. We discuss configuring and maintaining performance on your database. We also discuss replication and how you can configure replicated database servers to create a mirrored backup of your existing MySQL database. We also discuss how you can manage replication for the best performance.