Home > Store

MySQL Crash Course, 2nd Edition

eBook (Watermarked)

  • Your Price: $30.39
  • List Price: $37.99
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Acrobat® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

Also available in other formats.

Register your product to gain access to bonus material or receive a coupon.

Description

  • Copyright 2024
  • Edition: 2nd
  • eBook (Watermarked)
  • ISBN-10: 0-13-822312-2
  • ISBN-13: 978-0-13-822312-0

MySQL is one of the most popular database management systems available, powering everything from Internet powerhouses to individual corporate databases to simple end-user applications, and everything in between. This book will teach you all you need to know to be immediately productive with the latest version of MySQL. By working through 30 highly focused hands-on lessons, your MySQL Crash Course will be both easier and more effective than you'd have thought possible.

Learn How To

  • Retrieve and Sort Data
  • Filter Data Using Comparisons, Regular Expressions, Full Text Search, and Much More
  • Join Relational Data
  • Create and Alter Tables
  • Insert, Update, and Delete Data
  • Leverage the Power of Stored Procedures and Triggers
  • Use Views and Cursors
  • Manage Transactional Processing
  • Create User Accounts and Manage Security via Access Control

Sample Content

Table of Contents

Chapter 1 Understanding SQL 1
Database Basics 1
What Is a Database? 2
Tables 2
Columns and Datatypes 3
Rows 4
Primary Keys 4
What Is SQL? 6
Try It Yourself 6
Summary 7
Chapter 2 Introducing MySQL 9
What Is MySQL? 9
Client/Server Software 9
MySQL Versions 10
MySQL Tools 11
mysql Command-Line Utility 11
MySQL Workbench 12
Other Tools 13
Summary 13
Chapter 3 Working with MySQL 15
Using the Command-Line Tool 15
Selecting a Database 16
Learning About Databases and Tables 17
Using MySQL Workbench 20
Getting Started 20
Using MySQL Workbench 21
Selecting a Database 22
Learning About Databases and Tables 22
Executing SQL Statements 23
Next Steps 23
Summary 24
Chapter 4 Retrieving Data 25
The SELECT Statement 25
Retrieving Individual Columns 25
Retrieving Multiple Columns 27
Retrieving All Columns 29
Retrieving Distinct Rows 29
Limiting Results 31
Using Fully Qualified Table Names 32
Using Comments 33
Summary 34
Challenges 34
Chapter 5 Sorting Retrieved Data 35
Sorting Data 35
Sorting by Multiple Columns 37
Sorting by Column Position 38
Specifying Sort Direction 39
Summary 41
Challenges 42
Chapter 6 Filtering Data 43
Using the WHERE Clause 43
WHERE Clause Operators 44
Checking Against a Single Value 45
Checking for Nonmatches 46
Checking for a Range of Values 47
Checking for No Value 48
Summary 49
Challenges 49
Chapter 7 Advanced Data Filtering 51
Combining WHERE Clauses 51
Using the AND Operator 51
Using the OR Operator 52
Understanding the Order of
Evaluation 53
Using the IN Operator 54
Using the NOT Operator 56
Summary 58
Challenges 58
Chapter 8 Using Wildcard Filtering 59
Using the LIKE Operator 59
The Percent Sign (%) Wildcard 60
The Underscore (_) Wildcard 61
Tips for Using Wildcards 63
Summary 63
Challenges 63
Chapter 9 Searching Using Regular Expressions 65
Understanding Regular Expressions 65
Using MySQL Regular Expressions 66
Basic Character Matching 66
Performing OR Matches 68
Matching One of Several Characters 68
Matching Ranges 70
Matching Special Characters 70
Matching Character Classes 72
Matching Multiple Instances 72
Anchors 74
Summary 75
Challenges 76
Chapter 10 Creating Calculated Fields 77
Understanding Calculated Fields 77
Concatenating Fields 78
Using Aliases 80
Performing Mathematical Calculations 81
Summary 83
Challenges 83
Chapter 11 Using Data Manipulation Functions 85
Understanding Functions 85
Using Functions 86
Text Manipulation Functions 86
Date and Time Manipulation Functions 88
Numeric Manipulation Functions 91
Summary 92
Challenges 92
Chapter 12 Summarizing Data 93
Using Aggregate Functions 93
The Avg() Function 94
The Count() Function 95
The Max() Function 96
The Min() Function 97
The Sum() Function 98
Aggregates on Distinct Values 99
Combining Aggregate Functions 100
Summary 101
Challenges 101
Chapter 13 Grouping Data 103
Understanding Data Grouping 103
Creating Groups 104
Filtering Groups 105
Grouping and Sorting 107
Combining Grouping and Data Summarization 109
SELECT Clause Ordering 110
Summary 110
Challenges 110
Chapter 14 Working with Subqueries 113
Understanding Subqueries 113
Filtering by Subquery 113
Using Subqueries As Calculated Fields 117
Summary 119
Challenges 119
Chapter 15 Joining Tables 121
Understanding Joins 121
Understanding Relational Tables 121
Why Use Joins? 122
Creating a Join 123
The Importance of the WHERE Clause 124
Inner Joins 127
Joining Multiple Tables 128
Summary 130
Challenges 130
Chapter 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 138
Using Joins and Join Conditions 139
Summary 140
Challenges 140
Chapter 17 Combining Queries 141
Understanding Combined Queries 141
Creating Combined Queries 141
Using UNION 141
UNION Rules 143
Including or Eliminating Duplicate Rows 144
Sorting Combined Query Results 145
Summary 146
Challenges 146
Chapter 18 Full-Text Searching 147
Understanding Full-Text Searching 147
Using Full-Text Searching 148
Performing Full-Text Searches 148
Using Query Expansion 151
Boolean Text Searches 153
Full-Text Searching Notes 156
Summary 157
Challenges 157
Chapter 19 Inserting Data 159
Understanding Data Insertion 159
Inserting Complete Rows 159
Inserting Multiple Rows 163
Inserting Retrieved Data 164
Summary 166
Challenges 166
Chapter 20 Updating and Deleting Data 167
Updating Data 167
Deleting Data 169
Guidelines for Updating and Deleting Data 170
Summary 171
Challenges 171
Chapter 21 Creating and Manipulating Tables 173
Creating Tables 173
Basic Table Creation 173
Working with NULL Values 175
Primary Keys Revisited 176
Using AUTO_INCREMENT 177
Specifying Default Values 178
Engine Types 179
Updating Tables 180
Deleting Tables 182
Renaming Tables 182
Summary 182
Challenges 182
Chapter 22 Using Views 183
Understanding Views 183
Why Use Views 184
View Rules and Restrictions 185
Using Views 185
Using Views to Simplify Complex Joins 185
Using Views to Reformat
Retrieved Data 186
Using Views to Filter Unwanted Data 188
Using Views with Calculated Fields 188
Updating Views 189
Summary 190
Challenges 190
Chapter 23 Working with Stored Procedures 191
Understanding Stored Procedures 191
Why Use Stored Procedures 192
Using Stored Procedures 193
Executing Stored Procedures 193
Creating Stored Procedures 193
The DELIMITER Challenge 194
Dropping Stored Procedures 195
Working with Parameters 195
Building Intelligent Stored Procedures 199
Inspecting Stored Procedures 201
Summary 202
Challenges 202
Chapter 24 Using Cursors 203
Understanding Cursors 203
Working with Cursors 204
Creating Cursors 204
Opening and Closing Cursors 205
Using Cursor Data 206
Summary 210
Chapter 25 Using Triggers 211
Understanding Triggers 211
Creating Triggers 212
Dropping Triggers 213
Using Triggers 213
INSERT Triggers 213
DELETE Triggers 214
UPDATE Triggers 215
More on Triggers 216
Summary 216
Chapter 26 Managing Transaction Processing 217
Understanding Transaction Processing 217
Controlling Transactions 219
Using ROLLBACK 219
Using COMMIT 220
Using Savepoints 220
Changing the Default Commit Behavior 221
Summary 222
Chapter 27 Globalization and Localization 223
Understanding Character Sets and Collation Sequences 223
Working with Character Sets and Collation Sequences 224
Summary 226
Chapter 28 Managing Security 227
Understanding Access Control 227
Managing Users 228
Creating User Accounts 229
Deleting User Accounts 230
Setting Access Rights 230
Changing Passwords 233
Summary 234
Chapter 29 Database Maintenance 235
Backing Up Data 235
Performing Database Maintenance 235
Diagnosing Startup Problems 237
Reviewing Log Files 237
Summary 238
Chapter 30 Improving Performance 239
Improving Performance 239
Summary 240
Appendix A Getting Started with MySQL 241
What You Need 241
Obtaining the Software 242
Installing the Software 242
Preparing to Read This Book 242
Appendix B The Example Tables 243
Understanding the Example Tables 243
Table Descriptions 244
The vendors Table 244
The products Table 244
The customers Table 245
The orders Table 245
The orderitems Table 246
The productnotes Table 246
Creating the Sample Tables 247
Using Data Import 247
Using SQL Scripts 248
Appendix C MySQL Statement Syntax 249
ALTER TABLE 249
COMMIT 249
CREATE INDEX 250
CREATE PROCEDURE 250
CREATE TABLE 250
CREATE USER 250
CREATE VIEW 251
DELETE 251
DROP 251
INSERT 251
INSERT SELECT 251
ROLLBACK 252
SAVEPOINT 252
SELECT 252
START TRANSACTION 252
UPDATE 252
Appendix D MySQL Datatypes 253
String Datatypes 253
Numeric Datatypes 255
Date and Time Datatypes 256
Binary Datatypes 256
Appendix E MySQL Reserved Words 257

9780138223021 TOC 10/2/2023

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020