A brief overview of Database Management Systems (DBMS) and proper syntax while interacting with MySQL.
Before we can get into the specific meat and bones of interacting with the MySQL database, we must first do a little bit of explaining what a database is and why you would ever need to use one. Once we have established our base understanding of what and why the specific ways to interact with the MySQL database will be far more relevant to the future success of your web design projects. So without any more waisted whitespace let’s
describe * databases.
A database is simply a specifically organized assemblage of correlated electronic information that can range from a simple list of items, groups of photos, or even massive anthologies of customer-related information from a large corporation. Typically, the data or information housed within these electronic storage containers is arranged into tables, that then displayed in a column x row format, which makes interaction with these stored values far more economical. A Database is usually employed when multiple processes are accessing and altering data concurrently. The database serves to ensure that these simultaneous inquiries, do not overwrite the changes being performed on either the user or server-side. In the case of someone interacting with database information, this task is called performing a “query” and will require you to use particular verbiage to be successful in the appending or retrieving of desired data.
There are serval different types of databases available for use in your next project, but which one you employ is dependent upon not only some personal choices but also upon the project itself. Some considerations to help guide your database management system (DBMS) choice is:
Data structure (how it looks and is to be presented)
Data consistency (usability of stored data)
Data protection (access and encryption of stored data)
Accessibility/integration (the ability of multiple parties to
access and utilize data)
Interaction efficiency/ scalability (response time to
interactions and ability to change data)
Usability (regards how easy it is to work with DBMS)
Once you have considered the factors that will guide your choice you can sit down and choose your DBMS type. Here is a list of some of the types of DBMS:
Centralized – database information is stored at a centralized
Distributed – database information is stored across varies sites
Personal – data collected and stored on personal computers
End-user – shared data designed for end-users (i.e. different
Commercial – for huge data sets, subject-specific, paid access
through commercial links
NoSQL – for large sets of unstructured data
Operational – information related to company operations
Relational – data organized by tables (rows x columns),
Cloud – database built for a virtual environment
Object-oriented – a mixture of object-oriented programing and
relations database structures.
Graph – a type of NoSQL database that uses graph theory to store,
map, and query data relationships
With all these factors to consider and types of databases to choose from you can see just how complex this decision of which database to use in your next project can be. So, in that light, I have included links to this very influential information below in my sources section in the hope that you can utilize it to make the most appropriate choice for your project shortly. As for this blog project we will choose the relational database management system specifically MySQL and highlight how to interact with the tabled values stored within this database.
Keywords such as
ORDER BY are just some of the words and or terms used to interact in specific ways with stored data. Since this is our first encounter with MySQL syntax lets stick with some basic queries and get more advanced in the blogs to come.
Create a new database on server
mysql> CREATE DATABASE test;
View existing databases on the server:
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+
Create a new table
mysql> CREATE TABLE animal (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Display the new table you just made
mysql> DESCRIBE animal; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
Add a value to the table you just made
mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
These simple queries will get you started arranging information in your database by creating a place for and adding more values to your relational tables. We have only scratched the surface of our journey into databases, so I do hope these 4 examples show just how simple yet readable the MySQL query syntax is to understand and if you choose, put to use.
a database is an organized collection of electronic information
many factors influence the type of database you will employ in
interaction with stored data is called a query, queries are
MySQL query syntax is very human-readable
The modern database has evolved into many different choices that will be affected by a multitude of different factors that should be considered before your project beginning. I suggest a little bit of research into some queries into the syntax used in each one before you make your choice. While your free to choose anyone you want, I like the syntax utilized in the RDBMS MySQL for its human readability and structured displays. These may change with greater experience but for now, MySQL is the base I am banking on. With the information highlighted in this blog, I hope to have armed you with all you need to choose the best DBMS for your next project no matter it is big or small.
MySQL Docs (https://dev.mysql.com/doc/refman/8.0/en/what-is-
Oracle Docs (https://www.oracle.com/database/what-is-
“8 Key Considerations When Choosing a DBMS”
“Types of Databases” (https://www.tutorialspoint.com/Types-of-