Name: MySQL from novice to expert (Basic/Advanced/Optimization)
Uploaded: 2022-03-29T15:23:46+8:00
Duration: 5140 min 3 s
Description: 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...

Chapter1Introduction and introduction to databases: Course Introduction
09 minutes13 seconds; Why use databases and common database concepts
16 minutes02 seconds; Comparison of common DBMS
18 minutes47 seconds; Comparison of RDBMS and non-RDBMS
20 minutes04 seconds; 4 types of relationships between ER models and table records
20 minutes32 seconds
Chapter2mysql installation and configuration: Uninstalling mysql 8.0
20 minutes41 seconds; Download, installation and configuration of mysql 8.0 and 5.7 versions
35 minutes29 seconds; FAQs about mysql installation_service startup and user login
19 minutes18 seconds; Mysql usage demonstration-mysql5.7 character set setting
27 minutes52 seconds; Use of tools such as navicat_sqlyog_dbserver
28 minutes59 seconds; mysql directory structure and after-school exercises for the first two chapters
33 minutes55 seconds
Chapter3SQL Overview and Basic SELECT Query: SQL Overview and SQL Classification
34 minutes29 seconds; SQL usage specifications and data import
32 minutes01 seconds; The most basic SELECT...FROM structure
09 minutes01 seconds; Column alias_duplication_NULL_DESC and other operations
33 minutes18 seconds; Filter data using WHERE
10 minutes06 seconds; Homework
09 minutes34 seconds
Chapter4operator: Use of arithmetic operators
17 minutes04 seconds; Use of comparison operators
58 minutes02 seconds; Use of logical operators and bitwise operators
22 minutes57 seconds; Operator exercises after class
17 minutes26 seconds
Chapter5Sorting and paging operations: ORDER BY implements sorting operations
21 minutes32 seconds; LIMIT implements paging operations
30 minutes53 seconds; Sorting and paging after-class exercises
10 minutes48 seconds
Chapter6Multi-table query: Why do we need multi-table queries?
22 minutes13 seconds; Cartesian product errors and correct multi-table queries
35 minutes42 seconds; Equivalent join VS non-equivalent join, self-join VS non-self-join
16 minutes33 seconds; How to implement inner and outer joins with SQL92 and 99 syntax
35 minutes54 seconds; Use SQL99 to implement 7 JOIN operations
21 minutes39 seconds; Use of NATURAL JOIN and USING
10 minutes54 seconds; Multi-table query after-class exercises
34 minutes31 seconds
Chapter7single line function: Classification of functions
12 minutes45 seconds; Explanation of functions of numerical types
36 minutes10 seconds; Explanation of string type functions
30 minutes59 seconds; Explanation of functions of date and time types
52 minutes37 seconds; Process control function explanation
21 minutes42 seconds; Encryption and decryption_MySQL information functions and other explanations
25 minutes05 seconds; Single-line function after-class exercises
29 minutes02 seconds
Chapter8aggregate function: 5 commonly used aggregate functions
43 minutes07 seconds; Use of GROUP BY
31 minutes29 seconds; The use of HAVING and the execution process of SQL statements
44 minutes00 seconds; Aggregate function after-class exercises
19 minutes03 seconds
Chapter9subquery: Subquery examples and subquery classifications
27 minutes25 seconds; Single row subquery case analysis
29 minutes18 seconds; Multi-row subquery case analysis
25 minutes14 seconds; Correlated subquery case analysis
45 minutes57 seconds; Subquery after-school exercises
40 minutes16 seconds; Subquery after-class exercise 2
48 minutes28 seconds
Chapter10Create management data table: Database creation, modification and deletion
37 minutes54 seconds; Common data types_Two ways to create tables
30 minutes18 seconds; Modify table_rename table_delete table_clear table
22 minutes52 seconds; The use of COMMIT and ROLLBACK in DCL
21 minutes58 seconds; Alibaba MySQL naming convention and atomization of MySQL8DDL
20 minutes29 seconds; Post-class exercises on creating management tables
38 minutes37 seconds
Chapter11Add, delete, modify: Add data to DML
26 minutes28 seconds; DML update and delete operations_MySQL8 new features calculated columns
20 minutes40 seconds; Comprehensive case study of DDL and DML
36 minutes50 seconds; Add, delete and modify after-class exercises
37 minutes20 seconds
Chapter12mysql data type: Data Type Overview_Character Set Settings
23 minutes19 seconds; Integer data type explanation
25 minutes22 seconds; Explanation of floating point numbers, fixed point numbers and bit types
36 minutes29 seconds; Date and time types explained
37 minutes52 seconds; Explanation of text string types (including ENUM, SET)
35 minutes18 seconds; Explanation of binary types and JSON types
24 minutes12 seconds; Summary and suggestions for type usage
06 minutes13 seconds
Chapter13constraint: Classification of data integrity and constraints
17 minutes27 seconds; Use of non-null constraints
15 minutes30 seconds; Use of unique constraints
35 minutes45 seconds; Use of primary key constraints
21 minutes46 seconds; AUTO_INCREMENT
21 minutes16 seconds; Use of foreign key constraints
42 minutes01 seconds; Check constraints and default value constraints
20 minutes22 seconds; Constraint after-school exercises
33 minutes24 seconds
Chapter14view: Understanding database objects and views
25 minutes54 seconds; Creating and viewing views
26 minutes12 seconds; Updating view data and deleting views
25 minutes01 seconds; View after-class exercises
17 minutes08 seconds
Chapter15stored procedure: Stored procedure usage instructions
19 minutes20 seconds; Creating and calling stored procedures
33 minutes01 seconds; Creating and calling stored functions
23 minutes30 seconds; View, modify and delete stored procedures and functions
25 minutes45 seconds; Stored procedure function after-class exercises
42 minutes34 seconds
Chapter16Variables and structures: Use of GLOBAL and SESSION system variables
34 minutes41 seconds; Use of session user variables and local variables
42 minutes15 seconds; Program error handling mechanism
41 minutes15 seconds; Use of branch structure IF
35 minutes15 seconds; Use of branch structure CASE
27 minutes00 seconds; LOOP_WHILE_REPEAT three loop structures
51 minutes47 seconds; Use of LEAVE and ITEEATE
28 minutes42 seconds; Use of cursors
29 minutes07 seconds; Homework
58 minutes55 seconds
Chapter17trigger: Create trigger
38 minutes13 seconds; View Delete Trigger_Trigger After-Class Exercise
31 minutes28 seconds
Chapter18MySQL8.0 new features and common table expressions: new features of mysql8.0
01 Hours13 minutes00 seconds; Common table expressions_after-class exercises
35 minutes12 seconds
Chapter19Overview of Advanced Features: Chapter overview
19 minutes12 seconds; Preparation of CentOS environment
22 minutes26 seconds; MySQL uninstallation
11 minutes12 seconds; Install MySQL8.0 and 5.7 versions under Linux
25 minutes59 seconds; SQLyog implements remote connection for MySQL8.0 and 5.7
26 minutes16 seconds; Modification of character set and explanation of underlying principles
26 minutes40 seconds; Comparison rules_encoding and decoding process from request to response
21 minutes26 seconds; SQL case specifications and sql_mode settings
24 minutes05 seconds; MySQL directory structure and table representation in the file system
34 minutes48 seconds
Chapter20User and user password operations and permission management: User creation, modification, deletion
22 minutes28 seconds; Setting and management of user passwords
14 minutes42 seconds; Permission management and access control
38 minutes30 seconds; Use of roles
24 minutes02 seconds; Configuration files, system variables and mysql logical architecture
35 minutes16 seconds
Chapter21SQL execution process: SQL execution process
21 minutes30 seconds; Demonstration of SQL execution flow in mysql 8.0 and 5.7
16 minutes16 seconds; The use of SQL execution process and buffer pool in Oracle
22 minutes28 seconds
Chapter22Storage engines and indexes: Setting the storage engine of the table, comparison between InnoDB and MyISAM
32 minutes43 seconds; Use of storage engines such as Archive, CSV, and Memory
15 minutes43 seconds; Why use indexes and their advantages and disadvantages
24 minutes16 seconds; A simple index design solution
22 minutes22 seconds; Iterative Design Plan for Indexes
18 minutes51 seconds; The concepts of clustered index, secondary index and joint index
23 minutes24 seconds; Things to note about B+ trees in InnoDB_MyISAM index scheme
25 minutes22 seconds; Comparison of Hash index, AVL tree, B-tree and B+ tree
41 minutes20 seconds
Chapter23InnoDB data storage structure: Overview of InnoDB data storage structure
20 minutes23 seconds; Page structure file header and file tail
17 minutes52 seconds; Minimum maximum record of page structure_Record header information of row format
24 minutes10 seconds; Page structure: page directory and page header
17 minutes49 seconds; Set line format and ibd file analysis Compact line format
30 minutes46 seconds; Row overflow and Dynamic, Compressed, Redundant
17 minutes16 seconds; Segments, fragmentation areas, and table space structures
36 minutes28 seconds
Chapter24Index classification: Index classification
11 minutes33 seconds; Three ways to add indexes to a table
38 minutes52 seconds; Delete index and new index features: descending index, hidden index
32 minutes13 seconds; 11 situations suitable for index creation (1)
35 minutes22 seconds; 11 situations suitable for creating indexes (2)
24 minutes44 seconds; 7 situations when creating an index is not suitable
09 minutes57 seconds
Chapter25Database optimization steps: View system performance parameters
17 minutes56 seconds; Slow query log analysis, SHOW PROFILE to view SQL execution costs
26 minutes00 seconds
Chapter26EXPLAIN: Overview of EXPLAIN and analysis of table and id fields
31 minutes27 seconds; EXPLAIN中select_type、partitions
39 minutes57 seconds; Analysis of ref, rows, filtered and extra in EXPLAIN
30 minutes04 seconds; EXPLAIN's 4 formats and view optimizer rewrite SQL
19 minutes10 seconds; Trace analysis optimizer execution plan and use of Sys schema view
10 minutes21 seconds
Chapter27Index failure: 11 situations where data preparation and indexing fail
41 minutes23 seconds; 11 cases of index failure 2
19 minutes29 seconds
Chapter28Join query, subquery, paging query: Query optimization of outer joins and inner joins
19 minutes45 seconds; The underlying principle of the JOIN statement
27 minutes56 seconds; Subquery optimization and sorting optimization
42 minutes12 seconds; GROUP BY optimization, paging query optimization
06 minutes03 seconds; Use of covering index
21 minutes57 seconds; Index condition push down
18 minutes27 seconds; Other query optimization strategies
12 minutes51 seconds; How to design the primary key of Taobao database
26 minutes38 seconds
Chapter29paradigm: Paradigm Overview and First Normal Form
21 minutes42 seconds; Second normal form and third normal form
25 minutes44 seconds; Denormalization applications
23 minutes23 seconds; Bath normal form, fourth normal form, fifth normal form and field key normal form
18 minutes19 seconds; Paradigm practical cases
18 minutes37 seconds
Chapter30ER modeling, design principles, writing specifications: The process of ER modeling and converting data tables
25 minutes34 seconds; Database design principles and daily SQL writing specifications
15 minutes30 seconds; Power Designer creates conceptual and physical data models
36 minutes55 seconds
Chapter31Database tuning: Overall database tuning steps, optimizing MySQL server hardware and parameters
28 minutes46 seconds; Database structure optimization, three major table optimization, and other three strategies
41 minutes29 seconds
Chapter32affairs: ACID properties of transactions and status of transactions
27 minutes58 seconds; Explicit transactions and implicit transactions
19 minutes51 seconds; Examples of using transactions
25 minutes20 seconds
Chapter33Concurrency issues and isolation levels: Data concurrency issues and 4 isolation levels
19 minutes49 seconds; View and set MySQL isolation level
12 minutes12 seconds; Read demo in uncommitted isolation
13 minutes23 seconds; Demonstration of Read Committed and Repeatable Read Isolation
09 minutes27 seconds; Demonstration and solution of phantom reading
12 minutes18 seconds
Chapter34Redo log, Undo log: Understanding Redo logs and Undo logs, and why Redo logs are needed
20 minutes30 seconds; Analysis of Redo log flushing strategy and process
28 minutes12 seconds; Write to Redo Log Buffer and Redo Log File
19 minutes49 seconds; Overview and writing process of Undo log
32 minutes04 seconds
Chapter35Lock: Overview of locks, concurrency issues of reading and writing
21 minutes35 seconds; Understanding S lock and X lock from the perspective of data operation types
21 minutes46 seconds; Table locks: S lock, X lock, intention lock
34 minutes03 seconds; Table locks, self-increment locks and metadata locks
14 minutes49 seconds; Row lock record lock, gap lock
26 minutes59 seconds; Line lock, key lock and insertion intention lock
14 minutes23 seconds; Understanding of page locks, use of optimistic locks and pessimistic locks
19 minutes33 seconds; Division of locking methods: implicit lock and explicit lock
16 minutes07 seconds; Understanding global locks and deadlocks
18 minutes25 seconds; Lock memory structure and monitoring strategy
20 minutes20 seconds
Chapter36MVCC: MVCC solves reading and writing problems
10 minutes56 seconds; MVCC Three Musketeers: Hidden fields, Undolog version chain, ReadView
26 minutes25 seconds; MVCC operation process under read committed and repeatable read isolation levels
11 minutes47 seconds; MVCC’s process for solving phantom reads under repeatable reads
07 minutes41 seconds
Chapter37Six major log files: Overview of the six major log files
12 minutes08 seconds; General query log, error log
20 minutes02 seconds; Binlog log parameter setting and data recovery demonstration
43 minutes58 seconds; Binlog deletion, binlog writing mechanism and two-phase submission
21 minutes44 seconds; Analysis of the steps and principles of relay logs and master-slave replication
26 minutes46 seconds; Implementation of one master and one slave architecture and master-slave synchronization
34 minutes56 seconds; Binlog format setting instructions
11 minutes49 seconds
Chapter38Data synchronization and backup: Data synchronization consistency problem solving
14 minutes17 seconds; Overview of data backup and implementing logical backup data with mysqldump
30 minutes06 seconds; Demonstrate mysql to implement logical recovery of data
18 minutes06 seconds; Demonstration of physical backup and physical recovery, export and import of table data
24 minutes12 seconds; Database migration and how to delete the database without running away
18 minutes10 seconds; Message
08 minutes47 seconds

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

About Instructor

php.cn