advanced search

We Recommend

SQL Cookbook SQL Cookbook
Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Posted By

chrisaiv on 03/23/08


Tagged

mysql


Versions (?)


Who likes this?

8 people have marked this snippet as a favorite

sergiomco
basicmagic
melling
uisluu
heinz1959
mitchkramez
snucko
lfatr


MySQL Commands I wish I could remember

Published in: SQL 

1. Load Data from a csv file
2. Backing up a complete database
3. Backing up a specific table
4. Backing up multiple databases
5. Restoring a database
6. Setting up foreign keys between two databases


Plain TextExpand 
  1. #Loading Data CSV into a general table
  2.  
  3. LOAD DATA INFILE "/data.csv" 
  4.   INTO TABLE alldata 
  5.   FIELDS TERMINATED BY ",";  
  6.  
  7. #Backing up the complete database
  8.  
  9. mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]
  10.  
  11.  
  12. #Back Up specific tables. *Note: Multiple tables are seperated by a space.
  13.  
  14. mysqldump -u [username] -p [password] [databasename] [table1 table2 etc]
  15.  
  16.  
  17. #Backing up multiple databases
  18.  
  19. mysqldump -u [username] -p [password] --databases [databasename] > [backupfile.sql]
  20.  
  21.  
  22. #Restoring a Database
  23.  
  24. mysql -u [username] -p [password] [database_to_restore] < [backupfile.sql]
  25.  
  26. #Creating a username and password instead of using root
  27.  
  28. GRANT ALL PRIVILEGES ON database_development.* TO 'chrisaiv'@'localhost' 
  29.   IDENTIFIED BY 'password' WITH GRANT OPTION; 
  30.  
  31. #Setting a foreign key between two databases
  32.  
  33. UPDATE albums SET artist_id = 3 WHERE id = 6;

Report this snippet 

You need to login to post a comment.