Murach’s PHP and MySQL (4th Edition)
Murach’s MySQL (4th Edition)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!
Murach’s MySQL (3rd Edition)
There is a newer edition of this book!
If you’re an application developer, this is the MySQL book for you. That’s true whether you’ve never coded a SQL statement before or whether you’re coming to MySQL with a background in Oracle, SQL Server, or another flavor of SQL. It’s true whether you think that you know a lot about SQL, or whether you know that you know very little. It’s true whether you’re going to be your own database administrator (DBA) or whether a separate team handles that.
See for yourself why this book has been the go-to MySQL resource for developers ever since the 1st Edition was published in 2012.
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 classic features like summary queries, subqueries, data types, and functions, as well as with newer features like window functions and CTEs. These skills are 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 first learn how to design and normalize a database structure. With that background, you’ll see how to use MySQL Workbench to create and work with an EER (enhanced entity-relationship) model for your database.
Then, you’ll learn how to implement the database design by using the DDL (Data Definition Language) statements that are a part of SQL. Finally, you’ll learn how to use views in a database to simplify data access and improve data security.
When you’re done, you’ll be able to design and implement your own databases and views. 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 by assigning privileges to users and roles, 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's new in this edition
- We've updated all the content so it will work with MySQL 8.0 and beyond.
- We've added coverage of newer features that were introduced in MySQL 8.0, such as window functions, Common Table Expressions (CTE), and roles for database security, so you can take advantage of them.
- We've improved and added explanations and coding examples as needed, to make the book a better-than-ever reference for you.
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 Server 8.0 or higher
- MySQL Workbench 8.0 or higher
Both of these products can be downloaded for free from MySQL’s website. And appendixes A (for Windows) and B (for macOS) provide complete instructions for installing them.
MySQL is backwards compatible; in other words, new releases continue to implement most of the statements in earlier releases. That means that all of the SQL statements presented in this book should continue to work with future releases. It also means that most of the statements will work if you’re using an earlier release than MySQL 8.0, and we have done our best to identify any statements that won’t.
If you use MySQL Workbench 8.0, 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 release 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 Sanaulla, 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
"I love this book and refer back to it almost weekly."
- Posted at an online bookseller
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
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 page 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
How to use the GROUPING function
How to code aggregate window functions
How the aggregate window functions work
How to use frames
How to use named windows
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 cde 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
How to work with common table expressions
How to code a CTE
How to code a recursive CTE
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
How to use the regular expression functions
How to use the ranking functions
How to use the analytic 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 lock selected rows
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
How to use function characteristics
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 the event scheduler on or off
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 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 work with roles
How to create, manage, and drop roles
A script that creates users and roles
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 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 repair a MyISAM table
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 macOS
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
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 (1219Kb) Download Now
Chapter 3 PDF (933Kb) 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 of the 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 macOS – describe how to install and use these files.
Exe file for Windows (2.16Mb) Download Now
Zip file for any system (2.13Mb) Download Now
To view the "Frequently Asked Questions" for this book in a PDF, just click on this link: View the questions
Then, if you have any questions that aren't answered here, 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!