1 / 25

ITN270 Advanced Internet Databases

ITN270 Advanced Internet Databases. Lecture 02. Operators and Function Reference Topics: Explain & Demonstrate How You Can Use MySQL Operators Overview of MySQL’s Build-in Functions. Operators.

shen
Download Presentation

ITN270 Advanced Internet Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ITN270 Advanced Internet Databases Lecture 02. Operators and Function Reference Topics: Explain & Demonstrate How You Can Use MySQL Operators Overview of MySQL’s Build-in Functions

  2. Operators • Operators are used to combineterms in expressions to perform arithmetic, compare values, perform bitwise or logical operations and match patterns • MySQL comes with more than 25 operators. These operators can be classified, according to their function, into the following four categories: • Arithmetic operators • Comparison operators • Logical operators • Bit operators

  3. Operator Precedence • Operators have varying levels of precedence. The levels are shown in the fol- lowing list, from highest to lowest. Operators on the same line have the same precedence. Operators at a given precedence level are evaluated left to right. Operators at a higher precedence level are evaluated before operators at a lower precedence level. BINARY COLLATE NOT ! ^ XOR - (unary minus) ) ~ unary bit negation) * / % + - << >> & | < <= = <=> != <> >= > IN IS LIKE REGEXP RLIKE BETWEEN CASE WHEN THEN ELSE AND && OR || :=

  4. Grouping Operators • Parentheses can be used to group parts of an expression. They override the default operator precedence that determines the order in which terms of an expression are evaluated • Parentheses can also be used simply for visual clarity to make an expression more readable. • Examples: • 1+2*3/4 2.50 • (((1+2) * 3) / 4) 2.25

  5. Arithmetic Operators • + • Addition • Note: + does not serve as the string concatenation operator • - • Subtraction • * • Multiplication • / • Division (?/0 -> NULL) • % • Modulo operator (? % 0 -> NULL)

  6. Comparison Operators • Comparison operators return • 1 if true • 0 if False • Null if could not be determinated

  7. Comparison Operators • Examples: 1. = 1=1 -> TRUE(1); 1=2 -> FALSE(0) 2. <=> Similar to = except NULL<=>NULL -> TRUE(1) 3. != or <> 1 <> 1 ->FALSE(0) 4. < 1< 2 -> TRUE(1) 5. <= 1<2 -> TRUE(1) 6. >= 2>1 -> TRUE(1) 7. > 2>1 -> TRUE(1) 8. exp BETWEEN min AND max ‘def’ BETWEEN ‘abc’ AND ‘ghi’ -> TRUE(1) 9. exp NOT BETWEEN min AND max ‘def’ NOT BETWEEN ‘abc’ AND ‘ghi’ -> FALSE(0)

  8. Comparison Operators • Examples: 10. CASE expr WHEN expr1 THEN result1 … [ELSE default] END 11. CASE WHEN expr1 THEN result1 … [ELSE default] END CASE 0 WHEN 1 THEN ‘T’ WHEN 0 THEN ‘F’ END --> ‘F’ CASE ‘F’ WHEN T THEN 1 WHEN ‘F’ THEN 0 END --> 0 12. expr IN (value1, value2,…) 3 IN (1, 2, 3, 4, 5) -> TRUE(1) 13. expr NOT IN (value1, value2,…) 3 NOT IN (1, 2, 3, 4, 5) -> FALSE(0) 14. exp IS NULL: NULL IS NULL --> TRUE(1) 15. exp IS NOT NULL: 0 IS NULL --> FALSE(0)

  9. Bit Operators • &, |, ^, <<, >>, ~ • Examples: 1. & - Bitwise AND of the operands 1 & 1 -> 1 1 & 2 -> 0 7 & 5 -> 5 2. | - Bitwise OR of the operand 1 | 1 -> 1 1 | 2 -> 3 3. ^ - Bitwise XOR of the operand 1 ^ 1 -> 0 1 ^ 0 -> 1 4. << - shifts the leftmost operand left the number of bit position indicated by the right operand: 1 << 2 --> 4

  10. Bit Operators • &, |, ^, <<, >>, ~ • Examples: 5. >> - shifts the leftmost operand right the number of bit position indicated by the right operand: 16 >> 3 --> 2 6. ~ - performbitwise negation of the following expression: ~0 -> -1 ~(-1) -> 0

  11. Logical Operators • NOT or ! • NOT 1 -> 0 • NOT 0 -> 1 • NOT NULL -> NULL • AND or && • 0 AND 0 -> 0 • 1 AND 1 -> 1 • OR or || • 0 OR 0 ->0 • 0 OR 1 -> 1 • 0 or NLL -> NULL • NULL or NULL -> NULL • XOR • 0 XOR 0 -> 0 • 0 XOR 1 -> 1 • 1 XOR 1 -> 0

  12. Cast Operators • Cast operators convert values from one type to another • _charset str • _utf8 ‘abc’ • BINARY str • Binary causes the following operand to be treated as a binary string so that comparisons are case-sensitive. If the following operand is a number, it is converted to string form. • Str COLLATE charset • The COLLATE operator causes the given string str to be compared using the collating order for the character set charset

  13. Pattern-Matching Operators • str LIKE pat [ESCAPE ‘c’] • str NOT LIKE pat [ESCAPE ‘c’] • wildcards • ‘%’ - matches any sequence of characters • ‘_’ - match any single character ‘ ‘ LIKE ‘%’ -> 1 ‘cat’ LIKE ‘cat%’ -> 1 • ESCAPE clause: • to turn off special meaning of the wildcard characters

  14. Pattern-Matching Operators • str REGEXP pat • REGEXP performs a regular expression pattern match • ‘cats and gogs’ REGEXP ‘dogs’ -> 1 • str NOT REGEXP pat

  15. MySQL Functions • These built-in functions can broadly be classified into the following groups: 1. Comparison functions 2. Cast Functions 3. Numerica functions 4. String functions 5. Date and Time functions 6. Summary Functions 7. Security related functions etc.

  16. MySQL Functions - Comparison Functions • GRATEST(expr1, expr2, …) • LEAST(expr1, expr2,…) • IF(exp1, exp2, exp3) • if exp1 is true, returns expr2, otherwise return expr3 • IFNULL(expr1, expr2) • returns expr2 if expr1 is NULL, otherwise it returns expr1. • INTERVAL(n,n1,n2,…) • returns 0 if n<n1, 1 if n<n2, and so on. Or -1 if n is NULL. • ISNULL(expr) • RETURN 1 if expr is NULL, otherwise it returns 0 • NULLIF(expr1,expr2) • returns expr1 if the two expression values differ, otherwise, returns NULL • STRCMP(str1, str2): returns 1, 0, or -1

  17. MySQL Functions - Cast Functions • CAST (exp AS Type) • CASE(304 AS BINARY) --> ‘304’ • CONVERT(expr, type) • CONVERT(expr USING charset)

  18. MySQL Functions - Numeric Functions

  19. MySQL Functions - String Functions There are many string functions, the following table only instroduce some of them:

  20. MySQL Functions - String Functions

  21. MySQL Functions - Date & Time Functions • Given the large number of date and time data type in MySQL, it is obvious that there are large number of functions to manipulate date and time values. The following table lists some of the more important time and date functions.

  22. MySQL Functions - Date & Time Functions

  23. MySQL Functions - Summary Functions • Summary function are also known as aggregate functions. They calculate a single value based on a group of values. The following table lists some of the important aggregate functions.

  24. MySQL Functions - Security-Related Functions • These functions perform various security-related operations, such as encripting or decrypting strings. The following table lists some of the important security-related functions

  25. MySQL Functions - Micellaneous Functions/System Information Functions MySQL also provides specialized functions that can be used to obtain information about the system itself. The following table lists some of the functions:

More Related