MySQL cheat sheet

MySQL_Cheat_Sheet.sql

/*
 * mysql -u root -p < query_test.sql
 * senha geralmente é admin
 */

CREATE DATABASE name_of_the_database;
SHOW DATABASES;
DESCRIBE name_of_the_database;
USE name_of_the_database;
SHOW tables;

CREATE USER 'name_of_the_user'@'localhost' IDENTIFIED BY 'user_password';
GRANT ALL ON name_of_the_database.* TO name_of_the_user@localhost;
SHOW GRANTS FOR name_of_the_user@localhost;
SELECT User FROM mysql.user;

/* Qual banco estou usando no momento? */
SELECT DATABASE();

DROP DATABASE name_of_the_database;
DROP USER 'name_of_the_user'@'localhost';

/*
 * "Keep in mind throughout that, although the MySQL command line does
 * not pay attention to cases, the table and database names are case
 * sensitive: potluck is not the same as POTLUCK or Potluck."
 */

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
  name VARCHAR(20),
  food VARCHAR(30),
  confirmed CHAR(1), 
  signup_date DATE);

INSERT INTO `potluck`
  (id, name, food, confirmed, signup_date)
VALUES
  (NULL, "John", "Casserole","Y", "2012-04-11");

SELECT * FROM potluck;

DELETE from potluck
WHERE id = 'x';

UPDATE potluck
SET
confirmed = 'Y'
WHERE name = 'John';

ALTER TABLE name_of_the_table
ADD email VARCHAR(40)
AFTER name_of_any_column;

ALTER TABLE name_of_the_table
DROP name_of_any_column;

/*backup: */ mysqldump -u root -p [database_name] > dumpfilename.sql
/* ou:    */ mysqldump -h [127.0.0.1] -P [3006] -u [username] -p [database_name] > dump_file_name.sql

/*restore:*/  mysql -u root -p [database_name] < dumpfilename.sql

/*
 * Source: https://www.digitalocean.com/community/tutorials/a-basic-mysql-tutorial
 *         http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
 */