Page 245 - Open Soource Technologies 304.indd
P. 245
Unit 10: Database
SELECT * FROM guests; Notes
In the “mysql” program the returned data will appear in a text form that resembles this:
GUEST_ID GUEST_NAME GUEST_EMAIL GUEST_TIME GUEST_MESSAGE
0001 Tony tony@gmail.com 19990811105100 This is how it works!
Sometimes you only want certain records. The statement to limit a search to particular criteria
is like this:
SELECT * FROM guests WHERE guest_name=’Tony’;
This will only return the records where the “guest_name” is “Tony”. You can also limit the
fields that are returned in this way:
SELECT guest_name,guest_email FROM guests;
This should return something that looks like this:
GUEST_NAME GUEST_EMAIL
Tony tony@gmail.com
To avoid the mistakes with databases make sure that all SQL statements must
contain the correct syntax.
Updating Existing Data
Changing data is a little trickier. To change the email address of the previously entered data
use this statement:
UPDATE guests
SET guest_email=’info@gmail.com’
WHERE guest_email=’tony@gmail.com’;
The successful execution of the query will result in:
Query OK, 1 row affected (0.00 sec)
The resulting data when selected should look like this:
GUEST_ID GUEST_NAME GUEST_EMAIL GUEST_TIME GUEST_MESSAGE
0001 Tony info@mail.com 19990811105100 This is how it works!
Deleting Data
To delete all the data from a table only needs the following statement:
DELETE FROM guests;
To delete specific records the query would look like this:
DELETE FROM guests WHERE guest_name=’Tony’;
The successful execution of the query will result in:
Query OK, 1 row affected (0.00 sec)
Saving a Database
One other valuable feature most databases possess is the ability to output SQL statements that
define the structure and content of the tables in a database. MySQL is no exception and includes
LOVELY PROFESSIONAL UNIVERSITY 239