search
Search
Join our weekly DS/ML newsletter layers DS/ML Guides
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
chevron_left MySQL
Common questions2 topics
Cookbooks7 topics
Documentation5 topics
Getting startedAPI referenceRecipes reference
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook
chevron_left MySQL
Common questions2 topics
Cookbooks7 topics
Documentation5 topics
Getting startedAPI referenceRecipes reference
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

MySQL | API reference

Database
chevron_right
MySQL
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags

Data types

  • Date and Time

    • DATE, DATETIME and TIMESTAMP
      MySQL's DATE type is used when we do not need to store information about time, while DATETIME and TIMESTAMP types are used for values with both date and time parts.
    • MySQL | TIME
      MySQL's TIME type is used when we do not need information about the date.
    • MySQL | YEAR
      MySQL's YEAR type is used to represent a year value.
  • Numeric

    • MySQL | BIT
      MySQL's BIT data type is used to store bit values.
    • MySQL | BOOLEAN
      MySQL inherently does not support native Boolean data types. However, MySQL still provides us with the datatypes: BOOLEAN and BOOL, which are just aliases for TINYINT.
    • MySQL | DECIMAL and NUMERIC
      In MySQL, DECIMAL and NUMERIC are collectively referred to as Fixed-Point types. This just means they store exact values with a specific precision, which is particularly important for information such as monetary data.
    • MySQL | FLOAT and DOUBLE
      MySQL's FLOAT and DOUBLE types are used to represent approximate numeric data values. The FLOAT type is for single-precision (4 bytes), while DOUBLE is for double-precision (8 bytes).
    • MySQL | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
      There are a total of 5 integer types offered by MySQL.
  • String

    • MySQL | BINARY and VARBINARY
      The BINARY and VARBINARY types in MySQL are similar to CHAR and VARCHAR but they store binary strings rather than non-binary strings (i.e. byte strings rather than character strings).
    • MySQL | BLOB
      In order to store binary data such as images in MySQL, we can use one of the following data types: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
    • MySQL | CHAR and VARCHAR
      The CHAR and VARCHAR types in MySQL are both used to store strings (i.e. text).
    • MySQL | TEXT
      In order to store text in MySQL, we can use one of the following data types: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

Functions

  • Aggregate

    • MySQL | AVG method
      MySQL's AVG(~) aggregate method returns the average value of the provided numeric input / column.
    • MySQL | COUNT method
      MySQL's COUNT(~) aggregate method counts the number of returned results.
    • MySQL | GROUP_CONCAT method
      MySQL's GROUP_CONCAT(~) method concatenates non-NULL values from the same group together.
    • MySQL | MAX method
      MySQL's MAX(~) aggregate method returns the maximum value from the provided input.
    • MySQL | MIN method
      MySQL's MIN(~) aggregate method returns the minimum value from the provided input.
    • MySQL | STD method
      MySQL's STD(~) aggregate method returns the population standard deviation from the provided numeric input.
    • MySQL | STDDEV method
      MySQL's STDDEV(~) aggregate method returns the population standard deviation from the provided numeric input.
    • MySQL | STDDEV_POP method
      MySQL's STDDEV_POP(~) aggregate method returns the population standard deviation from the provided numeric input.
    • MySQL | STDDEV_SAMP method
      MySQL's STDDEV_SAMP(~) aggregate method returns the sample standard deviation from the provided numeric input.
    • MySQL | SUM method
      MySQL's SUM(~) aggregate method returns the sum of the input expression.
    • MySQL | VARIANCE method
      MySQL's VARIANCE(~) aggregate method returns the population standard variance from the provided numeric input.
    • MySQL | VAR_POP method
      MySQL's VAR_POP(~) aggregate method returns the population standard variance from the provided numeric input.
    • MySQL | VAR_SAMP method
      MySQL's VAR_SAMP(~) aggregate method returns the sample standard variance from the provided numeric input.
  • Date and Time

  • Numeric

    • MySQL | ABS method
      MySQL's ABS(~) method returns the absolute value of the input number.
    • MySQL | ACOS method
      MySQL's ACOS(~) method computes the arc cosine of the input value.
    • MySQL | ASIN method
      MySQL's ASIN(~) method computes the arc sine of the input value.
    • MySQL | ATAN method
      MySQL's ATAN(~) method computes the arc tangent of the input value(s).
    • MySQL | ATAN2 method
      MySQL's ATAN2(~) method computes the arc tangent of the input values.
    • MySQL | CEIL method
      MySQL's CEIL(~) method returns the smallest integer greater than or equal to the provided input number.
    • MySQL | CEILING method
      MySQL's CEILING(~) method returns the smallest integer greater than or equal to the provided input number.
    • MySQL | CONV method
      MySQL's CONV(~) method converts the input number from one base to another and returns it as a string.
    • MySQL | COS method
      MySQL's COS(~) method computes the cosine of the argument in radians.
    • MySQL | COT method
      MySQL's COT(~) method computes the cotangent of the input value in radians.
    • MySQL | CRC32 method
      MySQL's CRC32(~) method computes a cyclic redundancy check value and returns a 32-bit unsigned value.
    • MySQL | DEGREES method
      MySQL's DEGREES(~) method converts X radians into degrees and returns it.
    • MySQL | EXP method
      MySQL's EXP(~) method returns the value of ​e​ (base of natural logarithm) raised to the specified power.
    • MySQL | FLOOR method
      MySQL's FLOOR(~) method returns the largest integer smaller than or equal to the provided input number.
    • MySQL | LN method
      MySQL's LN(~) method returns the value of the natural (base-​e​) logarithm of the input number. If we provide an input less than or equal to 0 we will return NULL.
    • MySQL | LOG method
      MySQL's LOG(~) method returns the specified base logarithm of an input number (X). If we provide an input less than or equal to 0 we will return NULL.
    • MySQL | LOG10 method
      MySQL's LOG10(~) method returns the base-10 logarithm of the input number. If we provide an input less than or equal to 0 we will return NULL.
    • MySQL | LOG2 method
      MySQL's LOG2(~) method returns the base-2 logarithm of the input number. If we provide an input less than or equal to 0 we will return NULL.
    • MySQL | MOD method
      MySQL's MOD(~) method returns the remainder of one number (N) divided by another number (M).
    • MySQL | PI method
      MySQL's PI() method returns the value of π (pi). The default precision is 7 decimal places, however, the double-precision value is stored internally for calculations.
    • MySQL | POW method
      MySQL's POW(~) method returns the value of X raised to the power of Y.
    • MySQL | POWER method
      MySQL's POWER(~) method returns the value of X raised to the power of Y.
    • MySQL | RADIANS method
      MySQL's RADIANS(~) method converts the input degrees into radians and returns it.
    • MySQL | RAND method
      MySQL's RAND(~) method returns a random float value between 0 (inclusive) and 1 (exclusive).
    • MySQL | ROUND method
      MySQL's ROUND(~) method returns the input argument rounded to the specified number of decimal places.
    • MySQL | SIGN method
      MySQL's SIGN(~) method returns the sign of the argument.
    • MySQL | SIN method
      MySQL's SIN(~) method computes the sine of the input value in radians.
    • MySQL | SQRT method
      MySQL's SQRT(~) method returns the square root of a nonnegative number.
    • MySQL | TAN method
      MySQL's TAN(~) method computes the tangent of the input value in radians.
    • MySQL | TRUNCATE method
      MySQL's TRUNCATE(~) method returns the input number truncated to D decimal places.
  • Regular Expression

    • MySQL | REGEXP_INSTR method
      MySQL's REGEXP_INSTR(~) method returns the starting index of the substring matching the regular expression (pat).
    • MySQL | REGEXP_LIKE method
      MySQL's REGEXP_LIKE(~) method returns 1 if the input string matches the regular expression pattern. If there is no match 0 is returned.
    • MySQL | REGEXP_REPLACE method
      MySQL's REGEXP_REPLACE(~) method returns the input string (expr) with all occurrences that match the regular expression (pat) replaced by the new substring (repl).
  • String

    • MySQL | ASCII method
      MySQL's ASCII(~) method returns the ASCII numeric value of the leftmost character in the provided input string.
    • MySQL | BIN method
      MySQL's BIN(~) method returns a string representation of the binary (base 2 format) of the input number.
    • MySQL | BIT_LENGTH method
      MySQL's BIT_LENGTH(~) method returns the length of a string in bits.
    • MySQL | CHAR method
      MySQL's CHAR(~) method returns a binary string representation of the provided integer code values.
    • MySQL | CHARACTER_LENGTH method
      MySQL's CHARACTER_LENGTH(~) method returns the length of the input string measured in characters. It is synonymous with method CHAR_LENGTH.
    • MySQL | CHAR_LENGTH method
      MySQL's CHAR_LENGTH(~) method returns the length of the input string measured in characters. It is synonymous with  method CHARACTER_LENGTH.
    • MySQL | CONCAT method
      MySQL's CONCAT(~) method returns a concatenated string from the string inputs.
    • MySQL | CONCAT_WS method
      MySQL's CONCAT_WS(~) method concatenates input strings using the specified separator.
    • MySQL | ELT method
      MySQL's ELT(~) method returns the Nth element from the list of provided strings.
    • MySQL | EXPORT_SET method
      MySQL's EXPORT_SET(~) method returns a string such that for every set bit (bit value 1), we append the specified on string and for every unset bit (bit value 0), we append the specified off string.
    • MySQL | FIELD method
      MySQL's FIELD(~) method returns the position of a string in a list of strings.
    • MySQL | FIND_IN_SET method
      MySQL's FIND_IN_SET(~) method returns the position of the first occurrence of a string in a string list.
    • MySQL | FORMAT method
      MySQL's FORMAT(~) method returns a number formatted with thousands separator and rounded to the specified number of decimal places (D).
    • MySQL | FROM_BASE64 method
      MySQL's FROM_BASE64(~) method decodes a base64 encoded string and returns the resulting binary string.
    • MySQL | HEX method
      MySQL's HEX(~) method returns a hexadecimal representation of the input argument.
    • MySQL | INSERT method
      MySQL's INSERT(~) method inserts and replaces the specified number of characters (len) from a substring (newstr) at the specified position (pos) in the destination string (str).
    • MySQL | INSTR method
      MySQL's INSTR(~) method returns the position of the first occurrence of a substring in the input string.
    • MySQL | LCASE method
      MySQL's LCASE(~) method converts the string input to lowercase and returns it.
    • MySQL | LEFT method
      MySQL's LEFT(~) method returns a substring with the specified number of characters (len) from the left of the input string.
    • MySQL | LENGTH method
      MySQL's LENGTH(~) method returns the length of a string in bytes.
    • MySQL | LOAD_FILE method
      MySQL's LOAD_FILE(~) method reads the specified file and returns its contents as a string.
    • MySQL | LOCATE method
      MySQL's LOCATE(~) method returns the position of the first occurrence of a substring (substr) in the input string (str) starting from the specified position (pos).
    • MySQL | LOWER method
      MySQL's LOWER(~) method converts the string input to lowercase and returns it.
    • MySQL | LPAD method
      MySQL's LPAD(~) method returns the string argument, left-padded with the pad string up to a certain length.
    • MySQL | LTRIM method
      MySQL's LTRIM(~) method returns the input string with leading space characters removed.
    • MySQL | MAKE_SET method
      MySQL's MAKE_SET(~) method returns a set of comma-separated substrings that have the corresponding bit in the provided bit value(s).
    • MySQL | MID method
      MySQL's MID(~) method returns a substring from the input string (str) starting at the specified position (pos) with character length (len).
    • MySQL | OCT method
      MySQL's OCT(~) method returns a string representation of the octal value (base 8) of the input number.
    • MySQL | OCTET_LENGTH method
      MySQL's OCTET_LENGTH(~) method returns the length of a string in bytes.
    • MySQL | ORD method
      MySQL's ORD(~) method returns the character code (numeric value) for the leftmost character of the argument.
    • MySQL | POSITION method
      MySQL's POSITION(~) method returns the position of the first occurrence of a substring in the source string.
    • MySQL | QUOTE method
      MySQL's QUOTE(~) method returns a string enclosed in single quotation marks that is properly escaped. Each instance of backslash (\), single quote ('), ASCII NUL, and Control Z is preceded by a backslash.
    • MySQL | REPEAT method
      MySQL's REPEAT(~) method returns the input string repeated count times.
    • MySQL | REPLACE method
      MySQL's REPLACE(~) method returns the input string (str) with all occurrences of the specified substring (from_str) replaced by the new substring (to_str).
    • MySQL | REVERSE method
      MySQL's REVERSE(~) method returns the input string with the order of the characters reversed.
    • MySQL | RIGHT method
      MySQL's RIGHT(~) method returns a substring with the specified number of characters (len) from the right of the input string.
    • MySQL | RPAD method
      MySQL's RPAD(~) method returns the string argument, right-padded with the pad string up to a certain length.
    • MySQL | RTRIM method
      MySQL's RTRIM(~) method returns the input string with trailing space characters removed.
    • MySQL | SOUNDEX method
      MySQL's SOUNDEX(~) method returns a soundex string from the input string.
    • MySQL | SPACE method
      MySQL's SPACE(~) method returns a string of the specified number of space characters.
    • MySQL | STRCMP method
      MySQL's STRCMP(~) method compares two strings and returns one of 0, -1, 1 depending on the result of the comparison.
    • MySQL | SUBSTR method
      MySQL's SUBSTR(~) method returns a substring from the input string starting at the specified position.
    • MySQL | SUBSTRING method
      MySQL's SUBSTRING(~) method returns a substring from the input string starting at the specified position.
    • MySQL | SUBSTRING_INDEX method
      MySQL's SUBSTRING_INDEX(~) method returns the substring before the specified number of occurrences of a particular delimiter (delim) in the input source string (str).
    • MySQL | TO_BASE64 method
      MySQL's TO_BASE64(~) method converts the input string to base64 and returns it as a character string.
    • MySQL | TRIM method
      MySQL's TRIM(~) method returns the input string with the specified leading and/or trailing string removed.
    • MySQL | UCASE method
      MySQL's UCASE(~) method converts the input string to uppercase and returns it.
    • MySQL | UNHEX method
      MySQL's UNHEX(~) method returns a binary string from the hexadecimal string representation. For strings, UNHEX(~) is the inverse of the HEX(~) method.
    • MySQL | UPPER method
      MySQL's UPPER(~) method converts the input string to uppercase and returns it.
    • MySQL | WEIGHT_STRING method
      MySQL's WEIGHT_STRING(~) method returns a binary string representing the weight string for the given input.

Operators

  • MySQL | AND
    MySQL's AND operator can be used in conjunction with a WHERE clause to return values that satisfy all the conditions separated by AND in the WHERE clause.
  • MySQL | BETWEEN
    MySQL's BETWEEN operator returns values within a given range. It is inclusive of the start and end values, and it can be used with numbers, strings and dates.
  • MySQL | BINARY
    MySQL's BINARY operator converts an expression to a binary string.
  • MySQL | CASE
    MySQL's CASE operator allows us to add multiple conditions to our query and return a value for the first condition met.
  • MySQL | DIV
    MySQL's DIV operator performs integer division, discarding any fractional part of the division result.
  • MySQL | IN
    MySQL's IN operator allows you to combine multiple OR conditions elegantly. If a value is equal to any of the values provided in the IN list it returns 1 (true), otherwise it returns 0 (false).
  • MySQL | IS
    MySQL's IS operator performs a test against a boolean value and returns 1 if the test evaluates to true, 0 otherwise.
  • MySQL | IS NOT
    MySQL's IS NOT operator performs a test against a boolean value and returns 0 if the test evaluates to true, otherwise it returns 1.
  • MySQL | IS NOT NULL
    MySQL's IS NOT NULL operator tests whether a value is not NULL. It returns 1 if the value is not NULL and 0 if the value is NULL.
  • MySQL | IS NULL
    MySQL's IS NULL operator tests whether a value is NULL. It returns 0 if the value is not NULL and 1 if the value is NULL.
  • MySQL | LIKE
    MySQL's LIKE operator can be used to search for values with a particular string pattern. There are two wildcards that we can use with the LIKE operator: % (percent) and _ (underscore).
  • MySQL | NOT
    MySQL's NOT operator can be used in conjunction with a WHERE clause to return values that do not satisfy a particular condition in the WHERE clause.
  • MySQL | NOT BETWEEN
    MySQL's NOT BETWEEN operator returns values that are not within a given range. The start and end values are considered part of the range, hence are not included in the result of NOT BETWEEN.
  • MySQL | NOT IN
    MySQL's NOT IN operator returns 1 (true) if the value is not equal to any of the values provided in the NOT IN list, otherwise it returns 0 (false).
  • MySQL | NOT LIKE
    MySQL's NOT LIKE operator can be used to search for values that do not match a particular string pattern. There are two wildcards that we can use with the NOT LIKE operator: % (percent) and _ (underscore).
  • MySQL | NOT REGEXP
    MySQL's NOT REGEXP operator returns 1 if the expression string does not match the regular expression pattern. If there is a match 0 is returned.
  • MySQL | OR
    MySQL's OR operator can be used in conjunction with a WHERE clause to return values that satisfy at least one of the conditions separated by OR in the WHERE clause.
  • MySQL | REGEX
    MySQL's REGEXP operator returns 1 if the expression string matches the regular expression pattern. If there is no match 0 is returned.
  • MySQL | RLIKE
    MySQL's RLIKE operator returns 1 if a string (expr) matches the regular expression pattern (pat). If there is no match 0 is returned.
  • MySQL | SOUNDS LIKE
    MySQL's SOUNDS LIKE operator returns 1 if two string expressions sound alike or 0 if they do not.
  • MySQL | XOR
    MySQL's XOR operator returns 1 if an odd number of operands is nonzero or 0 if an even number of operands is nonzero. If either operand is NULL we return NULL.

Statements and Clauses

  • MySQL | AS
    MySQL's AS keyword is used to rename or provide an alias for a column or table.
  • MySQL | DELETE
    MySQL's DELETE statement is used to delete existing records in a table.
  • MySQL | GROUP BY
    MySQL's GROUP BY clause allows you to group identical entries in a column into groups. GROUP BY will return one record for each group.
  • MySQL | HAVING
    MySQL's HAVING clause is used to specify a condition involving aggregate functions. It is used in GROUP BY queries, and specifies a condition upon the grouped results.
  • MySQL | INNER JOIN
    The INNER JOIN clause retrieves rows from both tables where there is a common matching column value.
  • MySQL | INSERT INTO
    MySQL's INSERT INTO statement allows you to add new rows to a table.
  • My SQL | LEFT JOIN
    MySQL's LEFT JOIN clause returns all rows from the left table and records that match from the right table. NULL will be used for records that have no matching record in the right table. LEFT JOIN is also referred to as LEFT OUTER JOIN.
  • MySQL | LIMIT
    MySQL's LIMIT clause limits the number of returned results.
  • MySQL | ORDER BY
    MySQL's ORDER BY clause sorts the retrieved records and returns them in ascending order by default. We can specify to sort in descending order if needed.
  • MySQL | RIGHT JOIN
    MySQL's RIGHT JOIN clause returns all rows from the right table and matching records from the left table. NULL will be used for records that have no matching record in the left table. RIGHT JOIN is also referred to as RIGHT OUTER JOIN.
  • MySQL | SELECT
    MySQL's SELECT statement allows us to retrieve records from a database.
  • MySQL | SELECT DISTINCT
    The SELECT DISTINCT statement returns values that are distinct.
  • MySQL | TRUNCATE
    MySQL's TRUNCATE statement drops a table and all its records and then recreates it as an empty table. It is similar to a DELETE statement that deletes all rows of a table (i.e. DELETE statement without a WHERE clause).
  • MySQL | UNION
    MySQL's UNION clause combines the results from multiple SELECT statements into a single result set.
  • MySQL | UPDATE
    The UPDATE statement is used to modify existing records in a table.
  • MySQL | WHERE
    The WHERE clause allows us to add conditions to our query. Only records that meet our condition will be retrieved.
  • Cookbooks

    • Difference between HAVING and WHERE in MySQL
      HAVING specifies what condition should be applied after the aggregation takes place, while WHERE specifies what records are eligible for grouping or aggregation in the first place. If a record does not meet the WHERE condition, it will not even be considered for GROUP BY and HAVING.
    • Difference between INNER JOIN, LEFT JOIN and RIGHT JOIN in MySQL
      MySQL's INNER JOIN clause retrieves rows from both tables where there is a common matching column value. The LEFT JOIN clause returns all rows from the left table and records that match from the right table. NULL will be used for records that have no matching record in the right table. The RIGHT JOIN clause returns all rows from the right table and matching records from the left table. NULL will be used for records that have no matching record in the left table.
    • Order By 1 in MySQL
      The ORDER BY 1 clause in MySQL translates in plain English to order by the first column selected in the SELECT statement. This syntax is not considered good practice as a change in the order of columns could lead to unintentionally ordering by the wrong column.
    • Retrieving a fixed number of rows from a table in MySQL
      We can use a LIMIT clause to only retrieve a fixed number of rows from a table in MySQL.
    • Retrieving all the data from a table in MySQL
      This can be done using the SELECT statement combined with wildcard (*).
    • Retrieving columns in a specific order in MySQL
      This can be done using the SELECT statement followed by the list of columns you want in the order you want.
    • Retrieving distinct records in MySQL
      It is possible to retrieve distinct records from a table using the SELECT DISTINCT statement.
    • Sorting records in alphabetical order in MySQL
      We can retrieve records from a table in alphabetical order in MySQL using the ORDER BY clause.
mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Isshin Inada
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!