mysql>
mysql>
mysql> # create a table called "watches"
mysql> CREATE TABLE watches
-> (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> model VARCHAR(20) NOT NULL,
-> style VARCHAR(6) DEFAULT "Gents",
-> price DECIMAL(3,2) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> # insert 5 records into the "watches" table
mysql> INSERT INTO watches (model, price) VALUES ("Panama",69.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO watches (model, style, price) VALUES ("Club", "Ladies", 59.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO watches (model, price) VALUES ("Avante",49.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO watches (model, style, price) VALUES ("Panama", "Ladies", 69.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO watches (model, price) VALUES ("Club",59.99);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> # show all data in the "watches" table
mysql> SELECT * FROM watches;
+----+--------+--------+-------+
| id | model | style | price |
+----+--------+--------+-------+
| 1 | Panama | Gents | 9.99 |
| 2 | Club | Ladies | 9.99 |
| 3 | Avante | Gents | 9.99 |
| 4 | Panama | Ladies | 9.99 |
| 5 | Club | Gents | 9.99 |
+----+--------+--------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> # show data in "watches" grouped by style
mysql> SELECT model, style, price FROM watches ORDER BY 2;
+--------+--------+-------+
| model | style | price |
+--------+--------+-------+
| Panama | Gents | 9.99 |
| Avante | Gents | 9.99 |
| Club | Gents | 9.99 |
| Club | Ladies | 9.99 |
| Panama | Ladies | 9.99 |
+--------+--------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> # show data in "watches" in ascending price order
mysql> SELECT model, style, price FROM watches ORDER BY 3;
+--------+--------+-------+
| model | style | price |
+--------+--------+-------+
| Panama | Gents | 9.99 |
| Club | Ladies | 9.99 |
| Avante | Gents | 9.99 |
| Panama | Ladies | 9.99 |
| Club | Gents | 9.99 |
+--------+--------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> # delete this sample table
mysql> DROP TABLE watches;
Query OK, 0 rows affected (0.00 sec)