MySQL语句疑惑记录

主要是记录一些比较有疑惑的语句,方便以后的查找以及回忆。

LIMIT子句

主要参考:https://www.yiibai.com/mysql/limit.html

LIMIT子句语法:

1
2
3
4
5
SELECT
column1,column2,...
FROM
table
LIMIT offset , count;

SQL我们来查看LIMIT子句参数:

offset参数:指定要返回的第一行的偏移量。第一行的偏移量为0,而不是1。

count:指定要返回的最大行数。

使用带有一个参数的LIMIT子句时,此参数将用于确定从结果集的开头返回的最大行数。

1
2
3
4
5
SELECT
column1,column2,...
FROM
table
LIMIT count;

SQL上面的查询等同:

1
2
3
4
5
SELECT
column1,column2,...
FROM
table
LIMIT 0 , count;

实例说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT productCode, productName, buyprice
FROM products
ORDER BY buyprice DESC;
+-------------+--------------------------------------+----------+
| productCode | productName | buyprice |
+-------------+--------------------------------------+----------+
| S10_4962 | 1962 LanciaA Delta 16V | 103.42 |
| S18_2238 | 1998 Chrysler Plymouth Prowler | 101.51 |
| S10_1949 | 1952 Alpine Renault 1300 | 98.58 |
| S24_3856 | 1956 Porsche 356A Coupe | 98.3 |
| S12_1108 | 2001 Ferrari Enzo | 95.59 |
| S12_1099 | 1968 Ford Mustang | 95.34 |
... ....
+-------------+--------------------------------------+----------+
110 rows in set

默认语句为输出最前面的几行:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;
相当于:
mysql> SELECT customernumber, customername, creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 0, 5; #从第一个偏移量的指定开始向下输出指定行数的所有内容。
+----------------+------------------------------+-------------+
| customernumber | customername | creditlimit |
+----------------+------------------------------+-------------+
| 141 | Euro+ Shopping Channel | 227600 |
| 124 | Mini Gifts Distributors Ltd. | 210500 |
| 298 | Vida Sport, Ltd | 141300 |
| 151 | Muscle Machine Inc | 138500 |
| 187 | AV Stores, Co. | 136800 |
+----------------+------------------------------+-------------+
5 rows in set

简要深入理解偏移量的作用:

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
36
mysql> SELECT productCode, productName, buyprice FROM products
ORDER BY buyprice DESC
LIMIT 1, 1; #偏移量从0开始,所以要指定从1开始,然后取一行记录,以行数为1的指定仅输出一行对应的内容。
+-------------+--------------------------------+----------+
| productCode | productName | buyprice |
+-------------+--------------------------------+----------+
| S18_2238 | 1998 Chrysler Plymouth Prowler | 101.51 |
+-------------+--------------------------------+----------+
1 row in set
#如下几个深入理解
mysql> SELECT productCode, productName, buyprice FROM products
ORDER BY buyprice DESC
LIMIT 0, 1; #这时指定第一行(偏移量默认为第一行取0)
+-------------+--------------------------------+----------+
| productCode | productName | buyprice |
+-------------+--------------------------------+----------+
| S10_4962 | 1962 LanciaA Delta 16V | 103.42 |
+-------------+--------------------------------+----------+
1 row in set
mysql> SELECT productCode, productName, buyprice FROM products
ORDER BY buyprice DESC
LIMIT 0, 2; #从已指定第一行偏移量开始输出以此为基础的指定行数的以下所有内容
+-------------+--------------------------------------+----------+
| productCode | productName | buyprice |
+-------------+--------------------------------------+----------+
| S10_4962 | 1962 LanciaA Delta 16V | 103.42 |
| S18_2238 | 1998 Chrysler Plymouth Prowler | 101.51 |
+----------------+------------------------------+-------------+
2 rows in set

总结:offset就是相当于一个定位,count就是要返回指定的最大行数。

ORDER BY ASC/DESC

ASC:[A~Z], [1~N]…

DESC:[Z~A}, [N~1]…

A,B,…Z。Z相当于最大的。

经度(Longitude) 纬度(Latitude)

经度(Longitude)东经西经

东经正数,西经为负数。越大越靠东。

纬度(Latitude)南纬北纬

北纬为正数,南纬为负数。越大越靠北。

方便记忆:东经,东北正。

参考:https://baike.baidu.com/item/%E7%BB%8F%E7%BA%AC%E5%BA%A6

---------------本文终---------------

文章作者:刘俊

最后更新:2019年01月02日 - 14:01

许可协议: 转载请保留原文链接及作者。