MySQL Tutorial

• What Is a Database
• What Is Database System
• Database Access Interfaces
• Database Types
• What is MySQL
• Which Scenarios Is MySQL Suitable For
• MySQL Client and Server Architecture (C/S Architecture)

MySQL Installation and Configuration

• What's New in MySQL 5.7
• MySQL Download Steps
• Install And Config MySQL in Windows
• Check if MySQL Installed Successfully
• Login to MySQL Database
• Linux MySQL Packages
• Uninstall MySQL in Linux CentOS
• Install And Config MySQL in Linux
• MySQL my.cnf Configuration File
• MySQL my.ini Configuration File
• The sql_mode of the MySQL server
• MySql Command Line Client Flashback Solution

MySQL Database Operations

• What is SQL
• Basic writing rules of SQL
• MySQL SHOW DATABASES - View Or Display Database
• MySQL CREATE DATABASE - Create A Database
• MySQL ALTER DATABASE - Modify Database
• MySQL DROP DATABASE - Delete Database
• MySQL USE - Select Database
• MySQL Comments - Single-line And Multi-line Comments
• Case Rules For SQL Statements In MySQL
• MySQL System Help

Database Design

• Database Design Overview
• Database Design Steps
• Three paradigms of Database Design
• Why Should Database Design Use Three Paradigms

MySQL Data Types

• MySQL Data Types Overview
• MySQL Integer Type
• MySQL Decimal Type
• MySQL Date and Time Types
• MySQL String Type
• MySQL Binary Type
• How to Choose Data Type in MySQL
• MySQL Escape Characters
• MySQL System Variables

MySQL Storage Engines

• What Are the MySQL Storage Engines
• MySQL InnoDB Storage Engine
• MySQL MyISAM Storage Engine
• How Data Tables Of Different Storage Engines Represented In The File System
• MySQL View and Modify the Default Storage Engine
• How To Choose MySQL Storage Engine
• MySQL Modify The Storage Engine Of Table

MySQL Basic Operations of Tables

• MySQL CREATE TABLE - Creates A Data Table
• MySQL ALTER TABLE - Modify Data Table
• MySQL Modify/delete Fields
• MySQL DORP TABLE - Delete Data Table
• MySQL Delete The Main Table Associated With Other Tables
• MySQL Display Table Structure
• MySQL Add Fields to Data Table
• Map SQL Statements to File Operations

MySQL Constraints

• MySQL Constraints Overview
• MySQL PRIMARY KEY - Primary Key
• MySQL AUTO_INCREMENT - Primary Key Auto-increment
• MySQL FOREIGN KEY - Foreign Key Constraints
• MySQL UNIQUE KEY - Unique Constraint
• MySQL CHECK - Check Constraints
• MySQL DEFAULT - Default Value
• MySQL NOT NULL - Not Null Constraint
• MySQL View Constraints In Table

MySQL Operators

• MySQL Common Operators Overview
• MySQL Arithmetic Operators
• MySQL Logical Operators
• MySQL Comparison Operators
• MySQL Bitwise Operators
• MySQL Operators Precedence
• MySQL IN and NOT IN

MySQL Function

• MySQL Function Overview

MySQL Manipulate Table Data

• MySQL SELECT - Query Data Table
• MySQL DISTINCT - Filter Duplicate Data
• MySQL AS - Set Alias
• MySQL LIMIT - Limit The Number Of Query Results
• MySQL ORDER BY - Sort Query Results
• MySQL WHERE - Conditional Query
• MySQL LIKE - Fuzzy Query
• MySQL BETWEEN...AND - Range Query
• MySQL IS NULL - Null query
• MySQL GROUP BY - Group Queries
• MySQL HAVING - Filter Grouping
• MySQL CROSS JOIN - Cross Join
• MySQL INNER JOIN - Inner Join
• MySQL LEFT/RIGHT JOIN - Outer Join
• MySQL Subqueries
• MySQL Subqueries Considerations
• MySQL Subqueries' Simple Optimization
• MySQL REGEXP - Regular Expression
• MySQL INSERT - Insert Data (Add Data)
• MySQL UPDATE - Modify Data (Update Data)
• MySQL DELETE - Delete Data
• MySQL TRUNCATE - Empty Table Records
• MySQL UPDATE - Multi-table Association Update
• How Does MySQL Handle Invalid Data Values
• What's SQL Injection and How to Avoid It

MySQL View

• What is MySQL View
• MySQL CREATE VIEW - Create A View
• MySQL Dispaly View
• MySQL ALTER VIEW - Modify View
• MySQL DORP VIEW - Delete View

MySQL Indexes

• MySQL Index
• MySQL Index Types
• MySQL CREATE INDEX - Create Index
• MySQL SHOW INDEX - View Index
• MySQL DROP INDEX - Modify and Delete Indexes
• Why Indexes Don't Work in MySQL
• MySQL Index Design Principles

MySQL Stored Procedure

• What is MySQL Stored Procedure
• MySQL CREATE PROCEDURE - Creates a Stored Procedure
• MySQL View Stored Procedure
• MySQL ALTER PROCEDURE - Modify Stored Procedure
• MySQL DROP PROCEDURE - Delete Stored Procedure
• MySQL Stored Functions
• MySQL Calls Stored Procedures And Functions
• MySQL Define and Assign Variable
• MySQL Define Conditions And Handlers
• MySQL Cursor
• MySQL Flow Control Statement

MySQL Trigger

• What is MySQL Trigger
• MySQL CREATE TRIGGER - Create Trigger
• MySQL View Trigger
• MySQL DROP TRIGGER - Modify and Delete Triggers
• MySQL Event and Timed Task
• MySQL Create Events
• MySQL Display Events
• MySQL Modify And Delete Events

MySQL Transactions

• Database Transactions Concepts and Features
• MySQL Transactions Syntax and Execution Flow
• MySQL Transactions Autocommit (On And Off)
• MySQL Transaction Isolation Level
• MySQL View and Modify Transaction Isolation Level
• MySQL Lock Mechanism
• MySQL Table Locks/Row Locks/Page Locks
• MySQL InnoDB Row Lock
• Mysql Deadlocks

MySQL Character Set

• MySQL Character Set And Collation Rules
• MySQL View Character Set And Collation Rules
• MySQL Default Character Set And Collation Rules
• MySQL Choose the Correct Character Set

MySQL User Management

• MySQL user Privilege Table
• MySQL db, tables_priv, columns_priv and procs_priv Privilege Tables
• MySQL Create User
• MySQL RENAME USER - Modify User
• MySQL DROP/DELETE USER - Delete User
• MySQL SHOW GRANTS - View User Privileges
• MySQL GRANT - User Authorization
• MySQL REVOKE - Remove User Privileges
• MySQL Login And Logout Server
• MySQL root User Change Ordinary User Password
• MySQL Change Root Password
• MySQL Reset Root Password
• MySQL Change Password
• MySQL Privilege Control

MySQL Database Backup and Recovery

• Why Databases Need To Be Backed Up
• MySQL Backup Types
• MySQL Cold Backup And Recovery
• Physical Files Required For MySQL Cold Backup
• MySQL Hot Backup And Recovery
• MySQL mysqldump - Backup Database
• MySQL Restore Database (mysql Command)
• MySQL SELECT...INTO OUTFILE - Export Table Data to File
• MySQL LOAD DATA - Recovery Database

MySQL Log

• MySQL Logs And Classification
• MySQL Error Log
• MySQL Binary Log
• MySQL Use Binary Log To Restore The Database
• MySQL General Query Log
• MySQL Slow Query Log
• MySQL Set Log Output Mode

MySQL Performance Optimization

• MySQL Performance Optimization Overview
• MySQL Analyze Query by EXPLAIN And DESCRIBE
• Optimize Indexes
• Optimize MySQL Database Structure
• Optimize The Speed of Inserting Data in MySQL
• Optimize MySQ Server

MySQL my.ini Configuration File

my.ini is the configuration file used in the MySQL database. The MySQL server will read this configuration file when it starts up. We can modify this file to achieve the purpose of updating the configuration. This section uses the my.ini configuration file under Windows system as a template to explain the parameters in the MySQL configuration file.
For the convenience of readers, we omit the comments in the my.ini file. The specific meanings of the parameters in my.ini are introduced separately below. The contents of the file are as follows:

[client]
port=3306
[mysql]
default-character-set=gbk

The above shows the parameters of the client. [client] and [mysql] are both clients. The parameters are described as follows:

• port: indicates the port number used by the MySQL client to connect to the server. The default port number is 3306. If you need to change the port number, you can modify it directly here.
• default-character-set: Indicates the default character set of the MySQL client.

[mysqld]

port=3306
basedir=C:/Program Files/MySQL/MySQL Server 5.7/
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

character-set-server=gb2312
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=35M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=69M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

The above are the parameters of the server. The parameter descriptions are shown in the following table:

parameter name	illustrate
port	Indicates the port number of the MySQL server
basedir	Indicates the installation path of MySQL
datadir	Indicates the storage location of MySQL data files, and also the storage location of data tables
default-character-set	Indicates the server-side default character set
default-storage-engine	When creating a data table, the default storage engine used
sql-mode	Indicates the parameter of the SQL mode, through which the strictness of the SQL statement can be set
max_connections	Indicates the maximum number of connections allowed to access the MySQL server at the same time. One of the connections is reserved, reserved for the exclusive use of the administrator
query_cache_size	Indicates the size of the cache at the time of query. The cache can store information previously queried through the SELECT statement. When querying again, the information can be directly retrieved from the cache, which can improve query efficiency.
table_open_cache	Indicates the total number of open tables for all processes
tmp_table_size	Indicates the maximum size allowed for each temporary table in memory
thread_cache_size	Indicates the maximum number of threads to cache
myisam_max_sort_file_size	Indicates the maximum size of temporary files allowed by MySQL when rebuilding indexes
myisam_sort_buffer_size	Indicates the cache size when rebuilding the index
key_buffer_size	Indicates the cache size of the keyword
read_buffer_size	Indicates the size of the cache for full table scans of MyISAM tables
read_rnd_buffer_size	Indicates that the sorted data is stored in the cache
sort_buffer_size	Indicates the size of the cache used for sorting

 

innodb_additional_mem_pool_size=3M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=107M
innodb_log_file_size=54M
innodb_thread_concurrency=18

The above are the parameters used by the InnoDB storage engine. The parameter descriptions are as follows:

• innodb_additional_mem_pool_size: Indicates an additional memory pool used to store the contents of InnoDB tables.
• innodb_flush_log_at_trx_commit: is the timing to set the commit log. If set to 1, InnoDB will write the transaction log to disk after each commit.
• innodb_log_buffer_size: Indicates the size of the buffer used to store log data.
• innodb_buffer_pool_size: Indicates the size of the cache. InnoDB uses a buffer pool class to store indexes and raw data.
• innodb_log_file_size: Indicates the size of the log file.
• innodb_thread_concurrency: Indicates the maximum number of threads allowed in the InnoDB storage engine.

Note: After each modification of the parameters in the my.ini file, the MySQL service must be restarted to take effect.

1. Edit my.ini for MySQL Configuration on Windows:

   • Description: Edit the my.ini file using a text editor to adjust MySQL server settings on Windows.
   • Code: Open the file using a text editor such as Notepad:

     notepad "C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
     

     Adjust the path based on your MySQL installation.

2. Changing Buffer Settings in my.ini:

   • Description: Adjust buffer settings like innodb_buffer_pool_size in the my.ini file to optimize MySQL's use of memory.
   • Code: Edit my.ini and set values, e.g.:

     innodb_buffer_pool_size = 256M
     

3. Reload my.ini Without Restarting MySQL on Windows:

   • Description: Reload the MySQL configuration without restarting the server to apply changes on Windows.
   • Code:

     mysqladmin -u root -p shutdown
     mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
     

     Adjust the path based on your MySQL installation.