7 SQL Databases
Uploaded by
AEROSPACE ENGINEERS7 SQL Databases
Uploaded by
AEROSPACE ENGINEERSSQL Databases
An overview of the most common relational database management system.
WE'LL COVER THE FOLLOWING
• MySQL
• PostgreSQL
• MariaDB
• The transition to NoSQL
In the previous lesson, we discussed the role of database management systems
in web development and learned that one type of DBMS is SQL. Now, we will
discuss popular SQL Database Management Systems that are available to use
in web development.
MySQL #
MySQL is amongst the most commonly used database management systems,
and it has formed an integral part of most web applications in the past.
MySQL is backed by Oracle and uses Standard Query Language (SQL) to store
data in the form of a table, and to retrieve data requires making queries in
SQL. MySQL, unlike SQL itself, works across all platforms, including Linux,
iOS, and Windows and it has all the simplicity of SQL as it does not require
any new syntax to be learned once you know SQL. Let’s look at an example of
a MySQL query that creates a new table:
CREATE TABLE [IF NOT EXISTS] table_name(
column1 DATATYPE,
column2 DATATYPE,
column3 DATATYPE,
column4 DATATYPE,
PRIMARY KEY (column1)
) ENGINE=storage_engine
As is clear from the example above, MySQL uses basic SQL queries to create
tables and handle data. This is one of its primary advantages as SQL is not
difficult to learn and highly ubiquitous, which means that MySQL is relatively
easy to use for most beginners who already know SQL.
PostgreSQL #
PostgreSQL, also known as Postgres, is an open-source object-relational
database management system that can handle large amounts of data and can
provide seamless support for web applications that handle multiple
concurrent users. It is the default database for macOS and provides efficient
data management across platforms. Moreover, it also supports concurrent
database accesses and allows users to add custom functions developed using
several programming languages, including C, C++, and Java.
In addition to this, another primary benefit of PostgreSQL is that it is designed
to be extensible. This means that users can define their own data types and
even custom plugins to alter parts of the system according to their
requirements.
MariaDB #
MariaDB is a fork of MySQL, which was created by the original developers of
MySQL after MySQL’s acquisition. It is meant to be a replacement of MySQL
and includes a wide selection of storage engines so it can easily work with
data from other relational data management systems. MariaDB, like MySQL,
uses a standard querying language, which makes it just as simple to use as
MySQL. Moreover, MariaDB runs on a number of operating systems and
supports a wide variety of programming languages. In particular, it offers
support for PHP, which, as we will later learn, is one of the most popular web
development languages.
In addition to having all the benefits that MySQL poses, MariaDB also offers
many operations and commands that are not available in MySQL and replaces
features that tend to impact performance negatively.
Common SQL Databases
Quiz on SQL Databases
1
The queries in MySQL are not the same as simple SQL queries
COMPLETED 0%
1 of 3
The transition to NoSQL #
Speed is amongst the most important factors that affect user experience on a
web application. Relying on an unnecessary number of calls to a database
usually tends to slow things down for users, and it is, therefore, important for
web developers to keep some commonly requested chunks of data in
immediate storage so that they can be retrieved quickly, without creating
large latencies in displaying data. To do so, web developers need to think
about how to get as much data in memory as reasonably possible, and then
how to get things cached at the file system level to avoid making calls to a
database altogether. The reason for this is that retrieving data from a
database is the bottleneck of most web applications, and decreasing these
operations can significantly improve responsiveness. The process of selecting
commonly requested data components and storing them in immediate
memory to avoid extraneous database calls is referred to as caching, and
NoSQL databases have gained popularity in recent times because of their
ability to automatically cache data to enhance performance.
In addition to this, the database calls themselves are more inefficient in SQL
databases than in NoSQL databases. This is because SQL databases store data
in tabular form, and queries require comprehensive iterations of the table.
NoSQL databases, on the other hand, use data structures to store entries,
which means that lookups are always optimized and database calls are much
more efficient. Consequently, with modern web applications increasingly
requiring fast response times to achieve intended functionality, the shift
towards using NoSQL databases has been apparent in recent times.
That’s all on the various SQL database management systems available to use
with your web application and how web developers have transitioned to
using NoSQL database management systems. In the next lesson, we will be
discussing examples of the second type of DBMS, NoSQL.