distinct:
只返回不同值的行（返回结果不会重复）；
示例：
没有使用distinct的结果：
mysql> select vend_id from products; +---------+ | vend_id | +---------+ | 1001 | | 1001 | | 1001 | | 1002 | | 1002 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1005 | | 1005 | +---------+ 14 rows in set (0.00 sec)
使用distinct的结果：
mysql> select distinct vend_id from products; +---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1005 | +---------+ 4 rows in set (0.00 sec)
注释：/* */ or #
mysql> select * from products /*annotation*/ -> #anotation; -> ;
order by 列名：默认升序排列
mysql> select prod_name,prod_price from products order by prod_price; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | TNT (1 stick) | 2.50 | | Carrots | 2.50 | | Fuses | 3.42 | | Sling | 4.49 | | .5 ton anvil | 5.99 | | Oil can | 8.99 | | 1 ton anvil | 9.99 | | TNT (5 sticks) | 10.00 | | Bird seed | 10.00 | | Detonator | 13.00 | | 2 ton anvil | 14.99 | | JetPack 1000 | 35.00 | | Safe | 50.00 | | JetPack 2000 | 55.00 | +----------------+------------+ 14 rows in set (0.00 sec)
order by 列名 desc:降序排列
mysql> select prod_name,prod_price from products order by prod_price desc; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | JetPack 2000 | 55.00 | | Safe | 50.00 | | JetPack 1000 | 35.00 | | 2 ton anvil | 14.99 | | Detonator | 13.00 | | TNT (5 sticks) | 10.00 | | Bird seed | 10.00 | | 1 ton anvil | 9.99 | | Oil can | 8.99 | | .5 ton anvil | 5.99 | | Sling | 4.49 | | Fuses | 3.42 | | Carrots | 2.50 | | TNT (1 stick) | 2.50 | +----------------+------------+ 14 rows in set (0.00 sec)
AND：与
mysql> select prod_id from products where prod_id='ANV01' and vend_id=1001; +---------+ | prod_id | +---------+ | ANV01 | +---------+ 1 row in set (0.00 sec)
or：或
mysql> select prod_id from products where prod_id='ANV01' or vend_id=1001; +---------+ | prod_id | +---------+ | ANV01 | | ANV02 | | ANV03 | +---------+ 3 rows in set (0.00 sec)
in : 在（value1，vlaue2,value3，...）
mysql> select prod_id,prod_name,prod_price from products where vend_id in (1001,1003); +---------+----------------+------------+ | 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 | | SAFE | Safe | 50.00 | | SLING | Sling | 4.49 | | TNT1 | TNT (1 stick) | 2.50 | | TNT2 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 10 rows in set (0.00 sec)
not：不等于（此条示例也可以用！=实现）
mysql> select * from products where not vend_id=1001; +---------+---------+----------------+------------+-------------------------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+----------------+------------+-------------------------------------------------+ | 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 | +---------+---------+----------------+------------+-------------------------------------------------+ 11 rows in set (0.00 sec)
百分号（%）通配符：代表匹配0，1或多个字符
mysql> select * from products where vend_id like "%03"; +---------+---------+----------------+------------+-------------------------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+----------------+------------+-------------------------------------------------+ | 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) | | 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 | +---------+---------+----------------+------------+-------------------------------------------------+ 7 rows in set (0.00 sec) mysql> select * from products where vend_id like -> '%ton anvi%'; Empty set (0.00 sec) mysql> select * from products where prod_name like '%vil'; +---------+---------+--------------+------------+----------------------------------------------------------------+ | 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 | +---------+---------+--------------+------------+----------------------------------------------------------------+ 3 rows in set (0.00 sec)
select * from products where prod_name like '%';
不会匹配prod_name为null的行；
下划线 “_” ：匹配一个字符
mysql> select * from products where prod_name like '_ ton anvil'; +---------+---------+-------------+------------+----------------------------------------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+-------------+------------+----------------------------------------------------------------+ | 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 | +---------+---------+-------------+------------+----------------------------------------------------------------+ 2 rows in set (0.00 sec)
使用concat()函数对查询结果进行拼接：
mysql> select concat(cust_id,'==',cust_name) from customers; +--------------------------------+ | concat(cust_id,'==',cust_name) | +--------------------------------+ | 10001==Coyote Inc. | | 10002==Mouse House | | 10003==Wascals | | 10004==Yosemite Place | | 10005==E Fudd | +--------------------------------+ 5 rows in set (0.00 sec)