MySQL SELECT语法

2013-05-09_112859

这是MySQL 5实力养成暨评量里的3-41.‘为了在产品资料表格(product)中进行查询时,欲得到已经供货的供应商代号(supp_no),且自动去除重复的资料录,使每一供应商代号只显示一次,则可执行下列哪些SQL命令?

答案:C)Select DISTINCT supp_no From product; (D)Select DISTINCTROW supp_no From product;

[adsense][/adsense]

平常用惯了的DISTINCT,这回碰到DISTINCTROW就手足无措了,我们真的是识浅了。还是努力地看手册吧,请参考

MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.2 Data Manipulation Statements :: 13.2.9 SELECT Syntax

或简体手册

MySQL 5.1参考手册 :: 13. SQL语句语法::13.2. 数据操作语句::13.2.7. SELECT语法

繁体转译如下:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT用于恢复从一个或多个资料表中选择的行,并可以加入UNION语句和子查询。请参见13.2.7.2节,“UNION语法
13.2.8节,“Subquery语法”

·         每个select_expr都指示一个您想要恢复的列。

·         table_references指示行从哪个资料表或哪些资料表中被恢复。在13.2.7.1节,“JOIN语法”中对该语法进行了说明。

·         where_definition包括关键词WHERE,后面接一个资料表达式。该资料表达式指示被选择的行必须满足的条件。

有的行在计算时未引用任何资料表。SELECT也可以用于恢复这类行。

举例说明:

mysql> SELECT 1 + 1;
        -> 2

所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如,一个HAVING子句必须位于GROUP BY子句之后,并位于ORDER BY子句之前。

·         使用AS alias_name可以为select_expr给定一个别名。此别名用作资料表达式的列名,可以用于GROUP BY、ORDER BY或HAVING子句。例如:

·                mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
·                    -> FROM mytable ORDER BY full_name;

在为select_expr给定别名时,AS关键词是自选的。前面的例子可以这样编写:

mysql> SELECT CONCAT(last_name,', ',first_name) full_name
    -> FROM mytable ORDER BY full_name;

因为AS是自选的,如果您忘记在两个select_expr资料表达式之间加逗号,则会出现一个小问题:MySQL会把第二个资料表达式理解为一个别名。例如,在以下语句中,columnb被作为别名对待:

mysql> SELECT columna columnb FROM mytable;

因此,使用AS明确地指定列的别名,把它作为习惯,是一个良好的操作规范。

·         在一个WHERE子句中使用列别名是不允许的,因为当执行WHERE子句时,列值可能还没有被确定。请参见A.5.4节,“与列别名有关的问题”

·         FROM table_references子句指示行从哪些资料表中被恢复。如果您命名的资料表多于一个,则您在进行一个联合操作。要了解有关联合语法的说明,请参见13.2.7.1节,“JOIN语法”。对于每一个被指定的资料表,您可以自选地指定一个别名。

·                tbl_name [[AS] alias]
·                    [{USE|IGNORE|FORCE} INDEX (key_list)]

使用USE INDEX、IGNORE INDEX、FORCE INDEX可以向最佳化符提示如何选择索引。这部分内容在13.2.7.1节,“JOIN语法”中进行了讨论。

您可以使用SET max_seeks_for_key=value作为一种替代方法,来促使MySQL优先采用关键字扫瞄,替代资料表扫瞄。

·         您可以把当前资料库中的一个资料表作为tbl_name(在当前资料库中)引用,或作为db_name.tbl_name引用,来明确地指定一个资料库。您可以把一列作为col_nametbl_name.col_name引用或作为db_name.tbl_name.col_name引用。您不需要对一个列引用指定一个tbl_namedb_name.tbl_name前缀,除非此引用意义不明确。意义不明确时,要求指定明确的列引用格式。有关示范见9.2节,“资料库、资料表、索引、列和别名”

·         在没有资料表被引用的情况下,允许您指定DUAL作为一个假的资料表名。

·                mysql> SELECT 1 + 1 FROM DUAL;
·                        -> 2

有些伺服器要求一个FROM子句。DUAL仅用于与这些伺服器兼容。如果没有资料表被引用,则MySQL不要求该子句,前面的语句可以按以下方法编写:

mysql> SELECT 1 + 1;
        -> 2

·         使用tbl_name AS alias_nametbl_name alias_name可以为一个资料表引用起别名:

·                mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
·                    ->     WHERE t1.name = t2.name;
·                mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
·                    ->     WHERE t1.name = t2.name;

·         在WHERE子句中,您可以使用MySQL支援的所有函数,不过总计(总结)函数除外。请参见第12章:函数和操作符

·         被选择的用于输出的列可以使用列名称、列别名或列位置被引用到ORDER BY和GROUP BY子句中。列位置为整数,从1开始:

·                mysql> SELECT college, region, seed FROM tournament
·                    ->     ORDER BY region, seed;
·                mysql> SELECT college, region AS r, seed AS s FROM tournament
·                    ->     ORDER BY r, s;
·                mysql> SELECT college, region, seed FROM tournament
·                    ->     ORDER BY 2, 3;

要以相反的顺序进行分类,应把DESC(降序)关键字新增到ORDER BY子句中的列名称中。预设值为升序;该值可以使用ASC关键词明确地指定。

不建议使用列位置,因为该语法已经从SQL标准中删除。

·         如果您使用GROUP BY,则输出行根据GROUP BY列进行分类,如同您对相同的列进行了ORDER BY。MySQL对GROUP BY进行了延伸,因此您可以在各列(在子句中进行命名)的后面指定ASC和DESC:

·                SELECT a, COUNT(b) FROM test_table GROUP BY a DESC

·         MySQL对GROUP BY的使用进行了延伸,允许选择在GROUP BY子句中没有被提到的字段。如果您没有得到预期的结果,请阅读GROUP BY的说明,请参见12.10节,“与GROUP BY子句同时使用的函数和修改程式

·         GROUP BY允许一个WITH ROLLUP修饰符。请参见12.10.2节,“GROUP BY修改程式”

·         HAVING子句基本上是最后使用,只位于被发送给客户端的条目之前,没有进行最佳化。(LIMIT用于HAVING之后。)

SQL标准要求HAVING必须引用GROUP BY子句中的列或用于总计函数中的列。不过,MySQL支援对此工作性质的延伸,并允许HAVING因为SELECT清单中的列和外部子查询中的列。

如果HAVING子句引用了一个意义不明确的列,则会出现警告。在下面的语句中,col2意义不明确,因为它既作为别名使用,又作为列名使用:

mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

标准SQL工作性质具有优先权,因此如果一个HAVING列名既被用于GROUP BY,又被用作输出列清单中的起了别名的列,则优先权被给予GROUP BY列中的列。

·         HAVING不能用于应被用于WHERE子句的条目。例如,不能编写如下语句:

·                mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;

而应这么编写:

mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;

·         HAVING子句可以引用总计函数,而WHERE子句不能引用:

·                mysql> SELECT user, MAX(salary) FROM users
·                    ->     GROUP BY user HAVING MAX(salary)>10;

(在有些较早版本的MySQL中,本语句不运行。)

·         LIMIT子句可以被用于限制被SELECT语句返回的行数。LIMIT取一个或两个数字自变数,自变数必须是非负的整数常数(当使用已预备的语句时除外)。

使用两个自变数时,第一个自变数指定返回的第一行的偏移量,第二个自变数指定返回的行数的最大值。初始行的偏移量为0(不是1):

mysql> SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

为了与PostgreSQL兼容,MySQL也支援LIMIT row_count OFFSET offset语法。

如果要恢复从某个偏移量到结果集合的末端之间的所有的行,您可以对第二个参数是使用比较大的数。本语句可以恢复从第96行到最后的所有行:

mysql> SELECT * FROM tbl LIMIT 95,18446744073709551615;

使用1个自变数时,该值指定从结果集合的开头返回的行数:

mysql> SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

换句话说,LIMIT n与LIMIT 0,n等价。

对于已预备的语句,您可以使用位置保持符。以下语句将从tb1资料表中返回一行:

mysql> SET @a=1;
mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
mysql> EXECUTE STMT USING @a;

以下语句将从tb1资料表中返回第二到第六行:

mysql> SET @skip=1; SET @numrows=5;
mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql> EXECUTE STMT USING @skip, @numrows;

·         SELECT…INTO OUTFILE ‘file_name‘形式的SELECT可以把被选择的行写入一个档案中。该档案被建立到伺服器主机上,因此您必须拥有FILE权限,才能使用此语法。file_name不能是一个原有的档案。原有档案会阻止例如“/etc/passwd”的档案和资料库资料表被销毁。

SELECT…INTO OUTFILE语句的主要作用是让您可以非常快速地把一个资料表转储到伺服器机器上。如果您想要在伺服器主机之外的部分客户主机上建立结果档案,您不能使用SELECT…INTO OUTFILE。在这种情况下,您应该在客户主机上使用比如“mysql –e “SELECT …” > file_name”的命令,来生成档案。

SELECT…INTO OUTFILE是LOAD DATA INFILE的补语;用于语句的exort_options部分的语法包括部分FIELDS和LINES子句,这些子句与LOAD DATA INFILE语句同时使用。请参见13.2.5节,“LOAD DATA INFILE语法”

FIELDS ESCAPED BY用于控制如何写入特殊字元。如果FIELDS ESCAPED BY字元不是空字元,则被用于在输出中对以下字元设前缀:

o        FIELDS ESCAPED BY字元

o        FIELDS [OPTIONALLY] ENCLOSED BY字元

o        FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字元

o        ASCII 0(在编写时接在转义符后面的是ASCII ‘0’,而不是一个零值字节)

如果FIELDS ESCAPED BY字元是空字元,则没有字元被转义,并且NULL被作为NULL输出,而不是作为\N输出。指定一个空的转义符不是一个好的主意。特别是当您的数据中的字段值包含刚被给予的清单中的字元时,更是如此。

其原因是您必须对所有FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY或LINES TERMINATED BY字元进行转义,才能可靠地读取档案并返回。ASCII NUL被转义,以便更容易地使用调页程式观看。

生成的档案不必符合SQL语法,所以没有其它的字元需要被转义。

在下面的例子中,生成一个档案,各值用逗号隔开。这种格式可以被许多程式使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

·         如果您使用INTO DUMPFILE代替INTO OUTFILE,则MySQL只把一行写入到档案中,不对任何列或行进行终止,也不执行任何转义处理。如果您想要把一个BLOB值储存到档案中,则这个语句是有用的。

·         注释:任何由INTO OUTFILE或INTO DUMPFILE建立的档案都可以被伺服器主机上的所有用户编写。原因是,MySQL伺服器不能建立这样的档案,即档案的所有者不是该档案运行时所属的用户(任何时候,您都不能出于此原因或出于其它原因把mysqld作为根段运行)。该档案必须是全局可写的,这样您就可以操作其中的内容。

·         有的过程应在结果集合内处理数据。PROCEDURE子句用于对这些过程进行命名。要了解示范,请参见27.3.1节,“步骤分析”

·         储存引擎使用页面或行锁。如果您对储存引擎使用FOR UPDATE,则受到查询检验的行会被进行写锁定,直到当前事务结束为止。使用LOCK IN SHARE MODE可以设置一个共享锁。共享锁可以防止其它事务更新或删除被检验的行。请参见15.2.10.5节,“锁定读取SELECT … FOR UPDATE和SELECT … LOCK IN SHARE MODE”

在SELECT关键词的后面,您可以使用许多选项。这些选项可以影响语句的运行。

ALL, DISTINCT和DISTINCTROW选项指定是否重复行应被返回。如果这些选项没有被给定,则预设值为ALL(所有的匹配行被返回)。DISTINCT和DISTINCTROW是同义词,用于指定结果集合中的重复行应被删除

HIGH_PRIORITY, STRAIGHT_JOIN和以SQL_为开头的选项是MySQL相对于标准SQL的延伸。

·         HIGH_PRIORITY给予SELECT更高的优先权,高于用于更新资料表的语句。您应该只对查询使用HIGH_PRIORITY。查询速度非常快,而且立刻被执行。SELECT HIGH_PRIORITY查询在资料表被锁定用于读取时被发出。即使有一个新的语句正在等待资料表变为空闲,查询也会运行。

HIGH_PRIORITY不能和SELECT语句同时使用。SELECT语句是UNION的一部分。

·         STRAIGHT_JOIN用于促使最佳化符把资料表联合在一起,顺序按照这些资料表在FROM子句中排列的顺序。如果最佳化符联合资料表时顺序不佳,您可以使用STRAIGHT_JOIN来加快查询的速度。请参见7.2.1节,“EXPLAIN语法(获取关于SELECT的讯息)。STRAIGHT_JOIN也可以被用于table_references清单中。请参见13.2.7.1节,“JOIN语法”

·         SQL_BIG_RESULT可以与GROUP BY或DISTINCT同时使用,来告知最佳化符结果集合有很多行。在这种情况下,MySQL直接使用以磁盘为基础的临时资料表(如果需要的话)。在这种情况下,MySQL还会优先进行分类,不优先使用临时资料表。临时资料表对于GROUP BY组分带有关键字。

·         SQL_BUFFER_RESULT促使结果被放入一个临时资料表中。这可以帮助MySQL提前解开资料表锁定,在需要花费较长时间的情况下,也可以帮助把结果集合发送到客户端中。

·         SQL_SMALL_RESULT可以与GROUP BY或DISTINCT同时使用,来告知最佳化符结果集合是较小的。在此情况下,MySAL使用快速临时资料表来储存生成的资料表,而不是使用分类。在MySQL 5.1中,通常不需要这样。

·         SQL_CALC_FOUND_ROWS告知MySQL计算有多少行应位于结果集合中,不考虑任何LIMIT子句。行的数目可以使用SELECT FOUND_ROWS()恢复。请参见12.9.3节,“讯息函数”

·         如果您正在使用一个query_cache_type值,值为2或DEMAND,则SQL_CACHE告知MySQL把查询结果储存在查询缓存中。对于使用UNION的查询或子查询,本选项会影响查询中的所有SELECT。请参见5.13节,“MySQL查询高速缓冲”

·         SQL_NO_CACHE告知MySQL不要把查询结果储存在查询缓存中。请参见5.13节,“MySQL查询高速缓冲”。对于一个使用UNION或子查询的查询,本选项会影响查询中的SELECT。