MySQL Select Operation

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}]  [  Procedure procedure_name (argument_list)  ]      [  For update | lock in share mode  ] ]

SelectUsed to restore selected rows from one or more tables, and can be addedUnionStatement and subquery.

EachSelect_exprIndicates a column you want to restore, Table_referencesIndicates the table or table from which the row is restored.

 

Simple Query

 
SelectColumna columnbFromMytable;
 

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  ;  -- To classify data in 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

 Select A, Count (B) From Test_table Group   By ADesc  ;  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 ;  --  Having clauses can reference Aggregate functions, while where clauses cannot:  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  ;  --  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 restores all rows from the first row to the last row:  Select   *   From TBL limit 95 , 18446744073709551615 ;

 

Select... into OUTFILE

Select... into OUTFILE'File_name'FormatSelectYou can write the selected row into a file. This file is created on the server host, so you must haveFilePermission to use this syntax.File_nameIt cannot be an original file.

Select... into OUTFILEThe main function of a statement is to quickly dump a table to a server machine. You cannot useSelect... into OUTFILE. In this case, you should use theMySQL-E"Select..."> file_nameTo generate files.

Select... into OUTFILEYesLoad data infileThe complementExort_optionsSome syntaxes include someFieldsAndLinesClause.Load data infileStatement.

In the following example, a file is generated and values are 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 useInto dumpfileReplaceInto OUTFILE, ThenMySQLOnly one row is written to the file. No column or row is terminated, and no escape is performed. If you wantBlobThis statement is useful when values are stored in files.

 

Union

UnionUsedSelectStatement results are combined into a result set. The syntax is as follows:

 
Select...Union [All | distinct]Select...[Union [All | distinct]Select...]
 

Column in eachSelectThe columns selected at the corresponding position of the 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 .) In the firstSelectThe column name used in the statement is also the name of the Column Used for the result.

If youUnionDo not use keywordsAllAll returned rows are unique, as if you have usedDistinct. If you specifyAll, You will useSelectStatement.

You can mixUnion allAndUnion distinct. MixedUnionThe type is treated in this way, that isDistictThe shared body overwrites allAllShared body.DistinctThe shared body can be used.Union distinctExplicitly generate or useUnion(Not followedDistinctOrAllKeyword) is generated implicitly.

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, distinctAndDistinctrow

All, distinctAndDistinctrowOption to specify whether repeated rows should be returned. If these options are not given, the default value isAll(All matching rows are returned ).DistinctAndDistinctrowIs a synonym used to specify that duplicate rows in the result set should be deleted.

Select DistinctAFromTable_name;Select Count(DistinctA)FromTable_name;
 

 

From http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#select