→ MySQL – Functions, Administrative Commands, Datatypes → Start Learning / Download MP3 Flashcards

Question		Answer
Returns the ASCII value for the specific character start learning		ASCII(text)
Returns the length of a string (in characters) start learning		CHARACTER_LENGTH(text) =CHAR_LENGTH(text)
Concatenates two or more expressions together start learning		CONCAT(arg1, arg2, ...)
Concatenates two or more expressions together with a separator start learning		CONCAT_WS(sep, arg1, arg2, ...)
Returns the index position of a value in a list of values start learning		FIELD(arg1, arg2, ...)
Returns the position of a string within A LIST OF strings start learning		FIND_IN_SET(searched_string, s1, s2, s3, ...)
Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places FORMAT(12678.90, 2) start learning		FORMAT(number, decimals) "12,678,90"
Inserts a string within a string at the specified position and for a certain number of characters start learning		INSERT(text, position, n, s) n − number of characters to replace
Returns the position of the first occurrence of a string in another string start learning		INSTR(text, s)
Converts a string to lower-case start learning		LCASE(text) =LOWER(text)
Extracts a number of characters from a string (starting from left) start learning		LEFT(text, n)
Returns the length of a string (in bytes) start learning		LENGTH(text)
Returns the position of the first occurrence of a substring in a string start learning		LOCATE(s, text, start) =POSITION(...)
Left-pads a string with another string, to a certain length start learning		LPAD(text, width, s) Corresponds Python 3 rjust()
Removes leading spaces from a string start learning		LTRIM(text)
Repeats a string as many times as specified start learning		REPEAT(text, n)
Replaces all occurrences of a substring within a string, with a new substring start learning		REPLACE(text, s1, s2) Similar to Python's 3.
Reverses a string and returns the result start learning		REVERSE(text)
Extracts a number of characters from a string (starting from right) start learning		RIGHT(text, n)
Right-pads a string with another string, to a certain length start learning		RPAD(text, width, s)
Removes trailing spaces from a string start learning		RTRIM(text)
Returns a string of the specified number of spaces. start learning		SPACE(number)
Compares two strings start learning		STRCMP(s1, s2) returns -1 or 0 or 1
Extracts a substring from a string (starting at any position) start learning		SUBSTRING(text, start, len) =SUBSTR(...)=MID(...)
Returns a substring of a string before a specified number of DELIMITER occurs start learning		SUBSTRING_INDEX(text, delimiter, quantity)
Removes leading and trailing spaces from a string start learning		TRIM(text)
Converts a string to upper-case start learning		UCASE(text) =UPPER(text)
Adds a time/date interval to a date and then returns the date start learning		ADDDATE(date/datetime, interval) adddate(current_date(), INTERVAL 3 minute)
Adds a time interval to a time/datetime and then returns the time/datetime start learning		ADDTIME(time/datetime, interval)
Returns the current date start learning		CURRENT_DATE() =CURDATE()
Returns the current time start learning		CURRENT_TIME() =CURTIME()
Extracts the date part from a datetime expression start learning		DATE(datetime)
Returns the number of days between two date values start learning		DATEDIFF(date1, date2)
Adds a time/date interval to a date and then returns the date start learning		DATE_ADD(date, interval)
Formats a date start learning		DATE_FORMAT(date, format_specifier)
Subtracts a time/date interval from a date and then returns the date start learning		DATE_SUB(date, interval)
Returns the day of the month for a given date start learning		DAY(date[time])
Returns the weekday NAME for a given date start learning		DAYNAME(date[time])
Returns the day of the month for a given date start learning		DAYOFMONTH(date[time])
Returns the weekday number for a given date start learning		DAYOFWEEK(date[time])
Returns the day of the year for a given date start learning		DAYOFYEAR(date[time])
Extracts a part from a given date start learning		EXTRACT(time_unit FROM date/time)
Returns a date from a numeric datevalue start learning		FROM_DAYS(day_quantity)
Returns the hour part for a given date start learning		HOUR([date]time)
Extracts the last day of the month for a given date start learning		LAST_DAY([date]time)
Returns the current date and time start learning		NOW() LOCALTIMESTAMP(), localtime(), sysdate(), CURRENT_TIMESTAMP()
Creates and returns a date based on a year and a number of days value start learning		MAKEDATE(year, minute, second)
Creates and returns a time based on an hour, minute, and second value start learning		MAKETIME(hour, minute, second)
Returns the microsecond part of a time/datetime start learning		MICROSECOND([date]time)
Returns the minute part of a time/datetime start learning		MINUTE([date]time)
Returns the month part for a given date start learning		MONTH(date[time])
Returns the name of the month for a given date start learning		MONTHNAME(date[time])
Adds a specified number of months to a period start learning		PERIOD_ADD(period, number) SELECT PERIOD_ADD(201703, 15);
Returns the difference between two periods start learning		PERIOD_DIFF(period, number) SELECT PERIOD_DIFF(1703, 1612);
Returns the quarter of the year for a given date value start learning		QUARTER([date]time)
Returns the seconds part of a time/datetime start learning		SECOND([date]time)
Returns a time value based on the specified seconds start learning		SEC_TO_TIME(seconds)
Returns a date based on a string and a format start learning		STR_TO_DATE(text, format_specifier)
Subtracts a time/date interval from a date and then returns the date start learning		SUBDATE(date, interval)
Subtracts a time interval from a datetime and then returns the time/datetime start learning		SUBTIME(time, interval)
Extracts the time part from a given time/datetime start learning		TIME(datetime)
Formats a time by a specified format start learning		TIME_FORMAT(time, format_specifier)
Converts a time value into seconds start learning		TIME_TO_SEC(time)
Returns the difference between two time/datetime expressions start learning		TIMEDIFF(t1, t2)
Returns a datetime value based on a date or datetime value start learning		TIMESTAMP(date[time], [time])
Returns the number of days between a date and date "0000-00-00" start learning		TO_DAYS(date)
Returns the week number for a given date start learning		WEEK(date[time])
Returns the weekday number for a given date start learning		WEEKDAY(date[time)
Returns the week number for a given date start learning		WEEKOFYEAR(date[time)
Returns the year part for a given date start learning		YEAR(date[time)
Returns the year and week number for a given date start learning		YEARWEEK(date[time)
Returns the absolute value of a number start learning		ABS(x)
Returns the arc cosine of a number start learning		ACOS(x)
Returns the arc sine of a number start learning		ASIN(x)
Returns the arc tangent of one or two numbers start learning		ATAN(x)
Returns the arc tangent of two numbers start learning		ATAN2(x, y)
Returns the average value of an expression start learning		AVG(column)
Returns the smallest integer value that is >= to a number start learning		CEILING(x) =CEIL(x)
Returns the cosine of a number start learning		COS(x)
Returns the cotangent of a number start learning		COT(x)
Returns the number of records returned by a select query start learning		COUNT(column)
Converts a value in radians to degrees start learning		DEGREES(x)
Used for integer division start learning		DIV 5 div 2 = 2
Returns e raised to the power of a specified number start learning		EXP(x)
Returns the largest integer value that is <= to a number start learning		FLOOR(x)
Returns the greatest value of the list of arguments start learning		GREATEST(arg1, arg2, ...)
Returns the smallest value of the list of arguments start learning		LEAST(arg1, arg2, ...)
Returns the natural logarithm of a number start learning		LN(x)
Returns the natural logarithm of a number, or the logarithm of a number to a specified base start learning		LOG(base, x) wheras: LOG(x)=LN(x)
Returns the logarithm of a number to base 10 start learning		LOG10(x)
Returns the logarithm of a number to base 2 start learning		LOG2(x)
Returns the maximum value in a set of values start learning		MAX(column)
Returns the minimum value in a set of values start learning		MIN(column)
Returns the remainder of a number divided by another number start learning		x MOD y =MOD(x,y)=x%y
Returns the value of PI start learning		PI()
Returns the value of a number raised to the power of another number start learning		POWER(x, y) POW(x,y)
Converts a degree value into radians start learning		RADIANS(x)
Returns a random number x, where x∈⟨0;1) start learning		RAND() the same as Python 3 random. random()
Rounds a number to a specified number of decimal places start learning		ROUND(x,y)
Returns the sign of a number start learning		SIGN(x)
Returns the sine of a number start learning		SIN(x)
Returns the square root of a number start learning		SQRT(x)
Calculates the sum of a set of values start learning		SUM(column)
Returns the tangent of a number start learning		TAN(x)
Truncates a number to the specified number of decimal places start learning		TRUNCATE(x)
Returns a binary representation of a number start learning		BIN(x) Attention it AIN'T the same as BINARY()
Converts a value to a binary string start learning		BINARY value
Converts a value (of any type) into a specified datatype start learning		CAST(value AS datetype)
Returns the first non-null value in a list start learning		COALESCE(*args)
Returns the unique connection ID for the current connection start learning		CONNECTION_ID()
Converts a number from one numeric base system to another start learning		CONV(n, base, new_base)
Converts a value into the specified datatype or character set start learning		CONVERT(value, datatype)
Returns the name of the current database start learning		DATABASE()
Returns a value if a condition is TRUE, or another value if a condition is FALSE start learning		IF(condition, value1, value2)
Return a specified value if the expression is NULL, otherwise return the expression start learning		IFNULL(value)
Returns 1 or 0 depending on whether an expression is NULL start learning		ISNULL(value)
Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table start learning		LAST_INSERT_ID()
Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned start learning		NULLIF(value, value2)
Returns the current MySQL user name and host name start learning		USER() =CURRENT_USER()=SYSTEM_USER=SESSION_USER
Returns the current version of the MySQL database start learning		VERSION()
This will be used to select a database in the MySQL workarea. start learning		USE Databasename;
Lists out the databases that are accessible by the MySQL DBMS. start learning		SHOW DATABASES;
Shows the tables in the database once a database has been selected with the use command. start learning		SHOW TABLES;
Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table. start learning		SHOW COLUMNS FROM tablename;
Presents the details of all indexes on the table, including the PRIMARY KEY. start learning		SHOW INDEX FROM tablename
Reports details of the MySQL DBMS performance and statistics. start learning		SHOW TABLE STATUS LIKE tablename;
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 start learning		CHAR(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 start learning		VARCHAR(size)
Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1 start learning		BINARY(size)
Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. start learning		VARBINARY(size)
For BLOBs (Binary Large OBjects). Max length: 255 bytes start learning		TINYBLOB
Holds a string with a maximum length of 255 characters start learning		TINYTEXT
Holds a string with a maximum length of 65,535 bytes start learning		TEXT(size)
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data start learning		BLOB(size)
Holds a string with a maximum length of 16,777,215 characters start learning		MEDIUMTEXT
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data start learning		MEDIUMBLOB
Holds a string with a maximum length of 4,294,967,295 characters start learning		LONGTEXT
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data start learning		LONGBLOB
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 start learning		ENUM(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 start learning		SET(val1, val2, val3, ...)
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. start learning		BIT(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) start learning		TINYINT(size)
Zero is considered as false, nonzero values are considered as true. start learning		BOOL
Equal to BOOL start learning		BOOLEAN
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) start learning		SMALLINT(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) start learning		MEDIUMINT(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) start learning		INT(size)
Equal to INT(size) start learning		INTEGER(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) start learning		BIGINT(size)
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 start learning		FLOAT(size, d)
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() start learning		FLOAT(p)
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 start learning		DOUBLE(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. start learning		DECIMAL(size, d)
Equal to DECIMAL(size,d) start learning		DEC(size, d)
To disable negative numbers. start learning		UNSIGNED
To add leading zeros, which make all numbers in the column sustain the same length. start learning		ZEROFILL
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' start learning		DATE
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 start learning		DATETIME(fsp)
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. start learning		TIMESTAMP(fsp)
A time. Format: hh: mm: ss. The supported range is from '-838:59:59' to '838:59:59' start learning		TIME(fsp)
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. start learning		YEAR

MySQL – functions, administrative commands, datatypes

You must sign in to write a comment

More