MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
MySQL Tutorials - Herong's Tutorial Examples
https://www.herongyang.com/MySQL
Copyright © 1999-2023 Herong Yang. All rights reserved.
This MySQL tutorial book is a collection of notes and sample codes written by the author while he was learning MySQL himself, an ideal tutorial guide for beginners. Topics include introduction of Structured Query Language (SQL); installation of MySQL server on Windows, Linux, and macOS; using MySQL client program; accessing MySQL server from PHP, Java and Perl programs; SQL data types, literals, operations, expressions, and functions; Statements of Data Definition Language (DDL), Data Manipulation Language (DML), and Query Language; creating and using indexes; using window functions; stored procedures; transaction management; locks and deadlocks; InnoDB and other storage engines. Updated in 2023 (Version v4.46) with minor changes.
Table of Contents
SQL and Database Terminologies
MySQL Introduction and Installation
Downloading and Installing MySQL
Issues during MySQL Installation
Starting and Stopping MySQL Server
MySQL Command Line Client Tool
Using MySQL Non-Install Package
MySQL Data Directory Initialization
Creating MySQL Windows Service
%mysql% Variable for MySQL Server Path
Introduction of MySQL Programs
mysqld - The MySQL Server Program
mysqladmin - The Client Tool for Administrators
mysql - The Client Tool for End Users
Using "mysql" Command to Run SQL Statements
mysqldump - Dumping Data to Files
--secure-file-priv="" - MySQL Server Option
mysqlimport - Loading Data from Files
Configuring PHP for MySQL Server Access
MySQL Authentication Method "caching_sha2_password"
mysqli_connect() and Other MySQL Functions
MySqlLoop.php - MySQL Functions Test
Perl Programs and MySQL Servers
Accessing MySQL Servers from Perl Programs
HelloMySQL.pl - Sample Perl Program to Use MySQL
Java Programs and MySQL Servers
MySQL Connector/J - Download and Installation
Loading JDBC Driver Class - com.mysql.cj.jdbc.Driver
Connection URL Tests on Older MySQL Connector/J
Creating Connections with DataSource Class
Getting Driver and Server Information
Creating Tables with AUTO_INCREMENT Columns
Character String Literal Evaluation Examples
Hex String Literal Evaluation Examples
Numeric Literal Evaluation Examples
Collations in Predicate Operations
Examples of Different Types of Operation
Character Strings and Bit Strings
Operations and Functions for Character Strings
Operations and Functions for Bit Strings
Table Column Types for Different Types of Values
Table Column Types for Character Strings
Table Column Types for Byte Strings
Table Column Types for Bit Strings
Table Column Types for Exact Numbers
Table Column Types for Approximate Numbers
Table Column Types for Date and Time Values
Table Column Types for LOB (Large OBject)
Table Column Types for Look Up Values
Table Column Types for JSON Documents
Table Column Types for Spatial Geometry Data
Using DDL to Create Tables and Indexes
CREATE TABLE - Statement to Create Tables
Column Options When Creating Tables
CREATE INDEX - Statement to Create Indexes
ALTER TABLE - Statement to Alter Table Structures
Using DML to Insert, Update and Delete Records
INSERT INTO - Statement to Insert Records to Tables
UPDATE - Statement to Update Records in Tables
DELETE FROM - Statement to Delete Records from Tables
Using SELECT to Query Database
FROM Clause of a SELECT Statement
JOIN - Operation to Join Two Tables
JoinTable.sql - Example of Join Tables
WHERE Clause of a SELECT Statement
ORDER BY Clause of a SELECT Statement
GROUP BY Clause of a SELECT Statement
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
CREATE PROCEDURE - Statement to Create Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
System Variables Like @@version
Scopes of System Variables: Global and Session
User-Defined Variables Like @x
User-Defined vs. Stored Procedure Variables
"SHOW" - Show Server Information
"SHOW STATUS ..." - Server Status Variables
Server Performance Troubleshooting
Storage Engines in MySQL Server
Convert Table to InnoDB Storage Engine
Clustered Index Used by InnoDB Engine
Statistic Information on InnoDB Tables
MySQL Status Variables for InnoDB Engine
MySQL System Variables for InnoDB Engine
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Performance of Inserting Integers to MySQL Database
"SHOW PROFILE" - Query Profiling
Impact of Binary Logging on INSERT
Impact of InnoDB Log Buffer on INSERT
Performance Comparison of Inserting Integers vs. Strings
General Guidelines on Bulk Changes
Bulk Delete with a Simple Condition
Delete Records That Are Not Referenced
Reset AUTO_INCREMENT Value on Large Tables
Install MySQL Database Server on macOS
PHP mysqli_connect() Error on "localhost" on macOS
Installing MySQL Server on Linux
Install MySQL Database Server on CentOS
Manage MySQL Server 'mysqld' on CentOS
Set MySQL Server "root" Password on CentOS
MySQL Server File Locations on CentOS
MySQL Server Data Backups on CentOS
MySQL Server Log Files on CentOS
"Multiple files found for the same tablespace ID" Error
Connection, Performance and Second Instance on Linux
Archived: Installing MySQL 5.7.10 with Installer
Archived: Issues MySQL 5.7.10 Installer
Archived: MySQL Connector/J 5.1 Installation
Archived: Installing MySQL 5.6.28
Archived: Installing MySQL 5.5.15
Archived: Installing MySQL 5.0.2 (Alpha)
Keywords: MySQL, SQL, Language, Database, Tutorial