130 likes | 264 Views
CpSc 3220 The Language of SQL. Chapters 4-6. Using Functions. Function types Character (string) Numeric Date and Time Summary (aggregate). Some Character Functions. concat (str1,str2,…) format( numstr,numDec ) insert( str,pos,len,insStr ) lcase ( str ) ucase ( str ) length( str )
E N D
CpSc 3220The Language of SQL Chapters 4-6
Using Functions • Function types • Character (string) • Numeric • Date and Time • Summary (aggregate)
Some Character Functions • concat(str1,str2,…) • format(numstr,numDec) • insert(str,pos,len,insStr) • lcase(str) • ucase(str) • length(str) • substr(str,pos,len)
Some Numeric Functions • abs(x) • acos(x) • ceil(x) • exp(x) • mod(x) • pi(x) • pow(x,y) • round(x,d) • truncate(x,d)
Some Date/Time Functions • curdate() • date_format(date,format) • datedif(exp1,exp2) • dayname(date)
Summary Functions • count(exp) • min(exp) • max(exp) • sum(exp) • avg(exp)
Sorting Data • SQL uses the ORDER BY clause to sort rows
Syntax of the SELECT statement SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
Column-Based Logic • The select_expr term in the SELECT command can include logical conditions
A SELECT with a CASE Expression SELECT column1, column2, caseExpression FROM table
A Simple Example SELECT CASE categoryCode WHEN ‘F’ THEN ‘Fruit’ WHEN ‘V’ THEN ‘Vegetable’ ELSE ‘Other’ END AS ‘CategoryName’ FROM products;
The Searched Format SELECT CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 . . . [ELSE default] END
A Searched Format Example SELECT CASE WHEN categoryCode = ‘F’ THEN ‘Fruit’ WHEN categoryCode=‘V’ THEN ‘Vegetable’ ELSE ‘Other’ END AS ‘CategoryName’ FROM products;