MySQL Tutorials - Herong's Tutorial Examples

https://www.herongyang.com/MySQL

Copyright © 2021 Dr. Herong Yang. All rights reserved.

MySQL Tutorials 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 2021 (Version v4.42) with minor changes.

Table of Contents

About This Book

Introduction of SQL

What Is SQL

SQL History and Revisions

SQL and Database Terminologies

MySQL Introduction and Installation

What Is MySQL

Downloading and Installing MySQL

Issues during MySQL Installation

Starting and Stopping MySQL Server

Un-Installing MySQL

MySQL Command Line Client Tool

What Is MySQL Shell

What Is MySQL Workbench

Using MySQL Non-Install Package

MySQL Data Directory Initialization

Creating MySQL Windows Service

%mysql% Variable for MySQL Server Path

Introduction of MySQL Programs

List 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

PHP Programs and MySQL Server

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

JDBC Driver Connection URL

Connection URL Tests on Older MySQL Connector/J

Creating Connections with DataSource Class

Getting Driver and Server Information

Creating Tables with AUTO_INCREMENT Columns

"INSERT INTO" Statements

Datatypes and Data Literals

Introduction of Datatype

Data Binary Representations

Data Literals

Data Literal Evaluation

Character String Literal Evaluation Examples

Hex String Literal Evaluation Examples

Numeric Literal Evaluation Examples

Operations and Expressions

What Is Expression

Arithmetic Operations

Predicate Operations

Collations in Predicate Operations

Date and Time Operations

Examples of Different Types of Operation

Character Strings and Bit Strings

Operatins and Functions for Character Strings

Operatins and Functions for Bit Strings

Commonly Used Functions

Flow Control Functions

Character String Functions

Numeric Value Functions

Date and Time Functions

JSON Document Functions

XML Document Functions

Spatial Data Functions

Cast Function

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

UPDATE with Joined Tables

DELETE FROM - Statement to Delete Records from Tables

Using SELECT to Query Database

SELECT Statements

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

Locks Used in MySQL

Defining and Calling Stored Procedures

What Is a Stored Procedures

CREATE PROCEDURE - Statement to Create Stored Procedures

Stored Procedure Parameters

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

Using Administration Statements

"SHOW" - Show Server Information

"SHOW STATUS ..." - Server Status Variables

Capture Slow Queries

Analyze Slow Query Log File

Solutions for Slow Queries

Server Performance Troubleshooting

Storage Engines in MySQL Server

What Are Storage Engines

What Is InnoDB Storage Engine

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

Primary Key Index on InnoDB Table

InnoDB Primary Key Index on Large Table

"SELECT COUNT(*)" on InnoDB Table

InnoDB Table with Long Rows

Secondary Index on InnoDB Table

Unique Index on InnoDB Table

Performance of Index Range on InnoDB Table

Workaound on InnoDB "SELECT COUNT(*)" Problem

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

MySQL Server on macOS

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 Tutorials

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)

Archived: Installing MySQL 4.0.18

References

Full Version in PDF/EPUB

Keywords: MySQL, SQL, Language, Database, Tutorial