MySQL from novice to expert (Basic/Advanced/Optimization)-Free online video tutorial-php.cn

Community Learn Tools Library Leisure

English

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

MySQL course ceiling: explanation of 6 major paradigms, 7 major log analysis, 7 major SQL performance analysis tools, 9 major storage engine analysis, 10 major categories and 30 sub-categories of optimization scenarios, 15 application explanations of different locks, 18 rules for creating indexes , 300+ high-definition and uncensored technical analysis pictures...

Intermediate

mysql

optimization

Base

advanced

204267 person learning

200class hour

03 days 13 hours 40 minutes 03 secondsTotal course duration

2022-03-29update

王林

Gold lecturer

A total of 180 courses Student180310 Lecturer rating5.8

有朋自远方来，虽远必诛！More>>

Course Introduction Course Outline Data Download Other Courses

You will learn：

MySQL course ceiling: explanation of 6 major paradigms, 7 major log analysis, 7 major SQL performance analysis tools, 9 major storage engine analysis, 10 major categories and 30 sub-categories of optimization scenarios, 15 application explanations of different locks, 18 rules for creating indexes , 300+ high-definition and uncensored technical analysis pictures...

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

Chapter1 Introduction and introduction to databases
expand retract

Section1 Course Introduction
duration： 09 minutes 13 seconds

Section2 Why use databases and common database concepts
duration： 16 minutes 02 seconds

Section3 Comparison of common DBMS
duration： 18 minutes 47 seconds

Section4 Comparison of RDBMS and non-RDBMS
duration： 20 minutes 04 seconds

Section5 4 types of relationships between ER models and table records
duration： 20 minutes 32 seconds
Chapter2 mysql installation and configuration
expand retract

Section1 Uninstalling mysql 8.0
duration： 20 minutes 41 seconds

Section2 Download, installation and configuration of mysql 8.0 and 5.7 versions
duration： 35 minutes 29 seconds

Section3 FAQs about mysql installation_service startup and user login
duration： 19 minutes 18 seconds

Section4 Mysql usage demonstration-mysql5.7 character set setting
duration： 27 minutes 52 seconds

Section5 Use of tools such as navicat_sqlyog_dbserver
duration： 28 minutes 59 seconds

Section6 mysql directory structure and after-school exercises for the first two chapters
duration： 33 minutes 55 seconds
Chapter3 SQL Overview and Basic SELECT Query
expand retract

Section1 SQL Overview and SQL Classification
duration： 34 minutes 29 seconds

Section2 SQL usage specifications and data import
duration： 32 minutes 01 seconds

Section3 The most basic SELECT...FROM structure
duration： 09 minutes 01 seconds

Section4 Column alias_duplication_NULL_DESC and other operations
duration： 33 minutes 18 seconds

Section5 Filter data using WHERE
duration： 10 minutes 06 seconds

Section6 Homework
duration： 09 minutes 34 seconds
Chapter4 operator
expand retract

Section1 Use of arithmetic operators
duration： 17 minutes 04 seconds

Section2 Use of comparison operators
duration： 58 minutes 02 seconds

Section3 Use of logical operators and bitwise operators
duration： 22 minutes 57 seconds

Section4 Operator exercises after class
duration： 17 minutes 26 seconds
Chapter5 Sorting and paging operations
expand retract

Section1 ORDER BY implements sorting operations
duration： 21 minutes 32 seconds

Section2 LIMIT implements paging operations
duration： 30 minutes 53 seconds

Section3 Sorting and paging after-class exercises
duration： 10 minutes 48 seconds
Chapter6 Multi-table query
expand retract

Section1 Why do we need multi-table queries?
duration： 22 minutes 13 seconds

Section2 Cartesian product errors and correct multi-table queries
duration： 35 minutes 42 seconds

Section3 Equivalent join VS non-equivalent join, self-join VS non-self-join
duration： 16 minutes 33 seconds

Section4 How to implement inner and outer joins with SQL92 and 99 syntax
duration： 35 minutes 54 seconds

Section5 Use SQL99 to implement 7 JOIN operations
duration： 21 minutes 39 seconds

Section6 Use of NATURAL JOIN and USING
duration： 10 minutes 54 seconds

Section7 Multi-table query after-class exercises
duration： 34 minutes 31 seconds
Chapter7 single line function
expand retract

Section1 Classification of functions
duration： 12 minutes 45 seconds

Section2 Explanation of functions of numerical types
duration： 36 minutes 10 seconds

Section3 Explanation of string type functions
duration： 30 minutes 59 seconds

Section4 Explanation of functions of date and time types
duration： 52 minutes 37 seconds

Section5 Process control function explanation
duration： 21 minutes 42 seconds

Section6 Encryption and decryption_MySQL information functions and other explanations
duration： 25 minutes 05 seconds

Section7 Single-line function after-class exercises
duration： 29 minutes 02 seconds
Chapter8 aggregate function
expand retract

Section1 5 commonly used aggregate functions
duration： 43 minutes 07 seconds

Section2 Use of GROUP BY
duration： 31 minutes 29 seconds

Section3 The use of HAVING and the execution process of SQL statements
duration： 44 minutes 00 seconds

Section4 Aggregate function after-class exercises
duration： 19 minutes 03 seconds
Chapter9 subquery
expand retract

Section1 Subquery examples and subquery classifications
duration： 27 minutes 25 seconds

Section2 Single row subquery case analysis
duration： 29 minutes 18 seconds

Section3 Multi-row subquery case analysis
duration： 25 minutes 14 seconds

Section4 Correlated subquery case analysis
duration： 45 minutes 57 seconds

Section5 Subquery after-school exercises
duration： 40 minutes 16 seconds

Section6 Subquery after-class exercise 2
duration： 48 minutes 28 seconds
Chapter10 Create management data table
expand retract

Section1 Database creation, modification and deletion
duration： 37 minutes 54 seconds

Section2 Common data types_Two ways to create tables
duration： 30 minutes 18 seconds

Section3 Modify table_rename table_delete table_clear table
duration： 22 minutes 52 seconds

Section4 The use of COMMIT and ROLLBACK in DCL
duration： 21 minutes 58 seconds

Section5 Alibaba MySQL naming convention and atomization of MySQL8DDL
duration： 20 minutes 29 seconds

Section6 Post-class exercises on creating management tables
duration： 38 minutes 37 seconds
Chapter11 Add, delete, modify
expand retract

Section1 Add data to DML
duration： 26 minutes 28 seconds

Section2 DML update and delete operations_MySQL8 new features calculated columns
duration： 20 minutes 40 seconds

Section3 Comprehensive case study of DDL and DML
duration： 36 minutes 50 seconds

Section4 Add, delete and modify after-class exercises
duration： 37 minutes 20 seconds
Chapter12 mysql data type
expand retract

Section1 Data Type Overview_Character Set Settings
duration： 23 minutes 19 seconds

Section2 Integer data type explanation
duration： 25 minutes 22 seconds

Section3 Explanation of floating point numbers, fixed point numbers and bit types
duration： 36 minutes 29 seconds

Section4 Date and time types explained
duration： 37 minutes 52 seconds

Section5 Explanation of text string types (including ENUM, SET)
duration： 35 minutes 18 seconds

Section6 Explanation of binary types and JSON types
duration： 24 minutes 12 seconds

Section7 Summary and suggestions for type usage
duration： 06 minutes 13 seconds
Chapter13 constraint
expand retract

Section1 Classification of data integrity and constraints
duration： 17 minutes 27 seconds

Section2 Use of non-null constraints
duration： 15 minutes 30 seconds

Section3 Use of unique constraints
duration： 35 minutes 45 seconds

Section4 Use of primary key constraints
duration： 21 minutes 46 seconds

Section5 AUTO_INCREMENT
duration： 21 minutes 16 seconds

Section6 Use of foreign key constraints
duration： 42 minutes 01 seconds

Section7 Check constraints and default value constraints
duration： 20 minutes 22 seconds

Section8 Constraint after-school exercises
duration： 33 minutes 24 seconds
Chapter14 view
expand retract

Section1 Understanding database objects and views
duration： 25 minutes 54 seconds

Section2 Creating and viewing views
duration： 26 minutes 12 seconds

Section3 Updating view data and deleting views
duration： 25 minutes 01 seconds

Section4 View after-class exercises
duration： 17 minutes 08 seconds
Chapter15 stored procedure
expand retract

Section1 Stored procedure usage instructions
duration： 19 minutes 20 seconds

Section2 Creating and calling stored procedures
duration： 33 minutes 01 seconds

Section3 Creating and calling stored functions
duration： 23 minutes 30 seconds

Section4 View, modify and delete stored procedures and functions
duration： 25 minutes 45 seconds

Section5 Stored procedure function after-class exercises
duration： 42 minutes 34 seconds
Chapter16 Variables and structures
expand retract

Section1 Use of GLOBAL and SESSION system variables
duration： 34 minutes 41 seconds

Section2 Use of session user variables and local variables
duration： 42 minutes 15 seconds

Section3 Program error handling mechanism
duration： 41 minutes 15 seconds

Section4 Use of branch structure IF
duration： 35 minutes 15 seconds

Section5 Use of branch structure CASE
duration： 27 minutes 00 seconds

Section6 LOOP_WHILE_REPEAT three loop structures
duration： 51 minutes 47 seconds

Section7 Use of LEAVE and ITEEATE
duration： 28 minutes 42 seconds

Section8 Use of cursors
duration： 29 minutes 07 seconds

Section9 Homework
duration： 58 minutes 55 seconds
Chapter17 trigger
expand retract

Section1 Create trigger
duration： 38 minutes 13 seconds

Section2 View Delete Trigger_Trigger After-Class Exercise
duration： 31 minutes 28 seconds
Chapter18 MySQL8.0 new features and common table expressions
expand retract

Section1 new features of mysql8.0
duration： 01 hours 13 minutes 00 seconds

Section2 Common table expressions_after-class exercises
duration： 35 minutes 12 seconds
Chapter19 Overview of Advanced Features
expand retract

Section1 Chapter overview
duration： 19 minutes 12 seconds

Section2 Preparation of CentOS environment
duration： 22 minutes 26 seconds

Section3 MySQL uninstallation
duration： 11 minutes 12 seconds

Section4 Install MySQL8.0 and 5.7 versions under Linux
duration： 25 minutes 59 seconds

Section5 SQLyog implements remote connection for MySQL8.0 and 5.7
duration： 26 minutes 16 seconds

Section6 Modification of character set and explanation of underlying principles
duration： 26 minutes 40 seconds

Section7 Comparison rules_encoding and decoding process from request to response
duration： 21 minutes 26 seconds

Section8 SQL case specifications and sql_mode settings
duration： 24 minutes 05 seconds

Section9 MySQL directory structure and table representation in the file system
duration： 34 minutes 48 seconds
Chapter20 User and user password operations and permission management
expand retract

Section1 User creation, modification, deletion
duration： 22 minutes 28 seconds

Section2 Setting and management of user passwords
duration： 14 minutes 42 seconds

Section3 Permission management and access control
duration： 38 minutes 30 seconds

Section4 Use of roles
duration： 24 minutes 02 seconds

Section5 Configuration files, system variables and mysql logical architecture
duration： 35 minutes 16 seconds
Chapter21 SQL execution process
expand retract

Section1 SQL execution process
duration： 21 minutes 30 seconds

Section2 Demonstration of SQL execution flow in mysql 8.0 and 5.7
duration： 16 minutes 16 seconds

Section3 The use of SQL execution process and buffer pool in Oracle
duration： 22 minutes 28 seconds
Chapter22 Storage engines and indexes
expand retract

Section1 Setting the storage engine of the table, comparison between InnoDB and MyISAM
duration： 32 minutes 43 seconds

Section2 Use of storage engines such as Archive, CSV, and Memory
duration： 15 minutes 43 seconds

Section3 Why use indexes and their advantages and disadvantages
duration： 24 minutes 16 seconds

Section4 A simple index design solution
duration： 22 minutes 22 seconds

Section5 Iterative Design Plan for Indexes
duration： 18 minutes 51 seconds

Section6 The concepts of clustered index, secondary index and joint index
duration： 23 minutes 24 seconds

Section7 Things to note about B+ trees in InnoDB_MyISAM index scheme
duration： 25 minutes 22 seconds

Section8 Comparison of Hash index, AVL tree, B-tree and B+ tree
duration： 41 minutes 20 seconds
Chapter23 InnoDB data storage structure
expand retract

Section1 Overview of InnoDB data storage structure
duration： 20 minutes 23 seconds

Section2 Page structure file header and file tail
duration： 17 minutes 52 seconds

Section3 Minimum maximum record of page structure_Record header information of row format
duration： 24 minutes 10 seconds

Section4 Page structure: page directory and page header
duration： 17 minutes 49 seconds

Section5 Set line format and ibd file analysis Compact line format
duration： 30 minutes 46 seconds

Section6 Row overflow and Dynamic, Compressed, Redundant
duration： 17 minutes 16 seconds

Section7 Segments, fragmentation areas, and table space structures
duration： 36 minutes 28 seconds
Chapter24 Index classification
expand retract

Section1 Index classification
duration： 11 minutes 33 seconds

Section2 Three ways to add indexes to a table
duration： 38 minutes 52 seconds

Section3 Delete index and new index features: descending index, hidden index
duration： 32 minutes 13 seconds

Section4 11 situations suitable for index creation (1)
duration： 35 minutes 22 seconds

Section5 11 situations suitable for creating indexes (2)
duration： 24 minutes 44 seconds

Section6 7 situations when creating an index is not suitable
duration： 09 minutes 57 seconds
Chapter25 Database optimization steps
expand retract

Section1 View system performance parameters
duration： 17 minutes 56 seconds

Section2 Slow query log analysis, SHOW PROFILE to view SQL execution costs
duration： 26 minutes 00 seconds
Chapter26 EXPLAIN
expand retract

Section1 Overview of EXPLAIN and analysis of table and id fields
duration： 31 minutes 27 seconds

Section2 EXPLAIN中select_type、partitions
duration： 39 minutes 57 seconds

Section3 Analysis of ref, rows, filtered and extra in EXPLAIN
duration： 30 minutes 04 seconds

Section4 EXPLAIN's 4 formats and view optimizer rewrite SQL
duration： 19 minutes 10 seconds

Section5 Trace analysis optimizer execution plan and use of Sys schema view
duration： 10 minutes 21 seconds
Chapter27 Index failure
expand retract

Section1 11 situations where data preparation and indexing fail
duration： 41 minutes 23 seconds

Section2 11 cases of index failure 2
duration： 19 minutes 29 seconds
Chapter28 Join query, subquery, paging query
expand retract

Section1 Query optimization of outer joins and inner joins
duration： 19 minutes 45 seconds

Section2 The underlying principle of the JOIN statement
duration： 27 minutes 56 seconds

Section3 Subquery optimization and sorting optimization
duration： 42 minutes 12 seconds

Section4 GROUP BY optimization, paging query optimization
duration： 06 minutes 03 seconds

Section5 Use of covering index
duration： 21 minutes 57 seconds

Section6 Index condition push down
duration： 18 minutes 27 seconds

Section7 Other query optimization strategies
duration： 12 minutes 51 seconds

Section8 How to design the primary key of Taobao database
duration： 26 minutes 38 seconds
Chapter29 paradigm
expand retract

Section1 Paradigm Overview and First Normal Form
duration： 21 minutes 42 seconds

Section2 Second normal form and third normal form
duration： 25 minutes 44 seconds

Section3 Denormalization applications
duration： 23 minutes 23 seconds

Section4 Bath normal form, fourth normal form, fifth normal form and field key normal form
duration： 18 minutes 19 seconds

Section5 Paradigm practical cases
duration： 18 minutes 37 seconds
Chapter30 ER modeling, design principles, writing specifications
expand retract

Section1 The process of ER modeling and converting data tables
duration： 25 minutes 34 seconds

Section2 Database design principles and daily SQL writing specifications
duration： 15 minutes 30 seconds

Section3 Power Designer creates conceptual and physical data models
duration： 36 minutes 55 seconds
Chapter31 Database tuning
expand retract

Section1 Overall database tuning steps, optimizing MySQL server hardware and parameters
duration： 28 minutes 46 seconds

Section2 Database structure optimization, three major table optimization, and other three strategies
duration： 41 minutes 29 seconds
Chapter32 affairs
expand retract

Section1 ACID properties of transactions and status of transactions
duration： 27 minutes 58 seconds

Section2 Explicit transactions and implicit transactions
duration： 19 minutes 51 seconds

Section3 Examples of using transactions
duration： 25 minutes 20 seconds
Chapter33 Concurrency issues and isolation levels
expand retract

Section1 Data concurrency issues and 4 isolation levels
duration： 19 minutes 49 seconds

Section2 View and set MySQL isolation level
duration： 12 minutes 12 seconds

Section3 Read demo in uncommitted isolation
duration： 13 minutes 23 seconds

Section4 Demonstration of Read Committed and Repeatable Read Isolation
duration： 09 minutes 27 seconds

Section5 Demonstration and solution of phantom reading
duration： 12 minutes 18 seconds
Chapter34 Redo log, Undo log
expand retract

Section1 Understanding Redo logs and Undo logs, and why Redo logs are needed
duration： 20 minutes 30 seconds

Section2 Analysis of Redo log flushing strategy and process
duration： 28 minutes 12 seconds

Section3 Write to Redo Log Buffer and Redo Log File
duration： 19 minutes 49 seconds

Section4 Overview and writing process of Undo log
duration： 32 minutes 04 seconds
Chapter35 Lock
expand retract

Section1 Overview of locks, concurrency issues of reading and writing
duration： 21 minutes 35 seconds

Section2 Understanding S lock and X lock from the perspective of data operation types
duration： 21 minutes 46 seconds

Section3 Table locks: S lock, X lock, intention lock
duration： 34 minutes 03 seconds

Section4 Table locks, self-increment locks and metadata locks
duration： 14 minutes 49 seconds

Section5 Row lock record lock, gap lock
duration： 26 minutes 59 seconds

Section6 Line lock, key lock and insertion intention lock
duration： 14 minutes 23 seconds

Section7 Understanding of page locks, use of optimistic locks and pessimistic locks
duration： 19 minutes 33 seconds

Section8 Division of locking methods: implicit lock and explicit lock
duration： 16 minutes 07 seconds

Section9 Understanding global locks and deadlocks
duration： 18 minutes 25 seconds

Section10 Lock memory structure and monitoring strategy
duration： 20 minutes 20 seconds
Chapter36 MVCC
expand retract

Section1 MVCC solves reading and writing problems
duration： 10 minutes 56 seconds

Section2 MVCC Three Musketeers: Hidden fields, Undolog version chain, ReadView
duration： 26 minutes 25 seconds

Section3 MVCC operation process under read committed and repeatable read isolation levels
duration： 11 minutes 47 seconds

Section4 MVCC’s process for solving phantom reads under repeatable reads
duration： 07 minutes 41 seconds
Chapter37 Six major log files
expand retract

Section1 Overview of the six major log files
duration： 12 minutes 08 seconds

Section2 General query log, error log
duration： 20 minutes 02 seconds

Section3 Binlog log parameter setting and data recovery demonstration
duration： 43 minutes 58 seconds

Section4 Binlog deletion, binlog writing mechanism and two-phase submission
duration： 21 minutes 44 seconds

Section5 Analysis of the steps and principles of relay logs and master-slave replication
duration： 26 minutes 46 seconds

Section6 Implementation of one master and one slave architecture and master-slave synchronization
duration： 34 minutes 56 seconds

Section7 Binlog format setting instructions
duration： 11 minutes 49 seconds
Chapter38 Data synchronization and backup
expand retract

Section1 Data synchronization consistency problem solving
duration： 14 minutes 17 seconds

Section2 Overview of data backup and implementing logical backup data with mysqldump
duration： 30 minutes 06 seconds

Section3 Demonstrate mysql to implement logical recovery of data
duration： 18 minutes 06 seconds

Section4 Demonstration of physical backup and physical recovery, export and import of table data
duration： 24 minutes 12 seconds

Section5 Database migration and how to delete the database without running away
duration： 18 minutes 10 seconds

Section6 Message
duration： 08 minutes 47 seconds

Data Name operate
[must see]MySQL from novice to expert (basic/advanced/optimization) related courseware Download

[must see]Visualization tools Download

[must see]PowerDesigner Download

[must see]Linux remote access tools Download

[must see]mysql installation package linux version_mysql5.7 Download

[must see]mysql installation package linux version_mysql8.0.25 Download

王林 Lecturer Other Courses

ElementaryComplete collection of interview questions in front-end disciplines (Season 1)

23865
Collection
ElementaryFlexible box + responsive page layout practice

9107
Collection
ElementaryMake a responsive website from scratch—Grid layout

18075
Collection
ElementaryThe most easy-to-understand HTML+CSS course in 9 days

45390
Collection
ElementaryBasic tutorial for getting started with Web front-end

245992
Collection
ElementaryWeb front-end development-Zero Basic Programming Admission Guide

68202
Collection
ElementaryFront-end basic HTML5+CSS3 from entry to proficiency (full version)

302691
Collection
ElementaryThe latest CSS3 tutorial is quick and easy to understand

22897
Collection
ElementaryCSS3 Flex flexible box model makes layout fly

28911
Collection
ElementaryLearn DIV+CSS layout in one day (five practical exercises)

11204
Collection
ElementaryHTML5+CSS practice project [Hand Knock Onmyoji official website]

11154
Collection
ElementaryGetting started with HTML5+CSS3 for front-end newbies

265102
Collection

Start Learning

This course includes：

03 days 13 hours 40 minutes 03 seconds Watch videos anytime

6 Download Resources

Popular courses Latest courses

More>