MySQL : Essential Skills

An introductory look into the most popular open source database. Get step-by-step instruction from installing MySQL, manipulating it, and basic reporting, to advanced reporting, interfacing programs, and backups. An accessible introduction to a powerful database, featuring progress checks, exercises, and Ask the Experts sections in each chapter.

Dr. John W. Horn is a MySQL trainer at Interstate Software where he trains people like you everyday.

Michael Grey is an IT professional and writer who provides courseware development for Interstate Software, Inc. Michael Grey is an IT professional and writer who provides courseware development for Interstate Software, Inc.

ACKNOWLEDGMENTS

xiii

INTRODUCTION

xv

1 Installing MySQL

1

(38)

Critical Skill 1.1 Know Basic Database Terms

2

(1)

Critical Skill 1.2 Understand the Open Source and MySQL Relationship

3

(2)

Open Source vs. Proprietary Software

4

(1)

MySQL as Open Source

5

(1)

Critical Skill 1.3 Choose Your Development and Deployment Environments

5

(3)

Applying Broad Criteria

6

(1)

Narrowing the Decision

6

(2)

Critical Skill 1.4 Install MySQL on Windows

8

(17)

Project 1-1 Install MySQL on Windows and Start the MySQL Service

9

(10)

The WinMySQLAdmin Tool

15

(4)

Project 1-2 Download and Install MyODBC on Windows

19

(6)

Critical Skill 1.5 Install MySQL on Linux

25

(3)

Project 1-3 Download and Install MySQL on Linux

25

(3)

Critical Skill 1.6 Learn How to Use the Command Line

28

(7)

Using the Command Prompt in Windows

29

(3)

Using the Command Prompt in Linux

32

(3)

Critical Skill 1.7 Know Your Resources

35

(3)

Downloads

35

(1)

Documentation

36

(1)

Support & Consulting

37

(1)

Company

37

(1)

Module 1 Mastery Check

38

(1)

2 Defining a Database

39

(38)

Critical Skill 2.1 Understand MySQL Data Types

40

(13)

Number Data Types

41

(4)

Text Data Types

45

(2)

DATE and TIME Data Types

47

(4)

Defined Group Data Types: ENUM and SET

51

(2)

Critical Skill 2.2 Plan Your Database

53

(8)

Naming Your Database

53

(1)

Naming Your Tables

53

(1)

Naming and Defining Fields

54

(2)

Understanding Table Types

56

(5)

Critical Skill 2.3 Create Your Database and Tables

61

(6)

Creating a Database

62

(1)

Creating a Table

62

(1)

Correcting Mistakes in Commands

63

(1)

Formatting Table Data

64

(1)

Project 2-1 Create a Database and Tables with Data

64

(3)

Critical Skill 2.4 Insert Data into Tables

67

(9)

Project 2-2 Insert Data in Tables

69

(7)

Module 2 Mastery Check

76

(1)

3 Manipulating the Database

77

(42)

Critical Skill 3.1 Add Data to Your Database

78

(13)

Copying a Table

78

(2)

Inserting Data from a Text File

80

(1)

Project 3-1 Create Tables Using Existing Table Data

81

(10)

Critical Skill 3.2 Change a Table's Structure and Add an Index

91

(17)

Altering a Table

91

(1)

Creating an Index

92

(3)

Critical Skill 3.3 Change Table Data

95

(1)

Replacing Data

95

(2)

Using the safe-updates Option

97

(1)

Updating Data

97

(2)

Project 3-2 Make Changes to a Database

99

(9)

Critical Skill 3.4 Remove a Table or Records

108

(9)

Dropping a Table

108

(1)

Deleting Table Contents

108

(1)

Truncating Tables

109

(1)

Project 3-3 Remove Data and Revisit Changing Data

110

(7)

Module 3 Mastery Check

117

(2)

4 Basic Reporting

119

(22)

Critical Skill 4.1 Shape the Data from Your Tables to Fit Your Needs

120

(6)

Testing with the SELECT Command

120

(3)

Shaping Data with the SELECT Command

123

(3)

Critical Skill 4.2 Refine Your Data Selection

126

(12)

Focusing on Smaller Areas of Your Database

126

(1)

Using WHERE to Narrow Your Focus

126

(3)

Project 4-1 Create Reports Using SELECT...WHERE to Refine Data

129

(9)

Module 4 Mastery Check

138

(3)

5 Advanced Reporting

141

(50)

Critical Skill 5.1 Use Functions in MySQL

142

(34)

Using Universal Functions

142

(3)

Using Control Flow Functions

145

(2)

Using Aggregate Functions

147

(2)

Calculating with Mathematical Functions

149

(6)

Manipulating Strings with String Functions

155

(9)

Using Date Functions

164

(3)

Getting Information with Null-related Functions

167

(2)

Project 5-1 Use MySQL Functions to Create Reports

169

(7)

Critical Skill 5.2 Order and Group Data

176

(7)

Using the GROUP BY and ORDER BY Clauses

176

(1)

Using Grouping Functions

177

(2)

Screening Results with HAVING

179

(2)

Project 5-2 Use ORDER BY and GROUP BY

181

(2)

Critical Skill 5.3 Join Data in Multiple Tables

183

(7)

Understanding Cartesian Product vs. Simple Join Operations

184

(1)

Deciding Whether to Use LEFT JOIN or RIGHT JOIN

185

(2)

Project 5-3 Combine Table Data with JOIN

187

(3)

Module 5 Mastery Check

190

(1)

6 GUIs for Data Handling and Administration

191

(50)

Critical Skill 6.1 Use MySQL Administrator

192

(30)

Choosing MySQL Administrator Menu Options

193

(1)

Setting MySQL Administrator Options

194

(3)

Viewing Information in MySQL Administrator

197

(20)

Project 6-1 Download and Install MySQL Administrator on Windows

217

(4)

Project 6-2 Download and Install MySQL Administrator on Linux

221

(1)

Critical Skill 6.2 Use MySQL Control Center

222

(18)

Choosing MySQLCC Menu Options

223

(1)

Selecting MySQLCC Options

224

(1)

Using the Console Manager

224

(10)

Project 6-3 Download and Install MySQL Control Center on Windows

234

(5)

Project 6-4 Download and Install MySQL Control Center on Linux

239

(1)

Module 6 Mastery Check

240

(1)

7 Interfacing with Programs

241

(36)

Critical Skill 7.1 Use PHP and MySQL

242

(23)

Introducing PHP

242

(1)

Project 7-1 Create a Web Interface to Accept and Display Information

243

(11)

Connecting to a Database

245

(9)

Project 7-2 Create a Web Interface for a Database

254

(16)

Handling Errors

256

(2)

Good Design Concepts

258

(1)

Maintaining Web Security

259

(4)

Using the MySQL Improved interface (PHP 5 only)

263

(2)

Critical Skill 7.2 Separate Database Functions from Application Code

265

(5)

Installing PEAR::DB

266

(1)

Connecting to a Database

266

(1)

Executing Queries

267

(2)

Fetching Results

269

(1)

Critical Skill 7.3 Import MySQL Data into Access and Excel

270

(5)

Importing MySQL Data into Excel

271

(2)

Importing MySQL Data into Access

273

(1)

Project 7-3 Import and Link MySQL Data in Access

273

(2)

Module 7 Mastery Check

275

(2)

8 Basic Administration and Backups

277

(36)

Critical Skill 8.1 Set Privileges

278

(14)

Understanding the Levels of Security

278

(3)

Granting and Revoking User Privileges

281

(8)

Project 8-1 Set Privileges

289

(3)

Critical Skill 8.2 Make Backups

292

(9)

Reviewing the Causes of System Failure

292

(1)

Planning and Preparing for Backup

293

(1)

Understanding the Backup Methods

294

(5)

Project 8-2 Back Up a Database

299

(2)

Critical Skill 8.3 Recover from a Disaster

301

(10)

Knowing the Restore Procedure

301

(1)

Dealing with Damaged Tables

302

(4)

Performing Preventative Maintenance: A Stitch in Time

306

(1)

Project 8-3 Simulate Damage and Restore a Table

307

(26)

Project Summary

311

(1)

Module 8 Mastery Check

311

(2)

A Answers to Mastery Checks

313

(12)

Module 1: Installing MySQL

314

(1)

Module 2: Defining a Database

315

(1)

Module 3: Manipulating a Database

316

(2)

Module 4: Basic Reporting

318

(2)

Module 5: Advanced Reporting

320

(1)

Module 6: GUI Interfaces for Data Handling and Administration

321

(1)

Module 7: Interfacing with Programs

322

(1)

Module 8: Basic Administration and Backups

323

(2)

B Reserved Words

325

(4)

C PHP Installation and Basic Syntax

329

(24)

PHP and MySQL

330

(1)

PHP Setup

331

(1)

Checking for PHP Activation

331

(1)

Verifying PHP Installation

331

(1)

PHP in HTML

332

(1)

PHP Syntax

333

(20)

Variables

334

(4)

Operators

338

(3)

Functions

341

(1)

Control Structures

342

(3)

Project C-1 Displaying and Inserting Data in MySQL

345

(8)

Index

353

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

MySQL : Essential Skills

Summary

Author Biography

Table of Contents

Supplemental Materials