MySQL beginner tutorial part-2

Hello,

In the previous tutorial we just know about some basic concept about MySQL and some basic SQL commands.

In this tutorial I will tell you about some new  SQL (Structured Query Language) statements.

USE statement

 

This command is used for changing or selecting  the database from a list of databases.

 

when we use show databases; statement, it list  all available databases for a user.

so for selecting a particular database for working we use USE statement.

Syntax for use-

mysql> use db;         // replace db to your database name

As we know  RDBMS  uses table to represent data. so in RDBMS database contain a collection of tables.

Table is like a matrix contains rows and columns.

rows also knows a tuple or record

Here is an example of how a tables look like in MySQL.

 

student table

you can see an example of table named student where roll_no, name, sex, age are column name.

(101, Ram, M, 24) is a row of student table also known as tuples or record

CREATE statement in MySQL

Create a database 

In this section we learn about how to create a database in MySQL.

To create a database in MySQL we uses CREATE statement-

Suppose we are going to crate a database name college which store the student details.

mysql > CREATE DATABASE college;

It will create a college database in your MySQL server. you can check it using SHOW DATABASES; statement.

Create a table in database

In this section we learn about how to create a table in MySQL.

To create a table in MySQL we uses CREATE statement-

Step 1- First select the database (USE statement) in which you want to create a database.

In our case  we use college  as database that we just created.

USE college;

Step 2- Now we create a table student-

query to create a tabale-

mysql> CREATE TABLE student(roll_no varchar(20), name varchar(20), sex char(1), age int(3));

this command will create a table called student, which roll_no, name, sex, age as column name

DESC statement

 

DESC statement is used  to show the structure of a table.

Query to see the structure of table-

mysql> DESC student;;

student table structure

MySQL beginner tutorial

Hello,

I am writing this basic MySQL tutorial to provides some basic hands on to the student  and developers. My aim to writing this tutorial to provide some basic and quick learning of Data Base Management System(DBMS).

Firstly I will tell you about MySQL.

What is MySQL?

MySQL is an Relational Data Base Management System (DBMS) which is used to store and manage the application data.

MySQL works as Data Base server, its mean MySQL is a server application.

Now I am going to Explains some terminology that will clear your concept.

What is Data ?

Data is just fact that can be gathered and stored and have implicit meaning, like Name of students is a data.

What is DataBase ?

Data Base is something in which we store data, means provide some base to store data, like File can be some form of data base to store the students Name.

What is DBMS ?

DBMS is a collection of program that helps to create database and manage it efficiently.

First thing which you need to play with MySQL server, is installing the MySQL in your system.

The link given below provides you steps to install MySQL server in Linux/ubuntu.

Install MySQL in ubuntu

After installing MySQL successfully, the first thing which you need is MySQL client.

Since I am already tell you that MySQL is a server, so to use it you required some client program that can connect you to MySQL server.

There are many MySQL client programs, some are GUI based and some are command based client.

I advised you if you are a newbie use command line MySQL client because if you use GUI based client initially some concept may not be clear.

When you installed MySQL server is come up with MySQL client program also. So there is no need to install MySQL client.

I hope, you  have successfully installed MySQL sever and MySQL client program to your machine.

If you have any problem during installation you can type your problem in comment section.

So Now you have successfully installed MySQL programs, server and client both.

Now we see how to connect with MySQl server using MySQL client-

Step 1. Just open your Linux terminal

Step 2. Write the following command to connect the MySQL server from your client

mysql -u username -p  [enter key] // replace username string to your user name
Enter password:  // provide your password

If every thing goes successfully you can connect to MySQL server.

When we install MySQL server its come with some its own data base which it uses internally, so just play with MySQL.

Type the following command

SHOW databases;    // this command list all the data bases in MySQL

you look something like this ---

mysql> show databases; +----------------------------------+
| Database                    |
+----------------------------------+
| information_schema   |
| mysql                          |
| performance_schema |
| phpmyadmin               |
| sys                               |
| test                              |
+------------------------------------+
8 rows in set (0.00 sec)

Congrats! now successfully run your first command.

In next tutorial I will provide you some basic command.

Install MySQL on Ubuntu

In this tutorial I will show you steps to install MySQL server.

Step 1 : Update your system

Type the command given below to update your system

sudo apt-get update
sudo apt-get upgrade

Step 2: Install MySQL

Type the following command to install MySQL 

sudo apt-get install mysql-server

During installation process you get a window given below that asks you to provide password for your root user. read the instruction carefully. 

mysql installation window

After successful installation type the following command to check weather MySQL  server is running or not.

nmap localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2016-08-26 17:48 IST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000090s latency).
Not shown: 995 closed ports
PORT     STATE SERVICE
22/tcp   open  ssh
80/tcp   open  http
139/tcp  open  netbios-ssn
445/tcp  open  microsoft-ds
3306/tcp open  mysql  

by default MySQl uses 3306 port to run the server

if you get the highlighted portion it show your MySQL server is successfully installed and ready to use.

Spatial Data Type in MySQL DataBase

In this tutorial I will be able to provide you some information about Spatial Data and Spatial Data Type in MySQL.

What is Spatial Data ?

Spatial data also known as geo-spatial data, represents the location, size and shape of an object on  Earth such as a minar, lake, mountain or temple. this data can used to locate any device, mountain and place globally.

Spatial data consists of point, line, polygon and many others geometric object.

Spatial Data Types In MySQL

 

MySQL (here we use the version 5.7) has data types that correspond to OpenGIS classes. Some object that store single geometry values:

•     GEOMETRY

•     POINT

•     LINESTRING

•     POLYGON

GEOMETRY  can store geometry values of any type.

POINT, LINESTRING and POLYGON restrict their values to a particular geometry type.

data types that can hold multiple values:

•     MULTIPOINT

•     MULTILINESTRING

•     MULTIPOLYGON

•     GEOMETRYCOLLECTION

MySQL supported spatial Data Format

Two  types of format are supported by mysql queries-

   1.  WKT (well known text) format
   2.  WKB (well known binary) format


WKT :

The Well-Known Text (WKT) format of geometry values is designed for exchanging geometry data in ASCII form

Example of representing a point in WKT format
  
   POINT(10,20)


WKB :

The Well-Known Binary (WKB) format of geometric values is used for exchanging geometry data as binary streams described by BLOB (Binary Large Object) values containing geometric WKB information.

WKB uses 1-byte unsigned integers, 4-byte unsigned integers, and 8-byte double-precision numbers (IEEE 754 format). A byte is eight bits.

For example, a WKB value that corresponds to POINT(1 1) consists of this sequence of 21 bytes, each described by two hex digits:

0101000000000000000000F03F000000000000F03F

The sequence consists of these components:

Byte order:   01
WKB type:     01000000
X coordinate: 000000000000F03F
Y coordinate: 000000000000F03F


Storing geolocation of a point in MySQL using Spatial Data Type



Reference :
http://dev.mysql.com/doc/refman/5.7/en/

Storing geolocation of a point in MySQL using Spatial Data Type

AS we  know todays developing location-based application is most common. we know, we can get the location of a point in earth using Latitude and Longitude. we can uniquely define any point in a Earth using latitude and longitude, together they used to defined geographical co-ordinate of Earth's and represent the angular distance of any location from the center of the Earth.

The best way of storing latitude and longitude or position of a point in MySQL is  POINT class of MySQL Spatial Data Types.

When using POINT class, the order of the arguments for storing co-ordinates must be POINT(longitude, latitude).

For example suppose geo-location of a point is -

 long = 77.18563599999993       // long represents longitude
 lat    =    28.513096                   // lat represent latitude

Step to store and retrieve these points in MySQL

Step 1 : first we create a table for storing location of a point using POINT class of  Spatial Data Type.

--
-- Table structure for table `site`
--

        CREATE TABLE `site` (
             `id` tinyint(4) NOT NULL,
              `position` point NOT NULL)
              ENGINE=InnoDB

Step 2 : SQL syntax for storing geolocation using Spatial

   INSERT INTO `site` (`id`, `location`) VALUES ('1', GeomFromText('POINT(77.18563599999993 28.513096)'));

This SQL commands store the location of a  point as binary GEOMETRY object. GeomFromText(), this function return the geometry object.

Step 3 : Retrieve points from table

         SELECT *, AsText(position) FROM site