MySQL® Notes for Professionals book

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