General MySQL Syntax

Required Knowledge
> Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words.
> Identify MySQL data type properties and appropriate usage
> Recognize and use common functions and expressions for all MySQL data types
> Identify and use comment syntax
> Describe and utilize prepared statements
> Describe transactions and transaction isolation levels and the impact they have on database behavior

> Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words

Identifiers

  • Identifiers may be quoted or unquoted
  • Identifiers may contain all alphanumeric characters, the _ and the $
  • Identifiers may begin with all legal characters, even a digit, but they are discouraged
  • Identifiers may not consist entirely of digits
  • Identifiers MAY be QUOTED, and so can use spaces or dashes, which aren’t normally legal
    • to quote, use backticks ` or if ANSI_QUOTES is on, you can use double quotes “
    • quoted identifiers may consist entirely of digits
  • Database and table names may not include . \ or / even when quoted
  • Aliases can be referenced with ` or ‘ or ” in the SELECT string, but elsewhere, they must be referenced with ` or it will be treated as a string literal

Case Sensitivity

  • Most Unix filesystems are CASE SENSITIVE
  • Windows is not CASE SENSITIVE
  • All SQL queries must use the same lettercase in a statement for all references
  • If you want to use the option “lower_case_table_names,” you must set it before you start your session

Qualified Names

  • Higher levels can reference lower ones, by using the period to delimit the difference, which is why table names may not include the period even when quoted.
    • e.g. DATABASE_NAME.TABLE_NAME
    • e.g. DATABASE_NAME.TABLE_NAME.COLUMN_NAME

Reserved words

  • You cannot use reserved words or functions as identifiers unless you quote them (but doing so is discouraged, as it causes confusion):
    • Databases, tables, columns, and index identifiers can be quoted with ` or ” (ANSI_QUOTES)
    • Aliases can be quoted with ` ‘ and “
  • Reserved Words are not case sensitive

> Identify MySQL data type properties and appropriate usage

Rather than covering everything that’s already in the documentation, I’ll just cover the basics of the main data types, and link to the respective pages for further study. Generally speaking, MySQL has the following three categories of data types:

  • Numeric Values
    • Numbers may or may not have fractional parts.  14, -428.948, and +739 are all legal numbers
    • Integers have no fractional parts
    • Fractional numbers may be declared to have a fixed or variable number of decimal places
      • e.g. salary DECIMAL(5,2), which indicates 5 digits for precision (total number of digits), and 2 digits following the decimal point, so in the above would provide a range from -999.99 to 999.99
    • Numeric columns can be declared UNSIGNED to prevent values from having negative values.
    • DECIMAL and NUMERIC are exact values, and FLOAT and DOUBLE are considered approximate values
    • Integer types (INT) may be declared with a display width, INT(4), which is used for display purposes, and does not affect the range of values stored in the field.
      • If the value is smaller than the display width, the field is padded
      • If the value is larger than the display width, the field is expanded on display and the value is not truncated when viewing.
  • String Values
    • May be binary (character data) or non-binary (raw bytes)
      • Non-Binary
        • Non-Binary strings are sequences of characters that belong to a specific character set.
        • Non-Binary string comparisons are based on the collation (sorting order) of the character set associated with the string
        • Collation determines if uppercase and lowercase versions of a given character are equivalent.
        • Collation also determines whether to treat characters with accent marks as equivalent.
        • Binary collation will compare values based on numeric character value, which means that it becomes case sensitive, and accent sensitive.
      • Binary
        • Binary strings are a sequence of byte values.  It might look like characters, but as far as MySQL is concerned, it consists of byte values.
        • Binary comparisons compare the byte values, so ‘A’ and ‘a’ will be different, and case sensitivity does not apply.
        • Selecting non-binary UPPER() and binary UPPER() shows how MySQL treats the values:
          • $> SELECT upper(‘AaBb’), upper(binary ‘AaBb’)
            Results in: “AABB” and “AaBb” respectively
  • Temporal Values
    • Consists of DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
      • Each of these has a “Zero” value.  e.g. ‘0000-00-00′ for DATE and ’00:00:00’ for TIME
      • MySQL represents DATE in the format YYYY-MM-DD, which corresponds to ANSI SQL or ISO 8601.  Reformat dates according to your needs with date_format() function.
    • MySQL expects to receive dates in the ISO format (YYYY-MM-DD)
      • Leading zeros can be omitted for day and month. e.g. ‘2001-01-01’ and ‘2001-1-1’ are both valid
      • Delimiter does not need to be a hyphen, ‘/’ can be used as well.
      • 2 digit years are converted to 4 digit years; 70-99 are converted to 1970-1999, while 00-69 are converted to 2000-2069.
    • TIMESTAMP values represent the number of seconds elapsed since the beginning of 1970 and are stored using 4 bytes.
      • Stored in UTC; converted from the current timezone for storage, and converted back for display.
      • TIMESTAMP stores seconds but is displayed in DATETIME format, i.e. ‘YYYY-MM-DD HH:MM:SS’
      • TIMESTAMP field is special in that you can specify that it should be initialized with the current time stamp, or updated with the timestamp – without assigning it a value in the sql statement. See DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP

There are also some extensions that can handle spatial values, but supposedly, the spatial values are not covered on the exam.

Continue to Part 2

Leave a Reply

Your email address will not be published. Required fields are marked *