Low cost ecommerce web development India flash website design
Viewing Stored Data
The command we use to view data stored in your database tables, SELECT, is the
most complicated command in the SQL language. The reason for this complexity
is that the chief strength of a database is its flexibility in data retrieval and
presentation. As, at this point in our experience with databases, we need only
fairly simple lists of results, we'll just consider the simpler forms of the SELECT
command. This command will list everything stored in the Jokes table:
mysql>SELECT * FROM Jokes;
Read aloud, this command says "select everything from Jokes". If you try this
command, your results will resemble this:
+----+---------------------------------------------------
------------+------------+
| ID | JokeText
| JokeDate |
+----+---------------------------------------------------
------------+------------+
| 1 | Why did the chicken cross the road? To get to the
other side! | 2000-04-01 |
+----+---------------------------------------------------
------------+------------+
1 row in set (0.05 sec)
It looks a little disorganised because the text in the JokeText column is too long
for the table to fit properly on the screen. For this reason, you might want to tell
MySQL to leave out the JokeText column. The command for doing this is as
follows:
mysql>SELECT ID, JokeDate FROM Jokes;
This time instead of telling it to "select everything", we told it precisely which
columns we wanted to see. The results look like this:
+----+------------+
| ID | JokeDate |
+----+------------+
| 1 | 2000-04-01 |
+----+------------+
1 row in set (0.00 sec)
44
Getting Started with MySQL
Not bad, but we'd like to see at least some of the joke text, wouldn't we? In addition
to listing the columns that we want the SELECT command to show us, we
can modify those columns with functions. One function, called LEFT, lets us tell
MySQL to display up to a specified maximum number of characters when it
displays a column. For example, let's say we wanted to see only the first 20
characters of the JokeText column:
mysql>SELECT ID, LEFT(JokeText,20), JokeDate FROM Jokes;
+----+----------------------+------------+
| ID | LEFT(JokeText,20) | JokeDate |
+----+----------------------+------------+
| 1 | Why did the chicken | 2000-04-01 |
+----+----------------------+------------+
1 row in set (0.05 sec)
See how that worked? Another useful function is COUNT, which simply lets us
count the number of results returned. So, for example, if we wanted to find out
how many jokes were stored in our table, we could use the following command:
mysql>SELECT COUNT(*) FROM Jokes;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.06 sec)
As you can see, we have just one joke in our table. So far, all our examples have
fetched all the entries in the table. But if we add what's called a WHERE clause
(for reasons that will become obvious in a moment) to a SELECT command, we
can limit which entries are returned as results. Consider this example:
mysql>SELECT COUNT(*) FROM Jokes WHERE JokeDate >= "2000-01-01";
This query will count the number of jokes that have dates "greater than or equal
to"
"on or after". Another variation on this theme lets you search for entries that
contain a certain piece of text. Check out this query:
mysql>SELECT JokeText FROM Jokes WHERE JokeText LIKE "%chicken%";
This query displays the text of all jokes that contain the word "chicken" in their
JokeText column. The LIKE keyword tells MySQL that the named column must
match the given pattern. In this case, the pattern we've used is "%chicken%". The
45
Viewing Stored Data
% signs here indicate that the word "chicken" may be preceded and/or followed
by any string of text.
Additional conditions may also be combined in the WHERE clause to further restrict
results. For example, to display knock-knock jokes from April 2000 only, we
could use the following query:
mysql>SELECT JokeText FROM Jokes WHERE
->JokeText LIKE "%knock%" AND
->JokeDate >= "2000-04-01" AND
->JokeDate < "2000-05-01";
Enter a few more jokes into the table and experiment with SELECT statements a
little. A good familiarity with the SELECT statement will come in handy later in
this book.
There's a lot more you can do with the SELECT statement, but we'll save looking
at some of its more advanced features for later, when we need them.
website designer freelance ASP PHP ecommerce web developer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110