Saturday, April 25, 2015

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 ;


No comments:

Post a Comment