MySQL join

MySQL  join :

“JOIN” is an SQL keyword which used to query data from two or more related tables.

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database provides a number of tables containing related data. You can see a very simple example :

Here students and course are two table :

id name course
1 pradeep 1
2 amit 1
3 rahul 2
4 Dev 5
5 Raman (NULL)


Course table:

id name
1 Java
2 C#
3 C++
4 C
5 SQL

In essence, MySQL will automatically:Since we’re using InnoDB tables and know that student.course and course.id are related, we can specify a foreign key relationship:

  • re-number the associated entries in the user.course column if the course.id changes
  • reject any attempt to delete a course where users are enrolled.

Note: This is terrible database design!

This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.

JOINs allow us to query this data in a number of ways.

 

Example :


SELECT user.name, course.name

FROM `user`

INNER JOIN `course` on user.course = course.id;

Output :

 

user.name course.name
Pradeep Java
amit Java
rahul C#
Dec SQL

 

Left join :

What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course).


SELECT user.name, course.name

FROM `user`

LEFT JOIN `course` on user.course = course.id;

Right Join :


SELECT user.name, course.name

FROM `user`

RIGHT JOIN `course` on user.course = course.id;

 

Advertisements

MySQL Filtering table data

MySQL Filtering table data :

In database, when you need to view data from table it’s rare required to view all data each time. So sql give us some
method of filtering data that not required.

There are three ways to filter any table :

  • selected columns and all rows
  • selected rows and all columns
  • selected columns and selected rows

selected columns and all rows :

In this way you will write query as selected column name as your need.

Syntax :


select <column_name1>, <column_name2>....<column_name_n> from <table_name>;

Example :


select fname, lname from employee;

selected rows and all columns :

Sql provide where clause to filter row from table.

Syntax :

 


select * from <table_name> where <condition>;

In above query, sql engine compares each records in the table with the condition given in the where clause. Data display
only those records that satisfy the given condition.

Example :

 


select * from employee where salary='20000';

Note : With above condition you can use all standard operators like logical, arithmetic etc.

selected columns and selected rows :

SQL provide facilities to select column as well as row.

Syntax :

 


select <column_name1>, <column_name2>....<column_name_n> from <table_name> where <condition>;

Example :

 


select fname, lname from employee where salary='20000';


					

MySQL insert operation

MySQL insert operation :

When you need to write any data in a table you have to wite insert query in mysql.

Syntax :


insert into <table_name> (<column_name1>, <column_name2>....) values(<value1>, <value2>,......);

Example :


insert into employee(id, name, salary) values('101', 'Pradeep', 45000);

If you want to not specify column name then it’s sure that each column value must write.

Example :


insert into employee values('101', 'Pradeep', 45000); 

Here it is clear that you will not write all column value then arise an error.
Example :


insert into employee values('101');

The above query does not compile because MySQL engine not able to specify which column value query taken. But if you will write query with column name then it’s ok.

Example :


insert into employee(id) values('101');

In above query all remaining column value will insert null.


					

Call stored procedure from java application

Stored procedure can call from java application with following way:

Step 1: create Connection object.

Step 2: call prepareCall() method of CallableStatement.

Step 3: set parameter.

Step  4: call execute()

 

Example :

 


package com.pkjavacode.com;

import java.sql.*;

/**
 *
 * @author pradeep
 */
public class CallableExample {

 public static void main(String args[]) throws Exception {
 Connection conn = null;
 try {
 Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
 CallableStatement cs = conn.prepareCall("{call getEmpDetails(?,?,?)}");
 cs.setInt(1, 102);
 cs.setString(2, "Ajit");
 cs.setInt(3, 123765);
 cs.execute();
 System.out.println("Data inserted successfully in stored procedure!!");
 } catch (SQLException e) {
 e.printStackTrace();
 } finally {
 if (conn != null) {
 conn.close();
 }
 }
 }
}

 

What is stored procedure and how to create?

Sotred Procedure :

Stored procedure is a subroutine or a segment of declarative sql statement stored inside the database server. each stored procedure contains a body that consists of an sql statement. This statement can be a compound statements made up of several statements separated by semicolon (;) characters.
Stored procedure has a body made up of a BEGIN … END block.


delimiter $$
create procedure myprocedure()
begin
select * from employee;
end $$
delimiter;

 

Advantage of stored procedure :

  • It increase application performance. Once created compiled and stored in the database server.
  • It reduce traffic between application and database.
  • It is secure because it stored on database server.
  • It is reusable and transparent to any application which want to use it.