| Запросы | |
|---|---|
| SELECT * FROM tablename | # Returns all columns |
| SELECT column FROM tablename | # Returns specific column |
| SELECT COUNT(*) FROM tablename | # Returns number of rows |
| SELECT SUM(column) FROM tablename | # Returns sum of column |
| SELECT DISTINCT column FROM tablename | # Returns unique values of column |
| SELECT * FROM tablename WHERE condition | # Returns rows that match condition |
| SELECT * FROM tablename WHERE BINARY condition | # Condition is case-sensitive |
| SELECT * FROM t1 INNER JOIN t2 on t1.id = t2.id | # Join two tables, return all columns |
| SELECT t1.* FROM t1 INNER JOIN t2 on t1.id = t2.id | # Only return columns from table1 |
| SELECT LAST_INSERT_ID() as new_id | # Returns ID of last created row |
| SELECT max(column) AS alias | # Return maxium value in column as "alias" |
| SELECT * FROM table ORDER BY column | # Return all rows ordering by column |
| SELECT * FROM table LIMIT 10, 20 | # Return first 20 rows after row 10 |
| UPDATE table_name SET column1=value, column2=value2,… WHERE some_column=some_value | |
| INSERT INTO table_name VALUES (value1, value2, value3,...) | |
| INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) | |
| DELETE FROM table_name WHERE some_column=some_value | |
| Типы данных | |||
|---|---|---|---|
| CHAR | String, length 0 - 255 | VARCHAR | String, length 0 - 255 |
| TINYTEXT | String, length 0 - 255 | TEXT | String, length 0 - 65535 |
| BLOB | String, length 0 - 65535 | MEDIUMTEXT | String, length 0 - 16777215 |
| MEDIUMBLOB | String, length 0 - 16777215 | LONGTEXT | String, length 0 - 4294967295 |
| LONGBLOB | String, length 0 - 4294967295 | TINYINT | Integer, -128 to 127 |
| SMALLINT | Integer, -32768 to 32767 | MEDIUMINT | Integer, -8388608 to 8388607 |
| INT | Integer, -2147483648 to 2147483647 | BIGINT | Int, -9223372036854775808 |
| FLOAT | to 9223372036854775807 Decimal | DOUBLE | Decimal (24 to 53 digits) |
| DECIMAL | DOUBLE stored as string | DATE | YYYY-MM-DD |
| DATETIME | YYYY-MM-DD HH:MM:SS | TIMESTAMP | YYYYMMDDHHMMSS |
| TIME | HH:MM:SS | ENUM | One of preset options |
| SET | Selection of preset options | ||
| Функции | ||||
|---|---|---|---|---|
| String | Date and Time | Comparison | Mathematical | Group |
| ASCII | DAYOFWEEK | STRCMP | ABS | COUNT |
| ORD | WEEKDAY | Cast | SIGN | AVG |
| CONV | DAYOFMONTH | CAST | MOD | MIN |
| BIN,OCT,HEX | DAYOFYEAR | CONVERT | FLOOR | MAX |
| CHAR | MONTH | Other | CEILING | SUM |
| CONCAT | DAYNAME | BIT_COUNT | ROUND | GROUP_CONCAT |
| CONCAT_WS | MONTHNAME | DATABASE | DIV | VARIANCE |
| LENGTH | QUARTER | USER | EXP | STD |
| CHAR_LENGTH | WEEK | SYSTEM_USER | LN | STDDEV |
| BIT_LENGTH | YEAR | SESSION_USER | LOG,LOG2,LOG10 | BIT_OR |
| LOCATE | YEARWEEK | CURRENT_USER | POW | BIT_AND |
| INSTR | HOUR | PASSWORD | POWER | Control Flow |
| LPAD | MINUTE | OLD_PASSWORD | SQRT | IFNULL |
| RPAD | SECOND | ENCRYPT | PI | NULLIF |
| LEFT | PERIOD_ADD | DECODE | COS | IF |
| RIGHT | PERIOD_DIFF | MD5 | SIN | |
| SUBSTRING | DATE_ADD | SHA1 | TAN | |
| MID | DATE_SUB | AES_ENCRYPT | ACOS | |
| SUBSTRING_INDEX | ADDDATE | AES_DECRYPT | ASIN | |
| LTRIM | SUBDATE | DES_ENCRYPT | ATAN, ATAN2 | |
| RTRIM | EXTRACT | DES_DECRYPT | COT | |
| TRIM | TO_DAYS | LAST_INSERT_ID | RAND | |
| SOUNDEX | FROM_DAYS | FORMAT | LEAST | |
| SPACE | DATE_FORMAT | VERSION | GREATEST | |
| REPLACE | TIME_FORMAT | CONNECTION_ID | DEGREES | |
| REPEAT | CURRENT_DATE | GET_LOCK | RADIANS | |
| REVERSE | CURRENT_TIME | RELEASE_LOCK | TRUNCATE | |
| INSERT | NOW | IS_FREE_LOCK | ||
| ELT | SYSDATE | BENCHMARK | ||
| FIELD | UNIX_TIMESTAMP | INET_NTOA | ||
| LCASE | FROM_UNIXTIME | INET_ATON | ||
| UCASE | SEC_TO_TIME | FOUND_ROWS | ||
| LOAD_FILE | TIME_TO_SEC | |||
| QUOTE | ||||