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

Install And Config MySQL in Windows

MySQL is allowed to run on a variety of platforms, but installation methods vary from platform to platform. This section mainly introduces how to install and configure MySQL on the Windows platform.

There are two ways to install MySQL on the Windows platform:

• MySQL graphical installation (.msi installation file).
• Install-free version (.zip compressed file).

The steps for users to install and configure MySQL using the graphical installation package are as follows:

Install MySQL

Step 1): Double-click the downloaded MySQL installation file to enter the MySQL installation interface, first enter the "License Agreement" window, select the "I accept the license terms" check box, and click the "Next" button.

Some will directly enter the "Choosing a Setup Type" window, select the installation type that suits you according to the installation type description file on the right, and select the default installation type here, as shown in the figure.


  Note: There are 5 installation types listed in the figure, they are:

1. Developer Default: The default installation type.
2. Server only: only as a service.
3. Client only: only as a client.
4. Full: fully installed.
5. Custom: Custom installation type.

Step 2): Install the Windows system framework according to the selected installation type, click the Execute button, and the installation program will automatically complete the installation of the framework, as shown in the figure.
 
Step 3): When the installer window pops up, tick the "I agree to the license terms and conditions" checkbox and click the "Install" button.
Step 4): The "Setup Successful" interface pops up, indicating that the framework has been installed, click the "Close" button. All frame installations can refer to this operation.
Step 5): After the installation is complete, Complete will be displayed under the [status] list. After the required frameworks are installed successfully, click the Next button, as shown in the figure.
 
Step 6): Enter the installation confirmation window, click the Execute button to start the installation of MySQL components, as shown in the figure.
 
Step 7): Start to install the MySQL file. After the installation is complete, Complete will be displayed under the [Status] list, as shown in the figure.
 

Configure MySQL

After the MySQL installation is completed, the server needs to be configured. The specific configuration steps are as follows:

Step 1): In the last step of the installation, click the Next button to enter the server configuration window, confirm the configuration information, and click the Next button after confirmation, as shown in the figure Show.


  Step 2): Enter the MySQL network type configuration window, use the default settings, and click the Next button, as shown in the figure.


  Step 3): Enter the MySQL server type configuration window, use the default settings, and click the Next button, as shown in the figure.

The specific meanings of the three options in the above figure are as follows:

• Development Machine: The MySQL server installed as part of the development machine takes the least memory among the three optional types.
• Server Machine: The installed MySQL server is part of the server machine, and the memory occupied is in the middle of the three types.
• Dedicated MySQL Server Machine: Install a dedicated MySQL database server, occupying all the effective memory of the machine.

The MySQL port number defaults to 3306, if there is no special requirement, it is generally not recommended to modify it. Continue to click the Next button.
 

Step 4): Enter the password setting window of the server, enter the login password twice (letters and symbols are recommended), and click the Next button, as shown in the figure.

Step 5): Enter the server name window to set the server name, there is no special need and it is not recommended to modify it. Go ahead and click the Next button as shown.


  Step 6): Open the confirmation server setting window, click the Execute button to complete the configuration of MySQL, as shown in the figure.

After all the tests are passed, continue to click Finish and Next to install and the configuration is complete.

Step 7): Finally, open the Windows Task Manager dialog box, you can see that the MySQL service process mysqld.exe has been started, as shown in the figure.


So far, the installation and configuration of MySQL database under Windows operating system is completed.

1. Configure MySQL on Windows 10/11:

   • Description: Configure MySQL settings on Windows, such as setting up the root password and configuring other options.
   • Command (to set root password):

     mysqladmin -u root password "new_password"
     

2. Securing MySQL installation on Windows:

   • Description: Secure the MySQL installation on Windows by setting a strong root password and removing anonymous user accounts.
   • Commands:

     ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
     DELETE FROM mysql.user WHERE user = '' AND host = 'localhost';
     FLUSH PRIVILEGES;
     

3. MySQL configuration file location in Windows:

   • Description: Find the MySQL configuration file on Windows, which typically is my.ini.
   • Default location:

     C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
     

4. Basic MySQL setup on Windows:

   • Description: Perform basic setup tasks on MySQL in Windows, including creating a new database and user.
   • Commands (in MySQL Command Line Client):

     CREATE DATABASE your_database;
     CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'user_password';
     GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost';
     FLUSH PRIVILEGES;