Mysql Query, subquery, and connection query analysis _ MySQL

Mysql Query, subquery, and connection query analysis: bitsCN.com I. five clauses of mysql Query: where (conditional query), having (filter), group by (group), order by (SORT),
Limit (limit the number of results) 1. Common where operators: comparison operators >,<,= ,! = (<>), >=, <= In (v1, v2.. vn) between v1 and v2 between v1 and v2 (including v1, v2) logical operators not (! ) Logical non-or (|) logic or and (&&) logic and where price> = 3000 and price <= 5000 or price> = 500 and price <= 1000 get the value of 500-1000 or 3000-5000 where price not between 3000 and 5000 is not between 3000 and 5000 like wildcard: % arbitrary character _ single character where goods_name like 'Nokia % 'where goods_name like 'Nokia N _' 2. group by grouping usually requires a group with a statistical function (aggregate function) it makes sense to use it together, for example: select goods_id, goods_name, cat_id, max (shop_price) from
Goods group by cat_id; good_name in the result obtained here is incorrect!
Because shop_price uses the max function, it is the largest, and the statement uses group by grouping,
Goods_name does not use an aggregate function. it is only the first item in cat_id and will not be changed because of shop_price.
Change the five statistical functions in mysql: (1) max: calculate the maximum value select max (goods_price) from goods. here, the maximum price value is obtained, only the value # query the select cat_id with the highest price in each column, max (goods_price) from goos group by cat_id; # find the highest price item number select goods_id, max (goods_price) from goods group by goods_id; (2) min: calculates the minimum value (3) sum: calculates the sum of the total number and # calculates the total number of goods inventories select sum (goods_number) from goods; (4) avg: average # calculate the average price of each item in the column select cat_id, avg (goods_price) from goods group by cat_id; (5) count: calculate the total number of rows # find the product type under each column select cat_id, count (*) from goods group by cat_id; ### consider each field name as a variable, it can be calculated ### example: the price of each product in this shop is much lower than the market price; select goods_id, goods_name, goods_price-market_price from goods; query the backlog of payment under each column select cat_id, sum (goods_price * goods_number) from goods
Group by cat_id; ### you can use as to get an alias for the calculation result ### select cat_id, sum (goods_price * goods_number) as hk from
Goods group by cat_id: not only can the column name be an alias, but also can be an alias for the form. 3. similarities and differences between having and where. having is similar to where. data can be filtered. how can I write the where expression, after having, how can I write where statements to play a role in the columns in the table? querying data having can play a role in the query results. filtering data # How much is the price of the store's products lower than the market price, select goods_id, good_name, market_price-shop_price as s from goods having s> 200; // where cannot be used here because s is the query result, where can only filter field names in tables. if where is used, select goods_id, goods_name from goods where market_price-shop_price> 200; # same Use where and having select cat_id, goods_name, market_price-shop_price as s from goods where cat_id = 3 having s> 200, select cat_id, sum (shop_price * goods_number) as t from goods group by cat_id having s> 20000 # average score of students who fail to query two or more subjects: # calculate the average score of all students, select name, avg (score) as pj from stu group by name; # select name, score <60 from stu; # Here score <60 is a judgment statement, so the result is true or false, In mysql, the true value is 1 false: 0 # select name, sum (score <60) as gk from stu group by name having gk> 1 for two or more failed students; # select name, sum (score <60) as gk, avg (score) as pj from stu group by name having gk> 1; 4. order by (1) order by price // Default ascending order (2) order by price desc // descending order (3) order by price asc // ascending order, same as default (4) order by rand () // randomly arranged with low efficiency # Sort by column number in ascending order, and select * from goods where cat_id in descending order of product prices under each column! = 2 order by cat_id, price desc; 5, limit [offset,] N offset, optional, if not written, it is equivalent to limit 0, N fetch entries # select good_id, goods_name, goods_price from goods order by good_price desc limit 3; ### query the most expensive item ideas in each column: # First, select cat_id, goods_id, goods_name, shop_price from goods order by cat_id, shop_price desc; # The first row of the item in each column in the query result above is the most expensive item # understand the query result as a temporary table [in memory] [subquery ]# select * from (select goods_id, goods_name, cat_id, shop_price
From goods order by cat_id, shop_price desc) as t group by cat_id; # Here we use group by cat_id because the first item in each column in the temporary table
Is the most expensive product, and group by does not use aggregate functions before, so the first row of data for each group is taken by default,
Here, the cat_id group is used to better understand the model: 1. the expression after the where clause puts the expression in each row to see if it is true. 2. the field (column) is interpreted as a variable, arithmetic operations (arithmetic operations and logical operations) 3. the result can be understood as a temporary Table 2, mysql subquery 1, where subquery (the inner query result is treated as a comparison condition of the outer query) # select goods_id, goods_name from goods where goods_id =
(Select max (goods_id) from goods); # retrieve the latest product under each topic (goods_id is unique) select cat_id, goods_id, goods_name from goods where goods_id in (select max (goods_id) from goods group by cat_id); 2. from subquery (the query result of the inner layer is used for another query by the outer layer) # Use a subquery to find out the average score of students with two or more subjects: # First, find out which students have two or more subjects: select name, count (*) as gk from stu where score <60
Having gk> = 2; # for the above query results, we only need the name, so we can take the name again select name from (select name, count (*) as gk from stu
Having gk> = 2) as t; # find these students, then calculate their average select name, avg (score) from stu where name in
(Select name from (select name, count (*) as gk from stu having gk> = 2) as t) group by name; 3. exists subquery (obtain the outer query result to the inner layer to check whether the inner layer is queried) # Query which columns have products, column table category, and item table goods select cat_id, cat_name from category where exists
(Select * from goods where goods. cat_id = category. cat_id );
III. usage of union (merge two or more query results to ensure that the number of queried columns is consistent,
The column types of the recommended query are the same. you can query multiple tables. if the column names are different for multiple query statements,
The first column name! If the values of each column in the rows retrieved from different statements are the same, the results will be automatically repeated,
If you do not want to repeat it, add all to declare it, that is, union all) # existing table a: id num a 5 B 10 c 15 d 10 Table B: id num B 5 c 10 d 20 e 99 find the same id and select id in the two tables, sum (num) from (select * from ta union select * from tb)
As tmp group by id; // the above query results are correctly output in this example. However, if the value of B in tb is
Change to 10. The value of B in the query result is 10. because B in ta is 10, a duplicate result will be filtered out after union,
In this case, union all select id, sum (num) from (select * from ta union all select * from tb) is used)
As tmp group by id; # take the products of columns 4th and 5, sort them in ascending order, and sort the prices of each column in descending order. use union to complete select goods_id, goods_name, cat_id, shop_price from goods
Where cat_id = 4 union select goods_id, goods_name, cat_id, shop_price from goods
Where cat_id = 5 order by cat_id, shop_price desc,
That is, sort the final merged results] # Take 3rd and 4 columns, and sort the first three items with the highest price in descending order of price (select goods_id, goods_name, cat_id, shop_price from
Goods where cat_id = 3 order by shop_price desc limit 3) union (select goods_id, goods_name, cat_id, shop_price from goods where cat_id = 4 order
Shop_price desc limit 3) order by shop_price desc; 4. left join and right join. There are 10 data records in Table a and 8 data records in Table B, so what is the Delica product of tables a and B? Select * from ta, tb // The output result is 8*10 = 80. 1. the left table is used for the left join. If no matching data exists, then fill in the vacancy with null,
Therefore, the number of output results> = the number of original data in the left table syntax: select n1, n2, n3 from ta left join tb on ta. n1 = ta. n2
[Here, the expression after "on" is not necessarily set to "=" or ">, <等算术、逻辑运算符]【连接完成后，
It can be viewed as a new table and queried using where.] # obtain the five most expensive products and display the product category names: select goods_id, goods_name, goods. cat_id, cat_name, shop_price
From goods left join category on goods. cat_id = category. cat_id order by shop_price
Desc limit 5; 2. right join a left join B is equivalent to B right join a. It is recommended to use left join instead of right join syntax: select n1, n2, n3 from ta right join tb on ta. n1 = ta. n2 3. the inner connection query result is the intersection of left and right connections.
(Distinct items)] mysql currently does not support external connections (that is, the Union of Left and Right join results, without removing null items) syntax: select n1, n2, n3 from ta inner join tb on ta. n1 = ta. n2 ######### example: existing table a name hot a 12 B 10 c 15 Table B: name hot d 12 e 10 f 10g 8 table a left join Table B, query the same hot Data select. *, B. * from a left join B on. hot = B. hot query result: name hot a 12 d 12 B 10 e 10 B 10 f 10 c 15 null from the above we can see that the query result table a has columns, the data in Table B only shows the items that meet the conditions. for example, if Table B connects to table a left, query the data with the same hot Data select. *, B. * from B left join a on. hot = B. the hot query result is: name hot d 12 a 12 e 10 B 10 f 10 B 10g 8 null and then, for example, Table a, right join Table B, select. *, B. * from a right join B on. hot = B. the hot query result is the same as the above B left join a ### exercise, query the product name, category, brand select goods_id, goods_name, goods. cat_id, goods. brand_id, category. cat_name, brand. brand_name
From goods left join category on goods. cat_id = category. cat_id left join
Brand on goods. brand_id = brand. brand_id limit 5; understanding: the result after each connection can be considered as a new table ### exercise. The following table create table m (id int, zid int, kid int, res varchar (10), mtime date) charset utf8; insert into m values (2006, 2, '2: 0', '2017-05-21 '), (, 2, 3, 2, '2: 1', '2014-06-21 '), (2006, 1, 3, '2: 2', '2014-06-11'), (2006, 1, '2: 4', '2017-07-01 '); create table t (tid int, tname varchar (10) charset utf8; insert into t values (1, 'shenhua '), (2, 'redbull '), (3, 'Rocket'); the style of the competition results between-06-01 and-01 must be printed as follows: the query statement of the rocket Red Bull 2006-06-11 is: select zid, t1.tname as t1name, res, kid, t2.tname as t2name, mtime from m left join t as t1 on m. zid = t1.tid left join t as t2 on m. kid = t2.tid where mtime between '2017-06-01 'and '2017-07-01'; conclusion: you can connect to the same table multiple times to retrieve data multiple times.
Author: cnbeirbitsCN.com