Mysql 官方文档

Online

• Official Website

Download

• MySQL Cheat Sheet [.pdf]
• MySQL Database Quick Reference [.pdf]

Related

• DB2
  DB2 Cheat Sheet
• Oracle
  Oracle Cheat Sheet
• PostgreSQL
  PostgreSQL Cheat Sheet
• SQL
  SQL Cheat Sheet
• SQLite
  SQLite Cheat Sheet
• Sybase
  Sybase Cheat Sheet

Types

• BIGINT x
• Integer (-9223-372-036-854-775808 to 922337-203-685-477-5807)
• BLOB
• String (0 - 65535)
• CHAR
• String (0 - 255)
• DATE
• YYYY-MM-DD
• DATETIME
• YYYY-MM-DD HH:MM:SS
• DECIMAL
• "-DOU-BLE-" stored as string
• DOUBLE
• Decimal (24 to 53 digits)
• ENUM
• One of preset options
• FLOAT
• Decimal (precise to 23 digits)
• INT x
• Integer (-2147-483648 to 214748-3647)
• LONGBLOB
• String (0 - 429496-7295)
• LONGTEXT
• String (0 - 429496-7295)
• MEDIUMBLOB
• String (0 - 16777215)
• MEDIUMINT x
• Integer (-8388608 to 8388607)
• MEDIUMTEXT
• String (0 - 16777215)
• SET
• Selection of preset options
• SMALLINT x
• Integer (-32768 to 32767)
• TEXT
• String (0 - 65535)
• TIME
• HH:MM:SS
• TIMESTAMP
• YYYYMM-DDH-HMMSS
• TINYINT x
• Integer (-128 to 127)
• TINYTEXT
• String (0 - 255)
• VARCHAR
• String (0 - 255)

Assignment Operators

• :=
  Assign a value
• =
  Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)

Logical Operators

• ||, OR
  Logical OR
• AND, &&
  Logical AND
• NOT, !
  Negates value
• XOR
  Logical XOR

Comparison Operators

• ^
  Bitwise XOR
• -
  Minus operator
• !=, <>
  Not equal operator
• *
  Multiplication operator
• /
  Division operator
• &
  Bitwise AND
• >
  Greater than operator
• >>
  Right shift
• >=
  Greater than or equal operator
• <
  Less than operator
• <<
  Left shift
• <=
  Less than or equal operator
• <=>
  NULL-safe equal to operator
• % or MOD
  Modulo operator
• +
  Addition operator
• |
  Bitwise OR
• ~
  Invert bits
• BETWEEN ... AND ...
  Check whether a value is within a range of values
• BINARY
  Cast a string to a binary string
• CASE
  Case operator
• DIV
  Integer division
• IS
  Test a value against a boolean
• IS NOT
  Test a value against a boolean
• IS NOT NULL
  NOT NULL value test
• IS NULL
  NULL value test
• LIKE
  Simple pattern matching
• NOT BETWEEN ... AND ...
  Check whether a value is not within a range of values
• NOT LIKE
  Negation of simple pattern matching
• NOT REGEXP
  Negation of REGEXP
• REGEXP
  Pattern matching using regular expressions
• RLIKE
  Synonym for REGEXP
• SOUNDS LIKE
  Compare sounds

String Comparison

• STRCMP()
  Compare two strings

Regular Expressions

String Functions

• ASCII()
  Return numeric value of left-most character
• BIN()
  Return a string representation of the argument
• BIT_LENGTH()
  Return length of argument in bits
• CHAR_LENGTH()
  Return number of characters in argument
• CHAR()
  Return the character for each integer passed
• CHARACTER_LENGTH()
  A synonym for CHAR_LENGTH()
• CONCAT_WS()
  Return concatenate with separator
• CONCAT()
  Return concatenated string
• ELT()
  Return string at index number
• EXPORT_SET()
  Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
• FIELD()
  Return the index (position) of the first argument in the subsequent arguments
• FIND_IN_SET()
  Return the index position of the first argument within the second argument
• FORMAT()
  Return a number formatted to specified number of decimal places
• HEX()
  Return a hexadecimal representation of a decimal or string value
• INSERT()
  Insert a substring at the specified position up to the specified number of characters
• INSTR()
  Return the index of the first occurrence of substring
• LCASE()
  Synonym for LOWER()
• LEFT()
  Return the leftmost number of characters as specified
• LENGTH()
  Return the length of a string in bytes
• LOAD_FILE()
  Load the named file
• LOCATE()
  Return the position of the first occurrence of substring
• LOWER()
  Return the argument in lowercase
• LPAD()
  Return the string argument, left-padded with the specified string
• LTRIM()
  Remove leading spaces
• MAKE_SET()
  Return a set of comma-separated strings that have the corresponding bit in bits set
• MATCH
  Perform full-text search
• MID()
  Return a substring starting from the specified position
• OCTET_LENGTH()
  A synonym for LENGTH()
• ORD()
  Return character code for leftmost character of the argument
• POSITION()
  A synonym for LOCATE()
• QUOTE()
  Escape the argument for use in an SQL statement
• REPEAT()
  Repeat a string the specified number of times
• REPLACE()
  Replace occurrences of a specified string
• REVERSE()
  Reverse the characters in a string
• RIGHT()
  Return the specified rightmost number of characters
• RPAD()
  Append string the specified number of times
• RTRIM()
  Remove trailing spaces
• SOUNDEX()
  Return a soundex string
• SPACE()
  Return a string of the specified number of spaces
• SUBSTR()
  Return the substring as specified
• SUBSTRING_INDEX()
  Return a substring from a string before the specified number of occurrences of the delimiter
• SUBSTRING()
  Return the substring as specified
• TRIM()
  Remove leading and trailing spaces
• UCASE()
  Synonym for UPPER()
• UNHEX()
  Convert each pair of hexadecimal digits to a character
• UPPER()
  Convert to uppercase

Arithmetic Operators

Mathematical Functions

• ABS()
  Return the absolute value
• ACOS()
  Return the arc cosine
• ASIN()
  Return the arc sine
• ATAN()
  Return the arc tangent
• ATAN2(), ATAN()
  Return the arc tangent of the two arguments
• CEIL()
  Return the smallest integer value not less than the argument
• CEILING()
  Return the smallest integer value not less than the argument
• CONV()
  Convert numbers between different number bases
• COS()
  Return the cosine
• COT()
  Return the cotangent
• CRC32()
  Compute a cyclic redundancy check value
• DEGREES()
  Convert radians to degrees
• EXP()
  Raise to the power of
• FLOOR()
  Return the largest integer value not greater than the argument
• LN()
  Return the natural logarithm of the argument
• LOG()
  Return the natural logarithm of the first argument
• LOG10()
  Return the base-10 logarithm of the argument
• LOG2()
  Return the base-2 logarithm of the argument
• MOD()
  Return the remainder
• OCT()
  Return an octal representation of a decimal number
• PI()
  Return the value of pi
• POW()
  Return the argument raised to the specified power
• POWER()
  Return the argument raised to the specified power
• RADIANS()
  Return argument converted to radians
• RAND()
  Return a random floating-point value
• ROUND()
  Round the argument
• SIGN()
  Return the sign of the argument
• SIN()
  Return the sine of the argument
• SQRT()
  Return the square root of the argument
• TAN()
  Return the tangent of the argument
• TRUNCATE()
  Truncate to specified number of decimal places

Date and Time

• ADDDATE()
  Add time values (intervals) to a date value
• ADDTIME()
  Add time
• CONVERT_TZ()
  Convert from one timezone to another
• CURDATE()
  Return the current date
• CURRENT_DATE(), CURRENT_DATE
  Synonyms for CURDATE()
• CURRENT_TIME(), CURRENT_TIME
  Synonyms for CURTIME()
• CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
  Synonyms for NOW()
• CURTIME()
  Return the current time
• DATE_ADD()
  Add time values (intervals) to a date value
• DATE_FORMAT()
  Format date as specified
• DATE_SUB()
  Subtract a time value (interval) from a date
• DATE()
  Extract the date part of a date or datetime expression
• DATEDIFF()
  Subtract two dates
• DAY()
  Synonym for DAYOFMONTH()
• DAYNAME()
  Return the name of the weekday
• DAYOFMONTH()
  Return the day of the month (0-31)
• DAYOFWEEK()
  Return the weekday index of the argument
• DAYOFYEAR()
  Return the day of the year (1-366)
• EXTRACT()
  Extract part of a date
• FROM_DAYS()
  Convert a day number to a date
• FROM_UNIXTIME()
  Format UNIX timestamp as a date
• GET_FORMAT()
  Return a date format string
• HOUR()
  Extract the hour
• LAST_DAY
  Return the last day of the month for the argument
• LOCALTIME(), LOCALTIME
  Synonym for NOW()
• LOCALTIMESTAMP, LOCALTIMESTAMP()
  Synonym for NOW()
• MAKEDATE()
  Create a date from the year and day of year
• MAKETIME
  MAKETIME()
• MICROSECOND()
  Return the microseconds from argument
• MINUTE()
  Return the minute from the argument
• MONTH()
  Return the month from the date passed
• MONTHNAME()
  Return the name of the month
• NOW()
  Return the current date and time
• PERIOD_ADD()
  Add a period to a year-month
• PERIOD_DIFF()
  Return the number of months between periods
• QUARTER()
  Return the quarter from a date argument
• SEC_TO_TIME()
  Converts seconds to 'HH:MM:SS' format
• SECOND()
  Return the second (0-59)
• STR_TO_DATE()
  Convert a string to a date
• SUBDATE()
  A synonym for DATE_SUB() when invoked with three arguments
• SUBTIME()
  Subtract times
• SYSDATE()
  Return the time at which the function executes
• TIME_FORMAT()
  Format as time
• TIME_TO_SEC()
  Return the argument converted to seconds
• TIME()
  Extract the time portion of the expression passed
• TIMEDIFF()
  Subtract time
• TIMESTAMP()
  With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
• TIMESTAMPADD()
  Add an interval to a datetime expression
• TIMESTAMPDIFF()
  Subtract an interval from a datetime expression
• TO_DAYS()
  Return the date argument converted to days
• TO_SECONDS()
  Return the date or datetime argument converted to seconds since Year 0
• UNIX_TIMESTAMP()
  Return a UNIX timestamp
• UTC_DATE()
  Return the current UTC date
• UTC_TIME()
  Return the current UTC time
• UTC_TIMESTAMP()
  Return the current UTC date and time
• WEEK()
  Return the week number
• WEEKDAY()
  Return the weekday index
• WEEKOFYEAR()
  Return the calendar week of the date (0-53)
• YEAR()
  Return the year
• YEARWEEK()
  Return the year and week

GROUP BY Functions

• AVG()
  Return the average value of the argument
• BIT_AND()
  Return bitwise and
• BIT_OR()
  Return bitwise or
• BIT_XOR()
  Return bitwise xor
• COUNT()
  Return a count of the number of rows returned
• COUNT(DISTINCT)
  Return the count of a number of different values
• GROUP_CONCAT()
  Return a concatenated string
• MAX()
  Return the maximum value
• MIN()
  Return the minimum value
• STD()
  Return the population standard deviation
• STDDEV_POP()
  Return the population standard deviation
• STDDEV_SAMP()
  Return the sample standard deviation
• STDDEV()
  Return the population standard deviation
• SUM()
  Return the sum
• VAR_POP()
  Return the population standard variance
• VAR_SAMP()
  Return the sample variance
• VARIANCE()
  Return the population standard variance

Information Functions

• BENCHMARK()
  Repeatedly execute an expression
• CHARSET()
  Return the character set of the argument
• COERCIBILITY()
  Return the collation coercibility value of the string argument
• COLLATION()
  Return the collation of the string argument
• CONNECTION_ID()
  Return the connection ID (thread ID) for the connection
• CURRENT_USER(), CURRENT_USER
  The authenticated user name and host name
• DATABASE()
  Return the default (current) database name
• FOUND_ROWS()
  For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
• LAST_INSERT_ID()
  Value of the AUTOINCREMENT column for the last INSERT
• ROW_COUNT()
  The number of rows updated
• SCHEMA()
  A synonym for DATABASE()
• SESSION_USER()
  Synonym for USER()
• SYSTEM_USER()
  Synonym for USER()
• USER()
  The user name and host name provided by the client
• VERSION()
  Returns a string that indicates the MySQL server version

Encryption and Compression

• AES_DECRYPT()
  Decrypt using AES
• AES_ENCRYPT()
  Encrypt using AES
• COMPRESS()
  Return result as a binary string
• DECODE()
  Decodes a string encrypted using ENCODE()
• DES_DECRYPT()
  Decrypt a string
• DES_ENCRYPT()
  Encrypt a string
• ENCODE()
  Encode a string
• ENCRYPT()
  Encrypt a string
• MD5()
  Calculate MD5 checksum
• OLD_PASSWORD()
  Return the value of the pre-4.1 implementation of PASSWORD
• PASSWORD()
  Calculate and return a password string
• SHA1(), SHA()
  Calculate an SHA-1 160-bit checksum
• SHA2()
  Calculate an SHA-2 checksum
• UNCOMPRESS()
  Uncompress a string compressed
• UNCOMPRESSED_LENGTH()
  Return the length of a string before compression

Miscellaneous Functions

• DEFAULT()
  Return the default value for a table column
• GET_LOCK()
  Get a named lock
• INET_ATON()
  Return the numeric value of an IP address
• INET_NTOA()
  Return the IP address from a numeric value
• IS_FREE_LOCK()
  Checks whether the named lock is free
• IS_USED_LOCK()
  Checks whether the named lock is in use. Return connection identifier if true.
• MASTER_POS_WAIT()
  Block until the slave has read and applied all updates up to the specified position
• NAME_CONST()
  Causes the column to have the given name
• RELEASE_LOCK()
  Releases the named lock
• SLEEP()
  Sleep for a number of seconds
• UUID_SHORT()
  Return an integer-valued universal identifier
• UUID()
  Return a Universal Unique Identifier (UUID)
• VALUES()
  Defines the values to be used during an INSERT

Select Queires

• Returns all columns
• SELECT * FROM table
• Returns and joind
• SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
• Returns first 10 rows
• SELECT ... FROM ... WHERE condition LIMIT 10
• Returns rows that match condition
• SELECT ... FROM ... WHERE condition
• Returns specific column
• SELECT field1, field2, ... FROM table1, table2, ...
• Returns with no repeats
• SELECT DISTINCT field1 FROM ...
• Returns with orders
• SELECT ... FROM ... WHERE condition GROUP BY field
• Returns withd orders and match condition
• SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2