bitscn.com
mysql学习足迹记录02--select
本查询所用到的表格结构和数据
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.检索单个列：
eg: mysql> select prod_name from products; #mysql并不区分大小写+----------------+| prod_name |+----------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || detonator || bird seed || carrots || fuses || jetpack 1000 || jetpack 2000 || oil can || safe || sling || tnt (1 stick) || tnt (5 sticks) |+----------------+
2.检索多个列
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.检索所有列
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.检索不同的行
先列出所有的行以便作对比
eg: mysql> select vend_id from products;+---------+| vend_id |+---------+| 1001 || 1001 || 1001 || 1002 || 1002 || 1003 || 1003 || 1003 || 1003 || 1003 || 1003 || 1003 || 1005 || 1005 |+---------+ *distingct关键字便可以把相同的行去掉 mysql> select distinct vend_id from products;+---------+| vend_id |+---------+| 1001 || 1002 || 1003 || 1005 |+---------+
5.限制结果
*限制返回前几行 eg: mysql> select prod_name from products limit 5;+--------------+| prod_name |+--------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || detonator || bird seed |+--------------+ *限制返回从从第n行开始（下标从0开始），一直持续m行结束 eg: mysql> select prod_name from products limit 5,5;+--------------+| prod_name |+--------------+| carrots || fuses || jetpack 1000 || jetpack 2000 || oil can |+--------------+
6.使用完全限定的表名
eg: mysql> select products.prod_name from mysql_ex.products; #products为表名，mysql_ex为数据库名 +----------------+| prod_name |+----------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || detonator || bird seed || carrots || fuses || jetpack 1000 || jetpack 2000 || oil can || safe || sling || tnt (1 stick) || tnt (5 sticks) |+----------------+
bitscn.com