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

最新推荐文章于 2024-09-24 09:50:23 发布

hp柠檬茶

最新推荐文章于 2024-09-24 09:50:23 发布

阅读量404

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql 优化

本文链接：https://blog.csdn.net/hp5321/article/details/53787258

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一连接和子查询的使用
二sql处理行列转换

一、连接和子查询的使用

正确使用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 ALL
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 ) 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;

-- 最终sql
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
where 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_id
GROUP 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_id
where b.cate_name = '游戏本' 
ORDER BY a.goods_price DESC
LIMIT 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_id
WHERE c.cnt <=2
ORDER 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_student
GROUP 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 permission
from t_seq a
CROSS JOIN
(
select op_userid ,CONCAT(permission,',') as permission ,
LENGTH(permission)-LENGTH(REPLACE(permission,',',''))+1 as size
from t_operator) b
ON 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 size
from t_operator;

这条语句的执行结果
这里写图片描述

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

select * 
from t_seq a
CROSS JOIN( 
select op_userid ,CONCAT(permission,',') as permission ,
LENGTH(permission)-LENGTH(REPLACE(permission,',',''))+1 as size
from 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