Course Description

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.

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.

Because
companies can go from small startup to large enterprise without
changing its database solution, MySQL is scalable. Scalability is often
disregarded when startups have tight budgets, but since MySQL is free,
it's a good option for a startup that expects growth. It's also high
performance. Performance is paramount to application stability. Small
databases with only a few records will normally function well even if
they are poorly designed. However, if the database tables grow to
millions of records, application performance and stability can be
affected, which then affects your customers and employees.

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
bigger businesses, MySQL and NoSQL databases work together. The MySQL
database stores structured data such as orders and customer information,
and the NoSQL database stores unstructured data such as marketing and
traffic numbers. You can export data from a MySQL database to a NoSQL
database to work with them both for their best features.

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.

Completely Online

Self-Paced

Printable Lessons

Full HD Video

6 Months to Complete

24/7 Availability

Start Anytime

PC & Mac Compatible

Android & iOS Friendly

Accredited CEUs

❮

❯

Course Lessons

Average Lesson Rating:

"Extraordinarily Helpful"

(447 votes)

Lesson 1: Introduction to MySQL

Webmasters, ecommerce startups, bloggers, and programmers can all benefit by understanding and learning MySQL administration. 1 Total Points

Lesson 1 Video

Complete Assignment: Introduction : Why are you taking this course?

Lesson 2: Installing MySQL

Learn how to properly install MySQL for your platform of choice. 11 Total Points

Lesson 2 Video

Complete Assignment: MySQL Installation

Complete: Lesson 1 and 2 Exam

Lesson 3: Designing Databases

Redesigning a database is not an option for most large businesses. You need to get it right the first time. 10 Total Points

Lesson 3 Video

Review 2 Articles: An Explanation of the Database Build Process; Five Steps to Ensure the Success of Your Database

Complete: Lesson 3 Exam

Lesson 4: Basic SQL

This lesson explains the basics of all four main SQL statements: UPDATE, INSERT, SELECT, and DELETE. As a database administrator or programmer, these four statements are what you?ll work with in daily tasks. 9 Total Points