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 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 [table name] 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 [table name] [column name];
And type this command if you want to delete all table
ALTER TABLE [table name];
That’s all for this article.
