Mysql

Cheatsheet hat tip

Selecting a database:

Listing databases: Listing tables in a db: Describing the format of a table: Creating a database: Creating a table: Load tab-delimited data into a table: (Use \n for NULL) Inserting one row at a time: (Use NULL for NULL)

Retrieving information (general): 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";

Reloading a new data set into existing table: Fixing all records with a certain value: Selecting specific columns: Retrieving unique output records: Sorting: Backwards:

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

Pattern Matching: (% is wildcard - arbitrary # of chars) Find 5-char values: SELECT * FROM table WHERE rec like "_____"; (_ is any single character)

Extended Regular Expression Matching: (. for char, [...] for char class, * for 0 or more instances ^ for beginning, {n} for repeat n times, and $ for end) (RLIKE or REGEXP) To force case-sensitivity, use "REGEXP BINARY"

Counting Rows: Grouping with Counting: (GROUP BY groups together all records for each 'owner')

Selecting from multiple tables:

(Example) (You can join a table to itself to compare by using 'AS')

Currently selected database: Maximum value: Auto-incrementing rows: Adding a column to an already-created table: Removing a column: (Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script): (Use -t for nice table layout and -vvv for command echoing.) Alternatively: mysql> source batch_file;

Backing up a database with mysqldump: (Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.) (More info at MySQL's docs.)