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

The sql_mode of the MySQL server

Unlike other databases, the MySQL server can run in different SQL modes, and these modes can be applied in different ways for different clients, depending on the value of the sql_mode system variable.
 
The SQL schema defines the SQL syntax and data validation (data validation checks) supported by the MySQL database, making it easier to use MySQL in different environments.
 
In MySQL, SQL schemas are commonly used to solve the following types of problems:

• By setting the SQL Mode, data verification with different degrees of strictness can be completed, effectively ensuring the accuracy of the data.
• By setting the SQL Mode to ANSI mode, most of the SQL can be guaranteed to conform to the standard SQL syntax, so that no major modification is required when migrating between different databases.
• Before migrating data between different databases, setting SQL Mode can make it easier to migrate data in MySQL to the target database.

Common values ​​for the sql_mode system variable

The values ​​that are commonly used in several SQL modes are listed below.

1) TRIICT_ALL_TABLES and STRICT_TRANS_TABLES

If the value of sql_mode is set to TRICT_ALL_TABLES and STRICT_TRANS_TABLES, then MySQL will enable "strict" mode. In strict mode, the MySQL server treats received data more strictly, it will not convert these non-conforming data to the nearest valid value, but will refuse to accept them.

2) TRADITIONAL

Similar to strict mode, but gives an error instead of a warning for inserted unqualified values. It can be applied to transactional tables and non-transactional tables. When used for transactional tables, it will be rolled back as soon as an error occurs.

If you are using a non-transactional storage engine, it is recommended not to set the SQL Mode value to TRADITIONAL, because the operation performed before the error will not be rolled back, which will result in only part of the operation.

3) ANSI_QUOTES

The MySQL server recognizes double quotes as an identifier quote character, not a string quote character. So when ANSI_QUOTES is enabled, double quotes cannot be used to quote strings.

4) PIPES_AS_CONCAT

Causes the MySQL server to treat it ||as a standard SQL string concatenation operator, instead of treating it as a synonym for the OR operator.

In Oracle and other databases, it ||is regarded as a string connection operator, so ||SQL containing operators in other databases will not be executed in MySQL. In order to solve this problem, MySQL provides this value.

5) ANSI

ANSI_QUOTES, PIPES_AS_CONCAT, and several other mode values ​​are enabled at the same time, making the MySQL server behave more like standard SQL than it does by default.

How to set sql_mode

When setting the SQL mode, specify a value consisting of a single mode value or multiple mode values ​​(multiple mode values ​​separated by commas), or specify an empty string to clear the value. Mode values ​​are not case sensitive.
 
If you want to set the SQL mode when you start the server, you can set the system variable sql_mode on the mysqld command line, or in an options file. The following statement can be used:

sql_mode= "TRADITIONAL "
sql_mode= "ANSI_ QUOTES, PIPES_ AS_ CONCAT"

If you just want to change the SQL mode at runtime, you can use the SET statement to set the sql_mode system variable.

SET sql_mode = ' TRADITIONAL' ;

 
If you want to set a global SQL mode, you need to add the GLOBAL keyword:

SET GLOBAL sql_mode = ' TRADITIONAL';

Setting global variables requires SUPER administrative privileges. The newly set global variable value will become the default SQL mode for subsequent connections to the client.
 
If you want to get the current session or global SQL mode value, you can use the following statement:

SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL. sql_mode;

Its return value consists of all currently enabled modes, separated by a comma. Returns a null value if no mode is currently enabled.

1. How to Check sql_mode in MySQL:

   • Description: Use the following SQL query to check the current sql_mode setting:

     SELECT @@sql_mode;
     

2. Changing sql_mode in MySQL:

   • Description: To change the sql_mode, you can use the following query:

     SET GLOBAL sql_mode = 'new_sql_mode';
     

     Replace 'new_sql_mode' with the desired SQL mode.

3. Setting Strict Mode in MySQL:

   • Description: To set strict mode, include STRICT_ALL_TABLES in the sql_mode:

     SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';
     

4. MySQL sql_mode for Compatibility:

   • Description: For compatibility with other databases, set sql_mode to a value like:

     SET GLOBAL sql_mode = 'MYSQL40';