一、连接和子查询的使用
正确使用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.youkuaiyun.com/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个人学习笔记（慕课网）
956
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
