MySQL learning footprint record 02-SELECT

MySQL learning footprint record 02-SELECT the table structure and data used in this query

mysql> SHOW COLUMNS FROM products;  +------------+--------------+------+-----+---------+-------+| Field      | Type         | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+-------+| prod_id    | char(10)     | NO   | PRI | NULL    |       || vend_id    | int(11)      | NO   | MUL | NULL    |       || prod_name  | char(255)    | NO   |     | NULL    |       || prod_price | decimal(8,2) | NO   |     | NULL    |       || prod_desc  | text         | YES  |     | NULL    |       |+------------+--------------+------+-----+---------+-------+mysql> select * from products;+---------+---------+----------------+------------+----------------------------------------------------------------+| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |+---------+---------+----------------+------------+----------------------------------------------------------------+| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  || ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case || ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case || DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                || FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          || FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           || FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            || JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          || JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        || OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   || SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     || SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       || TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         || TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |+---------+---------+----------------+------------+----------------------------------------------------------------+

 

1. retrieve a single column:

Eg: mysql> SELECT prod_NAME FROM products; # MYSQL is not case sensitive + ---------------- + | prod_NAME | + ---------------- + |. 5 ton andevil | 1 ton andevil | 2 ton andevil | Detonator | Bird seed | Carrots | Fuses | JetPack 1000 | JetPack 2000 | Oil can | safe | Sling | TNT (1 stick) | TNT (5 sticks) | + ---------------- +

 

2. Retrieve multiple columns

  eg:     mysql> SELECT prod_id,prod_name,prod_price FROM products;+---------+----------------+------------+| prod_id | prod_name      | prod_price |+---------+----------------+------------+| ANV01   | .5 ton anvil   |       5.99 || ANV02   | 1 ton anvil    |       9.99 || ANV03   | 2 ton anvil    |      14.99 || DTNTR   | Detonator      |      13.00 || FB      | Bird seed      |      10.00 || FC      | Carrots        |       2.50 || FU1     | Fuses          |       3.42 || JP1000  | JetPack 1000   |      35.00 || JP2000  | JetPack 2000   |      55.00 || OL1     | Oil can        |       8.99 || SAFE    | Safe           |      50.00 || SLING   | Sling          |       4.49 || TNT1    | TNT (1 stick)  |       2.50 || TNT2    | TNT (5 sticks) |      10.00 |+---------+----------------+------------+

 

3. Retrieve all columns

  eg:   mysql> SELECT * FROM products;+---------+---------+----------------+------------+----------------------------------------------------------------+| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |+---------+---------+----------------+------------+----------------------------------------------------------------+| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  || ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case || ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case || DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                || FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          || FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           || FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            || JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          || JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        || OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   || SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     || SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       || TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         || TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |+---------+---------+----------------+------------+----------------------------------------------------------------+

 

4. Retrieve different rows to list all rows for comparison

Eg: mysql> SELECT vend_id FROM products; + --------- + | vend_id | + --------- + | 1001 | 1001 | 1001 | 1002 | 1002 | 1003 | 1003 | 1003 | 1003 | 1003 | 1003 | 1003 | 1005 | 1005 | + --------- + * DISTINGCT keyword can remove the same row mysql> select distinct vend_id FROM products; + --------- + | vend_id | + --------- + | 1001 | 1002 | 1003 | 1005 | + --------- +

 

5. Restrict results

* Restrict the first few rows returned. For example: mysql> SELECT prod_name FROM products LIMIT 5; + ---------------- + | prod_name | + -------------- + |. 5 ton andevil | 1 ton andevil | 2 ton andevil | Detonator | Bird seed | + -------------- + * restrict the return from row N (subscript starts from 0 ), continue until M rows end eg: mysql> SELECT prod_name FROM products LIMIT 5, 5; + -------------- + | prod_name | + -------------- + | Carrots | Fuses | JetPack 1000 | JetPack 2000 | Oil can | + -------------- +

 

6. Use a fully qualified table name

Eg: mysql> SELECT products. prod_name FROM MySQL_ex.products; # products indicates the table name, And MySQL_ex indicates the database name + ---------------- + | prod_name | + ---------------- + |. 5 ton andevil | 1 ton andevil | 2 ton andevil | Detonator | Bird seed | Carrots | Fuses | JetPack 1000 | JetPack 2000 | Oil can | safe | Sling | TNT (1 stick) | TNT (5 sticks) | + ---------------- +