Knowledge points and details of mysql

The knowledge and details of mysql have been read over the mysql system over the past few days ......
1. the char/varchar type differs from the char fixed-length string. The length is fixed to the declared length (0-255) when the table is created. If the length is insufficient, spaces are filled on the right of the tables to achieve the declared length. When the CHAR value is retrieved, the spaces at the end of the string are deleted. The varchar variable-length string (0-65535) is deleted. When the VARCHAR value is saved, only the required characters are saved, add another byte to record the length (if the declared length of a column exceeds 255, two bytes are used). The varchar value is not filled when it is saved. When the value is saved and retrieved, the trailing space is retained. Www.2cto.com they are retrieved in different ways, char speed is relatively faster note that their length is expressed as the number of characters, both Chinese and English are so long

Text Search is a little slow, so if it is not very large, use char/varchar, and text cannot be added with the default value 2. in windows, the mysql client contains garbled content because the system code is gbk. You can simply send a "set names utf8" statement before using it. decimal: float (M, D), double (M, D), decimal (M, D) M represents the total number of digits, excluding the decimal point, D represents the decimal point, such as (5, 2) -999.99 -- 999.99 M indicates the total number of decimal places, and D indicates the number of digits after the decimal point. If M and D are omitted, the values are saved according to the restrictions allowed by the hardware. The single-precision floating point number is precise to about 7 decimal places. 4. the combination of group by and Aggregate functions makes sense (sum, avg, max, min, count). The principle of group by is that all columns after select, columns without Aggregate functions must appear after group by. If not, only the results of the first row in each type are returned.

If you want to query the most expensive products under each topic: id, price, product name, type. Use select goods_id, cat_id, goods_name, max (shop_price) from goods group by cat_id; the product name of the query result, id does not match the most expensive product. If order by is added? This is also incorrect because Select statements are executed in sequence (before and after the syntax): The where clause filters record rows based on the specified conditions. The group by clause divides data into multiple groups; aggregate functions are used for calculation. www.2cto.com uses the having clause to filter groups. All expressions are calculated. order by is used to sort result sets.
You can use the following query statement: select * from (select goods_id, cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc) as tmp group by cat_id; 5. the having clause is slower than the aggregate statement (sum, min, max, avg, count) in the query process ). the where clause is faster than the aggregate statement (sum, min, max, avg, count) in the query process ). Simply put: where clause: select sum (num) as RMB from order where id> 10 // an aggregate statement can be executed only when records with IDs greater than 10 are first queried.
Having clause: select reportsto as manager, count (*) as reports from employees group by reportsto having count (*)> 4 The following statement is incorrect: select goods_id, cat_id, market_price-shop_price as sheng where cat_id = 3 where sheng> 200; should be changed to: select goods_id, cat_id, market_price-shop_price as sheng where cat_id = 3 having sheng> 200; where for the columns in the table, query data. having is used to filter data for the columns in the query results. For more information, see www.2cto.com.

Query: average score of two or more failed students: select name, count (score <60) as s, avg (score) from stu group by name having s> 1; this statement does not work. First, find out the following two points: a, count (exp), no matter what the parameter is, the number of rows is queried, the parameter results are not affected, as shown in figure



B. You can use the following statement to replace count with sum:

Or: select name, avg (score) from stu group by name having sum (score <60)> 1; Statement 6. subquery. where subquery: use the results of the inner query as the comparison condition of the outer query. Eg: query the latest product select max (goods_id), goods_name from goods; error: Mixing of GROUP columns (MIN (), MAX (), COUNT (),...) with no GROUP columns is illegal if there is no group by clause, you can use this query: select goods_id, goods_name from goods where goods_id = (select max (goods_id) from goods ); www.2cto.com query the latest items in each column: select goods_id, cat_id, goods_name from goods where goods_id in (select max (goods_id) from goods group by cat_id );
B. from subquery: Use the query result of the inner layer as a temporary table for the outer SQL statement to query the latest item select * from (select goods_id, cat_id, goods_name from goods order by cat_id asc, goods_id desc) as t group by cat_id; 5. The average score of the two or more students involved in the query is select sname from (select name as sname from stu) as tmp; c. exists subquery: Get the result variable of the outer query to the inner layer to check whether the inner layer query is used to query the columns with products: select cat_id, cat_name from category where exists (select * from goods where goods. cat_id = category. cat_id );
Because there are no conditions, all columns will be identified: select cat_id, cat_name from category where exists (select * from goods); 7. in (v1, v2 -----) between v1 and v2 (including v1, v2) like (%, _) order by column1 (asc/desc), column2 (asc/desc) first sort by the first, then sort by the second 8. union merges the query results twice or multiple times to ensure that the columns of the two queries are consistent. If the types of the columns of the corresponding columns are different, retrieve the column name of the first SQL statement. If the values of the rows retrieved from different statements are the same, the same rows will be merged (repeated ), if union all is not reused to specify that if the clause contains order by, the limit clause must add (), select * from ta union all select * from tb;
Take the fourth item, and sort the prices in descending order. You also want to take the fifth item, and the prices are also sorted in descending order (select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price desc) union (select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 5 order by shop_price desc ); it is recommended that www.2cto.com be placed after all clauses, that is, sorting the final Merged Results (select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price desc) union (select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 5 order by shop_price desc );
9. join query left join: select column1, column2, columnN from ta left join tb on ta column = tb column [here the table is connected to a large table and is regarded as a normal table] where group, having .... write right join as usual: select column1, column2, columnN from ta right join tb on ta column = tb column [here the table is connected to a large table, look at it as a normal table] www.2cto.com where group, having .... write-in join as usual: select column1, column2, columnN from ta inner join tb on ta column = tb column [here the table is connected to a large table and is regarded as a normal table] where group, having .... as usual, the left join is based on the left table. The right table is used to find matching data. If no matching columns exist, null is used to complete the query. If multiple columns exist, the following two tables are listed:

Select boy. *, girl. * from boy left join girl on boy. flower = girl. flower; Result:

Left-side connections can be converted to each other. We recommend that you use left-side connections. Database transplantation facilitates connections within www.2cto.com: query the data (intersection of left-side connections) in both the left and right tables and select a matched combination.
The Left or Right join query specifies the data of the table, and the default (left and right join is not specified) is the column data in both tables, that is, inner join mysql does not support outer join, that is, the Union of left and right joins. When fields in multiple tables need to be specified in which table the fields in the three tables are connected to query brand, goods, categoryselect g. goods_id, cat_name, g. brand_id, brand_name, goods_name from goods g left join brand B on B. brand_id = g. brand_id left join category c on g. cat_id = c. cat_id; author ljfbest