Information Technology Reference
In-Depth Information
'Hello, I am Sam Lam, I am new to this forum');
INSERT INTO COMMENTS VALUES (2, null,
'Greetings, my name is Jean Green, I just joined this forum');
INSERT INTO COMMENTS VALUES (1, null,
'Hi Jean, I am also new, Sam')
The null value in the second field of this case will be filled by the current
time when the message was inserted. Using the ID value saves table space
because we don't need to store the full name of the user in each row of the
COMMENTS table. We query the contents of this table:
SELECT * FROM COMMENTS;
+
————
+
————————
+
——————————————————————————————
——————
+
| USERID | TIME | MESSAGE |
+
————
+
————————
+
——————————————————————————————
——————
+
| 1 | 20070512221208 | Hello, I am Sam Lam, I am new to this forum |
| 2 | 20070512221255 | Greetings, my name is Jean Green, I just joined
this forum |
|
1 | 20070512221406 | Hi Jean, I am also new, Sam |
+
————
+
————————
+
——————————————————————————————
——————
+
3 rows in set (0.04 sec)
With these two tables, we can do a slightly more complicated query to
use the relational aspect of the database. The USERID field of the COM-
MENTS table is a relation into the USERS table via the ID field. We can do
what is called a join to get a result that combines information from both
tables:
SELECT USERS.FIRST,USERS.LAST,COMMENTS.TIME,COMMENTS.MESSAGE
FROM USERS,COMMENTS WHERE USERS.ID
=
COMMENTS.USERID;
+
————
+
————————
+
——————————————————————————————
——————
+
| FIRST | LAST | TIME | MESSAGE |
+
————
+
————————
+
——————————————————————————————
——————
+
| Sam | Lam | 20070512221208 | Hello, I am Sam Lam, I am new to this
forum |
| Jean | Green | 20070512221255 | Greetings, my name is Jean Green, I just
joined this forum |
| Sam | Lam | 20070512221406 | Hi Jean, I am also new, Sam |
+
————
+
————————
+
——————————————————————————————
——————
+
3 rows in set (0.42 sec)
Here you see that to join you need to figure out which username to use
from the ID value. The more complicated notation using dots is required in
Search WWH ::




Custom Search