MySQL SELECT statement Operation instance _mysql

The syntax of the Select

Copy Code code as follows:

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 recover rows selected from one or more tables and to join Union statements and subqueries.

Each select_expr indicates a column that you want to recover, table_references indicates which table or tables the row was recovered from.

Simple query

Copy Code code as follows:

SELECT Columna columnb from MyTable;

ORDER BY Query

Copy Code code as follows:

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;

--to categorize in reverse order, you should add the desc (descending) keyword to the column name in the orders by clause. The default value is ascending, which can be specified explicitly using the ASC keyword.
SELECT A, COUNT (b) from test_table order by a DESC;

GROUP by Query

Copy Code code as follows:

SELECT A, COUNT (b) from Test_table GROUP by a DESC;
SELECT COUNT (col1) as col2 from T GROUP by col2 has col2 = 2;

--The having cannot be used for an entry that should be used in a WHERE clause, the following statement cannot be written:
SELECT col_name from Tbl_name have col_name > 0;
--and that's how it should be written
SELECT col_name from Tbl_name WHERE col_name > 0;

The--having clause can refer to the total function, and the WHERE clause cannot reference:
SELECT user, Max (salary) from users GROUP by user has Max (salary) >10;

Limit Query

Copy Code code as follows:

SELECT * from TBL LIMIT 10; # Retrieve rows 0-9;
SELECT * from TBL LIMIT 5, 10; # Retrieve rows 6-15;

--If you want to restore all rows from an offset to the end of the result set, you can use a larger number for the second parameter.
--The following statement restores all rows from line 96th to last:
SELECT * from TBL LIMIT 95,18446744073709551615;

SELECT ... into outfile

SELECT ... A select in the form outfile ' file_name ' can write the selected row 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.

SELECT ... The main function of the into OUTFILE statement is that you can quickly dump a table onto the server machine. If you want to create a result file on a partial client host other than the server host, you cannot use the Select ... into outfile. In this case, you should use the command on the client host, such as "Mysql–e" SELECT ... "> file_name" to generate the file.

SELECT ... Into outfile is the complement of the load data infile; The syntax for the exort_options part of the statement includes partial fields and lines clauses that are used concurrently with the load data infile statement.

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

Copy Code code as follows:

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, does not terminate any columns or rows, and does not perform any escape processing. This statement is useful if you want to store a BLOB value in a file.

UNION

The union is used to combine the results from many SELECT statements into a single result set, and the syntax is as follows:

Copy Code code as follows:

SELECT ...
UNION [All | DISTINCT]
SELECT ...
[UNION [All | DISTINCT]
SELECT ...]

The selected columns that are listed at the corresponding location of each SELECT statement should have the same type. (for example, the first column selected by the first statement should have the same type as the first column selected by other statements.) The column name used in the first SELECT statement is also used for the column name of the result.

If you do not use the keyword all for union, all returned rows are unique, as if you have used distinct for the entire result set. If you specify all, you get all the matching rows from all the used SELECT statements.

You can mix UNION all and union DISTINCT in the same query. The mixed union type is treated in this manner, that is, the distict common body overwrites all the all shared bodies located on its left. The distinct common body can be explicitly generated using union distinct or implicitly generated using union (followed by no distinct or all keywords).

Simple example:

Copy Code code as follows:

(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 that specify that duplicate rows in the result set should be deleted.

Copy Code code as follows:

SELECT DISTINCT a from table_name;
SELECT COUNT (DISTINCT a) from table_name;