Monday, 12 February 2018

mysql

Install mysql workbench

Install mysql server

Launch mysql command line client

connect Databasefrom mysql work bench

Ctrl + enter for executing the queries
---------------------------------------


use abc;

create table student (rollno int, sname varchar(20));

desc student

-----------------
ddl - data def lan                 - creating table - changing structure of table
dml  - data maninpulation lang  - insert , update values - change the value of table

dql/dql - data query lan  - for query
drl -  data retrieve lang

tcl - for granting permission and revoke
-------------

Simple query

import java.sql.*;

public class DemoClass {

public static void main(String args[]) throws Exception{

String url= "jdbc:mysql://localhost:3306/abc";
String uname="root";
String pass="1234";
String query= "select * from student where rollno=3";

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(url, uname, pass);
Statement st = con.createStatement();
ResultSet rs=st.executeQuery(query);
rs.next();
String name=rs.getString("sname");  // cnu
System.out.println(name);
st.close();
con.close();

                 // table student
// rollno  sname
// 1       sriniva
// 2       chrav
// 3       cnu



}

}


------------------------------------------------------------------
Fetching whole table


import java.sql.*;

public class DemoClass {

public static void main(String args[]) throws Exception{

String url= "jdbc:mysql://localhost:3306/abc";
String uname="root";
String pass="1234";
String query= "select * from student";

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(url, uname, pass);
Statement st = con.createStatement();
ResultSet rs=st.executeQuery(query);


String userData;

while(rs.next()){
userData= rs.getInt(1)+" : " + rs.getString(2);
System.out.println(userData);
}
st.close();
con.close();


/*
rs.next();

String userData= rs.getInt(1)+" : " + rs.getString(2);
System.out.println(userData);
st.close();
con.close();

*/



/*
String name=rs.getString("sname");  // cnu
System.out.println(name);
st.close();
con.close();

// rollno  sname
// 1       sriniva
// 2       chrav
// 3       cnu

*/

}

}


------------------------------------------------------------------------

Inserting into db

import java.sql.*;

public class DemoClass {

public static void main(String args[]) throws Exception{

String url= "jdbc:mysql://localhost:3306/abc";
String uname="root";
String pass="1234";
String query= "insert into student values(4,'hari')";

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(url, uname, pass);
Statement st = con.createStatement();
//ResultSet rs=st.executeQuery(query);
    int count=st.executeUpdate(query);
    System.out.println("rows effected "+count);


st.close();
con.close();

}

}


--------------------------------------------------------



No comments:

Post a Comment