book

MySQL® Crash Course

by Ben Forta

December 2005

Intermediate to advanced

336 pages

4h 52m

English

Sams

Read now

Unlock full access

Who Is This Book For?Companion WebsiteConventions Used in This Book
Database BasicsWhat Is a Database?TablesColumns and DatatypesRowsPrimary KeysWhat Is SQL?Try It YourselfSummary
What Is MySQL?Client-Server SoftwareMySQL VersionsMySQL Toolsmysql Command-Line UtilityMySQL AdministratorMySQL Query BrowserSummary
Making the ConnectionSelecting a DatabaseLearning About Databases and TablesSummary
The SELECT StatementRetrieving Individual ColumnsRetrieving Multiple ColumnsRetrieving All ColumnsRetrieving Distinct RowsLimiting ResultsUsing Fully Qualified Table NamesSummary
Sorting DataSorting by Multiple ColumnsSpecifying Sort DirectionSummary

Using the WHERE ClauseThe WHERE Clause OperatorsChecking Against a Single ValueChecking for NonmatchesChecking for a Range of ValuesChecking for No ValueSummary
Combining WHERE ClausesUsing the AND OperatorUsing the OR OperatorUnderstanding Order of EvaluationUsing the IN OperatorUsing the NOT OperatorSummary
Using the LIKE OperatorThe Percent Sign (%) WildcardThe Underscore (_) WildcardTips for Using WildcardsSummary
Understanding Regular ExpressionsUsing MySQL Regular ExpressionsBasic Character MatchingPerforming OR MatchesMatching One of Several CharactersMatching RangesMatching Special CharactersMatching Character ClassesMatching Multiple InstancesAnchorsSummary
Understanding Calculated FieldsConcatenating FieldsUsing AliasesPerforming Mathematical CalculationsSummary
Understanding FunctionsUsing FunctionsText Manipulation FunctionsDate and Time Manipulation FunctionsNumeric Manipulation FunctionsSummary
Using Aggregate FunctionsThe AVG() FunctionThe COUNT() FunctionThe MAX() FunctionThe MIN() FunctionThe SUM() FunctionAggregates on Distinct ValuesCombining Aggregate FunctionsSummary
Understanding Data GroupingCreating GroupsFiltering GroupsGrouping and SortingSELECT Clause OrderingSummary
Understanding SubqueriesFiltering by SubqueryUsing Subqueries As Calculated FieldsSummary
Understanding JoinsUnderstanding Relational TablesWhy Use Joins?Creating a JoinThe Importance of the WHERE ClauseInner JoinsJoining Multiple TablesSummary
Using Table AliasesUsing Different Join TypesSelf JoinsNatural JoinsOuter JoinsUsing Joins with Aggregate FunctionsUsing Joins and Join ConditionsSummary
Understanding Combined QueriesCreating Combined QueriesUsing UNIONUNION RulesIncluding or Eliminating Duplicate RowsSorting Combined Query ResultsSummary
Understanding Full-Text SearchingUsing Full-Text SearchingEnabling Full-Text Searching SupportPerforming Full-Text SearchesUsing Query ExpansionBoolean Text SearchesFull-Text Search Usage NotesSummary
Understanding Data InsertionInserting Complete RowsInserting Multiple RowsInserting Retrieved DataSummary
Updating DataDeleting DataGuidelines for Updating and Deleting DataSummary
Creating TablesBasic Table CreationWorking with NULL ValuesPrimary Keys RevisitedUsing AUTO_INCREMENTSpecifying Default ValuesEngine TypesUpdating TablesDeleting TablesRenaming TablesSummary
Understanding ViewsWhy Use ViewsView Rules and RestrictionsUsing ViewsUsing Views to Simplify Complex JoinsUsing Views to Reformat Retrieved DataUsing Views to Filter Unwanted DataUsing Views with Calculated FieldsUpdating ViewsSummary
Understanding Stored ProceduresWhy Use Stored ProceduresUsing Stored ProceduresExecuting Stored ProceduresCreating Stored ProceduresDropping Stored ProceduresWorking with ParametersBuilding Intelligent Stored ProceduresInspecting Stored ProceduresSummary
Understanding CursorsWorking with CursorsCreating CursorsOpening and Closing CursorsUsing Cursor DataSummary
Understanding TriggersCreating TriggersDropping TriggersUsing TriggersINSERT TriggersDELETE TriggersUPDATE TriggersMore on TriggersSummary
Understanding Transaction ProcessingControlling TransactionsUsing ROLLBACKUsing COMMITUsing SavepointsChanging the Default Commit BehaviorSummary
Understanding Character Sets and Collation SequencesWorking with Character Set and Collation SequencesSummary
Understanding Access ControlManaging UsersCreating User AccountsDeleting User AccountsSetting Access RightsChanging PasswordsSummary
Backing Up DataPerforming Database MaintenanceDiagnosing Startup ProblemsReview Log FilesSummary
Improving PerformanceSummary
What You’ll NeedObtaining the SoftwareInstalling the SoftwarePreparing for Your Chapters
Understanding the Sample TablesTable DescriptionsThe vendors TableThe products TableThe customers TableThe orders TableThe orderitems TableThe productnotes TableCreating the Sample Tables
ALTER TABLECOMMITCREATE INDEXCREATE PROCEDURECREATE TABLECREATE USERCREATE VIEWDELETEDROPINSERTINSERT SELECTROLLBACKSAVEPOINTSELECTSTART TRANSACTIONUPDATE
String DatatypesNumeric DatatypesDate and Time DatatypesBinary Datatypes

Overview

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. And this book will teach you all you need to know to be immediately productive with 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

Ben Forta is Macromedia's Senior Technical Evangelist, and has almost 20 years of experience in the computer industry in product development, support, training, and product marketing. Ben is the author of the best-selling Sams Teach Yourself SQL in 10 Minutes (now in its third edition, and translated into over a dozen languages), ColdFusion Web Application Construction Kit, and Advanced ColdFusion Development (both published by Que Publishing), Sams Teach Yourself Regular Expressions in 10 Minutes, as well as books on SQL, Flash, JSP, HomeSite, WAP, Windows 2000, and other subjects.