MySQL

MySQL is an open source, relational database management system—what is known as an RDBMS for short. MySQL relies on structured query language commonly referred as SQL, which is a programming language used for inserting, deleting and updating data in databases. A database is a collection of data that is organized for easy data manipulation. Common SQL commands can be used with MySQL, such as SELECT, INSERT, UPDATE and DROP. It stores data in tables, which have rows and columns. These tables are created using SQL and data from them can be retrieved also through the use of SQL.

A little history

MySQL was first released in 1995. It was named after My, the daughter of Michael Widenius who was one of the co-founder of MySQL. It was originally owned by Sun Microsystems, but it is now owned by the Oracle Corporation.

Here are some key features of MySQL:

• MySQL is a relational database system
• MySQL is SQL compatible
• MySQL has client/server architecture
• MySQL comes in several client and utility programs such as CLI and MySQL workbench, which has a convenient interface for managing a MySQL server
• MySQL supports storing and processing two-dimensional geographical data
• MySQL supports Transactions
• MySQL allows database replication
• MySQL supports SubSELECTs
• MySQL uses triggers and stored procedures
• MySQL works on a variety of platforms

What is it good for?

MySQL is helpful in managing large data and information. It helps store and keep large data in a database. MySQL is commonly used in database driven websites, which are websites where some contents are generated or retrieve from a database. For example user registration details are stored in a database and are retrieved whenever the user views his profile. MySQL is a good choice to use in building an application that potentially needs to save thousands of entries of data.  

Here are some advantages of using MySQL:

• Easy to manage and easy to use
• Open source, free to download and free to use
• Has many users. It has a large community of developers who provide support
• Fast and secured
• Runs in many different operating systems
• Supports several development interfaces

Examples and Code Snippets

Let us assume you have already set up and installed MySQL on your computer.

This is the command to connect to a MySQL server using the command line interface:

[root@host]# mysql -u root -p
Enter password:******

This command will ask you to input your password. This will then bring you to the mysql>  shell where you can run SQL commands.

You can check available databases using this command:

mysql> SHOW DATABASES;

Your command prompt or terminal screen would look something like this:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

Here is the command for creating a database via the MySQL shell: mysql>

mysql> CREATE DATABASE database my_database;

The created database “my_database” should be visible in the list of available databases. It would now look like this:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_database        |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

You could delete a database using the DROP command:

DROP DATABASE database my_database;

You can access and select a database using this command:

USE my_database;

Here is an example for creating a table:

CREATE TABLE user (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
   name VARCHAR(20),
   email VARCHAR(30),
   confirmed CHAR(1), 
   created_at DATE);

It has created a table named “user” under the “my_database” database. This command accomplishes 4 things:

• It set up 5 columns in the user table. These columns are id, name, email, confirmed and created_at
• In the command the “id” is (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) this automatically put numbers in each row.
• The name, email, confirmed and created_at is assign to data types VARCHAR CHAR and DATE.

To show the list of tables in a database use this command:

SHOW tables;

Your terminal or command prompt screen should look like this:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_my_database |
+-----------------------+
| user                  |
+-----------------------+
1 row in set (0.01 sec)

To view a table's information and how it is organized, use this command:

mysql> DESCRIBE user;

It would return the following:

mysql>DESCRIBE user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | YES  |     | NULL    |                |
| email       | varchar(30) | YES  |     | NULL    |                |
| confirmed   | char(1)     | YES  |     | NULL    |                |
| created_at  | date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

To insert data:

mysql> INSERT INTO `user` (`id`,`name`,`email`,`confirmed`,`created_at`) VALUES (NULL, "John", "john@email.com","Y", '2016-05-18');

To update data:

mysql> UPDATE `user` 
SET 
`confirmed` = 'Y' 
WHERE `user`.`name` ='Sandy';

To add or delete columns use the ALTER command:

mysql> ALTER TABLE user ADD address VARCHAR(40) AFTER email;

mysql> ALTER TABLE user DROP address;

To delete a row:

mysql> DELETE from user where name = ‘Sandy’;

Conclusion 

MySQL is known as fast, free, reliable and robust database with a good set of features. It has an active development team thus more capabilities are added. It has also a large number of users so whenever problems arises, you can count on a lot of help coming from them. MySQL is great to use for lightweight applications but still MySQL is not perfect and it has also its downside. So at the end of the day, it is according to your need to choose what database to use but don’t worry, there are a variety of databases management system to choose from.