MySQL Basic Database Administration Commands – Part I

Databaseis a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of100pages, if you have to search for all the employees whose salary were less than10k, just think how much difficult it would have been, then.

In today’s world you just can’t escapeDatabase. Right now millions of database is working around the world to store and fetch data of every kind be it strategic data, employee record or web technologies.

MySQL Administration Guide – Part I

Database is oftenly termed as back-end process, as because it is neither visible to end user norEnd Userinteracts directly with the database. They works on front-end process viz.,PHP,VB,ASP.NET, etc. and ask the front end to deal with database in back-end.

There are several database server and client available likeOracle,MySQL,MySQLi,MariaDB,MongoDBetc. The syntax of all of these are more or less the same. Mastering one means gaining control on most of them and learning the queries of a database is very easy and fun.

Lets start with simple queries on database. We will be usingMySQLwhich comes bundled with most of theLinuxdistributions by default, you could install it manually from repository, if it is not installed by default in your case.

Well a database query is a simple piece of code that is sent to database to get custom and refined result, as required.

Install MySQL Database

Use “yum” or “apt” package manager to installMySQLDatabase.

# yum install mysql mysql-client mysql-server  (on Yum based Systems)

# apt-get install mysql mysql-client mysql-server (on Apt based Systems)

Start MySQL

StartMySQLdatabase service as:

# service mysqld start
or
# service mysql start

Well installing aMySQLdatabase will take you to the configuration where you are asked to setupadminpassword, etc. Once finished installing and starting the server go to yourMySQLprompt.

# mysql -u root -p

Replacerootwith your configuredusernameand enterpasswordwhen prompted, if the login credential is correct, you will be at yourMySQLprompt at the blink of your eyes.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 195 

Server version: 5.5.31-0+wheezy1 (Debian) 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Now carrying out queries at this prompt is very educative and fun.

Create a database tecmint

mysql> create database tecmint ;
Query OK, 1 row affected (0.02 sec) 

mysql>

Note: It reports that the query was correct, means database is created. You can verify your newly created database as.

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
9 rows in set (0.00 sec) 
mysql>

Note: Notice your database in the above output.

Select Database

Now you need to select the database to work upon it.

mysql> use tecmint;
Database changed
mysql>

Create Tables in MySQL

Here we will be creating a table say “minttec” with three fields as:

mysql> CREATE TABLE minttec (
    -> id Int(3), 
    -> first_name Varchar (15), 
    -> email Varchar(20) 
    -> ); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

Note: The above query saysOKwhich means table was created without any error. To verify the table run the below query.

mysql> show tables; 
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| minttec           | 
+-------------------+ 

1 row in set (0.00 sec) 

mysql>

Things are going fine till now. Yup! You can view the columns you created in the table “minttec” as:

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

mysql>

It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.

1. Intis Integer
2. Varcharis char having variable length as defined. The value after Type is the length of field up-to which it can store data.

OK now we need to add a column say ‘last_name‘ after column ‘first_name‘.

mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, verify it in your table.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 

4 rows in set (0.00 sec) 

mysql>

Add Column in MySQL

Now we will add a column to the right say a column ‘country‘ to the right ofemail.

mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (0.16 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

mysql>

Verify the above column insertion query.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
| country    | varchar(15) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 

mysql>

Insert Values in Field

What about inserting values to the field?

mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , '[e-mail]' , 'India' );
Query OK, 1 row affected (0.02 sec) 

mysql>

How about inserting more than1value at a time in the above table.

mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , '[e-mail]' , 'India' ), ('3' , 'user' , 'singh' , '[e-mail]' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , '[e-mail]' , 'India' );
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

Verify the above insertion.

mysql> select * from minttec; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi	    | Saive     | [e-mail] | India   | 
|    2 | Narad      | Shrestha  | [e-mail]     | India   | 
|    3 | user       | singh     | [e-mail]      | Aus     | 
|    4 | tecmint    | [dot]com  | [e-mail] | India   | 
+------+------------+-----------+-------------------+---------+ 

4 rows in set (0.00 sec)

mysql>

Delete Values in Field

Let’s say the third entry in the above output is invalid and we need to delete the third entry.

mysql> DELETE FROM minttec WHERE id = 3;

Query OK, 1 row affected (0.02 sec)

Verify the above operation.

mysql> select * from minttec;

+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | [e-mail] | India   | 
|    2 | Narad      | Shrestha  | [e-mail]     | India   | 
|    4 | tecmint    | [dot]com  | [e-mail] | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)

Update Values in Field

The id (=4) needs to be edited.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Verify the above query.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Note: The above query, as performed is not a good idea. It will change the id to ‘4‘ where ever the first name is ‘tecmint’. It is always a good idea to use more than one column with where clause to get minimal error, as:

mysql> UPDATE minttec SET id = 6 WHERE first_name = 'tecmint'AND last_name = '[dot]com'; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Delete Column in MySQL

Let we need to drop (delete) a column we think, is of no importance say ‘country‘ here.

mysql> ALTER TABLE minttec drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

Verify the table.

mysql> select * from minttec; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | [e-mail] | 
|    2 | Narad      | Shrestha  | [e-mail]     | 
|    6 | tecmint    | [dot]com  | [e-mail] | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec) 

mysql>

Rename Table in MySQL

Don’t you think our table name “minttec” is not very much relevant. How about changing it totecmint_table.

mysql> RENAME TABLE minttec TO tecmint_table; 
Query OK, 0 rows affected (0.03 sec)

mysql>

List all Tables

See all the tables under the current database.

mysql> show tables; 

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+
1 row in set (0.00 sec) 

mysql>

The table has been renamed. Now take a backup of the aboveMySQLdatabase, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.

# mysqldump -u root -p tecmint > tecmint.sql

check the dumped file on your desktop which would have contents something like
-- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (i686) --
-- Server version 5.5.31-0+wheezy1 -- 
Dump completed on 2013-09-02 12:55:37

It is always a good idea to maintainBackup of MySQL databases. Restoring the backed upMySQLData is again a simple line of code you need to run at your terminal prompt and not at your mysql prompt.

But, wait first we will delete the database to verify if our restore is perfect.

Delete a Database

mysql> drop database tecmint; 
Query OK, 1 row affected (0.02 sec)

Check for database ‘tecmint’ on your database server.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+

7 rows in set (0.00 sec) 
mysql>

Great! The database is lost, but we need not to worry, we are having the backup.

Restore a Database

To restore lost database, run the following command.

# mysql -u root -p tecmint < tecmint.sql
Enter password:
ERROR 1049 (42000): Unknown database 'tecmint'

OOPS! An error, hey we have not create the databasetecmint. So go to your mysql prompt and create a database ‘tecmint‘.

mysql> create database tecmint; 
Query OK, 1 row affected (0.00 sec) 

mysql>

Now time to run restore command at your shell prompt (strictly).

# mysql -u root -p tecmint < tecmint.sql 
Enter password:

Verify your database.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
8 rows in set (0.00 sec)

Verify the contents of database.

mysql> show tables from tecmint;

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+ 
1 row in set (0.00 sec)

mysql>

Verify the contents of your restored table.

mysql> select * from tecmint_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | [e-mail] | 
|    2 | Narad      | Shrestha  | [e-mail]     | 
|    6 | tecmint    | [dot]com  | [e-mail] | 
+------+------------+-----------+-------------------+

3 rows in set (0.00 sec)

This is not the end definitely, we will cover the concept ofprimary key,foreign key,multiple tablesandrunning queriesusing simplePHPscript in the next part of the article.

Don’t forget totell us, how you felt while going through the article. Your comments are highly appreciated. StayHealthyandTuned, remain connected toTecmint.