An Introduction To MySQL Database

 

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 unixmen database.

mysql> create database;

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               |

| unixmen            | 

|                    |

+--------------------+

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 unixmen;

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_unixmen |

+------------------+

| 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 ;