Taking dump of query result into file

I have users table in  my database and If you want to take all user rows data into file, then execute below query..
SELECT id,user_name, address into OUTFILE  ‘d://userdbdump.txt’ FIELDS TERMINATED BY ‘,’ FROM users

Read Full Post »

Joins In MySql

Create table by using these statements
create table apples (variety char(10) not null primary key, price int not null);
create table oranges (variety char(10) not null primary key, price int not null);
insert into apples(variety, price) values(‘Fuji’,5),(‘Gala’,6);
insert into oranges(variety, price) values(‘Valencia’,4),(‘Navel’,5);

mysql> select * from apples;
+———+——-+
| variety | price |
+———+——-+
| Fuji | 5 [...]

Read Full Post »

Finding last inserted autoincrement id value

Suppose you have table like
User Table with fields id(auto increment) and name
address table with field id(auto increment), address, user_id(foreign key)
when u inserted one row in user table then u want the last inserted id value in user table to insert it into address table in the field user_id for that corresponding user address entry
use mysql [...]

Read Full Post »

Import or Export A Database

This section deals with mysqldump which is a tool to import and export MySQL databases.
It can be used to back up a database or to move database information from one server to another.
1. Export A MySQL Database
This example shows you how to export a database. It is a good idea to export your data [...]

Read Full Post »

Importing and Exporting data from csv/txt file to table

Importing
The example below imports data from .txt file into table testtable.
temp.txt file is a tab separated file:
“1 string”      100
“2 string”      102
“3 string”      104
“4 string”      106
testtable structure

CREATE TABLE testtable
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
text varchar(45) NOT NULL,
price integer not null);
 
query = “LOAD DATA INFILE ‘”+filename+
    “‘ INTO TABLE testtable (text,price);”; 
 
If you want to [...]

Read Full Post »