A Guide to Mysql

by Pratt, Philip J.; Last, Mary Z.

• ISBN13: 9781418836351
• ISBN10: 1418836354
• Edition: 1st
• Format: Paperback
• Copyright: 2005-12-09
• Publisher: Cengage Learning

Summary

The MySQL® database has become the world's most popular open source database because of its consistent fast performance, high reliability, and ease of use. That’s why best-selling authors Phil Pratt and Mary Last are pleased to introduce their latest database textbook - A Guide to MySQL. This straightforward text utilizes three case databases – Premiere Products, Henry Books, and Alexamara Marina Group – to teach the concepts and techniques of using MySQL. Your students will gain the skills to build professional quality MySQL databases, thanks to this real-world approach. Additional hands-on exercises at the end of each chapter offer students the opportunity to problem-solve based on what they’ve previously learned.

Table of Contents

Preface	xi
Introduction to Premiere Products, Henry Books, and Alexamara Marina Group	1	(24)
What is a Database?	2	(1)
The Premiere Products Database	2	(6)
The Henry Books Database	8	(7)
The Alexamara Marina Group Database	15	(5)
MySQL	20	(1)
Chapter Summary	21	(1)
Key Terms	21	(1)
Exercises	21	(4)
Database Design Fundamentals	25	(36)
Database Concepts	26	(5)
Relational Databases	26	(2)
Entities, Attributes, and Relationships	28	(3)
Functional Dependence	31	(3)
Primary Keys	34	(2)
Database Design	36	(7)
Design Method	36	(2)
Database Design Requirements	38	(1)
Database Design Process Example	38	(5)
Normalization	43	(11)
First Normal Form	44	(1)
Second Normal Form	45	(4)
Third Normal Form	49	(5)
Diagrams for Database Design	54	(2)
Chapter Summary	56	(1)
Key Terms	57	(1)
Review Questions	57	(1)
Exercises	58	(3)
An Introduction to SQL	61	(32)
Introduction to MySQL	62	(4)
Starting MySQL	62	(1)
Obtaining Help in MySQL	63	(1)
Using the MySQL Reference Manual to Get Help	64	(2)
Creating a Database	66	(1)
Changing the Default Database	67	(1)
Creating a Table	67	(2)
Running SQL Commands	69	(3)
Editing SQL Commands	69	(2)
Dropping a Table	71	(1)
Data Types	72	(2)
Nulls	74	(1)
Implementation of Nulls	74	(1)
Adding Rows to a Table	75	(2)
The INSERT Command	75	(1)
The INSERT Command with Nulls	76	(1)
Viewing Table Data	77	(2)
Correcting Errors in the Database	79	(1)
Saving SQL Commands	80	(1)
Creating the Remaining Database Tables	81	(4)
Describing a Table	85	(1)
Chapter Summary	86	(1)
Key Terms	86	(1)
Review Questions	87	(1)
Exercises	87	(6)
Single-Table Queries	93	(36)
Constructing Simple Queries	94	(12)
Retrieving Certain Columns and All Rows	94	(1)
Retrieving All Columns and All Rows	95	(1)
Using a WHERE Clause	96	(2)
Using Compound Conditions	98	(3)
Using the BETWEEN Operator	101	(1)
Using Computed Columns	102	(2)
Using the LIKE Operator	104	(1)
Using the IN Operator	105	(1)
Sorting	106	(2)
Using the ORDER BY Clause	106	(1)
Additional Sorting Options	107	(1)
Using Functions	108	(5)
Using the COUNT Function	109	(1)
Using the SUM Function	109	(1)
Using the AVG, MAX, and MIN Functions	110	(1)
Using the DISTINCT Operator	111	(2)
Nesting Queries	113	(3)
Subqueries	114	(2)
Grouping	116	(5)
Using the GROUP BY Clause	116	(2)
Using a HAVING Clause	118	(1)
HAVING vs. WHERE	119	(2)
Nulls	121	(1)
Summary of SQL Clauses, Functions, and Operators	121	(2)
Chapter Summary	123	(1)
Key Terms	123	(1)
Review Questions	124	(1)
Exercises	125	(4)
Multiple-Table Queries	129	(32)
Querying Multiple Tables	130	(3)
Joining Two Tables	130	(3)
Comparing JOIN, IN, and EXISTS	133	(12)
Using the IN Operator	134	(1)
Using the EXISTS Operator	134	(2)
Using a Subquery Within a Subquery	136	(2)
A Comprehensive Example	138	(1)
Using an Alias	139	(1)
Joining a Table to Itself	140	(2)
Using a Self-Join on a Primary Key	142	(1)
Joining Several Tables	143	(2)
Set Operations	145	(5)
ALL and ANY	150	(2)
Special Operations	152	(4)
Inner Join	152	(1)
Outer Join	153	(1)
Product	154	(2)
Chapter Summary	156	(1)
Key Terms	156	(1)
Review Questions	157	(1)
Exercises	157	(4)
Updating Data	161	(20)
Creating a New Table from an Existing Table	162	(1)
Changing Existing Data in a Table	163	(2)
Adding New Rows to an Existing Table	165	(1)
Deleting Existing Rows from a Table	166	(1)
Changing a Value in a Column to Null	167	(1)
Changing a Table's Structure	168	(4)
Making Complex Changes	172	(1)
Commit and Rollback	172	(2)
Transactions	174	(1)
Dropping a Table	175	(1)
Chapter Summary	176	(1)
Key Terms	176	(1)
Review Questions	176	(1)
Exercises	177	(4)
Database Administration	181	(32)
Defining and Using Views	182	(6)
Using a View to Update Data	188	(4)
Updating Row-and-Column Subset Views	188	(1)
Updating Views Involving Joins	189	(3)
Updating Views Involving Statistics	192	(1)
Dropping a View	192	(1)
Security	193	(3)
Indexes	196	(4)
Creating an Index	199	(1)
Dropping an Index	200	(1)
Creating Unique Indexes	200	(1)
System Catalog	200	(3)
Integrity Constraints in SQL	203	(3)
Chapter Summary	206	(1)
Key Terms	206	(1)
Review Questions	207	(1)
Exercises	207	(6)
MySQL Special Topics	213	(36)
Importing and Exporting Data	214	(4)
Importing Data into a Database	214	(3)
Exporting Data from a Table	217	(1)
Database Performance Issues	218	(5)
Optimizing the Tables in a Database	219	(2)
Optimizing the Queries in a Database	221	(2)
Using the MySQL Query Browser	223	(13)
Starting and Using the Query Browser	224	(1)
Getting Help in the Query Browser	225	(1)
Using the Database Browser	226	(1)
Using the Syntax Browser	227	(1)
Creating and Executing Queries	228	(2)
Exporting a Resultset	230	(1)
Using the Script Editor	231	(1)
Using the MySQL Table Editor	232	(3)
Analyzing Query Performance	235	(1)
Using the MySQL Administrator	236	(6)
Starting the Administrator	236	(1)
Viewing the Administrator Window	237	(1)
Getting Help in the Administrator	237	(1)
Backing Up a Database	238	(1)
Restoring a Database	239	(1)
Maintaining a Database	240	(2)
Chapter Summary	242	(1)
Key Terms	242	(1)
Review Questions	243	(1)
Exercises	244	(5)
Appendix A SQL Reference	249	(24)
Aliases (Pages 139--143)	249	(1)
Alter Table (Pages 168--170, 200--202)	249	(1)
Analyze Table (Pages 218--219)	250	(1)
Check Table (Pages 219--220)	250	(1)
Column or Expression List (Select Clause) (Pages 94--96)	251	(1)
Computed Columns (Pages 102--104)	251	(1)
The DISTINCT Operator (Pages 111--112)	251	(1)
Functions (Pages 108--113)	252	(1)
Commit (Pages 172--174)	252	(1)
Conditions (Pages 96--102)	252	(2)
Simple Conditions (Pages 96--98)	252	(1)
Compound Conditions (Pages 98--100)	253	(1)
Between Conditions (Pages 101--102)	253	(1)
Like Conditions (Pages 104--105)	253	(1)
In Conditions (Pages 105--106, 114--116, 134)	253	(1)
Exists Conditions (Pages 134--136)	254	(1)
All and Any (Pages 150--152)	254	(1)
Create Database (Page 66)	254	(1)
Create Index (Pages 193--197)	255	(1)
Create Table (Pages 67--68)	255	(1)
Create View (Pages 180--190)	256	(1)
Data Types (Pages 72--73)	257	(1)
Delete (Rows) (Pages 79--80, 166)	258	(1)
Drop Index (Page 197)	258	(1)
Drop Table (Pages 71, 174)	259	(1)
Drop View (Page 190)	259	(1)
Explain (Pages 221--222, 235--236)	259	(1)
Grant (Pages 190--193)	260	(1)
Insert Into (Query) (Pages 162--163)	260	(1)
Insert Into (Values) (Pages 75--77, 165--166)	261	(1)
Integrity (Pages 200--202)	262	(1)
Load Data Infile (Pages 215--217)	262	(1)
Optimize Table (Pages 220--221)	263	(1)
Procedure Analyse()(Pages 222--223)	263	(1)
Repair Table (Page 220)	264	(1)
Revoke (Page 193)	264	(1)
Rollback (Pages 172--174)	265	(1)
Select (Pages 77--78, 94--122, 130--133)	265	(1)
Select Into Outfile (Pages 217--218)	266	(1)
Show Columns (Pages 85, 198--199)	267	(1)
Show Grants (Page 200)	267	(1)
Show Index (Pages 199--200)	267	(1)
Show Tables (Page 198)	268	(1)
Subqueries (Pages 113--116, 134--137)	268	(1)
Union, Intersect, and Minus (Pages 145--149)	268	(2)
Update (Pages 79--80, 163--165)	270	(1)
Use (Page 67)	271	(2)
Appendix B How Do I Reference	273	(4)
Appendix C Answers to Odd-Numbered Review Questions	277	(6)
Introduction to Premiere Products, Henry Books, and Alexamara Marina Group	277	(1)
Database Design Fundamentals	277	(1)
An Introduction to SQL	278	(1)
Single-Table Queries	279	(1)
Multiple-Table Queries	279	(1)
Updating Data	280	(1)
Database Administration	280	(1)
MySQL Special Topics	281	(2)
Index	283