Showing Page:
1/7
MySQL Data Types
It is MySQL is a very popular was first released in 1995 open-source relational database
management system (RDBMS). MySQL is very fast, reliable, scalable, and easy to use. It is ideal
for both small and large applications. compliant with the ANSI SQL standard and it is is developed,
distributed, and supported by Oracle Corporation.
Who Uses MySQL?
Huge websites like Facebook, Twitter, Airbnb, Booking.com, Uber, GitHub, YouTube, etc.
Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc.
A very large number of web developers around the world .
An SQL developer must decide what type of data that will be stored inside each column when
creating a table. The data type is a guideline for SQL to understand what type of data is expected
inside of each column, and it also identifies how SQL will interact with the stored data.
String Data Types
Data type
Description
CHAR(size)
A FIXED length string (can contain letters, numbers, and special characters).
The size parameter specifies the column length in characters - can be from 0
to 255. Default is 1
VARCHAR(size)
A VARIABLE length string (can contain letters, numbers, and special
characters). The size parameter specifies the maximum column length in
characters - can be from 0 to 65535
BINARY(size)
Equal to CHAR(), but stores binary byte strings. The size parameter specifies
the column length in bytes. Default is 1
VARBINARY(size)
Equal to VARCHAR(), but stores binary byte strings. The size parameter
specifies the maximum column length in bytes.
TINYBLOB
For BLOBs (Binary Large OBjects). Max length: 255 bytes
TINYTEXT
Holds a string with a maximum length of 255 characters
TEXT(size)
Holds a string with a maximum length of 65,535 bytes
BLOB(size)
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(val1, val2,
val3, ...)
A string object that can have only one value, chosen from a list of possible
values. You can list up to 65535 values in an ENUM list. If a value is inserted
that is not in the list, a blank value will be inserted. The values are sorted in
the order you enter them
SET(val1, val2,
val3, ...)
A string object that can have 0 or more values, chosen from a list of possible
values. You can list up to 64 values in a SET list
Showing Page:
2/7
Numeric Data Types
Data type
Description
BIT(size)
A bit-value type. The number of bits per value is specified in size. The size
parameter can hold a value from 1 to 64. The default value for size is 1.
TINYINT(size)
A very small integer. Signed range is from -128 to 127. Unsigned range is from
0 to 255. The size parameter specifies the maximum display width (which is
255)
BOOL
Zero is considered as false, nonzero values are considered as true.
BOOLEAN
Equal to BOOL
SMALLINT(size)
A small integer. Signed range is from -32768 to 32767. Unsigned range is from
0 to 65535. The size parameter specifies the maximum display width (which is
255)
MEDIUMINT(size
)
A medium integer. Signed range is from -8388608 to 8388607. Unsigned range
is from 0 to 16777215. The size parameter specifies the maximum display
width (which is 255)
INT(size)
A medium integer. Signed range is from -2147483648 to 2147483647.
Unsigned range is from 0 to 4294967295. The size parameter specifies the
maximum display width (which is 255)
INTEGER(size)
Equal to INT(size)
BIGINT(size)
A large integer. Signed range is from -9223372036854775808 to
9223372036854775807. Unsigned range is from 0 to 18446744073709551615.
The size parameter specifies the maximum display width (which is 255)
FLOAT(size, d)
A floating point number. The total number of digits is specified in size. The
number of digits after the decimal point is specified in the d parameter. This
syntax is deprecated in MySQL 8.0.17, and it will be removed in future
MySQL versions
FLOAT(p)
A floating point number. MySQL uses the p value to determine whether to use
FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data
type becomes FLOAT(). If p is from 25 to 53, the data type becomes
DOUBLE()
DOUBLE(size, d)
A normal-size floating point number. The total number of digits is specified in
size. The number of digits after the decimal point is specified in the d
parameter
DOUBLE
PRECISION(size,
d)
DECIMAL(size, d)
An exact fixed-point number. The total number of digits is specified in size.
The number of digits after the decimal point is specified in the d parameter.
The maximum number for size is 65. The maximum number for d is 30. The
default value for size is 10. The default value for d is 0.
DEC(size, d)
Equal to DECIMAL(size,d)
Showing Page:
3/7
Date and Time Data Types
Data type
Description
DATE
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to
'9999-12-31'
DATETIME(fsp
)
A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported
range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT
and ON UPDATE in the column definition to get automatic initialization and
updating to the current date and time
TIMESTAMP(f
sp)
A timestamp. TIMESTAMP values are stored as the number of seconds since the
Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss.
The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07'
UTC. Automatic initialization and updating to the current date and time can be
specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE
CURRENT_TIMESTAMP in the column definition
TIME(fsp)
A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
YEAR
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and
0000.
MySQL 8.0 does not support year in two-digit format.
MySQL String Functions
Function
ASCII
CHAR_LENGTH
CHARACTER_LENG
TH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
Showing Page:
4/7
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
MySQL Numeric Functions
Function
Description
ABS
Returns the absolute value of a number
ACOS
Returns the arc cosine of a number
ASIN
Returns the arc sine of a number
ATAN
Returns the arc tangent of one or two numbers
ATAN2
Returns the arc tangent of two numbers
AVG
Returns the average value of an expression
CEIL
Returns the smallest integer value that is >= to a number
CEILING
Returns the smallest integer value that is >= to a number
COS
Returns the cosine of a number
COT
Returns the cotangent of a number
COUNT
Returns the number of records returned by a select query
DEGREES
Converts a value in radians to degrees
DIV
Used for integer division
EXP
Returns e raised to the power of a specified number
FLOOR
Returns the largest integer value that is <= to a number
GREATEST
Returns the greatest value of the list of arguments
LEAST
Returns the smallest value of the list of arguments
LN
Returns the natural logarithm of a number
LOG
Returns the natural logarithm of a number, or the logarithm of a number to a
specified base
LOG10
Returns the natural logarithm of a number to base 10
LOG2
Returns the natural logarithm of a number to base 2
Showing Page:
5/7
MAX
Returns the maximum value in a set of values
MIN
Returns the minimum value in a set of values
MOD
Returns the remainder of a number divided by another number
PI
Returns the value of PI
POW
Returns the value of a number raised to the power of another number
POWER
Returns the value of a number raised to the power of another number
RADIANS
Converts a degree value into radians
RAND
Returns a random number
ROUND
Rounds a number to a specified number of decimal places
SIGN
Returns the sign of a number
SIN
Returns the sine of a number
SQRT
Returns the square root of a number
SUM
Calculates the sum of a set of values
TAN
Returns the tangent of a number
TRUNCATE
Truncates a number to the specified number of decimal places
MySQL Date Functions
Function
Description
ADDDATE
Adds a time/date interval to a date and then returns the date
ADDTIME
Adds a time interval to a time/datetime and then returns the
time/datetime
CURDATE
Returns the current date
CURRENT_DATE
Returns the current date
CURRENT_TIME
Returns the current time
CURRENT_TIMESTAMP
Returns the current date and time
CURTIME
Returns the current time
DATE
Extracts the date part from a datetime expression
DATEDIFF
Returns the number of days between two date values
DATE_ADD
Adds a time/date interval to a date and then returns the date
DATE_FORMAT
Formats a date
DATE_SUB
Subtracts a time/date interval from a date and then returns the date
DAY
Returns the day of the month for a given date
DAYNAME
Returns the weekday name for a given date
DAYOFMONTH
Returns the day of the month for a given date
DAYOFWEEK
Returns the weekday index for a given date
DAYOFYEAR
Returns the day of the year for a given date
EXTRACT
Extracts a part from a given date
FROM_DAYS
Returns a date from a numeric datevalue
HOUR
Returns the hour part for a given date
LAST_DAY
Extracts the last day of the month for a given date
LOCALTIME
Returns the current date and time
LOCALTIMESTAMP
Returns the current date and time
Showing Page:
6/7
MAKEDATE
Creates and returns a date based on a year and a number of days value
MAKETIME
Creates and returns a time based on an hour, minute, and second value
MICROSECOND
Returns the microsecond part of a time/datetime
MINUTE
Returns the minute part of a time/datetime
MONTH
Returns the month part for a given date
MONTHNAME
Returns the name of the month for a given date
NOW
Returns the current date and time
PERIOD_ADD
Adds a specified number of months to a period
PERIOD_DIFF
Returns the difference between two periods
QUARTER
Returns the quarter of the year for a given date value
SECOND
Returns the seconds part of a time/datetime
SEC_TO_TIME
Returns a time value based on the specified seconds
STR_TO_DATE
Returns a date based on a string and a format
SUBDATE
Subtracts a time/date interval from a date and then returns the date
SUBTIME
Subtracts a time interval from a datetime and then returns the
time/datetime
SYSDATE
Returns the current date and time
TIME
Extracts the time part from a given time/datetime
TIME_FORMAT
Formats a time by a specified format
TIME_TO_SEC
Converts a time value into seconds
TIMEDIFF
Returns the difference between two time/datetime expressions
TIMESTAMP
Returns a datetime value based on a date or datetime value
TO_DAYS
Returns the number of days between a date and date "0000-00-00"
WEEK
Returns the week number for a given date
WEEKDAY
Returns the weekday number for a given date
WEEKOFYEAR
Returns the week number for a given date
YEAR
Returns the year part for a given date
YEARWEEK
Returns the year and week number for a given date
MySQL Advanced Functions
Function
Description
BIN
Returns a binary representation of a number
BINARY
Converts a value to a binary string
CASE
Goes through conditions and return a value when the first condition is met
CAST
Converts a value (of any type) into a specified datatype
COALESCE
Returns the first non-null value in a list
CONNECTION_I
D
Returns the unique connection ID for the current connection
CONV
Converts a number from one numeric base system to another
CONVERT
Converts a value into the specified datatype or character set
CURRENT_USER
Returns the user name and host name for the MySQL account that the server
used to authenticate the current client
DATABASE
Returns the name of the current database
Showing Page:
7/7
IF
Returns a value if a condition is TRUE, or another value if a condition is
FALSE
IFNULL
Return a specified value if the expression is NULL, otherwise return the
expression
ISNULL
Returns 1 or 0 depending on whether an expression is NULL
LAST_INSERT_I
D
Returns the AUTO_INCREMENT id of the last row that has been inserted or
updated in a table
NULLIF
Compares two expressions and returns NULL if they are equal. Otherwise, the
first expression is returned
SESSION_USER
Returns the current MySQL user name and host name
SYSTEM_USER
Returns the current MySQL user name and host name
USER
Returns the current MySQL user name and host name
VERSION
Returns the current version of the MySQL database

Unformatted Attachment Preview

MySQL Data Types It is MySQL is a very popular was first released in 1995 open-source relational database management system (RDBMS). MySQL is very fast, reliable, scalable, and easy to use. It is ideal for both small and large applications. compliant with the ANSI SQL standard and it is is developed, distributed, and supported by Oracle Corporation. Who Uses MySQL? • Huge websites like Facebook, Twitter, Airbnb, Booking.com, Uber, GitHub, YouTube, etc. • Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc. • A very large number of web developers around the world . An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data. String Data Types Data type Description A FIXED length string (can contain letters, numbers, and special characters). CHAR(size) The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1 A VARIABLE length string (can contain letters, numbers, and special VARCHAR(size) characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535 Equal to CHAR(), but stores binary byte strings. The size parameter specifies BINARY(size) the column length in bytes. Default is 1 Equal to VARCHAR(), but stores binary byte strings. The size parameter VARBINARY(size) specifies the maximum column length in bytes. TINYBLOB For BLOBs (Binary Large OBjects). Max length: 255 bytes TINYTEXT Holds a string with a maximum length of 255 characters TEXT(size) Holds a string with a maximum length of 65,535 bytes BLOB(size) For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data A string object that can have only one value, chosen from a list of possible ENUM(val1, val2, values. You can list up to 65535 values in an ENUM list. If a value is inserted val3, ...) that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them SET(val1, val2, A string object that can have 0 or more values, chosen from a list of possible val3, ...) values. You can list up to 64 values in a SET list Numeric Data Types Data type Description A bit-value type. The number of bits per value is specified in size. The size BIT(size) parameter can hold a value from 1 to 64. The default value for size is 1. A very small integer. Signed range is from -128 to 127. Unsigned range is from TINYINT(size) 0 to 255. The size parameter specifies the maximum display width (which is 255) BOOL Zero is considered as false, nonzero values are considered as true. BOOLEAN Equal to BOOL A small integer. Signed range is from -32768 to 32767. Unsigned range is from SMALLINT(size) 0 to 65535. The size parameter specifies the maximum display width (which is 255) A medium integer. Signed range is from -8388608 to 8388607. Unsigned range MEDIUMINT(size is from 0 to 16777215. The size parameter specifies the maximum display ) width (which is 255) A medium integer. Signed range is from -2147483648 to 2147483647. INT(size) Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) INTEGER(size) Equal to INT(size) A large integer. Signed range is from -9223372036854775808 to BIGINT(size) 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This FLOAT(size, d) syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data FLOAT(p) type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() A normal-size floating point number. The total number of digits is specified in DOUBLE(size, d) size. The number of digits after the decimal point is specified in the d parameter DOUBLE PRECISION(size, d) An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. DECIMAL(size, d) The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. DEC(size, d) Equal to DECIMAL(size,d) Date and Time Data Types Data type Description A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to DATE '9999-12-31' A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported DATETIME(fsp range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT ) and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. TIMESTAMP(f The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' sp) UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition TIME(fsp) A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59' A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and YEAR 0000. MySQL 8.0 does not support year in two-digit format. MySQL String Functions Function ASCII CHAR_LENGTH CHARACTER_LENG TH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION Description Returns the ASCII value for the specific character Returns the length of a string (in characters) Returns the length of a string (in characters) Adds two or more expressions together Adds two or more expressions together with a separator Returns the index position of a value in a list of values Returns the position of a string within a list of strings Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places Inserts a string within a string at the specified position and for a certain number of characters Returns the position of the first occurrence of a string in another string Converts a string to lower-case Extracts a number of characters from a string (starting from left) Returns the length of a string (in bytes) Returns the position of the first occurrence of a substring in a string Converts a string to lower-case Left-pads a string with another string, to a certain length Removes leading spaces from a string Extracts a substring from a string (starting at any position) Returns the position of the first occurrence of a substring in a string Repeats a string as many times as specified Replaces all occurrences of a substring within a string, with a new REPLACE substring REVERSE Reverses a string and returns the result RIGHT Extracts a number of characters from a string (starting from right) RPAD Right-pads a string with another string, to a certain length RTRIM Removes trailing spaces from a string SPACE Returns a string of the specified number of space characters STRCMP Compares two strings SUBSTR Extracts a substring from a string (starting at any position) SUBSTRING Extracts a substring from a string (starting at any position) Returns a substring of a string before a specified number of delimiter SUBSTRING_INDEX occurs TRIM Removes leading and trailing spaces from a string UCASE Converts a string to upper-case UPPER Converts a string to upper-case REPEAT MySQL Numeric Functions Function ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 Description Returns the absolute value of a number Returns the arc cosine of a number Returns the arc sine of a number Returns the arc tangent of one or two numbers Returns the arc tangent of two numbers Returns the average value of an expression Returns the smallest integer value that is >= to a number Returns the smallest integer value that is >= to a number Returns the cosine of a number Returns the cotangent of a number Returns the number of records returned by a select query Converts a value in radians to degrees Used for integer division Returns e raised to the power of a specified number Returns the largest integer value that is <= to a number Returns the greatest value of the list of arguments Returns the smallest value of the list of arguments Returns the natural logarithm of a number Returns the natural logarithm of a number, or the logarithm of a number to a specified base Returns the natural logarithm of a number to base 10 Returns the natural logarithm of a number to base 2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Returns the maximum value in a set of values Returns the minimum value in a set of values Returns the remainder of a number divided by another number Returns the value of PI Returns the value of a number raised to the power of another number Returns the value of a number raised to the power of another number Converts a degree value into radians Returns a random number Rounds a number to a specified number of decimal places Returns the sign of a number Returns the sine of a number Returns the square root of a number Calculates the sum of a set of values Returns the tangent of a number Truncates a number to the specified number of decimal places MySQL Date Functions Function ADDDATE Description Adds a time/date interval to a date and then returns the date Adds a time interval to a time/datetime and then returns the ADDTIME time/datetime CURDATE Returns the current date CURRENT_DATE Returns the current date CURRENT_TIME Returns the current time CURRENT_TIMESTAMP Returns the current date and time CURTIME Returns the current time DATE Extracts the date part from a datetime expression DATEDIFF Returns the number of days between two date values DATE_ADD Adds a time/date interval to a date and then returns the date DATE_FORMAT Formats a date DATE_SUB Subtracts a time/date interval from a date and then returns the date DAY Returns the day of the month for a given date DAYNAME Returns the weekday name for a given date DAYOFMONTH Returns the day of the month for a given date DAYOFWEEK Returns the weekday index for a given date DAYOFYEAR Returns the day of the year for a given date EXTRACT Extracts a part from a given date FROM_DAYS Returns a date from a numeric datevalue HOUR Returns the hour part for a given date LAST_DAY Extracts the last day of the month for a given date LOCALTIME Returns the current date and time LOCALTIMESTAMP Returns the current date and time MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Creates and returns a date based on a year and a number of days value Creates and returns a time based on an hour, minute, and second value Returns the microsecond part of a time/datetime Returns the minute part of a time/datetime Returns the month part for a given date Returns the name of the month for a given date Returns the current date and time Adds a specified number of months to a period Returns the difference between two periods Returns the quarter of the year for a given date value Returns the seconds part of a time/datetime Returns a time value based on the specified seconds Returns a date based on a string and a format Subtracts a time/date interval from a date and then returns the date Subtracts a time interval from a datetime and then returns the time/datetime Returns the current date and time Extracts the time part from a given time/datetime Formats a time by a specified format Converts a time value into seconds Returns the difference between two time/datetime expressions Returns a datetime value based on a date or datetime value Returns the number of days between a date and date "0000-00-00" Returns the week number for a given date Returns the weekday number for a given date Returns the week number for a given date Returns the year part for a given date Returns the year and week number for a given date MySQL Advanced Functions Function Description Returns a binary representation of a number Converts a value to a binary string Goes through conditions and return a value when the first condition is met Converts a value (of any type) into a specified datatype Returns the first non-null value in a list BIN BINARY CASE CAST COALESCE CONNECTION_I Returns the unique connection ID for the current connection D CONV Converts a number from one numeric base system to another CONVERT Converts a value into the specified datatype or character set Returns the user name and host name for the MySQL account that the server CURRENT_USER used to authenticate the current client DATABASE Returns the name of the current database Returns a value if a condition is TRUE, or another value if a condition is FALSE Return a specified value if the expression is NULL, otherwise return the IFNULL expression ISNULL Returns 1 or 0 depending on whether an expression is NULL LAST_INSERT_I Returns the AUTO_INCREMENT id of the last row that has been inserted or D updated in a table Compares two expressions and returns NULL if they are equal. Otherwise, the NULLIF first expression is returned SESSION_USER Returns the current MySQL user name and host name SYSTEM_USER Returns the current MySQL user name and host name USER Returns the current MySQL user name and host name VERSION Returns the current version of the MySQL database IF Name: Description: ...
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4