6 Awesome mySQL Statements That I Always Forget

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

Count

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'

 

Leave a Reply

Your email address will not be published. Required fields are marked *