sql必知必会笔记

it2022-05-05 180

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的行；

下划线 “_” ：匹配一个字符

使用concat()函数对查询结果进行拼接：

转载于:https://www.cnblogs.com/lastingjava/p/9968734.html

专利

最新回复(0)