Notes made initially whilst studying for the MySQL 5 Certified Associate Exam (and then expanded)

mysql5

EXAM DETAILS
  Main MySQL Website: http://www.mysql.com/
	Certification Webpage: http://education.oracle.com/
	Exam Name: MySQL 5 Certified Associate Exam
	Exam Number: 1Z0-870
e
=========================================================================================================
EXAM TOPICS
	Section 1: Theory, Terminology and Concepts (10%)
		Client/Server Concepts
		Database and Database Objects
		
	Section 2: Data Definition using SQL (25%)
		Databases
		Data Types
		Tables
		Constraints and Indexes
		Views
		
	Section 3: Basic Data Manipulation using SQL (25%)
		Recurring SQL Constructs
		Adding data
		Modifying data
		Removing data
		Searching data
		
	Section 4: Advanced Data Manipulation using SQL (20%)
		Expressions
		Grouping and Aggregate Functions
		Joining Tables
		
	Section 5: Transactions (10%)
		Transaction Concepts
		SQL for working with Transaction
		
	Section 6: Import/Export (10%)
		Tools for Import/Export
		SQL for Import/Export

=========================================================================================================
SQL CHEAT SHEET

	SELECT DATABASE(); //Shows currently selected database
	SELECT VERSION(); //Shows version of current database
	------------------------------------------------------------------------------------------------------
	SET time_zone = '+00:00'; //Manually set connection specific timezone
	SET sql_mode = 'STRICT_TRANS_TABLES'; //Set sql_mode (Default = '', forgiving) (Comma delimit multiple modes)
	SET [GLOBAL|SESSION] storage_engine = engine_name; //Set default storage engine, need SUPER priviledge for GLOBAL and only affects future connections
	SET character_set_connection = 'utf8' //Literal string character set
	SET collation_connection = 'utf8_general_ci' //Literal string collation
	SET max_allowed_packet = 'value' //Controls max size of query buffer
	SET @user = value //Set a user variable
	SET autocommit = value //Set autocommit (1 or 0)
	SET completion_type = value //Set autocommit (1 or 0)
	------------------------------------------------------------------------------------------------------
	SELECT @@global.time_zone, @@session.time_zone //Get default and connection time_zone settings
	SELECT @@global.sql_mode, @@session.sql_mode
	+ others from above
	------------------------------------------------------------------------------------------------------
	USE db_name; //Switch current default database
	------------------------------------------------------------------------------------------------------
	SHOW [FULL] TABLES [{FROM | IN} db_name] //FULL includes base/view columns
		[LIKE 'pattern' | WHERE expr]	
	SHOW WARNINGS; //Display warnings, including altered values in non-strict mode
	SHOW CREATE DATABASE dtabase_name //Show CREATE DATABASE for database_name
	SHOW CREATE TABLE table_name; //Show CREATE TABLE sql for table_name
	SHOW CREATE VIEW view_name; //Show CREATE VIEW sql for table_name
	SHOW DATABASES [LIKE 'pattern'] //Need priviledge, displays datbase names on server which current user has access for
	SHOW TABLES [FROM database] [LIKE 'pattern'] //display tables in default database (if no FROM clause), or error if no default
	SHOW ENGINES; //Display available storage engines
	SHOW INDEX FROM table_name; //Display index info from table
	SHOW PROCESSLIST;
	SHOW PROFILES; //Query info from current session (enable with SET profiling = 1);
	------------------------------------------------------------------------------------------------------
        KILL [CONNECTION | QUERY] thread_id //Get thread_id from SHOW PROCESSLIST;
	------------------------------------------------------------------------------------------------------
	DESCRIBE table_name; //Show table definition
	SHOW COLUMNS FROM table_name; //Same
	SHOW FIELDS FROM table_name; //Same
	------------------------------------------------------------------------------------------------------
	DATABASE == SCHEMA //i.e. they are interchangable since MySQL 5
	DATABASES == SCHEMAS //i.e. they are interchangable since MySQL 5
	* //All columns in table (may not be in order)
	------------------------------------------------------------------------------------------------------
	CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET character_set] [COLLATE collation]
		- Error if no exists clause and db already exists
	------------------------------------------------------------------------------------------------------
	ALTER DATABASE db_name CHARACTER SET character_set COLLATE collation
		- Only affects new tables in database
		- Can omit database name, in which case default database will be altered, else error
	------------------------------------------------------------------------------------------------------
	DROP DATABASE [IF EXISTS] db_name;
		- Removes entire database directory
		- Warning if exists clause and db does not exist, error without clause
	------------------------------------------------------------------------------------------------------
	CREATE [TEMPORARY] TABLE [IF EXISTS] table_name (
		column_name column_type column_attributes,
		...
		[INDEX|KEY [name] [USING algo] (column_name)] //Other way of specifying index, can be multi-column with comma delimited
		[UNIQUE [name] [USING algo] (column_name)] //Other way of specifying unique index, can be multi-column with comma delimited
		[PRIMARY KEY [USING algo] (column_name)] //Other way of specifying primary key, can be multi-column with comma delimited
	) [ENGINE = engine_name] [DEFAULT CHARSET=chracter_set];
	
	CREATE TABLE table_name SELECT query; //Create table with contents of query
	CREATE TABLE table_name LIKE table_name; //Create empty table with definition like that of other table
		- Column Attributes	
			General
				NULL|NOT NULL - Allow / disallow NULLs in column, default = NULL
					NULL cannot be stored in AUTO_INCREMENT (gives int), or TIMESTAMP (gives CURRENT_TIMESTAMP)
				DEFAULT value - Set a default value for column
					Cannot be used with TEXT / BLOB or TIMESTAMP with auto-update
					Cannot be an expression (except CURRENT_TIMESTAMP)
					Cannot be NULL if column is NOT NULL
					Cannot be out-of-range
					Without DEFAULT, NULL will be used when possible
						Else 0 if numeric, 1st member if ENUM, '' if String or zero value if temporal
				PRIMARY KEY
					Add primary index to column
					Not BLOB / TEXT
				UNIQUE
					Add index to column
			
			Numeric types (other than BITCREA)
				UNSIGNED - Causes negative values to be disallowed, extends positive range
				ZEROFILL - Left pad width with 0 on left to max width on retrieval (Makes column UNSIGNED)
				AUTO_INCREMENT
					- Null inserted values give next in sequence, only one per table, must be indexed, non-null, integer column.
					- Set a positive unused value to reset counter
					- Inserting 0 gives next in sequence unless NO_AUTO_VALUE_ON_ZERO SQL mode set (then will give 0)
					- Updating value with NULL or zero gives 0 value
					- Behavior for replace is same as insert
					- Going out of range will result in duplicate key errors
					- Removing rows does not reset counter (MyISAM / InnoDB)
					- In MyISAM can use multi-column with AUTO_INCREMENT, e.g. PRIMARY KEY (name, id) for duplicate names, ids are reused but combination is unique
				
			String types (non-binary strings)
				CHARACTER SET / CHARSET - Specify character set for column. (If combined with COLLATE then collation must be legal for character set) 
				COLLATE - Specify character collation (If combined with CHARACTER SET then collation must be legal for character set)
					If COLLATE omitted will use default for character set, if CHARACTER SET omitted will use correct character set for specified collation, if both omitted than table defaults are used
				BINARY - Shorthand for binary collation
					Binary collaction converts CHAR / VARCHAR / TEXT to BINARY / VARBINARY / BLOB
	------------------------------------------------------------------------------------------------------			
	ALTER TABLE table_name ADD column_name column_type column_attributes [FIRST|AFTER column_name]; //Add a column to end of table or with optional first / after placement
	ALTER TABLE table_name ADD PRIMARY_KEY|INDEX|UNIQUE [index_name] [USING algo] (column1, ...) //Add an index, optional USING clause for algo choice
	ALTER TABLE table_name DROP column_name|INDEX index_name|PRIMARY_KEY; //Drop a column, index or PRIMARY KEY index
	ALTER TABLE table_name MODIFY column_name column_type column_attributes; //modify a column definition in table
	ALTER TABLE table_name CHANGE column_name new_column_name column_type column_attributes; //modify a column definition in table including it's name
	ALTER TABLE table_name ENGINE = engine_name //Change storage engine for table_name
	ALTER TABLE table_name RENAME TO table_name //Rename a table
		- Alter table clauses can be comma delmited to modify table in multiple ways e.g. ALTER TABLE name ADD ....., DROP ...., ENGINE ...;
	------------------------------------------------------------------------------------------------------
	RENAME TABLE table_name TO table_name //Rename a table alternative, can comma delimit multiple changes
	------------------------------------------------------------------------------------------------------
	CREATE [UNIQUE] INDEX name [USING algo] ON table (column, ...) //lame way of creating index (must specify name, can't do PRIMARY KEY, one per statement)
	------------------------------------------------------------------------------------------------------
	DROP INDEX index_name ON table_name; //Remove an index, use 'PRIMARY' do remove primary key without collision
	------------------------------------------------------------------------------------------------------
	DROP TABLE [IF EXISTS] table_name; //Remove table, can be comma delimited, error if does not exist without clause, with gives warnings
	------------------------------------------------------------------------------------------------------
	SELECT [DISTINCT] values_to_display
	[INTO OUTFILE]
	[FROM table_name]
	[WHERE expression]
	[GROUP BY how_to_group [WITH ROLLUP]]
	[HAVING expression]
	[ORDER BY how_to_sort]
	[LIMIT row_count]
	[UNION [ALL]]
	...
		- values_to_display can include expressions which return numbers, strings, temporal values and NULL
		- a value_to_display can be aliased e.g. columns_name AS Alias (AS is optional, quote for spaces, can refer to in all other clauses but WHERE)
		- table names should be qualified with a database if
			- There is no default database set
			- table is in a different database than default database (could alt do USE statements either side of query)
			- Uncertain which is default database
		- ORDER BY can be multiple comma delimited columns / combined with ASC and DESC
		- ORDER BY can also use aliases, expressions and 1-indexed position of column in values_to_display list (i.e. ORDER BY 2 to order by second column in select)
		- ORDER BY can be combined with LIMIT or DELETE or UPDATE to select a range of values
		- LIMIT has two forms, LIMIT row_count and LIMIT skip_count, row_count (no expressions)
		- DISTINCT affected by collation for non-binary strings, equiv to grouping by all columns with no aggregate functions
		- GROUP BY can take multiple arguments, only select grouped values and aggregates, by default MySQL also sorts when using GROUP BY
		- HAVING applies to group, use summary (aggregate) functions
		- Select order, WHERE, GROUP BY, HAVING
		- WITH ROLLUP returns summary value for whole set as well as groupings with NULL for non-summary returns in select, works for multiple group clauses (will then apply to each sub-group as well as full set)
		- UNION: Rows from selects combined with UNION will be put into single set in order of selects, must have same number of columns, duplicate rows are removed
		- UNION ALL to keep duplicates
		- Column names in UNION result set come from first select
		- Can add ORDER BY and LIMIT to UNION, just use brackets around selects and then follow with them
		
		Joins
			- FROM can have multiple tables (upto 61) in Mysql 5, e.g. FROM table1 INNER JOIN table2
			- Combines tables
			- Can select columns from any table within a join
			- Ambiguos column names must be qualified, should generally be
			- Ambiguos table names must be qualified, should generally be
			- Can also use aliases for tables in joins to get around ambiguity e.g. self join
			- FROM table1, table2 (Inner Join)
				- Use a WHERE clause to match column in one table to one in another
				- Order of tables does not matter
				- Without WHERE clause, result set includes all possible combinations of rows from joined table (A Cartesian product)
				- Can join UPDATE query this way
					- Update rows in one table with data from another
					- Update rows in one table by refering to condition in other
					- Update mulple tables
					- Cant use GROUP BY and LIMIT with joined UPDATES
			- DELETE table1[,  ...] FROM table1 WHERE table1.id = table2.id / DELETE FROM table 1[,  ...] USING table1, table2 WHERE table1.id = table2.id
				- Deletes rows from listed tables when condition matches in join result set
				- Delete rows in one table by refering to condition in other
				- Delete from multiple tables
				- Cant use GROUP BY and LIMIT with joined UPDATES					
			- FROM table1 INNER JOIN table2 ON column1 = column2 (Inner Join)
				- instead of ON can use USING(column_name) if they are the same in both tables
			- JOIN / CROSS JOIN are same as other inner joins
			- FROM table1 LEFT JOIN table2 ON column1 = column2 (Outer Join)
				- Left is reference table
				- Returns rows from left table even if no matches in right table (with NULLS for missing values from right table)
				- instead of ON can use USING(column_name) if they are the same in both tables
				- Add WHERE possible_null_column IS NULL clause to reduce result set to only those with no match in the right table
			- FROM table1 RIGHT JOIN table2 ON column1 = column2 (Outer Join)
				- Right is reference table
				- Returns rows from right table even if no matches in right table (with NULLS for missing values from left table)
				- instead of ON can use USING(column_name) if they are the same in both tables			
				- Add WHERE possible_null_column IS NULL clause to reduce result set to only those with no match in the left table
				
		- Use ANY / ALL / SOME to qualify comparisons with column sub queries (or IN / NOT IN for '= ANY')
		- Use EXISTS / NOT EXISTS to check if subquery returns any rows
		- Use [ROW]value1, value2) = (SELECT col1, col2...) for row comparison
		
		- Use INTO OUTFILE instead of table_name for export to file
			- See rules for LOAD DATA INFILE for path rules
			- Result not sent by host to client
			- Output file cannot already exist
			- File owned by server, readable by world
			- Default is tab delimited, row per line, all values, 
	------------------------------------------------------------------------------------------------------
	LOAD DATA [LOCAL] INFILE file_name [IGNORE|REPLACE] INTO TABLE table_name [format specifiers] [IGNORE n LINES] [(column list)] [SET (assignment list)]
		- Import rows into single table
		- Use / or \\ as path seperator (\ is excape)
		- Default assumes on file on local server, tab seperated, row per line, value for each column
		- LOCAL means file at client end
		- On server, if full pathname then uses this, if releative with a single component then default database directory, relative with more than one then relative to data directory
		- On client, if full pathname then uses this,releative then relative to CWD of client
		- Skip commonly used to skip 1st row of file (used often for column names)
		- Use column list if column count does not match table, missing go to defaults, extras are ignored
		- Can use @ in column list to assign to user var, then use that var to SET column in new table (SET name = @first where @first was in column list)
		- From server Duplicate-keys in unique columns
			- insert if none
			- error if collide unles IGNORE (then warning)
			- replace if REPLACE
			- Same if from client except ignored inserts do not cause errors by default (like IGNORE)
		- Cannot specify iGNORE and REPLACE together
		- Info given after
			- Records (read from file), deleted (removed by REPLACE), skipped (duplicates with IGNORE), warnings
		- More effiecient than issuing INSERTs
	------------------------------------------------------------------------------------------------------
	INSERT [IGNORE] INTO table_name (columns, ...) VALUES (values, ...)[, ...] [ON DUPLICATE KEY UPDATE column=expression];
		- Can omit all columns and values
		- Can omit all columns, if all values given in correct order of table definition
		- Duplicate key inserts are discarded and cause an error
		- Multirow duplicate key inserts: MyISAM partial, InnoDB rollback
		- IGNORE will treat statement in non-strict mode, overriding restrictions and errors will conversion and warnings
		- IGNORE will also ignore duplicate key insert errors, discarding the row
		- NULLs do not cause duplicate key issues
		- ON DUPLICATE KEY UPDATE will cause a single query to update part of a record rather than fully replace a row if there is a unique-index key collision on insert
		- May affect two or more rows (if there are collions)
		- Other form
			INSERT [IGNORE] INTO table_name SET column_name = value[, ...];
	
	REPLACE INTO table_name (columns, ...) VALUES (values, ...)[, ...]
		- Same as insert except if there is a unique-index key collision on insert then row is removed before new row is inserted
		- Otherwise what applies to INSERT applies to REPLACE
		
	------------------------------------------------------------------------------------------------------
	UPDATE [IGNORE] table_name SET column = value[, ...] [WHERE expression] [ORDER BY expression] [LIMIT value[,value]];
		- Ignore will treat statement in non-strict mode, overriding restrictions and errors will conversion and warnings
		- ORDER BY affects order rows are updated
		- LIMIT limits rows updated
	------------------------------------------------------------------------------------------------------
	DELETE FROM table_name [WHERE expression] [ORDER BY expression] [LIMIT value[, value]]; //Removes data from table
		- Add a always true WHERE clause to stop AUTO_INCREMENT sequence reset
		- Slower than truncate when emptying a whole table
		- ORDER BY affects order in which rows are deleted
		- LIMIT limits rows deleted
	------------------------------------------------------------------------------------------------------
	TRUNCATE TABLE table_name; //Removes all data from table, the wrod TABLE is optional here
		- Does not return affected row count
	------------------------------------------------------------------------------------------------------
	BEGIN [WORK] //Start a Transaction
	START TRANSACTION [WITH CONSISTENT SNAPSHOT] //Start a Transaction (even if in autocommit mode)
	COMMIT [WORK] //Commit (complete) a transaction
	ROLLBACK [WORK] {AND [NO] CHAIN] [[NO] RELEASE] //Rollback (cancel a transaction)
		- WORK always optional
		- CHAIN begins new transaction immediately
		- RELEASE closes connection on COMMIT
	------------------------------------------------------------------------------------------------------
	CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE]
	VIEW view_name [(column list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]
	
		- View name can be qualified with database
		- REPLACE will drop any existing matching views (not tables) before creating new one
		- Option column list of names for columns in view
		- WITH CHECK OPTION, changes made to view are checked to ensure conditions are still met, otherwise changes to view and underlying tables can be dropped
		- Share namespace with base tables
		- View column names must be unique within the view
		- Can use aliases
		- Cannot be TEMPORARY, use triggers, table in select must already exist
		- No FROM subqueries, references to TEMP tables, references to user variables, preferences to procedure parameters or prepared statement parameters
		- UNDEFINED -> MySQL chooses algorithm
		- MERGE -> MySQL merges a statement for a view with the view then executes statement, must have one-to-one base table to view rows
		- TEMPTABLE makes view in temp table then queries
		- Changes to view will update base table if one-to-one and references only (no expressions)
			- Also insert if non-specified values have defaults in base table / references and no expressions
			- WITH CHECK OPTION stops update / insert (so view must still contain new value) if it makes where clause fail for new row
			- LOCAL limits where check to view, CASCADE checks underlying views to
		
		- Alter with same statement as above, just replace CREATE with ALTER
		INFORMATION_SCHEMA has a VIEWS table with view info
		
	------------------------------------------------------------------------------------------------------
	DROP VIEW [IF EXISTS] viewname[, ...]; //if exists supresses errors to warngins
	------------------------------------------------------------------------------------------------------
	CHECK TABLE name; //Use to check if view components have changed causing an error
	------------------------------------------------------------------------------------------------------
	QUERY OPTIMISATION
	
        EXPLAIN query //Shows execution plan for query
          - Table per row
          - Order = execution order
        EXPLAIN EXTENDED query //Same as above but shows extra info when followed by SHOW WARNINGS;
        - Avoid file sorts
        - Use keys
        - ORDER BY NULL can supress sorting in GROUP BY
        - ORDER BY ignored inside UNION SELECTs unless a LIMIT is used
        - Derived table e.g. FROM (SELECT..), often require temporary tables - ensure to EXPLAIN derived         and full query seperately
        - Parenthesis SELECTs outside of FROM are sub-queries, often require temporary tables, correlated         subqueries on entire row of result set (where using reference from outer in inner) - often better         to replace with a derived query - ensure to         EXPLAIN sub and full queries
        - GROUP BY may need temporary table, best to group by indexed columns, sorts by default
	
	#This is a comment
	/* another comment */
	/* multi-line 
	comment */
	-- (followed by space of control character) another comment
	
	/*! SQL only run by MySQL */
	
	/*!50002 SQL only run by MySQL 5.0.2+ */

=========================================================================================================
FUNCTIONS
	LAST_INSERT_ID() //Last auto-generated index value on current session
        FOUND_ROWS() / SQL_CALC_FOUND_ROWS //Number of rows matched in last query (ignores LIMIT)
	
	YEAR(value) //Extract year from date
	MONTH(value)
	DAYOFMONTH(value)
	DAYOFYEAR(value)
	HOUR(value)
	MINUTE(value)
	SECOND(value)
	MAKEDATE(year, dayofyear)
	MAKETIME(hour, minute, second)
	DATE_FORMAT(date, format) //Reformats date according to format
	DATE_ADD('2012-02-02', INTERVAL 7 DAY); //Add interval to a date
	TIME_FORMAT(time, format) //Reformats time according to format
	CONVERT_TZ('2012-04-03 11:12:45', '+01:00', '+-03:00') //Alter timezone of temporal value

	CURRENT_DATE //current , parenthesis optional
	CURRENT_TIME //Current time, parenthesis optional
	CURRENT_TIMESTAMP  //Current datetime, parenthesis optional
	NOW()

        FROM_UNIXTIME(<int>)
        UNIX_TIMESTAMP([<mysql timestamp>])
	
	CAST(character_string AS character_data_type [CHARACTER SET charset_name])
	CONVERT(expr USING characterset)
	ROUND(value, decimal places) / TRUNCATE(value, decimal places)
	
	ISNULL(value) //true if value is NULL
	IFNULL(value1, value2) //If value1 is NULL return value2, else return value1
	
	CURDATE()
	VERSION()	
	
	CONCAT('string', 'another_string', ...); //Simple concatenation, returns NULL if any values are NULL
	CONCAT_WS('seperator', 'another_string', ...); //Simple concatenation with first argument as custom seperator, ignores NULL values
	LENGTH(expression) //returns length of string in bytes
	CHAR_LENGTH(expression) //returns length of string in characters
	STRCMP('string', 'string'); //COmpare strings, -1 if first less than second, 0 if equal, 1 if first greater than second
	REPEAT('x', 5); //Repeat x 5 times	
	UPPER(expression) //Convert string to uppercase, must be non-binary
	LOWER(expression) //Convert string to lowercase, must be non-binary
	
	PASSWORD() //Used by server for user passwords, one way, should not use in apps as may change
	ENCODE() / DECODE()
	DES_ENCRYPT / DES_DECRYPT() //Needs SSL support
	AES_ENCRYPT() / AES_DECRYPT() //Most secure?
	
	MD5(expression) //Return 32 byte MD5 hash, treats argument as binary string
	LEAST(values) //Return smallest value from set
	GREATEST(values) //Return largest value from set
	INTERVAL(values) //Compares first value to rest, returns number which are equal or less than
	FLOOR(value) //Returns largest integer less than argument
	CEILING(value) //Returns smallest integer larger than argument
	ABS(value) //Returns absolute value
	SIGN(value) //Returns -1, 1 or 0 dependant on sign of value
	SIN(value), COS(value), TAN(value) //Return results of trignometric calculations
	PI() //returns value of Pi
	DEGREES(value) / RADIANS(value) //Convert between deggrees and radians
	RAND() //Generate 0-1 decimal value
	
	IF(expression, true, false)
	
	CASE expression
		WHEN expressions THEN result
		[WHEN expressions THEN result] ...
		[ELSE result]
	END
	
	CASE
		WHEN expressions THEN result
		[WHEN expressions THEN result] ...
		[ELSE result]
	END
	
	- If all conditions fail then CASE returns NULL
	
	AGGREGATE FUNCTIONS
		COUNT(*) //Count number of rows in group (even NULLs)
		COUNT(expression) //Count number of non-NULL values in expression, ignores NULLS
		COUNT(DISTINCT expression)//Count number of unique non-NULL values in expression, ignores NULLS
		COUNT(DISTINCT expression, expression)//Count number of unique non-NULL combinations of values in expression
		SUM(expression) //sum values in group, ignore NULL, numeric conversion of other types
		AVG(expression) //Mean, ignore NULL, numeric conversion of other types
		MIN(expression) //Min value, or lexacally first value (affected by COLLATION), or earliest date, ignore NULL
		MAX(expression) //Max value, or lexacally last value (affected by COLLATION), or latest date, ignore NULL
		GROUP_CONCAT(expression) //Concatenates values together with comma by default, ignores NULL values
		GROUP_CONCAT(expression SEPERATOR ' - ') //Concatenates values together with ' - ' (follows ORDER BY if also used)
		GROUP_CONCAT(expression ORDER BY expression) //Concatenates values together with comma in order specified
		GROUP_CONCAT(DISTINCT expression) //Concatenates values together with comma removing duplicate values from group of values
			- All but COUNT (returns 0) return NULL if group is empty / only NULL	
=========================================================================================================
GENERAL
	- MySQL Server / mysqld = Manages access to database(s), different server programs have different storage engines and other capabilities.
	- Client Programs - Communicate with server
		- MySQL Query Browser - GUI (Replaced with SQL Editor stored in User folder appdata)
			- Now part of workbench (Shares info / settings)
		- Mysql Administrator - GUI (Replaced with Admin)
			- Now part of workbench (Shares info / settings stored in User folder appdata)
		- mysql - Command line client
		- mysqldump - command line backup (= INTO OUTFILE)
		- mysqlimport - command line import (= LOAD DATA INFILE)
		- mysqlcheck - command line integrity checker
	- Non-client Programs - Act independantly of the server program
		- myisamchk - Checks and repairs MyISAM tables
		- myisampack - Creates compressed read only MyISAM tables	
	- In windows . also represents localhost

	Locations searched for config:
		Windows: my,ini and my.cnf in windows folder, then in root
		Unix: /etc/my.cnf (or user level in user home directory)
		
	Multiple can be used, missing files do not cause errors
	Clients just need read access

	Format:
	[[program / group name]]
	[fullname]
	[fullname] = [value]
	
	e.g.
	[mysql]
	host = thisisahot.com
	
	Also 'client' is special group for options for all
	
	!include file_name (includes options from other file)
	!includedir dir_name (includes options from .cnf files (+ .ini in windows) in specified folder)
	
	If option is read more than once, last occurance sets value.
	Command line options take precendence over option files.

	libmysqlclient is used by many connectors, also used directly for many C client programs

	Many types for connecting to Mysql Server
		- MySQL Connector/ODBC for ODBC programs
			- C client library, converts ODBC calls to CAPI operations.
			- TCP/IP or pipes, Windows and Unix
		- MySQL Connector/J for JDBC programs
			- Written in Java
			- TCP/IP or named pipes, Windows and Unix
			- Support 
		- MySQL Connector/NET for a ADO.NET interface for .net programs
			- Written in C#
			- TCP/IP or named pipes or socket or shared memory, Windows and linux (using mono)
		- Many others not offically supported
		
		- Unix uses socket for local connection, unless hostname is used then TCP/IP
		- Windows uses TCP/IP for local connection, unless hostname is used then TCP/IP
		- Connecting to . will be shared me first if not named pipe (windows)
		- Connecting to 127.0.01 or 192.168.10.1 will connect to local by TCP-IP on any OS
		- With no host, connects localhost, UNI with socket, Windows with shared else named else tcp-ip
=========================================================================================================
NUMERIC TYPES

	- Precision = significat digits
	- Scale = decimal placed

	Type		Size		Range (Signed)									Range (Unsigned)
	TINYINT		1 byte		-128 -> 127										0 -> 255
	SMALLINT	2 bytes		-32768 -> 32767									0 -> 65535
	MEDIUMINT	3 bytes		-8388608 -> 8388607								0 -> 16777215
	INT			4 bytes		-2147683648 -> 2147683647						0 -> 4294967295
	BIGINT		8 bytes		-9223372036854775808 -> 9223372036854775807		0 -> 18446744093709551615
	
	- Display width can be set e.g. INT(4), defaults to max width of type.
	
	FLOATING POINT
	Type		Size	
	FLOAT		4 bytes (or REAL or DOUBLE PRECISION)
	DOUBLE		8 bytes
	
	- Only approximate
	- Integer part, fractional part or both
	- Can set precision and scale e.g. FLOAT(5, 2)
	
	FIXED POINT
	- DECIMAL OR NUMERIC
	- Declared with precision and scale e.g. DECIMAL(1, 2), defaults to DECIMAL(10,0) / DECIMAL(10) / DECIMAL
	- Approx 4 bytes per nine digits either side of decimal point
	- In standard (non MySQL) NUMERIC digits = precision (ie 100 in 3), DECIMAL digits >= precision (ie 100 in 4). Mysql precision as given for both.

	BIT
	- Binary value
	- Specify length e.g. BIT(2)
	- Range = 0 -> (2^n)-1
	- Storage = INT((n+7/8)

=========================================================================================================
CHARACTER SETS

	- Character set = set of available characters
	- collation determins sort and sensivity
	- Character sets have a default collation
	- Default character set is latin1 / ISO-8859-1
	- non-binary strings are made of characters from a character set
	- can me single byte, multi-byte or mixed
	- Multi-byte comparisons are done by character not bytes
	- Collation determines if character set is case sensitive, and if accented characters are equivalent
	- Binary character set strings comparisons based on numeric value = accented characters and different cases are sensitive
		- This means case functions will not work as intended
		- Character boundaries do not apply	
	- bin = binary, ci = case insensitive, cs = case sensitive in collation names
	- Can set different non-binary character sets and collations in a column
	- collation effects how value can be treated in sorting
		- case insensitive / case sensitive / binary are the common three types
	
	Add CHARACTER SET [value] [COLLATE value] to column definition
	
	- See collations with SHOW COLLATION LIKE blob
	- UTF-8 characters max 3 bytes
	
=========================================================================================================
NON_BINARY STRING TYPES

	- Inherit characters set and collactio of table by default.

	CHAR
	- Fixed length (0 to 255 characters)
	- define using CHAR(length)
	- Padded with spaces when stored / trimmed when retrieved
	- Truncated id inserted string is too long
	- Storage = bytes equal to max length x longest byte character in set
	
	VARCHAR
	- Variable length (0 -> 65,535), with some restrictions placed by most storage engines
	- Storage = no of characters stored + 1 or 2
	- Stored without any extra padding (as is)
	
	TEXT
	- Variable length
	
		Type			Max Size		Storage	
		TINYTEXT		255				Characters stored + 1 bytes
		TEXT			65535			Characters stored + 2 bytes
		MEDIUMTEXT		1677215			Characters stored + 3 bytes
		LONGTEST		4294967295		Characters stored + 4 bytes

=========================================================================================================

BINARY STRING TYPES

	BINARY
	- Fixed length (0 -> 255)
	- Stored with padding
	- Trimmed on retrieval
	- Storage = max length
	
	VARBINARY
	- Variable length (0 -> 65535), with restriction placed by most storage engines
	- Storage = length + 1 or 2 bytes
	- No padding on storage / trimming on retrieval
	
	BLOBS
	- Variable length
	
		Type		Max Size		Storage
		TINYBLOB	255				length + 1 byte
		BLOB		65535			length + 2 bytes
		MEDIUMBLOB	16777215		length + 3 bytes
		LONGBLOB	4294967295		length + 4 bytes
		
	- Binary string ASCII will have A before a (sorting, comparison)
		
	ENUM
	- Defines a set of allowed values
	- e.g. ENUM('Y', 'N')
	- Stored as an integer, 1 through n where n is number of values
	- Up to 65535 members / elements
	- Storage = 1 byte each for upto 255, then 2 bytes each
	- 0 represents and illegal value and will be retrieved as an empty string
	- Stored 1 indexed key in DB
	- Strict MySQl mode will cause errors for illegal ENUM values
	- Inserts and select can use internal values
	
	SET
	- Same general idea as ENUM except a value can consist of multiple members
	- Stored using a bitmap
	- Maximum 64 members
	- Storage
		Members		Storage
		8			1
		16			2
		24			3
		32			4
		64			8
	- Illegal values are ignored, legal values in list with illegal values works
	- In strict mode illegal values give errors
	- Inserts and select can use internal values

=========================================================================================================
TEMPORAL TYPES

	- For times and dates
	
	Type		Storage			Range
	DATE		3 bytes			1000-01-01 -> 9999-12-31
	TIME		3 bytes			-838:59:59 -> 838:59:59
	DATETIME	8 bytes			1000-01-01 00:00:00 -> 9999-12-31 23:59:59
	TIMESTAMP	4 bytes			1970-01-01 -> mid-year 2037
	YEAR		1 byte			1901-2155 for YEAR(4) the default, 1970-2069 for YEAR(2) - (n) is display length
	
	- Illegal values will be stored as all zeros in same format
	- Values need to respect MySQL ISO (8601) date format
	- Leading zeros on month / day / time may be omitted
	- Can use a delimiter other than '-' such as '/'
	- Two digit years allowed
		- 70-99 -> 1970-1999
		- 00-69 -> 2000-2069
		
	TIMESTAMP
	- Stored as number of seconds since 01-01-1970
	- Displayed in same format as DATETIME
	- Converts to from / to local time zone on storage / retrieval respectively
	
	DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP
	- Used in column definition will set current timestamp on row insertion or update respectively
	- If neither are used when creating a table, both are assigned to first timestamp column in table
	- Pre 4.1 can't only use one, both will be applied automatically
	- Cannot use one with one column and the other with another column within the same table definition
		-> Can get aroundby using DEFAULT 0 on one column
	- Timestamps are non-null by default, setting NULL / 0 will give CURRENT_TIMESTAMP
		-> Break behaviour by setting column as NULL
	
	

=========================================================================================================
DATE FORMAT SPECIFIERS

		%a	Abbreviated weekday name (Sun..Sat)
		%b	Abbreviated month name (Jan..Dec)
		%c	Month, numeric (0..12)
		%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
		%d	Day of the month, numeric (00..31)
		%e	Day of the month, numeric (0..31)
		%f	Microseconds (000000..999999)
		%H	Hour (00..23)
		%h	Hour (01..12)
		%I	Hour (01..12)
		%i	Minutes, numeric (00..59)
		%j	Day of year (001..366)
		%k	Hour (0..23)
		%l	Hour (1..12)
		%M	Month name (January..December)
		%m	Month, numeric (00..12)
		%p	AM or PM
		%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
		%S	Seconds (00..59)
		%s	Seconds (00..59)
		%T	Time, 24-hour (hh:mm:ss)
		%U	Week (00..53), where Sunday is the first day of the week
		%u	Week (00..53), where Monday is the first day of the week
		%V	Week (01..53), where Sunday is the first day of the week; used with %X
		%v	Week (01..53), where Monday is the first day of the week; used with %x
		%W	Weekday name (Sunday..Saturday)
		%w	Day of the week (0=Sunday..6=Saturday)
		%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
		%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
		%Y	Year, numeric, four digits
		%y	Year, numeric (two digits)
		%%	A literal “%” character
		%x	x, for any “x” not listed above

=========================================================================================================
TIMEZONES

	- UTC = Coordinated Universal Time (Equiv of GMT)
	- Time Zone Formats
		- Signed our/minute offset e.g. +hh:mm / -hh:mm
		- Named timezone e.g. US/Eastern, translated by server. Timezones stored in table in the mysql database
		- SYSTEM time zone, retrieved from server host. Used by server as default timezone when setup
	- Time zone settings
		- time_zone, server maintains global and session versions copied on connect
		- Default = SYSTEM
		- SELECT @@global.time_zone, @@session.time_zone (for current global and connection session values)
		- SET time_zone = '+00:00' (Sets session time_zone value)
		- Affects NOW() result
		- CONVERT_TZ('2012-04-03 11:12:45', '+01:00', '+-03:00'); //Converts from one timezone to another
		
=========================================================================================================
HANDLING MISSING OR INVALID DATA

	- MySQL is more 'forgiving' than other databases
	- Conversion can occur during INSERT/UPDATE/REPLACE/LOAD DATA INFILE/ALTER TABLE/DEFAULT
	- Input restictions can be imposed using sql_mode, to prevent entry of illegal values
		- '' (default)
		- STRICT_TRANS_TABLES //Abort + Error for illegal values in transactional tables, non-trans tables will do the same if single or first in multi (otherwise non-strict behaviour)
		- STRICT_ALL_TABLES //Abort + Error for illegal values in all tables, can cause partial updates in multiple row queries
		- TRADITIONAL //Strict mode + extra restictions below (and any future ones)
		Additional restiction on top of strict
			- ERROR_FOR_DIVISION_BY_ZERO //Generate error if /0 in statement, in non-strict results in NULL + warning
			- NO_ZERO_DATE / NO_ZERO_IN_DATE //Causes errors on '0000-00-00' or '2013-00-01'
			- ALLOW_INVALID_DATES //Allows all dates as long as day 1-31 and month 1-12 in MySQL 5+
		More
			- ANSI_QUOTES //allow double quotes in identifiers
			- IGNORE_SPACE //Allow spaces between function names and parenthesis, can cause parsing issues
			- PIPES_AS_CONCAT //Swapps || from OR to concat
	- Possible illegal values
		- Out of range
		- Too long
		- Null in NOT NULL
		- Invalid ENUM and SET values
	- Illegal values, non-strict (generate warnings, show with SHOW WARNINGS;)
		- Automatic conversion of strings to numbers e.g. '43' -> 43, '43x' -> 43 + Warning
		- Negative value in UNSIGNED COLUMN results in 0
		- Rounding to boundary of type
		- Strings will be truncated (trimming does not generate error)
		- Invalid ENUM gives empty string
		- Invalid SET gives valid members only
		- If conversion between types not possible than implicit default o new type used
		- If altering column to NOT NULL, NULL values will be set to implicit default
		- Null to NOT NULL gives error if single line query, implicit defaults if multiple
		- In MySQL 5+ invalid dates (e.g. 31 Feb) set to zero value + warning or error in strict
	- Illegal values, strict mode
	- No value
		- Gives DEFAULT if one set
		- If column has no DEFAULT and is NULL than DEFAULT NULL added to definition
		- non-strict with no DEFAULT, implicit default is used for data type
		- Strict
			- Transactional, fail and rollback and error
			- Non-transactional, fail and error, may be partial update
			
=========================================================================================================
IDENTIFIERS

	- Unquoted
		- Alphanumeric characters, _ and $
		- Can start with any legal character, best no to use confusing formats e.g. 1e3, 0x3
		- Cannot exist entirely with digits
	- Quoted
		- Can contain any character, except bytes with numeric value of 0 and 255 (except .m /, \ in table + database names)
		- Can consist entirely of digits
		- Quote using backticks
		- Can use double quotes if ANSI_QUOTES sql mode enabled
	- Aliases
		- Can be quotes with backticks, single quote or double quote
		- Any character but should be quotes if using a reserver word, special characters or consists entirely of digits
		- Double quote to use as literal
	- Case sensitivity
		- Databases + Tables
			- Depends on the operating system, databases and tables are stored in files, if files are case senitive then so are database / table names
			- Alias affected by lower_case_table_names system variable, 1 or 2 gives case insensitive in UNIX like systems. Set before making databases and tables
			- Must be consistent in a statement
			- Generally Windows = case-insensitive, Unix is case-sensitive
		- Column, column aliases, index, stored routine and trigger identifiers are not case sensitive
	- Qualified Names
		- Removes ambiguity
		- Tables, stored routines can be qualified with a database e.g. database.table
		- Columns and triggers can be qualified with a table e.g. table.column
		- Combined database and table qualification for column gives e.g. database.table.column
		- Quoted identifiers seperated by dot e.g. 'database' . 'table'
	- Reserverd words
	    - Function names and sytax can collide with identifiers
		- To use quote with backtick (or double quote if ANSI_QUOTES mode)
		- Must be used as quotes in subsequent queries not just CREATE TABLE statement
		- For aliases use backtick, quote or double quote for reserved words
		- Reserved words are not case sensitive
		
=========================================================================================================
DATABASES

	- Consist of tables and related data-manipulation objects
	- Stored in "data directory"
		- Server represents each database using a sub-directoyr of called a "database directory"
		- Database directory name == database name
		- Tables and other database objects are stored in the database directories
		- Database directory can be empty
		- Database directory has default character set and collation
			- Stored in db.opt in the database directory
		- Databases cannot be nested
		- Number of databases on server can be limited by file system restrictions
	- Databases cannot be renamed
	- Database == Schema
	
	INFORMATION_SCHEMA.SCHEMATA contains database meta-data
		- Can select particular database using 'WHERE SCHEMA_NAME = "db_name"
	- mysql database contains grant information
	
=========================================================================================================
TABLES (AND STORAGE ENGINES)

	- Table can be a "base table" or a "view"
	- Table consists of rows and columns with a minimum of 1 column and no rows
	- Stored as one or more files in database directory
		- Format file = table_name.frm, containing the structure of the table
		- Storage engine creates and manages data file(s) for table
	- Each can be stored with a different sotrage engine
	- Server imposes no restrictions on number of tables, but storage engines and filesystem might
		- Limit on number of files in directory
		- OS effieciency in handling large numbers of files in a diretory
		- Out of disk space
	- Storage engines generally place (high) limits on maz size of tables (may be limited by max filesize / disk space)
	- Can change storage engine or OS to enable larger tables
	- Creates in default database if not qualified
	- Column names must be unique and are case insensitive
		
	- MyISAM (storage engine)
		- 1 format file = table_name.frm
		- 1 index file = table_name.MYI
		- 1 data file = table_name.MYD
		- Can get around yoo large tables by breakinging and using MERGE tables
		
	- InnoDB (storage engine)
		- 1 format file = tbale_name.frm
		- data and indexes stored in InnoDB shared tablespace (shared with other databases on the server)
		- Max 2 billion tables
		- Higher table size capacaty than MyISAM, as large as shared tablespace
		
	- MEMORY (storage engine)
		- Does not use any files on disk
		- Data lost (but definitions kept) when server restarted
		
	Available Engines
		- By default: MyISAM, MERGE, MEMORY
		- With binary distribution: InnoDB
		- More with MySQL Max binary distrobutions
	
	- If storage engine for table not specified then storage_engine variable used (set as MyISAM prebuilt)
	- Can alter storage_engine at runtime
	- If storage engine specified legal but missing, then default + warning
	
	- Tables can be defined, made from result set or from another table definition (see SQL)
		- using LIKE to use existing definition better than SELECT as will retain indexes and A_I, so use LIKE with 0 WHERE clause
		- Never copied
			- DATA DIRECTORY and INDEX DIRECTORY options for MyISAM tables
			- Foreign Keys
			
	- Temporary tables
		- Only appear to client who created table, does not collide with other clients
		- Dropped when client disconnects
		- Can share name with normal table but will make normal table hidden to current session
		- Cannot be renamed with RENAME TABLE (must use ALTER TABLE)
		
	- Table metadata is held in INFORMATION_SCHEMA.TABLES (e.g. can use TABLE_SCHEMA and TABLE_NAME in where clause
		
=========================================================================================================
INDEXES

	- Reduce lookup time on column (especially in large tables)
	- Can force unique values
	- Index types
		- Primary key, unique, non-null, only one per table
		- Unique, all non-null values are unique but can contain NULLs
		- Index
		- FULLTEXT, for text searching
		- SPATIAL for spatial data types
		- All indexes can be composite using more than one column from the table
	- Indexes can be added at (as part of or following column definitions, or after table creation (see SQL)
	- Table can have multiple indexes
	- PRIMARY KEY often coupled with AUTO_INCREMENT
	- PRIMARY KEY makes column NOT NULL by default (even if NULL in CREATE statement)
	- PRIMARY KEY same as UNIQUE on non-null column, important as allows unique id of row
	- All but PRIMARY keys can be named (use seperate clause in CREATE statement)
	- Index names must be unique
	- If no name provided, then name based on first column of index unless PRIMARY KEY then PRIMARY is used
	- Can add USING to index to specifiy algorithm for indexing
		- Only works for MEMORY storage engine currently
		- Use HASH for unique index, BTREE for non-unique
	- Index metadata is in INFORMATION_SCHEMA.STATISTICS (e.g. can use TABLE_SCHEMA, TABLE_NAME, INDEX_NAME in where clause)
	
=========================================================================================================
EXPRESSIONS
	- Consist of numbers, strings, dates, times, NULLs, references to table columns and function calls
	- Can use standard mathematical operators, +, -, /, *, >, <, >=, <=, =, <>, !=
	- Divide by zero returns NULL
	- AND and OR (||) and parenthesis can be used to combine clauses, also BETWEEN value AND value
	- PIPES_AS_CONCAT changes behaciour of ||
	- AND has higher precedence then OR
	- Numbers
		- Numbers can be exact or approx in exponent form e.g. 3E5
		- Numbers are represented by integers or DECIMAL.
		- Integer operations have 64-bit precision
		- Decimal operations have 64 decimal place precision with 30 max scale
	- Strings
		- Strings and numbers in an expression will lead to strings being converted
		- Strings use single or double quotes (double quotes cannot be used if ANSI_QUOTES enabled)
		- AND and OR with strings causes numeric conversion
		- Non-binary string comparison with different character sets normally results in an error
		- Non-binary string comparison effected by collation, case or accent may or may not affect
		- Binary strings compared on value
		- Literal text character_set_connection and collation_connection defaults are latin1 and latin1_swedish_ci
		- Can use BINARY before literal string to treat as binary string
	- value LIKE pattern
		- % represents zero or more characters in pattern
		- _ represents one character in pattern
		- _% represnts a string of at least one character
		- Above can be excaped with a backslash
		- Evaluates to NULL if value or pattern are NULL
		- 'same_string' LIKE 'same_string' will cause match
		- column1 LIKE column1 will cause match
		- FUNCTION() LIKE FUNCTION() will cause match except for RAND()
		- Non-binary comparison if both non-binary, else binary
		- Use NOT LIKE to invert
		- Add ESCAPE 'char' to change escape character
	- INTERVAL number unit
		- Units: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR
		- Used for arthmetic with temporal values
	- NULL
		- NULL same as null
		- Using NULL in expressions will result in NULL, e.g. 3 + NULL = NULL
		- NULL = NULL results in NULL (cannot be compared)
		- Use IS NULL and IS NOT NULL to check for NULLs
		- Use <=> to treat NULL as a value e.g. NULL <=> NULL results in 1
		- NULL values sort together, group together and are not distinct
		- Expressions which cannot be evaluated give NULL
	- expression IN(values) 
		- checks if expression matches one of a set
		- values can be expressions which result in a value
		- values can be of different types
		- NULL IN(values) results in NULL
	- variables
		- use @name
		- SET @name
=========================================================================================================
ORDERING
	- Numeric columns in ascending order by default
	- Temporal column sorts in ascending order by default
	- Non-binary strings sort according to collation (so can force ci or cs or bin (by value) with COLLATE)
	- Binary strings sort on numeric values of bytes
	- ENUM and SET ordered in internal values, can use CAST to convert to lexical sorting)
	- NULL at start for ASC, at end for DESC
=========================================================================================================
PRIVILEGES
	- INSERT needs INSERT
	- UPDATE needs UPDATE
	- DELETE needs DELETE
	- REPLACE needs INSERT + DELETE
	- TRUNCATE TABLE needs DELETE
	- CREATE VIEW needs CREATE VIEW + Suffiecient access to refered tables
	- OR REPLACE / ALTER VIEW needs DROP VIEW
	- LOAD DATA INFILE needs INSERT (and DELETE for REPLACE) and FILE if using file on the server
	- INTO OUTFILE needs FILE
=========================================================================================================
TRANSACTIONS
	- Sequentail group of queries, either all succeed or all fail, ensures atomicity, consistency, isolation and durability
	- WHen autocommit is set to one then all queries are said to be transactons and are committed, 0 they are not without a COMMIT commands
	- InnoDB supports transactions
	- When autocommit = 0 then InnoDB (and other transactional) tables will always be transactional, no changes unles COMMIT
	- completion_type affects CHAIN and RELEASE
	- Transactions which are rolled back are not logged
	- Rollback on non-transaction tables (e.g. MyISAM) causes warnings
        - The following commands will implicitly commit a transaction in progress
            - ALTER TABLE, BEGIN, CREATE, DROP, RENAME TABLE, SET AUTOCOMMIT = 1, START TRANSACTION, TRUNCATE, LOCK TABLES, UNLOCK TABLES
=========================================================================================================
SUBQUERIES
	- Subqueries can be used to replace values in an outer query / compare
	- Scalar - return a single value
		- Simply surround query in brackets and replace value in query
		- Cannot be used where literal value is required e.g. as an argument in a LIMIT clause
		- Error if more than one value returned generally
	- Row - return a single row
		- compare bracketed terms with rows (column count must match)
		- e.g. WHERE [ROW]value1, value2) = (SELECT col1, col2...)
		- Equality only, no modifiers
	- Column - return a single column
		- Can use the ALL, ANY / SOME qualifiers to match all, one or a few of set, error if more than one column
		- ANY / SOME used to make queries more readable
		- Can also use IN instead of = ANY
	- Table - return a set with one or more rows and one or more columns
		- Use after CREATE TABLE table_name
	- Joins are often more efficient, but subs are more readable
	- In correlated queries (where inside query references values from outside, may need to qualify names
	- Can be used in FROM clause with an alias then use alias in SELECT
	- Cannot select in sub if updating in outer query
=========================================================================================================
EXPLAIN / Optimisation
        - When using explain type will hint at speed of lookup (in order from fastest to slowest)
            - system (Single-row table) 
            - const (Constant specified in query against unique column) 
            - eq_ref (Single row to be retrieved from table, against a unique join-column)
            - ref (Like eq_ref but against a non-unique column)
            - range (Set of rows from non-unique key) 
            - index (Full index scan)
            - all (Full Table Scan)
        - Try and ensure PRIMARY keys are used for lookups and indexes are used for joins
        - Try not to use BETWEEN to compare columns
        - Try to use indexed columns in where queries
        - Try not to create too many indexed on columns in the same table
        - Try no to create indexed on columns with low cardinality
        - Try to check for INNER JOIN in an inefficient order (e.g. want smallest common denominator first), use STRAIGHT_JOIN 
instead
        - Try to avoid GROUP BY queries with no sort, use ORDER BY NULL
        - Try to avoid WHERE ColName IS NULL (use values instead)
        - If wanting to use BETWEEN with temporal values, CAST to numerics first and then convert back
        - Don't match a GROUP BY with a ORDER BY with same column lists as this will double sort

=========================================================================================================
SLOW QUERIES

Queries which take longer than a defined n seconds can be logged seperately (and additionally queries with no indexed search column), set this in my.cnf (long_query_time can be set for session in client):

log_slow_queries = <filename>
long_query_time = <nSeconds>
log_queries_not_using_index	 	 	 	 	 	 

=========================================================================================================
USERS

CREATE USER <username>
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost' WITH GRANT OPTION;

=====
More useful stuff
select * from INFORMATION_SCHEMA.INNODB_TRX where trx_id="751CBF343" \G
SHOW ENGINE INNODB STATUS

==========
(EXAMPLE) CHEAT QUERY FOR TABLE STATS (SIZE ETC)

SELECT 
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='dbComplaints'
        AND table_name='tblComplaintHistory'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;