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
   240   241   242   243   244   245   246   247   248   249   250