Mysql study Note 1

Insert multiple records at a time: insertintotablename (field1, field2 ,..., Fieldn) values (record1_value1, record1_value2 ,..., Record1_valuen ),(...) The difference between having and where is that having filters conditions for aggregated results, while where filters records before aggregation. If the logic permits

Insert multiple records at a time: insert into tablename (field1, field2 ,..., Fieldn) values (record1_value1, record1_value2 ,..., Record1_valuen ),(...) The difference between having and where is that having filters conditions for aggregated results, while where filters records before aggregation. If the logic permits

1. Insert multiple records at a time:
   Insert into tablename (field1, field2 ,..., Fieldn)
   Values (record1_value1, record1_value2 ,..., Record1_valuen ),(...)
2. The difference between having and where is that having filters conditions for aggregated results, while where filters records before aggregation. If the logic permits, we try to use where to filter records first. As a result set is reduced, the aggregation efficiency is greatly improved. Finally, we can check whether having is used for filtering based on the logic.
3. Only two matching records are selected for the inner join, and other unmatched records are selected for the outer join. The outer join is divided into join and right join.
4. Record Union
   Use of union and union all, select... [Union] | [union all] select...
   Difference: the former is de-duplicated, and the latter is not de-duplicated.
5. Int (5), which refers to the width of a number. If it is smaller than 5 digits, it is filled with 0. It is not displayed by default. It is generally used with zerofill. The default value is 11. In addition, when the value exceeds the set parameter, no truncation or error occurs.
6. For decimal representation, MySQL can be divided into two methods: Floating Point Number and fixed point number. Floating Point Numbers include float (single precision) and double (double Precision), while the number of fixed points is only represented by decimal. The usage of (M, D) is non-standard. If you want to use it for database migration, you 'd better not use it like this. When no precision is specified for float and double, the actual precision (determined by the actual hardware and operating system) is used by default. Otherwise, the precision is rounded down. When decimal does not specify the precision, the default integer is 10, and the default decimal point is 0. Decimal is usually used to indicate data with high precision such as currency.
7. The TIMESTAMP type is 4 bytes, stored in numeric value, displayed as "YYYY-MM-DD HH: MM: SS" if the current system time is often inserted or updated, TIMESTAMP is used. To obtain the numeric value, add "+ 0" in the TIMESTAMP column ". In addition, the most important feature is that it is related to the time zone. However, it has a defect that has a small value range and is limited to one day from 19700101080001 to 2038.
8. Differences between char and varchar types: varchar is a variable string with a length range of 0 ~ 255 (earlier than MySQL 5.0.3) or 65535 (later than 5.0.3 ). During retrieval, spaces at the end of the CHAR column are deleted, while those at VARCHAR are retained.
9. When using concat (s1, s2 ,..., Sn) When splicing a string, note that the result of connecting any string to NULL will be NULL.
10. The STRCMP (s1, s2) function compares the ASCII value of a string. If the latter is smaller than the former,-1 is equal to 0; otherwise, 1 is used.
11. TRUNCATE (x, y) returns the result of truncating the number x to y decimal places, but only truncates, not rounding, and ROUND (x, y) rounding. For example, TRUNCATE (1.235, 2) gets 1.23.
12.