Summary I am selling you future books (programming).

Contents
About ................................................................................................................................................................................... 1
Chapter 1: Getting started with MySQL ............................................................................................................. 2
Section 1.1: Getting Started ........................................................................................................................................... 2
Section 1.2: Information Schema Examples ................................................................................................................ 6
Chapter 2: Data Types ............................................................................................................................................... 7
Section 2.1: CHAR(n) ...................................................................................................................................................... 7
Section 2.2: DATE, DATETIME, TIMESTAMP, YEAR, and TIME ................................................................................... 7
Section 2.3: VARCHAR(255) -- or not .......................................................................................................................... 8
Section 2.4: INT as AUTO_INCREMENT ...................................................................................................................... 8
Section 2.5: Others ........................................................................................................................................................ 8
Section 2.6: Implicit / automatic casting ..................................................................................................................... 9
Section 2.7: Introduction (numeric) ............................................................................................................................. 9
Section 2.8: Integer Types .......................................................................................................................................... 10
Section 2.9: Fixed Point Types ................................................................................................................................... 10
Section 2.10: Floating Point Types ............................................................................................................................. 10
Section 2.11: Bit Value Type ........................................................................................................................................ 11
Chapter 3: SELECT ...................................................................................................................................................... 12
Section 3.1: SELECT with DISTINCT ............................................................................................................................ 12
Section 3.2: SELECT all columns (*) ........................................................................................................................... 12
Section 3.3: SELECT by column name ....................................................................................................................... 13
Section 3.4: SELECT with LIKE (%) ............................................................................................................................. 13
Section 3.5: SELECT with CASE or IF .......................................................................................................................... 15
Section 3.6: SELECT with Alias (AS) ........................................................................................................................... 15
Section 3.7: SELECT with a LIMIT clause ................................................................................................................... 16
Section 3.8: SELECT with BETWEEN .......................................................................................................................... 16
Section 3.9: SELECT with WHERE ............................................................................................................................... 18
Section 3.10: SELECT with LIKE(_) ............................................................................................................................. 18
Section 3.11: SELECT with date range ........................................................................................................................ 19
Chapter 4: Backticks ................................................................................................................................................. 20
Section 4.1: Backticks usage ....................................................................................................................................... 20
Chapter 5: NULL .......................................................................................................................................................... 21
Section 5.1: Uses for NULL .......................................................................................................................................... 21
Section 5.2: Testing NULLs ......................................................................................................................................... 21
Chapter 6: Limit and Oset ................................................................................................................................... 22
Section 6.1: Limit and Oset relationship .................................................................................................................. 22
Chapter 7: Creating databases ........................................................................................................................... 24
Section 7.1: Create database, users, and grants ...................................................................................................... 24
Section 7.2: Creating and Selecting a Database ...................................................................................................... 26
Section 7.3: MyDatabase ............................................................................................................................................ 26
Section 7.4: System Databases .................................................................................................................................. 27
Chapter 8: Using Variables .................................................................................................................................... 28
Section 8.1: Setting Variables ..................................................................................................................................... 28
Section 8.2: Row Number and Group By using variables in Select Statement ..................................................... 29
Chapter 9: Comment MySQL ................................................................................................................................. 31
Section 9.1: Adding comments ................................................................................................................................... 31
Section 9.2: Commenting table definitions ............................................................................................................... 31

,Chapter 10: INSERT .................................................................................................................................................... 32
Section 10.1: INSERT, ON DUPLICATE KEY UPDATE ................................................................................................. 32
Section 10.2: Inserting multiple rows ......................................................................................................................... 32
Section 10.3: Basic Insert ............................................................................................................................................. 33
Section 10.4: INSERT with AUTO_INCREMENT + LAST_INSERT_ID() .................................................................... 33
Section 10.5: INSERT SELECT (Inserting data from another Table) ....................................................................... 35
Section 10.6: Lost AUTO_INCREMENT ids ................................................................................................................. 35
Chapter 11: DELETE ..................................................................................................................................................... 37
Section 11.1: Multi-Table Deletes ................................................................................................................................. 37
Section 11.2: DELETE vs TRUNCATE ........................................................................................................................... 38
Section 11.3: Multi-table DELETE ................................................................................................................................. 39
Section 11.4: Basic delete ............................................................................................................................................. 39
Section 11.5: Delete with Where clause ...................................................................................................................... 39
Section 11.6: Delete all rows from a table .................................................................................................................. 39
Section 11.7: LIMITing deletes ...................................................................................................................................... 39
Chapter 12: UPDATE ................................................................................................................................................... 41
Section 12.1: Update with Join Pattern ...................................................................................................................... 41
Section 12.2: Basic Update ......................................................................................................................................... 41
Section 12.3: Bulk UPDATE .......................................................................................................................................... 42
Section 12.4: UPDATE with ORDER BY and LIMIT ..................................................................................................... 42
Section 12.5: Multiple Table UPDATE ......................................................................................................................... 42
Chapter 13: ORDER BY .............................................................................................................................................. 44
Section 13.1: Contexts ................................................................................................................................................... 44
Section 13.2: Basic ........................................................................................................................................................ 44
Section 13.3: ASCending / DESCending ..................................................................................................................... 44
Section 13.4: Some tricks ............................................................................................................................................. 44
Chapter 14: Group By ............................................................................................................................................... 46
Section 14.1: GROUP BY using HAVING ...................................................................................................................... 46
Section 14.2: Group By using Group Concat ............................................................................................................. 46
Section 14.3: Group By Using MIN function ............................................................................................................... 46
Section 14.4: GROUP BY with AGGREGATE functions .............................................................................................. 47
Chapter 15: Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause
... .......................................................................................................................................................................................... 50
Section 15.1: Misusing GROUP BY to return unpredictable results: Murphy's Law ................................................ 50
Section 15.2: Misusing GROUP BY with SELECT *, and how to fix it ........................................................................ 50
Section 15.3: ANY_VALUE() ........................................................................................................................................ 51
Section 15.4: Using and misusing GROUP BY ........................................................................................................... 51
Chapter 16: Joins ......................................................................................................................................................... 53
Section 16.1: Joins visualized ....................................................................................................................................... 53
Section 16.2: JOIN with subquery ("Derived" table) ................................................................................................. 53
Section 16.3: Full Outer Join ........................................................................................................................................ 54
Section 16.4: Retrieve customers with orders -- variations on a theme ................................................................ 55
Section 16.5: Joining Examples .................................................................................................................................. 56
Chapter 17: JOINS: Join 3 table with the same name of id. .................................................................. 57
Section 17.1: Join 3 tables on a column with the same name ................................................................................. 57
Chapter 18: UNION ...................................................................................................................................................... 58
Section 18.1: Combining SELECT statements with UNION ....................................................................................... 58
Section 18.2: Combining data with dierent columns ............................................................................................. 58
Section 18.3: ORDER BY .............................................................................................................................................. 58

, Section 18.4: Pagination via OFFSET ......................................................................................................................... 58
Section 18.5: Combining and merging data on dierent MySQL tables with the same columns into unique
rows and running query ..................................................................................................................................... 59
Section 18.6: UNION ALL and UNION ......................................................................................................................... 59
Chapter 19: Arithmetic .............................................................................................................................................. 60
Section 19.1: Arithmetic Operators ............................................................................................................................. 60
Section 19.2: Mathematical Constants ...................................................................................................................... 60
Section 19.3: Trigonometry (SIN, COS) ...................................................................................................................... 60
Section 19.4: Rounding (ROUND, FLOOR, CEIL) ....................................................................................................... 62
Section 19.5: Raise a number to a power (POW) ..................................................................................................... 62
Section 19.6: Square Root (SQRT) ............................................................................................................................. 63
Section 19.7: Random Numbers (RAND) ................................................................................................................... 63
Section 19.8: Absolute Value and Sign (ABS, SIGN) ................................................................................................. 63
Chapter 20: String operations ............................................................................................................................. 65
Section 20.1: LENGTH() ............................................................................................................................................... 66
Section 20.2: CHAR_LENGTH() .................................................................................................................................. 66
Section 20.3: HEX(str) ................................................................................................................................................. 66
Section 20.4: SUBSTRING() ........................................................................................................................................ 66
Section 20.5: UPPER() / UCASE() .............................................................................................................................. 67
Section 20.6: STR_TO_DATE - Convert string to date ............................................................................................ 67
Section 20.7: LOWER() / LCASE() .............................................................................................................................. 67
Section 20.8: REPLACE() ............................................................................................................................................. 67
Section 20.9: Find element in comma separated list .............................................................................................. 67
Chapter 21: Date and Time Operations ........................................................................................................... 69
Section 21.1: Date arithmetic ....................................................................................................................................... 69
Section 21.2: SYSDATE(), NOW(), CURDATE() .......................................................................................................... 69
Section 21.3: Testing against a date range ............................................................................................................... 70
Section 21.4: Extract Date from Given Date or DateTime Expression ................................................................... 70
Section 21.5: Using an index for a date and time lookup ........................................................................................ 70
Section 21.6: Now() ...................................................................................................................................................... 71
Chapter 22: Handling Time Zones ...................................................................................................................... 72
Section 22.1: Retrieve the current date and time in a particular time zone .......................................................... 72
Section 22.2: Convert a stored `DATE` or `DATETIME` value to another time zone ............................................. 72
Section 22.3: Retrieve stored `TIMESTAMP` values in a particular time zone ....................................................... 72
Section 22.4: What is my server's local time zone setting? .................................................................................... 72
Section 22.5: What time_zone values are available in my server? ....................................................................... 73
Chapter 23: Regular Expressions ........................................................................................................................ 74
Section 23.1: REGEXP / RLIKE ..................................................................................................................................... 74
Chapter 24: VIEW ........................................................................................................................................................ 76
Section 24.1: Create a View ........................................................................................................................................ 76
Section 24.2: A view from two tables ........................................................................................................................ 77
Section 24.3: DROPPING A VIEW ............................................................................................................................... 77
Section 24.4: Updating a table via a VIEW ............................................................................................................... 77
Chapter 25: Table Creation ................................................................................................................................... 78
Section 25.1: Table creation with Primary Key ......................................................................................................... 78
Section 25.2: Basic table creation ............................................................................................................................. 79
Section 25.3: Table creation with Foreign Key ......................................................................................................... 79
Section 25.4: Show Table Structure ........................................................................................................................... 80
Section 25.5: Cloning an existing table ..................................................................................................................... 81

, Section 25.6: Table Create With TimeStamp Column To Show Last Update ....................................................... 81
Section 25.7: CREATE TABLE FROM SELECT ............................................................................................................ 81
Chapter 26: ALTER TABLE ....................................................................................................................................... 83
Section 26.1: Changing storage engine; rebuild table; change file_per_table ..................................................... 83
Section 26.2: ALTER COLUMN OF TABLE ................................................................................................................. 83
Section 26.3: Change auto-increment value ............................................................................................................ 83
Section 26.4: Renaming a MySQL table .................................................................................................................... 83
Section 26.5: ALTER table add INDEX ....................................................................................................................... 84
Section 26.6: Changing the type of a primary key column .................................................................................... 84
Section 26.7: Change column definition .................................................................................................................... 84
Section 26.8: Renaming a MySQL database ............................................................................................................ 84
Section 26.9: Swapping the names of two MySQL databases ............................................................................... 85
Section 26.10: Renaming a column in a MySQL table ............................................................................................. 85
Chapter 27: Drop Table ........................................................................................................................................... 87
Section 27.1: Drop Table ............................................................................................................................................. 87
Section 27.2: Drop tables from database ................................................................................................................. 87
Chapter 28: MySQL LOCK TABLE ........................................................................................................................ 88
Section 28.1: Row Level Locking ................................................................................................................................ 88
Section 28.2: Mysql Locks ........................................................................................................................................... 89
Chapter 29: Error codes .......................................................................................................................................... 91
Section 29.1: Error code 1064: Syntax error ............................................................................................................... 91
Section 29.2: Error code 1175: Safe Update ............................................................................................................... 91
Section 29.3: Error code 1215: Cannot add foreign key constraint ......................................................................... 91
Section 29.4: 1067, 1292, 1366, 1411 - Bad Value for number, date, default, etc ...................................................... 93
Section 29.5: 1045 Access denied .............................................................................................................................. 93
Section 29.6: 1236 "impossible position" in Replication ........................................................................................... 93
Section 29.7: 2002, 2003 Cannot connect ................................................................................................................ 94
Section 29.8: 126, 127, 134, 144, 145 .............................................................................................................................. 94
Section 29.9: 139 .......................................................................................................................................................... 94
Section 29.10: 1366 ....................................................................................................................................................... 94
Section 29.11: 126, 1054, 1146, 1062, 24 ......................................................................................................................... 95
Chapter 30: Stored routines (procedures and functions) ..................................................................... 97
Section 30.1: Stored procedure with IN, OUT, INOUT parameters ......................................................................... 97
Section 30.2: Create a Function ................................................................................................................................. 98
Section 30.3: Cursors ................................................................................................................................................... 99
Section 30.4: Multiple ResultSets ............................................................................................................................. 100
Section 30.5: Create a function ................................................................................................................................ 100
Chapter 31: Indexes and Keys ............................................................................................................................. 102
Section 31.1: Create index .......................................................................................................................................... 102
Section 31.2: Create unique index ............................................................................................................................ 102
Section 31.3: AUTO_INCREMENT key ...................................................................................................................... 102
Section 31.4: Create composite index ...................................................................................................................... 102
Section 31.5: Drop index ............................................................................................................................................ 103
Chapter 32: Full-Text search ............................................................................................................................... 104
Section 32.1: Simple FULLTEXT search .................................................................................................................... 104
Section 32.2: Simple BOOLEAN search ................................................................................................................... 104
Section 32.3: Multi-column FULLTEXT search ........................................................................................................ 104
Chapter 33: PREPARE Statements ................................................................................................................... 106
Section 33.1: PREPARE, EXECUTE and DEALLOCATE PREPARE Statements ...................................................... 106