[Tutorial] MySQL database Getting Started Practical

MySQL

MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications.

RDBMS Terminology:

Before we proceed to explain MySQL database system, let’s revise few definitions related to database.

Database: A database is a collection of tables, with related data.
Table: A table is a matrix with data. A table in a database looks like a simple spreadsheet.
Column: One column contains data of one and the same kind, for example the column postcode.
Row: A row is a group of related data, for example the data of one subscription.
Primary Key: A primary key is unique. A key value can not occur twice in one table. With a key, you can find at most one row.
Foreign Key: A foreign key is the linking pin between two tables.

Installing Process

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-level-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm 
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

Query in MySQL:

Basic Queries are

Create database

Creating separate space in MySQL.

Syntax:
create database <DB_Name>;

To use the created Database

Syntax:
use <DB_Name>;
Create table

Creating table in Database

Syntax:
create table <TableName>(Column1 Datatype,Column2 Datatype,...);
Insert table

Insert data in created table

Syntax:
insert into <TableName> 
values('string_data',integer_data,....);

Select table

To retrieve data from table

Syntax for access all Rows:

select * from <TableName>; (or) select 
<column_name> from <TableName>;
Syntax for access particular Rows:
select * from <TableName> where 
<column_name>='<user_data'>;

View table

Create view table form existing table.

Syntax:
create view <ViewTableName> as 
select <Column1>, <Column1> from <OldTableName>;

Delete table

To delete data or table

Syntax for delete all data:
 delete table <TableName>;
Syntax for delete particular data:
delete table <TableName> where <ColumnName>="
<userdata>"

MySQL aggregate functions:

Avg

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.

Syntax:
select AVG(<ColumnName) from <TableName>;

Count

The COUNT function returns the number of the rows in a table.

Syntax:
select COUNT(*) from <TableName> (or) select 
COUNT(<columnName>) from <TableName>;

Sum

The SUM function returns the sum of a set of values.

Syntax:
 select SUM(<ColumnName>) from <TableName>;

Min

The MIN function returns the minimum value in a set of values.

Syntax:
 select MIN(<ColumnName>) from <TableName>;

Max

The MAX function returns the maximum value in a set of values.

Syntax:
   select MAX(<ColumnName>) from <TableName>;

MySQL with node.js

Example:
       nodejs with MySQL
       var express    = require("express");   
       var mysql  = require('mysql');  
       var connection = mysql.createConnection({   
       host     : 'localhost',       
       user     : 'root',                
       password : '',                
       database : 'address_book'         
       }); 
        var app = express();
        connection.connect(function(err){   
        if(!err) {
     console.log("Database is connected ... nn");
     } else {
     console.log("Error connecting database ... nn");   
 }
 });

       app.get("/",function(req,res){
     var query="select * from register where 
      urname='"+req.body.urname+"' and 
     pw='"+req.body.pw+"'";
       connection.query(query, function(err, 
   rows, fields) {    
 connection.end();
   if (!err)
console.log('The solution is: ', rows);
 else
console.log('Error while performing Query.');
  });
 });
  app.get("/",function(req,res){
  connection.query('insert into user values('+1+','+sathiya+','+sathiya@gmail.com+','+sathiya2901+')', function(err, rows, fields) { 
 connection.end();
 if (!err)
console.log('The solution is: ', rows);
 else
console.log('Error while performing Query.');
 });
});
 app.listen(3000);  

For any query contact Clofus Innovations.

Clofus

Software Solutions.