5)mysql sql的使用技巧及优化(1)

一、连接和子查询的使用

正确使用sql的重要性

准备数据（其中，tdb_goods的cate_id是逻辑外键）：

表：tdb_goods

表：tdb_goods_cates

DROP TABLE IF EXISTS `tdb_goods_cates`;CREATE TABLE `tdb_goods_cates` (  `cate_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `cate_name` varchar(40) DEFAULT NULL,  PRIMARY KEY (`cate_id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of tdb_goods_cates-- ----------------------------INSERT INTO `tdb_goods_cates` VALUES ('1', '台式机');INSERT INTO `tdb_goods_cates` VALUES ('2', '平板电脑');INSERT INTO `tdb_goods_cates` VALUES ('3', '服务器/工作站');INSERT INTO `tdb_goods_cates` VALUES ('4', '游戏本');INSERT INTO `tdb_goods_cates` VALUES ('5', '笔记本');INSERT INTO `tdb_goods_cates` VALUES ('6', '笔记本配件');INSERT INTO `tdb_goods_cates` VALUES ('7', '超级本');INSERT INTO `tdb_goods_cates` VALUES ('8', '路由器');INSERT INTO `tdb_goods_cates` VALUES ('9', '交换机');INSERT INTO `tdb_goods_cates` VALUES ('10', '网卡');DROP TABLE IF EXISTS `tdb_goods`;CREATE TABLE `tdb_goods` (  `goods_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `goods_name` varchar(150) NOT NULL,  `cate_id` smallint(5) unsigned NOT NULL,  `brand_id` smallint(5) unsigned NOT NULL,  `goods_price` decimal(15,3) unsigned NOT NULL DEFAULT '0.000',  `is_show` tinyint(1) NOT NULL DEFAULT '1',  `is_saleoff` tinyint(1) NOT NULL DEFAULT '0',  PRIMARY KEY (`goods_id`)) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;-- ------------------------------ Records of tdb_goods-- ----------------------------INSERT INTO `tdb_goods` VALUES ('1', 'R510VC 15.6英寸笔记本', '5', '2', '3399.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('2', 'Y400N 14.0英寸笔记本电脑', '5', '7', '4899.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('3', 'G150TH 15.6英寸游戏本', '4', '9', '8499.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('4', 'X550CC 15.6英寸笔记本', '5', '2', '2799.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('5', 'X240(20ALA0EYCD) 12.5英寸超极本', '7', '7', '4999.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('6', 'U330P 13.3英寸超极本', '7', '7', '4299.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('7', 'SVP13226SCB 13.3英寸触控超极本', '7', '6', '7999.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('8', 'iPad mini MD531CH/A 7.9英寸平板电脑', '2', '8', '1998.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('9', 'iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）', '2', '8', '3388.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('10', ' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）', '2', '8', '2788.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('11', 'IdeaCentre C340 20英寸一体电脑 ', '1', '7', '3499.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('12', 'Vostro 3800-R1206 台式电脑', '1', '5', '2899.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('13', 'iMac ME086CH/A 21.5英寸一体电脑', '1', '8', '9188.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('14', 'AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）', '1', '3', '3699.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('15', 'Z220SFF F4F06PA工作站', '3', '4', '4288.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('16', 'PowerEdge T110 II服务器', '3', '5', '5388.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('17', 'Mac Pro MD878CH/A 专业级台式电脑', '3', '8', '28888.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('18', ' HMZ-T3W 头戴显示设备', '6', '6', '6999.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('19', '商务双肩背包', '6', '6', '99.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('20', 'X3250 M4机架式服务器 2583i14', '3', '1', '6888.000', '1', '0');INSERT INTO `tdb_goods` VALUES ('23', ' LaserJet Pro P1606dn 黑白激光打印机', '12', '4', '1849.000', '1', '0');

1.1 inner join (内连接)

查询出公共部分

-- 内连接select a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a inner join tdb_goods_cates b on a.cate_id = b.cate_id;

1.2 Left Outer Join (左外连接)

这里分为两个部分，包含公共部分跟不包含公共部分

• 1.包含公共部分，直接将上面inner 该查 left即可，观察结构即可发现多出了一条记录的cate_name为null,因为这条记录的cate_id为12，而tdb_goods_cates 表中找不到
  

-- 左连接select a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a left join tdb_goods_cates b on a.cate_id = b.cate_id ;

• 2.不包含公共部分，即只是查出这条cate_name为null的记录,sql改造如下

-- 左连接(不包含公共部分)select a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a left join tdb_goods_cates b on a.cate_id = b.cate_id where b.cate_id is null;

1.3 右连接

这里他跟左连接相反，就不多加说明,直接给出例子，观察结果即可。

-- 右连接select a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a right join tdb_goods_cates b on a.cate_id = b.cate_id ;-- 右连接(不包含公共部分)select a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a right join tdb_goods_cates b on a.cate_id = b.cate_id where a.cate_id is null;

1.4 full join (mysql不支持，使用UNION ALL代替)

-- 包含了大量公共的重复数据select * from (select a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a left join tdb_goods_cates b on a.cate_id = b.cate_id UNION ALLselect a.goods_name,a.goods_price,a.is_show,b.cate_name from tdb_goods a right join tdb_goods_cates b on a.cate_id = b.cate_id ) c;

1.5 update join set(多表更新)的使用

在 http://blog.csdn.net/hp5321/article/details/53786796 中有讲解。 8.4中

1.6 使用join避免子查询，提高效率

例子： 查询出每类商品中，最贵的。

子查询的实现：

-- 先根据id查询每类商品最贵的一个select MAX(a.goods_price),a.goods_name,a.cate_id from tdb_goods a where a.cate_id = 1;select MAX(a.goods_price),a.goods_name,a.cate_id from tdb_goods a where a.cate_id = 2;select MAX(a.goods_price),a.goods_name,a.cate_id from tdb_goods a where a.cate_id = 3;select MAX(a.goods_price),a.goods_name,a.cate_id from tdb_goods a where a.cate_id = 4;select MAX(a.goods_price),a.goods_name,a.cate_id from tdb_goods a where a.cate_id = 5;-- 最终sqlselect a.goods_name,a.goods_price,b.cate_name from tdb_goods a INNER JOIN tdb_goods_cates b ON a.cate_id = b.cate_idwhere a.goods_price = (select MAX(c.goods_price) from tdb_goods c where c.cate_id = b.cate_id) ORDER BY a.goods_price;

使用join的实现：

-- 使用join 避免子查询select a.goods_name,a.goods_price,b.cate_name from tdb_goods a INNER JOIN tdb_goods_cates b ON a.cate_id = b.cate_id-- 在查询出数据的基础上，关联多一次tdb_goods表，获取 max(c.goods_price) 条件INNER JOIN tdb_goods c ON c.cate_id = b.cate_idGROUP BY a.goods_name,a.goods_price,b.cate_name HAVING a.goods_price = max(c.goods_price)ORDER BY a.goods_price;

1.7 特殊情况，使用子查询，可简化开发

例子：查询出每类商品价格最贵的前两个。

-- 查询出每类商品价格最贵的前两个。select a.goods_name,a.goods_price,b.cate_name from tdb_goods a INNER JOIN tdb_goods_cates b ON a.cate_id = b.cate_idwhere b.cate_name = '游戏本' ORDER BY a.goods_price DESCLIMIT 2;-- 可以使用以上的语句，根据类型遍历出所有

使用子查询，可方便查出所有类型的前2名

这个sql的思路就是：给tdb_goods表中,查的的时候计算出一列排位列，外部根据排位列，即可很方便的查询出所有类别的前二名

select c.goods_name,c.goods_price,d.cate_name from (select a.goods_name,a.goods_price,a.cate_id-- 根据 分类cate_id, 增加一个排位字段cnt，后面直接根据这个排位字段cnt<=2,即可查出前2名,(select count(*) from tdb_goods b where a.cate_id=b.cate_id and a.goods_price<=b.goods_price) cnt from tdb_goods a  GROUP BY a.goods_name,a.goods_price,a.cate_id) c INNER JOIN tdb_goods_cates d ON c.cate_id = d.cate_idWHERE c.cnt <=2ORDER BY d.cate_name,c.goods_price desc

二、sql处理行列转换

2.1 行转列：场景

统计每个月的销售金额

学生成绩的成绩表

2.1 行转列：数据准备

以学生表为例

倒入数据表

DROP TABLE IF EXISTS `t_student`;CREATE TABLE `t_student` (  `stu_id` int(11) NOT NULL AUTO_INCREMENT,  `stu_name` varchar(20) DEFAULT NULL COMMENT '姓名',  `stu_subject` varchar(20) DEFAULT NULL COMMENT '学科',  `stu_score` int(11) DEFAULT NULL COMMENT '成绩',  PRIMARY KEY (`stu_id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_student-- ----------------------------INSERT INTO `t_student` VALUES ('1', '张三', '数学', '78');INSERT INTO `t_student` VALUES ('2', '张三', '语文', '85');INSERT INTO `t_student` VALUES ('3', '张三', '英语', '90');INSERT INTO `t_student` VALUES ('4', '李四', '数学', '90');INSERT INTO `t_student` VALUES ('5', '李四', '语文', '75');INSERT INTO `t_student` VALUES ('6', '李四', '英语', '80');

2.3 行转列 ：例子

查看学生成绩表

select stu_name,SUM(case when stu_subject='数学' then stu_score end) as "数学",SUM(case when stu_subject='语文' then stu_score end) as "语文",SUM(case when stu_subject='英语' then stu_score end) as "英语"from t_studentGROUP BY stu_name;

2.4 列转行： 场景

有一个权限表，记录了用户跟权限的关系，而权限是以逗号的方式隔开，此时需要转成右边以行的形式

2.5 列转行 ：数据准备

做这个转换的sql，需要一张序列表来协助完成

两张如下：

sql导入：

DROP TABLE IF EXISTS `t_operator`;CREATE TABLE `t_operator` (  `operator_id` int(11) NOT NULL AUTO_INCREMENT,  `op_userid` int(11) DEFAULT NULL COMMENT '用户id',  `permission` varchar(255) DEFAULT NULL COMMENT '权限',  PRIMARY KEY (`operator_id`)) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_operator-- ----------------------------INSERT INTO `t_operator` VALUES ('1', '10001', '10070501,13011104,1231532,12412,123123');INSERT INTO `t_operator` VALUES ('71', '10002', '1007012301,130111104,1231531232,1241122');INSERT INTO `t_operator` VALUES ('72', '10003', '1234123,131231123');DROP TABLE IF EXISTS `t_seq`;CREATE TABLE `t_seq` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_seq-- ----------------------------INSERT INTO `t_seq` VALUES ('1');INSERT INTO `t_seq` VALUES ('2');INSERT INTO `t_seq` VALUES ('3');INSERT INTO `t_seq` VALUES ('4');INSERT INTO `t_seq` VALUES ('5');INSERT INTO `t_seq` VALUES ('6');INSERT INTO `t_seq` VALUES ('7');INSERT INTO `t_seq` VALUES ('8');INSERT INTO `t_seq` VALUES ('9');

2.6 列转行 ：例子

先看完整例子，再解释例子

此例子为固定句式：

select     b.op_userid,-- 这个替换规则是固定的，复制过来用即可    REPLACE(        SUBSTRING(            SUBSTRING_INDEX(b.permission,',',a.id),            CHAR_LENGTH(                SUBSTRING_INDEX(b.permission,',',a.id-1)            )+1        ),',',''    ) as permissionfrom t_seq aCROSS JOIN(select op_userid ,CONCAT(permission,',') as permission ,LENGTH(permission)-LENGTH(REPLACE(permission,',',''))+1 as sizefrom t_operator) bON a.id <= b.size;

执行效果：

例子解释：
例子中，我们先看 cross join 里面的内容，这里是在permission中拼接多一个逗号，主要是看后一个，在LENGTH(permission)-LENGTH(REPLACE(permission,’,’,”))+1 as size中，LENGTH(permission)为获取这个字段的长度，
LENGTH(REPLACE(permission,’,’,”) 这个则是将逗号替换成空字符串之后的长度，两个想减然后加一，即得到了permission中的个数

select op_userid ,CONCAT(permission,',') as permission ,LENGTH(permission)-LENGTH(REPLACE(permission,',',''))+1 as sizefrom t_operator;

这条语句的执行结果

接着，利用cross join 将语句变成多行

select * from t_seq aCROSS JOIN( select op_userid ,CONCAT(permission,',') as permission ,LENGTH(permission)-LENGTH(REPLACE(permission,',',''))+1 as sizefrom t_operator)b-- 这个条件是根据 size的多少，计算需要多少行 ：如 10001这一行的，需要变成5行ON a.id <= b.size;

最后，根据这个结构，进行permission的没一行进行切割字符串即可
切割规则为
固定写法：

    REPLACE(        SUBSTRING(            SUBSTRING_INDEX(b.permission,',',a.id),            CHAR_LENGTH(                SUBSTRING_INDEX(b.permission,',',a.id-1)            )+1        ),',',''    ) as permission

个人学习笔记（慕课网）