mysql_basics.md

MySQL basics

There are several non-common SQL operators in this handbook.

Operator	Denotes
[ ]	Optional
|	Choices

SELECT

SELECT * FROM table1;

WHERE

SELECT * FROM table1 WHERE condition1;

AND, OR & NOT

SELECT * FROM table1 WHERE [NOT] condition1 AND|OR condition2 AND|OR ...;

ORDER BY

SELECT * FROM table1 ORDER BY `column1`, `column2`, ... [ASC|DESC];

INSERT INTO

INSERT INTO table1[(`column1`, `column2`, ...)]
  VALUES('value1', 'value2', ...), ('value1', 'value2', ...), ...;

NULL

SELECT * FROM table1 WHERE `column1` IS [NOT] NULL;

UPDATE

UPDATE table1 SET `column1` = 'value1', `column2` = 'value2', ...
  WHERE condition1;

DELETE

DELETE FROM table1 WHERE condition1;

LIMIT

SELECT * FROM table1 WHERE condition1 LIMIT number1;

MIN, MAX, COUNT, AVG & SUM

SELECT MIN|MAX|COUNT|AVG|SUM(`column1`) FROM table1 WHERE condition1;

LIKE

SELECT * FROM table1 WHERE `column1` LIKE pattern1;

Wildcards

Symbol	Description
%	Represents zero or more characters.
_	Represents a single character.

IN

SELECT * FROM table1 WHERE `column1` IN('value1', 'value2', ...);
SELECT * FROM table1 WHERE `column1` IN(SELECT * FROM table2);

BETWEEN

SELECT * FROM table1 WHERE `column1` BETWEEN 'value1' AND|OR 'value2';

AS

SELECT `column1` AS `alias1` FROM table1;
SELECT * FROM table1 AS t;

JOIN

SELECT * FROM table1 INNER|LEFT|RIGHT|CROSS JOIN table2 ON condition1;

Join types

Inner	Left	Right	Cross

Self join

SELECT * FROM table1 T1, table1 T2 WHERE condition1;

UNION

SELECT * FROM table1 UNION [ALL] SELECT * FROM table2;

GROUP BY

SELECT * FROM table1 WHERE condition1
  GROUP BY `column1`, `column2`, ...
  ORDER BY `column1`, `column2`, ...;

HAVING

SELECT * FROM table1 GROUP BY `column1`, `column2`, ... HAVING condition1;

EXISTS

SELECT * FROM table1
  WHERE EXISTS(SELECT `column1` FROM table2 WHERE condition1);

ANY & ALL

SELECT * FROM table1
  WHERE `column1` = ANY|ALL(SELECT `column1` FROM table2 WHERE condition1);

INSERT INTO SELECT

INSERT INTO table2[(`column1`, `column2`, ...)]
  SELECT column1, column2, ... FROM table1 WHERE condition1;

CASE

CASE
  WHEN condition1 THEN 'value1'
  WHEN condition2 THEN 'value2'
  ...
  ELSE 'defaultValue'
END;

IFNULL & COALESCE

SELECT `UnitPrice` * (`UnitsInStock` + IFNULL|COALESCE(`UnitsOnOrder`, 0))
  FROM Products;

Comments

-- Hello World

Operators

Arithmetic	Bitwise
Operator Description + Add - Subtract * Multiply / Divide % Modulo	Operator Description & AND | OR ^ Exclusive OR

Comparison	Compound
Operator Description = Equal to > Greater than < Less than >= Greater than or equal to <= Less than or equal to <> Not equal to	Operator Description += Add equals -= Subtract equals *= Multiply equals /= Divide equals %= Modulo equals &= AND equals ^-= Exclusive AND equals |*= OR equals

Operator	Description
ALL	TRUE if all of the subquery values meet the condition.
AND	TRUE if all the conditions separated by AND is TRUE.
ANY	TRUE if any of the subquery values meet the condition.
BETWEEN	TRUE if the operand is within the range of comparisons.
EXISTS	TRUE if the subquery returns one or more records.
IN	TRUE if the operand is equal to one of a list of expressions.
LIKE	TRUE if the operand matches a pattern.
NOT	Displays a record if the condition(s) is NOT TRUE.
OR	TRUE if any of the conditions separated by OR is TRUE.
SOME	TRUE if any of the subquery values meet the condition.