Mysql select statement operation instance, mysqlselect

Mysql select statement operation instance, mysqlselect

Select syntax

Copy codeThe Code is 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}]
[PROCEDURE procedure_name (argument_list)]
[For update | lock in share mode]

SELECT is used to restore the rows selected from one or more tables, and can be added to the UNION statement and subquery.

Each select_expr indicates a column you want to restore, and table_references indicates the table from which the row is restored or which table.

Simple Query

Copy codeThe Code is as follows:
SELECT columna columnb FROM mytable;

Order by Query

Copy codeThe Code is 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 classify data in the reverse ORDER, add the DESC (descending ORDER) keyword to the column name in the order by clause. The default value is ascending. The value can be explicitly specified using the ASC keyword.
SELECT a, COUNT (B) FROM test_table ORDER BY a DESC;

Group by Query

Copy codeThe Code is as follows:
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 cannot be used for entries that should be used in the WHERE clause, and the following statements cannot be written:
SELECT col_name FROM tbl_name HAVING col_name> 0;
-- This should be written
SELECT col_name FROM tbl_name WHERE col_name> 0;

The -- HAVING clause can reference the aggregate function, but the WHERE clause cannot reference:
SELECT user, MAX (salary) FROM users group by user having max (salary)> 10;

LIMIT Query

Copy codeThe Code is as follows:
SELECT * FROM tbl LIMIT 10; # Retrieve rows 0-9;
SELECT * FROM tbl LIMIT 5, 10; # Retrieve rows 6-15;

-- To restore all rows from an offset to the end of the result set, you can use a large number for the second parameter.
-- The following statement can restore all rows from the first row to the last row:
SELECT * FROM tbl LIMIT 95,18446731673709551615;

SELECT... INTO OUTFILE

SELECT... into outfile 'file _ name' can write the selected row INTO a file. This FILE is created on the server host. Therefore, you must have the FILE permission to use this syntax. File_name cannot be an existing file.

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

SELECT... into outfile is the complement of load data infile. The syntax used for the exort_options part of the statement includes some FIELDS and LINES clauses, which are used together with the load data infile statement.

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

Copy codeThe Code is as follows:
SELECT a, B, a + B INTO OUTFILE '/tmp/result. text'
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ N'
FROM test_table;

If you use into dumpfile instead of into outfile, MySQL writes only one row to the file, and 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

UNION is used to combine the results from many SELECT statements into a result set. The syntax is as follows:

Copy codeThe Code is as follows:
SELECT...
UNION [ALL | DISTINCT]
SELECT...
[UNION [ALL | DISTINCT]
SELECT...]

Columns selected at the corresponding position of each SELECT statement should have 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 other statements .) The column name used in the first SELECT statement is also the name of the Column Used for 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 ALL is specified, ALL matched rows are obtained from ALL used SELECT statements.

You can mix union all and union distinct in the same query. The mixed UNION type is treated in this way, that is, the DISTICT shared body overwrites ALL the shared bodies located on the left. The DISTINCT shared body can be explicitly generated using union distinct, or implicitly generated using UNION (without the DISTINCT or ALL keyword.

Simple Example:

Copy codeThe Code is 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

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

Copy codeThe Code is as follows:
Select distinct a FROM table_name;
Select count (DISTINCT a) FROM table_name;