Commands are entered into a multi-line buffer, terminated with the delimiter character ( default ;) or
\g aka go and and then sent to the server.

Commands are saved in history, which is retained across invocations of MySQL, and can be recalled see readline.

AUTOCOMMIT is enabled by default.
MyISAM storage engine (default) does not support transactions!
rollback will report: Warning (Code 1196): Some non-transactional changed tables couldn't be rolled back

SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
For transaction-safe tables SET AUTOCOMMIT=0;commit(MySQL) will permit ROLLBACK.

source \. file take commands from file
tee \T to_outfile Append output [outfile]
in addition to STDOUT
notee \t stop TEEing
edit \e use $EDITOR on current buffer  print \p Print current command
use \u database connect \r Reconnect
optional args db host
go \g Send command
to mysql server
clear \c   prompt \R Change prompt
help \h keyword   quit \q  
delimiter\d d Set query delimiter
default is ;
rehash \# Rebuild completion hash
ego \Gdisplay result vertically (no ; needed)
select *  from aicvCompanies \G
*…** 1. row ***…**
        num: 0
       name: Geico
      state: NJ
 lastupdate: 2013-01-14 18:31:02

+-----+-------+-------+---------------------+
| num | name  | state | lastupdate          |
+-----+-------+-------+---------------------+
|   0 | Geico | NJ    | 2013-01-14 18:31:02 |
+-----+-------+-------+---------------------+

status \s 2/19/13 @ midphase
 
/usr/bin/mysql  Ver 14.14 Distrib 5.1.66, 
        for unknown-linux-gnu (x86_64) using readline 5.1

Connection id:      4618872
Current database:   rexxxer1_birding
Current user:       rexxxer1_dgexxxn@localhost
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.1.66-cll MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         6 days 19 hours 28 min 35 sec

Threads: 7  Questions: 92705383  Slow queries: 330  Opens: 2956038  
    Flush tables: 25  
    Open tables: 300  Queries per second avg: 157.524/usr/bin/mysql  

Ver 14.14 Distrib 5.1.61, for unknown-linux-gnu (x86_64) 
                            using readline 5.1 
was
/usr/bin/mysql  Ver 14.7 Distrib 4.1.21, 
                            for pc-linux-gnu (i686) 
                            using readline 4.3

Connection id:          9690869
… 
Server version:         4.1.21-standard
Uptime:                 61 days 5 hours 20 min 10 sec 
Threads: 4  Questions: 375362065  Slow queries: 31761
     Opens: 22592926  Flush tables: 1  Open tables: 64
     Queries per second avg: 70.962

The last 50 previous lines of the buffer can be recalled into current buffer (even across invocations of MySQL).
[^] or [F5]scroll to previous buffer line (repeatedly)
[F7] Present prior buffer lines for insertion into current buffer

N.B. If line inserted contains delimiter, buffer is sent, sounds pretty dangerous to me.
[F9] copy prior buffer line nn into current buffer.
[F2] copy buffer to char
[F4] delete buffer to char

Data Manipulation

Clauses must be presented in the order shown

INSERT, REPLACE, UPDATE, DELETE   Merge (Oracle extension is not available in MySQL)

SELECT [ALL|DISTINCT|DISTINCTROW]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    column, table.column, select_expr, ...
          [AS alias ]
            [WHERE where_condition]
              [GROUP BY [column | expr | position] [ASC | DESC], ... [WITH ROLLUP]]
                [HAVING where_condition]
                  [ORDER BY [column | expr | position] [ASC | DESC], ...]
[LIMIT [[offset,] row_count | row_count OFFSET offset]]
[PROCEDURE procedure_name(argument_list)]
[INTO [OUTFILE| DUMPFILE] 'file_name' export_options
| @var_name [, @var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]]>]

SELECT can retrieve rows computed without reference to any table.

where_condition: column comparison := IS [NOT] NULL | ISNULL() |
= | <=>
  | !=     v
< | > | <= | >=     v
GREATEST(v,v …) | LEAST(v,v …)
[NOT]BETWEEN min AND max
(inclusive)
[NOT] IN ( v, v …) |
     example: select dakey,company from aicv where dakey in
               (select dakey from aicvISO_Vehicles where CollDeductible=0);

STRCMP() |[NOT] LIKE "xxx%" | [NOT] LIKE "xxx_xx"
INTERVAL(testVar, max0, max1, max2, …)
index of the argument that is less than the first testvar
COALESCE(v,v …) Return the first non-NULL argument

GROUP BY (Aggregate) Functions ignore null
Using a group function in a statement without a GROUP BY clause, it is equivalent to grouping on all rows.
GROUP BY and HAVING with Hidden Columns

GROUP BY  
    COUNT( expr) count of a number of values, ignores NULL
    COUNT(DISTINCT expr) count of a number of different values, ignores NULL
    COUNT(*) number of rows returned
    GROUP_CONCAT(  [DISTINCT] expr [,expr ...]                concatenated string
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
select day,location,sum(Pileated_Woodpecker) from birdingObservations 
                where  Pileated_Woodpecker >0 group by year(day),location  with rollup;
+------------+------------------------------------------+-
 day         location
+------------+------------------------------------------
 2007-03-11  Essex Fells Water Co                         1
 2007-04-11  Great Piece Meadows                          1
 2007-10-01  Hatfield swamp:Twin Rocks                    1
 2007-09-29  South Mountain Reservation                   1
 2007-09-29  NULL                                         4  rollup sum

Aggregate functions ( HAVING can refer to aggregate functions, which the WHERE cannot):
SUM( expr) MIN( expr) AVG( expr) MAX( expr)
STDDEV(), STD(),
STDDEV_POP()
population standard deviation
VAR_POP( expr), VARIANCE( expr) population standard variance
VAR_SAMP( expr) sample variance STDDEV_SAMP( expr) sample standard deviation i.e Square Root of VAR_SAMP()
BIT_AND( expr) bitwise and ; BIT_OR( expr) BIT_XOR( expr) (use bigint)

INSERT [INTO] table SET column=[expr | DEFAULT], …
OR
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name
 [(column,…)]
 [ VALUES | VALUE] ([expr | DEFAULT]…),(…),…
[ ON DUPLICATE KEY UPDATE column=expr [, col_name=expr]]

OR

INSERT … [INTO] tbl_name [(column,…)]
                                SELECT …

ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b);

VALUES(column,…)


REPLACE is DELETE then INSERTMySQL extension

REPLACE [INTO] table [(column,…)] VALUE[S] ({expr | DEFAULT},…),(…),…

OR

REPLACE [INTO] table SET column={expr | DEFAULT}, …

OR

REPLACE [INTO] table [(column,…)] SELECT …

REPLACE [INTO] table [(column,…)]


UPDATE [LOW_PRIORITY] [IGNORE] table SET column1 = [expr1|DEFAULT] [, column2 = [expr2|DEFAULT]]

  1. [WHERE where_condition]
  2. [ORDER BY ...]
  3. [LIMIT row_count]
    i.e if multiple rows, match the WHERE and LIMIT rows, already have the SET value no rows will be effected.


DELETE FROM table WHERE column = expr LIMIT 1

UNION JOIN

RESET |SET

DO

SHOW

create

CACHE INDEX
EXPLAIN
PURGE FLUSH KILL
LOAD DATA LOAD INDEX


Functions:

Date and Time

Dates must be in year-month-day order.

SELECT CURDATE(); -> 2008-06-13
SELECT CURDATE() + 0; -> 20080613

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

'HH:MM:SS' or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. 

SELECT CURTIME(); -> 23:50:26
SELECT CURTIME() + 0; -> 235026
SELECT CURTIME() + 0.; -> 235026
SELECT CURTIME() + .0; -> 235026.0


SELECT NOW(); -> 2016-05-10 10:22:26
SELECT NOW() +0 -> 20160510102156
Set a shell variable to last month
 export lastMonth=`echo "select   CURDATE() - INTERVAL 3 month;" |mysql |tail -n1  `
Two-digit years 00-69 are converted to 2000-2069.
2009-11-31 use SERVERMODE!!

ALLOW_INVALID_DATES my.cnf sql-mode="no ALLOW_INVALID_DATES" or
Following NG:
mysql --sql-mode="NO ALLOW_INVALID_DATES" …
SET SESSION sql_mode='ALLOW_INVALID_DATES'

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

TIME Formats accepted: mm and ss <= 59
text:
[d] [h]h:[m]m:[s]s.f
[d] [h]h:[m]m:[s]S
[d] [h]h:[m]m
[d] [h]h
[s]S.
text or integer:
hhmmss.f      ex: 159 is 1 minute, 59 seconds

mmss
ss

Examples:
as integer: 1856 or as text: '18:56'

                                        hour 01..12
                 NO space      24H 0min  |   hour 1..12
                  ↓            | 01  |  /
Select Time_Format(curtime(), '%H:%i %h %I %l %f');
                               10:17 10 10 10 000000  

time is '-838:59:59' to '838:59:59' allowing for elapsed, timeBefore and timeafter as much as 34 days

Select DATE_FORMAT( min(day), "%b %y")
Feb 06

(arranged by function, not alphabetically) Many not like unix date

%m  month, numeric 00..12;  %c  0..12;             %M  January..December ;  %b  Jan..Dec
(how to blank padded?? ie ' 1, 2, 3'  vs '01,02,03' ?? )
%d  00..31;                 %e  0..31;             %D  Day suffixed (0th, 1st, 2nd, 3rd, …)
%y YY;                      %Y  CCYY               %C  cc  century 00..99 
%F    %Y-%m-%d 

%h 01..12   %I Hour 01..12; %p AM or PM locale's upper case AM or PM (blank in many locales)    %P locale's lower case am or pm  
%H 00..23;  %k 0..23
%l 1..12  (NB Not good with SUBTIME as zero hours will display as 12!) 
%i  Minutes 00..59
%S  Seconds 00..60; %s  00..60    The 60 is necessary to accommodate a leap second 
%f  Microseconds (000000..999999) always 00000

%r  Time, 12-hour (hh:mm:ss .M ; %T  24-hour (hh:mm:ss)
                   10:18:06 AM                10:18:16
%w  Day of the week (0=Sunday..6=Saturday); %W (Sunday..Saturday) %a (Sun..Sat)
%j  Day of year (001..366)
%U  Week (00..53), where Sunday is the first day of the week                 %u  Week (00..53), where Monday 
%V  Week (01..53), where Sunday is the first day of the week; used with %X   %v  Week (01..53), where Monday …
%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

%z    -zzzz      RFC-822 numeric timezone (-0500)   ;
%Z    time zone (e.g., EDT) nothing if  no  time  zone  is  determinable 
only for  TIMESTAMP values, now(), currtime()!
    see time zone support 
as of 6/18/13  on slmp-550-13.slc.westdc.net 
SET time_zone ='US/Eastern'; select now();
  Returns: ERROR 1298 (HY000): Unknown or incorrect time zone: 'US/Eastern' and the date/time in eastern -2! 
%s    seconds since 00:00:00 1970-01-01 UTC
 literals:  %n newline    ;  %% percent; %t horizontal tab 
 
nicer:     day > (current_date  - INTERVAL 20 day) 

ADDDATE(e1,e2)  Add intervals to a date
ADDTIME(e1,e2)    update racers set end= addtime(start, "1:51:44" ) where name like "Joe %";
DATE_ADD(e1,e2)  Add time values (intervals) to a date value     DATEDIFF()  Subtract two dates
DATE_SUB(d, INTERVAL n units),                                   SUBDATE(d,days)  Subtract two dates

FROM_DAYS() a day number to a date
MAKEDATE(year,dayofyear)
MAKETIME(hour,minute,second) 
SUBTIME('2007-12-31 23:59:59.999999','02:00:00.999998')   


PERIOD_ADD(yymm,months)    Add a period to a year-month     PERIOD_DIFF()   number of months between periods

STR_TO_DATE()   string to a date

GET_FORMAT()    date format string
TIME_FORMAT()   Format as time ( no date info, use DATE_FORMAT
DATE_FORMAT()   
SEC_TO_TIME()   seconds to 'HH:MM:SS' format

TIME_TO_SEC()   to seconds
TIMEDIFF()      Subtract time

TIMESTAMP (dt2 )         date or datetime expression;
TIMESTAMP (dt1, dt2 )    sum of the arguments
TIMESTAMPADD(unit,i, dt) Add an interval to a datetime expression TIMESTAMPDIFF(unit, dt1,dt2)   Subtract an interval 

TO_DAYS()   date argument converted to days

EXTRACT     Extract part of a date
DATE()      Extract the date part of a date or datetime expression
DAY()     DAYOFMONTH() 0-31     julian use: DAYOFYEAR() 1-366
HOUR()    TIME()      MINUTE()    SECOND()  0-59    MICROSECOND()   
MONTH()   MONTHNAME() DAYOFWEEK() WEEKDAY() weekday index ; DAYNAME() 
QUARTER() YEAR()  WEEK()  WEEKOFYEAR() 0-53   YEARWEEK()  year and week
LAST_DAY    of the month 

UNIX_TIMESTAMP()    
FROM_UNIXTIME()     Format UNIX timestamp as a date

SYSDATE()           time at which the function executes
CURDATE()           CURRENT_DATE() CURRENT_TIMESTAMP() Synonyms for NOW()
CURTIME()           CURRENT_TIME()
now()
LOCALTIME()         LOCALTIMESTAMP()
UTC_DATE()          current UTC date;  UTC_TIME()
UTC_TIMESTAMP()     current UTC date and time
CONVERT_TZ()        from one timezone to another
days=DATEDIFF(expr1,expr2)
DATE_ADD(date,INTERVAL expr unit) aka ADDDATE(
SELECT DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND); -> '2101-01-01 00:01:00'

Also permitted (no DATE_ADD() SELECT '2008-12-31 23:59:59' + INTERVAL 3 SECOND; -> '2009-01-01 00:00:00'
DATE_SUB(date,INTERVAL expr unit)

MICROSECOND , SECOND , MINUTE, HOUR , DAY , WEEK, MONTH , QUARTER ,
YEAR , SECOND_MICROSECOND , MINUTE_MICROSECOND, MINUTE_SECOND ,
HOUR_MICROSECOND , HOUR_SECOND , HOUR_MINUTE ,
DAY_MICROSECOND , DAY_SECOND , DAY_MINUTE 'DAYS HOURS:MINUTES', DAY_HOUR ,
YEAR_MONTH

ADDTIME(e1,e2)
    SELECT ADDTIME ('2007-12-31 23:59:59.999999', '1 12:7:8.000002'); -> '2008-01-02 12:07:08.000001'
   SELECT TIMEDIFF('2007-12-29 22:57:54',       '2007-12-31 23:59:59');-> -49:02:05

subtract Select dateDiff(MONTH,'2003-02-01','2003-05-01');
                                            3
add Select '2008-03-28' + interval 2 hour

date and time functions

Information
Numeric
String

Encryption
Miscellaneous

CHAR BYTE
TRUE FALSE
DUAL

Data Types

DATE
TIME
DATETIME
YEAR

TIMESTAMP

ENUM
DECIMAL(prec,scale)
  (fixed Point) ex:DECIMAL(5,2) ok for 999.99
  (aka NUMERIC)
FLOAT
DOUBLE
INT
TinyINT
SmallINT
MediumINT
BigINT

attribute:   
AUTO_INCREMENT

BOOLEAN

CHAR
fixed length 0‑255.

VARCHAR
0‑32766 

TEXT
TinyTEXT
MediumTEXT
LongTEXT
permitted

BLOB
TinyBLOB 

MediumBLOB
LongBLOB 

BINARY
VARBINARY
BIT

Data Definition

ALTER [IGNORE] TABLE table

ADD [COLUMN] column column_definition [FIRST | AFTER column ]
DESCRIBE table [column | wild] … ADD [COLUMN] (column column_definition,…)
… CHANGE [COLUMN] old_column new_column column_definition [FIRST|AFTER column]
… ALTER [COLUMN] column {SET DEFAULT literal | DROP DEFAULT}
… MODIFY [COLUMN] column column_definition [FIRST | AFTER column]
CREATE
ALTER … DROP [COLUMN] column

column_definitions …:
colname data_type [NOT NULL|NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY]|[PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [reference_definition] )

Declare columns to be NOT NULL saves time

ADD {INDEX|KEY} [index_name] [index_type] (index_column,…) [index_type]
ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_column,…) [index_type]
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_column,…) [index_type]
ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_column,…) [index_type]
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_column,…) reference_definition
DROP PRIMARY KEY
DROP FOREIGN KEY fk_symbol

DROP {INDEX|KEY} index_name

DISABLE KEYS | ENABLE KEYS
RENAME [TO] new_tbl_name
ORDER BY column [, column]
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
[DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
DISCARD TABLESPACE
TRUNCATE TABLE
IMPORT TABLESPACE

CREATE TABLE tablename table_options (

Change the value of the AUTO_INCREMENT counter to be used for new rows:
ALTER TABLE table AUTO_INCREMENT = value;

Inserting a NULL (as with an insert without specifying the column) into an indexed AUTO_INCREMENT column, sets it to the next sequence value, typically max(value)+1, sequences begin with 1.

To retrieve the value after inserting a row, use information function (without specifying a table ) select LAST_INSERT_ID() (from this connection.)
One AUTO_INCREMENT column per table, must be indexed, cannot have a DEFAULT and contains only positive values.

TABLE VIEW

GRANT      priv_type [(column_list)] [, priv_type [(column_list)]] …
     ON [object_type] priv_level
     TO user_specification [, user_specification]
     [REQUIRE {NONE | ssl_option [[AND] ssl_option] …}]
     [WITH with_option …]

priv_type column SELECT, INSERT, UPDATE
table CREATE, ALTER, DROP,
SELECT, DELETE, UPDATE, INSERT,
GRANT OPTION, INDEX, SHOW VIEW, TRIGGER, CREATE VIEW

database CREATE, DROP, EVENT, GRANT OPTION, LOCK TABLES
administrative
granted globally
CREATE TABLESPACE, CREATE USER, SHOW DATABASES,
FILE (LOAD DATA INFILE )
PROCESS, RELOAD,
SHUTDOWN, SUPER
REPLICATION CLIENT, REPLICATION SLAVE

ALL

object_type: TABLE | FUNCTION | PROCEDURE

priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name

user_specification: user [IDENTIFIED BY [PASSWORD] 'password']

grant alter, SELECT, DELETE, UPDATE, INSERT on rws.PCCraceApp to realger1_philly;

INDEX: CREATE| DROP
ALGORITHM
MERGE
TEMPTABLE WITH CHECK OPTION
RENAME Table


Function and Operator Reference

String Functions
FUNC(arg
No space permitted after (
CONCAT('abc','def',7, …) numerics converted to string CONCAT_WS('sep',str1,str2 …) with inserting separator
FORMAT(n, d) a number with d decimal places using #,###,###.##
INSTR(str, substr)) index of the first occurrence of substring LOCATE(substring, string[,posi]) position of the first occurrence of substring
POSITION() == LOCATE
LENGTH(str) of str in bytes
 
MATCH (col1,col2,…) AGAINST (expr IN BOOLEAN MODE)
        Perform full-text search + == and, - == not, or is implied.
                                        < more relevent, > less relevent. * suffix wildcard.…
LIKE Simple pattern matching:
"_" one character,
"%" any number of characters
NOT LIKE Negation of simple pattern matching
REGEXP Pattern matching using regular expressions RLIKE Synonym for REGEXP
NOT REGEXP Negation of REGEXP
 
REPEAT(str, count) Repeat str count times. Use to make "bar" chart
REPLACE(str, from_str,to_str) Replace occurrences of a specified string
INSERT() substring at the specified position up to the specified number of characters
 
LEFT(str, len) leftmost characters RIGHT(str, len) rightmost characters
LTRIM() Remove leading spaces RTRIM(str) Remove trailing spaces
                              TRIM() Remove leading and trailing spaces
LPAD(str, len, pad_str) left-padded with the specified string RPAD(str, len, pad_str)
adjust str to len by truncating or padding with padstr
 
REVERSE(str) Reverse the characters in str
SPACE(n) Returns n spaces
STRCMP(str1,str2) Compare two strings
MID(string, start_pos[,length])
SUBSTR(
SUBSTRING(
SUBSTRING_INDEX(string, delimiter, occur) returns left substr,
if occur is negative, scans from RIGHT and returns right substring
lower(str) aka LCASE(str) (Yes the function names can be upper or lower case!)
UCASE(str) aka UPPER(str)
.
SOUNDEX() Returns a soundex string SOUNDS LIKE Compare sounds
ELT(N,str1,str2,str3,…) Elment in LisT ;Returns string at index number
MAKE_SET() set of comma-separated strings that have the corresponding bit in bits set
EXPORT_SET(bits,on,off[,separator[,number_of_bits]]) string with ons for bits set in the value bits, and off for unset bit
mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
        -> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
        -> '0,1,1,0,0,0,0,0,0,0'

FIND_IN_SET() index position of arg1 within arg2
FIELD() index of the arg1 in the subsequent arguments
.
ASCII() numeric value of left-most character
ORD() Returns character code for leftmost character of the argument
CHAR() character for each integer passed
BIN() string representation of the argument
OCT() octal representation of a decimal number
HEX() of decimal or string value
UNHEX() character from hex
QUOTE() display in HEX orEscape xstr for use in an SQL statement
BIT_LENGTH() length of argument in bits
CHAR_LENGTH(),
CHARACTER_LENGTH()
number of characters in argument
OCTET_LENGTH() synonym for LENGTH()
 

ABS() absolute value
CEIL()
CEILING()smallest integer value not less than the argument
FLOOR() largest integer value not greater than the argument
ROUND() Round
TRUNCATE(x, d) Truncate x to specified number of decimal places.
0=> integer
negative numbers set low digits to zero, example TRUNCATE (122,-2) returns 100
RAND() random floating-point value
SIGN() sign
EXP(n) Raise ⅇ to the power of n
LN() natural logarithm; LOG10() base-10 log; LOG2() base-2 log; LOG() natural log
PI() value of pi
POW() POWER() raised to the specified power
SQRT() square root
 
DEGREES() radians to degrees
RADIANS() to radians
SIN() sine TAN() tangent COS() cosine COT() cotangent
ACOS() arc cosine ATAN() arc tangent ASIN() arc sine ATAN2(), ATAN() arc tangent of the two arguments
 
CONV() Convert numbers between different number bases
CRC32() Compute a cyclic redundancy check value
 
+ Addition
/ Division
DIV Integer division
% Modulo
MOD() Return the remainder
- Minus
* Times
- Change the sign of the argument

MySQL Control Flow
CASE value WHEN [compare_value] THEN result
         [WHEN [compare_value] THEN result … ] [ELSE result] END
CASE WHEN [condition] THEN result
    [WHEN [condition] THEN result … ]
[ELSE result] END
Example from Paddlers' Race:
CASE WHEN name2 is not null THEN ' ' ELSE 'XXXXX' END 'Waiver2',
IF(var, TrueVal, FalseVal) If/else construct
IF(IFNULL(name2,TRUE),"XXXXX"," ") "Waiver2",
IF(expr1,expr2,expr3) If expr1 is TRUE (i.e expr1 <> 0 and expr1 <> NULL) then returns expr2; otherwise it returns expr3.
IFNULL(expr1,expr2) If expr1 not NULL returns expr1.
If expr1 is NULL returns expr2 similar to Oracle's NVL (NullVaLue) function
NULLIF(expr1,expr2) If expr1 = expr2 is true, returns NULL
If expr1 <> returns expr1

The same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Administration

CREATE USER user IDENTIFIED BY PASSWORD 'password' GRANT SELECT UPDATE ON TABLE tbl_name
RENAME USER
REVOKE
SET PASSWORD

ANALYZE CHECK* CHECKSUM CONSTRAINT

FLUSH QUERY CACHE
REPAIR
BACKUP TABLE, RESTORE
LOAD_FILE(file) from server (requires file priviledge).

SHOW CREATE VIEW

Error codes N.B. Empty set i.e. no rows found is NOT an error!!

See My SQL admin for addtional details.


show variables like 'AUTO%';
set autocommit =off;

N.B. If the connection to the server is lost (perhaps due to inactivity)
and reconnected as indicated by:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6098700
autocommit is reset!

show variables like 'sql%'

perl interface.

$q ="insert …| SELECT …"
$execute = $connect->query($q);
 $ar=$execute->affectedrows($q);     $nr=$execute->numrows();         $nf=$execute->numfields(); 
keep fetching rows as long as there are rows to fetch.
 while(@results = $execute->fetchrow() ){ $r0=$results[0];  … } 

Geographic features

 GEOMETRY
 GEOMETRY HIERARCHY
 SPATIAL 
categories: Geometry constructors Geometry properties Geometry relations GeometryCollection properties LineString properties MBR Point properties Polygon properties WKB WKT
RESET SLAVE SHOW SLAVE STATUS START/stop SLAVE SET GLOBAL
SQL_SLAVE_SKIP_COUNTER

Transactions Topics:

HANDLER, ISOLATION, LOCK, SAVEPOINT, START TRANSACTION

Reserved words

Some of the ones you might be inclined to use for column names (especially nouns, some verbs used as nouns for example lock). If you really want to use them as column names, quote them.

There are more (see the link)

case character default div from key keys limit order check ignore option signal call grant group dec references condition interval purge lines load separator char constraint

verbs: alter analyze change collate continue convert create cross declare delete desc describe double drop exit explain fetch force get index insert iterate join kill leave lock loop match mod modifies natural not null numeric optimize outfile partition precision primary procedure range read write release rename repeat replace require resignal restrict return revoke select sensitive set show spatial specific starting table terminated trailing trigger true undo union unique unlock unsigned update usage use using values varying then to when where while with

adjectives: long real accessible float enclosed escaped exists dual each distinct decimal delayed both by having if for before between out left right on integer into int inner insensitive foreign cursor like linear optionally or false outer asc