OBIETTIVI

• Capire le misure necessarie per invocare programmi client MySQL
• Utilizzare il generico programma client mysql e il MySQL Query Browser interfaccia utente grafica (GUI) per eseguire query e recuperare i risultati
• Selezionare il miglior tipo di dati per rappresentare informazioni in MySQL
• Gestire le caratteristiche strutturali dei database e le tabelle nel database
• Utilizzare l’istruzione SELECT per recuperare informazioni da tabelle di database
• Utilizzare espressioni nelle query SQL per ottenere informazioni più dettagliate
• Utilizzare le istruzioni SQL per modificare i contenuti delle tabelle del database
• Scrivi unire espressioni nelle istruzioni SQL per ottenere informazioni da più tabelle
• Utilizzare subquery nelle istruzioni SQL
• Creare viste utilizzando istruzioni SELECT per produrre tavoli virtuali di dati specifici
• Eseguire l’importazione di massa di dati e le operazioni di esportazione
• Creare variabili definite dall’utente, istruzioni preparate e routine memorizzate
• Creare e gestire trigger
• Utilizzare il database INFORMATION_SCHEMA per accedere ai metadati
• Debug di applicazioni MySQL
• Configurare e ottimizzare MySQL

CONTENUTI:

1: Introduction to MySQL

• Introduction to MySQL
• Practice 0-1: Course Overview Environment
• Practice 1-1: Course Environment

2: Connectors and APIs

• Connectors and APIs
• Practice 2-1: Quiz

3: Using Connectors

• Using Connectors
• Connection Pooling
• Practice 3-1: Connecting to the MySQL Server in a Web Environment with PHP/PDO
• Practice 3-2: Connecting to the MySQL Server in a Web Environment with Connector/J
• Practice 3-3: Connecting to the MySQL Server in a Web Environment with Connector/Python
• Practice 3-4: Executing SQL in a Web Environment with PHP/PDO
• Practice 3-5: Executing SQL in a Web Environment with Connector/J
• Practice 3-6: Executing SQL in a Web Environment with Connector/Python

4: Prepared Statements

• Prepared Statements
• Practice 4-1: Preparing Statements in MySQL
• Practice 4-2: Using Prepared Statements with PHP/PDO
• Practice 4-3: Using Prepared Statements with Connector/J
• Practice 4-4: Using Prepared Statements with Connector/Python

5: Handling Errors and Warnings

• Handling Errors and Warnings
• Practice 5-1: Quiz – Handling Errors
• Practice 5-2: Identifying Errors and Warnings with the mysql Command-Line Client
• Practice 5-3: Handling Errors and Warnings When Coding with PHP/PDO
• Practice 5-4: Handling Errors and Warnings When Coding with Connector/J
• Practice 5-5: Handling Errors and Warnings When Coding with Connector/Python

6: Creating Database-Driven Web Applications

• Creating Database-Driven Web Applications
• Web Forms
• Practice 6-1: Displaying Query Results in a Table Using PHP/PDO
• Practice 6-2: Displaying Query Results in a Table Using Java/JSP
• Practice 6-3: Displaying Query Results in a Table Using Python
• Practice 6-4: Paging Query Results Using PHP/PDO
• Practice 6-5: Paging Query Results Using Java/JSP
• Practice 6-6: Paging Query Results Using Python
• Practice 6-7: Enabling Sorting of Query Results Using PHP/PDO
• Practice 6-8: Enabling Sorting of Query Results Using Java/JSP
• Practice 6-9: Enabling Sorting of Query Results Using Python

7: Working with Views

• Working with Views
• Practice 7-1: Creating a View
• Practice 7-2: Updating a View
• Practice 7-3: Checking a View
• Practice 7-4: Obtaining View Metadata
• Practice 7-5: Creating, Updating, and Querying Multiple-table Views

8: Working with Strings

• Working with Strings
• Practice 8-1: Manipulating String Data with MySQL Expressions
• Practice 8-2: Matching Patterns in Strings
• Practice 8-3: Using Full-Text Searches

9: Working with Numeric and Temporal Data

• Working with Numeric and Temporal Data
• Practice 9-1: Using Numeric Data in SQL Expressions
• Practice 9-2: Using Temporal Data in SQL Expressions
• Practice 9-3: Using Control Flow Functions in SQL Expressions

10: Transactions

• Transactions
• Consistency Issues
• Practice 10-1: Quiz—Transactions, Isolation Levels, and Locking
• Practice 10-2: Examining the Effect of Isolation Levels in Concurrent Transactions
• Practice 10-3: Using Transactional Statements with PHP/PDO
• Practice 10-4: Using Transactional Statements with Connector/J
• Practice 10-5: Using Transactional Statements with Connector/Python

11: Optimizing Queries

• Optimizing Queries
• Using EXPLAIN to Analyze Queries
• Practice 11-1: Quiz – Optimization
• Practice 11-2: Managing Table Indexes
• Practice 11-3: Optimizing Queries Using Indexes
• Practice 11-4: Rewriting Queries for Optimization
• Practice 11-5: Identifying and Fixing Slow Queries with MySQL Enterprise Monitor Query Analyzer

12: Stored Routines

• Stored Routines
• Control Flow Statements
• Practice 12-1: Creating Simple Stored Routines
• Practice 12-2: Creating Stored Routines with Compound Statements
• Practice 12-3: Creating Stored Routines with Parameters
• Practice 12-4: Examining Stored Routines Metadata
• Practice 12-5: Deleting and Recreating a Stored Routine
• Practice 12-6: Creating Stored Routines with Control Flow Statements
• Practice 12-7: Working with Conditions and Handler in Stored Routines
• Practice 12-8: Working with Cursors in Stored Routines
• Practice 12-9: Creating a Functional Library

13: Triggers and Scheduled Events

• Triggers and Scheduled Events
• Using OLD and NEW Qualifiers
• Practice 13-1: Creating and Dropping Triggers
• Practice 13-2: Investigating Triggers in the sakila Database
• Practice 13-3: Creating a Scheduled Event

14: Reporting

• Reporting
• Materialized Views
• Practice 14-1: Aggregating Data
• Practice 14-2: Grouping Aggregated Data
• Practice 14-3: Using Window Functions
• Practice 14-4: Creating a Crosstab Report with a Bar Chart

15: Spatial Data

• Spatial Data
• Practice 15-1: Creating Spatial Data
• Practice 15-2: Using Spatial Indexes for Analysis

16: JSON Data

• JSON Data
• Practice 16-1: Creating and Querying JSON Data
• Practice 16-2: Updating JSON Data
• Practice 16-3: Indexing JSON Data

17: Document Store

• Document Store
• Using X DevAPI with Connector/J to Access a Document Store
• Practice 17-1: Accessing JSON in MySQL Shell
• Practice 17-2: Accessing JSON by Using X DevAPI with PHP
• Practice 17-3: Accessing JSON by Using X DevAPI with Connector/J
• Practice 17-4: Accessing JSON by Using X DevAPI with Connector/Python
• Practice 17-5: Accessing JSON by Using Python Statements in MySQL Shell