List of all important functions of mysql which are used frequently
Data Types
Types
- CHAR
- String (0 – 255)
- VARCHAR
- String (0 – 255)
- TINYTEXT
- String (0 – 255)
- TEXT
- String (0 – 65535)
- BLOB
- String (0 – 65535)
- MEDIUMTEXT
- String (0 – 16777215)
- MEDIUMBLOB
- String (0 – 16777215)
- LONGTEXT
- String (0 – 429496-7295)
- LONGBLOB
- String (0 – 429496-7295)
- TINYINT x
- Integer (-128 to 127)
- SMALLINT x
- Integer (-32768 to 32767)
- MEDIUMINT x
- Integer (-8388608 to 8388607)
- INT x
- Integer (-2147-483648 to 214748-3647)
- BIGINT x
- Integer (-9223-372-036-854-775808 to 922337-203-685-477-5807)
- FLOAT
- Decimal (precise to 23 digits)
- DOUBLE
- Decimal (24 to 53 digits)
- DECIMAL
- “-DOU-BLE-” stored as string
- DATE
- YYYY-MM-DD
- DATETIME
- YYYY-MM-DD HH:MM:SS
- TIMESTAMP
- YYYYMM-DDH-HMMSS
- TIME
- HH:MM:SS
- ENUM
- One of preset options
- SET
- Selection of preset options
Operators
String
Regular Expressions
String Functions
- ASCII()
- BIN()
- BIT_LENGTH()
- CHAR_LENGTH()
- CHAR()
- CHARACTER_LENGTH()
- CONCAT_WS()
- CONCAT()
- ELT()
- EXPORT_SET()
- FIELD()
- FIND_IN_SET()
- FORMAT()
- HEX()
- INSERT()
- INSTR()
- LCASE()
- LEFT()
- LENGTH()
- LIKE
- LOAD_FILE()
- LOCATE()
- LOWER()
- LPAD()
- LTRIM()
- MAKE_SET()
- MATCH
- MID()
- NOT LIKE
- NOT REGEXP
- OCTET_LENGTH()
- ORD()
- POSITION()
- QUOTE()
- REGEXP
- REPEAT()
- REPLACE()
- REVERSE()
- RIGHT()
- RLIKE
- RPAD()
- RTRIM()
- SOUNDEX()
- SOUNDS LIKE
- SPACE()
- STRCMP()
- SUBSTR()
- SUBSTRING_INDEX()
- SUBSTRING()
- TRIM()
- UCASE()
- UNHEX()
- UPPER()
Numeric
Date
Date and Time
- ADDDATE()
- ADDTIME()
- CONVERT_TZ()
- CURDATE()
- CURRENT_DATE(), CURRENT_DATE
- CURRENT_TIME(), CURRENT_TIME
- CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
- CURTIME()
- DATE_ADD()
- DATE_FORMAT()
- DATE_SUB()
- DATE()
- DATEDIFF()
- DAY()
- DAYNAME()
- DAYOFMONTH()
- DAYOFWEEK()
- DAYOFYEAR()
- EXTRACT()
- FROM_DAYS()
- FROM_UNIXTIME()
- GET_FORMAT()
- HOUR()
- LAST_DAY
- LOCALTIME(), LOCALTIME
- LOCALTIMESTAMP, LOCALTIMESTAMP()
- MAKEDATE()
- MAKETIME
- MICROSECOND()
- MINUTE()
- MONTH()
- MONTHNAME()
- NOW()
- PERIOD_ADD()
- PERIOD_DIFF()
- QUARTER()
- SEC_TO_TIME()
- SECOND()
- STR_TO_DATE()
- SUBDATE()
- SUBTIME()
- SYSDATE()
- TIME_FORMAT()
- TIME_TO_SEC()
- TIME()
- TIMEDIFF()
- TIMESTAMP()
- TIMESTAMPADD()
- TIMESTAMPDIFF()
- TO_DAYS()
- TO_SECONDS()
- UNIX_TIMESTAMP()
- UTC_DATE()
- UTC_TIME()
- UTC_TIMESTAMP()
- WEEK()
- WEEKDAY()
- WEEKOFYEAR()
- YEAR()
- YEARWEEK()
Group
Information
Encrypt
Miscellaneous
Samples
Select Queires
- Returns all columns
- SELECT * FROM table
- Returns all columns
- SELECT * FROM table1, table2, …
- Returns specific column
- SELECT field1, field2, … FROM table1, table2, …
- Returns rows that match condition
- SELECT … FROM … WHERE condition
- 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
- Returns first 10 rows
- SELECT … FROM … WHERE condition LIMIT 10
- Returns with no repeats
- SELECT DISTINCT field1 FROM …
- Returns and joind
- SELECT … FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition