MySQL Notes For Professionals.pdf

【实例截图】

【核心代码】

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