SpirirHost Support Centre: MySQL Reference Manual p.163

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 3: Tutorial Introduction 163 To nd names containing exactly ve characters, use `^' and `$' to match the beginning and end of the name, and ve instances of `.' in between: mysql> SELECT * FROM pet WHERE name REGEXP "^.....$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ You could also write the previous query using the `{n}' \repeat-n-times" operator: mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 3.3.4.8 Counting Rows Databases are often used to answer the question, \How often does a certain type of data occur in a table?" For example, you might want to know how many pets you have, or how many pets each owner has, or you might want to perform various kinds of censuses on your animals. Counting the total number of animals you have is the same question as \How many rows are in the pet table?" because there is one record per pet. The COUNT() function counts the number of non-NULL results, so the query to count your animals looks like this: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ Earlier, you retrieved the names of the people who owned pets. You can use COUNT() if you want to nd out how many pets each owner has: mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ Note the use of GROUP BY to group together all records for each owner. Without it, all you get is an error message:

MySQL Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide