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.
alternative link download