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

Content preview from MySQL® Crash Course

Chapter 8. Using Wildcard Filtering

In this chapter, you’ll learn what wildcards are, how they are used, and how to perform wildcard searches using the LIKE operator for sophisticated filtering of retrieved data.

Using the `LIKE` Operator

All the previous operators we studied filter against known values. Be it matching one or more values, testing for greater-than or less-than known values, or checking a range of values, the common denominator is that the values used in the filtering are known. But filtering data that way does not always work. For example, how could you search for all products that contained the text anvil within the product name? That cannot be done with simple comparison operators; that’s a job for wildcard searching. Using wildcards, ...