MySQL
Quick Reference
(MySQL 5.1, 5.5, 5.6, 5.7, 8.0 compatible)

Data Types

• Data Type Overview
  • Overview of Numeric Types
  • Overview of Date and Time Types
  • Overview of String Types
  • Data Type Default Values
• Numeric Types
  • Integer Types (Exact Value)
  • Fixed-Point Types (Exact Value)
  • Floating-Point Types (Approximate Value)
  • Bit-Value Type
  • Numeric Type Attributes
  • Out-of-Range and Overflow Handling
• Date and Time Types
  • The DATETIME , DATE , and TIMESTAMP Types
  • The TIME Type
  • The YEAR Type
  • Year 2000 Issues and Date Types
• String Types
  • The CHAR and VARCHAR Types
  • The BINARY and VARBINARY Types
  • The BLOB and TEXT Types
  • The ENUM Type
  • The SET Type
• Data Type Storage Requirements
• Out-of-Range and Overflow Handling
• Choosing the Right Type for a Column
• Using Data Types from Other Database Engines

Stored Programs and Views

• Defining Stored Programs
• Using Stored Routines (Procedures and Functions)
  • Stored Routine Syntax
  • Stored Routines and MySQL Privileges
  • Stored Routine Metadata
  • Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
• Using Triggers
  • Trigger Syntax
  • Trigger Metadata
• Using the Event Scheduler
  • Event Scheduler Overview
  • Event Scheduler Configuration
  • Event Syntax
  • Event Metadata
  • Event Scheduler Status
  • The Event Scheduler and MySQL Privileges
• Using Views
  • View Syntax
  • View Processing Algorithms
  • Updatable and Insertable Views
  • View Metadata
• Access Control for Stored Programs and Views
• Binary Logging of Stored Programs

MySQL Differences from Standard SQL

• Introduction
• SELECT INTO TABLE
• UPDATE
• Transactions and Atomic Operations
• Foreign Keys
• --' as the Start of a Comment

Limits in MySQL

• The Maximum Number of Tables Per DATABASE Depend on the Filesystem
• File-size Limits
• The Maximum Number of Columns Per TABLE is 4096
• The Maximum Number of Tables That Can Be Referenced in a Single JOIN is 61
• Windows Platform Limitations

SQL Statement Syntax

• Data Definition Statements
  • ALTER DATABASE Syntax
  • ALTER EVENT Syntax
  • ALTER LOGFILE GROUP Syntax
  • ALTER FUNCTION Syntax
  • ALTER PROCEDURE Syntax
  • ALTER SERVER Syntax
  • ALTER TABLE Syntax
  • ALTER TABLESPACE Syntax
  • ALTER VIEW Syntax
  • CREATE DATABASE Syntax
  • CREATE EVENT Syntax
  • The CREATE FUNCTION Statement
  • CREATE INDEX Syntax
  • CREATE LOGFILE GROUP Syntax
  • CREATE PROCEDURE and CREATE FUNCTION Syntax
  • CREATE SERVER Syntax
  • CREATE TABLE Syntax
  • CREATE TABLESPACE Syntax
  • CREATE TRIGGER Syntax
  • CREATE VIEW Syntax
  • DROP DATABASE Syntax
  • DROP EVENT Syntax
  • DROP FUNCTION Syntax
  • DROP INDEX Syntax
  • DROP LOGFILE GROUP Syntax
  • DROP PROCEDURE and DROP FUNCTION Syntax
  • DROP SERVER Syntax
  • DROP TABLE Syntax
  • DROP TABLESPACE Syntax
  • DROP TRIGGER Syntax
  • DROP VIEW Syntax
  • RENAME DATABASE Syntax
  • RENAME TABLE Syntax
  • TRUNCATE TABLE Syntax
• Data Manipulation Statements
  • CALL Syntax
  • DELETE Syntax
  • DO Syntax
  • HANDLER Syntax
  • INSERT Syntax
  • LOAD DATA INFILESyntax
  • REPLACE Syntax
  • SELECT Syntax
  • Subquery Syntax
  • UPDATE Syntax
• MySQL Transactional and Locking Statements
  • START TRANSACTION, COMMIT, and ROLLBACK Syntax
  • Statements That Cannot Be Rolled Back
  • Statements That Cause an Implicit Commit
  • SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax
  • LOCK TABLES and UNLOCK TABLES Syntax
  • SET TRANSACTION Syntax
  • XA Transactions
• Database Administration Statements
  • Account Management Statements
    • CREATE USER Syntax
    • DROP USER Syntax
    • GRANT Syntax
    • RENAME USER Syntax
    • REVOKE Syntax
    • SET PASSWORD Syntax
  • Table Maintenance Statements
    • ANALYZE TABLE Syntax
    • BACKUP TABLE Syntax
    • CHECK TABLE Syntax
    • CHECKSUM TABLE Syntax
    • OPTIMIZE TABLE Syntax
    • REPAIR TABLE Syntax
    • RESTORE TABLE Syntax
  • Plugin and User-Defined Function Statements
    • CREATE FUNCTION Syntax (User-Defined)
    • DROP FUNCTION Syntax
    • INSTALL PLUGIN Syntax
    • UNINSTALL PLUGIN Syntax
  • SET Syntax
  • SHOW Syntax
  • Other Administrative Statements
    • BINLOG Syntax
    • CACHE INDEX Syntax
    • FLUSH Syntax
    • KILL Syntax
    • LOAD INDEX INTO CACHE Syntax
    • RESET Syntax
• Replication Statements
  • SQL Statements for Controlling Master Servers
  • SQL Statements for Controlling Slave Servers
• SQL Syntax for Prepared Statements
  • PREPARE Syntax
  • EXECUTE Syntax
  • DEALLOCATE PREPARE Syntax
  • Automatic Prepared Statement Repreparation
• MySQL Compound-Statement Syntax
  • BEGIN ... ENDCompound-Statement Syntax
  • Statement Label Syntax
  • DECLARE Syntax
  • Variables in Stored Programs
  • Conditions and Handlers
  • Cursors
  • Flow Control Constructs
  • RETURN Syntax
• MySQL Utility Statements
  • DESCRIBE Syntax
  • EXPLAIN Syntax
  • HELP Syntax
  • USE Syntax

Functions and Operators

• Function and Operator Reference: AND/OR, BETWEEN, CONCAT(), IS NULL, LENGTH(), LIKE, NOW(), ROUND(), !=, <>, etc.
  
• Type Conversion in Expression Evaluation
• Operators
  • Operator Precedence
  • Comparison Functions and Operators
  • Logical Operators
  • Assignment Operators
• Control Flow Functions
• String Functions
  • String Comparison Functions
  • Regular Expressions
• Numeric Functions and Operators
  • Arithmetic Operators
  • Mathematical Functions
• Date and Time Functions
• What Calendar Is Used By MySQL?
• Full-Text Search Functions
  • Natural Language Full-Text Searches
  • Boolean Full-Text Searches
  • Full-Text Searches with Query Expansion
  • Full-Text Stopwords
  • Full-Text Restrictions
  • Fine-Tuning MySQL Full-Text Search
  • Adding a Collation for Full-Text Indexing
• Cast Functions and Operators
• XML Functions
• Bit Functions
• Encryption and Compression Functions
• Information Functions
• Miscellaneous Functions
• Functions and Modifiers for Use with GROUP BY Clauses
  • GROUP BY (Aggregate) Functions
  • GROUP BY Modifiers
  • GROUP BY and HAVING with Hidden Columns
• Spatial Extensions
  • Introduction to MySQL Spatial Support
  • The OpenGIS Geometry Model
  • Supported Spatial Data Formats
  • Creating a Spatially Enabled MySQL Database
  • Spatial Analysis Functions
  • Optimizing Spatial Analysis
  • MySQL Conformance and Compatibility
• Precision Math
  • Types of Numeric Values
  • DECIMAL Data Type Changes
  • Expression Handling
  • Rounding Behavior
  • Precision Math Examples

Optimization

• Optimization Overview
  • MySQL Design Limitations and Tradeoffs
  • Designing Applications for Portability
  • The MySQL Benchmark Suite
  • Using Your Own Benchmarks
• Obtaining Query Execution Plan Information
  • Optimizing Queries with EXPLAIN
  • EXPLAIN Output Format
  • Estimating Query Performance
• Optimizing SQL Statements
  • Optimizing SELECT Statements
  • Optimizing Non- SELECT Statements
  • INFORMATION_SCHEMA Optimization
  • Other Optimization Tips
• Controlling the Query Optimizer
  • Controlling Query Plan Evaluation
  • Controlling Switchable Optimizations
• Optimization and Indexes
  • Column Indexes
  • Multiple-Column Indexes
  • How MySQL Uses Indexes
  • InnoDB and MyISAM Index Statistics Collection
• Buffering and Caching
  • The MyISAM Key Cache
  • The InnoDB Buffer Pool
  • The MySQL Query Cache
• Locking Issues
  • Internal Locking Methods
  • Table Locking Issues
  • Concurrent Inserts
  • External Locking
• Optimizing Database Structure
  • Make Your Data as Small as Possible
  • How MySQL Opens and Closes Tables
  • Disadvantages of Creating Many Tables in the Same Database
  • How MySQL Uses Internal Temporary Tables
• Optimizing the MySQL Server
  • System Factors and Startup Parameter Tuning
  • Tuning Server Parameters
  • How MySQL Uses Threads for Client Connections
  • How MySQL Uses Memory
  • Disk Issues
  • Using Symbolic Links
  • Enabling Large Page Support
  • How MySQL Uses DNS
• Examining Thread Information
  • Thread Command Values
  • General Thread States
  • Delayed-Insert Thread States
  • Query Cache Thread States
  • Replication Master Thread States
  • Replication Slave I/O Thread States
  • Replication Slave SQL Thread States
  • Replication Slave Connection Thread States
  • MySQL Cluster Thread States
  • Event Scheduler Thread States