Working with records

Adding records

To add a record use the INSERT statement:

INSERT INTO table_name VALUES(value_1, value_2, ...);

Example:: mysql> INSERT INTO products VALUES('AMD', 'Athlon', 250.99, 21);

One can also add multiple records stored in advance in a file with the following syntax:

LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name;

Use one line per record and separate the records by the TABs. If a field in a record is missing, use the keyword NULL instead.

Example:: mysql> LOAD DATA LOCAL INFILE 'products.txt' INTO TABLE products;

This feature is, however, not supported by the MySQL version installed on the server.

Displaying records

The easiest way to display all records in a small database it to use the following syntax:

mysql> SELECT * FROM table_name

Example:

mysql> SELECT * FROM products; 
+--------------+----------+--------+----------+
| make         | model    | price  | quantity |
+--------------+----------+--------+----------+
| AMD          | Athlon   | 250.99 |       21 |
| Intel        | Pentium  | 399.95 |       25 |
| Linksys      | WRT54GL  |  69.99 |       14 |
| D-Link       | DI-524   |  40.99 |       12 |
| NETGEAR      | WGT624   |  64.99 |       15 |
| MOTOROLA     | SB5120   |  59.99 |        8 |
| U.S.Robotics | USR5686E |  80.99 |        5 |
| ZALMAN       | VF900    |  49.99 |       10 |
| Thermaltake  | A1280    |   8.49 |       16 |
+--------------+----------+--------+----------+
9 rows in set (0.00 sec)

Sorting query results

Use the SELECT command to sort the values in ascending or descending order:

Examples:: mysql> SELECT make, model FROM products ORDER BY make, model; mysql> SELECT make, model FROM products ORDER BY make DESC, model;

In the above example, the entries will be sorted first my make and secondary by model (for the same make, if any).

Filtering query results

One can specify the records to retrieve by using the WHERE keyword:

Example:: mysql> SELECT * FROM products WHERE make='Intel';

Updating records

To update an existing record, use the UPDATE statement:

UPDATE table_name SET column_name=value WHERE condition;

Example:: mysql> UPDATE products SET quantity=15 WHERE make='ZALMAN';

Deleting records

To delete a record, use the following syntax:

DELETE FROM table_name WHERE condition;

Example:: mysql> DELETE FROM products WHERE make='ZALMAN' AND model='VF900';

printTitle()