MySQL Common SQL statements

SELECT*FromTable1SELECT*From table1WHERE ID>=1and ID<=20and IDIn (1,2,3,4,5,6,7,8,9)OR (IDBetween1and10)SELECT*From Table3WHERE t3date>=‘2011-08-10‘SELECT*From table1WHERE IDIsNotNullGROUPby Id,t1nameORDERById,t1nameSELECT*From (SELECT A.*,b.t2valueFrom table1 ALeftJOIN Table2 bOn a.ID=B.t2table1id) ASELECT A.*,b.*From table1 ALeftJOIN Table2 bOn a.ID= B.t2table1id/*Display all the data in table A and all the data in table B that meet the conditions of the association, there are several rows in table B that satisfy the condition of a, then all the rows in table B are displayed, and the contents of the display portion of table A in the results are the same. If a piece of data in table A does not have associated data in table B, the result of the query results in a column with the B table being null NULL*/SELECT A.*,b.*From table1 ARightJOIN Table2 bOn a.ID= B.t2table1id/*Displays all data in table B with all the associated conditions in table A. If a piece of data in table B does not have associated data in table A, the query results that record corresponds to the column A is null NULL*/SELECT A.*,b.*From table1 AINNERJOIN Table2 bOn a.ID= B.t2table1id/*Show only the corresponding data in table B of Table A*/--Add the results of the two-statement query before and after the UNION, and the result will be returned with the same number of records with exactly the same field value. The field names and order of the two statement queries must be consistentSELECT field1From table1WHERE ID<10UNIONSELECT field1From table1WHERE ID>20--Returns the last ID value inserted by the databaseSELECT@ @IDENTITYAsIdSELECT last_insert_id ()AsIdUPDATE Table4 ALeftJOIN Table5 bOn a.table5_id= b.IDSET A.name= CONCAT (A.name,‘. html‘)WHERE b.id=1;--CONCAT (A.name, '. html ') CONCAT () returns two strings after they are concatenated--Batch InsertINSERTinto table1 (ID, t1name)VALUES (1,‘Name1‘), (2,‘Name2‘), (3,‘Name3‘);CREATETABLE Table6AsSELECT A.id,b.nameFrom Table4 ALeftJOIN Table5 bOn a.table5_id= b.IDWHERE b.id=1;--Create a new table with query results that you can use to copy tables--Add sort number to output resultSELECT (@i:=@i+1)As Num,b.test_field2FromTest_table ALeftJOIN Test_table2 bOn a.ID=B.TEST_TABLE_ID, (SELECT@i:=0)AsNum_table--========================================= common functions, where parameters are available as fields instead of--Summary functionsSELECTSUM (ID)From table1--Gets the and of the fields in parentheses;SELECTCOUNT (*)From table1--Get the number of arguments within parentheses;--Logical functionsSELECTIF (2>1,‘2 Greater than 1‘,‘2 Not greater than 1‘)--Judging the first argument, True returns the second argument, FALSE returns the third argument;SELECT Ifnull (Null0)--The first parameter is judged, NOT NULL, the first argument is returned, and the second argument is null;SELECTCase1When1Then‘This is 1.‘When2Then‘This is 2.‘When3Then‘This is 3.‘ENDAs a--If the value of the parameter after the case is equal to the value of the When, it returns the value of the when corresponding to that when, without limiting the number of when and then;SELECT CONCAT (‘A‘,‘B‘,‘C‘)--The parameter values in parentheses are concatenated and returned with no limit on the number of arguments.;--Take integer functionSELECT Ceil (1.2)--Rounding up to get the smallest integer greater than the parameter, output 2;SELECTFloor (1.2)--Rounding down to get the largest integer less than the parameter, output 1;SELECTROUND (1.2),ROUND (1.6)--Rounding rounded rounding;--Time functionSELECT Now ()--Gets the current formatted time output 2014-10-16 00:26:04;SELECT Unix_timestamp (now ())--Gets the timestamp of the formatted time within the parentheses, and returns the current timestamp with a null parenthesis;SELECT Unix_timestamp (Utc_date ())--0-point time stamp of the day;SELECT From_unixtime (1413388191,‘%y-%m-%d%h:%i:%s‘)-- According to the second parameter format time, the second parameter can be not written, output 2014-10-15 23:49:51;-- mixed app select if (1  is not null,concat ( "1 ",  not  '  Null '  is Nullas A;       

MySQL Common SQL statements