FAQs

mySQL
Browse in : All > FAQs > mySQL

CAST() and CONVERT() Explained

CAST(expression AS type) or CONVERT(expression,type) are used to change the datatype of the expression to (BINARY, DATE, DATETIME, SIGNED {INTEGER}, TIME, or UNSIGNED {INTEGER}).

Back to top


Comparison Operators in the WHERE Clause

  • =
  • >
  • <
  • >=
  • <=
  • != or <>
  • IS NOT NULL
  • IS NULL
  • BETWEEN # AND #
  • IN (list)
  • NOT IN (list)
  • LIKE ("%" and multiple chars wildcard, "_" and single char wildcard)
  • NOT LIKE
  • REGEXP

    Back to top


    Date/Time Formats and UNIX Timestamps

    Due to the different time and date formats used in PHP and mySQL, the following clarifications are helpful:

  • NOW() returns date formated like 'YYYY-MM-DD HH:MM:SS' or 19971215235026 depending on whether it is interpretted as string or numeric format.
  • UNIX_TIMESTAMP() will accept, as argument, a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time.
  • FROM_UNIXTIME() returns the UNIX format timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format.

    Back to top


    Order of Precedence in Query Clauses

    After the SELECT and FROM in a query, the following clauses may be called, ordered respectively:

  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • LIMIT

    Back to top


    phpMyAdmin Backup Options

    "complete inserts" includes field names for each insert statement for each table "extended inserts" uses a single insert statement for all rows (comma separated) for each table

    Back to top


    SQL Query Joins

    LEFT join -> If no matching row is available in the right table, a row is added that contains NULL values for the right table columns. If both tables to be joined contain an identically named field used for the join, it may be specified after the second table as "USING (fieldname)" instead of "ON table1.fieldname = table2.fieldname".

    Back to top


    Unix Timestamp limitations

    The use of the UNIX_TIMESTAMP function the number of seconds since '1970-01-01 00:00:00' GMT. This function can and will limit the range of usable dates. Any unix timestamp can be fed to the FROM_UNIXTIME function to reverse the process and see full english dates. Also note that unix timestamps accomodate hours, minutes, and seconds, but they do not account for time zones as easily.

    Back to top


    Using mysql_free_result()

    After code has finished processing a result set and no longer has need of it, use of mysql_free_result($result) can be used to free up the space used. This is good practice when dealing with large result sets.

    Back to top