MySQL from novice to expert (Basic/Advanced/Optimization)

• Chapter1 Introduction and introduction to databases
• 1-1 Course Introduction
• 1-2 Why use databases and common database concepts
• 1-3 Comparison of common DBMS
• 1-4 Comparison of RDBMS and non-RDBMS
• 1-5 4 types of relationships between ER models and table records
• Chapter2 mysql installation and configuration
• 2-1 Uninstalling mysql 8.0
• 2-2 Download, installation and configuration of mysql 8.0 and 5.7 versions
• 2-3 FAQs about mysql installation_service startup and user login
• 2-4 Mysql usage demonstration-mysql5.7 character set setting
• 2-5 Use of tools such as navicat_sqlyog_dbserver
• 2-6 mysql directory structure and after-school exercises for the first two chapters
• Chapter3 SQL Overview and Basic SELECT Query
• 3-1 SQL Overview and SQL Classification
• 3-2 SQL usage specifications and data import
• 3-3 The most basic SELECT...FROM structure
• 3-4 Column alias_duplication_NULL_DESC and other operations
• 3-5 Filter data using WHERE
• 3-6 Homework
• Chapter4 operator
• 4-1 Use of arithmetic operators
• 4-2 Use of comparison operators
• 4-3 Use of logical operators and bitwise operators
• 4-4 Operator exercises after class
• Chapter5 Sorting and paging operations
• 5-1 ORDER BY implements sorting operations
• 5-2 LIMIT implements paging operations
• 5-3 Sorting and paging after-class exercises
• Chapter6 Multi-table query
• 6-1 Why do we need multi-table queries?
• 6-2 Cartesian product errors and correct multi-table queries
• 6-3 Equivalent join VS non-equivalent join, self-join VS non-self-join
• 6-4 How to implement inner and outer joins with SQL92 and 99 syntax
• 6-5 Use SQL99 to implement 7 JOIN operations
• 6-6 Use of NATURAL JOIN and USING
• 6-7 Multi-table query after-class exercises
• Chapter7 single line function
• 7-1 Classification of functions
• 7-2 Explanation of functions of numerical types
• 7-3 Explanation of string type functions
• 7-4 Explanation of functions of date and time types
• 7-5 Process control function explanation
• 7-6 Encryption and decryption_MySQL information functions and other explanations
• 7-7 Single-line function after-class exercises
• Chapter8 aggregate function
• 8-1 5 commonly used aggregate functions
• 8-2 Use of GROUP BY
• 8-3 The use of HAVING and the execution process of SQL statements
• 8-4 Aggregate function after-class exercises
• Chapter9 subquery
• 9-1 Subquery examples and subquery classifications
• 9-2 Single row subquery case analysis
• 9-3 Multi-row subquery case analysis
• 9-4 Correlated subquery case analysis
• 9-5 Subquery after-school exercises
• 9-6 Subquery after-class exercise 2
• Chapter10 Create management data table
• 10-1 Database creation, modification and deletion
• 10-2 Common data types_Two ways to create tables
• 10-3 Modify table_rename table_delete table_clear table
• 10-4 The use of COMMIT and ROLLBACK in DCL
• 10-5 Alibaba MySQL naming convention and atomization of MySQL8DDL
• 10-6 Post-class exercises on creating management tables
• Chapter11 Add, delete, modify
• 11-1 Add data to DML
• 11-2 DML update and delete operations_MySQL8 new features calculated columns
• 11-3 Comprehensive case study of DDL and DML
• 11-4 Add, delete and modify after-class exercises
• Chapter12 mysql data type
• 12-1 Data Type Overview_Character Set Settings
• 12-2 Integer data type explanation
• 12-3 Explanation of floating point numbers, fixed point numbers and bit types
• 12-4 Date and time types explained
• 12-5 Explanation of text string types (including ENUM, SET)
• 12-6 Explanation of binary types and JSON types
• 12-7 Summary and suggestions for type usage
• Chapter13 constraint
• 13-1 Classification of data integrity and constraints
• 13-2 Use of non-null constraints
• 13-3 Use of unique constraints
• 13-4 Use of primary key constraints
• 13-5 AUTO_INCREMENT
• 13-6 Use of foreign key constraints
• 13-7 Check constraints and default value constraints
• 13-8 Constraint after-school exercises
• Chapter14 view
• 14-1 Understanding database objects and views
• 14-2 Creating and viewing views
• 14-3 Updating view data and deleting views
• 14-4 View after-class exercises
• Chapter15 stored procedure
• 15-1 Stored procedure usage instructions
• 15-2 Creating and calling stored procedures
• 15-3 Creating and calling stored functions
• 15-4 View, modify and delete stored procedures and functions
• 15-5 Stored procedure function after-class exercises
• Chapter16 Variables and structures
• 16-1 Use of GLOBAL and SESSION system variables
• 16-2 Use of session user variables and local variables
• 16-3 Program error handling mechanism
• 16-4 Use of branch structure IF
• 16-5 Use of branch structure CASE
• 16-6 LOOP_WHILE_REPEAT three loop structures
• 16-7 Use of LEAVE and ITEEATE
• 16-8 Use of cursors
• 16-9 Homework
• Chapter17 trigger
• 17-1 Create trigger
• 17-2 View Delete Trigger_Trigger After-Class Exercise
• Chapter18 MySQL8.0 new features and common table expressions
• 18-1 new features of mysql8.0
• 18-2 Common table expressions_after-class exercises
• Chapter19 Overview of Advanced Features
• 19-1 Chapter overview
• 19-2 Preparation of CentOS environment
• 19-3 MySQL uninstallation
• 19-4 Install MySQL8.0 and 5.7 versions under Linux
• 19-5 SQLyog implements remote connection for MySQL8.0 and 5.7
• 19-6 Modification of character set and explanation of underlying principles
• 19-7 Comparison rules_encoding and decoding process from request to response
• 19-8 SQL case specifications and sql_mode settings
• 19-9 MySQL directory structure and table representation in the file system
• Chapter20 User and user password operations and permission management
• 20-1 User creation, modification, deletion
• 20-2 Setting and management of user passwords
• 20-3 Permission management and access control
• 20-4 Use of roles
• 20-5 Configuration files, system variables and mysql logical architecture
• Chapter21 SQL execution process
• 21-1 SQL execution process
• 21-2 Demonstration of SQL execution flow in mysql 8.0 and 5.7
• 21-3 The use of SQL execution process and buffer pool in Oracle
• Chapter22 Storage engines and indexes
• 22-1 Setting the storage engine of the table, comparison between InnoDB and MyISAM
• 22-2 Use of storage engines such as Archive, CSV, and Memory
• 22-3 Why use indexes and their advantages and disadvantages
• 22-4 A simple index design solution
• 22-5 Iterative Design Plan for Indexes
• 22-6 The concepts of clustered index, secondary index and joint index
• 22-7 Things to note about B+ trees in InnoDB_MyISAM index scheme
• 22-8 Comparison of Hash index, AVL tree, B-tree and B+ tree
• Chapter23 InnoDB data storage structure
• 23-1 Overview of InnoDB data storage structure
• 23-2 Page structure file header and file tail
• 23-3 Minimum maximum record of page structure_Record header information of row format
• 23-4 Page structure: page directory and page header
• 23-5 Set line format and ibd file analysis Compact line format
• 23-6 Row overflow and Dynamic, Compressed, Redundant
• 23-7 Segments, fragmentation areas, and table space structures
• Chapter24 Index classification
• 24-1 Index classification
• 24-2 Three ways to add indexes to a table
• 24-3 Delete index and new index features: descending index, hidden index
• 24-4 11 situations suitable for index creation (1)
• 24-5 11 situations suitable for creating indexes (2)
• 24-6 7 situations when creating an index is not suitable
• Chapter25 Database optimization steps
• 25-1 View system performance parameters
• 25-2 Slow query log analysis, SHOW PROFILE to view SQL execution costs
• Chapter26 EXPLAIN
• 26-1 Overview of EXPLAIN and analysis of table and id fields
• 26-2 EXPLAIN中select_type、partitions
• 26-3 Analysis of ref, rows, filtered and extra in EXPLAIN
• 26-4 EXPLAIN's 4 formats and view optimizer rewrite SQL
• 26-5 Trace analysis optimizer execution plan and use of Sys schema view
• Chapter27 Index failure
• 27-1 11 situations where data preparation and indexing fail
• 27-2 11 cases of index failure 2
• Chapter28 Join query, subquery, paging query
• 28-1 Query optimization of outer joins and inner joins
• 28-2 The underlying principle of the JOIN statement
• 28-3 Subquery optimization and sorting optimization
• 28-4 GROUP BY optimization, paging query optimization
• 28-5 Use of covering index
• 28-6 Index condition push down
• 28-7 Other query optimization strategies
• 28-8 How to design the primary key of Taobao database
• Chapter29 paradigm
• 29-1 Paradigm Overview and First Normal Form
• 29-2 Second normal form and third normal form
• 29-3 Denormalization applications
• 29-4 Bath normal form, fourth normal form, fifth normal form and field key normal form
• 29-5 Paradigm practical cases
• Chapter30 ER modeling, design principles, writing specifications
• 30-1 The process of ER modeling and converting data tables
• 30-2 Database design principles and daily SQL writing specifications
• 30-3 Power Designer creates conceptual and physical data models
• Chapter31 Database tuning
• 31-1 Overall database tuning steps, optimizing MySQL server hardware and parameters
• 31-2 Database structure optimization, three major table optimization, and other three strategies
• Chapter32 affairs
• 32-1 ACID properties of transactions and status of transactions
• 32-2 Explicit transactions and implicit transactions
• 32-3 Examples of using transactions
• Chapter33 Concurrency issues and isolation levels
• 33-1 Data concurrency issues and 4 isolation levels
• 33-2 View and set MySQL isolation level
• 33-3 Read demo in uncommitted isolation
• 33-4 Demonstration of Read Committed and Repeatable Read Isolation
• 33-5 Demonstration and solution of phantom reading
• Chapter34 Redo log, Undo log
• 34-1 Understanding Redo logs and Undo logs, and why Redo logs are needed
• 34-2 Analysis of Redo log flushing strategy and process
• 34-3 Write to Redo Log Buffer and Redo Log File
• 34-4 Overview and writing process of Undo log
• Chapter35 Lock
• 35-1 Overview of locks, concurrency issues of reading and writing
• 35-2 Understanding S lock and X lock from the perspective of data operation types
• 35-3 Table locks: S lock, X lock, intention lock
• 35-4 Table locks, self-increment locks and metadata locks
• 35-5 Row lock record lock, gap lock
• 35-6 Line lock, key lock and insertion intention lock
• 35-7 Understanding of page locks, use of optimistic locks and pessimistic locks
• 35-8 Division of locking methods: implicit lock and explicit lock
• 35-9 Understanding global locks and deadlocks
• 35-10 Lock memory structure and monitoring strategy
• Chapter36 MVCC
• 36-1 MVCC solves reading and writing problems
• 36-2 MVCC Three Musketeers: Hidden fields, Undolog version chain, ReadView
• 36-3 MVCC operation process under read committed and repeatable read isolation levels
• 36-4 MVCC’s process for solving phantom reads under repeatable reads
• Chapter37 Six major log files
• 37-1 Overview of the six major log files
• 37-2 General query log, error log
• 37-3 Binlog log parameter setting and data recovery demonstration
• 37-4 Binlog deletion, binlog writing mechanism and two-phase submission
• 37-5 Analysis of the steps and principles of relay logs and master-slave replication
• 37-6 Implementation of one master and one slave architecture and master-slave synchronization
• 37-7 Binlog format setting instructions
• Chapter38 Data synchronization and backup
• 38-1 Data synchronization consistency problem solving
• 38-2 Overview of data backup and implementing logical backup data with mysqldump
• 38-3 Demonstrate mysql to implement logical recovery of data
• 38-4 Demonstration of physical backup and physical recovery, export and import of table data
• 38-5 Database migration and how to delete the database without running away
• 38-6 Message