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

---

查询的五种子句

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。

左连接，右连接，内连接

	1、左连接
		以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，所以输出结果数>=左表原数据数
	2、右连接
		a left join b 等价于 b right join a
	3、内连接
		查询结果是左右连接的交集，【即左右连接的结果去除null项后的并集（去除了重复项）】