Most Popular commands

Normalization :



Index:

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

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

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

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

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

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

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

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

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

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

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

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



Some commands of MYSQL

hi
today  i would like  to  write  all SQL  commands  that  mostly  i am using in my  analysis.

1-  create  database :  
      mysql> create  database test_db;

2- enter  on  your  database:
      mysql> use  test_db;

3- deleting  all database:
     mysql>  drop  database  test_db ;

4- Create  table  :
     mysql>  create  table  login ( User_Id  varchar(18) ,  Time_Spent  int ,  hits  int , gender  char(1));

5- showing  the  name  of  all  tables  in the  database :
     mysql > show  tables;

6-deleting  table :
     mysql> drop  log_table;

7- show  the  structure of  tables :
     mysql> describe  table_name ;

8- changing  the  structure  of  tables (adding/ deleting  columns):
     mysql> alter  table table_name  DROP   column_Name ;
                                             or
     mysql> alter  table  table_name   add  Reg_No  varchar(18);

9- Inserting  rows  into  table :
     mysql> Insert  into  table_name  table_column_name     values ();

10-  loading  data  from  .txt  file  into  sql  table:
     mysql> load  data  local infile  "h:/pp.txt"  into  table table_name  fields  terminated  by ","  ;

11- make  one  table with  joining  two  other  table :
     mysql> create  tabel   NV  as (select  T.User_Id, S.Sname, S.Reg_No  from  Tool as  T,  Sol  as S       where  T.User_Id=S.User_Name) ;

12- Export  data  from  mysql  to   .txt  file :
     mysql> select  *  into  outfile  "h:/u.txt"  fields  terminated  by ","  from  NV   where  gender="f" ;

13- showing  favorite numbers  of  lines  from  table  in SQL :
     mysql>  select  *  from  table_name  limit  100 ;

14- Update  data :
     mysql> update  table_name  set  age=age+1 ;

15- delete  data  from   table :
     mysql > delete  from  table_name  where   state="ky"  ;

16- create  another  table based on one tables contents sum  :  
    mysql> select  customer_Id, count(distinct transactions) as  T, sum(quantity) as  items_bought , sum   (amount) as spendings   from  table1  group  by  customer_Id  order by spending desc;






  

Write a Program in java for selecting Special Columns in a file which separated by space

/* open a file which includes columns separeted by "  "  and copy all content in other file  which  fields  separated by "," */ import  java.io.*; import  java.util.*; class SeparateColumns {       public static void main(String  args[]){             File inFile= new File("G:/Sign-Code/cpi.txt");             File outFile=new File("G:/Sign-Code/cccpi.txt");             FileReader ins= null;             FileWriter outs=null;             BufferedReader br=null;             BufferedWriter bw=null;                         try { ins=new  FileReader(inFile);                   outs=new FileWriter(outFile);                   br=new BufferedReader(ins);                   bw=new BufferedWriter(outs);                   String temp[]=new String[7];  // shows number of column=7                   String str;                   while((str=br.readLine())!=null){                     temp= str.split("\t");                     bw.write(temp[0]);                     bw.append(",");                     bw.write(temp[1]);                     bw.append(",");                     bw.write(temp[2]);                     bw.append(",");                     bw.write(temp[3]);                     bw.append(",");                     bw.write(temp[4]);                     bw.append(",");                     bw.write(temp[5]);                     bw.append(",");                     bw.append(temp[6]);                     bw.newLine();}                     }catch(IOException e){}                     finally {                           try {                                 br.close();                                 bw.close();} catch(IOException e){}                                                                                         }                 }       }

Import and Export .txt files to MYSQL

One of  the main poblems  related to work with SQL is :  exporting and importing  .txt files to  SQL.
I see myself  too many  websites and books for finding  a simple command  for this purpose , but most of them proposing  tools or  very difficult commands.
Myself  , i am  using  the following  simple command .

1- make a database :  create database name_db ;
          mysql> create database my_exper;

 Enter in  your  database :  Use  my_exper ;

2- make  a  table with your favorite number of  columns:  create table exper ( U_Id varchar(18), Reg_No varchar(8) , Mob_No  int , age int);

3- Import  data  from  .txt files on your  table  in  MYSQL :  [ in my .txt file fields  separated  by "," ]
            load data local   infile "d:/myfile/m.txt"   into table  exper   fields  terminated  by  ","  ;
some time  no neccessary  write  local word  on this command.

4- show tables from  my_exper ;
5- show  structure  of  your  table  : describe  exper ;
6- show  all  rows  in  your  table  : select  *  from  exper ;
 7-  Exporting    (saving )  MySQL   data  in .txt   file :
  select  *  into  outfile  "D:/my_results/mr.txt"   fields  terminated  by ","    from   exper where   age>40 ; 
  
I will be happy if  share with me  your  new  ideas.
Best  wishes

First Post

Hello
I am Rozita  , Phd  scholar in India.
My area  of  research is data mining,  web mining , social networking , behavior mining related to education data mining.

I have  problem  related forgetting  all my  own results related to my experience  on different  area of my study, so i decided  make  this  blog  for just  recording my all important results,  codes and  ...

I will be happy  if any one share with me her/his  idea  about different  related results .
best woshes