1. CRUD
    1. Insert
    2. Load
      1. Reloading a New data Set Into an Existing Table
    3. Select
      1. Select with Time
      2. Select with LIKE:
      3. Select with RLIKE / REGEXP:
    4. Update
  2. Command Line
    1. Create & Drop Database
    2. Batch mode
    3. MySQL Dump
  3. Meta-data
  4. Table Manipulations
    1. Alter
      1. Altering Multiple Columns in One Command
    2. Create Table
    3. Drop Tabble

CRUD

Insert

  INSERT INTO people (name) VALUES ("tom"),("dick"),("harry");  # Batch Insert w/ given name column
  INSERT INTO people VALUES ('MyName', '2002­08­31');  # Inserting one row at a time (Use NULL for NULL)
  INSERT INTO people (name, company_id, created_at) SELECT name, 50, NOW() FROM people WHERE company_id = 49; # copying rows from the same table

Load

  LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;     # Load tab­delimited data into a table (Use \n f or NULL)

Reloading a New data Set Into an Existing Table

  SET AUTOCOMMIT=1;  ### used for quick recreation of table  
  DELETE FROM pet;  
  LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Select

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

  SELECT column_name FROM table;                                         # Selecting specific columns
  SELECT DISTINCT column_name FROM table;                                # Retrieving unique output records
  SELECT col1, col2 FROM table ORDER BY col2;                            # Sorting
  SELECT col1, col2 FROM table ORDER BY col2 DESC;                       # Sorting Backward
  SELECT COUNT(*) FROM table;                                            # Counting Rows
  SELECT owner, COUNT(*) FROM table GROUP BY owner;                      # Grouping with Counting
  SELECT MAX(col_name) AS label FROM table;                              # Maximum value

  SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;  # Selecting from multiple tables (You can join again the same table by alias w/ "AS")

Select with Time

  • MONTH(some_date) extracts the month value.
  • DAYOFMONTH() extracts day.
  SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)­YEAR(date_col)) AS time_diff [FROM table];

Select with LIKE:

  SELECT * FROM table WHERE rec LIKE "blah%";  # % is wildcard ­ arbitrary ### of chars
  SELECT * FROM table WHERE rec LIKE "_____";  # Find 5­char values: _ is any single character

Select with RLIKE / REGEXP:

  • . for char
  • [...] for char class
  • * for 0 or more instances
  • ^ for beginning
  • {n} for repeat n times
  • $ for end
  • (RLIKE or REGEXP) 
  SELECT * FROM table WHERE rec RLIKE "^b$";

Update

  UPDATE table SET column_name = "new_value" WHERE record_name = "value";  # Fixing all records with a certain value

Command Line

Create & Drop Database

  mysqladmin -uroot -p create database 
  mysqladmin -uroot -p -f drop database # -f is for no prompt

Batch mode

Use option -tfor nice table layout and option -vvv for command echoing: 

  mysql ­u user ­p -t -vvv < batch_file

Alternatively you can use this in mysql console:

  mysql> source batch_file;

MySQL Dump

  # Backing up a database with mysqldump (Use 'mysqldump ­­opt ­­all­databases):
  mysqldump -uroot -p database > database_backup.sql 
  # Just the schema definition:
  mysqldump -d -hlocalhost -uroot -p database > dumpfile.sql
  # You can also export a particular table:
  mysqldump -uroot -p database name_of_table > tablefile.sql

  # Dump a table w/ within a time span
  mysqldump database table_name --where='created_at < NOW() - INTERVAL 1 MONTH' > tablefile.sql

  # To import 
  mysql -uroot database_name < dumpfile.sql

Meta-data

  USE database;                   # Selecting a database
  SELECT DATABASE();              # Currently selected database
  SHOW DATABASES;                 # Listing databases
  SHOW TABLES;                    # Listing tables in a db 
  DESCRIBE table;                 # Describing the format of a table
  CREATE DATABASE db_name;        # Creating a database

Table Manipulations

Alter

  ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;      # Adding a column to an already­created table
  ALTER TABLE tbl DROP COLUMN col;                                       # Removing a column
  ALTER TABLE table_name ADD INDEX index_name (col_name);                # Adding index to an existing table;

Altering Multiple Columns in One Command

This usually runs much faster comparing to altering table multiple times) 

  ALTER TABLE authors 
    ADD     name VARCHAR(255), 
    CHANGE  author_work_id wokr_id INT,
    DROP    nickname,
    CHANGE `count(*)` cnt bigint(21),  ### renaming
    ALTER   is_rich SET DEFAULT FALSE;

Create Table

  CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));  
  CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
  CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name  CHAR(10) NOT NULL);    # auto incrementing a column

Drop Tabble

  DROP TABLE table_name