Understand select statement operation examples in mysql

Select syntax

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 is used to restore selected rows from one or more tables and can joinUNIONStatement and subquery.
Each select_expr indicates a column that you want to restore, and table_references indicates the table or tables from which the row is restored.
Simple query

SELECT columna columnb FROM mytable;

ORDER BY query

SELECT college, region, seed FROM tournament  ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;

SELECT college, region, seed FROM tournament  ORDER BY 2, 3;
--要以相反的顺序进行分类，应把DESC（降序）关键字添加到ORDER BY子句中的列名称中。默认值为升序；该值可以使用ASC关键词明确地指定。

SELECT a, COUNT(b) FROM test_table ORDER BY a DESC；

GROUP BY query

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

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
-- HAVING不能用于应被用于WHERE子句的条目,不能编写如下语句：

SELECT col_name FROM tbl_name HAVING col_name > 0;

--而应该这么编写

SELECT col_name FROM tbl_name WHERE col_name > 0;
--HAVING子句可以引用总计函数，而WHERE子句不能引用：

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

LIMIT query

SELECT * FROM tbl LIMIT 10;  # Retrieve rows 0-9;

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15;
--如果要恢复从某个偏移量到结果集合的末端之间的所有的行，您可以对第二个参数是使用比较大的数。

--以下语句可以恢复从第96行到最后的所有行：

SELECT * FROM tbl LIMIT 95,18446744073709551615;

SELECT...INTO OUTFILE

SELECT...INTO OUTFILE The 'file_name' form of SELECT can write the selected lines to a file. The file is created on the server host, so you must have FILE permissions to use this syntax. file_name cannot be an original file.

The main function of the SELECT...INTO OUTFILE statement allows you to dump a table to the server machine very quickly. If you want to create the result file on a client host other than the server host, you cannot use SELECT...INTO OUTFILE. In this case, you should use a command such as "mysql -e "SELECT ..." > file_name" on the client host to generate the file.

SELECT...INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax used for the exort_options part of the statement includes partial FIELDS and LINES clauses, which are used simultaneously with the LOAD DATA INFILE statement.

In the following example, a file is generated with each value separated by commas. This format can be used by many programs

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' 

FIELDS TERMINATED BY ',' 

OPTIONALLY ENCLOSED BY '"' 

LINES TERMINATED BY '\n'

FROM test_table;

If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row to the file without terminating any columns or rows, and No escaping is performed. This statement is useful if you want to store a BLOB value to a file.
UNION
UNION is used to combine the results from many SELECT statements into a result set. The syntax is as follows:

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

is listed in each SELECT statement The selected columns at corresponding positions should be of the same type. (For example, the first column selected by the first statement should be of the same type as the first column selected by the other statements.) The column names used in the first SELECT statement are also used for the column names of the result.
If you do not use the keyword ALL for UNION, all returned rows are unique as if you had used DISTINCT for the entire result set. If you specify ALL, you get all matching rows from all SELECT statements used.
You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated in such a way that the DISTICT union covers all ALL unions to its left. DISTINCT unions can be generated explicitly using UNION DISTINCT, or implicitly using UNION (without the DISTINCT or ALL keywords following).
Simple example:

(SELECT a FROM tbl_name WHERE a=10 AND B=1)

UNION

(SELECT a FROM tbl_name WHERE a=11 AND B=2)

ORDER BY a LIMIT 10;

ALL, DISTINCT and DISTINCTROW
ALL, DISTINCT and DISTINCTROW options specify whether duplicate rows should be returned. If these options are not given, the default value is ALL (all matching rows are returned). DISTINCT and DISTINCTROW are synonyms and are used to specify that duplicate rows in the result set should be removed.

SELECT DISTINCT a FROM table_name;

SELECT COUNT(DISTINCT a) FROM table_name;

The above is the detailed content of Understand select statement operation examples in mysql. For more information, please follow other related articles on the PHP Chinese website!