Mysql千万級データ照会まとめ

CREATE TABLE `mall_goods` (
  `goods_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '  ID',
  `cat_id` INT(11) NOT NULL DEFAULT '0' COMMENT '  id',
  `price` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '    ',
  `state` TINYINT(3) DEFAULT '1' COMMENT '  （1:  ，0:  ）',
  PRIMARY KEY (`goods_id`),
  KEY `icatid` (`cat_id`)
) ENGINE=INNODB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8
CREATE TABLE `mall_category` (
  `cat_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '  id',
  `cat_name` VARCHAR(50) DEFAULT NULL COMMENT '  ',
  PRIMARY KEY (`cat_id`)
) ENGINE=INNODB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8

クエリの5種類の文

where(    )、having（  ）、group by（  ）、order by（  ）、limit（    ）

 1. where 
			      
                > ,  < ,=  , != （< >）,>= , <=  
                in(v1,v2..vn)  
                between v1 and v2     v1 v2  （  v1,v2）
                IS NULL:       NULL,       true。
				IS NOT NULL:        NULL,       true。
				<=>:      （   =   ），          NULL     true。
                 
                not ( ! )     
                or ( || )     
                and ( && )     
                

 2. group by      （    ）       
	#         
	SELECT a.goods_id, a.cat_id, MAX(a.price) AS price, a.state FROM mall_goods AS a GROUP BY a.cat_id 
	#      
	SELECT a.goods_id, a.cat_id, MIN(a.price) AS price, a.state FROM mall_goods AS a GROUP BY a.cat_id
	#        
	SELECT a.goods_id, a.cat_id, SUM(a.price) AS price, a.state FROM mall_goods AS a GROUP BY a.cat_id
	#       
	SELECT a.goods_id, a.cat_id, AVG(a.price) AS price, a.state FROM mall_goods AS a GROUP BY a.cat_id
	#         
	SELECT a.goods_id, a.cat_id, COUNT(a.price) AS price, a.state FROM mall_goods AS a GROUP BY a.cat_id 
 3. having where  ，      ，where        ，having     where          ，    having            ，   ,having group by  ，having       。
    #      95   
	SELECT a.goods_id,a.cat_id,a.price,a.state FROM mall_goods AS a HAVING a.price>95
	      。。。
 4. order by      ，desc     ，asc    ，     。
 5. limit x,n

mysqlサブクエリ

 1. where    
	  #  order by        
	  SELECT  a.goods_id,a.cat_id,a.price,a.state FROM mall_goods AS a WHERE a.goods_id = (SELECT MAX(goods_id) FROM mall_goods);
	  #  order by            
	  select a.goods_id,a.cat_id,a.price,a.state FROM mall_goods AS a where a.goods_id in (select max(goods_id) from mall_goods group by cat_id);
 2. from    (               )
		#              。
		#  1
			SELECT a.goods_id,a.cat_id,a.price,a.state FROM mall_goods AS a 
			WHERE (SELECT COUNT(b.goods_id) FROM mall_goods AS b WHERE b.cat_id = a.cat_id AND b.price > a.price  ) <2 
			ORDER BY a.cat_id,a.price DESC;
		#  2
			SELECT a.goods_id,a.cat_id,a.price,a.state FROM mall_goods AS a
			LEFT JOIN mall_goods AS b 
			ON a.cat_id = b.cat_id AND a.price < b.price
			GROUP BY a.goods_id,a.cat_id,a.price
			HAVING COUNT(b.goods_id) < 2
			ORDER BY a.cat_id,a.price DESC;
 3. exists    （           ，          ）
		#          
		SELECT cat_id,cat_name FROM mall_category AS b WHERE EXISTS(SELECT a.goods_id,a.cat_id,a.price,a.state FROM mall_goods AS a WHERE a.cat_id = b.cat_id);

unionの使い方

UNION              SELECT       。
  【UNION     SELECT             。             。  ，   SELECT             】。
   ，UNION          。        ，    UNION ALL。

Left Connection

	1、   
		     ，      ，         ，  null   ，       >=      
	2、   
		a left join b     b right join a
	3、   
		            ，【          null     （      ）】