MariaDB Crash Course / Edition 1

Table of 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

Read More