Download our latest
Android and iPhone apps ...
At a glance ...
Pages
533
Exercises
16
Sections
24
Public
On-Site
Level
1
Days
4

Course Name: Essential MariaDB 10

Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
4 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
24
16
533
None
None
Contact us for the latest pricing

Synopsis for course

All the power of a large Database but with little or no setup or running problems

This course breaks down the usage of MariaDB for storage and retrieval of data, this is principally for Users needing to extract and maintain the Database

The course uses the MariaDB command line tool for all examples and exercises

Bear in mind MariaDB was created by the original creators of MySQL, there is little or no difference in this product with MySQL, if you need a public course please select the MySQL course.

Course Contents

Section
Contents
Page

One
Introduction to MariaDB
2

 
- Definition of SQL
3

 
- Definition of a Database
4

Two
Database Tables
5

 
- Database Views
10

Three
The MariaDB Interface
11

 
- Introduction
12

 
- Interface Keys
13

 
- Copying and Pasting
14

 
- Help
17

 
- Exiting MariaDB
18

Four
Simple Queries
19

 
- Introduction
20

 
- Show Tables
21

 
- Show Columns
24

 
- Basic interrogation of a Table
26

 
- Running existing SQL
31

 
- Saving output to a file
32

 
- Exercise One
38

 
- SQL Syntax
42

 
- Displaying Literals in a Select statement
45

 
- Displaying Current Date in a Select statement
48

 
- Column Aliases
50

 
- Ordering Data
54

 
- Ordering more than One Column
57

 
- Performing Calculations
58

 
- Commenting
59

 
- Exercise Two
60

Five
Using Functions
66

 
- Counting Records in a Table
67

 
- Summing Records in a Table
69

 
- Highest/Lowest/Average Records in a Table
70

 
- Distinct Records in a Table
71

 
- Row Functions
72

 
- Changing Case
73

 
- Length
74

 
- Rounding Values
75

 
- Substring
77

 
- Coalesce
79

 
- Ifnull
81

 
- Format
82

 
- Concat
83

 
- Concat_ws
84

 
- Nesting Functions
85

 
- Manipulating Dates using date_add
86

 
- Displaying Dates using date_format
88

 
- Exercise Three
90

Six
Limiting Data Selected with a Where Clause
95

 
- Limiting Data
96

 
- Equals
97

 
- Not Equals
98

 
- Less than / Greater than
99

 
- IN
100

 
- Between
101

 
- Wildcards (Like)
103

 
- Column comparison
111

 
- Handling Nulls
112

 
- Case Restrictions
113

 
- Using Functions
114

 
- Joining Where Clauses
116

 
- Sub-queries using IN
118

 
- Sub-queries using more than one column
120

 
- Sub-queries using Exists
123

 
- Sub-queries using Not Exists
125

 
- Sub-queries using other where clauses
126

 
- Using Limit
127

 
- Exercise Four
130

Seven
Aggregating Values in MariaDB
136

 
- Introduction
137

 
- Group By
138

 
- Having
141

 
- Using With Rollup to Total Grouping
143

 
- Exercise Five
144

Eight
Regular Expressions
148

 
- Introduction
149

 
- Testing for 'Contains' within a String
150

 
- Starts or Ends using Line Anchors
152

 
- Starts and Ends using Line Anchors (or)
154

 
- Using the Dot (Any Character)
155

 
- Using {Intervals}
157

 
- Using Character Lists []
159

 
- Using Character Lists to exclude matches
162

 
- POSIX
163

 
- Using the Question Mark (Optional)
165

 
- Using the Plus sign (Mandatory)
167

 
- Using the Star sign (Optional)
168

 
- Exercise Six
169

Nine
Joining Tables in an SQL Statement
171

 
- Introduction
172

 
- Identifying Columns and its Table
173

 
- Cartesian products
175

 
- Linking Tables
176

 
- Methods of joining tables
178

 
- ANSI SQL86
179

 
- ANSI SQL99
181

 
- ANSI SQL99 (Natural Joins)
182

 
- ANSI SQL99 (Cross Joins)
183

 
- ANSI SQL99 (Using)
184

 
- ANSI SQL99 (On)
185

 
- ANSI SQL99 (Multiple On)
186

 
- ANSI SQL99 Outer Joins
187

 
- ANSI SQL99 (Left Outer Joins)
190

 
- ANSI SQL99 (Right Outer Joins)
191

 
- ANSI SQL99 (Full Outer Joins)
192

 
- Merging Tables
193

 
- Using Union to Merge Tables
194

 
- Using Union All to Merge Tables
195

 
- Using Intersect to Merge Tables
196

 
- Using Minus to Merge Tables
197

 
- Knowledge Check
198

 
- Knowledge Check - Union
199

 
- Knowledge Check - Union All
200

 
- Knowledge Check - Intersect
201

 
- Knowledge Check - Minus
202

 
- Using the Merge Syntax
203

 
- Intersect Syntax
204

 
- Minus Syntax
205

 
- Exercise Seven
206

Ten
More MySql Query Techniques
211

 
- Using If
212

 
- Using Ifnull
213

 
- Using Nullif
214

 
- Using Case Function
215

 
- Using Inline SQL
220

 
- Using Inline SQL with Select
221

 
- Using Correlated Inline SQL with Select
222

 
- Using Inline SQL with From
223

 
- Using Set to create variables
224

 
- Populating a Variable from a Query
225

 
- Clearing a User Variable
227

 
- Exercise Eight
228

Eleven
MySQL Datatypes
233

 
- Introduction
234

 
- Numeric Datatypes (Integer)
235

 
- Numeric Datatypes (Decimal)
236

 
- String Text Datatypes
237

 
- String Blob Datatypes
238

 
- String List Datatypes
239

 
- Date Datatypes
240

Twelve
Creating Database Objects
241

 
- Creating Tables
242

 
- Mandatory Columns Tables
244

 
- Creation Syntax for Tables
245

 
- Column Creation
249

 
- AUTO_INCREMENT
251

 
- Last_insert_id
252

 
- Drop Table Syntax
253

 
- Temporary Tables
254

 
- Creating Indexes
256

 
- Primary Key Creation
259

 
- Primary Key Removal
261

 
- Unique Key Creation
262

 
- Foreign Key Creation
265

 
- On Delete / On Update
267

 
- CASCADE
270

 
- SET NULL
271

 
- NO ACTION
272

 
- RESTRICT
273

 
- SET DEFAULT
274

 
- Creating Views
275

 
- Dropping Views
278

 
- Renaming Views and Tables
279

 
- Listing Views
280

 
- Exercise Nine
281

Thirteen
Altering Elements
283

 
- Altering Tables
284

Fourteen
Inserting and Altering Data
287

 
- Rules
288

 
- Syntax for Populating Tables
289

 
- Inserting using the Set syntax
295

 
- Multiple Inserts
296

 
- On Duplicate Key Update
297

 
- Using Replace for Inserting Records
298

 
- General Comments
301

 
- Updating Records Rules
302

 
- Updating Data Tables
303

 
- Deleting Data
306

 
- Truncate
307

 
- Inserting Large Objects
308

 
- Extracting Large Objects
309

 
- Views
310

 
- Exercise Ten
314

Fifteen
Prepared Statements
321

 
- Introduction
322

 
- Preparing the Statement
323

 
- Removing Prepared Statements
324

 
- Using Parameters
325

Sixteen
Committing Records
327

 
- DDL and DML
328

 
- Autocommit
330

 
- Commit and Rollback
331

 
- Transactions
333

 
- What happens if disconnected ?
335

 
- Savepoints
336

Seventeen
Importing and Exporting
340

 
- Saving output to a file
341

 
- Saving output to Variables
347

 
- Importing Data using Infile
348

 
- Ignoring Lines using Infile
350

 
- Using the Set command with Infile
351

 
- Using the Lines option with Infile
354

 
- Loading comma separated files
355

 
- Loading XML files
357

 
- Loading XML files using Ignore
359

 
- Loading XML file naming Columns
360

 
- Loading XML file using Set
361

 
- Exercise Eleven
362

Eighteen
MariaDB User Defined Processes
364

 
- Delimiter
367

Nineteen
Procedures
369

 
- Introduction
370

 
- Dropping
371

 
- Viewing
372

 
- Calling
375

 
- Syntax
376

 
- Body
377

 
- Variables
378

 
- Messaging
383

 
- Signal
385

 
- DML and DDL
389

 
- Passing Parameters
392

 
- Exercise Twelve
399

 
- Conditional Statements
403

 
- Exercise Thirteen
413

 
- Explicit Cursors
415

 
- Looping
422

 
- While Looping
423

 
- Loop Labels
425

 
- Repeat Looping
427

 
- Repeat Looping with leave
429

 
- Loop
430

 
- Loop with Iterate
431

 
- Looping Tables
432

 
- Exercise Fourteen
436

Twenty
Handler
441

 
- Introduction
442

 
- Handler Types
443

 
- MariaDB Error Code
444

 
- Error Types
449

 
- SQLState
450

 
- SQLWarning
452

 
- Not Found
453

 
- SQLException
454

 
- Condition Name
455

Twenty One
Functions
456

 
- Introduction
457

 
- Dropping
458

 
- Viewing
459

 
- Calling
462

 
- Syntax
464

 
- Parameters
466

 
- Functions without Bodies
467

 
- Exercise Fifteen
468

Twenty Two
Advanced Declaration
472

 
- Introduction
473

 
- Comment
474

 
- Deterministic
476

 
- SQL
478

 
- SQL Security
480

 
- Language
482

Twenty Three
Triggers
483

 
- Introduction
484

 
- Dropping
485

 
- Viewing
486

 
- Suggested Naming Conventions
489

 
- Basic Syntax
490

 
- Referencing Values
493

 
- Follows / Precedes
498

 
- Enabling / Disabling
501

 
- Problems
504

 
- Triggers on Views
507

Twenty Four
Miscellaneous MySQL
508

 
- Sleep
509

 
- Do
510

 
- Events
511

 
- Changing Database
523

 
- Creating a new Database
525

 
- Dropping a Database
526

 
- Software Version
527

 
- Exercise Sixteen
528