Selecting a database

 USE database; 

Listing databases

 SHOW DATABASES; 

Listing tables in a db

 SHOW TABLES; 

Describing the format of a table

  DESCRIBE table; 

Creating a database

  CREATE DATABASE db_name; 

Creating a table

  CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));  CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE); 

Inserting one row at a time

  INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31'); 

Retrieving information (general)

  SELECT from_columns FROM table WHERE conditions; ## All values:   SELECT * FROM table; ## Some values:  SELECT * FROM table WHERE rec_name = "value"; ##Multiple critera:  SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2"; 

Fixing all records with a certain value

  UPDATE table SET column_name = "new_value" WHERE record_name = "value"; 

Retrieving unique output records

  SELECT DISTINCT column_name FROM table; 

Sorting

  SELECT col1, col2 FROM table ORDER BY col2; Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC; 

Date calculations

MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

  SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table]; 

Pattern matching

“%” is a wildcard, equalling an arbitrary number of characters. “_” is any single character

  SELECT * FROM table WHERE rec LIKE "blah%"; ##Find 5-char values  SELECT * FROM table WHERE rec like "_____"; 

Extended regular expression matching

To force case-sensitivity, use “REGEXP BINARY”. “.” for char, […] for char class, * for 0 or more instances, ^ for beginning, {n} for repeat n times, and $ for end) (RLIKE or REGEXP)

  SELECT * FROM table WHERE rec RLIKE "^b$"; 

Counting rows

  SELECT COUNT(*) FROM table; 

Grouping with counting

  SELECT owner, COUNT(*) FROM table GROUP BY owner; (GROUP BY groups together all records for each 'owner') 

Selecting from multiple tables

 SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name; 

Currently selected database

  SELECT DATABASE(); 

Maximum value

  SELECT MAX(col_name) AS label FROM table; 

Adding a column to an already-created table

  ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name; 

Removing a column

Full ALTER TABLE syntax available at mysql.com.

  ALTER TABLE tbl DROP COLUMN col; 

Batch mode (feeding in a script)

Use -t for nice table layout and -vvv for command echoing.

 mysql -u user -p  batch_file ##Alternatively:  source batch_fie; 

Backing up

 #back up a database<br />mysqldump --opt -u username -p database &gt; database_backup.sql<br /><br />#back up multiple databases<br />mysqldump -u username -p --databases database_one database_two &gt; two_databases.sql<br /><br />#back up all databases<br />mysqldump --opt --all-databases &gt; all_backup.sql 

Restoring a backup

Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.

mysql database_name &lt; database_backup.sql<br /><br />#restore a single database from dump of all the databases<br />mysql -u username -p --one-database database_name &lt; all_backup.sql