Using IN operator Example using an array
Category: SELECT
Ternary Operator if( , , )
if(Condition, ConditionIsTrue, ConditionIsNotTrue) SELECT depending on condition UPDATE depending on condition Example – Increment value if below # or reset to 1
DateTime WHERE
Rows with a DateTime in the past Time based condition Argument can be:
DateTime SELECT
Date Order ASC = oldest firstDESC = most recent first Date or DateTime from strings Get time difference between now and a DateTime field Argument can be: If DateTime value is Null? You will get a Null value returned from TIMESTAMPDIFF Specifying Returned Date Format Get SQL Server’s Current DateTime Same as Now() but giving […]
Null
WHERE statements UPDATE statements
Count – Get number of results
How many rows in a table Count and Group Combined List the number of customers in each country Adding values returned as a the total Use SUM()
GROUP BY
Get a single result for each MyColumnName value that matches the search criteria For example, say you have multiple rows where MyColumnName=1 that each matches the query, you’ll get back just one result with MyColumnName=1 Same but with an additional field that tells you how many rows matched for each MyColumnName COUNT and GROUP BY […]
Strings
Null or Empty Strings An empty field can be either an empty string or a NULL. Is field is not empty AND not null (i.e. it has an actual string in it): WHERE field_name > '' Is field is empty OR null: WHERE IFNULL(field_name, '') = ''
Maths based results
This is PHP4 Code! Get Number of Results $query1 = @mysql_query("SELECT COUNT(*) FROM some_table WHERE some_column != 1"); $number_of_rows = @mysql_fetch_array($query1); $number_of_rows = $number_of_rows[0]; echo "This many: $number_of_rows"; Results Based On An Average Score Calculation $query1 = @mysql_query("SELECT university_name, CONVERT((score / games_played), UNSIGNED INTEGER) as ave_score FROM tbl_universities ORDER BY ave_score DESC, university_name ASC"); […]
DateTime general
ORDER BY Date Order ASC = oldest first DESC = most recent first Note it’s “MINUTE”, “HOUR”, etc not “MINUTES” or “HOURS” etc Convert DateTime to Date Specifying Date and DateTime from a string It is recommended to use CAST() Last Day Of Month (This produces a DATE result, not DATETIME) Get SQL Server’s Current […]