Mysql注意事项（一）

最近回顾了一下MySQL，发现了一些MySQL需要注意的事项，同时也作为学习笔记，记录下来。–2020年05月13日

1、通配符*

检索所有的列。

不建议使用

通常，除非你确定需要表中的每个列，否则最好别使用*通配符，虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

优点

由于不明确指定列名（因为星号检索每个列），所以能检索出名字未知的列。

2、DISTINCT

用于检索不同的行（去重）。

不能部分使用DISTINCT

DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id,prod_price FROM products ；会查找出vend_id和prod_price都不相同的内容，而不是vend_id去除重复，除非指定的两个列都不同，否则所有列都将被检索出来。

示例：

表数据：z

SELECT * FROM products;

结果：共14条记录

表数据

使用DISTINCT获取供应商：

SELECT DISTINCT vend_id FROM products;

结果：共4条记录

获取供应商

部分使用DISTINCT：

SELECT DISTINCT vend_id,prod_price FROM products;

结果：共12条记录

部分使用DISTINCT

结果查出了vend_id和prod_price都不相同的内容；

3、LIMIT

限制结果，指定返回的行

使用方法

示例：

SELECT prod_name 
FROM products
LIMIT 5;

结果：返回前5行

LIMIT使用

示例：获取行3开始的4行，即第4至第8行

SELECT prod_name 
FROM products
LIMIT 3,4;

等价于：从行3开始取4行

SELECT prod_name 
FROM products
LIMIT 4 OFFSET 3;

LIMIT使用

注意：行0

行0 ：检索出来的第一行为行0而不是行1.因此，LIMIT 1,1将检索出第二行而不是第一行。

4、ORDER BY

排序数据，降序DESC，升序ASC（默认，即如果不指定DESC、也不指定ASC，则默认为ASC）

在多个列上降序排序

如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

ORDER BY 子句的位置

在给出ORDER BY 子句时，应该保证它位于FROM子句之后，如果使用LIMIT，它必须位于ORDER BY 之后。使用子句的次序不对将产生错误消息。

5、WHERE

过滤数据

WHERE子句的位置

在给出WHERE子句时，应该保证它位于FROM子句之后，如果同时使用ORDER BY，应该让ORDER BY位于WHERE 之后，否则将会产生错误。
BETWEEN 范围值检查

BETWEEN匹配范围中的所有值，包括指定的开始值和结束值。
NULL 空值检查

NULL 无值，它与字段包含0,、空字符串或仅仅包含空格不同。
NULL与不匹配

IS NULL 和 IS NOT NULL 即为空和不为空
```
--IS NULL
SELECT cust_id 
From customers
where cust_email IS NULL;-- IS NOT NULL
SELECT cust_id 
From customers
where cust_email  IS NOT NULL;
```
在过滤数据时，一定要验证返回数据中确定给出了被过滤列具有NULL的行。
AND、OR

注意：AND比OR的优先级更高，建议在WHERE子句中使用圆括号；

示例：（未使用圆括号）
```
SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;
```
结果：

分析：

SQL在处理OR操作符之前，优先处理AND操作符。当SQL看到上述的WHERE子句时，它理解为由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品，不管其价格如何。换句话说，由于AND在计算次序中优先级更高，操作符被错误的组合了。
IN、NOT
- IN操作符
  
  与OR操作符相比较，IN有如下优点（建议替换OR）：
  - 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观；
  - 在使用IN时，计算次序更容易管理（因为使用的操作符更少）；
  - IN操作符一般比OR操作符清单执行更快；
  - IN最大的优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
- NOT操作符
  
  使用NOT对IN、BETWEEN和EXISTS子句取反。

6、LIKE、通配符%和_

LIKE指示MYSQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较，匹配整个列；

示例1：

SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'

结果：不会返回prod_name为1000的行

%表示任何字符出现任意次数；

示例2：使用了通配符%的LIKE

SELECT prod_name
FROM products
WHERE prod_name LIKE '%1000'

结果：不会返回prod_name为1000的行

LIMIT使用

_表示任意单个字符出现一次。

通配符搜索的处理一般比其他搜索所花时间更长。

NULL与%通配符

%通配符几乎可以匹配任何东西，但是不能匹配NULL

通配符使用技巧

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确定需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始出。把通配符置于搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的配置。如果放错地方，可能不会返回想要的数据。

7、日期和时间处理函数

MySQL使用的日期格式为yyyy-mm-dd；

日期比较

示例：

SELECT cust_id,order_num,order_date
FROM orders
WHERE order_date='2005-09-01'

结果：

LIMIT使用

注意：使用*WHERE order_date=’2005-09-01*‘ 可靠吗？

order_date的数据类型为datetime，这种类型存储日期和时间值，表中的时间值为*00:00:00*，但实际中很可能并不总是这样。比如：order_date值为2005-09-01 11:30:05，则*WHERE order_date=’2005-09-01’*失败，即使给出具有该日期的一行，也不会检索出来，因为WHERE匹配失败。

解决方案：

使用Date()函数。*Date(order_date)*函数指示MySQL仅提取列的日期部分，更可靠的SELECT语句为：

SELECT cust_id,order_num,order_date
FROM orders
WHERE DATE(order_date)='2005-09-01'

结果：

LIMIT使用

建议

如果要的是日期，请使用Date()。

如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你如果知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time()函数，在你只想要时间时应该使用它。

8、聚集函数

AVG()函数

通过对表中行数计算并计算特定列值之和，求得该列的平均值。

只能用于单列 。AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

NULL值 。AVG()函数忽略列值为NULL的行。

COUNT()函数

使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值；
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

示例1：

SELECT COUNT(*) AS num_cust
FROM customers;

结果1：对所有行计数，不管行中各列有什么值

LIMIT使用

示例2：

SELECT COUNT(cust_email) AS num_cust
FROM customers;

结果2：对cust_email列中有值的计数

LIMIT使用

### MAX()函数、MIN()函数

MAX()返回指定列中的最大值；

MIN()返回指定列中的最小值。

对非数值数据使用MAX()或MIN() 返回文本列中的最大值或最小值。

SUM()函数

用来返回指定列值的和（总计）。

示例1：

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num=20005;

结果1：

LIMIT使用

在多列上进行计算 利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

示例2：

SELECT SUM(item_price*quantity) AS items_ordered
FROM orderitems
WHERE order_num=20005;

结果2：

LIMIT使用

聚集不同值

对聚合函数AVG()、COUNT()、MAX()、MIN()、SUM()的使用：

对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
只包含不同的值，指定DISTINCT参数。

使用DISTINCT，查询特定供应商提供的产品的平均价格。

示例：

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id=1003;

结果：使用了DISTINCT，平均值只考虑各个不同的价格

LIMIT使用

注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT()，因此不允许使用COUNT(DISTINCT)*，否则会产生错误。类似的，DISTINCT必须使用列名，不能用于计算或表达式。

Mysql注意事项（一）