MySQL Cookbook / Paul DuBois.

By: DuBois, Paul, 1956-Material type: Text

TextLanguage: English Publication details: New Delhi : Shroff publishers & Distributors Pvt. Ltd., O'Reilly, c2003. Edition: 1st edDescription: xxvii, 992 p. ; 24 cmISBN: 0596001452; 8173665648; 9780596001452Subject(s): SQL (Computer program language)DDC classification: 005.13 LOC classification: QA76.73.S67 | D587 2003Online resources: Contributor biographical information | Publisher description | Table of contents only | WorldCat details | E-Book Fulltext

Contents:

TOC 1. Using the mysql Client Program 1 -- 1.1 Setting Up a MySQL User Account 2 -- 1.2 Creating a Database and a Sample Table 4 -- 1.3 Starting and Terminating mysql 5 -- 1.4 Specifying Connection Parameters by Using Option Files 7 -- 1.5 Protecting Option Files 9 -- 1.6 Mixing Command-Line and Option File Parameters 10 -- 1.7 What to Do if mysql Cannot Be Found 10 -- 1.8 Setting Environment Variables 12 -- 1.9 Issuing Queries 14 -- 1.10 Selecting a Database 15 -- 1.11 Canceling a Partially Entered Query 17 -- 1.12 Repeating and Editing Queries 18 -- 1.13 Using Auto-Completion for Database and Table Names 19 -- 1.14 Using SQL Variables in Queries 20 -- 1.15 Telling mysql to Read Queries from a File 23 -- 1.16 Telling mysql to Read Queries from Other Programs 25 -- 1.17 Specifying Queries on the Command Line 26 -- 1.18 Using Copy and Paste as a mysql Input Source 27 -- 1.19 Preventing Query Output from Scrolling off the Screen 28 -- 1.20 Sending Query Output to a File or to a Program 29 -- 1.21 Selecting Tabular or Tab-Delimited Query Output Format 31 -- 1.22 Specifying Arbitrary Output Column Delimiters 31 -- 1.23 Producing HTML Output 33 -- 1.24 Producing XML Output 34 -- 1.25 Suppressing Column Headings in Query Output 35 -- 1.26 Numbering Query Output Lines 36 -- 1.27 Making Long Output Lines More Readable 37 -- 1.28 Controlling mysql's Verbosity Level 38 -- 1.29 Logging Interactive mysql Sessions 39 -- 1.30 Creating mysql Scripts from Previously Executed Queries 40 -- 1.31 Using mysql as a Calculator 41 -- 1.32 Using mysql in Shell Scripts 42 -- 2. Writing MySQL-Based Programs 48 -- 2.1 Connecting to the MySQL Server, Selecting a Database, and Disconnecting 53 -- 2.2 Checking for Errors 65 -- 2.3 Writing Library Files 73 -- 2.4 Issuing Queries and Retrieving Results 85 -- 2.5 Moving Around Within a Result Set 100 -- 2.6 Using Prepared Statements and Placeholders in Queries 101 -- 2.7 Including Special Characters and NULL Values in Queries 106 -- 2.8 Handling NULL Values in Result Sets 114 -- 2.9 Writing an Object-Oriented MySQL Interface for PHP 118 -- 2.10 Ways of Obtaining Connection Parameters 132 -- 2.11 Conclusion and Words of Advice 147 -- 3. Record Selection Techniques 149 -- 3.1 Specifying Which Columns to Display 151 -- 3.2 Avoiding Output Column Order Problems When Writing Programs 152 -- 3.3 Giving Names to Output Columns 153 -- 3.4 Using Column Aliases to Make Programs Easier to Write 156 -- 3.5 Combining Columns to Construct Composite Values 157 -- 3.6 Specifying Which Rows to Select 158 -- 3.7 WHERE Clauses and Column Aliases 161 -- 3.8 Displaying Comparisons to Find Out How Something Works 162 -- 3.9 Reversing or Negating Query Conditions 163 -- 3.10 Removing Duplicate Rows 165 -- 3.11 Working with NULL Values 166 -- 3.12 Negating a Condition on a Column That Contains NULL Values 168 -- 3.13 Writing Comparisons Involving NULL in Programs 169 -- 3.14 Mapping NULL Values to Other Values for Display 170 -- 3.15 Sorting a Result Set 171 -- 3.16 Selecting Records from the Beginning or End of a Result Set 172 -- 3.17 Pulling a Section from the Middle of a Result Set 175 -- 3.18 Choosing Appropriate LIMIT Values 177 -- 3.19 Calculating LIMIT Values from Expressions 179 -- 3.20 What to Do When LIMIT Requires the "Wrong" Sort Order 180 -- 3.21 Selecting a Result Set into an Existing Table 182 -- 3.22 Creating a Destination Table on the Fly from a Result Set 183 -- 3.23 Moving Records Between Tables Safely 185 -- 3.24 Creating Temporary Tables 187 -- 3.25 Cloning a Table Exactly 188 -- 3.26 Generating Unique Table Names 190 -- 4. Working with Strings 192 -- 4.1 Writing Strings That Include Quotes or Special Characters 193 -- 4.2 Preserving Trailing Spaces in String Columns 195 -- 4.3 Testing String Equality or Relative Ordering 196 -- 4.4 Decomposing or Combining Strings 197 -- 4.5 Checking Whether a String Contains a Substring 201 -- 4.6 Pattern Matching with SQL Patterns 201 -- 4.7 Pattern Matching with Regular Expressions 203 -- 4.8 Matching Pattern Metacharacters Literally 208 -- 4.9 Controlling Case Sensitivity in String Comparisons 211 -- 4.10 Controlling Case Sensitivity in Pattern Matching 215 -- 4.11 Using FULLTEXT Searches 218 -- 4.12 Using a FULLTEXT Search with Short Words 222 -- 4.13 Requiring or Excluding FULLTEXT Search Words 224 -- 4.14 Performing Phrase Searches with a FULLTEXT Index 226 -- 5. Working with Dates and Times 228 -- 5.1 Changing MySQL's Date Format 231 -- 5.2 Telling MySQL How to Display Dates or Times 232 -- 5.3 Determining the Current Date or Time 234 -- 5.4 Decomposing Dates and Times Using Formatting Functions 235 -- 5.5 Decomposing Dates or Times Using Component-Extraction Functions 236 -- 5.6 Decomposing Dates or Times Using String Functions 239 -- 5.7 Synthesizing Dates or Times Using Formatting Functions 241 -- 5.8 Synthesizing Dates or Times Using Component-Extraction Functions 242 -- 5.9 Combining a Date and a Time into a Date-and-Time Value 243 -- 5.10 Converting Between Times and Seconds 244 -- 5.11 Converting Between Dates and Days 246 -- 5.12 Converting Between Date-and-Time Values and Seconds 247 -- 5.13 Adding a Temporal Interval to a Time 248 -- 5.14 Calculating Intervals Between Times 250 -- 5.15 Breaking Down Time Intervals into Components 251 -- 5.16 Adding a Temporal Interval to a Date 252 -- 5.17 Calculating Intervals Between Dates 255 -- 5.18 Canonizing Not-Quite-ISO Date Strings 258 -- 5.19 Calculating Ages 259 -- 5.20 Shifting Dates by a Known Amount 263 -- 5.21 Finding First and Last Days of Months 265 -- 5.22 Finding the Length of a Month 267 -- 5.23 Calculating One Date from Another by Substring Replacement 268 -- 5.24 Finding the Day of the Week for a Date 270 -- 5.25 Finding Dates for Days of the Current Week 271 -- 5.26 Finding Dates for Weekdays of Other Weeks 272 -- 5.27 Performing Leap Year Calculations 274 -- 5.28 Treating Dates or Times as Numbers 277 -- 5.29 Forcing MySQL to Treat Strings as Temporal Values 279 -- 5.30 Selecting Records Based on Their Temporal Characteristics 280 -- 5.31 Using TIMESTAMP Values 283 -- 5.32 Recording a Row's Last Modification Time 284 -- 5.33 Recording a Row's Creation Time 286 -- 5.34 Performing Calculations with TIMESTAMP Values 287 -- 5.35 Displaying TIMESTAMP Values in Readable Form 288 -- 6. Sorting Query Results 290 -- 6.1 Using ORDER BY to Sort Query Results 291 -- 6.2 Sorting Subsets of a Table 296 -- 6.3 Sorting Expression Results 297 -- 6.4 Displaying One Set of Values While Sorting by Another 299 -- 6.5 Sorting and NULL Values 304 -- 6.6 Controlling Case Sensitivity of String Sorts 306 -- 6.7 Date-Based Sorting 308 -- 6.8 Sorting by Calendar Day 310 -- 6.9 Sorting by Day of Week 312 -- 6.10 Sorting by Time of Day 314 -- 6.11 Sorting Using Substrings of Column Values 315 -- 6.12 Sorting by Fixed-Length Substrings 315 -- 6.13 Sorting by Variable-Length Substrings 318 -- 6.14 Sorting Hostnames in Domain Order 323 -- 6.15 Sorting Dotted-Quad IP Values in Numeric Order 325 -- 6.16 Floating Specific Values to the Head or Tail of the Sort Order 327 -- 6.17 Sorting in User-Defined Orders 330 -- 6.18 Sorting ENUM Values 331 -- 7. Generating Summaries 335 -- 7.1 Summarizing with COUNT() 337 -- 7.2 Summarizing with MIN() and MAX() 339 -- 7.3 Summarizing with SUM() and AVG() 341 -- 7.4 Using DISTINCT to Eliminate Duplicates 342 -- 7.5 Finding Values Associated with Minimum and Maximum Values 345 -- 7.6 Controlling String Case Sensitivity for MIN() and MAX() 348 -- 7.7 Dividing a Summary into Subgroups 350 -- 7.8 Summaries and NULL Values 354 -- 7.9 Selecting Only Groups with Certain Characteristics 357 -- 7.10 Determining Whether Values are Unique 358 -- 7.11 Grouping by Expression Results 359 -- 7.12 Categorizing Non-Categorical Data 361 -- 7.13 Controlling Summary Display Order 365 -- 7.14 Finding Smallest or Largest Summary Values 367 -- 7.15 Date-Based Summaries 368 -- 7.16 Working with Per-Group and Overall Summary Values Simultaneously 372 -- 7.17 Generating a Report That Includes a Summary and a List 374 -- 8. Modifying Tables with ALTER TABLE 378 -- 8.1 Dropping, Adding, or Repositioning a Column 379 -- 8.2 Changing a Column Definition or Name 380 -- 8.3 The Effect of ALTER TABLE on Null and Default Value Attributes 383 -- 8.4 Changing a Column's Default Value 384 -- 8.5 Changing a Table Type 385 -- 8.6 Renaming a Table 387 -- 8.7 Adding or Dropping Indexes 388 -- 8.8 Eliminating Duplicates by Adding an Index 391 -- 8.9 Using ALTER TABLE to Normalize a Table 392 -- 9. Obtaining and Using Metadata 398 -- 9.1 Obtaining the Number of Rows Affected by a Query 399 -- 9.2 Obtaining Result Set Metadata 401 -- 9.3 Determining Presence or Absence of a Result Set 409 -- 9.4 Formatting Query Results for Display 410 -- 9.5 Getting Table Structure Information 414 -- 9.6 Getting ENUM and SET Column Information 422 -- 9.7 Database-Independent Methods of Obtaining Table Information 424 -- 9.8 Applying Table Structure Information 426 -- 9.9 Listing Tables and Databases 433 -- 9.10 Testing Whether a Table Exists 434 -- 9.11 Testing Whether a Database Exists 435 -- 9.12 Getting Server Metadata 436 -- 9.13 Writing Applications That Adapt to the MySQL Server Version 437 -- 9.14 Determining the Current Database 438 -- 9.15 Determining the Current MySQL User 439 -- 9.16 Monitoring the MySQL Server 440

Summary: Summary: This volume offers a problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe" - short, focused pieces of code that you can insert directly into your applications.

List(s) this item appears in: CSE302: Database Systems

Tags from this library: No tags from this library for this title. Log in to add tags.

Average rating: 0.0 (0 votes)

Holdings ( 4 )
Title notes ( 7 )
Comments ( 0 )

Holdings
Item type	Current library	Collection	Call number	Copy number	Status	Barcode
E-Book	EWU Library E-book	Non-fiction	005.13 DUM 2003 (Browse shelf(Opens below))		Not for loan
Text	EWU Library Reserve Section	Non-fiction	005.13 DUM 2003 (Browse shelf(Opens below))	C-1	Not For Loan	16310
Text	EWU Library Reserve Section	Non-fiction	005.13 DUM 2003 (Browse shelf(Opens below))	C-2	Not For Loan	16311
Text	EWU Library Circulation Section	Non-fiction	005.13 DUM 2003 (Browse shelf(Opens below))	C-3	Available	17575

Total holds: 0

"Covers MySQL 4.0"--Cover.

"Solutions and examples for MySQL database developers"--Cover.

Online version:
DuBois, Paul, 1956-
MySQL Cookbook.
Sebastopol, CA : O'Reilly, c2003
(OCoLC)606904482

Includes bibliographical references (p. 958-960) and index.

TOC 1. Using the mysql Client Program 1 --
1.1 Setting Up a MySQL User Account 2 --
1.2 Creating a Database and a Sample Table 4 --
1.3 Starting and Terminating mysql 5 --
1.4 Specifying Connection Parameters by Using Option Files 7 --
1.5 Protecting Option Files 9 --
1.6 Mixing Command-Line and Option File Parameters 10 --
1.7 What to Do if mysql Cannot Be Found 10 --
1.8 Setting Environment Variables 12 --
1.9 Issuing Queries 14 --
1.10 Selecting a Database 15 --
1.11 Canceling a Partially Entered Query 17 --
1.12 Repeating and Editing Queries 18 --
1.13 Using Auto-Completion for Database and Table Names 19 --
1.14 Using SQL Variables in Queries 20 --
1.15 Telling mysql to Read Queries from a File 23 --
1.16 Telling mysql to Read Queries from Other Programs 25 --
1.17 Specifying Queries on the Command Line 26 --
1.18 Using Copy and Paste as a mysql Input Source 27 --
1.19 Preventing Query Output from Scrolling off the Screen 28 --
1.20 Sending Query Output to a File or to a Program 29 --
1.21 Selecting Tabular or Tab-Delimited Query Output Format 31 --
1.22 Specifying Arbitrary Output Column Delimiters 31 --
1.23 Producing HTML Output 33 --
1.24 Producing XML Output 34 --
1.25 Suppressing Column Headings in Query Output 35 --
1.26 Numbering Query Output Lines 36 --
1.27 Making Long Output Lines More Readable 37 --
1.28 Controlling mysql's Verbosity Level 38 --
1.29 Logging Interactive mysql Sessions 39 --
1.30 Creating mysql Scripts from Previously Executed Queries 40 --
1.31 Using mysql as a Calculator 41 --
1.32 Using mysql in Shell Scripts 42 --
2. Writing MySQL-Based Programs 48 --
2.1 Connecting to the MySQL Server, Selecting a Database, and Disconnecting 53 --
2.2 Checking for Errors 65 --
2.3 Writing Library Files 73 --
2.4 Issuing Queries and Retrieving Results 85 --
2.5 Moving Around Within a Result Set 100 --
2.6 Using Prepared Statements and Placeholders in Queries 101 --
2.7 Including Special Characters and NULL Values in Queries 106 --
2.8 Handling NULL Values in Result Sets 114 --
2.9 Writing an Object-Oriented MySQL Interface for PHP 118 --
2.10 Ways of Obtaining Connection Parameters 132 --
2.11 Conclusion and Words of Advice 147 --
3. Record Selection Techniques 149 --
3.1 Specifying Which Columns to Display 151 --
3.2 Avoiding Output Column Order Problems When Writing Programs 152 --
3.3 Giving Names to Output Columns 153 --
3.4 Using Column Aliases to Make Programs Easier to Write 156 --
3.5 Combining Columns to Construct Composite Values 157 --
3.6 Specifying Which Rows to Select 158 --
3.7 WHERE Clauses and Column Aliases 161 --
3.8 Displaying Comparisons to Find Out How Something Works 162 --
3.9 Reversing or Negating Query Conditions 163 --
3.10 Removing Duplicate Rows 165 --
3.11 Working with NULL Values 166 --
3.12 Negating a Condition on a Column That Contains NULL Values 168 --
3.13 Writing Comparisons Involving NULL in Programs 169 --
3.14 Mapping NULL Values to Other Values for Display 170 --
3.15 Sorting a Result Set 171 --
3.16 Selecting Records from the Beginning or End of a Result Set 172 --
3.17 Pulling a Section from the Middle of a Result Set 175 --
3.18 Choosing Appropriate LIMIT Values 177 --
3.19 Calculating LIMIT Values from Expressions 179 --
3.20 What to Do When LIMIT Requires the "Wrong" Sort Order 180 --
3.21 Selecting a Result Set into an Existing Table 182 --
3.22 Creating a Destination Table on the Fly from a Result Set 183 --
3.23 Moving Records Between Tables Safely 185 --
3.24 Creating Temporary Tables 187 --
3.25 Cloning a Table Exactly 188 --
3.26 Generating Unique Table Names 190 --
4. Working with Strings 192 --
4.1 Writing Strings That Include Quotes or Special Characters 193 --
4.2 Preserving Trailing Spaces in String Columns 195 --
4.3 Testing String Equality or Relative Ordering 196 --
4.4 Decomposing or Combining Strings 197 --
4.5 Checking Whether a String Contains a Substring 201 --
4.6 Pattern Matching with SQL Patterns 201 --
4.7 Pattern Matching with Regular Expressions 203 --
4.8 Matching Pattern Metacharacters Literally 208 --
4.9 Controlling Case Sensitivity in String Comparisons 211 --
4.10 Controlling Case Sensitivity in Pattern Matching 215 --
4.11 Using FULLTEXT Searches 218 --
4.12 Using a FULLTEXT Search with Short Words 222 --
4.13 Requiring or Excluding FULLTEXT Search Words 224 --
4.14 Performing Phrase Searches with a FULLTEXT Index 226 --
5. Working with Dates and Times 228 --
5.1 Changing MySQL's Date Format 231 --
5.2 Telling MySQL How to Display Dates or Times 232 --
5.3 Determining the Current Date or Time 234 --
5.4 Decomposing Dates and Times Using Formatting Functions 235 --
5.5 Decomposing Dates or Times Using Component-Extraction Functions 236 --
5.6 Decomposing Dates or Times Using String Functions 239 --
5.7 Synthesizing Dates or Times Using Formatting Functions 241 --
5.8 Synthesizing Dates or Times Using Component-Extraction Functions 242 --
5.9 Combining a Date and a Time into a Date-and-Time Value 243 --
5.10 Converting Between Times and Seconds 244 --
5.11 Converting Between Dates and Days 246 --
5.12 Converting Between Date-and-Time Values and Seconds 247 --
5.13 Adding a Temporal Interval to a Time 248 --
5.14 Calculating Intervals Between Times 250 --
5.15 Breaking Down Time Intervals into Components 251 --
5.16 Adding a Temporal Interval to a Date 252 --
5.17 Calculating Intervals Between Dates 255 --
5.18 Canonizing Not-Quite-ISO Date Strings 258 --
5.19 Calculating Ages 259 --
5.20 Shifting Dates by a Known Amount 263 --
5.21 Finding First and Last Days of Months 265 --
5.22 Finding the Length of a Month 267 --
5.23 Calculating One Date from Another by Substring Replacement 268 --
5.24 Finding the Day of the Week for a Date 270 --
5.25 Finding Dates for Days of the Current Week 271 --
5.26 Finding Dates for Weekdays of Other Weeks 272 --
5.27 Performing Leap Year Calculations 274 --
5.28 Treating Dates or Times as Numbers 277 --
5.29 Forcing MySQL to Treat Strings as Temporal Values 279 --
5.30 Selecting Records Based on Their Temporal Characteristics 280 --
5.31 Using TIMESTAMP Values 283 --
5.32 Recording a Row's Last Modification Time 284 --
5.33 Recording a Row's Creation Time 286 --
5.34 Performing Calculations with TIMESTAMP Values 287 --
5.35 Displaying TIMESTAMP Values in Readable Form 288 --
6. Sorting Query Results 290 --
6.1 Using ORDER BY to Sort Query Results 291 --
6.2 Sorting Subsets of a Table 296 --
6.3 Sorting Expression Results 297 --
6.4 Displaying One Set of Values While Sorting by Another 299 --
6.5 Sorting and NULL Values 304 --
6.6 Controlling Case Sensitivity of String Sorts 306 --
6.7 Date-Based Sorting 308 --
6.8 Sorting by Calendar Day 310 --
6.9 Sorting by Day of Week 312 --
6.10 Sorting by Time of Day 314 --
6.11 Sorting Using Substrings of Column Values 315 --
6.12 Sorting by Fixed-Length Substrings 315 --
6.13 Sorting by Variable-Length Substrings 318 --
6.14 Sorting Hostnames in Domain Order 323 --
6.15 Sorting Dotted-Quad IP Values in Numeric Order 325 --
6.16 Floating Specific Values to the Head or Tail of the Sort Order 327 --
6.17 Sorting in User-Defined Orders 330 --
6.18 Sorting ENUM Values 331 --
7. Generating Summaries 335 --
7.1 Summarizing with COUNT() 337 --
7.2 Summarizing with MIN() and MAX() 339 --
7.3 Summarizing with SUM() and AVG() 341 --
7.4 Using DISTINCT to Eliminate Duplicates 342 --
7.5 Finding Values Associated with Minimum and Maximum Values 345 --
7.6 Controlling String Case Sensitivity for MIN() and MAX() 348 --
7.7 Dividing a Summary into Subgroups 350 --
7.8 Summaries and NULL Values 354 --
7.9 Selecting Only Groups with Certain Characteristics 357 --
7.10 Determining Whether Values are Unique 358 --
7.11 Grouping by Expression Results 359 --
7.12 Categorizing Non-Categorical Data 361 --
7.13 Controlling Summary Display Order 365 --
7.14 Finding Smallest or Largest Summary Values 367 --
7.15 Date-Based Summaries 368 --
7.16 Working with Per-Group and Overall Summary Values Simultaneously 372 --
7.17 Generating a Report That Includes a Summary and a List 374 --
8. Modifying Tables with ALTER TABLE 378 --
8.1 Dropping, Adding, or Repositioning a Column 379 --
8.2 Changing a Column Definition or Name 380 --
8.3 The Effect of ALTER TABLE on Null and Default Value Attributes 383 --
8.4 Changing a Column's Default Value 384 --
8.5 Changing a Table Type 385 --
8.6 Renaming a Table 387 --
8.7 Adding or Dropping Indexes 388 --
8.8 Eliminating Duplicates by Adding an Index 391 --
8.9 Using ALTER TABLE to Normalize a Table 392 --
9. Obtaining and Using Metadata 398 --
9.1 Obtaining the Number of Rows Affected by a Query 399 --
9.2 Obtaining Result Set Metadata 401 --
9.3 Determining Presence or Absence of a Result Set 409 --
9.4 Formatting Query Results for Display 410 --
9.5 Getting Table Structure Information 414 --
9.6 Getting ENUM and SET Column Information 422 --
9.7 Database-Independent Methods of Obtaining Table Information 424 --
9.8 Applying Table Structure Information 426 --
9.9 Listing Tables and Databases 433 --
9.10 Testing Whether a Table Exists 434 --
9.11 Testing Whether a Database Exists 435 --
9.12 Getting Server Metadata 436 --
9.13 Writing Applications That Adapt to the MySQL Server Version 437 --
9.14 Determining the Current Database 438 --
9.15 Determining the Current MySQL User 439 --
9.16 Monitoring the MySQL Server 440

Summary:
This volume offers a problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe" - short, focused pieces of code that you can insert directly into your applications.

CSE

Tahur Ahmed

There are no comments on this title.

to post a comment.