MySQL 8.0 for Developers

Introduction to MySQL

• Objectives
• Course Goals
• Course Lesson Map
• Introductions
• Classroom Environment
• MySQL Powers the Web
• MySQL Market Share: DB-Engines 2019
• MySQL Enterprise Edition
• Oracle Premier Support for MySQL
• MySQL and Oracle Integration
• MySQL Websites
• Community Resources
• Oracle University: MySQL Training
• MySQL Certification
• Summary
• Practice 1: Overview

Connectors and APIs

• Objectives
• Topics
• Programming MySQL
• MySQL Connectors
• Connectors Developed by Oracle
• Connectors Developed by the Community
• Quiz
• Topics
• MySQL and PHP
• Topics
• Connector/J
• Downloading Connector/J
• Installing Connector/J
• Topics
• Connector/Python
• Summary
• Practice 2: Overview

Using Connectors

• Objectives
• Topics
• Using PHP mysqli to Connect to and Query MySQL
• Example: PHP and mysqli
• Another Example: PHP and mysqli
• Using PDO_MySQL to Connect to and Query MySQL
• Retrieving Query Results with PDO_MySQL
• Example: PHP and PDO_MySQL
• Buffered Queries in PHP
• Quiz
• Topics
• Connecting to the MySQL Server with Connector/J
• Obtaining the Connector/J Connection Object
• Disconnecting from MySQL with Java
• Using Java to Execute SQL
• Example: Connector/J – executeUpdate()
• Example: Connector/J – executeQuery()
• JavaServer Pages
• JavaServer Pages Standard Tag Library (JSTL)
• Accessing MySQL with JSP/JSTL
• Quiz
• Topics
• Using Python to Connect to the MySQL Server
• Using Python to Execute SQL
• Example: Connector/Python – Modifying the Database
• Example: Connector/Python – Data Retrieval with fetchone()
• Example: Connector/Python – Data Retrieval with the cursor Object
• Buffered Cursors in Connector/Python
• Python CGI Programming
• Example: Python/MySQL CGI Programming
• Quiz
• Topics
• Connection Pooling
• Topics
• Using Special Characters in SQL Statements
• Referring to NULL Values in SQL Statements
• Dealing With NULL Values in the Result Set
• Topics
• Obtaining Connection Parameters
• Topics
• Securing Connections with SSL
• Connecting Via SSL with PDO_MySQL
• Connecting Via SSL with Connector/J
• Connecting Via SSL with Connector/Python
• Topics
• MySQL 8.0 and the caching_sha2_password Plugin
• Options for Connector not Supporting caching_sha2_password Plugin
• Summary
• Practice 3: Overview

Prepared Statements

• Objectives
• Topics
• Why Use Prepared Statements?
• Limitations of Prepared Statements
• SQL Injection
• Topics
• User-Defined Variables
• Topics
• Preparing a Statement
• Executing a Prepared Statement
• Deallocating a Prepared Statement
• Prepared Statement Example: SQL
• Quiz
• Topics
• Preparing Statements with Connectors
• Preparing Statements with PHP/PDO
• Example: Preparing Statements with PHP/PDO
• Preparing Statements with Connector/J
• Example: Preparing Statements with Connector/J
• Preparing Statements with Connector/Python
• Example: Preparing Statements with Connector/Python
• Quiz
• Summary
• Practice 4: Overview

Handling Errors and Warnings

• Objectives
• Topics
• SQL Modes
• The Default SQL Mode
• Setting SQL Modes
• Strict Mode
• Example: Strict Mode
• The NO_ENGINE_SUBSTITUTION SQL Mode
• Other Important SQL Modes
• Topics
• Interpreting Error Messages
• Investigating Error Codes with perror
• Troubleshooting Syntax Errors
• Using SHOW WARNINGS
• Example: SHOW WARNINGS
• Using SHOW ERRORS
• Topics
• Using MySQL Diagnostics
• Retrieving Diagnostic Information
• Example: Retrieving Diagnostic Information
• Topics
• Handling Errors When Using Connectors
• Connector/J: The SQLException Class
• Example: SQLException
• PHP/PDO Error Handling Mode
• PHP/PDO: The PDOException Class
• Example: PHP/PDO Exception Handling
• Connector/Python: The mysql.connector.Error Class
• Example: Connector/Python Error Handling
• Quiz
• Summary
• Practice 5: Overview

Creating Database-Driven Web Applications

• Objectives
• Topics
• Database-Driven Web Application
• Client Request
• Server Response
• Topics
• Displaying Query Results
• Displaying Query Results in Paragraph Tags (PHP/PDO)
• Displaying Query Results in Paragraph Tags (Java/JSP)
• Displaying Query Results in Paragraph Tags (Python)
• Separating Row Fetching and HTML Generation
• Quiz
• Displaying Query Results in a List
• Displaying Query Results in a List (PHP/PDO)
• Display Query Results in a List (Java/JSP)
• Displaying Query Results in a List (Python)
• Displaying Query Results in a Table
• HTML Table Markup
• Displaying Query Results in a Table (PHP/PDO): Header Row
• Displaying Query Results in a Table (PHP/PDO): Table Body
• Displaying Query Results in a Table (Java/JSP)
• Displaying Query Results in a Table (Python)
• Displaying Query Results as Hyperlinks (PHP/PDO)
• Displaying Query Results as Hyperlinks (Java/JSP)
• Displaying Query Results as Hyperlinks (Python)
• Topics
• Web Forms
• Comparing GET and POST Requests
• Web Form Processing
• Retrieving Request Parameters with PHP
• Retrieving Request Parameters with JSP/JSTL
• Retrieving Request Parameters with Python/CGI
• Topics
• Using Hyperlinks for Navigation (Single Page)
• Using Hyperlinks for Navigation (Multiple Pages)
• Topics
• Displaying Query Results on Multiple Pages
• Topics
• Using Hyperlinks to Sort by Column
• Summary
• Practice 6: Overview

Working with Views

• Objectives
• Topics
• Views
• Benefits of Views
• Creating Views
• Example: CREATE VIEW
• CREATE VIEW Options
• View Column Names
• Column Names Derived From Expressions
• Topics
• Updatable Views
• Example: Updatable View
• Insertable Views
• Updatable Versus Insertable Views
• Using WITH CHECK OPTION
• Using WITH CHECK OPTION: Example
• Updating Multiple-table Views
• Quiz
• Topics
• Checking Views
• Checking a View: Example
• Topics
• Altering Views
• Altering a View: Example
• Dropping Views
• Topics
• Obtaining View Metadata with INFORMATION_SCHEMA
• Obtaining View Metadata with INFORMATION_SCHEMA.TABLES: Example
• Obtaining View Metadata with INFORMATION_SCHEMA.VIEWS: Example
• Obtaining View Metadata with SHOW CREATE VIEW
• Other Metadata Statements for Views
• Quiz
• Summary
• Practice 7: Overview

Working with Strings

• Objectives
• Topics
• SQL Expressions
• String Expressions
• String Functions
• Topics
• Concatenating Strings Using MySQL Functions
• Concatenating NULL Values
• Concatenating Strings Using the || Operator
• Retrieving the Left and Right Portions of a String
• Retrieving Part of a String
• Trimming a String
• Inserting Into and Replacing Portions of a String
• Converting to Uppercase and Lowercase
• Topics
• Determining String Length
• Comparing Strings
• Finding Strings Within Strings
• Quiz
• Topics
• String Comparison and Sorting Behavior
• Character Sets and Collations
• Character Set and Collation Functions
• Changing the Default Collation
• Topics
• Simple Pattern Matching Using LIKE
• Examples: LIKE and NOT LIKE
• Complex Pattern Matching Using RLIKE
• Regular Expression Syntax
• Examples: Literal and Anchors
• Example: Alternative
• Example: Complex Pattern
• Examples: Escape Character
• Quiz
• Topics
• MySQL Full-Text Search
• Full-Text Index
• Natural Language Full-Text Search
• Boolean Full-Text Search
• Boolean Mode Operators
• Full-Text Index Configurations
• Summary
• Practice 8: Overview

Working with Numeric and Temporal Data

• Objectives
• Topics
• Boolean Expressions
• Numeric Literals
• Arithmetic Operators
• Numeric Expressions: Examples
• Topics
• Temporal Expressions
• Temporal Data Types
• Interval Arithmetic
• Quiz
• Topics
• Numeric Functions
• Using ABS() and SIGN()
• Approximating Numbers With FLOOR() and CEILING()
• Approximating Numbers With ROUND()
• Trigonometric Functions
• Quiz
• Topics
• Temporal Functions
• Temporal Functions Syntax
• Displaying the Current Date and Time
• Determining Date and Time Format
• Customizing the Output Format
• Retrieving Components of Dates and Times
• Creating Date and Time Values
• Performing Date Arithmetic Using Functions
• Topics
• CASE Expressions
• Examples: CASE Expressions
• NULLIF() and IFNULL() Functions
• IF() Function
• Quiz
• Summary
• Practice 9: Overview

Transactions

• Objectives
• Topics
• What Is a Transaction?
• Handling Partial Operations
• The ACID Properties of Transactions
• Topics
• Transaction Control Statements
• autocommit Mode
• Controlling autocommit Mode
• Statements Causing an Implicit COMMIT
• Topics
• General Approach to Coding Transactional Statements
• Coding Transactional Statements in PHP/PDO
• Coding Transactional Statements in Connector/J
• Coding Transactional Statements in Connector/Python
• Topics
• Retrieving the AUTO_INCREMENT Value of the Last Insertion
• Retrieving the ID of the Last Insert by Using PHP/PDO
• Retrieving the ID of the Last Insert by Using Connector/J
• Retrieving the Last Insert ID by Using Connector/Python
• Quiz
• Topics
• Consistency Issues
• Dirty Read: Example
• Non-repeatable Read: Example
• Phantom Read: Example
• Topics
• Isolation Levels
• Resolving Consistency Issues with Isolation Levels
• Setting Isolation Levels
• Displaying the Current Isolation Level
• Topics
• Locking
• Locking Reads
• SELECT…FOR SHARE: Example
• SELECT…FOR UPDATE: Example
• Quiz
• Summary
• Practice 10: Overview

Optimizing Queries

• Objectives
• Topics
• Why Optimize?
• Optimization Strategies
• Topics
• SQL Parser and Query Optimizer
• Topics
• Using Indexes for Optimization
• General Index Types
• Special Index Types
• Creating Indexes
• Adding Indexes to a New Table
• Multiple and Unique Value Indexes
• Primary Key Indexes
• Functional Index
• Naming Indexes
• Adding Indexes to Existing Tables
• Using Index Prefixes
• Indexing Large Columns Without Prefixes
• Indexing with Prefixes
• Evaluating Index Prefixes
• Leftmost Index Prefixes
• Leftmost Index Prefixes: Example
• Invisible Indexes
• Dropping Indexes
• Quiz
• Topics
• Using EXPLAIN to Analyze Queries
• How EXPLAIN Works
• EXPLAIN Output: Example 1
• EXPLAIN Output: Example 2
• EXPLAIN Output Columns
• EXPLAIN for Access Method Analysis
• EXPLAIN Types
• EXPLAIN for Simple Join
• Explanation of Simple Join Output
• Extra Column: Signs of Efficient Queries
• Extra Column: Signs of Inefficient Queries
• EXPLAIN Output in JavaScript Object Notation (JSON) Format
• Visual EXPLAIN
• Topics
• Query Rewriting Techniques
• Avoiding Column Expressions in Comparisons
• Creating Indexes to Support Joins
• Comparing Similar Data Types
• Avoiding Leading Wildcards in “LIKE” Patterns
• Creating Functional Indexes for Expressions
• Optimizing Queries by Limiting Output
• Using Summary Tables
• Using Summary Tables: Example
• Optimizing Data Modification Statements
• Quiz
• Topics
• MySQL Enterprise Monitor
• MySQL Enterprise Monitor Query Analyzer
• Summary
• Practice 11: Overview

Stored Routines

• Objectives
• Topics
• What Is a Stored Routine?
• Uses of Stored Routines
• Issues with Stored Routines
• Topics
• Creating Stored Procedures
• Creating Stored Functions
• Compound Statements in Stored Routines
• Compound Statement: Example
• Topics
• Local and User Variables
• DECLARE Statement
• Rules of Precedence for Variable Names
• Using SELECT…INTO
• Assigning Values to Variables with SET
• Parameter Declarations
• Parameter Declaration Syntax
• Topics
• Stored Routines in Databases
• Examining Stored Routines
• Removing Stored Routines
• Quiz
• Topics
• Control Flow Statements
• IF Statement
• CASE Statement: Simple Case
• CASE Statement: Searched Case
• REPEAT Statement
• WHILE Statement
• LOOP Statement
• LEAVE and ITERATE
• Quiz
• Topics
• Declaring Error Handlers
• Declaring Named Conditions
• RESIGNAL
• SIGNAL
• Topics
• Cursors
• Declaring Cursors
• Working with Cursors
• Working with Cursors: Example
• Limitations of Cursors
• Topics
• Limitations of Stored Routines
• Limitations of Stored Functions
• Summary
• Practice 12: Overview

Triggers and Scheduled Events

• Objectives
• Topics
• What Are Triggers?
• Trigger Use Cases
• Creating Triggers
• Trigger Events
• Using BEFORE Triggers
• Using AFTER Triggers
• Using OLD and NEW Qualifiers
• Trigger Metadata
• Trigger Error Handling
• Deleting Triggers
• Trigger Restrictions
• Quiz
• Topics
• Scheduling Database Operations
• Using the MySQL Event Scheduler
• Managing Events
• Summary
• Practice 13: Overview

Reporting

• Objectives
• Topics
• Calculating Totals and Averages
• Finding Minimum and Maximum Values
• Finding the Row Containing Minimum or Maximum Value
• Finding All Rows Containing the Minimum or Maximum Value
• Counting Rows
• Performing Multiple Counts on a Set of Rows
• Conditional Counting
• Calculating Percentages
• Multiple Conditions Within Aggregate Functions
• Quiz
• Topics
• Grouping by the Results of an Expression
• Grouping Temporal Data
• Grouping Nonrepetitive Data
• Grouping Nonrepetitive Data: Example
• Topics
• Generating Row Numbers
• Rank and Row Number
• Calculating Percentage Using Column Total
• Calculating Percentage using Partitioned Total
• Topics
• Materialized Views
• Summary Tables: Advantages and Disadvantages
• Topics
• Crosstab Reports
• Showing Totals in Crosstab Reports
• Topics
• Creating a SQL Bar Chart
• Adding Extra Information to the Bar Chart
• Topics
• Decision Tables
• Explaining the Decision Table
• Implementing the Decision Table in MySQL: Step 1
• Implementing the Decision Table in MySQL: Step 2
• Implementing the Decision Table in MySQL: Step 3
• Summary
• Practice 14: Overview

Spatial Data

• Objectives
• Topics
• Spatial Data Support in MySQL
• Topics
• OpenGIS Geometry Model
• Geometry Class
• More Geometry Class Properties
• Point Class
• Curve Class
• LineString Class
• Surface Class
• Polygon Class
• Collections of Geometries
• Topics
• Spatial Data Formats
• Representing Geometry Objects as WKT
• Creating Geometry Objects from WKT
• Well-Known Binary (WKB) Format
• Creating and Populating Spatial Columns
• Fetching Spatial Data
• Quiz
• Topics
• Spatial Reference Systems
• Spatial Reference IDs (SRIDs)
• Specifying the SRID
• Distance Calculations Depend on the SRID
• Topics
• Analyzing Spatial Data
• Spatial Indexes
• Creating and Dropping Spatial Indexes
• Creating a Spatial Index: Examples
• Topics
• Spatial Data Functions: Review
• Other Spatial Data Functions
• Examples of ST_Contains() and ST_Within()
• Examples of ST_Intersects()
• Examples of ST_Area()
• Quiz
• Summary
• Practice 15: Overview

JSON Data

• Objectives
• Topics
• JavaScript Object Notation (JSON)
• JSON Objects
• JSON Arrays
• Combinations of Nested Objects and Arrays
• Quiz
• Topics
• MySQL JSON Data Type
• Using a JSON Column in a Table
• Querying a JSON Column in a Table
• Topics
• JSON Functions in MySQL
• JSON_OBJECT() Function
• JSON_ARRAY() Function
• JSON_OBJECT() and JSON_ARRAY() Can Be Nested
• JSON Paths
• JSON Path Examples
• JSON_EXTRACT() Function
• JSON_UNQUOTE() Function
• JSON_PRETTY() Function
• JSON_REMOVE() Function
• JSON_SET() Function
• Updating JSON Columns
• Other JSON Functions
• Quiz
• Topics
• Generated Columns
• Indexing JSON Data with a Generated Column
• Storing Generated Columns
• Summary
• Practice 16: Overview

Document Store

• Objectives
• Topics
• NoSQL: Overview
• NoSQL and Traditional RDBMS: Comparison
• Topics
• Document Store and X DevAPI Functions
• A Document Store Example in world_x
• Example of a JSON Document in the countryinfo Collection
• X DevAPI Functions – Connecting to a Schema
• X DevAPI Functions – Accessing, Adding, and Dropping Collections
• X DevAPI Functions – Collections and Relational Tables
• X DevAPI Functions – Additional Functions
• X DevAPI Functions – Examples of Chaining Functions
• Quiz
• Topics
• Using X DevAPI with PHP to Access a Document Store
• PHP with X DevAPI – Reading Data from an Existing Collection
• PHP with X DevAPI – Creating and Modifying a Collection
• PHP with X DevAPI – Working with Relational Tables
• Topics
• Using X DevAPI with Connector/J to Access a Document Store
• Connector/J with X DevAPI – Reading Data from an Existing Collection
• Connector/J with X DevAPI – Creating and Modifying a Collection
• Connector/J with X DevAPI – Working with Relational Tables
• Topics
• Using X DevAPI with Connector/Python to Access a Document Store
• Connector/Python with X DevAPI – Reading Data from an Existing Collection
• Connector/Python with X DevAPI – Creating and Modifying a Collection
• Connector/Python with X DevAPI – Working with Relational Tables
• Topics
• Using MySQL Shell to Access JSON
• Starting MySQL Shell
• Using MySQL Shell to Access a Document Store – SQL Mode
• Using MySQL Shell to Access a Document Store – Current Schema
• Accessing the Document Store in MySQL Shell
• Accessing the Document Store in MySQL Shell – Vertical Format
• Accessing the Document Store in MySQL Shell – Pretty-printing
• Quiz
• Topics
• Issuing Python statements from MySQL Shell – Python as the Active Language
• 17-41
• Python in MySQL Shell – Reading Data from an Existing Collection
• Python in MySQL Shell – Executing X DevAPI Functions Interactively
• Python in MySQL Shell – Creating and Modifying a Collection
• Python in MySQL Shell – Working with Relational Tables
• Summary
• Practice 17: Overview

Conclusion

• Course Goals
• Oracle University: MySQL Training
• MySQL Websites
• Your Evaluation
• Thank You
• Q&A Session