Visão Geral

Curso MariaDB para desenvolvedores foi projetado para Desenvolvedores MariaDB que têm um bom conhecimento de um banco de dados MariaDB e experiência no uso de comandos SQL.

O curso fornece mais experiência prática em comandos MariaDB mais avançados e instruções SQL, incluindo suporte a expressões regulares, expressões de tabela comuns, funções de janela, rotinas armazenadas, funções personalizadas, tratamento de exceções e gatilhos.

Exercícios e exemplos são usados ​​ao longo do curso para fornecer experiência prática com as técnicas abordadas.

Objetivos do Curso

• Para fornecer as habilidades necessárias para escrever consultas mais avançadas e instruções de manutenção de banco de dados em um banco de dados MariaDB.

Objetivo

Após realizar este Curso MariaDB para desenvolvedores você será capaz de:

• Usando recursos avançados do cliente MySQL com MariaDB
• Usando tipos de dados avançados
• Escrever instruções de consulta SQL complexas
• Usando expressões SQL avançadas
• Execução de operações avançadas de inserção, atualização, exclusão, substituição e truncamento
• Usando sintaxe e propriedades de variáveis ​​de usuário
• Importando e exportando dados de dentro do MariaDB
• Importar e exportar dados da linha de comando
• Executando subconsultas complexas
• Usando Conectores MariaDB
• Integração MariaDB e NoSQL
• Criação, gerenciamento e uso de visualizações
• Implementando uma Expressão de Tabela Comum (CTE)
• Usando funções de janela
• Implementando Expressões Regulares
• Usando declarações preparadas
• Criação e uso de rotinas armazenadas
• Criação e uso de gatilhos
• Obtenção de metadados de banco de dados
• Otimizando consultas
• Trabalhando com os principais mecanismos de armazenamento
• Depurando aplicativos MariaDB

Publico Alvo

• Desenvolvedores que possuem um conhecimento básico de um banco de dados MariaDB (ou MySQL) e comandos SQL conforme abordado no curso Introdução ao MariaDB.
  

Pre-Requisitos

• É necessário um conhecimento prático de MariaDB (ou MySQL). Isso pode ser obtido através da participação na Introdução ao MariaDB .
  

Materiais

Conteúdo Programatico

Course Introduction

1. Administration and Course Materials
2. Course Structure and Agenda
3. Delegate and Trainer Introductions

CLIENT/SERVER CONCEPTS

1. MariaDB client/server architecture
2. Server modes
3. Using client programs
4. Logging in options
5. Configuration files
6. Precedence of logging in options
7. Exercises: Using client/server

THE MYSQL CLIENT PROGRAM FOR MariaDB

1. Using MariaDB interactively
2. The MariaDB prompts
3. Client commands and SQL statements
4. Editing
5. Selecting a database
6. Help
7. Safe updates
8. Using script files
9. Using a source file
10. Redirecting output into a file
11. Command line execution
12. MariaDB output formats
13. Overriding the defaults
14. Html and xml output
15. MySQL Utilities
16. Exercises: Using the MySQL client program to connect to MariaDB

DATA TYPES

1. Bit data type
2. Numeric data types
3. Auto_increment
4. Character string data types
5. Character sets and collation
6. Binary string data types
7. Enum and Set data types
8. Temporal data types
9. Timezone support
10. Spatial Datatypes
11. Handling Missing Or Invalid Data Values
12. SQL_MODE options
13. Exercises: Using data types

IDENTIFIERS

1. Using Quotes with identifier naming
2. Case sensitivity in Identifier naming
3. Qualifying columns with table and database names
4. Using reserved words as identifiers
5. Function names
6. Exercises: Using identifiers

QUERYING FOR DATA

1. The SQL select statement and MAriaDB differences
2. Advanced order by
3. Order by and collation
4. Order by with enum datatype
5. Order by with Set datatype
6. Ordering with distinct and group by
7. Special features of union
8. Limit and order by clauses
9. Group By clause
10. Group_concat
11. Using Rollup in a Group By clause
12. Exercises: Querying for data

SQL EXPRESSIONS AND FUNCTIONS

1. Components of expressions
2. Nulls
3. Numeric expressions
4. String expressions
5. Temporal expressions
6. Comparison functions
7. Flow control functions
8. Numeric functions
9. String functions
10. Temporal functions
11. Exercises: Using expressions and functions

UPDATING DATA

1. Update operations and privileges
2. Inserting rows
3. Insert using a set clause
4. Inserting duplicate values
5. Replacing rows
6. Updating rows
7. Update using the order by and limit clauses
8. Deleting rows
9. The delete and truncate statements
10. Exercise: Inserting, updating, replacing and deleting data

CONNECTORS

1. MariaDB connectors
2. Connecting to MariaDB server using Java and PHP connectors
3. MariaDB and NoSQL

OBTAINING DATABASE METADATA

1. What is metadata?
2. Using mysqlshow utility with MariaDB
3. The show and describe commands
4. Describing tables
5. The information_schema
6. Listing tables
7. Listing columns
8. Listing views
9. Listing key_columns_usage
10. Exercises: Obtaining database metadata

DEBUGGING

1. MariaDB error messages
2. The show statement
3. Show errors
4. Show count(*) errors
5. Show warnings
6. Show count(*) warnings
7. Note messages
8. The perror utility
9. Exercises: Debugging

IMPORT AND EXPORT

1. Exporting using SQL
2. Privileges required to export data
3. Importing using SQL
4. Messages when loading data
5. Privileges required to load data
6. Exporting from the command line
7. Mysqldump main options
8. Importing from the command line
9. Mysqlimport main options
10. Exercises: Importing and exporting

SUBQUERIES

1. Types of subquery
2. Multiple-column subqueries
3. Correlated subqueries
4. Using the ANY, ALL and SOME operators
5. Using the EXISTS operator
6. Subqueries as scalar expressions
7. Derived Table
8. WITH Clause ( Common Table Expression - CTE )
9. Recursive CTEs
10. Using subqueries in updates and deletes
11. Exercises: Coding subqueriess

VIEWS

1. Why views are used
2. Creating views
3. View creation restrictions
4. View algorithms
5. Updateable views
6. Altering and dropping views
7. Displaying information about views
8. Privileges for views
9. Exercises: Using views

USING WINDOW FUNCTIONS

1. Description
2. Non-aggregate window functions
3. Using RANK, DENSE_RANK, ROW_NUMBER, NTILE
4. Window partition clause
5. Using LAG. LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
6. Aggregate window functions - SUM, AVG etc
7. Window frame clause
8. Exercises: Using Window Functions

REGULAR EXPRESSION SUPPORT

1. Overview of Regular Expression
2. Regular Expression Notation
3. The REGEXP_LIKE operator
4. The REGEXP_INSTR function
5. The REGEXP_SUBSTR function
6. The REGEXP_INSTR function
7. The REGEXP_REPLACE function
8. Exercises: Regular Expression Support

USER VARIABLES AND PREPARED STATEMENTS

1. Creating User variables
2. User variables in a select
3. Prepared statements
4. The prepare statement
5. The execute statement
6. The deallocate statement
7. Using prepared statements in code, with connectors
8. Exercises: Using variables and prepared statements

INTRODUCTION TO STORED ROUTINES

1. Types of stored routines
2. Benefits of stored routines
3. Stored routine features
4. Differences between procedures and functions
5. Introduction to the Block
6. Declaring variables and constants
7. Assigning values to variables
8. Definer rights and invoker rights
9. Using SELECT in stored routines
10. Altering and dropping stored routines
11. Obtaining stored routine metadata
12. Stored routine privileges and execution security
13. Exercises: Writing simple stored routines

STORED ROUTINES - PROGRAM LOGIC

1. The IF .. THEN .. ELSEIF construct
2. The CASE statement
3. The basic loop
4. The while loop
5. The repeat loop
6. The iterate statement
7. Nested loops
8. Exercises: Writing stored routines with program logic

PROCEDURES WITH PARAMETERS

1. Creating procedures with parameters
2. Calling Procedures With Parameters
3. Exercises: Writing stored routines with parameters

STORED ROUTINES - EXCEPTION HANDLERS & CURSORS

1. Dealing with errors using Exception handlers
2. Cursors
3. What is a cursor?
4. Cursor operations
5. Declaring cursors
6. Opening and closing cursors
7. Fetching rows
8. Status checking
9. Exercises: Writing stored routines with program logic

FUNCTIONS

1. What is a function?
2. The create function statement
3. Executing functions
4. Executing functions from code
5. Executing functions from SQL statements
6. The deterministic and SQL clauses
7. Exercises: Writing functions

TRIGGERS

1. Trigger creation
2. Restrictions on triggers
3. The create trigger statement
4. Using the old and new qualifiers
5. Managing triggers
6. Destroying triggers
7. Required privileges
8. Exercises: Writing triggers

BASIC OPTIMIZATIONS

1. Normalisation of data to third normal form
2. Using indexes for optimization
3. General query enhancement
4. Using Explain to analyze queries
5. Choosing an INNODB or MYISAM storage engine
6. Using MariaDB Enterprise Monitor in query optimization
7. Exercises: Making use of basic optimizations

MORE ABOUT INDEXES

1. Indexes and joins
2. Exercises: Investigating indexes and joins