Hello all,

welcome to the part 2 of Prerequisites for Hadoop.
In this post, we are going to look over the SQL part required in order to start Hadoop.
We are going to use MySQL Database Server to demonstrate basic SQL queries.

We are going to go through following parts in SQL.

  • MySQL Installation on Ubuntu 14.04
  • Accessing MySQL Database
  • See list of Databases
  • Use a Database to work with
  • See list of Tables in a Database
  • Create a Table
  • Insert sample records in a table
  • View inserted records from a table/li>
  • View specific columns of inserted records from a table/li>
  • Where clause
  • Delete all the records from a table
  • View records from table based on specific condition
  • Delete records from table based on specific condition
  • Drop table from a database
  • Truncate table from a database
  • Drop Database
  • Exit from MySQL Database Server

MySQL Database Server 5.6 Installation on Ubuntu 14.04

We have to run following command in order to install MySQL Database 5.6 on Ubuntu 14.04
***************************************************************************************
$sudo apt-get install mysql-server-5.6
***************************************************************************************

Once you reach the end part of installation, new screen will appear asking for password.
Type password of your choice and please remember it as you will need it to login to MySQL in later stages.

Consider “root” as our password for demo purpose.

Accessing MySQL Database

***************************************************************************************
$mysql -uroot -proot
***************************************************************************************

Above command will connect to MySQL Database server installed on the same system.
-u indicates the username parameter (“root” is MySQL Username).
-p indicates the password parameter (“root” is password for “root” MySQL Username).

If your username and password is correct, then you will get MySQL prompt as follows.
mysql>

All MySQL commands are run from this MySQL prompt.

See list of Databases

***************************************************************************************
mysql>show databases;
***************************************************************************************

Use a Database to work with

***************************************************************************************
mysql>use DB_NAME; //SYNTAX
mysql>use test; //EXAMPLE
***************************************************************************************

See list of Tables in a Database

***************************************************************************************
mysql>show tables;
***************************************************************************************

Create a Table

***************************************************************************************
mysql>create table TABLE_NAME(
>COLUMN_NAME_1 DATATYPE,
>COLUMN_NAME_2 DATATYPE,…,
>COLUMN_NAME_N DATATYPE); //SYNTAX
mysql>create table student(
>id int,
>name varchar(11),
>age int); //EXAMPLE
***************************************************************************************

Insert sample records in a table

***************************************************************************************
mysql>insert into table TABLE_NAME values(VALUE_1, VALUE_2,…, VALUE_N); //SYNTAX
mysql>insert into table student values(1, ‘milind’, 25); //EXAMPLE
mysql>insert into table student values(2, ‘rahul’, 25); //EXAMPLE
***************************************************************************************

View all inserted records from a table/h3>

***************************************************************************************
mysql>select * from TABLE_NAME; //SYNTAX
mysql>select * from student; //EXAMPLE
***************************************************************************************

View specific columns of inserted records from a table/h3>

***************************************************************************************
mysql>select COLUMN_NAME_1,COLUMN_NAME_2,…,COLUMN_NAME_N from TABLE_NAME; //SYNTAX
mysql>select id,name from student; //EXAMPLE
***************************************************************************************

Where clause

***************************************************************************************
mysql>select * from TABLE_NAME where CONDITION; //SYNTAX
mysql>select * from student where id=1; //EXAMPLE
***************************************************************************************

Delete all the records from a table

***************************************************************************************
mysql>delete * from TABLE_NAME; //SYNTAX
mysql>delete * from student; //EXAMPLE
***************************************************************************************

View records from table based on specific condition

***************************************************************************************
mysql>select * from TABLE_NAME where CONDITION; //SYNTAX
mysql>select * from student where id=1; //EXAMPLE
***************************************************************************************

Delete records from table based on specific condition

***************************************************************************************
mysql>delete * from TABLE_NAME where CONDITION; //SYNTAX
mysql>delete * from student where id=1; //EXAMPLE
***************************************************************************************

Drop table from a database

***************************************************************************************
mysql>drop table TABLE_NAME; //SYNTAX
mysql>drop table student; //EXAMPLE
***************************************************************************************

Truncate table from a database

***************************************************************************************
mysql>truncate table TABLE_NAME; //SYNTAX
mysql>truncate table student; //EXAMPLE
***************************************************************************************

Drop Database

***************************************************************************************
mysql>drop database DATABASE_NAME; //SYNTAX
mysql>drop database student; //EXAMPLE
***************************************************************************************

Exit from MySQL Database Server

***************************************************************************************
mysql>exit;
***************************************************************************************

I will post the screenshots very soon.
Hope this text helps.

Cheers.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s