MySQL Notes For Professionals.pdf

ContentsAbout ................................................................................................................................................................................... 1Chapter 1: Getting started with MySQL ............................................................................................................. 2Section 1.1: Getting Started ........................................................................................................................................... 2Section 1.2: Information Schema Examples ................................................................................................................ 6Chapter 2: Data Types ............................................................................................................................................... 7Section 2.1: CHAR(n) ...................................................................................................................................................... 7Section 2.2: DATE, DATETIME, TIMESTAMP, YEAR, and TIME ................................................................................... 7Section 2.3: VARCHAR(255) -- or not .......................................................................................................................... 8Section 2.4: INT as AUTO_INCREMENT ...................................................................................................................... 8Section 2.5: Others ........................................................................................................................................................ 8Section 2.6: Implicit / automatic casting ..................................................................................................................... 9Section 2.7: Introduction (numeric) ............................................................................................................................. 9Section 2.8: Integer Types .......................................................................................................................................... 10Section 2.9: Fixed Point Types ................................................................................................................................... 10Section 2.10: Floating Point Types ............................................................................................................................. 10Section 2.11: Bit Value Type ........................................................................................................................................ 11Chapter 3: SELECT ...................................................................................................................................................... 12Section 3.1: SELECT with DISTINCT ............................................................................................................................ 12Section 3.2: SELECT all columns (*) ........................................................................................................................... 12Section 3.3: SELECT by column name ....................................................................................................................... 13Section 3.4: SELECT with LIKE (%) ............................................................................................................................. 13Section 3.5: SELECT with CASE or IF .......................................................................................................................... 15Section 3.6: SELECT with Alias (AS) ........................................................................................................................... 15Section 3.7: SELECT with a LIMIT clause ................................................................................................................... 16Section 3.8: SELECT with BETWEEN .......................................................................................................................... 16Section 3.9: SELECT with WHERE ............................................................................................................................... 18Section 3.10: SELECT with LIKE(_) ............................................................................................................................. 18Section 3.11: SELECT with date range ........................................................................................................................ 19Chapter 4: Backticks ................................................................................................................................................. 20Section 4.1: Backticks usage ....................................................................................................................................... 20Chapter 5: NULL .......................................................................................................................................................... 21Section 5.1: Uses for NULL .......................................................................................................................................... 21Section 5.2: Testing NULLs ......................................................................................................................................... 21Chapter 6: Limit and Oset ................................................................................................................................... 22Section 6.1: Limit and Oset relationship .................................................................................................................. 22Chapter 7: Creating databases ........................................................................................................................... 24Section 7.1: Create database, users, and grants ...................................................................................................... 24Section 7.2: Creating and Selecting a Database ...................................................................................................... 26Section 7.3: MyDatabase ............................................................................................................................................ 26Section 7.4: System Databases .................................................................................................................................. 27Chapter 8: Using Variables .................................................................................................................................... 28Section 8.1: Setting Variables ..................................................................................................................................... 28Section 8.2: Row Number and Group By using variables in Select Statement ..................................................... 29Chapter 9: Comment MySQL ................................................................................................................................. 31Section 9.1: Adding comments ................................................................................................................................... 31Section 9.2: Commenting table definitions ............................................................................................................... 31Chapter 10: INSERT .................................................................................................................................................... 32Section 10.1: INSERT, ON DUPLICATE KEY UPDATE ................................................................................................. 32Section 10.2: Inserting multiple rows ......................................................................................................................... 32Section 10.3: Basic Insert ............................................................................................................................................. 33Section 10.4: INSERT with AUTO_INCREMENT LAST_INSERT_ID() .................................................................... 33Section 10.5: INSERT SELECT (Inserting data from another Table) ....................................................................... 35Section 10.6: Lost AUTO_INCREMENT ids ................................................................................................................. 35Chapter 11: DELETE ..................................................................................................................................................... 37Section 11.1: Multi-Table Deletes ................................................................................................................................. 37Section 11.2: DELETE vs TRUNCATE ........................................................................................................................... 39Section 11.3: Multi-table DELETE ................................................................................................................................. 39Section 11.4: Basic delete ............................................................................................................................................. 39Section 11.5: Delete with Where clause ...................................................................................................................... 39Section 11.6: Delete all rows from a table .................................................................................................................. 39Section 11.7: LIMITing deletes ...................................................................................................................................... 40Chapter 12: UPDATE ................................................................................................................................................... 41Section 12.1: Update with Join Pattern ...................................................................................................................... 41Section 12.2: Basic Update ......................................................................................................................................... 41Section 12.3: Bulk UPDATE .......................................................................................................................................... 42Section 12.4: UPDATE with ORDER BY and LIMIT ..................................................................................................... 42Section 12.5: Multiple Table UPDATE ......................................................................................................................... 42Chapter 13: ORDER BY .............................................................................................................................................. 44Section 13.1: Contexts ................................................................................................................................................... 44Section 13.2: Basic ........................................................................................................................................................ 44Section 13.3: ASCending / DESCending ..................................................................................................................... 44Section 13.4: Some tricks ............................................................................................................................................. 44Chapter 14: Group By ............................................................................................................................................... 46Section 14.1: GROUP BY using HAVING ...................................................................................................................... 46Section 14.2: Group By using Group Concat ............................................................................................................. 46Section 14.3: Group By Using MIN function ............................................................................................................... 46Section 14.4: GROUP BY with AGGREGATE functions .............................................................................................. 47Chapter 15: Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause... .......................................................................................................................................................................................... 50Section 15.1: Misusing GROUP BY to return unpredictable results: Murphy's Law ................................................ 50Section 15.2: Misusing GROUP BY with SELECT *, and how to fix it ........................................................................ 50Section 15.3: ANY_VALUE() ........................................................................................................................................ 51Section 15.4: Using and misusing GROUP BY ........................................................................................................... 51Chapter 16: Joins ......................................................................................................................................................... 53Section 16.1: Joins visualized ....................................................................................................................................... 53Section 16.2: JOIN with subquery ("Derived" table) ................................................................................................. 53Section 16.3: Full Outer Join ........................................................................................................................................ 54Section 16.4: Retrieve customers with orders -- variations on a theme ................................................................ 55Section 16.5: Joining Examples .................................................................................................................................. 56Chapter 17: JOINS: Join 3 table with the same name of id. .................................................................. 57Section 17.1: Join 3 tables on a column with the same name ................................................................................. 57Chapter 18: UNION ...................................................................................................................................................... 58Section 18.1: Combining SELECT statements with UNION ....................................................................................... 58Section 18.2: Combining data with dierent columns ............................................................................................. 58Section 18.3: ORDER BY .............................................................................................................................................. 58Section 18.4: Pagination via OFFSET ......................................................................................................................... 58Section 18.5: Combining and merging data on dierent MySQL tables with the same columns into uniquerows and running query ..................................................................................................................................... 59Section 18.6: UNION ALL and UNION ......................................................................................................................... 59Chapter 19: Arithmetic .............................................................................................................................................. 60Section 19.1: Arithmetic Operators ............................................................................................................................. 60Section 19.2: Mathematical Constants ...................................................................................................................... 60Section 19.3: Trigonometry (SIN, COS) ...................................................................................................................... 60Section 19.4: Rounding (ROUND, FLOOR, CEIL) ....................................................................................................... 62Section 19.5: Raise a number to a power (POW) ..................................................................................................... 62Section 19.6: Square Root (SQRT) ............................................................................................................................. 63Section 19.7: Random Numbers (RAND) ................................................................................................................... 63Section 19.8: Absolute Value and Sign (ABS, SIGN) ................................................................................................. 63Chapter 20: String operations ............................................................................................................................. 65Section 20.1: LENGTH() ............................................................................................................................................... 66Section 20.2: CHAR_LENGTH() .................................................................................................................................. 66Section 20.3: HEX(str) ................................................................................................................................................. 66Section 20.4: SUBSTRING() ........................................................................................................................................ 66Section 20.5: UPPER() / UCASE() .............................................................................................................................. 67Section 20.6: STR_TO_DATE - Convert string to date ............................................................................................ 67Section 20.7: LOWER() / LCASE() .............................................................................................................................. 67Section 20.8: REPLACE() ............................................................................................................................................. 67Section 20.9: Find element in comma separated list .............................................................................................. 67Chapter 21: Date and Time Operations ........................................................................................................... 69Section 21.1: Date arithmetic ....................................................................................................................................... 69Section 21.2: SYSDATE(), NOW(), CURDATE() .......................................................................................................... 69Section 21.3: Testing against a date range ............................................................................................................... 70Section 21.4: Extract Date from Given Date or DateTime Expression ................................................................... 70Section 21.5: Using an index for a date and time lookup ........................................................................................ 70Section 21.6: Now() ...................................................................................................................................................... 71Chapter 22: Handling Time Zones ...................................................................................................................... 72Section 22.1: Retrieve the current date and time in a particular time zone .......................................................... 72Section 22.2: Convert a stored `DATE` or `DATETIME` value to another time zone ............................................. 72Section 22.3: Retrieve stored `TIMESTAMP` values in a particular time zone ....................................................... 72Section 22.4: What is my server's local time zone setting? .................................................................................... 72Section 22.5: What time_zone values are available in my server? ....................................................................... 73Chapter 23: Regular Expressions ........................................................................................................................ 74Section 23.1: REGEXP / RLIKE ..................................................................................................................................... 74Chapter 24: VIEW ........................................................................................................................................................ 76Section 24.1: Create a View ........................................................................................................................................ 76Section 24.2: A view from two tables ........................................................................................................................ 77Section 24.3: DROPPING A VIEW ............................................................................................................................... 77Section 24.4: Updating a table via a VIEW ............................................................................................................... 77Chapter 25: Table Creation ................................................................................................................................... 78Section 25.1: Table creation with Primary Key ......................................................................................................... 78Section 25.2: Basic table creation ............................................................................................................................. 79Section 25.3: Table creation with Foreign Key ......................................................................................................... 79Section 25.4: Show Table Structure ........................................................................................................................... 80Section 25.5: Cloning an existing table ..................................................................................................................... 81Section 25.6: Table Create With TimeStamp Column To Show Last Update ....................................................... 81Section 25.7: CREATE TABLE FROM SELECT ............................................................................................................ 81Chapter 26: ALTER TABLE ....................................................................................................................................... 83Section 26.1: Changing storage engine; rebuild table; change file_per_table ..................................................... 83Section 26.2: ALTER COLUMN OF TABLE ................................................................................................................. 83Section 26.3: Change auto-increment value ............................................................................................................ 83Section 26.4: Renaming a MySQL table .................................................................................................................... 83Section 26.5: ALTER table add INDEX ....................................................................................................................... 84Section 26.6: Changing the type of a primary key column .................................................................................... 84Section 26.7: Change column definition .................................................................................................................... 84Section 26.8: Renaming a MySQL database ............................................................................................................ 84Section 26.9: Swapping the names of two MySQL databases ............................................................................... 85Section 26.10: Renaming a column in a MySQL table ............................................................................................. 85Chapter 27: Drop Table ........................................................................................................................................... 87Section 27.1: Drop Table ............................................................................................................................................. 87Section 27.2: Drop tables from database ................................................................................................................. 87Chapter 28: MySQL LOCK TABLE ........................................................................................................................ 88Section 28.1: Row Level Locking ................................................................................................................................ 88Section 28.2: Mysql Locks ........................................................................................................................................... 89Chapter 29: Error codes .......................................................................................................................................... 91Section 29.1: Error code 1064: Syntax error ............................................................................................................... 91Section 29.2: Error code 1175: Safe Update ............................................................................................................... 91Section 29.3: Error code 1215: Cannot add foreign key constraint ......................................................................... 91Section 29.4: 1067, 1292, 1366, 1411 - Bad Value for number, date, default, etc ...................................................... 93Section 29.5: 1045 Access denied .............................................................................................................................. 93Section 29.6: 1236 "impossible position" in Replication ........................................................................................... 93Section 29.7: 2002, 2003 Cannot connect ................................................................................................................ 94Section 29.8: 126, 127, 134, 144, 145 .............................................................................................................................. 94Section 29.9: 139 .......................................................................................................................................................... 94Section 29.10: 1366 ....................................................................................................................................................... 94Section 29.11: 126, 1054, 1146, 1062, 24 ......................................................................................................................... 95Chapter 30: Stored routines (procedures and functions) ..................................................................... 97Section 30.1: Stored procedure with IN, OUT, INOUT parameters ......................................................................... 97Section 30.2: Create a Function ................................................................................................................................. 98Section 30.3: Cursors ................................................................................................................................................... 99Section 30.4: Multiple ResultSets ............................................................................................................................. 100Section 30.5: Create a function ................................................................................................................................ 100Chapter 31: Indexes and Keys ............................................................................................................................. 102Section 31.1: Create index .......................................................................................................................................... 102Section 31.2: Create unique index ............................................................................................................................ 102Section 31.3: AUTO_INCREMENT key ...................................................................................................................... 102Section 31.4: Create composite index ...................................................................................................................... 102Section 31.5: Drop index ............................................................................................................................................ 103Chapter 32: Full-Text search ............................................................................................................................... 104Section 32.1: Simple FULLTEXT search .................................................................................................................... 104Section 32.2: Simple BOOLEAN search ................................................................................................................... 104Section 32.3: Multi-column FULLTEXT search ........................................................................................................ 104Chapter 33: PREPARE Statements ................................................................................................................... 106Section 33.1: PREPARE, EXECUTE and DEALLOCATE PREPARE Statements ...................................................... 106Section 33.2: Alter table with add column .............................................................................................................. 106Chapter 34: JSON ..................................................................................................................................................... 107Section 34.1: Create simple table with a primary key and JSON field ................................................................. 107Section 34.2: Insert a simple JSON .......................................................................................................................... 107Section 34.3: Updating a JSON field ....................................................................................................................... 107Section 34.4: Insert mixed data into a JSON field ................................................................................................. 108Section 34.5: CAST data to JSON type ................................................................................................................... 108Section 34.6: Create Json Object and Array .......................................................................................................... 108Chapter 35: Extract values from JSON type .............................................................................................. 109Section 35.1: Read JSON Array value ..................................................................................................................... 109Section 35.2: JSON Extract Operators .................................................................................................................... 109Chapter 36: MySQL Admin .................................................................................................................................... 111Section 36.1: Atomic RENAME & Table Reload ....................................................................................................... 111Section 36.2: Change root password ...................................................................................................................... 111Section 36.3: Drop database .................................................................................................................................... 111Chapter 37: TRIGGERS ........................................................................................................................................... 112Section 37.1: Basic Trigger ........................................................................................................................................ 112Section 37.2: Types of triggers ................................................................................................................................ 112Chapter 38: Configuration and tuning ........................................................................................................... 114Section 38.1: InnoDB performance .......................................................................................................................... 114Section 38.2: Parameter to allow huge data to insert ........................................................................................... 114Section 38.3: Increase the string limit for group_concat ...................................................................................... 114Section 38.4: Minimal InnoDB configuration .......................................................................................................... 114Section 38.5: Secure MySQL encryption ................................................................................................................. 115Chapter 39: Events ................................................................................................................................................... 116Section 39.1: Create an Event ................................................................................................................................... 116Chapter 40: ENUM ................................................................................................................................................... 119Section 40.1: Why ENUM? ......................................................................................................................................... 119Section 40.2: VARCHAR as an alternative .............................................................................................................. 119Section 40.3: Adding a new option .......................................................................................................................... 119Section 40.4: NULL vs NOT NULL ............................................................................................................................ 119Chapter 41: Install Mysql container with Docker-Compose ............................................................... 121Section 41.1: Simple example with docker-compose ............................................................................................. 121Chapter 42: Character Sets and Collations ................................................................................................ 122Section 42.1: Which CHARACTER SET and COLLATION? ...................................................................................... 122Section 42.2: Setting character sets on tables and fields ..................................................................................... 122Section 42.3: Declaration .......................................................................................................................................... 122Section 42.4: Connection .......................................................................................................................................... 123Chapter 43: MyISAM Engine ................................................................................................................................ 124Section 43.1: ENGINE=MyISAM .................................................................................................................................. 124Chapter 44: Converting from MyISAM to InnoDB ................................................................................... 125Section 44.1: Basic conversion ................................................................................................................................. 125Section 44.2: Converting All Tables in one Database ........................................................................................... 125Chapter 45: Transaction ...................................................................................................................................... 126Section 45.1: Start Transaction ................................................................................................................................. 126Section 45.2: COMMIT , ROLLBACK and AUTOCOMMIT ....................................................................................... 127Section 45.3: Transaction using JDBC Driver ......................................................................................................... 129Chapter 46: Log files .............................................................................................................................................. 132Section 46.1: Slow Query Log ................................................................................................................................... 132Section 46.2: A List .................................................................................................................................................... 132Section 46.3: General Query Log ............................................................................................................................. 133Section 46.4: Error Log ............................................................................................................................................. 134Chapter 47: Clustering ........................................................................................................................................... 136Section 47.1: Disambiguation ................................................................................................................................... 136Chapter 48: Partitioning ....................................................................................................................................... 137Section 48.1: RANGE Partitioning ............................................................................................................................. 137Section 48.2: LIST Partitioning ................................................................................................................................. 137Section 48.3: HASH Partitioning ............................................................................................................................... 138Chapter 49: Replication ........................................................................................................................................ 139Section 49.1: Master - Slave Replication Setup ....................................................................................................... 139Section 49.2: Replication Errors ............................................................................................................................... 141Chapter 50: Backup using mysqldump ......................................................................................................... 143Section 50.1: Specifying username and password ................................................................................................ 143Section 50.2: Creating a backup of a database or table ...................................................................................... 143Section 50.3: Restoring a backup of a database or table .................................................................................... 144Section 50.4: Tranferring data from one MySQL server to another ................................................................... 144Section 50.5: mysqldump from a remote server with compression .................................................................... 145Section 50.6: restore a gzipped mysqldump file without uncompressing .......................................................... 145Section 50.7: Backup database with stored procedures and functions .............................................................. 145Section 50.8: Backup direct to Amazon S3 with compression ............................................................................. 145Chapter 51: mysqlimport ...................................................................................................................................... 146Section 51.1: Basic usage ........................................................................................................................................... 146Section 51.2: Using a custom field-delimiter ........................................................................................................... 146Section 51.3: Using a custom row-delimiter ............................................................................................................ 146Section 51.4: Handling duplicate keys ..................................................................................................................... 146Section 51.5: Conditional import .............................................................................................................................. 147Section 51.6: Import a standard csv ........................................................................................................................ 147Chapter 52: LOAD DATA INFILE ......................................................................................................................... 148Section 52.1: using LOAD DATA INFILE to load large amount of data to database .......................................... 148Section 52.2: Load data with duplicates ................................................................................................................. 149Section 52.3: Import a CSV file into a MySQL table ............................................................................................... 149Chapter 53: MySQL Unions .................................................................................................................................. 150Section 53.1: Union operator .................................................................................................................................... 150Section 53.2: Union ALL ............................................................................................................................................ 150Section 53.3: UNION ALL With WHERE ................................................................................................................... 151Chapter 54: MySQL client .................................................................................................................................... 152Section 54.1: Base login ............................................................................................................................................. 152Section 54.2: Execute commands ............................................................................................................................ 152Chapter 55: Temporary Tables ......................................................................................................................... 154Section 55.1: Create Temporary Table .................................................................................................................... 154Section 55.2: Drop Temporary Table ...................................................................................................................... 154Chapter 56: Customize PS1 ................................................................................................................................... 155Section 56.1: Customize the MySQL PS1 with current database ........................................................................... 155Section 56.2: Custom PS1 via MySQL configuration file ........................................................................................ 155Chapter 57: Dealing with sparse or missing data ................................................................................... 156Section 57.1: Working with columns containg NULL values .................................................................................. 156Chapter 58: Connecting with UTF-8 Using Various Programming language. ........................... 159Section 58.1: Python .................................................................................................................................................. 159Section 58.2: PHP ...................................................................................................................................................... 159Chapter 59: Time with subsecond precision ............................................................................................... 160Section 59.1: Get the current time with millisecond precision ............................................................................... 160Section 59.2: Get the current time in a form that looks like a Javascript timestamp ....................................... 160Section 59.3: Create a table with columns to store sub-second time ................................................................. 160Section 59.4: Convert a millisecond-precision date / time value to text ............................................................. 160Section 59.5: Store a Javascript timestamp into a TIMESTAMP column ............................................................ 161Chapter 60: One to Many ..................................................................................................................................... 162Section 60.1: Example Company Tables ................................................................................................................. 162Section 60.2: Get the Employees Managed by a Single Manager ....................................................................... 162Section 60.3: Get the Manager for a Single Employee ......................................................................................... 162Chapter 61: Server Information ......................................................................................................................... 164Section 61.1: SHOW VARIABLES example ................................................................................................................ 164Section 61.2: SHOW STATUS example .................................................................................................................... 164Chapter 62: SSL Connection Setup .................................................................................................................. 166Section 62.1: Setup for Debian-based systems ...................................................................................................... 166Section 62.2: Setup for CentOS7 / RHEL7 .............................................................................................................. 168Chapter 63: Create New User ............................................................................................................................. 173Section 63.1: Create a MySQL User ......................................................................................................................... 173Section 63.2: Specify the password ......................................................................................................................... 173Section 63.3: Create new user and grant all priviliges to schema ....................................................................... 173Section 63.4: Renaming user .................................................................................................................................... 173Chapter 64: Security via GRANTs .................................................................................................................... 174Section 64.1: Best Practice ........................................................................................................................................ 174Section 64.2: Host (of user@host) ........................................................................................................................... 174Chapter 65: Change Password ........................................................................................................................... 175Section 65.1: Change MySQL root password in Linux ............................................................................................ 175Section 65.2: Change MySQL root password in Windows .................................................................................... 175Section 65.3: Process ................................................................................................................................................ 176Chapter 66: Recover and reset the default root password for MySQL 5.7 ............................. 177Section 66.1: What happens when the initial start up of the server ..................................................................... 177Section 66.2: How to change the root password by using the default password .............................................. 177Section 66.3: reset root password when " /var/run/mysqld' for UNIX socket file don't exists" ....................... 177Chapter 67: Recover from lost root password ......................................................................................... 180Section 67.1: Set root password, enable root user for socket and http access .................................................. 180Chapter 68: MySQL Performance Tips .......................................................................................................... 181Section 68.1: Building a composite index ................................................................................................................ 181Section 68.2: Optimizing Storage Layout for InnoDB Tables ............................................................................... 181Chapter 69: Performance Tuning ..................................................................................................................... 183Section 69.1: Don't hide in function .......................................................................................................................... 183Section 69.2: OR ........................................................................................................................................................ 183Section 69.3: Add the correct index ......................................................................................................................... 183Section 69.4: Have an INDEX ................................................................................................................................... 184Section 69.5: Subqueries ........................................................................................................................................... 184Section 69.6: JOIN GROUP BY .............................................................................................................................. 184Section 69.7: Set the cache correctly ...................................................................................................................... 185Section 69.8: Negatives ............................................................................................................................................ 185Appendix A: Reserved Words ............................................................................................................................. 186Section A.1: Errors due to reserved words .............................................................................................................. 186Credits ............................................................................................................................................................................ 187You may also like ...................................................................................................................................................... 190