Course Introduction

Why use databases and common database concepts

Comparison of common DBMS

Comparison of RDBMS and non-RDBMS

4 types of relationships between ER models and table records

Uninstalling mysql 8.0

Download, installation and configuration of mysql 8.0 and 5.7 versions

FAQs about mysql installation_service startup and user login

Mysql usage demonstration-mysql5.7 character set setting

Use of tools such as navicat_sqlyog_dbserver

mysql directory structure and after-school exercises for the first two chapters

SQL Overview and SQL Classification

SQL usage specifications and data import

The most basic SELECT...FROM structure

Column alias_duplication_NULL_DESC and other operations

Filter data using WHERE

Homework

Use of arithmetic operators

Use of comparison operators

Use of logical operators and bitwise operators

Operator exercises after class

ORDER BY implements sorting operations

LIMIT implements paging operations

Sorting and paging after-class exercises

Why do we need multi-table queries?

Cartesian product errors and correct multi-table queries

Equivalent join VS non-equivalent join, self-join VS non-self-join

How to implement inner and outer joins with SQL92 and 99 syntax

Use SQL99 to implement 7 JOIN operations

Use of NATURAL JOIN and USING

Multi-table query after-class exercises

Classification of functions

Explanation of functions of numerical types

Explanation of string type functions

Explanation of functions of date and time types

Process control function explanation

Encryption and decryption_MySQL information functions and other explanations

Single-line function after-class exercises

5 commonly used aggregate functions

Use of GROUP BY

The use of HAVING and the execution process of SQL statements

Aggregate function after-class exercises

Subquery examples and subquery classifications

Single row subquery case analysis

Multi-row subquery case analysis

Correlated subquery case analysis

Subquery after-school exercises

Subquery after-class exercise 2

Database creation, modification and deletion

Common data types_Two ways to create tables

Modify table_rename table_delete table_clear table

The use of COMMIT and ROLLBACK in DCL

Alibaba MySQL naming convention and atomization of MySQL8DDL

Post-class exercises on creating management tables

Add data to DML

DML update and delete operations_MySQL8 new features calculated columns

Comprehensive case study of DDL and DML

Add, delete and modify after-class exercises

Data Type Overview_Character Set Settings

Integer data type explanation

Explanation of floating point numbers, fixed point numbers and bit types

Date and time types explained

Explanation of text string types (including ENUM, SET)

Explanation of binary types and JSON types

Summary and suggestions for type usage

Classification of data integrity and constraints

Use of non-null constraints

Use of unique constraints

Use of primary key constraints

AUTO_INCREMENT

Use of foreign key constraints

Check constraints and default value constraints

Constraint after-school exercises

Understanding database objects and views

Creating and viewing views

Updating view data and deleting views

View after-class exercises

Stored procedure usage instructions

Creating and calling stored procedures

Creating and calling stored functions

View, modify and delete stored procedures and functions

Stored procedure function after-class exercises

Use of GLOBAL and SESSION system variables

Use of session user variables and local variables

Program error handling mechanism

Use of branch structure IF

Use of branch structure CASE

LOOP_WHILE_REPEAT three loop structures

Use of LEAVE and ITEEATE

Use of cursors

Homework

Create trigger

View Delete Trigger_Trigger After-Class Exercise

new features of mysql8.0

Common table expressions_after-class exercises

Chapter overview

Preparation of CentOS environment

MySQL uninstallation

Install MySQL8.0 and 5.7 versions under Linux

SQLyog implements remote connection for MySQL8.0 and 5.7

Modification of character set and explanation of underlying principles

Comparison rules_encoding and decoding process from request to response

SQL case specifications and sql_mode settings

MySQL directory structure and table representation in the file system

User creation, modification, deletion

Setting and management of user passwords

Permission management and access control

Use of roles

Configuration files, system variables and mysql logical architecture

SQL execution process

Demonstration of SQL execution flow in mysql 8.0 and 5.7

The use of SQL execution process and buffer pool in Oracle

Setting the storage engine of the table, comparison between InnoDB and MyISAM

Use of storage engines such as Archive, CSV, and Memory

Why use indexes and their advantages and disadvantages

A simple index design solution

Iterative Design Plan for Indexes

The concepts of clustered index, secondary index and joint index

Things to note about B+ trees in InnoDB_MyISAM index scheme

Comparison of Hash index, AVL tree, B-tree and B+ tree

Overview of InnoDB data storage structure

Page structure file header and file tail

Minimum maximum record of page structure_Record header information of row format

Page structure: page directory and page header

Set line format and ibd file analysis Compact line format

Row overflow and Dynamic, Compressed, Redundant

Segments, fragmentation areas, and table space structures

Index classification

Three ways to add indexes to a table

Delete index and new index features: descending index, hidden index

11 situations suitable for index creation (1)

11 situations suitable for creating indexes (2)

7 situations when creating an index is not suitable

View system performance parameters

Slow query log analysis, SHOW PROFILE to view SQL execution costs

Overview of EXPLAIN and analysis of table and id fields

EXPLAIN中select_type、partitions

Analysis of ref, rows, filtered and extra in EXPLAIN

EXPLAIN's 4 formats and view optimizer rewrite SQL

Trace analysis optimizer execution plan and use of Sys schema view

11 situations where data preparation and indexing fail

11 cases of index failure 2

Query optimization of outer joins and inner joins

The underlying principle of the JOIN statement

Subquery optimization and sorting optimization

GROUP BY optimization, paging query optimization

Use of covering index

Index condition push down

Other query optimization strategies

How to design the primary key of Taobao database

Paradigm Overview and First Normal Form

Second normal form and third normal form

Denormalization applications

Bath normal form, fourth normal form, fifth normal form and field key normal form

Paradigm practical cases

The process of ER modeling and converting data tables

Database design principles and daily SQL writing specifications

Power Designer creates conceptual and physical data models

Overall database tuning steps, optimizing MySQL server hardware and parameters

Database structure optimization, three major table optimization, and other three strategies

ACID properties of transactions and status of transactions

Explicit transactions and implicit transactions

Examples of using transactions

Data concurrency issues and 4 isolation levels

View and set MySQL isolation level

Read demo in uncommitted isolation

Demonstration of Read Committed and Repeatable Read Isolation

Demonstration and solution of phantom reading

Understanding Redo logs and Undo logs, and why Redo logs are needed

Analysis of Redo log flushing strategy and process

Write to Redo Log Buffer and Redo Log File

Overview and writing process of Undo log

Overview of locks, concurrency issues of reading and writing

Understanding S lock and X lock from the perspective of data operation types

Table locks: S lock, X lock, intention lock

Table locks, self-increment locks and metadata locks

Row lock record lock, gap lock

Line lock, key lock and insertion intention lock

Understanding of page locks, use of optimistic locks and pessimistic locks

Division of locking methods: implicit lock and explicit lock

Understanding global locks and deadlocks

Lock memory structure and monitoring strategy

MVCC solves reading and writing problems

MVCC Three Musketeers: Hidden fields, Undolog version chain, ReadView

MVCC operation process under read committed and repeatable read isolation levels

MVCC’s process for solving phantom reads under repeatable reads

Overview of the six major log files

General query log, error log

Binlog log parameter setting and data recovery demonstration

Binlog deletion, binlog writing mechanism and two-phase submission

Analysis of the steps and principles of relay logs and master-slave replication

Implementation of one master and one slave architecture and master-slave synchronization

Binlog format setting instructions

Data synchronization consistency problem solving

Overview of data backup and implementing logical backup data with mysqldump

Demonstrate mysql to implement logical recovery of data

Demonstration of physical backup and physical recovery, export and import of table data

Database migration and how to delete the database without running away

Message