MariaDB Crash Course

Description

CONTENTS:

Introduction 1

What Is MariaDB Crash Course? 1

Who Is This Book For? 2

Companion Web Site 3

Conventions Used in This Book 3

1: Understanding SQL 5

Database Basics 5

     What Is a Database? 6

     Tables 6

     Columns and Datatypes 7

     Rows 8

     NULL 8

     Primary Keys 9

What Is SQL? 10

Try It Yourself 11

Summary 11

2: Introducing MariaDB 13

What Is MariaDB? 13

     Client-Server Software 14

     MySQL Compatibility 15

MariaDB Tools 16

    mysql Command Line 16

     MySQL Workbench 17

Summary 19

3: Working with MariaDB 21

Making the Connection 21

Selecting a Database 22

Learning About Databases and Tables 23

Summary 26

4: Retrieving Data 27

The SELECT Statement 27

Retrieving Individual Columns 27

Retrieving Multiple Columns 29

Retrieving All Columns 30

Retrieving Distinct Rows 31

Limiting Results 32

Using Fully Qualified Table Names 34

Using Comments 35

Summary 36

5: Sorting Retrieved Data 37

Sorting Data 37

Sorting by Multiple Columns 39

Specifying Sort Direction 40

Summary 43

6: Filtering Data 45

Using the WHERE Clause 45

The WHERE Clause Operators 46

     Checking Against a Single Value 47

     Checking for Nonmatches 48

     Checking for a Range of Values 49

     Checking for No Value 50

Summary 51

7: Advanced Data Filtering 53

Combining WHERE Clauses 53

     Using the AND Operator 53

     Using the OR Operator 54

     Understanding Order of Evaluation 55

Using the IN Operator 57

Using the NOT Operator 58

Summary 59

8: Using Wildcard Filtering 61

Using the LIKE Operator 61

     The Percent Sign (%) Wildcard 62

     The Underscore (_) Wildcard 64

Tips for Using Wildcards 65

Summary 65

9: Searching Using Regular Expressions 67

Understanding Regular Expressions 67

Using Regular Expressions 68

     Basic Character Matching 68

     Performing OR Matches 70

     Matching One of Several Characters 71

     Matching Ranges 72

     Matching Special Characters 73

     Matching Character Classes 75

     Matching Multiple Instances 75

     Anchors 77

Summary 79

10: Creating Calculated Fields 81

Understanding Calculated Fields 81

Concatenating Fields 82

     Using Aliases 84

Performing Mathematical Calculations 85

Summary 87

11: Using Data Manipulation Functions 89

Understanding Functions 89

Using Functions 90

     Text Manipulation Functions 90

     Date and Time Manipulation Functions 92

     Numeric Manipulation Functions 96

Summary 96

12: Summarizing Data 97

Using Aggregate Functions 97

     The AVG() Function 98

     The COUNT() Function 99

     The MAX() Function 100

     The MIN() Function 101

     The SUM() Function 102

Aggregates on Distinct Values 103

Combining Aggregate Functions 104

Summary 105

13: Grouping Data 107

Understanding Data Grouping 107

Creating Groups 108

Filtering Groups 109

Grouping and Sorting 112

SELECT Clause Ordering 113

Summary 114

14: Working with Subqueries 115

Understanding Subqueries 115

Filtering by Subquery 115

Using Subqueries as Calculated Fields 119

Summary 122

15: Joining Tables 123

Understanding Joins 123

     Understanding Relational Tables 123

     Why Use Joins? 125

Creating a Join 125

     The Importance of the WHERE Clause 127

     Inner Joins 129

     Joining Multiple Tables 130

Summary 132

16: Creating Advanced Joins 133

Using Table Aliases 133

Using Different Join Types 134

     Self Joins 134

     Natural Joins 136

     Outer Joins 137

Using Joins with Aggregate Functions 139

Using Joins and Join Conditions 140

Summary 140

17: Combining Queries 141

Understanding Combined Queries 141

Creating Combined Queries 141

     Using UNION 142

    UNION Rules 144

     Including or Eliminating Duplicate Rows 144

     Sorting Combined Query Results 145

Summary 146

18: Full-Text Searching 147

Understanding Full-Text Searching 147

Using Full-Text Searching 148

     Enabling Full-Text Searching Support 148

     Performing Full-Text Searches 149

     Using Query Expansion 152

     Boolean Text Searches 154

     Full-Text Search Usage Notes 158

Summary 159

19: Inserting Data 161

Understanding Data Insertion 161

Inserting Complete Rows 161

Inserting Multiple Rows 165

Inserting Retrieved Data 166

Summary 168

20: Updating and Deleting Data 169

Updating Data 169

Deleting Data 171

Guidelines for Updating and Deleting Data 172

Summary 173

21: Creating and Manipulating Tables 175

Creating Tables 175

     Basic Table Creation 176

     Working with NULL Values 177

     Primary Keys Revisited 179

     Using AUTO_INCREMENT 180

     Specifying Default Values 181

     Engine Types 182

Updating Tables 183

Deleting Tables 185

Renaming Tables 185

Summary 186

22: Using Views 187

Understanding Views 187

     Why Use Views 188

     View Rules and Restrictions 188

Using Views 189

     Using Views to Simplify Complex Joins 189

     Using Views to Reformat Retrieved Data 191

     Using Views to Filter Unwanted Data 192

     Using Views with Calculated Fields 193

     Updating Views 194

Summary 195

23: Working with Stored Procedures 197

Understanding Stored Procedures 197

Why Use Stored Procedures 198

Using Stored Procedures 199

     Executing Stored Procedures 199

     Creating Stored Procedures 200

     Dropping Stored Procedures 201

     Working with Parameters 202

     Building Intelligent Stored Procedures 205

     Inspecting Stored Procedures 208

Summary 208

24: Using Cursors 209

Understanding Cursors 209

Working with Cursors 209

     Creating Cursors 210

     Opening and Closing Cursors 210

     Using Cursor Data 212

Summary 216

25: Using Triggers 217

Understanding Triggers 217

Creating Triggers 218

Dropping Triggers 219

Using Triggers 219

    INSERT Triggers 219

    DELETE Triggers 221

    UPDATE Triggers 223

     More on Triggers 223

Summary 224

26: Managing Transaction Processing 225

Understanding Transaction Processing 225

Controlling Transactions 227

     Using ROLLBACK 227

     Using COMMIT 228

     Using Savepoints 229

  Changing the Default Commit Behavior 230

Summary 230

27: Globalization and Localization 231

Understanding Character Sets and Collation Sequences 231

Working with Character Set and Collation Sequences 232

Summary 234

28: Managing Security 235

Understanding Access Control 235

Managing Users 236

     Creating User Accounts 237

     Deleting User Accounts 238

     Setting Access Rights 238

     Changing Passwords 241

Summary 242

29: Database Maintenance 243

Backing Up Data 243

Performing Database Maintenance 243

Diagnosing Startup Problems 245

Review Log Files 245

Summary 246

30: Improving Performance 247

Improving Performance 247

Summary 249

A: Getting Started with MariaDB 251

What You Need 251

Obtaining the Software 252

Installing the Software 252

Preparing to Try It Yourself 253

B: The Example Tables 255

Understanding the Sample Tables 255

     Table Descriptions 256

Creating the Sample Tables 259

     Using mysql 260

     Using MySQL Workbench 261

C: MariaDB Datatypes 263

String Datatypes 263

Numeric Datatypes 265

Date and Time Datatypes 266

Binary Datatypes 266

D: MariaDB Reserved Words 269

Index 275