Database administration

I am learning MySQL at home so that I have the power of databases for my own personal research.

One thing that I do is make mistakes. How do you undo them?  Transactions.

The command line is powerful but requires learning syntax.  GUI is fast but not always as powerful.

So when I import data to update my personal database of gold, silver and currencies, I want to be able to undo mistakes.

Code for a single insert:

start transaction;
insert into london_fix values ('2010-07-21',1,1,1,1,1,1,1) ;
select * from london_fix order by dtDate desc;
rollback;

Code for importing a text file:

start transaction;

LOAD DATA LOCAL INFILE 'z:\\gold.txt'
REPLACE
INTO TABLE london_fix
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

show warnings;

rollback;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s