More SQL Commands
SELECT DISTINCT column_name_1, column_name_2
is useful for grabbing all the unique fields elements and is often used with a
WHERE BETWEEN/LIKE/IN with operators >=, <=, <> and connectors OR/AND/NOT.
BETWEEN: inclusive of the start and endpoints
LIKE: pattern matching such as '%s' to find fields that end in the letter s.
IN: find members of a set. Think of it as multiple ORs.
SELECT * FROM table_name ORDER BY column1, column2
first orders ASC by default on column1, then any ties are broken by sorting on column2. Note: you need to put the ASC or DESC after each column name.
INSERT INTO on less than all columns of a table will put a NULL into any column not specified with the command. So if you have a table with 10 columns, but only insert data into 4 of the columns, the 6 unassigned columns would automatically be filled with NULL (assuming the column is declared to be nullable in the design).
Test for NULL with IS NULL or IS NOT NULL.
UPDATE table_name SET column1=value1, column2=value2 WHERE conditions
This is a good way to change the timestamps that cross over the midnight boundary because your timezone is different than UTC.
SELECT MIN/MAX() AS variable_name FROM table_name
In MySQL Workbench, functions like MIN/MAX() will appear in gray.
produces a new variable.
COUNT/AVG/SUM() does not include any NULLs.
LIKE can take wildcards:
% -> 0 more more characters in that position of the pattern
_ -> a single placeholder for a character
% ... % -> the ... in any position
Those wildcards also work against an INT.
ESCAPE: allows you to use special characters as literals in the pattern. For example, LIKE '543#%' means match 543% exactly, not wildcarded.
A powerful function in MySQL is REGEXP_LIKE() because it permits traditional regular expression syntax and pattern matching.
NOT IN is useful when followed by (SELECT ...)
(NOT) BETWEEN accepts numbers, text or dates and is often used with IN. If you are using it for dates, use a pair of # to bracket the date.
ALIAS only exists for the duration of the query.
UNION combines two or more SELECTs.
1. Each SELECT needs the same number of columns
2. All columns must be of the similar type to what it is lined up with.
UNION ALL will preserve multiplicity.
Resultant column names will inherit from the names of the columns from the first SELECT clause.
GROUP BY breaks down the various elements in a column into N subsets, gathered into any nth subset all together, for N distinct field values available.
HAVING is a pseudo-where to go with aggregation keywords such as GROUP BY, ORDER BY
EXISTS returns a boolean if any row survives the joins and conditions.
ANY and ALL are restricted to use with WHERE or HAVING only. ANY or ALL must be preceded by a compariosn operator such as > or <>.
SELECT INTO copies data into a new table. Supplementing with AS will provide new column names.
SELECT * INTO backupTable FROM old_table creates a copy in the same schema.
SELECT * INTO backupTable IN 'BackupSchema.mdb' FROM old_table creates a copy in a different schema.
TRICK: Create a new empty table with the same column design:
add WHERE 1=0 to force no matching.
INSERT INTO table_name(col names) SELECT another_table's columns FROM another_table
CASE
WHEN cond1 THEN res1
..........
ELSE default_res
END AS new column
Important: If all WHENs fail and there is no ELSE, then a NULL is returned. Otherwise it quits at the first true condition.
If there is no AS clause after the END keyword, the column is named in very verbose ugly way with all of the WHENs' result string concatened together.??!!
IFNULL() or COALESCE() allow you to substitute any non-null values for NULL in math calculations.
Stored Procedures:
CREATE PROCEDURE proc_name
AS
sql-commands
GO
Run it with:
EXEC proc_name
Procedures can receive parameters too.
use @param_name data_type
EXEC proc_name @param_name='...'
and add more parameters with commas
Comments are either -- or /* */
See the full list of SQL operator symbols at w3schools.com/sql/sql_operators.asp
