MySQL Notes for Professionals (GoalKicker.com) (Z-Library)

MySQL Notes for ProfessionalsMySQL® Notes for Professionals GoalKicker.com Free Programming Books Disclaimer This is an unocial free book created for educational purposes and is not aliated with ocial MySQL® group(s) or company(s). All trademarks and registered trademarks are the property of their respective owners 100+ pages of professional hints and tricks

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 39 ........................................................................................................................... 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 40 ...................................................................................................................................... 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 ......................................................

Section 33.2: Alter table with add column 106 .............................................................................................................. Chapter 34: JSON 107 ..................................................................................................................................................... Section 34.1: Create simple table with a primary key and JSON field 107 ................................................................. Section 34.2: Insert a simple JSON 107 .......................................................................................................................... Section 34.3: Updating a JSON field 107 ....................................................................................................................... Section 34.4: Insert mixed data into a JSON field 108 ................................................................................................. Section 34.5: CAST data to JSON type 108 ................................................................................................................... Section 34.6: Create Json Object and Array 108 .......................................................................................................... Chapter 35: Extract values from JSON type 109 .............................................................................................. Section 35.1: Read JSON Array value 109 ..................................................................................................................... Section 35.2: JSON Extract Operators 109 .................................................................................................................... Chapter 36: MySQL Admin 111 .................................................................................................................................... Section 36.1: Atomic RENAME & Table Reload 111 ....................................................................................................... Section 36.2: Change root password 111 ...................................................................................................................... Section 36.3: Drop database 111 .................................................................................................................................... Chapter 37: TRIGGERS 112 ........................................................................................................................................... Section 37.1: Basic Trigger 112 ........................................................................................................................................ Section 37.2: Types of triggers 112 ................................................................................................................................ Chapter 38: Configuration and tuning 114 ........................................................................................................... Section 38.1: InnoDB performance 114 .......................................................................................................................... Section 38.2: Parameter to allow huge data to insert 114 ........................................................................................... Section 38.3: Increase the string limit for group_concat 114 ...................................................................................... Section 38.4: Minimal InnoDB configuration 114 .......................................................................................................... Section 38.5: Secure MySQL encryption 115 ................................................................................................................. Chapter 39: Events 116 ................................................................................................................................................... Section 39.1: Create an Event 116 ................................................................................................................................... Chapter 40: ENUM 119 ................................................................................................................................................... Section 40.1: Why ENUM? 119 ......................................................................................................................................... Section 40.2: VARCHAR as an alternative 119 .............................................................................................................. Section 40.3: Adding a new option 119 .......................................................................................................................... Section 40.4: NULL vs NOT NULL 119 ............................................................................................................................ Chapter 41: Install Mysql container with Docker-Compose 121 ............................................................... Section 41.1: Simple example with docker-compose 121 ............................................................................................. Chapter 42: Character Sets and Collations 122 ................................................................................................ Section 42.1: Which CHARACTER SET and COLLATION? 122 ...................................................................................... Section 42.2: Setting character sets on tables and fields 122 ..................................................................................... Section 42.3: Declaration 122 .......................................................................................................................................... Section 42.4: Connection 123 .......................................................................................................................................... Chapter 43: MyISAM Engine 124 ................................................................................................................................ Section 43.1: ENGINE=MyISAM 124 .................................................................................................................................. Chapter 44: Converting from MyISAM to InnoDB 125 ................................................................................... Section 44.1: Basic conversion 125 ................................................................................................................................. Section 44.2: Converting All Tables in one Database 125 ........................................................................................... Chapter 45: Transaction 126 ...................................................................................................................................... Section 45.1: Start Transaction 126 ................................................................................................................................. Section 45.2: COMMIT , ROLLBACK and AUTOCOMMIT 127 ....................................................................................... Section 45.3: Transaction using JDBC Driver 129 ......................................................................................................... Chapter 46: Log files 132 ..............................................................................................................................................

Section 46.1: Slow Query Log 132 ................................................................................................................................... Section 46.2: A List 132 .................................................................................................................................................... Section 46.3: General Query Log 133 ............................................................................................................................. Section 46.4: Error Log 134 ............................................................................................................................................. Chapter 47: Clustering 136 ........................................................................................................................................... Section 47.1: Disambiguation 136 ................................................................................................................................... Chapter 48: Partitioning 137 ....................................................................................................................................... Section 48.1: RANGE Partitioning 137 ............................................................................................................................. Section 48.2: LIST Partitioning 137 ................................................................................................................................. Section 48.3: HASH Partitioning 138 ............................................................................................................................... Chapter 49: Replication 139 ........................................................................................................................................ Section 49.1: Master - Slave Replication Setup 139 ....................................................................................................... Section 49.2: Replication Errors 141 ............................................................................................................................... Chapter 50: Backup using mysqldump 143 ......................................................................................................... Section 50.1: Specifying username and password 143 ................................................................................................ Section 50.2: Creating a backup of a database or table 143 ...................................................................................... Section 50.3: Restoring a backup of a database or table 144 .................................................................................... Section 50.4: Tranferring data from one MySQL server to another 144 ................................................................... Section 50.5: mysqldump from a remote server with compression 145 .................................................................... Section 50.6: restore a gzipped mysqldump file without uncompressing 145 .......................................................... Section 50.7: Backup database with stored procedures and functions 145 .............................................................. Section 50.8: Backup direct to Amazon S3 with compression 145 ............................................................................. Chapter 51: mysqlimport 146 ...................................................................................................................................... Section 51.1: Basic usage 146 ........................................................................................................................................... Section 51.2: Using a custom field-delimiter 146 ........................................................................................................... Section 51.3: Using a custom row-delimiter 146 ............................................................................................................ Section 51.4: Handling duplicate keys 146 ..................................................................................................................... Section 51.5: Conditional import 147 .............................................................................................................................. Section 51.6: Import a standard csv 147 ........................................................................................................................ Chapter 52: LOAD DATA INFILE 148 ......................................................................................................................... Section 52.1: using LOAD DATA INFILE to load large amount of data to database 148 .......................................... Section 52.2: Load data with duplicates 149 ................................................................................................................. Section 52.3: Import a CSV file into a MySQL table 149 ............................................................................................... Chapter 53: MySQL Unions 150 .................................................................................................................................. Section 53.1: Union operator 150 .................................................................................................................................... Section 53.2: Union ALL 150 ............................................................................................................................................ Section 53.3: UNION ALL With WHERE 151 ................................................................................................................... Chapter 54: MySQL client 152 .................................................................................................................................... Section 54.1: Base login 152 ............................................................................................................................................. Section 54.2: Execute commands 152 ............................................................................................................................ Chapter 55: Temporary Tables 154 ......................................................................................................................... Section 55.1: Create Temporary Table 154 .................................................................................................................... Section 55.2: Drop Temporary Table 154 ...................................................................................................................... Chapter 56: Customize PS1 155 ................................................................................................................................... Section 56.1: Customize the MySQL PS1 with current database 155 ........................................................................... Section 56.2: Custom PS1 via MySQL configuration file 155 ........................................................................................ Chapter 57: Dealing with sparse or missing data 156 ................................................................................... Section 57.1: Working with columns containg NULL values 156 ..................................................................................

Chapter 58: Connecting with UTF-8 Using Various Programming language. 159 ........................... Section 58.1: Python 159 .................................................................................................................................................. Section 58.2: PHP 159 ...................................................................................................................................................... Chapter 59: Time with subsecond precision 160 ............................................................................................... Section 59.1: Get the current time with millisecond precision 160 ............................................................................... Section 59.2: Get the current time in a form that looks like a Javascript timestamp 160 ....................................... Section 59.3: Create a table with columns to store sub-second time 160 ................................................................. Section 59.4: Convert a millisecond-precision date / time value to text 160 ............................................................. Section 59.5: Store a Javascript timestamp into a TIMESTAMP column 161 ............................................................ Chapter 60: One to Many 162 ..................................................................................................................................... Section 60.1: Example Company Tables 162 ................................................................................................................. Section 60.2: Get the Employees Managed by a Single Manager 162 ....................................................................... Section 60.3: Get the Manager for a Single Employee 162 ......................................................................................... Chapter 61: Server Information 164 ......................................................................................................................... Section 61.1: SHOW VARIABLES example 164 ................................................................................................................ Section 61.2: SHOW STATUS example 164 .................................................................................................................... Chapter 62: SSL Connection Setup 166 .................................................................................................................. Section 62.1: Setup for Debian-based systems 166 ...................................................................................................... Section 62.2: Setup for CentOS7 / RHEL7 168 .............................................................................................................. Chapter 63: Create New User 173 ............................................................................................................................. Section 63.1: Create a MySQL User 173 ......................................................................................................................... Section 63.2: Specify the password 173 ......................................................................................................................... Section 63.3: Create new user and grant all priviliges to schema 173 ....................................................................... Section 63.4: Renaming user 173 .................................................................................................................................... Chapter 64: Security via GRANTs 174 .................................................................................................................... Section 64.1: Best Practice 174 ........................................................................................................................................ Section 64.2: Host (of user@host) 174 ........................................................................................................................... Chapter 65: Change Password 175 ........................................................................................................................... Section 65.1: Change MySQL root password in Linux 175 ............................................................................................ Section 65.2: Change MySQL root password in Windows 175 .................................................................................... Section 65.3: Process 176 ................................................................................................................................................ Chapter 66: Recover and reset the default root password for MySQL 5.7+ 177 ............................. Section 66.1: What happens when the initial start up of the server 177 ..................................................................... Section 66.2: How to change the root password by using the default password 177 .............................................. Section 66.3: reset root password when " /var/run/mysqld' for UNIX socket file don't exists" 177 ....................... Chapter 67: Recover from lost root password 180 ......................................................................................... Section 67.1: Set root password, enable root user for socket and http access 180 .................................................. Chapter 68: MySQL Performance Tips 181 .......................................................................................................... Section 68.1: Building a composite index 181 ................................................................................................................ Section 68.2: Optimizing Storage Layout for InnoDB Tables 181 ............................................................................... Chapter 69: Performance Tuning 183 ..................................................................................................................... Section 69.1: Don't hide in function 183 .......................................................................................................................... Section 69.2: OR 183 ........................................................................................................................................................ Section 69.3: Add the correct index 183 ......................................................................................................................... Section 69.4: Have an INDEX 184 ................................................................................................................................... Section 69.5: Subqueries 184 ........................................................................................................................................... Section 69.6: JOIN + GROUP BY 184 .............................................................................................................................. Section 69.7: Set the cache correctly 185 ......................................................................................................................

Section 69.8: Negatives 185 ............................................................................................................................................ Appendix A: Reserved Words 186 ............................................................................................................................. Section A.1: Errors due to reserved words 186 .............................................................................................................. Credits 187 ............................................................................................................................................................................ You may also like 190 ......................................................................................................................................................

GoalKicker.com – MySQL® Notes for Professionals 1 About Please feel free to share this PDF with anyone for free, latest version of this book can be downloaded from: https://goalkicker.com/MySQLBook This MySQL® Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow. Text content is released under Creative Commons BY-SA, see credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless otherwise specified This is an unofficial free book created for educational purposes and is not affiliated with official MySQL® group(s) or company(s) nor Stack Overflow. All trademarks and registered trademarks are the property of their respective company owners The information presented in this book is not guaranteed to be correct nor accurate, use at your own risk Please send feedback and corrections to web@petercv.com

GoalKicker.com – MySQL® Notes for Professionals 2 Chapter 1: Getting started with MySQL Version Release Date 1.0 1995-05-23 3.19 1996-12-01 3.20 1997-01-01 3.21 1998-10-01 3.22 1999-10-01 3.23 2001-01-22 4.0 2003-03-01 4.1 2004-10-01 5.0 2005-10-01 5.1 2008-11-27 5.5 2010-11-01 5.6 2013-02-01 5.7 2015-10-01 Section 1.1: Getting Started Creating a database in MySQL CREATE DATABASE mydb; Return value: Query OK, 1 row affected (0.05 sec) Using the created database mydb USE mydb; Return value: Database Changed Creating a table in MySQL CREATE TABLE mytable ( id int unsigned NOT NULL auto_increment, username varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE mytable will create a new table called mytable. id int unsigned NOT NULL auto_increment creates the id column, this type of field will assign a unique numeric ID to each record in the table (meaning that no two rows can have the same id in this case), MySQL will

GoalKicker.com – MySQL® Notes for Professionals 3 automatically assign a new, unique value to the record's id field (starting with 1). Return value: Query OK, 0 rows affected (0.10 sec) Inserting a row into a MySQL table INSERT INTO mytable ( username, email ) VALUES ( "myuser", "myuser@example.com" ); Example return value: Query OK, 1 row affected (0.06 sec) The varchar a.k.a strings can be also be inserted using single quotes: INSERT INTO mytable ( username, email ) VALUES ( 'username', 'username@example.com' ); Updating a row into a MySQL table UPDATE mytable SET username="myuser" WHERE id=8 Example return value: Query OK, 1 row affected (0.06 sec) The int value can be inserted in a query without quotes. Strings and Dates must be enclosed in single quote ' or double quotes ". Deleting a row into a MySQL table DELETE FROM mytable WHERE id=8 Example return value: Query OK, 1 row affected (0.06 sec) This will delete the row having id is 8. Selecting rows based on conditions in MySQL SELECT * FROM mytable WHERE username = "myuser"; Return value: +----+----------+---------------------+ | id | username | email |

GoalKicker.com – MySQL® Notes for Professionals 4 +----+----------+---------------------+ | 1 | myuser | myuser@example.com | +----+----------+---------------------+ 1 row in set (0.00 sec) Show list of existing databases SHOW databases; Return value: +-------------------+ | Databases | +-------------------+ | information_schema| | mydb | +-------------------+ 2 rows in set (0.00 sec) You can think of "information_schema" as a "master database" that provides access to database metadata. Show tables in an existing database SHOW tables; Return value: +----------------+ | Tables_in_mydb | +----------------+ | mytable | +----------------+ 1 row in set (0.00 sec) Show all the fields of a table DESCRIBE databaseName.tableName; or, if already using a database: DESCRIBE tableName; Return value: +-----------+----------------+--------+---------+-------------------+-------+

GoalKicker.com – MySQL® Notes for Professionals 5 | Field | Type | Null | Key | Default | Extra | +-----------+----------------+--------+---------+-------------------+-------+ | fieldname | fieldvaluetype | NO/YES | keytype | defaultfieldvalue | | +-----------+----------------+--------+---------+-------------------+-------+ Extra may contain auto_increment for example. Key refers to the type of key that may affect the field. Primary (PRI), Unique (UNI) ... n row in set (0.00 sec) Where n is the number of fields in the table. Creating user First, you need to create a user and then give the user permissions on certain databases/tables. While creating the user, you also need to specify where this user can connect from. CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_password'; Will create a user that can only connect on the local machine where the database is hosted. CREATE USER 'user'@'%' IDENTIFIED BY 'some_password'; Will create a user that can connect from anywhere (except the local machine). Example return value: Query OK, 0 rows affected (0.00 sec) Adding privileges Grant common, basic privileges to the user for all tables of the specified database: GRANT SELECT, INSERT, UPDATE ON databaseName.* TO 'userName'@'localhost'; Grant all privileges to the user for all tables on all databases (attention with this): GRANT ALL ON *.* TO 'userName'@'localhost' WITH GRANT OPTION; As demonstrated above, *.* targets all databases and tables, databaseName.* targets all tables of the specific database. It is also possible to specify database and table like so databaseName.tableName. WITH GRANT OPTION should be left out if the user need not be able to grant other users privileges. Privileges can be either ALL or a combination of the following, each separated by a comma (non-exhaustive list). SELECT

GoalKicker.com – MySQL® Notes for Professionals 6 INSERT UPDATE DELETE CREATE DROP Note Generally, you should try to avoid using column or table names containing spaces or using reserved words in SQL. For example, it's best to avoid names like table or first name. If you must use such names, put them between back-tick `` delimiters. For example: CREATE TABLE `table` ( `first name` VARCHAR(30) ); A query containing the back-tick delimiters on this table might be: SELECT `first name` FROM `table` WHERE `first name` LIKE 'a%'; Section 1.2: Information Schema Examples Processlist This will show all active & sleeping queries in that order then by how long. SELECT * FROM information_schema.PROCESSLIST ORDER BY INFO DESC, TIME DESC; This is a bit more detail on time-frames as it is in seconds by default SELECT ID, USER, HOST, DB, COMMAND, TIME as time_seconds, ROUND(TIME / 60, 2) as time_minutes, ROUND(TIME / 60 / 60, 2) as time_hours, STATE, INFO FROM information_schema.PROCESSLIST ORDER BY INFO DESC, TIME DESC; Stored Procedure Searching Easily search thru all Stored Procedures for words and wildcards. SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%word%';

GoalKicker.com – MySQL® Notes for Professionals 7 Chapter 2: Data Types Section 2.1: CHAR(n) CHAR(n) is a string of a fixed length of n characters. If it is CHARACTER SET utf8mb4, that means it occupies exactly 4*n bytes, regardless of what text is in it. Most use cases for CHAR(n) involve strings that contain English characters, hence should be CHARACTER SET ascii. (latin1 will do just as good.) country_code CHAR(2) CHARACTER SET ascii, postal_code CHAR(6) CHARACTER SET ascii, uuid CHAR(39) CHARACTER SET ascii, -- more discussion elsewhere Section 2.2: DATE, DATETIME, TIMESTAMP, YEAR, and TIME The DATE datatype comprises the date but no time component. Its format is 'YYYY-MM-DD' with a range of '1000-01-01' to '9999-12-31'. The DATETIME type includes the time with a format of 'YYYY-MM-DD HH:MM:SS'. It has a range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. The TIMESTAMP type is an integer type comprising date and time with an effective range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. The YEAR type represents a year and holds a range from 1901 to 2155. The TIME type represents a time with a format of 'HH:MM:SS' and holds a range from '-838:59:59' to '838:59:59'. Storage Requirements: |-----------|--------------------|----------------------------------------| | Data Type | Before MySQL 5.6.4 | as of MySQL 5.6.4 | |-----------|--------------------|----------------------------------------| | YEAR | 1 byte | 1 byte | | DATE | 3 bytes | 3 bytes | | TIME | 3 bytes | 3 bytes + fractional seconds storage | | DATETIME | 8 bytes | 5 bytes + fractional seconds storage | | TIMESTAMP | 4 bytes | 4 bytes + fractional seconds storage | |-----------|--------------------|----------------------------------------| Fractional Seconds (as of Version 5.6.4): |------------------------------|------------------| | Fractional Seconds Precision | Storage Required | |------------------------------|------------------| | 0 | 0 bytes | | 1,2 | 1 byte | | 3,4 | 2 byte | | 5,6 | 3 byte | |------------------------------|------------------| See the MySQL Manual Pages DATE, DATETIME, and TIMESTAMP Types, Data Type Storage Requirements, and Fractional Seconds in Time Values.

GoalKicker.com – MySQL® Notes for Professionals 8 Section 2.3: VARCHAR(255) -- or not Suggested max len First, I will mention some common strings that are always hex, or otherwise limited to ASCII. For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space: UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16) country_code CHAR(2) CHARACTER SET ascii ip_address CHAR(39) CHARACTER SET ascii -- or pack into BINARY(16) phone VARCHAR(20) CHARACTER SET ascii -- probably enough to handle extension postal_code VARCHAR(20) CHARACTER SET ascii -- (not 'zip_code') (don't know the max city VARCHAR(100) -- This Russian town needs 91: Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin country VARCHAR(50) -- probably enough name VARCHAR(64) -- probably adequate; more than some government agencies allow Why not simply 255? There are two reasons to avoid the common practice of using (255) for everything. When a complex SELECT needs to create temporary table (for a subquery, UNION, GROUP BY, etc), the preferred choice is to use the MEMORY engine, which puts the data in RAM. But VARCHARs are turned into CHAR in the process. This makes VARCHAR(255) CHARACTER SET utf8mb4 take 1020 bytes. That can lead to needing to spill to disk, which is slower. In certain situations, InnoDB will look at the potential size of the columns in a table and decide that it will be too big, aborting a CREATE TABLE. VARCHAR versus TEXT Usage hints for *TEXT, CHAR, and VARCHAR, plus some Best Practice: Never use TINYTEXT. Almost never use CHAR -- it is fixed length; each character is the max length of the CHARACTER SET (eg, 4 bytes/character for utf8mb4). With CHAR, use CHARACTER SET ascii unless you know otherwise. VARCHAR(n) will truncate at n characters; TEXT will truncate at some number of bytes. (But, do you want truncation?) *TEXT may slow down complex SELECTs due to how temp tables are handled. Section 2.4: INT as AUTO_INCREMENT Any size of INT may be used for AUTO_INCREMENT. UNSIGNED is always appropriate. Keep in mind that certain operations "burn" AUTO_INCREMENT ids. This could lead to an unexpected gap. Examples: INSERT IGNORE and REPLACE. They may preallocate an id before realizing that it won't be needed. This is expected behavior and by design in the InnoDB engine and should not discourage their use. Section 2.5: Others There is already a separate entry for "FLOAT, DOUBLE, and DECIMAL" and "ENUM". A single page on datatypes is likely to be unwieldy -- I suggest "Field types" (or should it be called "Datatypes"?) be an overview, then split into these topic pages: INTs FLOAT, DOUBLE, and DECIMAL

GoalKicker.com – MySQL® Notes for Professionals 9 Strings (CHARs, TEXT, etc) BINARY and BLOB DATETIME, TIMESTAMP, and friends ENUM and SET Spatial data JSON type (MySQL 5.7.8+) How to represent Money, and other common 'types' that need shoehorning into existing datatypes Where appropriate, each topic page should include, in addition to syntax and examples: Considerations when ALTERing Size (bytes) Contrast with non-MySQL engines (low priority) Considerations when using the datatype in a PRIMARY KEY or secondary key other Best Practice other Performance issues (I assume this "example" will self-distruct when my suggestions have been satisfied or vetoed.) Section 2.6: Implicit / automatic casting select '123' * 2; To make the multiplication with 2 MySQL automatically converts the string 123 into a number. Return value: 246 The conversion to a number starts from left to right. If the conversion is not possible the result is 0 select '123ABC' * 2 Return value: 246 select 'ABC123' * 2 Return value: 0 Section 2.7: Introduction (numeric) MySQL offers a number of different numeric types. These can be broken down into Group Types Integer Types INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

GoalKicker.com – MySQL® Notes for Professionals 10 Fixed Point Types DECIMAL, NUMERIC Floating Point Types FLOAT, DOUBLE Bit Value Type BIT Section 2.8: Integer Types Minimal unsigned value is always 0. Type Storage (Bytes) Minimum Value (Signed) Maximum Value (Signed) Maximum Value (Unsigned) TINYINT 1 -27 -128 27-1 127 28-1 255 SMALLINT 2 -215 -32,768 215-1 32,767 216-1 65,535 MEDIUMINT 3 -223 -8,388,608 223-1 8,388,607 224-1 16,777,215 INT 4 -231 -2,147,483,648 231-1 2,147,483,647 232-1 4,294,967,295 BIGINT 8 -263 -9,223,372,036,854,775,808 263-1 9,223,372,036,854,775,807 264-1 18,446,744,073,709,551,615 Section 2.9: Fixed Point Types MySQL's DECIMAL and NUMERIC types store exact numeric data values. It is recommended to use these types to preserve exact precision, such as for money. Decimal These values are stored in binary format. In a column declaration, the precision and scale should be specified Precision represents the number of significant digits that are stored for values. Scale represents the number of digits stored after the decimal salary DECIMAL(5,2) 5 represents the precision and 2 represents the scale. For this example, the range of values that can be stored in this column is -999.99 to 999.99 If the scale parameter is omitted, it defaults to 0 This data type can store up to 65 digits. The number of bytes taken by DECIMAL(M,N) is approximately M/2. Section 2.10: Floating Point Types FLOAT and DOUBLE represent approximate data types. Type Storage Precision Range FLOAT 4 bytes 23 significant bits / ~7 decimal digits 10^+/-38 DOUBLE 8 bytes 53 significant bits / ~16 decimal digits 10^+/-308 REAL is a synonym for FLOAT. DOUBLE PRECISION is a synonym for DOUBLE.

GoalKicker.com – MySQL® Notes for Professionals 11 Although MySQL also permits (M,D) qualifier, do not use it. (M,D) means that values can be stored with up to M total digits, where D can be after the decimal. Numbers will be rounded twice or truncated; this will cause more trouble than benefit. Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. Note in particular that a FLOAT value rarely equals a DOUBLE value. Section 2.11: Bit Value Type The BIT type is useful for storing bit-field values. BIT(M) allows storage of up to M-bit values where M is in the range of 1 to 64 You can also specify values with bit value notation. b'111' -> 7 b'10000000' -> 128 Sometimes it is handy to use 'shift' to construct a single-bit value, for example (1 << 7) for 128. The maximum combined size of all BIT columns in an NDB table is 4096.