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 SHOW INDEX: View Index

The SHOW INDEX statement in MySQL is used to display information about the indexes in a table. It's a useful way to see what indexes are in place and how they are structured.

Here is the basic syntax of the SHOW INDEX statement:

SHOW INDEX FROM table_name;

Where table_name is the name of the table for which you want to see the indexes.

For example, if you have a table named 'users', you could see its indexes like this:

SHOW INDEX FROM users;

This statement will return a result set with the following columns:

• Table: The name of the table.
• Non_unique: 0 if the index can't contain duplicates, 1 if it can.
• Key_name: The name of the index.
• Seq_in_index: The position of the column in the index.
• Column_name: The name of the column in the index.
• Collation: How the column is sorted in the index. 'A' means ascending, 'B' means descending, NULL means not sorted.
• Cardinality: An estimate of the number of unique values in the index.
• Sub_part: The number of indexed characters if the column is only partly indexed, NULL if the entire column is indexed.
• Packed: Indicates how the key is packed. NULL if it is not.
• Null: Contains 'YES' if the column can contain NULL values and '' if it can't.
• Index_type: The type of the index (BTREE, FULLTEXT, HASH, RTREE).
• Comment: Additional information about the index.

The SHOW INDEX statement is a good way to understand the structure of your indexes, which can help you optimize your database for better performance.

1. MySQL SHOW INDEX Example:

   • Description: The SHOW INDEX statement in MySQL is used to display the indexes of a table, including information such as index name, column names, and index type.
   • Code:

     -- Example of using SHOW INDEX
     SHOW INDEX FROM your_table;
     

2. SHOW INDEX vs SHOW INDEXES in MySQL:

   • Description: SHOW INDEX and SHOW INDEXES are interchangeable; both statements provide similar information about indexes in MySQL.
   • Code:

     -- Example of using SHOW INDEXES (equivalent to SHOW INDEX)
     SHOW INDEXES FROM your_table;
     

3. MySQL SHOW INDEX Extended Option:

   • Description: The EXTENDED option with SHOW INDEX provides additional information, including index comments and cardinality.
   • Code:

     -- Example of using SHOW INDEX with EXTENDED option
     SHOW INDEX FROM your_table EXTENDED;
     

4. Examples of Using SHOW INDEX in MySQL Queries:

   • Description: Demonstrate various scenarios where SHOW INDEX is applied to retrieve information about indexes.
   • Code: Experiment with different tables and observe the displayed index information.

     -- Additional examples of using SHOW INDEX
     SHOW INDEX FROM another_table;
     SHOW INDEX FROM your_table WHERE Non_unique = 0;