Show * Database MySQL

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.

What is a database && why use one?

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.

Are all databases the same?

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)

• Service/implementation costs.

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
  location

• 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
  department managers)

• 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),
  predefined category

• 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.

How do I interact with relationally stored data?

Remember earlier we stated that the interaction with stored information was done via something called a query. Well that query, is simply a statement to the DBMS containing keywords along with instructions of how the program should alter data you want to target. Unlike other programing interaction verbiage, you may have encountered in the world of code, I find MySQL syntax extremely readable and more closely resembling human speech than JavaScript or syntax.

Keywords such as SHOW, USE, CREATE, SELECT, DELETE, DESCRIBE, INSERT, LOAD DATA, FROM, WHERE, IN, & 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.

Topic Recap:

• a database is an organized collection of electronic information

• many factors influence the type of database you will employ in
  your project

• interaction with stored data is called a query, queries are
  strings

• MySQL query syntax is very human-readable

Conclusion:

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.

Happy Coding!!!

Sources:

• MySQL Docs (https://dev.mysql.com/doc/refman/8.0/en/what-is-
  mysql.html)

• Oracle Docs (https://www.oracle.com/database/what-is-
  database.html)

• “8 Key Considerations When Choosing a DBMS”
  (https://blog.paessler.com/key-considerations-when-choosing-a-
  dbms)

• “Types of Databases” (https://www.tutorialspoint.com/Types-of-
  databases)