mysql-cheat-sheet-v1., Study notes of Database Programming

Download mysql-cheat-sheet-v1. and more Study notes Database Programming in PDF only on Docsity! DATA TYPES CHAR VARCHAR TINYTEXT TEXT BLOB MEDIUMTEXT MEDIUMBLOB LONGTEXT LONGBLOB * TINYINT * SMALLINT * MEDIUMINT * INT * BIGINT FLOAT DOUBLE DECIMAL DATE DATETIME TIMESTAMP TIME ENUM SET * Note: "UNSIGNED" TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT have the same range of values but start at 0, e.g. TINYINT UNSIGNED is between 0 and 255. String, length 0 - 255 String, length 0 - 255 String, length 0 - 255 String, length 0 - 65535 String, length 0 - 65535 String, length 0 - 16777215 String, length 0 - 16777215 String, length 0 - 4294967295 String, length 0 - 4294967295 Integer, -128 to 127 Integer, -32768 to 32767 Integer, -8388608 to 8388607 Integer, -2147483648 to 2147483647 Int, -9223372036854775808 to 9223372036854775807 Decimal (precise to 23 digits) Decimal (24 to 53 digits) "DOUBLE" stored as string YYYY-MM-DD YYYY-MM-DD HH:MM:SS YYYYMMDDHHMMSS HH:MM:SS One of preset options Selection of preset options Mathematical ABS SIGN MOD FLOOR CEILING ROUND DIV EXP LN LOG,LOG2,LOG10 POW POWER SQRT PI COS SIN TAN ACOS ASIN ATAN, ATAN2 COT RAND LEAST GREATEST DEGREES RADIANS TRUNCATE Date and Time DAYOFWEEK WEEKDAY DAYOFMONTH DAYOFYEAR MONTH DAYNAME MONTHNAME QUARTER WEEK YEAR YEARWEEK HOUR MINUTE SECOND PERIOD_ADD PERIOD_DIFF DATE_ADD DATE_SUB ADDDATE SUBDATE EXTRACT TO_DAYS FROM_DAYS DATE_FORMAT TIME_FORMAT CURRENT_DATE CURRENT_TIME NOW SYSDATE UNIX_TIMESTAMP FROM_UNIXTIME SEC_TO_TIME TIME_TO_SEC Group COUNT AVG MIN MAX SUM GROUP_CONCAT VARIANCE STD STDDEV BIT_OR BIT_AND Control Flow IFNULL NULLIF IF String ASCII ORD CONV BIN,OCT,HEX CHAR CONCAT CONCAT_WS LENGTH CHAR_LENGTH BIT_LENGTH LOCATE INSTR LPAD RPAD LEFT RIGHT SUBSTRING MID SUBSTRING_INDEX LTRIM RTRIM TRIM SOUNDEX SPACE REPLACE REPEAT REVERSE INSERT ELT FIELD LCASE UCASE LOAD_FILE QUOTE Comparison STRCMP Cast CAST CONVERT Other BIT_COUNT DATABASE USER SYSTEM_USER SESSION_USER CURRENT_USER PASSWORD OLD_PASSWORD ENCRYPT DECODE MD5 SHA1 AES_ENCRYPT AES_DECRYPT DES_ENCRYPT DES_DECRYPT LAST_INSERT_ID FORMAT VERSION CONNECTION_ID GET_LOCK RELEASE_LOCK IS_FREE_LOCK BENCHMARK INET_NTOA INET_ATON FOUND_ROWS Available free from AddedBytes.com SAMPLE SELECT QUERIES SELECT * FROM tablename SELECT column FROM tablename SELECT COUNT(*) FROM tablename SELECT SUM(column) FROM tablename SELECT DISTINCT column FROM tablename SELECT * FROM tablename WHERE condition SELECT * FROM tablename WHERE BINARY condition SELECT * FROM table1 INNER JOIN table2 on table1.id = table2.id SELECT table1.* FROM table1 INNER JOIN table2 on table1.id = table2.id SELECT LAST_INSERT_ID() as new_id SELECT max(column) AS alias SELECT * FROM table ORDER BY column SELECT * FROM table LIMIT 10, 20 # Returns all columns # Returns specific column # Returns number of rows # Returns sum of column # Returns unique values of column # Returns rows that match condition # Condition is case-sensitive # Join two tables, return all columns # Only return columns from table1 # Returns ID of last created row # Return maxium value in column as "alias" # Return all rows ordering by column # Return first 20 rows after row 10 MYSQL FUNCTIONS IN PHP mysql_affected_rows mysql_close mysql_connect mysql_data_seek mysql_db_name mysql_errno mysql_error mysql_fetch_array mysql_fetch_assoc mysql_fetch_field mysql_fetch_lengths mysql_fetch_object mysql_fetch_row mysql_field_flags mysql_field_len mysql_field_name mysql_field_seek mysql_field_table mysql_field_type mysql_free_result mysql_insert_id mysql_list_dbs mysql_list_processes mysql_list_tables mysql_num_fields mysql_num_rows mysql_pconnect mysql_query mysql_real_escape_string mysql_select_db