Course Introduction
09 minutes13 secondsWhy use databases and common database concepts
16 minutes02 secondsComparison of common DBMS
18 minutes47 secondsComparison of RDBMS and non-RDBMS
20 minutes04 seconds4 types of relationships between ER models and table records
20 minutes32 secondsUninstalling mysql 8.0
20 minutes41 secondsDownload, installation and configuration of mysql 8.0 and 5.7 versions
35 minutes29 secondsFAQs about mysql installation_service startup and user login
19 minutes18 secondsMysql usage demonstration-mysql5.7 character set setting
27 minutes52 secondsUse of tools such as navicat_sqlyog_dbserver
28 minutes59 secondsmysql directory structure and after-school exercises for the first two chapters
33 minutes55 secondsSQL Overview and SQL Classification
34 minutes29 secondsSQL usage specifications and data import
32 minutes01 secondsThe most basic SELECT...FROM structure
09 minutes01 secondsColumn alias_duplication_NULL_DESC and other operations
33 minutes18 secondsFilter data using WHERE
10 minutes06 secondsHomework
09 minutes34 secondsUse of arithmetic operators
17 minutes04 secondsUse of comparison operators
58 minutes02 secondsUse of logical operators and bitwise operators
22 minutes57 secondsOperator exercises after class
17 minutes26 secondsORDER BY implements sorting operations
21 minutes32 secondsLIMIT implements paging operations
30 minutes53 secondsSorting and paging after-class exercises
10 minutes48 secondsWhy do we need multi-table queries?
22 minutes13 secondsCartesian product errors and correct multi-table queries
35 minutes42 secondsEquivalent join VS non-equivalent join, self-join VS non-self-join
16 minutes33 secondsHow to implement inner and outer joins with SQL92 and 99 syntax
35 minutes54 secondsUse SQL99 to implement 7 JOIN operations
21 minutes39 secondsUse of NATURAL JOIN and USING
10 minutes54 secondsMulti-table query after-class exercises
34 minutes31 secondsClassification of functions
12 minutes45 secondsExplanation of functions of numerical types
36 minutes10 secondsExplanation of string type functions
30 minutes59 secondsExplanation of functions of date and time types
52 minutes37 secondsProcess control function explanation
21 minutes42 secondsEncryption and decryption_MySQL information functions and other explanations
25 minutes05 secondsSingle-line function after-class exercises
29 minutes02 seconds5 commonly used aggregate functions
43 minutes07 secondsUse of GROUP BY
31 minutes29 secondsThe use of HAVING and the execution process of SQL statements
44 minutes00 secondsAggregate function after-class exercises
19 minutes03 secondsSubquery examples and subquery classifications
27 minutes25 secondsSingle row subquery case analysis
29 minutes18 secondsMulti-row subquery case analysis
25 minutes14 secondsCorrelated subquery case analysis
45 minutes57 secondsSubquery after-school exercises
40 minutes16 secondsSubquery after-class exercise 2
48 minutes28 secondsDatabase creation, modification and deletion
37 minutes54 secondsCommon data types_Two ways to create tables
30 minutes18 secondsModify table_rename table_delete table_clear table
22 minutes52 secondsThe use of COMMIT and ROLLBACK in DCL
21 minutes58 secondsAlibaba MySQL naming convention and atomization of MySQL8DDL
20 minutes29 secondsPost-class exercises on creating management tables
38 minutes37 secondsAdd data to DML
26 minutes28 secondsDML update and delete operations_MySQL8 new features calculated columns
20 minutes40 secondsComprehensive case study of DDL and DML
36 minutes50 secondsAdd, delete and modify after-class exercises
37 minutes20 secondsData Type Overview_Character Set Settings
23 minutes19 secondsInteger data type explanation
25 minutes22 secondsExplanation of floating point numbers, fixed point numbers and bit types
36 minutes29 secondsDate and time types explained
37 minutes52 secondsExplanation of text string types (including ENUM, SET)
35 minutes18 secondsExplanation of binary types and JSON types
24 minutes12 secondsSummary and suggestions for type usage
06 minutes13 secondsClassification of data integrity and constraints
17 minutes27 secondsUse of non-null constraints
15 minutes30 secondsUse of unique constraints
35 minutes45 secondsUse of primary key constraints
21 minutes46 secondsAUTO_INCREMENT
21 minutes16 secondsUse of foreign key constraints
42 minutes01 secondsCheck constraints and default value constraints
20 minutes22 secondsConstraint after-school exercises
33 minutes24 secondsUnderstanding database objects and views
25 minutes54 secondsCreating and viewing views
26 minutes12 secondsUpdating view data and deleting views
25 minutes01 secondsView after-class exercises
17 minutes08 secondsStored procedure usage instructions
19 minutes20 secondsCreating and calling stored procedures
33 minutes01 secondsCreating and calling stored functions
23 minutes30 secondsView, modify and delete stored procedures and functions
25 minutes45 secondsStored procedure function after-class exercises
42 minutes34 secondsUse of GLOBAL and SESSION system variables
34 minutes41 secondsUse of session user variables and local variables
42 minutes15 secondsProgram error handling mechanism
41 minutes15 secondsUse of branch structure IF
35 minutes15 secondsUse of branch structure CASE
27 minutes00 secondsLOOP_WHILE_REPEAT three loop structures
51 minutes47 secondsUse of LEAVE and ITEEATE
28 minutes42 secondsUse of cursors
29 minutes07 secondsHomework
58 minutes55 secondsCreate trigger
38 minutes13 secondsView Delete Trigger_Trigger After-Class Exercise
31 minutes28 secondsnew features of mysql8.0
01 Hours13 minutes00 secondsCommon table expressions_after-class exercises
35 minutes12 secondsChapter overview
19 minutes12 secondsPreparation of CentOS environment
22 minutes26 secondsMySQL uninstallation
11 minutes12 secondsInstall MySQL8.0 and 5.7 versions under Linux
25 minutes59 secondsSQLyog implements remote connection for MySQL8.0 and 5.7
26 minutes16 secondsModification of character set and explanation of underlying principles
26 minutes40 secondsComparison rules_encoding and decoding process from request to response
21 minutes26 secondsSQL case specifications and sql_mode settings
24 minutes05 secondsMySQL directory structure and table representation in the file system
34 minutes48 secondsUser creation, modification, deletion
22 minutes28 secondsSetting and management of user passwords
14 minutes42 secondsPermission management and access control
38 minutes30 secondsUse of roles
24 minutes02 secondsConfiguration files, system variables and mysql logical architecture
35 minutes16 secondsSQL execution process
21 minutes30 secondsDemonstration of SQL execution flow in mysql 8.0 and 5.7
16 minutes16 secondsThe use of SQL execution process and buffer pool in Oracle
22 minutes28 secondsSetting the storage engine of the table, comparison between InnoDB and MyISAM
32 minutes43 secondsUse of storage engines such as Archive, CSV, and Memory
15 minutes43 secondsWhy use indexes and their advantages and disadvantages
24 minutes16 secondsA simple index design solution
22 minutes22 secondsIterative Design Plan for Indexes
18 minutes51 secondsThe concepts of clustered index, secondary index and joint index
23 minutes24 secondsThings to note about B+ trees in InnoDB_MyISAM index scheme
25 minutes22 secondsComparison of Hash index, AVL tree, B-tree and B+ tree
41 minutes20 secondsOverview of InnoDB data storage structure
20 minutes23 secondsPage structure file header and file tail
17 minutes52 secondsMinimum maximum record of page structure_Record header information of row format
24 minutes10 secondsPage structure: page directory and page header
17 minutes49 secondsSet line format and ibd file analysis Compact line format
30 minutes46 secondsRow overflow and Dynamic, Compressed, Redundant
17 minutes16 secondsSegments, fragmentation areas, and table space structures
36 minutes28 secondsIndex classification
11 minutes33 secondsThree ways to add indexes to a table
38 minutes52 secondsDelete index and new index features: descending index, hidden index
32 minutes13 seconds11 situations suitable for index creation (1)
35 minutes22 seconds11 situations suitable for creating indexes (2)
24 minutes44 seconds7 situations when creating an index is not suitable
09 minutes57 secondsView system performance parameters
17 minutes56 secondsSlow query log analysis, SHOW PROFILE to view SQL execution costs
26 minutes00 secondsOverview of EXPLAIN and analysis of table and id fields
31 minutes27 secondsEXPLAIN中select_type、partitions
39 minutes57 secondsAnalysis of ref, rows, filtered and extra in EXPLAIN
30 minutes04 secondsEXPLAIN's 4 formats and view optimizer rewrite SQL
19 minutes10 secondsTrace analysis optimizer execution plan and use of Sys schema view
10 minutes21 seconds11 situations where data preparation and indexing fail
41 minutes23 seconds11 cases of index failure 2
19 minutes29 secondsQuery optimization of outer joins and inner joins
19 minutes45 secondsThe underlying principle of the JOIN statement
27 minutes56 secondsSubquery optimization and sorting optimization
42 minutes12 secondsGROUP BY optimization, paging query optimization
06 minutes03 secondsUse of covering index
21 minutes57 secondsIndex condition push down
18 minutes27 secondsOther query optimization strategies
12 minutes51 secondsHow to design the primary key of Taobao database
26 minutes38 secondsParadigm Overview and First Normal Form
21 minutes42 secondsSecond normal form and third normal form
25 minutes44 secondsDenormalization applications
23 minutes23 secondsBath normal form, fourth normal form, fifth normal form and field key normal form
18 minutes19 secondsParadigm practical cases
18 minutes37 secondsThe process of ER modeling and converting data tables
25 minutes34 secondsDatabase design principles and daily SQL writing specifications
15 minutes30 secondsPower Designer creates conceptual and physical data models
36 minutes55 secondsOverall database tuning steps, optimizing MySQL server hardware and parameters
28 minutes46 secondsDatabase structure optimization, three major table optimization, and other three strategies
41 minutes29 secondsACID properties of transactions and status of transactions
27 minutes58 secondsExplicit transactions and implicit transactions
19 minutes51 secondsExamples of using transactions
25 minutes20 secondsData concurrency issues and 4 isolation levels
19 minutes49 secondsView and set MySQL isolation level
12 minutes12 secondsRead demo in uncommitted isolation
13 minutes23 secondsDemonstration of Read Committed and Repeatable Read Isolation
09 minutes27 secondsDemonstration and solution of phantom reading
12 minutes18 secondsUnderstanding Redo logs and Undo logs, and why Redo logs are needed
20 minutes30 secondsAnalysis of Redo log flushing strategy and process
28 minutes12 secondsWrite to Redo Log Buffer and Redo Log File
19 minutes49 secondsOverview and writing process of Undo log
32 minutes04 secondsOverview of locks, concurrency issues of reading and writing
21 minutes35 secondsUnderstanding S lock and X lock from the perspective of data operation types
21 minutes46 secondsTable locks: S lock, X lock, intention lock
34 minutes03 secondsTable locks, self-increment locks and metadata locks
14 minutes49 secondsRow lock record lock, gap lock
26 minutes59 secondsLine lock, key lock and insertion intention lock
14 minutes23 secondsUnderstanding of page locks, use of optimistic locks and pessimistic locks
19 minutes33 secondsDivision of locking methods: implicit lock and explicit lock
16 minutes07 secondsUnderstanding global locks and deadlocks
18 minutes25 secondsLock memory structure and monitoring strategy
20 minutes20 secondsMVCC solves reading and writing problems
10 minutes56 secondsMVCC Three Musketeers: Hidden fields, Undolog version chain, ReadView
26 minutes25 secondsMVCC operation process under read committed and repeatable read isolation levels
11 minutes47 secondsMVCC’s process for solving phantom reads under repeatable reads
07 minutes41 secondsOverview of the six major log files
12 minutes08 secondsGeneral query log, error log
20 minutes02 secondsBinlog log parameter setting and data recovery demonstration
43 minutes58 secondsBinlog deletion, binlog writing mechanism and two-phase submission
21 minutes44 secondsAnalysis of the steps and principles of relay logs and master-slave replication
26 minutes46 secondsImplementation of one master and one slave architecture and master-slave synchronization
34 minutes56 secondsBinlog format setting instructions
11 minutes49 secondsData synchronization consistency problem solving
14 minutes17 secondsOverview of data backup and implementing logical backup data with mysqldump
30 minutes06 secondsDemonstrate mysql to implement logical recovery of data
18 minutes06 secondsDemonstration of physical backup and physical recovery, export and import of table data
24 minutes12 secondsDatabase migration and how to delete the database without running away
18 minutes10 secondsMessage
08 minutes47 seconds