千万级mysql数据查询_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项后的并集(去除了重复项)】