Mysql必知必会mysql学习笔记（五）

排序检索数据

检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初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）、空值检查

检查某位顾客的电子邮件没有地址

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值NULL。NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。

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)

PS：MySQL在执行匹配时默认不区分大小写

如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

 

NULL与不匹配 在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。

8、组合where子句

操作符（operator） 用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符（logical operator）。

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