An Introduction To MySQL Database

About MySQL

MySQL is an open source database management software that helps users store, organize, and retrieve data. It is a very powerful program with a lot of flexibility.

This tutorial will explains how to install MySQL, create a sample database, create a table, insert records into the table, and select records from the table.

Installation

You can install mysql using the following command:

On Ubuntu:

sudo apt-get install mysql-server

On Centos:

sudo yum install mysql-server

Follows the steps below to stop and start MySQL

service mysql start 
Starting MySQL.                                            [  OK  ]
service mysql status
MySQL running (12588) 
service mysql stop
Shutting down MySQL.                                       [  OK  ]

Verifying Installation

You can check the MySQL installed version by performing mysql -V as shown below:

[local-host]# mysql -V
mysql  Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1

Access the MySQL shell

Once you have MySQL installed on your droplet, you can access the MySQL shell by typing the following command into terminal:

mysql -u root -p

After entering the root MySQL password into the prompt, you will be able to start building your MySQL database.

mysql -u root -p
Enter password: password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Creating Databases

After connecting as MySQL root user, you can use this command to create database.

In this example, we will create truetech4 database.

mysql> create database truetech4;

You can check what databases are available by typing this command:

SHOW DATABASES;

Your screen should look something like this:

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

Creating Tables

Before you create a mysql table, you need to choose the database that you want to use:

USE truetech4;
Database changed

The following example creates a article table.

create table article (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
number varchar(10),
page int(10)
writing_date DATE);

The command show tables to view all the tables available in the database.

mysql> SHOW TABLES;
+-------------------+
| Tables_in_truetech4 |

+------------------+
| article         |
+------------------+
1 row in set (0.01 sec)

To view the table description, do the following command

 mysql>DESCRIBE article;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | YES  |     | NULL    |                |
| number      |  int(11)    | YES  |     | NULL    |                |
| page        | char(1)     | YES  |     | NULL    |                |
| writing_date| date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

Add Information to Tables

Use the following sample insert commands to insert some records to the article table.

insert into article values(1,'article1','4','a','2012-04-13');

insert into article values(2,'article2','5','b','2012-04-14');

insert into article values(3,'article3','6','C','2012-04-15');

insert into article values(4,'article4','7','d','2012-04-16');

You can take a look at your table using this command

mysql> SELECT * FROM article
+----+------- +----------------+-----------+-------------+
| id | name   | number         | page      |wrinting_date|
+----+------- +----------------+-----------+-------------+
|  1 |article1| 1              | a         | 2012-04-13  |
|  2 |article2| 2              | b         | 2012-04-14  |
|  3 |article3| 3              | c         | 2012-04-15  |
|  4 |article4| 4              | d         | 2012-04-16  |
+----+--------+----------------+-----------+-------------+
4 rows in set (0.00 sec)

Update Information in the Table

You can update a stored information in the table with this command:

UPDATE `article` 
SET 
`number` = '6' 
WHERE `article`.`name` ='article4';

Delete a Row, a Column and a Table

You can  delete rows from the table with the following command:

DELETE from  where [column name]=[field text];

mysql> DELETE from article  where name='article2';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM article
+----+------- +----------------+-----------+-------------+
| id | name   | number         | page      |wrinting_date|
+----+------- +----------------+-----------+-------------+
| 1 |article1 | 1              | a          | 2012-04-13 |
| 3 |article3 | 3              | c          | 2012-04-15 |
| 4 |article4 | 4              | d          | 2012-04-16 |
+----+--------+----------------+-----------+-------------+
3 rows in set (0.00 sec)

You can also delete a column using this command

ALTER TABLE  [column name];

And type this command if you want to delete all table

ALTER TABLE ;

That’s all for this article.