Strictly Programming

Mini FAQ for MySQL

Posted in MySql by sqllyw on 12/06/2008

How to save query output to a text file

select id from names where id > 100 into outfile "/tmp/names.txt"

How to save query output to a csv file
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Backup database

mysqldump -u [username] -p[password] [databasename] > [backupfile.sql]
mysqldump -u root -pmysql mydatabase > backupfile_08_12_12.sql

A good article about backup can be found here

How to change password
UPDATE mysql.user SET Password=PASSWORD(‘mysql’) WHERE User=’root’;
More here…

Insert into … select ..
Link to this
insert into leaders (user_id, remark) select id, “leader” from user where id > 1000;

Advertisements
Tagged with:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: