Having vs Where

Where is use for SELECT statement to filter rows while Having is used when using aggregate functions such as GROUP BY for filtering rows.

Example:

WHERE -> SELECT * FROM tbl_name WHERE fieldname = 1

—

HAVING -> SELECT album, COUNT( track_number ) FROM tbl_tracks  JOIN tbl_album

ON tbl_album.id = tbl_tracks.id

GROUP BY tbl_tracks.id

HAVING tracks >= 10

 

Computing Dates

SELECT DATE_ADD( NOW(), INTERVAL 5 WEEK )

—

you can use

DAY, WEEK, MONTH, YEAR

—

SELECT DATE_SUB( NOW(), INTERVAL 5 WEEK )

 

Using Numeric and Date Functions

ABS(‘-12′) -> Returns 12

ROUND(-5.5, 0) -> Returns -6 WHERE 0 is the number of decimal places.

—

Create Minutes Like:

SELECT CONCAT( 420 DIV 60, ‘:’, LPAD(420 MOD 60, 2, ’0′))

—

SELECT CURDATE() -> 2010-02-12, Year, Month, Day

—

SELECT CURTIME() -> 10:30:42, Hour, Minutes, Seconds

—

SELECT NOW() -> 2010-02-12 10:30:42

—

SUM() -> Sum of numeric values

—

AVG() -> Returns average value of number in column.

—

MIN() -> Aggregate Function, returns smallest of numeric values.

—

MAX() -> Aggregate Function, returns largest of numeric values.

 

String Functions

SELECT LENGTH(fieldname) FROM tbl_name -> Finds the length of a string.

—

SELECT SUBSTR(‘Hello, World’, 1, 5) FROM tbl_name -> Output is “Hello”.

The above function takes 3 parameters. 1. The string, 2. Position number, 3. The number of characters to fetch.

—

SELECT UPPER(fieldname) -> Make the string in upper case.

—

SELECT LOWER(fieldname) -> Make the string in lower case.

—

SELECT TRIM(‘         string          ‘) FROM tbl_name -> Trims the left and right of a string

—

SELECT RIGHT(‘Hello, World’, 5) -> Output is “World”

—

SELECT LEFT(‘Hello, World’, 5) -> Output is “Hello”

 

Using GROUP BY

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

—

NOTE:

This code came from http://www.w3schools.com/sql/sql_groupby.asp

 

Using ORDER BY

SELECT * FROM tbl_name ORDER BY fieldname

SELECT * FROM tbl_name ORDER BY fieldname, fieldname2 -> ORDER the table from fieldname ASC, then fieldname2 ASC

 

Using DISTINCT

DISTINCT removes redundancy from a set of row.

SELECT DISTINCT fieldname FROM tbl_name WHERE fieldname LIKE ‘Z%’

—

HINT:

The default is ALL

SELECT ALL fieldname FROM tbl_name