MySQL: OpenSource Data Storage

2008 October 02 | 05:59 pm

Introduction

MySQL is what is known as a relational database management system (RDBMS). A RDBMS is a database management system (DBMS) that is based on the relational model. A RDBMS should meet at least these two requirements:

• Present the data to the user as relations.
• Provide relational operators to manipulate the data in tabular form.

MySQL (pronounced My Es Que Ell) is an OpenSource, cross-platform database system software written in C/C++, and is available under GNU Public License. MySQL is commonly paired with PHP as part of a content management system (CMS), such as Drupal, Wordpress, etc. MySQL uses a modified version of standard SQL syntax.

Advantages?

One very obvious advantage to using MySQL as a storage engine is that, like most OpenSource software, it is completely free. Being OpenSource also means that a development team can dig into it's codebase and make modifications if necessary, or extend an existing library. The cross-platform nature of MySQL also means that your team is not tied to using a specific operating system or hardware vendor.

Drawbacks?

One big gripe with MySQL is that it has (in the past) not supported such niceties as stored procedures or triggers, features that most other major versions of SQL have supported and that have been shown to increase database performance. Recent press releases by Sun and MySQL AB have indicated that these deficiencies may soon be addressed...

File Structure

Most standard SQL statements will work when using a MySQL database. Some examples follow. NOTE: I tend to uppercase keywords when I am writing SQL statements, but the MySQL engine is case-insensitive.

Create a table:

CREATE TABLE table-name
(
field-name1 DATATYPE(size),
field-name2 DATATYPE(size), ...
field-nameN DATATYPE(size)
);

Add a record:

INSERT INTO table-name
( field-name1, field-name2, ..., field-nameN )
VALUES ( 'value1', 'value2', ..., 'valueN' );

Remove a record:

DELETE FROM table-name
WHERE field-name = 'value';

Update a record:

UPDATE table-name
SET field-name = 'value1' WHERE field-name = 'value2';

Select a record:

SELECT field-name1, field-name2, ..., field-nameN FROM table-name
WHERE field-name = 'value';

Want more?

I have only covered the basics here. If you want a more thorough run-down on MySQL, Tizag.com has a very good series of tutorials.