MySQL必知必会笔记(1-8章节)

检索数据SELECT语句

1.检索多个列

SELECT prod_id, prod_name, prod_price FROM products;

在这个例子中，指定了3个列名，列名之间用逗号分隔。

2.检索所有列

SELECT * FROM products;

如果给定一个通配符（*），则返回表中所有列。

3.检索不同的行(DISTINCT)

SELECT DISTINCT vend_id FROM products;

SELECT DISTINCT vend_id告诉MySQL只返回不同（唯一）的vend_id行，因此只返回4行，如下面的输出所示。如果使用DISTINCT关键字，它必须直接放在列名的前面。

4.限制结果(LIMIT)

SELECT prod_name FROM products LIMIT 5;

此语句使用SELECT语句检索单个列。 LIMIT 5指示MySQL返回不多于5行。为得出下一个5行，可指定要检索的开始行和行数，如下所示：

SELECT prod_name FROM products LIMIT 5,5;

5.使用完全限定的表名

SELECT products.prod_name FROM test.products LIMIT 5,5;

这里指定了一个完全限定的列名，和一个完全限定的表名(假定products表确实位于test数据库中）。

排序检索数据ORDER BY子句

1.排序数据

SELECT prod_name FROM products ORDER BY prod_name;

这条语句除了指示MySQL对prod_name列以字母顺序排序数据的ORDER BY子句外，与前面的语句相同。

2.按多个列排序

下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序。

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

重要的是理解在按多个列排序时，排序完全按所规定的顺序进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

3.指定排序方向(ASC和DESC)

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

下面的例子按价格以降序排序产品（最贵的排在最前面）：

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;

下面的例子以降序排序产品（最贵的在最前面），然后再对产品名排序：

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。

注意事项

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

过滤数据

1.使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

这条语句从products表中检索两个列，但不返回所有行，只返回prod_price值为2.50的行。

2.WHERE子句操作符

操作符	说明
=	等于
!=	不等于
<>	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

1.检查单个值

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';

检查WHERE prod_name=‘fuses’语句，它返回prod_name的值为Fuses的一行。MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配。

2.不匹配检查

以下例子列出不是由供应商1003制造的所有产品：

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

3.范围值检查

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

从这个例子中可以看到，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。 BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

4.空值检查

SELECT prod_name FROM products WHERE prod_price IS NULL;

这条语句返回没有价格（空prod_price字段，不是价格为0）的所有产品，由于表中没有这样的行，所以没有返回数据。

但是， customers表确实包含有具有空值的列，如果在文件中没有某位顾客的电子邮件地址，则cust_email列将包含NULL值：

SELECT cust_id FROM customers WHERE cust_email IS NULL;

注意事项

  WHERE子句的位置 在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后， 否则将会产生错误。

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

数据过滤

1.组合WHERE子句

为了进行更强的过滤控制， MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

1.AND操作符

为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。下面的代码给出了一个例子

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

2. OR操作符

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。请看如下的SELECT语句：

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;

3.计算次序

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。圆括号具有较AND或OR操作符高的计算次序。

SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

这条语句中，前两个条件用圆括号括了起来。因为圆括号具有较AND或OR操作符高的计算次序， DBMS首先过滤圆括号内的OR条件。这时， SQL语句变成了选择由供应商1002或1003制造的且价格都在10美元（含）以上的任何产品，这正是我们所希望的。

2.IN操作符

圆括号在WHERE子句中还有另外一种用法。 IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN取合法值的由逗号分隔的清单，全都括在圆括号中。下面的例子说明了这个操作符：

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;

如果你认为IN操作符完成与OR相同的功能，那么你的这种猜测是对的。下面的SQL语句完成与上面的例子相同的工作：

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;

IN操作符一般比OR操作符清单执行更快。

3.NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

下面的例子说明NOT的使用。为了列出除1002和1003之外的所有供应商制造的产品，可编写如下的代码：

SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

这里的NOT否定跟在它之后的条件，因此， MySQL不是匹配 1002 和 1003 的 vend_id ， 而是匹配 1002 和 1003 之外供应商的vend_id。

为什么使用NOT？对于简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中， NOT是非常有用的。例如，在与IN操作符联合使用时， NOT使找出与条件列表不匹配的行非常简单。

注意事项

MySQL支持使用NOT对IN 、BETWEEN 和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

用通配符进行过滤

  前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个还是多个值，测试大于还是小于已知值，或者检查某个范围的值，共同点是过滤中使用的值都是已知的。但是，这种过滤方法并不是任何时候都好用。

  例如，怎样搜索产品名中包含文本anvil的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符可创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含anvil的所有产品，可构造一个通配符搜索模式，找出产品名中任何位置出现anvil的产品。

1.LIKE操作符

  为在搜索子句中使用通配符，必须使用LIKE操作符。 LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

1. 百分号（ %）通配符

最常使用的通配符是百分号（%）。在搜索串中， %表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可使用以下SELECT语句：

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';

  此例子使用了搜索模式’jet%'。在执行这条子句时，将检索任意以jet起头的词。 %告诉MySQL接受jet之后的任意字符，不管它有多少字符。

  通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';

  搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

  通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以s起头以e结尾的所有产品：

SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

  重要的是要注意到，除了一个或多个字符外， %还能匹配0个字符。 %代表搜索模式中给定位置的0个、 1个或多个字符。

注意事项

区分大小写 根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写， 'jet%'与JetPack 1000将不匹配。

注意尾空格 尾空格可能会干扰通配符匹配。例如，在保存词anvil 时 ， 如 果 它 后 面 有 一 个 或 多 个 空 格 ， 则 子 句 WHERE prod_name LIKE '%anvil’将不会匹配它们，因为在最后的 l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。

注意NULL 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。

2. 下划线（ _）通配符

  另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。举一个例子：

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

  此WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配1，第二行中匹配2。 .5 ton anvil产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。

2. 使用通配符的技巧

  正如所见， MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

总之，通配符是一种极重要和有用的搜索工具，以后我们经常会用到它。