MySQL Common statements

1. Basic Enquiry: (1) I only want to query a column of a table: SELECT col1,col2 from table1 filter out the column fields; (2) When I want to query the information of a row: use where col1=1 and col2=2 to filter out the rows; (3) We can use GROUP by to aggregate data, when aggregated, the fields shown are generally summary functions; (4) We can use having to filter out aggregated data; (5) We can use order by to sort the results of our query, such as order by col2; Explanation: Select is used to query key fields; distinct's key field returns a unique result between A and B: used to limit the range of a value; like: The text data used to filter a column; In: Whether a value is inside 2. Data operation: (1) I want to update a row of data in the table with a col1 value of col2 equal to 2 to 1: Update table1 set col1=1 where col2=2 (2) I want to manually insert data into the Table1: INSERT into table (COL1,COL2) Values (a); (3) Query insert data, this development must use: INSERT INTO table1 (col1) Select col2 from Table2 3. View (use to complex query) CREATE VIEW View1 as select Co1 from Ta Ble1 when using select from View1 4. Correlation table Query: (1) Left association: All rows are from the main table, even if the child table does not, generally include more to be taken as the main table, (2) Within the connection: the main table and the child table have rows; (3) Right association and left associative meaning; 5. The associated query updates the data (this seems very powerful) Update T1 set a=1 from table1 t1 join Table2 T2 on T1.id=t2.id where t1.col1=0 and t2.col2 are null; 6. Sub-query: Select from table1 where ID in (select ID from table2 where date>current_timestamp) 7. Index to create index index1 on t Able1 (ID) index can speed up our delete and update operations 8. Useful tool Functions (1) The string becomes a date classType: To_date (oracle,postgresql), Str_to_date (Musql) (2) Returns a field that is not null for the first field: Coalesce (col1,col2, "defualt"); (3) Returns the current time Current_timestamp (4) Operations on two query results: Union (returns all data for two tables) except (all rows must have only the primary table, and the child table cannot have) Intersect: Row 9 for each table. Summary function: Count: Returns the number of rows sum: Returns all and AVG of the column: Returns the average of the column Min/max: Returns the maximum or minimum value of the column for a specific reference:
pwq857
Links: http://www.imooc.com/article/10779
Source: MU-Class Network
This article original published in Mu class network, reproduced please indicate the source, thank you for your cooperation!

MySQL common statements (GO)