Basic select queries query: retrieve/extract data but sometimes more generally: any data manipulation operation, e.g. insert/update/delete "queries" (which often involve an implicit or explicit selection) ad-hoc query: on-the-fly interactively. programmatic: embedded in a program/script/procedure. SQL is declarative/nonprocedural (What is wanted, not How to do it) ********************** SELECT values,to,display,columns,constants,expressions FROM source_table(s) WHERE row_specifying_expression/restriction/filter GROUP BY how_to_group HAVING expresion ORDER BY how_to_sort LIMIT row_count; --all clauses are optional --if a clause is used, it must be in this order --evaluate expressions (no retrieval from any table) SELECT Sqrt(2), Now(), 1/0, Round(123.456,2), Length('asdfqwerty'), Left('asdfqwerty',4); Usually, query a table: SELECT * FROM t --all columns, in declared order SELECT f1,f2 ... --fields f1 and f2 only of selected rows SELECT DISTINCT f1... --unique f1 values SELECT DISTINCT f1,f2... --unique f1,f2 combos --Limit number of rows returned. esp. useful on Windows, w/out pager. reduce network traffic. SELECT ... LIMIT 5; LIMIT skip,count #arbitrary section of result set --sorted order SELECT ... ORDER BY f1 SELECT ... ORDER BY f1 DESC --descending order SELECT ... ORDER BY f1,f2 --primary and secondary sorts SELECT ... ORDER BY f1 DESC,f2 --primary descending, secondary ascending cannot Order By Count(*) [use Alias] Nulls sort as smallest select ... Order By f1 Limit 1; #smallest f1 row select ... Order By f1 Desc Limit 1; #largest f1 row --WHERE clause filters rows SELECT ... FROM t WHERE f1 relOp value [AND|OR...]] relOp: < <= > >= = (single =) != (<> too) --pattern matching: WHERE f1 [NOT] LIKE [BINARY] 'string' case-insensitive unless BINARY 'string' contains pattern matching/wildcard operators: % 0 or more chars, _ single char: "j%" #starts with j "%j%" #contains j "j____" #5 chars, starts with j Like can be used with non-strings: date1 LIKE '19%' #dates starting 19.. --testing for Null. = and != cannot be used WHERE f1 IS [NOT] NULL --range test WHERE f1 [NOT] BETWEEN x AND y equivalent to: f1>=x AND f1<=y --set membership WHERE f1 [NOT] IN (val1,val2,...) equivalent to: f1=val1 OR f1=val2 OR... vals can be columns or expressions too --alternate/alias column name. if can't/don't want to change field name OR for derived column (see below) SELECT f1 AS newalias, f2 AS meaningfulname FROM t [ORDER BY meaningfulname] alias can not be used in WHERE clause AS is optional ' " ` quoting if is keyword, contains non-alphanumeric chars, or is all-digits. --calculated/derived values/columns SELECT f1*f2+sqrt(f3),f4-2*f5 FROM t.... --counts: SELECT COUNT(*) ... --number of rows SELECT COUNT(f1) ... --number of non-null f1 values SELECT COUNT(DISTINCT f1)... --number of different non-null f1 values SELECT COUNT(DISTINCT f1,f2)... --number of non-null f1,f2 combos --aggregate/column functions: summarize an entire column SELECT SUM(f1)... AVG(f1)... MIN(f1)... MAX(f1)... can be derived column/expression: SUM(100*f1/f2) Nulls ignored. cannot be in Where clause: !! select * from pet where birth=max(birth); if result set is empty or only nulls, aggregate function is Null (count is 0) --grouping/subtotals. summarize column per group of rows (or combos). output is sorted by the grouping column(s) SELECT f1,aggFunc(f2) FROM t GROUP BY f1 SELECT f1,f2,aggFunc(f3) FROM t GROUP BY f1,f2 select species,count(*) from pet group by species; select species,count(*),avg(weight) from pet group by species; select species,sex,count(*) from pet group by species,sex; select species,sex,avg(weight) from pet group by species,sex; --cannot mix column and aggregate function in select list without Group By: //syntax error? or now rubbish: !! select f1,aggFunc(f2) from t --unpredictable/unreadable if display columns not in Group By: !! select f1,aggregateFunction(f2) from t group by f3 !! select f1,f4,aggregateFunction(f2) from t group by f1 --frequencies of each different f1 value SELECT f1,COUNT(*) FROM t GROUP BY f1 --frequency of each f1,f2 combo SELECT f1,f2,COUNT(*) FROM t GROUP BY f1,f2 --grouping without any aggregate function is equivalent to Distinct Select Distinct fi From t Select fi From t Group By fi