排序检索数据
检索出的数据并不是以纯粹的随机顺序显示的。如果不排序,数据一般将以它在底层表中出现的顺序显示。这可以是数据最初v>添加到表中的顺序。但是,如果数据后来进行过更新或删除,则此顺序将会受到MySQL重用回收存储空间的影响。因此,如果不明确控制的话,不能(也不应该)依赖该排序顺序。关系数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
1、排序数据
select 列名 from 表名;
通过order by子句,默认升序排序
select 列名 from 表名 order by 列名;
mysql> select prod_name from products order by prod_name; +----------------+ | prod_name | +----------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | | Bird seed | | Carrots | | Detonator | | Fuses | | JetPack 1000 | | JetPack 2000 | | Oil can | | Safe | | Sling | | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 14 rows in set (0.00 sec) mysql> select prod_name from products; +----------------+ | 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) | +----------------+ 14 rows in set (0.00 sec)
通过非选择列进行排序:通常,ORDER BY子句中使用的列将是为显示所选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的。
2、按多个列排序
select 列名, 列名, 列名 from 表名 order by 列名, 列名;
mysql> select prod_id,prod_price,prod_name from products order by prod_price,prod_name; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | FC | 2.50 | Carrots | | TNT1 | 2.50 | TNT (1 stick) | | FU1 | 3.42 | Fuses | | SLING | 4.49 | Sling | | ANV01 | 5.99 | .5 ton anvil | | OL1 | 8.99 | Oil can | | ANV02 | 9.99 | 1 ton anvil | | FB | 10.00 | Bird seed | | TNT2 | 10.00 | TNT (5 sticks) | | DTNTR | 13.00 | Detonator | | ANV03 | 14.99 | 2 ton anvil | | JP1000 | 35.00 | JetPack 1000 | | SAFE | 50.00 | Safe | | JP2000 | 55.00 | JetPack 2000 | +---------+------------+----------------+ 14 rows in set (0.00 sec)
仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的,则不会按prod_name排序。
3、指定排序方向,按照降序排序
select 列名, 列名, 列名 from 表名 order by 列名 desc;
mysql> select prod_id,prod_price,prod_name from products order by prod_price desc; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | JP2000 | 55.00 | JetPack 2000 | | SAFE | 50.00 | Safe | | JP1000 | 35.00 | JetPack 1000 | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | | FB | 10.00 | Bird seed | | TNT2 | 10.00 | TNT (5 sticks) | | ANV02 | 9.99 | 1 ton anvil | | OL1 | 8.99 | Oil can | | ANV01 | 5.99 | .5 ton anvil | | SLING | 4.49 | Sling | | FU1 | 3.42 | Fuses | | FC | 2.50 | Carrots | | TNT1 | 2.50 | TNT (1 stick) | +---------+------------+----------------+ 14 rows in set (0.00 sec) mysql> select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | JP2000 | 55.00 | JetPack 2000 | | SAFE | 50.00 | Safe | | JP1000 | 35.00 | JetPack 1000 | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | | FB | 10.00 | Bird seed | | TNT2 | 10.00 | TNT (5 sticks) | | ANV02 | 9.99 | 1 ton anvil | | OL1 | 8.99 | Oil can | | ANV01 | 5.99 | .5 ton anvil | | SLING | 4.49 | Sling | | FU1 | 3.42 | Fuses | | FC | 2.50 | Carrots | | TNT1 | 2.50 | TNT (1 stick) | +---------+------------+----------------+ 14 rows in set (0.00 sec) mysql> select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name desc; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | JP2000 | 55.00 | JetPack 2000 | | SAFE | 50.00 | Safe | | JP1000 | 35.00 | JetPack 1000 | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | | TNT2 | 10.00 | TNT (5 sticks) | | FB | 10.00 | Bird seed | | ANV02 | 9.99 | 1 ton anvil | | OL1 | 8.99 | Oil can | | ANV01 | 5.99 | .5 ton anvil | | SLING | 4.49 | Sling | | FU1 | 3.42 | Fuses | | TNT1 | 2.50 | TNT (1 stick) | | FC | 2.50 | Carrots | +---------+------------+----------------+ 14 rows in set (0.00 sec)
数据排序不限于升序排序(从A到Z)。这是默认的排序顺序,还可以通过关键字 desc降序(从Z到A),desc只应用到直接位于其前面的列名,如果要多个列以降序,则每个列需加desc。
与DESC相反的关键字是ASC(ASCENDING),在升序排序时可以指定它。但实际上,ASC没有多大用处,因为升序是默认的(如果既不指定ASC也不指定DESC,则假定为ASC)。
区分大小写和排序顺序:取决于数据库设置
ORDER BY子句的位置 在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。
4、找出一列中的最高或最低值
select 列名 from 表名 order by 列名 desc limie 1;
mysql> select prod_id,prod_price,prod_name from products order by prod_price desc limit 1; +---------+------------+--------------+ | prod_id | prod_price | prod_name | +---------+------------+--------------+ | JP2000 | 55.00 | JetPack 2000 | +---------+------------+--------------+ 1 row in set (0.00 sec)
6、使用where子句。只检索所需数据需要指定搜索条件,利用where子句,如下所示
select 列名, 列名 from 表名 where 列名 = xxx;(where跟某一条件)
order by要在where子句之后
mysql> select prod_id,prod_price,prod_name from products where prod_price=2.5 ; +---------+------------+---------------+ | prod_id | prod_price | prod_name | +---------+------------+---------------+ | FC | 2.50 | Carrots | | TNT1 | 2.50 | TNT (1 stick) | +---------+------------+---------------+ 2 rows in set (0.00 sec) mysql> select prod_id,prod_price,prod_name from products where prod_price<>2.5 ; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | ANV01 | 5.99 | .5 ton anvil | | ANV02 | 9.99 | 1 ton anvil | | ANV03 | 14.99 | 2 ton anvil | | DTNTR | 13.00 | Detonator | | FB | 10.00 | Bird seed | | FU1 | 3.42 | Fuses | | JP1000 | 35.00 | JetPack 1000 | | JP2000 | 55.00 | JetPack 2000 | | OL1 | 8.99 | Oil can | | SAFE | 50.00 | Safe | | SLING | 4.49 | Sling | | TNT2 | 10.00 | TNT (5 sticks) | +---------+------------+----------------+ 12 rows in set (0.00 sec) mysql> select prod_id,prod_price,prod_name from products where prod_price<>2.5 order by prod_price; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | FU1 | 3.42 | Fuses | | SLING | 4.49 | Sling | | ANV01 | 5.99 | .5 ton anvil | | OL1 | 8.99 | Oil can | | ANV02 | 9.99 | 1 ton anvil | | FB | 10.00 | Bird seed | | TNT2 | 10.00 | TNT (5 sticks) | | DTNTR | 13.00 | Detonator | | ANV03 | 14.99 | 2 ton anvil | | JP1000 | 35.00 | JetPack 1000 | | SAFE | 50.00 | Safe | | JP2000 | 55.00 | JetPack 2000 | +---------+------------+----------------+ 12 rows in set (0.00 sec)
7、where子句操作符
(1)、检测单个值,同上
(2)、检测小于10美元的所有产品
select prod_name, prod_price from products where prod_price < 10;
或者等于10美元
select prod_name, prod_price from products where prod_price <= 10;
mysql> select prod_name, prod_price from products where prod_price < 10; +---------------+------------+ | prod_name | prod_price | +---------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Carrots | 2.50 | | Fuses | 3.42 | | Oil can | 8.99 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | +---------------+------------+ 7 rows in set (0.00 sec) mysql> select prod_name, prod_price from products where prod_price <= 10; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Bird seed | 10.00 | | Carrots | 2.50 | | Fuses | 3.42 | | Oil can | 8.99 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.00 sec)
(3)、不匹配检查
找出不是由供应商1003制造的所有产品
select vend_id, prod_name from products where vend_id <> 1003;
select vend_id, prod_name from products where vend_id != 1003;
mysql> select vend_id, prod_name from products where vend_id <> 1003; +---------+--------------+ | vend_id | prod_name | +---------+--------------+ | 1001 | .5 ton anvil | | 1001 | 1 ton anvil | | 1001 | 2 ton anvil | | 1002 | Fuses | | 1005 | JetPack 1000 | | 1005 | JetPack 2000 | | 1002 | Oil can | +---------+--------------+ 7 rows in set (0.00 sec) mysql> select vend_id, prod_name from products where vend_id != 1003; +---------+--------------+ | vend_id | prod_name | +---------+--------------+ | 1001 | .5 ton anvil | | 1001 | 1 ton anvil | | 1001 | 2 ton anvil | | 1002 | Fuses | | 1005 | JetPack 1000 | | 1005 | JetPack 2000 | | 1002 | Oil can | +---------+--------------+ 7 rows in set (0.00 sec)
(4)、范围值检查
检索价格在5美元和10美元之间所有的产品
select prod_name, prod_price from products where prod_price between 5 and 10;
mysql> select prod_name,prod_price from products where prod_price between 5 and 10; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | .5 ton anvil | 5.99 | | 1 ton anvil | 9.99 | | Bird seed | 10.00 | | Oil can | 8.99 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 5 rows in set (0.00 sec)
(5)、空值检查
检查某位顾客的电子邮件没有地址
select cust_id from customers where cust_email is null;
mysql> select cust_id from customers where cust_email is null; +---------+ | cust_id | +---------+ | 10002 | | 10005 | +---------+ 2 rows in set (0.00 sec)
8、组合where子句
MySQL允许给出多个WHERE子句。这些子句可以两种方式使用:以AND子句的方式或OR子句的方式使用。
(1)and操作符
检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格
select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;
用and语句可以添加多个过滤条件,每添加一个条件就使用一个and
mysql> select prod_id,prod_price,prod_name from products where vend_id = 1003 and prod_price <= 10; +---------+------------+----------------+ | prod_id | prod_price | prod_name | +---------+------------+----------------+ | FB | 10.00 | Bird seed | | FC | 2.50 | Carrots | | SLING | 4.49 | Sling | | TNT1 | 2.50 | TNT (1 stick) | | TNT2 | 10.00 | TNT (5 sticks) | +---------+------------+----------------+ 5 rows in set (0.00 sec)
(2)or操作符
检索由1002和1003中任一个指定供应商制造的所有产品的产品和价格
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;
mysql> select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | Detonator | 13.00 | | Bird seed | 10.00 | | Carrots | 2.50 | | Fuses | 3.42 | | Oil can | 8.99 | | Safe | 50.00 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.01 sec)
(3)计算次序
where可包含任意数目的and和or操作符,允许两者结合以进行复杂和高级的过滤,但是谁的优先级比较高
需要列出价格为10美元(含)以上且由1002和1003制造的所有产品
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;
mysql> select vend_id,prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10; +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1003 | Detonator | 13.00 | | 1003 | Bird seed | 10.00 | | 1002 | Fuses | 3.42 | | 1002 | Oil can | 8.99 | | 1003 | Safe | 50.00 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 6 rows in set (0.00 sec)
这样表达室友问题的,因为and的优先级比or高,所以正确的表达如下
select prod_name, prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
mysql> select vend_id,prod_name, prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10; +---------+----------------+------------+ | vend_id | prod_name | prod_price | +---------+----------------+------------+ | 1003 | Detonator | 13.00 | | 1003 | Bird seed | 10.00 | | 1003 | Safe | 50.00 | | 1003 | TNT (5 sticks) | 10.00 | +---------+----------------+------------+ 4 rows in set (0.00 sec)
9、IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配,其实等同于OR
select prod_name, prod_price from products where vend_id in (1002,1003) order by prod_name;
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 order by prod_name;
mysql> select prod_name, prod_price from products where vend_id in (1002,1003) order by prod_name; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | Bird seed | 10.00 | | Carrots | 2.50 | | Detonator | 13.00 | | Fuses | 3.42 | | Oil can | 8.99 | | Safe | 50.00 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.00 sec) mysql> select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 order by prod_name; +----------------+------------+ | prod_name | prod_price | +----------------+------------+ | Bird seed | 10.00 | | Carrots | 2.50 | | Detonator | 13.00 | | Fuses | 3.42 | | Oil can | 8.99 | | Safe | 50.00 | | Sling | 4.49 | | TNT (1 stick) | 2.50 | | TNT (5 sticks) | 10.00 | +----------------+------------+ 9 rows in set (0.00 sec)
10、NOT操作符
列出除1002和1003之外的所有供应商制造的产品
select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;
在复杂的子句中,not非常有用,在与IN操作符联合使用时,not找出不匹配信息非常简单
mysql> select vend_id,prod_name from products where vend_id in (1001,1003); +---------+----------------+ | vend_id | prod_name | +---------+----------------+ | 1001 | .5 ton anvil | | 1001 | 1 ton anvil | | 1001 | 2 ton anvil | | 1003 | Detonator | | 1003 | Bird seed | | 1003 | Carrots | | 1003 | Safe | | 1003 | Sling | | 1003 | TNT (1 stick) | | 1003 | TNT (5 sticks) | +---------+----------------+ 10 rows in set (0.00 sec) mysql> select vend_id,prod_name from products where vend_id not in (1001,1003); +---------+--------------+ | vend_id | prod_name | +---------+--------------+ | 1002 | Fuses | | 1005 | JetPack 1000 | | 1005 | JetPack 2000 | | 1002 | Oil can | +---------+--------------+ 4 rows in set (0.00 sec)
————————————————
版权声明:本文为CSDN博主「搞IT的王蜀黍」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/yilouwen7522/article/details/81091139
原文:https://www.cnblogs.com/laonicc/p/12456420.html
如果您也喜欢它,动动您的小指点个赞吧