MySQL® Notes for Professionals book

Description

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