RSS

Category Archives: My SQL

Add New column in existing MySQL table Specific Row


To add a column LastName to the Student table with a datatype of VARCHAR(60), use the following SQL statement:

ALTER TABLE Student ADD LastName VARCHAR(60);

This first statement will add the LastName column to the end of the table.If you insert the new column after a specific column, such as FirstName, then use this statement:

ALTER TABLE Student ADD LastName VARCHAR(60) AFTER FirstName;

If you want to Add new column to first Column, Then use this statement:

 

ALTER TABLE Student ADD LastName VARCHAR(60) FIRST;

If you want to Add new column with Default Value, Then use this statement:

ALTER TABLE Student ADD LastName VARCHAR(60) Default 0;

Advertisements
 
Leave a comment

Posted by on November 19, 2015 in My SQL

 

Tags: ,

group by sequence number MySQL


Before you use below sample code i prefer you use this URL code which better for you.

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

 

SAMPLE DataTable

CREATE TABLE CountryCity
 (
 city VARCHAR(100),
 country VARCHAR(100),
 population int
 );

INSERT INTO CountryCity VALUES ('Lahore', 'Pakistan',8175133); 
 INSERT INTO CountryCity VALUES ('Multan', 'Pakistan',3792621); 
 INSERT INTO CountryCity VALUES ('Liquat Pur', 'Pakistan',2695598); 
 
 INSERT INTO CountryCity VALUES ('Kalkata', 'India',2181000);
 INSERT INTO CountryCity VALUES ('Dehli', 'India',808000);
 INSERT INTO CountryCity VALUES ('Goya', 'India',422000);
 
 INSERT INTO CountryCity VALUES ('Riyadh', 'KSA',7825300);
 INSERT INTO CountryCity VALUES ('Madina', 'KSA',1016800);
 INSERT INTO CountryCity VALUES ('Makkah', 'KSA',770800);

MySQL Query to Get Top 2

To get the 2 largest Country cities for each country, you can use the following query in MySQL:
SELECT city, country, population
FROM
(SELECT city, country, population,
@country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM CountryCity
ORDER BY country, population DESC
) ranked
WHERE country_rank <= 2;

 

The query returns:

 


city
country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621

 

How It Works

Explanation of the MySQL query:

  • Session Variables

Currently MySQL does not support ROW_NUMBER() function that can assign a sequence number within a group, but as a workaround we can use MySQL session variables.

These variables do not require declaration, and can be used in a query to do calculations and to store intermediate results.

@current_country := country

This code is executed for each row and stores the value of country column to @current_country variable.

@country_rank := IF(@current_country = country, @country_rank + 1, 1)

In this code, if @current_country is the same we increment rank, otherwise set it to 1. For the first row@current_country is NULL, so rank is also set to 1.

For correct ranking, we need to have ORDER BY country, population DESC

So if we just execute the subquery:

   SELECT city, country, population, 
       @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
       @current_country := country 
  FROM Countrycities
  ORDER BY country, population DESC

We get the list of cities ranked by population within the country:

city country population country_rank current_country
Kalkata India 2181000 1 India
Dehli India 808000 2 India
Goya India 422000 3 India
riyadh KSA 7825300 1 KSA
Madina KSA 1016800 2 KSA
Makkah KSA 770800 3 KSA
Lahore Pakistan 8175133 1 Pakistan
Multan Pakistan 3792621 2 Pakistan
Liquat Pur Pakistan 2695598 3 Pakistan
  • Selecting Range

When we have a rank assigned to each city within its country, we can retrieve the required range:

   -- Get top 2 for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank <= 2;
 
   -- Get the city with 3rd population for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank = 3;

ROW_NUMBER() – Oracle, SQL Server and PostgreSQL

In Oracle, SQL Server and PostgreSQL you can achieve the same functionality using ROW_NUMBER function:

   SELECT city, country, population
   FROM
    (SELECT city, country, population, 
                  ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
      FROM cities) ranked
   WHERE country_rank <= 2;

This query works in Oracle, SQL Server and PostgreSQL without any changes and returns:

city country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621
 
Leave a comment

Posted by on February 12, 2015 in My SQL

 

Tags: , , ,

Add custom Code in Mysql Table Column Using Query


if you face problem like that then this code very helpful for you and save your lot of time.

Example: if you want to add Table Unique code after data Enter and you want to inter unique code in sequence with custom code type then use this way.i added image for your help to understand.

After Alter to add new Column with the name of locationCode then you first run this query for number sequence

SET@rank:=0;update T
set Number=@rank:=@rank+1

My Code:

SET @rank:=0;
update location
set LocationCode=@rank:=@rank+1

After Execute this code table column appear Value in Number sequence.if you set custom code type then..


UPDATE Table SET Column= Concat('0', Column);

MyCode: 


update location
set LocationCode=Concat('LOC-', LocationCode)



Add custom Secuance With Query



 

 

 
Leave a comment

Posted by on April 14, 2014 in My SQL

 

Tags: ,

Log_bin_trust_function_creators Mysql


We now have our dump file containing the functions and stored procedures.
I thought I was done at this stage. But I got the following error message while restoring the database on the destination server:

ERROR 1419 (HY000) at line 1140: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

 

The error message is pretty clear. Binary logging needs to be enabled in order to restore the functions. There are two ways for doing this:

  • Execute the following command into the MySQL console:
    SET GLOBAL log_bin_trust_function_creators = 1;
  • Add the line log_bin_trust_function_creators = 1 to the mysql.ini configuration file
 
Leave a comment

Posted by on April 1, 2014 in My SQL

 

Tags:

Backing Up Database from Remote Server to Local in VB.NET


 

 

Private Sub BackupMySql()

 

Dim localDir As String = “D:\”

Dim ftphost As String = “Mybkp”

Dim strDate As String = Date.Now.ToShortDateString ‘Prepend file with date for dated backups

 

Dim fileName As String = strDate.Replace(“/”, “-“) & “_” & ftphost & “.sql”

 

Dim saveFile As String = localDir & fileName

 

Dim DBServer As String = “192.168.10.40”

 

Dim DBServerPort As String = “mysql port”

 

Dim Database As String = “erpisto”

 

Dim DBUser As String = “root”

 

Dim DBPass As String = “root”

 

Dim MyConString As String = “SERVER=” & DBServer & “;DATABASE=” & Database & “;UID=” & DBUser & “;PASSWORD=” & DBPass

 

Dim connection As New MySqlConnection(MyConString)

 

Dim tablesCommand As MySqlCommand = connection.CreateCommand()

 

Dim rowsCommand As MySqlCommand = connection.CreateCommand()

 

Dim tablesReader As MySqlDataReader

 

Dim rowsReader As MySqlDataReader

tablesCommand.CommandText = “SHOW TABLES FROM ” & Database

connection.Open()

tablesReader = tablesCommand.ExecuteReader()

 

Dim query As String = “”

 

Dim tablesArray As New ArrayList()

 

While tablesReader.Read()

tablesArray.Add(tablesReader.GetValue(0).ToString())

 

End While

tablesReader.Close()

 

For i = 0 To tablesArray.Count – 1

query += “DROP TABLE IF EXISTS `” & tablesArray(i) & “`;” & vbCrLf ‘& Database & “.”

query += vbCrLf & “CREATE TABLE `” & tablesArray(i) & “` (” & vbCrLf

rowsCommand.CommandText = “DESCRIBE ” & tablesArray(i)

rowsReader = rowsCommand.ExecuteReader()

 

Dim temp As String = “”

 

While rowsReader.Read()

query += “`” & rowsReader.GetString(“Field”) & “` ” & rowsReader.GetString(“Type”)

 

If Not rowsReader.GetString(“Null”) = “YES” Then

query += ” NOT NULL”

 

End If

 

If IsDBNull(rowsReader.Item(“Default”)) = False Then

query += ” DEFAULT ‘” & rowsReader.GetString(“Default”) & “‘”

 

End If

 

If Not rowsReader.GetString(“Extra”) = Nothing Then

query += ” ” & rowsReader.GetString(“Extra”).ToUpper()

 

End If

 

If rowsReader.GetString(“Key”) = “PRI” Then

temp = “primary key(” & rowsReader.GetString(“Field”) & “)”

 

End If

query += “,” & vbCrLf

 

End While

query += temp & vbCrLf & “);” & vbCrLf & vbCrLf

rowsReader.Close()

rowsCommand.CommandText = “SELECT * FROM ” & tablesArray(i)

rowsReader = rowsCommand.ExecuteReader()

 

While rowsReader.Read()

query += “INSERT INTO `” & tablesArray(i) & “` (“

 

Dim count As Integer = rowsReader.FieldCount – 1

 

Dim keys(count) As String

 

Dim values(count) As String

 

For n = 0 To count

keys(n) = rowsReader.GetName(n)

Dim str As String = rowsReader.GetDataTypeName(n)
If Not rowsReader.GetDataTypeName(n) = “BLOB” Then
If (IsDBNull(rowsReader.Item(n))) Then
values(n) = If(IsDBNull(rowsReader.Item(n)), 0, rowsReader.Item(n))
Else
If Not str = “TIME” Then
values(n) = rowsReader.Item(n)
Else
If Not rowsReader.GetName(n) = “TimeSpan” Then
Dim interval As TimeSpan
interval = DirectCast(rowsReader.Item(n), System.TimeSpan)
values(n) = Convert.ToString(interval)
Else
values(n) = rowsReader.Item(n)

End If

 

End If

End If

Else
values(n) = Nothing

End If

 

Next

query += Join(keys, “, “) & “)” & vbCrLf & “VALUES (‘” & Join(values, “‘, ‘”) & “‘);” & vbCrLf

 

End While

rowsReader.Close()

query += vbCrLf & vbCrLf

 

Next

connection.Close()

connection.Dispose()

 

 

If File.Exists(saveFile) Then

File.Delete(saveFile)

 

End If

 

Dim objWriter As New System.IO.StreamWriter(saveFile)

objWriter.Write(query)

objWriter.Close()

 

End Sub

 
Leave a comment

Posted by on January 28, 2014 in My SQL

 

Tags: ,

Restore on Local Mysql Database through vb.net


if you want to restore mysql data on local mysql database then use below code.

1. Just Change “C:\wamp\bin\mysql\mysql5.6.12\bin\ ” path url according to your install wamp sever or mysql sever.

2. Backup file or script data path…… E:\db\mpostest.sql

3. “mpostest”  mysql database name

 

Try

Dim myProcess As New Process()
myProcess.StartInfo.FileName = “cmd.exe”
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = “C:\wamp\bin\mysql\mysql5.6.12\bin\”
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput
myStreamWriter.WriteLine(“mysql -u root  -proot mpostest < E:\db\mpostest.sql “)
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
Catch ex As Exception

End Try

 

You can also  find here article  restore mysql database on remote or network sever

 

https://humrahimcs.wordpress.com/2014/01/28/restore-on-remote-server-mysql-database-using-vb-net/

 
Leave a comment

Posted by on January 28, 2014 in My SQL

 

restore on remote server mysql database using vb.net


if you want to restore mysql database on remote sever or network deploy server then this code very help you.i also wast lot of time for that at the end this solution resolve my problem.

1. Just Add Remoter Sever Name/IP   -h192.168.10.40

2. In WorkingDirectory  give your Install Dir mysql Path.

3. E:\db\mpostest.sql you can use Filediaglog for dynamic path

4. You must create Empty db with name mpostest  in mysql for testing else give your orignal db name

Dim myProcess As New Process()
myProcess.StartInfo.FileName = “cmd.exe”
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = “C:\wamp\bin\mysql\mysql5.6.12\bin\”
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput
myStreamWriter.WriteLine(“mysql -u root -h192.168.10.40 -proot mpostest < E:\db\mpostest.sql “)
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
Catch ex As Exception

End Try

***** Also you can find Article on Local db & Backup mysql db(which resolve issue of blob , time,timespan type issue).because mostly backup article give Error for these type.

 
1 Comment

Posted by on January 28, 2014 in My SQL

 

Tags: