Essential MySQL

Getting started with MySQL
- Getting started with MySQL
- Getting Started
- Information Schema Examples
Limit and Offset
- Limit and Offset
- Limit and Offset relationship
Creating Databases
- Creating databases
- Create database users and grants
- MyDatabase
- System Databases
- Creating and Selecting a Database
Backup using mysqldump
- Backup using mysqldump
- Specifying username and password
- Creating a backup of a database or table
- Restoring a backup of a database or table
- mysqldump from a remote server with compression
- restore a gzipped mysqldump file without uncompressing
- Backup direct to Amazon S3 with compression
- Tranferring data from one MySQL server to another
- Backup database with stored procedures and functions
Table Creation
- 000Table Creation
- Table creation with Primary Key
- 0Basic table creation
- Table creation with Foreign Key
- Show Table Structure
- Cloning an existing table
- CREATE TABLE FROM SELECT
- Table Create With TimeStamp Column To Show Last Update
Insert
- INSERT
- INSERT ON DUPLICATE KEY UPDATE
- Inserting multiple rows
- Basic Insert
- INSERT with AUTO INCREMENT LAST INSERT ID
- INSERT SELECT Inserting data from another Table
- Lost AUTO INCREMENT ids
Error Codes
- Error code 1064 Syntax error
- Error code 1175 Safe Update
- Error code 1215 Cannot add foreign key constraint
- 1411 - Bad Value for number date default etc.
- Access denied
- impossible position in Replication
- Cannot connect
- 144 145
- 139
- 1366
- 1062 24
Stored routines, procedures and functions
- Stored routines procedures and functions
- Stored procedure with IN OUT INOUT parameters
- Create a Function
- Create Procedure with a Constructed Prepare
- Cursors
- Multiple ResultSets
- Create a function
Reserved Words
- Reserved Words
- Errors due to reserved words
String operations
- String operations
- Find element in comma separated list
- STR TO DATE - Convert string to date
- LOWER LCASE
- REPLACE
- SUBSTRING
- UPPER, UCASE
- LENGTH
- CHAR LENGTH
- HEX
Delete
- DELETE
- Delete with Where clause
- Delete all rows from a table
- 0LIMITing deletes
- Multi-Table Deletes
- DELETE vs TRUNCATE
- Basic delete
- Multi-table DELETE
View
- VIEW
- Create a View
- A view from two tables
- Updating a table via a VIEW
- DROPPING A VIEW
Indexes and Keys
- Indexes and Keys
- Create index
- Create unique index
- Drop index
- Create composite index
- AUTO INCREMENT key
Date and Time Operations
- Date arithmetic
- 02SYSDATE NOW CURDATE
- Now
- Testing against a date range
- Extract Date from Given Date or DateTime Expression
- Using an index for a date and time lookup
Comments
LOAD DATA INFILE
- LOAD DATA INFILE
- using LOAD DATA INFILE to load large amount of data to database
- Load data with duplicates
- Import a CSV file into a table
- import export
PREPARE Statements
- PREPARE Statements
- PREPARE EXECUTE and DEALLOCATE PREPARE Statements
- Alter table with add column
- Construct and execute
ALTER TABLE
- ALTER TABLE
- Changing storage engine rebuild table change file per table
- ALTER COLUMN OF TABLE
- Renaming a table
- Change auto-increment value
- Changing the type of a primary key column
- Change column definition
- Renaming a database
- Swapping the names of two databases
- ALTER table add INDEX
- Renaming a column in a table
Joins
- 000Joins
- Joins visualized
- JOIN with subquery Derived table
- Full Outer Join
- Joining Examples
- Retrieve customers with orders -- variations on a theme
- Inner-join for 3 tables
UPDATE
- UPDATE
- Basic Update
- Update with Join Pattern
- UPDATE with ORDER BY and LIMIT
- Multiple Table UPDATE
- Bulk UPDATE
Change Password
- Change MySQL root password in Linux
- Change MySQL root password in Windows
- Process
JSON
- JSON
- Create simple table with a primary key and JSON field
- Insert a simple JSON
- Updating a JSON field
- Insert mixed data into a JSON field.
- CAST data to JSON type
- Create JSON Object and Array
MySQL Admin
- Atomic RENAME Table Reload
- Change root password
- Drop database
TRIGGERS
- TRIGGERS
- Basic Trigger
- Types of triggers
Pivot Queries
- 0Pivot queries
- Creating a pivot query
Configuration and tuning
- Configuration and tuning
- InnoDB performance
- Parameter to allow huge data to insert
- Increase the string limit for group concat
- Minimal InnoDB configuration
- Secure MySQL encryption
Converting from MyISAM to InnoDB
- Basic conversion
- Converting All Tables in one Database
SELECT
- SELECT
- SELECT with DISTINCT
- SELECT all columns
- SELECT by column name
- SELECT with LIKE
- SELECT with CASE or IF
- SELECT with Alias AS
- SELECT with a LIMIT clause
- SELECT with WHERE
- SELECT with BETWEEN
- SELECT with LIKE
- SELECT with date range
Create New User
- Create New User
- Create a MySQL User
- Specify the password
- Create new user and grant all priviliges to schema
- Renaming user
Group By
- Group By
- GROUP BY using HAVING
- Group By using Group Concat
- Group By Using MIN function
- GROUP BY with AGGREGATE functions
- GROUP BY USING SUM Function
- GROUP BY USING COUNT Function
UNION
- UNION
- Combining SELECT statements with UNION
- ORDER BY
- Pagination via OFFSET
- Combining data with different columns
- UNION ALL and UNION
- Combining and merging data on different MySQL tables with the same columns into unique rows and running query
Drop Table
- Drop Table
- Drop Table
- Drop tables from database
Data Types
- VARCHAR255 -- or not
- INT as AUTO INCREMENT
- Others
- Introduction numeric
- Integer Types
- Fixed Point Types
- Floating Point Types
- Bit Value Type
- CHARn
- DATE DATETIME TIMESTAMP YEAR and TIME
- Implicit automatic casting
Performance Tuning
- Performance Tuning
- Add the correct index
- Don't hide in function
- OR
- Set the cache correctly
- JOIN GROUP BY
- Negatives
- 0Have an INDEX
- Subqueries
- Avoid inefficient constructs
Events
ENUM
- Why ENUM
- VARCHAR as an alternative
- Adding a new option
- NULL vs NOT NULL
- TINYINT as an alternative
Install MySQL with Docker
- Simple example with docker-compose
Arithmetic
- Arithmetic
- Arithmetic Operators
- Mathematical Constants
- Trigonometry SIN COS
- Rounding ROUND FLOOR CEIL
- Raise a number to a power POW
- Square Root SQRT
- Random Numbers RAND
- Absolute Value and Sign ABS SIGN
Character Sets and Collations
- Which CHARACTER SET and COLLATION
- 0Declaration
- Connection
- Setting character sets on tables and fields
MyISAM Engine
- MyISAM Engine
- ENGINE MyISAM
Many-to-many Mapping table
- Many-to-many Mapping table
- Typical schema
Using Variables
- Setting Variables
- Row Number and Group By using variables in Select Statement
Log files
- Slow Query Log
- A List
- General Query Log
- Error Log
Partitioning
- 00Partitioning
- RANGE Partitioning
- LIST Partitioning
- HASH Partitioning
Security via GRANTs
- Best Practice
- Host of userhost
Backticks
mysqlimport
- mysqlimport
- Basic usage
- Using a custom field-delimiter
- Using a custom row-delimiter
- Handling duplicate keys
- Conditional import
- Import a standard csv
MySQL LOCK TABLE
- MySQL LOCK TABLE
- Mysql Locks
- Row Level Locking
MySQL Unions
- MySQL Unions
- Union operator
- Union ALL
- UNION ALL With WHERE
ORDER BY
- Contexts
- Basic
- ASCending DESCending
- Some tricks
MySQL client
- MySQL client
- Base login
- Execute commands
MySQL Performance Tips
- Optimizing Storage Layout for InnoDB Tables
- Building a composite index
- Select Statement Optimization
Temporary Tables
- Create Temporary Table
- Drop Temporary Table
Transaction
- Start Transaction
- COMMIT ROLLBACK and AUTOCOMMIT
- Transaction using JDBC Driver
Customize PS1
- Customize the MySQL PS1 with current database
- Custom PS1 via MySQL configuration file
Dealing with sparse or missing data
- Working with columns containg NULL values
Dynamic Un-Pivot Table using Prepared Statement
- Un-pivot a dynamic set of columns based on condition
NULL
- Uses for NULL
- Testing NULLs
Replication
- Replication
- Master - Slave Replication Setup
- Replication Errors
Connecting with UTF-8
- Python
- PHP
SSL Connection Setup
- Setup for Debian-based systems
- Setup for CentOS7 RHEL7
Handling Time Zones
- 00Handling Time Zones
- Retrieve the current date and time in a particular time zone
- Convert a stored DATE or DATETIME value to another time zone
- Retrieve stored TIMESTAMP values in a particular time zone
- What is my servers local time zone setting
- What time zone values are available in my server
Time with subsecond precision
- Time with subsecond precision
- Get the current time with millisecond precision
- Get the current time in a form that looks like a Javascript timestamp.
- Create a table with columns to store sub-second time
- Convert a millisecond-precision date time value to text
- Store a Javascript timestamp into a TIMESTAMP column
Error 1055 ONLY FULL GROUP BY
- Error 1055 ONLY FULL GROUP BY something is not in GROUP BY clause ...
- Misusing GROUP BY to return unpredictable results Murphys Law
- Misusing GROUP BY with SELECT and how to fix it
- Using and misusing GROUP BY
- ANY VALUE
Full-Text search
- Full-Text search
- Simple FULLTEXT search
- Simple BOOLEAN search
- Multi-column FULLTEXT search
Extract values from JSON type
- Extract values from JSON type
- Read JSON Array value
- JSON Extract Operators
Regular Expressions
- Regular Expressions
- REGEXP RLIKE
Recover and reset default root password
- Recover and reset the default root password for MySQL 5.7
- What happens when the initial start up of the server
- How to change the root password by using the default password
- reset root password when varrunmysqld for UNIX socket file dont exists
One to Many
- One to Many
- Example Company Tables
- Get the Employees Managed by a Single Manager
- Get the Manager for a Single Employee
JOINS Join 3 tables
- Join 3 tables on a column with the same name
Server Information
- Server Information
- SHOW VARIABLES example
- SHOW STATUS example
Recover from lost root password
- Set root password enable root user for socket and http access
Contributors

Contents