My Cart (0)

Customer Service 1-800-221-5528

Murach’s MySQL (2nd Edition)

by Joel Murach
19 chapters, 590 pages, 247 figures
Published February 2015
ISBN 978-1-890774-82-0
Print: $54.50
eBook: $49.50
Print + eBook: $67.00

*** Now available: 3rd Edition covering MySQL 8 ***

Today, MySQL is the world’s most popular open-source database, and this book will help you master it. As you would expect, this book shows how to code all of the essential SQL statements for working with a MySQL database. But beyond that, this book shows how to design and implement a database and how to take advantage of relatively new features like foreign keys, transactions, and stored procedures. It even has a section on database administrator (DBA) skills.

College Instructors

Go to our instructor’s site to learn more about this book and its instructor’s materials.

 

I was amazed at how much information was packed into this book. The style of the book made it really easy to read and understand the information.... I learned a lot of new MySQL ideas reading this book, and I will be using it frequently as a reference."

Paul Turpin Southeastern Inter-Relational Database Users Group

  • About this Book
  • Table of Contents
  • FREE Downloads
  • Book FAQs
  • Corrections
  • Reviews

What you’ll learn in this book

Section 1: An introduction to MySQL

In this section, you’ll learn the concepts and terms for working with any database. You’ll learn how to use MySQL Workbench to work with a database and run SQL statements. And you’ll learn the basic SQL skills for retrieving data from a database and for adding, updating, and deleting data. When you complete this section, you’ll be able to continue with any of the other sections in the book.

Section 2: More SQL skills as you need them

This section presents the advanced skills for retrieving data from a database. That includes working with summary queries, subqueries, data types, and functions. These skills are also sure to raise your expertise, even if you already have SQL experience. And you can read these chapters in whatever sequence you prefer.

Section 3: Database design and implementation

In this section, you’ll learn how to use MySQL Workbench to create an EER (enhanced entity-relationship) model for your database. Then, you’ll learn how to implement that design by using the DDL (Data Definition Language) statements that are a part of SQL. When you’re done, you’ll be able to design and implement your own databases, and you’ll have a new perspective on databases that will make you a better SQL programmer, even if you never have to design a database.

Section 4: Stored program development

In this section, you’ll learn how to use MySQL to create stored procedures, functions, triggers, and events. In addition, you’ll learn how to manage transactions and locking. These features let you create stored programs made up of multiple SQL statements that can be stored in the database and accessed as needed, either to run on their own or to use in application programs…a great productivity booster!

Section 5: Database administration

In this section, you’ll learn a starting set of skills for becoming a database administrator (DBA). These skills include how to secure a database, how to back up a database, and how to restore a database.

Who this book is for

Application developers

I think it’s fair to say that most developers don’t know enough about SQL and the database management system they’re using. As one customer put it, "I know just enough about SQL to be a little bit dangerous!"

That’s why developers often code SQL statements that don’t perform as efficiently as they ought to. That’s why they aren’t able to code some of the queries that they need for their applications. And that’s why they don’t take advantage of all the features that MySQL has to offer.

That’s also why our MySQL book should be required reading for every application programmer who uses MySQL. It shows you how to code the SQL statements that you need for your applications. It shows you how to code these statements so they run efficiently. And it shows you how to take advantage of the most useful advanced features that MySQL has to offer.

Anyone who wants to become a database administrator

This book is also the right first book for anyone who wants to become a database administrator. Although this book doesn’t present all of the advanced skills that are needed by a DBA, it will get you started. Once you’ve finished it, you’ll be prepared for more advanced books on the subject.

Anyone who wants to learn standard SQL

This book is also a good choice for anyone who wants to learn standard SQL. Since SQL is a standard language for accessing database data, most of the SQL code in this book will work with any database management system. As a result, once you use this book to learn how to use SQL to work with a MySQL database, you can transfer most of what you have learned to another DBMS such as Oracle, DB2, or Microsoft SQL Server.

What the prerequisites are

Although you will progress through this book more quickly if you have some development experience, everything you need to know about databases and SQL is presented in this book. As a result, you don’t need to have any programming background to use this book.

What software you need

Although you should be able to use this book with most versions of MySQL, we recommend that you use:

  • MySQL Community Edition 5.6 or higher
  • MySQL Workbench 6.2 or higher

Both of these products can be downloaded for free from MySQL’s website. And appendixes A (for Windows) and B (for Mac OS X) provide complete instructions for installing them.

Since MySQL is backwards compatible, all of the SQL statements presented in this book should also work with future versions of MySQL. In addition, most statements presented in this book work with earlier versions of MySQL, and we have done our best to identify any statements that don’t.

If you use MySQL Workbench 6.2, all of the skills presented in this book should work exactly as described. However, MySQL Workbench is being actively developed, so its functionality is improving all the time. As a result, you may want to use a later version of MySQL Workbench. If you do, the skills presented in this book may not work exactly as described, but they’ll be similar enough that you shouldn’t have any trouble with them.

What people say about this book

"A very solid book with plenty of breadth and lots of examples. As a developer with almost 10 years of MySQL experience, I still picked up a lot of new detail on things I thought I knew."
- David Bolton, C/C++/C# Guide, About.com

"I was amazed at how much information was packed into this book. The style of the book made it really easy to read and understand the information.... I learned a lot of new MySQL ideas reading this book, and I will be using it frequently as a reference."
- Paul Turpin Southeastern Inter-Relational Database Users Group

"If you ever want to learn to use MySQL, write SQL queries, create database elements, then this is the book to pick up. Rating: 10 Horseshoes."
- Review by Mohamed Sanualla, JavaRanch.com

"I’ve found that many technical books go into laborious details that make them difficult to read, let alone use, but Murach’s MySQL has already proven to be helpful in solving several challenges I’ve encountered on my current MySQL project.... One thing I enjoyed is that it’s well-indexed, and the material itself is concise, with stand-alone, real-world examples.... It’s not theoretical, it’s practical, and presents topics in a friendly style that can be consumed painlessly, a few chapters at a time.”
- Posted at an online bookseller

“Beautifully written, and encyclopedic. Concepts are presented on facing pages with explanation on left and examples/visuals on right. I’m constantly referring to it - and find it easy to use as a quick reference.”
- Posted at an online bookseller

"I found this to be a great introductory book to MySQL.... The examples make it easy to quickly see the differences between the database system you have been working on and how to do the same thing in MySQL. Because of this book, I know that I could easily transition my skills in developing, managing, and designing an Oracle database application to a MySQL database system."
- Eric "Morty" Mortensen, Northeast Ohio Oracle Users Group

View the table of contents for this book in a PDF: Table of Contents (PDF)

Click on any chapter title to display or hide its content.

Section 1 An introduction to MySQL

Chapter 1 An introduction to relational databases and SQL

An introduction to client/server systems

The hardware components of a client/server system

The software components of a client/server system

Other client/server architectures

An introduction to the relational database model

How a table is organized

How tables are related

How columns are defined

How to read a database diagram

An introduction to SQL and SQL-based systems

A brief history of SQL

A comparison of Oracle, DB2, Microsoft SQL Server, and MySQL

The SQL statements

An introduction to the SQL statements

How to work with database objects

How to query a single table

How to join data from two or more tables

How to add, update, and delete data in a table

SQL coding guidelines

How to use SQL from an application program

Common options for accessing MySQL data

PHP code that retrieves data from MySQL

Java code that retrieves data from MySQL

Chapter 2 How to use MySQL Workbench and other development tools

An introduction to MySQL Workbench

The Home tab of MySQL Workbench

How to open a database connection

How to start and stop the database server

How to navigate through the database objects

How to view and edit the data for a table

How to view and edit the column definitions for a table

How to use MySQL Workbench to run SQL statements

How to enter and execute a SQL statement

How to use snippets

How to handle syntax errors

How to open and save SQL scripts

How to enter and execute SQL scripts

How to use the MySQL Reference Manual

How to view the manual

How to look up information

How to use the MySQL Command Line Client

How to start and stop the MySQL Command Line Client

How to use the MySQL Command Line Client to work with a database

Chapter 3 How to retrieve data from a single table

An introduction to the SELECT statement

The basic syntax of the SELECT statement

SELECT statement examples

How to code the SELECT clause

How to code column specifications

How to name the columns in a result set using aliases

How to code arithmetic expressions

How to use the CONCAT function to join strings

How to use functions with strings, dates, and numbers

How to test expressions by coding statements without FROM clauses

How to eliminate duplicate rows

How to code the WHERE clause

How to use the comparison operators

How to use the AND, OR, and NOT logical operators

How to use the IN operator

How to use the BETWEEN operator

How to use the LIKE and REGEXP operators

How to use the IS NULL clause

How to code the ORDER BY clause

How to sort by a column name

How to sort by an alias, expression, or column number

How to code the LIMIT clause

How to limit the number of rows

How to return a range of rows

Chapter 4 How to retrieve data from two or more tables

How to work with inner joins

How to code an inner join

How to use table aliases

How to join to a table in another database

How to use compound join conditions

How to use a self-join

How to join more than two tables

How to use the implicit inner join syntax

How to work with outer joins

How to code an outer join

Outer join examples

Other skills for working with joins

How to join tables with the USING keyword

How to join tables with the NATURAL keyword

How to use cross joins

How to work with unions

How to code a union

A union that combines result sets from different tables

A union that combines result sets from the same tables

A union that simulates a full outer join

Chapter 5 How to insert, update, and delete data

How to create test tables

How to create the tables for this book

How to create a copy of a table

How to insert new rows

How to insert a single row

How to insert multiple rows

How to insert default values and null values

How to use a subquery in an INSERT statement

How to update existing rows

How to update rows

How to use a subquery in an UPDATE statement

How to delete existing rows

How to delete rows

How to use a subquery in a DELETE statement

Section 2 More SQL skills as you need them

Chapter 6 How to code summary queries

How to work with aggregate functions

How to code aggregate functions

Queries that use aggregate functions

How to group and summarize data

How to code the GROUP BY and HAVING clauses

Queries that use the GROUP BY and HAVING clauses

How the HAVING clause compares to the WHERE clause

How to code compound search conditions

How to use the WITH ROLLUP operator

Chapter 7  How to code subqueries

An introduction to subqueries

Where to code subqueries

When to use subqueries

How to code subqueries in the WHERE clause

How to use the IN operator

How to use the comparison operators

How to use the ALL keyword

How to use the ANY and SOME keywords

How to code correlated subqueries

How to use the EXISTS operator

How to code subqueries in other clauses

How to code subqueries in the HAVING clause

How to code subqueries in the SELECT clause

How to code subqueries in the FROM clause

How to work with complex queries

A complex query that uses subqueries

A procedure for building complex queries

Chapter 8  How to work with data types

The data types

Overview

The character types

The integer types

The fixed-point and floating-point types

The date and time types

The ENUM and SET types

The large object types

How to convert data

How implicit data conversion works

How to convert data using the CAST and CONVERT functions

How to convert data using the FORMAT and CHAR functions

Chapter 9 How to use functions

How to work with string data

A summary of the string functions

Examples that use string functions

How to sort by a string column that contains numbers

How to parse a string

How to work with numeric data

How to use the numeric functions

How to search for floating-point numbers

How to work with date/time data

How to get the current date and time

How to parse dates and times with date/time functions

How to parse dates and times with the EXTRACT function

How to format dates and times

How to perform calculations on dates and times

How to search for a date

How to search for a time

Other functions you should know about

How to use the CASE function

How to use the IF, IFNULL, and COALESCE functions

Section 3 Database design and implementation

Chapter 10 How to design a database

How to design a data structure

The basic steps for designing a data structure

How to identify the data elements

How to subdivide the data elements

How to identify the tables and assign columns

How to identify the primary and foreign keys

How to enforce the relationships between tables

How normalization works

How to identify the columns to be indexed

How to normalize a data structure

The seven normal forms

How to apply the first normal form

How to apply the second normal form

How to apply the third normal form

When and how to denormalize a data structure

How to use MySQL Workbench for database design

How to open an existing EER model

How to create a new EER model

How to work with an EER model

How to work with an EER diagram

Chapter 11 How to create databases, tables, and indexes

How to work with databases

How to create and drop a database

How to select a database

How to work with tables

How to create a table

How to code a primary key constraint

How to code a foreign key constraint

How to alter the columns of a table

How to alter the constraints of a table

How to rename, truncate, and drop a table

How to work with indexes

How to create an index

How to drop an index

A script that creates a database

How to use MySQL Workbench

How to work with the columns of a table

How to work with the indexes of a table

How to work with the foreign keys of a table

How to work with character sets and collations

An introduction to character sets and collations

How to view character sets and collations

How to specify a character set and a collation

How to work with storage engines

An introduction to storage engines

How to view storage engines

How to specify a storage engine

Chapter 12 How to create views

An introduction to views

How views work

Benefits of using views

How to work with views

How to create a view

How to create an updatable view

How to use the WITH CHECK OPTION clause

How to insert or delete rows through a view

How to alter or drop a view

Section 4 Stored program development

Chapter 13 Language skills for writing stored programs

An introduction to stored programs

Four types of stored programs

A script that creates and calls a stored procedure

A summary of statements for coding stored programs

How to write procedural code

How to display data

How to declare and set variables

How to code IF statements

How to code CASE statements

How to code loops

How to use a cursor

How to declare a condition handler

How to use a condition handler

How to use multiple condition handlers

Chapter 14 How to use transactions and locking

How to work with transactions

How to commit and rollback transactions

How to work with save points

How to work with concurrency and locking

How concurrency and locking are related

The four concurrency problems that locks can prevent

How to set the transaction isolation level

How to prevent deadlocks

Chapter 15 How to create stored procedures and functions

How to code stored procedures

How to create and call a stored procedure

How to code input and output parameters

How to set a default value for a parameter

How to validate parameters and raise errors

A stored procedure that inserts a row

How to work with user variables

How to work with dynamic SQL

How to drop a stored procedure

How to code stored functions

How to create and call a function

A function that calculates balance due

How to drop a function

How to use Workbench with procedures and functions

How to view and edit stored routines

How to create stored routines

How to drop stored routines

Chapter 16 How to create triggers and events

How to work with triggers

How to create a BEFORE trigger

How to use a trigger to enforce data consistency

How to create an AFTER trigger

How to view or drop triggers

How to work with events

How to turn on the event scheduler

How to create an event

How to view, alter, or drop events

Section 5 Database administration

Chapter 17 An introduction to database administration

Database administration concepts

Database administrator responsibilities

Types of database files

Types of log files

How to monitor the server

How to view the server status

How to view and kill processes

How to view the status variables

How to view the system variables

How to configure the server

How to set system variables using MySQL Workbench

How to set system variables using a text editor

How to set system variables using the SET statement

How to work with logging

How to enable and disable logging

How to configure logging

How to view text-based logs

How to view the binary log

How to manage logs

Chapter 18 How to secure a database

An introduction to user accounts

An introduction to SQL statements for user accounts

A summary of privileges

The four privilege levels

The grant tables in the mysql database

How to work with users and privileges

How to create, rename, and drop users

How to specify user account names

How to grant privileges

How to view privileges

How to revoke privileges

How to change passwords

A script that creates users

How to use MySQL Workbench

How to work with users and privileges

How to connect as a user for testing

Chapter 19 How to backup and restore a database

Strategies for backing up and restoring a database

A backup strategy

A restore strategy

How to back up a database

How use mysqldump to back up a database

A SQL script file for a database backup

How to set advanced options for a database backup

How to restore a database

How to use a SQL script file to restore a full backup

How to execute statements in the binary log

How to view and edit statements in the binary log

How to import and export data

How to export data to a file

How to import data from a file

How to check and repair tables

How to use the CHECK TABLE statement

How to use the REPAIR TABLE statement

How to repair an InnoDB table

How to use the mysqlcheck program

How to use the myisamchk program

Appendixes

Appendix A How to install the software for this book on Windows

How to install the software from mysql.com

How to install the MySQL Community Server

How to install MySQL Workbench

How to install the software from murach.com

How to install the source files for this book

How to create the databases for this book

How to restore the databases

Appendix B How to install the software for this book on Mac OS X

How to install the software from mysql.com

How to install the MySQL Community Server

How to install MySQL Workbench

How to install the software from murach.com

How to install the source files for this book

How to create the databases for this book

How to restore the databases

How to update the password for the root user

Sample chapters

Chapter 2 : How to use MySQL Workbench and other development tools

MySQL Workbench is a graphical user interface that makes it easy to work with MySQL database objects and to build and run SQL statements. This tutorial chapter shows you how to use it.

Chapter 3: How to retrieve data from a single table

This chapter shows you how to use SQL SELECT statements to retrieve data from a single table. Our hope is that this will give you a better idea of how much there is to learn about SQL querying...and how well our MySQL book works.

Chapter 2 PDF (919Kb) Download Now

Chapter 3 PDF (608Kb) Download Now

The book examples and exercises

This download includes:

  • A script that creates the databases used by this book
  • The source code for all the SQL examples in the book
  • Solutions to the exercises in the book so you can check your work

The two appendixes at the end of the book – one for Windows and one for Mac OS X – describe how to install and use these files.

Exe file for Windows (1Mb) Download Now

Zip file for any system (0.9Mb) Download Now

On this page, we’ll be posting answers to the questions that come up most often about this book. So if you have any questions that you haven’t found answered here at our site, please email us. Thanks!

To view the corrections for this book in a PDF, just click on this link: View the corrections

Then, if you find any other errors, please email us so we can correct them in the next printing of the book. Thank you!

There are no reviews for this product yet.

To leave a review, please log in to your account.     Log In Here

Our Ironclad Guarantee

You must be satisfied. Try our print books for 30 days or our eBooks for 14 days. If they aren't the best you've ever used, you can return the books or cancel the eBooks for a prompt refund. No questions asked!

Contact Murach Books

For orders and customer service:

1-800-221-5528

Weekdays, 8 to 4 Pacific Time

College Instructors

If you're a college instructor who would like to consider a book for a course, please visit our website for instructors to learn how to get a complimentary review copy and the full set of instructional materials.