Mysql注意点

排序

排序有两种 升序 asc 和降序 desc

  • 按照价格降序排列 默认是升序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 |
| 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 |
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
+---------+------------+----------------+
14 rows in set (0.03 sec)
多列排序

排序不一定按照一列进行排序 还有多列排序

可以看到价格是降序,但是如果价格相同的话 默认按照字母升序排列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.06 sec)
至于a 和 A谁大,a 和 z谁排在前面,取决于数据库的设置

详情


AND 和 OR 计算次序问题

计算价格在10美元(含)以上且有1002或者1003制造的所有产品

  • 可以看到其中有小雨10美元的商品
  • 因为AND优先于OR 所以下面SQL语句表达的是

查询价格大于10美元且是1003制造的或者1002制造的产品

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.02 sec)
加括号

下列语句意思为查询1002或者1003制造且价格大于等于10美元的产品

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.02 sec)

IN 操作符

IN 操作符和 OR 功能一样 但是和OR相比优点如下

  • IN操作符语法更清楚直观
  • 比OR操作符执行更快
  • 可以更动态的建立WHERE子句

包含情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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.02 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.03 sec)

不包含情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select prod_name, prod_price 
from products
where vend_id not in (1002, 1003) order by prod_name;
+--------------+------------+
| prod_name | prod_price |
+--------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
+--------------+------------+
5 rows in set (0.01 sec)

mysql> select prod_name, prod_price
from products
where vend_id != 1002 and vend_id != 1003 order by prod_name;
+--------------+------------+
| prod_name | prod_price |
+--------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
+--------------+------------+
5 rows in set (0.02 sec)


通配符

  • % 可以匹配任何东西 但是不能匹配 NULL

使用通配符的缺点

  • 速度比其他操作符慢,尽量不要使用通配符

日期

1
2
3
4
5
6
7
8
9
10
mysql> select cust_id, order_num, order_date
from orders
where order_date = '2005-09-01';

+---------+-----------+---------------------+
| cust_id | order_num | order_date |
+---------+-----------+---------------------+
| 10001 | 20005 | 2005-09-01 00:00:00 |
+---------+-----------+---------------------+
1 row in set (0.06 sec)

日期类型如果是 datetime 那就会有时分秒的格式,再这样就失效了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select cust_id, order_num, order_date from orders;

+---------+-----------+---------------------+
| cust_id | order_num | order_date |
+---------+-----------+---------------------+
| 10001 | 20005 | 2005-09-01 11:11:11 |
| 10003 | 20006 | 2005-09-12 00:00:00 |
| 10004 | 20007 | 2005-09-30 00:00:00 |
| 10005 | 20008 | 2005-10-03 00:00:00 |
| 10001 | 20009 | 2005-10-08 00:00:00 |
+---------+-----------+---------------------+
5 rows in set (0.05 sec)

mysql> select cust_id, order_num, order_date
from orders
where order_date = '2005-09-01';

Empty set

可以使用Date() 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select Date(order_date), order_date from orders;
+------------------+---------------------+
| Date(order_date) | order_date |
+------------------+---------------------+
| 2005-09-01 | 2005-09-01 11:11:11 |
| 2005-09-12 | 2005-09-12 00:00:00 |
| 2005-09-30 | 2005-09-30 00:00:00 |
| 2005-10-03 | 2005-10-03 00:00:00 |
| 2005-10-08 | 2005-10-08 00:00:00 |
+------------------+---------------------+
5 rows in set (0.05 sec)

mysql> select cust_id, order_num, order_date
from orders
where Date(order_date) = '2005-09-01';

+---------+-----------+---------------------+
| cust_id | order_num | order_date |
+---------+-----------+---------------------+
| 10001 | 20005 | 2005-09-01 11:11:11 |
+---------+-----------+---------------------+
1 row in set (0.05 sec)

还可以比较

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select cust_id, order_num, order_date 
from orders
where Date(order_date)
between '2005-09-01' and '2005-09-30';

+---------+-----------+---------------------+
| cust_id | order_num | order_date |
+---------+-----------+---------------------+
| 10001 | 20005 | 2005-09-01 11:11:11 |
| 10003 | 20006 | 2005-09-12 00:00:00 |
| 10004 | 20007 | 2005-09-30 00:00:00 |
+---------+-----------+---------------------+
3 rows in set (0.05 sec)

其他函数

Year() -> 年份 Month() -> 月份