Retrieving Information from a Table
210
To find names containing exactly five characters, use “
^
” and “
$
” to match the beginning and end of the
name, and five 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 |
+-------+--------+---------+------+------------+-------+
Section 12.5.2, “Regular Expressions”
, provides more information about the syntax for regular
expressions.
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 census operations 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.
COUNT(*)
[970]
counts the number of rows, 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()
[970]
if you
want to find 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 |
+--------+----------+
The preceding query uses
GROUP BY
to group all records for each
owner
. The use of
COUNT()
[970]
in conjunction with
GROUP BY
is useful for characterizing your data under various
groupings. The following examples show different ways to perform animal census operations.
Number of animals per species:
mysql>
SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
