COURSE OBJECTIVE:
Course Objectives To provide the skills needed to write more advanced queries and database maintenance statements on a MariaDB database.
TARGET AUDIENCE:
Who will the Course Benefit? Developers who have a basic understanding of a MariaDB (or MySQL) database and SQL commands as covered on the Introduction to MariaDB course.
COURSE PREREQUISITES:
Requirements A working knowledge of MariaDB (or MySQL) is required. This can be gained by attendance on the Introduction to MariaDB.
COURSE CONTENT:
MariaDB for Developers Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: CLIENT/SERVER CONCEPTS • MariaDB client/server architecture • Server modes • Using MariaDB client programs • Logging in options • Configuration files • Precedence of logging in options • Exercises: Using client/server Session 2: THE MYSQL CLIENT PROGRAM FOR MariaDB • Using MariaDB interactively • The MariaDB prompts • Client commands and SQL statements • Editing • Selecting a database • Help • Safe updates • Using script files • Using a source file • Redirecting output into a file • Command line execution • MariaDB output formats • Overriding the defaults • Html and xml output • Exercises: Using the MariaDB client program to connect to MariaDB Session 3: DATA TYPES • Bit data type • Numeric data types • Auto_increment • Character string data types • Character sets and collation • Binary string data types • Enum and Set data types • Temporal data types • Timezone support • Spatial Datatypes • Handling Missing Or Invalid Data Values • SQL_MODE options • Exercises: Using data types Session 4: IDENTIFIERS • Using Quotes with identifier naming • Case sensitivity in Identifier naming • Qualifying columns with table and database names • Using reserved words as identifiers • Function names • Exercises: Using identifiers Session 5: QUERYING FOR DATA • The SQL select statement • Advanced order by • Order by and collation • Order by with enum datatype • Order by with Set datatype • Ordering with distinct and group by • Special features of union • Limit and order by clauses • Group By clause • Group_concat • Using Rollup in a Group By clause • Exercises: Querying for data MariaDB for Developers Training Course Course Contents – DAY 2 Session 6: SQL EXPRESSIONS AND FUNCTIONS • Components of expressions • Nulls • Numeric expressions • String expressions • Temporal expressions • Comparison functions • Flow control functions • Numeric functions • String functions • Temporal functions • Exercises: Using expressions and functions Session 7: UPDATING DATA • Update operations and privileges • Inserting rows • Insert using a set clause • Inserting duplicate values • Replacing rows • Updating rows • Update using the order by and limit clauses • Deleting rows • The delete and truncate statements • Exercise: Inserting,updating,replacing and deleting data Session 8: CONNECTORS • MariaDB connectors • Connecting to MariaDB server using Java and PHP connectors • MariaDB and NoSQL Session 9: OBTAINING DATABASE METADATA • What is metadata? • Using mariadb-show utility • The show and describe commands • Describing tables • The information_schema • Listing tables • Listing columns • Listing views • Listing key_columns_usage • Exercises: Obtaining database metadata Session 10: DEBUGGING • MariaDB error messages • The show statement • Show errors • Show count(*) errors • Show warnings • Show count(*) warnings • Note messages • The perror utility • Exercises: Debugging MariaDB for Developers Training Course Course Contents – DAY 3 Session 11: IMPORT AND EXPORT • Exporting using SQL • Privileges required to export data • Importing using SQL • Messages when loading data • Privileges required to load data • Exporting from the command line • Mariadb-export main options • Importing from the command line • Mariadb-import main options • Exercises: Importing and exporting data Session 12: SUBQUERIES • Types of subquery • Multiple-column subqueries • Correlated subqueries • Using the ANY,ALL and SOME operators • Using the EXISTS operator • Subqueries as scalar expressions • Derived Table • WITH Clause ( Common Table Expression – CTE ) • Recursive CTEs • Using subqueries in updates and deletes • Exercises: Coding subqueriess Session 13: VIEWS • Why views are used • Creating views • View creation restrictions • View algorithms • Updateable views • Altering and dropping views • Displaying information about views • Privileges for views • Exercises: Using views Session 14: USING WINDOW FUNCTIONS • Description • Non-aggregate window functions • Using RANK,DENSE_RANK,ROW_NUMBER,NTILE • Window partition clause • Using LAG. LEAD,FIRST_VALUE,LAST_VALUE,NTH_VALUE • Aggregate window functions – SUM,AVG etc • Window frame clause • Exercises: Using Window Functions Session 15: REGULAR EXPRESSION SUPPORT • Overview of Regular Expression • Regular Expression Notation • The REGEXP_LIKE operator • The REGEXP_INSTR function • The REGEXP_SUBSTR function • The REGEXP_INSTR function • The REGEXP_REPLACE function • Exercises: Regular Expression Support MariaDB for Developers Training Course Course Contents – DAY 4 Session 16: USER VARIABLES AND PREPARED STATEMENTS • Creating User variables • User variables in a select • Prepared statements • The prepare statement • The execute statement • The deallocate statement • Using prepared statements in code,with connectors • Exercises: Using variables and prepared statements Session 17: INTRODUCTION TO STORED ROUTINES • Types of stored routines • Benefits of stored routines • Stored routine features • Differences between procedures and functions • Introduction to the Block • Declaring variables and constants • Assigning values to variables • Definer rights and invoker rights • Using SELECT in stored routines • Altering and dropping stored routines • Obtaining stored routine metadata • Stored routine privileges and execution security • Exercises: Writing simple stored routines Session 18: STORED ROUTINES – PROGRAM LOGIC • The IF .. THEN .. ELSEIF construct • The CASE statement • The basic loop • The while loop • The repeat loop • The iterate statement • Nested loops • Exercises: Writing stored routines with program logic Session 19: PROCEDURES WITH PARAMETERS • Creating procedures with parameters • Calling Procedures with Parameters • Exercises: Writing stored routines with parameters MariaDB for Developers Training Course Course Contents – DAY 5 Session 20: STORED ROUTINES – EXCEPTION HANDLERS & CURSORS • Dealing with errors using Exception handlers • Cursors • What is a cursor? • Cursor operations • Declaring cursors • Opening and closing cursors • Fetching rows • Status checking • Exercises: Writing stored routines with program logic Session 21: FUNCTIONS • What is a function? • The create function statement • Executing functions • Executing functions from code • Executing functions from SQL statements • The deterministic and SQL clauses • Exercises: Writing functions Session 22: TRIGGERS • Trigger creation • Restrictions on triggers • The create trigger statement • Using the old and new qualifiers • Managing triggers • Destroying triggers • Required privileges • Exercises: Writing triggers Session 23: BASIC OPTIMIZATIONS • Normalisation of data to third normal form • Using indexes for optimization • General query enhancement • Using Explain to analyze queries • Choosing an INNODB or MYISAM storage engine • Exercises: Making use of basic optimizations Session 24: MORE ABOUT INDEXES • Indexes and joins • Exercises: Investigating indexes and joins
FOLLOW ON COURSES:
Further Learning MariaDB Database Administration