Data Manipulation

INSERT,REPLACE, UPDATE, DELETE

Merge (an 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]
    select_expr, ...
          [FROM table_references
            [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	= | < | > | != | <= | >= | <=> †	value
		GREATEST() | IN {} | COALESCE() Return the first    non-NULL argument STRCMP() | LEAST() | INTERVAL() index of the argument     that is less than the first argument
		column IS [NOT] NULL | ISNULL()
		BETWEEN … AND …

GROUP BY (Aggregate) Functions
ignores nulls

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(DISTINCT) count of a number of different values COUNT() number of rows returned GROUP_CONCAT() concatenated string SUM() MIN() AVG() MAX() STD() population standard deviation STDDEV_POP() population standard deviation STDDEV_SAMP() sample standard deviation STDDEV() population standard deviation VAR_POP() population standard variance VAR_SAMP() sample variance VARIANCE() population standard variance BIT_AND() bitwise and ; BIT_OR() BIT_XOR()

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 INSERT← MySQL 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]] … [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

DELETE FROM table WHERE column = expr LIMIT 1

UNION JOIN

RESET |SET

DO

SHOW alter

create

CACHE INDEX
EXPLAIN
PURGE FLUSH KILL
LOAD DATA LOAD INDEX

• Bit
  
• Control flow
  
  
• Math

Date and Time

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.000000

 export lastMonth=`echo "select   CURDATE() - INTERVAL 1 month;" |mysql |tail -n1  `

                                         hour 01..12
                 NO space         24H 0min  |   hour 1..12
                  ↓                   | 01  |  /
Select Time_Format('100:00:00', '%H:%i %h %I %l');
                         '100: 100 04 04 4' 

 
%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

%h 01..12   %I Hour 01..12; %l 1..12  %p  AM or PM
%H 00..23;  %k 0..23
%i  Minutes 00..59
%S  Seconds 00..60; %s  00..60    The 60 is necessary to accommodate a leap second 
%f  Microseconds (000000..999999)

%r  Time, 12-hour (hh:mm:ss .M ; %T  24-hour (hh:mm:ss)

%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


%F    %Y-%m-%d 

%p    locale's upper case AM or PM (blank in many locales)    %P     locale's lower case am or pm  
%z    -zzzz      RFC-822 style numeric timezone (-0500)  
%Z    time zone (e.g., EDT) nothing if  no  time  zone  is  determinable 
%s    seconds since 00:00:00 1970-01-01 UTC
 literals:  %n newline    ;  %% percent; %t horizontal tab 

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()
LOCALTIME()         LOCALTIMESTAMP()
UTC_DATE()          current UTC date;  UTC_TIME()
UTC_TIMESTAMP()     current UTC date and time
CONVERT_TZ()        from one timezone to another

Information
Numeric
String

Encryption
Miscellaneous

CHAR BYTE
TRUE FALSE
DUAL

Column Types
AUTO_INCREMENT BINARY VARBINARY BIT	DATE TIME DATETIME TIMESTAMP YEAR	ENUM DECIMAL NUMERIC FLOAT DOUBLE	INT TinyINT SmallINT MediumINT BigINT BOOLEAN†	CHAR fixed length 0‑255.† VARCHAR 0‑32766  TEXT† TinyTEXT MediumTEXT LongTEXT permitted	BLOB TinyBLOB  MediumBLOB LongBLOB

Data Definition

ALTER [IGNORE] TABLE table

ADD [COLUMN] column column_definition [FIRST | AFTER column ] ADD [COLUMN] (column column_definition,...) 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 ALTER [COLUMN] column {SET DEFAULT literal | DROP DEFAULT} CHANGE [COLUMN] old_column new_column column_definition [FIRST|AFTER column] MODIFY [COLUMN] column column_definition [FIRST | AFTER column] DROP [COLUMN] column 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 table_options

Dev.MySQL/refMan

CREATE Declare columns to be NOT NULL saves time
	DATABASE
	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	table	CREATE, ALTER, DROP, SELECT, DELETE, UPDATE, INSERT, GRANT OPTION, INDEX, SHOW VIEW, TRIGGER, CREATE VIEW
	column	SELECT, INSERT, UPDATE
	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

Functions FUNC(arg No space permitted after (
CONCAT('abc','def',7, …) numeric argument is converted to string
CONCAT_WS(sep, …) concatenate with separator
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'
FIELD() index of the arg1 in the subsequent arguments
FIND_IN_SET() index position of arg1 within arg2
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
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
REGEXP Pattern matching using regular expressions
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
RLIKE Synonym for REGEXP
QUOTE(str) Escape str for use in an SQL statement
REPEAT(str,count) Repeat str count times
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
LENGTH(str) of str in bytes
LEFT(str,len) leftmost characters
LPAD() left-padded with the specified string
LTRIM() Remove leading spaces
TRIM() Remove leading and trailing spaces
RTRIM(str) Remove trailing spaces
RPAD(str,len,padstr) adjust str to len by truncating or padding with padstr
RIGHT(str,len) rightmost caracters
REVERSE(str) Reverse the characters in dtr
SPACE(n) Returns nspaces
STRCMP(str) Compare two strings
MID(str) substring starting from the specified position
SUBSTR(start,pos[,length])
SUBSTRING_INDEX(string, delimiter, occur) positive occur returns left substr, negative scans from RIGHT and returns right substring
SUBSTRING() Returns substring as specified
LOWER(str) Returns argument in lowercase
LCASE(str) == LOWER()
UCASE(str) == UPPER()
UPPER(str)
.
SOUNDEX() Returns a soundex string
SOUNDS LIKE Compare sounds
.
ASCII() numeric value of left-most character
ORD() Returns character code for leftmost character of the argument
BIN() string representation of the argument
CHAR() character for each integer passed
HEX() of decimal or string value
UNHEX() character from hex
BIT_LENGTH() length of argument in bits
CHAR_LENGTH(), CHARACTER_LENGTH() number of characters in argument
OCTET_LENGTH() synonym for LENGTH()

Control Flow

CASE Case operator

IF() If/else construct IF(expr1,expr2,expr3) If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

IFNULL(expr1,expr2) if expr1 not NULL returns expr1. If expr1 is NULL returns expr2; returns numeric or string , depending on the context IFNULL is like oracle's NVL function

NULLIF(expr1,expr2) Returns NULL if expr1 = expr2 is true, if expr1 <> returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

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

ABS()	absolute value
CEIL()	,
CEILING()	smallest integer value not less than the argument
FLOOR()	largest integer value not greater than the argument
ROUND()	Round
RAND()	random floating-point value
SIGN()	sign
EXP()	Raise to the power of
LN()	natural logarithm LOG10() base-10 log LOG2() base-2 log LOG() natural log
OCT()	octal representation of a decimal number
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
TRUNCATE()	Truncate to specified number of decimal places
+	Addition
/	Division
DIV	Integer division
%	Modulo
MOD()	Return the remainder
-	Minus
*	Times
-	Change the sign of the argument

LOAD_FILE(ifile) from server. requires file priviledge.

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

DESCRIBE table [column | wild]

Transactions Topics: HANDLER, ISOLATION, LOCK, SAVEPOINT, START TRANSACTION,

Administration ANALYZE CHECK* CHECKSUM CONSTRAINT CREATE USER user IDENTIFIED BY PASSWORD 'password' GRANT SELECT UPDATE ON TABLE tbl_name RENAME USER REVOKE SET PASSWORD FLUSH QUERY CACHE MERGE REPAIR BACKUP TABLE RESTORE SHOW CREATE VIEW

Error codes

See My SQL admin for addtional details.

show variables
show variables like 'sql%'

perl interface.

$q ="insert …| SELECT …"
$execute = $connect->query($q);
 $ar=$execute->affectedrows($q);     $nr=$execute->numrows();         $nf=$execute->numfields(); 

 while(@results = $execute->fetchrow() ){ $r0=$results[0];  … }