A collection of some the simplest SQL examples that I always forget.
Order by Random
Want to return your result in a random order, just add RAND().
SELECT * FROM table ORDER BY RAND()
Limit Rows Returned
This one is great for large database tables when you only wish to return a small number of rows.
SELECT * FROM table LIMIT 6
You can also combine with the above RAND() by adding at the end of your SQL statement, which is great for a short, random similar products list.
Ordering by Multiple Columns
Sometimes, ordering by just one column is not enough, simply add additional comma separated ORDER statements to order in that succession.
SELECT * FROM table ORDER BY col_one DESC, column2 ASC
Works great if you want to order a large list by category, then order alphabetically or by date for example.
Distinct and Group By
The DISTINCT and GROUP BY commands are great if you wanted to find out what values are in a long table that repeat, say states or countries, which you may use in a select list for example.
First the distinct, this is great, but will only return a single column, which can be fine to create a list of states in a list of contacts.
SELECT DISTINCT column FROM table
The drawback of DISTINCT is that it will only return a single column, the one you run the DISTINCT on.
If you need all columns retuned, try using the GROUP BY command at the end of your statement instead.
SELECT * FROM table GROUP BY column
Just want to know how many rows in the table, use the COUNT function.
SELECT COUNT(column) FROM table
Date Based SQL Queries
SQL works really well with dates and things dont need to be super complicated either, here are a few easy examples
Return rows for a specific date, as you can see, dates are formatted in the YYYY-MM-DD format which is the SQL default.
SELECT * FROM table WHERE date = '2017-04-01'
Return all rows before a specific date, which we will also order as well.
SELECT * FROM table WHERE date < '2017-04-01' ORDER BY date DESC
What about a date range, no worries, just use the BETWEEN command.
SELECT * FROM table WHERE date BETWEEN '2017-04-01' AND '2017-04-30'
You could also do a similar thing using the MONTH() and DATE() functions.
SELECT * FROM table WHERE MONTH(date) = '04' AND YEAR(date) = '2017'