Viewing Tables

To view data the SELECT command is used.

 

The result is stored in a result table, called the result-set:

 

mysql> select * from Students ;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
| 1  | Pam   | Frost   | 21  | London  | 1065 | MySQL    | 98    |
| 2  | James | Cross   | 20  | Watford | 7519 | Music    | 88    |
| 3  | Andy  | Jones   | 34  | Croydon | 8198 | Markets  | 64    |
| 4  | David | Smith   | 42  | London  | 0151 | Antiques | 82    |
| 5  | Julia | James   | 17  | London  | 2108 | Maths    | 77    |
| 6  | Phil  | Watson  | 36  | Hook    | 9437 | Cars     | 44    |
| 7  | Terry | Waite   | 58  | Manc    | 2643 | Music    | 59    |
| 8  | Alex  | Rose    | 49  | Cheam   | 0672 | C        | 70    |
+----+-------+---------+-----+---------+------+----------+-------+
8 rows in set (0.00 sec)

In the above example, the wildcard * has been to use to signify that all fields are required.

 

Alternatively, just a single field could be specified:

mysql> select Study from Students ;
+----------+
| Study    |
+----------+
| MySQL    |
| Music    |
| Markets  |
| Antiques |
| Maths    |
| Cars     |
| Music    |
| C        |
+----------+
8 rows in set (0.00 sec)

 

Records can be filtered by a specified field using the WHERE command:

mysql> select * from Students WHERE Town = 'London' ;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
| 1  | Pam   | Frost   | 21  | London  | 1065 | MySQL    | 98    |
| 4  | David | Smith   | 42  | London  | 0151 | Antiques | 82    |
| 5  | Julia | James   | 17  | London  | 2108 | Maths    | 77    |
+----+-------+---------+-----+---------+------+----------+-------+
3 rows in set (0.00 sec)

Leave a Reply