Next Section: 二进制类型与JSON类型讲解928 plays

5 秒后自动播放下一节
  Watch Again

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

Introduction >
ChapterCourseware
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
  • CancelreplySending
  • Cancelpost a noteSending