CpSc 3220 The Language of SQL

1. CpSc 3220The Language of SQL Chapters 7-9

2. The WHERE Clause • Determines which rows of table are to be selected

3. 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]]

4. Row-based Logic • The where_condition term in the WHERE clause is applied to each row and only those for which the condition is true are selected • The where_conditionis very similar to conditional expressions in other languages such as Java and PHP • It can be comprised of column variables, literals and operators

5. WHERE Clause Comparison Operators = equals <> does not equal != does not equal > is greater than < is less than >= is greater than or equal to <= is less than or equal to

6. Limiting Rows • In MySQL the LIMIT clause can be used to return only a specified number of rows • LIMIT n returns the ‘first’ n rows • If used after an ORDER clause it will return the first n rows of the sorted table • Other DBMSs handle this process differently

7. Boolean Logic • Boolean operators can be combined with conditional operators to express more complex logical conditions

8. The AND Operator • Returns true if both operands are true WHERE age > 21 ANDclass = ‘FRESHMAN’

9. The OR Operator • Returns true if either or both operands are true • Returns false otherwise WHERE age > 21 ORclass = ‘FRESHMAN’

10. The XOR Operator • Returns true if one but not both operands are true • Returns false otherwise WHERE age > 21 XOR class = ‘FRESHMAN’ • Available in MySQL but not all other DBMSs

11. The NOT Operator • A unary operator • Returns true if operand is false • Returns false if operand is ture

12. Precedence • AND takes precedence over OR and XOR • NOT takes precedence over AND • All boolean operators take precedence over conditional operators • Parentheses can be used to override precedence

13. The BETWEEN Operator • Example: WHERE hours BETWEEN 30 AND 59

14. The IN Operator • Examples WHERE age IN (20,24,45) WHERE color IN (‘Red’,’White’,’Blue’)

15. Boolean Logic and NULL Values • The conditional operators do not work on NULL values • The ISNULL function can be used • ISNULL(operand,result) returns result if operand is NULL, returns operand otherwise • Example: ISNULL(Weight,0)

16. Inexact Matches • SQL has several ways of handling inexact matches • LIKE • REGEXP (supported by MySQL) • SOUNDEX • DIFFERENCE (not supported by MySQL)

17. Pattern Matches Using LIKE • WHERE operand LIKE ‘pattern’ • pattern has two special symbols, % and _ • % matches any string of any length • _ matches a single character

18. Example Matches Using LIKE WHERE name LIKE ‘%ie’ finds all names ending in ie WHERE name LIKE ‘TH%’ finds all names starting with th WHERE name LIKE ‘%ing%’ finds all name containing ing Matches are case-insensitive

19. Pattern Matches Using REGEXP • MySQL supports a REGEXP operator that functions much like the REGEXP in PHP • WHERE name REGEXP regexpPatternuses the regexpPattern to match the name operand • REGEXP is much the same as the regular expression function in PHP

20. Matches by Sound • There is a SOUNDEX function which converts a string into a code that reflects its ‘sound value’ and this can be used to find two strings that have the same sound code.