LAMPsig MySQL Class :: Class #4

_Instructor:_
*David Benjamin :: david_AT_peterbenjamin_(dot)_com*

_Class Contact:_
*Sharon Lake :: sharon_AT_linuxchixla_(dot)_org*

_Lab Help:_
*Steve Glasser :: steve_AT_fpig_(dot)_net*


*_CLASS #4 SYLLABUS_*

Class 4 – Select Statements, Expressions and Functions, Misc SQL
(Chap 5 & 6 / MySQL-M 9.2, 12, 13.1.7, 13.1.8, A.5.4)
_20% exam material_ (10% Chapter 5, 10% Chapter 6)


Example Sorting database sortingOrderExamples. Sql file will create three tables 'sortChar', 'sortEnum', and 'sortSet'. Please create a database called sortingOrderExamples and import. These tables will be used to demonstrate sorting options when dealing with Char vs. Binary Char, ENUM, and SET column types.

1. SELECT
   1. Select Syntax
      • mysql> SELECT values_to_display
        FROM table_name
        WHERE expression
        GROUP BY how_to_group
        HAVING expression
        ORDER BY sort_options
        LIMIT row_count;
        
      • mysql> SELECT 2+2, REPEAT('x', 5), DATE_ADD('2001-01-01', INTERVAL 7 DAY), 1/0;
   
   
   2. Using Aliases for Column Names
      1. AS is optional keyword
      2. Aliases *may* be quoted, multiple word Alias *must* be quoted
      3. You can refer to an Alias elsewhere in a query, e.g., GROUP BY, HAVING, or ORDER BY, but it *cannot* be used as part of the WHERE clause.
      
      
   3. Restricting a selection Using WHERE
      
      
   4. Using ORDER BY to Sort
      1. Natural (or default) Sort Order of Column Types
         • Numeric: Ascending Numeric
         • Decimal: sorted numerically, even though DECIMAL values are stored in string form
         • Date/Time: Ascending Temporal (oldest values first)
         • String Binary: Numeric Value of Bytes that make up the String
         • String Non binary: Lexical
         • Enum: Internal Numeric Value based on Enum Set Order
         • Set: Internal Number Value based on a more complex Set Order
      
      
      
   5. Limiting a Selection using LIMIT
      
      
   6. Aggregate Functions. Using both with and without WHERE clause. Aggregate values for Empty Set
      1. SUM()
      2. AVG()
      3. MIN()
      4. MAX()
      5. COUNT()
      
      
   7. GROUP BY
      
      
   8. HAVING
      
      
   9. Using DISTINCT to Eliminate Duplicates
      
   10. Concatenating SELECT Results with UNION
       
       
   11. Pattern Matching with LIKE and WILDCARD Characters
       
       
   12. Dealing with NULL
       1. NULL Values and Column Definitions
       2. NULL Values and NOT NULL Columns
       3. NULL Values in Expressions and Comparisons
          • ISNULL(), IFNULL()
       4. NULL Values in Aggregate Functions are ignored except for the COUNT() Function.

2. New in mysql 4.1 / 5 SUB-SELECTS. Will also be covered Class #6 & 7 (Chap. 8 Joins).

3. Using Reserved Words as Identifiers

4. Commentating in SQL files

5. Functions. Per the study guide, you are not expected to every detail about each function, but you are expected to know it's general behavior

• ABS()
• AES_DECRYPT()and AES_ENCRYPT() / DES_DECRYPT() and DES_ENCRYPT()
• BIN()
• CELILING()
• CHAR()
• CHAR_LENGTH()
• CHARACTER_LENGTH()
• CONCAT()and CONCAT_WS()
• CONV()
• CURRENT_DATE() and CURRENT_TIME()
• DATE_ADD()
• DATE_FORMAT()
• DATE_SUB()
• DAYNAME() and DAYOFMONTH() and DAYOFWEEK() and DAYOFYEAR()
• DECODE and ENCODE
• ELT()
• EXPORT_SET()
• FIELD()
• FIND_IN_SET()
• FLOOR()
• FROM_DAYS()
• FROM_UNIXTIME()
• GREATEST()
• HEX()
• HOUR()
• IF()
• IFNULL()and ISNULL()
• IN()
• INSERT()
• INSTR()
• LCASE()
• LEAST()
• LEFT()
• LENGTH()
• LOAD_FILE()
• LENGTH()
• LOCATE()
• LOWER()
• LPAD()
• LTRIM()
• MAKE_SET()
• MD5()
• MID()
• MINUTE()
• MOD()
• MONTH()
• MONTHNAME()
• NOW()
• OCT()
• PASSWORD()
• PERIOD_ADD()
• PERIOD_DIFF()
• POSITION()
• POW() and POWER()
• QUARTER()
• QUOTE()
• RAND()
• REPLACE()
• REVERSE()
• RIGHT()
• ROUND()
• RPAD()
• RTRIM()
• SEC_TO_TIME()
• SECOND()
• SIGN()
• SUBSTRING()and SUBSTRING_INDEX()
• TIME_FORMAT()
• TIME_TO_SEC()
• TO_DAYS()
• TRIM()
• TRUNCATE()
• UCASE()
• UNIX_TIMESTAMP()
• UPPER()
• WEEK()
• WEEKDAY()
• YEAR()
• YEARWEEK()
• VERSION()