SELECT COUNT(*) AS repetitions, last_name, first_name FROM cat_mailing GROUP BY last_name, first_name HAVING repetitions > 1;
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
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); Load tab-delimited data into a table
LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name; (Use \n for NULL) Inserting one row at a time
INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31'); (Use NULL for NULL) 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"; Reloading a new data set into existing table
SET AUTOCOMMIT=1; # used for quick recreation of table
DELETE FROM pet;
LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table; Fixing all records with a certain value
UPDATE table SET column_name = "new_value" WHERE record_name = "value"; Selecting specific columns
SELECT column_name FROM table; 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
SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table]; MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day. Pattern Matching
SELECT * FROM table WHERE rec LIKE "blah%"; (% is wildcard - arbitrary # of chars) Find 5-char values
SELECT * FROM table WHERE rec like "_____"; (_ is any single character) Extended Regular Expression Matching
SELECT * FROM table WHERE rec RLIKE "^b$"; (. 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
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
(Example)
SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name; (You can join a table to itself to compare by using 'AS') Currently selected database
SELECT DATABASE(); Maximum value
SELECT MAX(col_name) AS label FROM table; Auto-incrementing rows
CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
INSERT INTO table (name) VALUES ("tom"),("dick"),("harry"); Adding a column to an already-created table
ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name; Removing a column