小匿 | 作者
知乎专栏 | 来源
https://zhuanlan.zhihu.com/p/30212389
1
关联公式：Vlookup
create table sale_guang SELECT * from sale where city="广州";
需求：根据订单明细号关联两表，并且sale_guang只有订单明细号与利润两列
SELECT * from sale a inner JOIN (SELECT ordernum,profit from sale_guang) b on a.`ordernum`=b.`ordernum`;
2
对比两列差异
需求：对比sale的订单明细号与sale_guang订单明细号的差异
SELECT * from sale a WHERE a.ordernum not in (SELECT b.ordernum from sale_guang b);
3
去除重复值
需求：去除业务员编码的重复值
SELECT * FROM sale where salesnum not in (SELECT salesnum from sale GROUP BY salesman HAVING COUNT(salesnum)>1)
4
缺失值处理
需求：用0填充缺失值或则删除有地区名称缺失值的行
#用0填充： update sale set city = 0 where city = NULL #删除有缺失值的行： delete from sale where city = NULL;
5
多条件筛选
需求：想知道业务员张爱，在北京区域卖的商品订单金额大于等于6000的信息
SELECT * from sale where salesman = "张爱" and city = "北京" and orderaccount >=6000;
6
模糊筛选数据
需求：筛选存货名称含有"三星"或则含有"索尼"的信息
SELECT * from sale where inventoryname like "%三星%" or 存货名称 like "%索尼%";
7
分类汇总
需求：北京区域各业务员的利润总额。
SELECT city,sum(`profit`) from sale WHERE city = "北京" GROUP BY `city`;
8
条件计算
需求：存货名称含“三星字眼”并且税费高于1000的订单有几个？这些订单的利润总和和平均利润是多少？
#有多少个？
SELECT COUNT(*) from sale
where inventoryname like "%三星%"
and `tax` > 1000 ;
#这些订单的利润总和和平均利润是多少？
SELECT `ordernum`,SUM(profit),AVG(`profit`) from sale
where inventoryname like "%三星%"
and `tax` > 1000
GROUP BY `ordernum`;
9
删除数据间的空格
需求：删除存货名称两边的空格
SELECT trim(inventoryname) from sale;
10
合并与排序列
需求：计算每个订单号的成本并从高到低排序（成本 = 不含税金额 - 利润）
SELECT city,ordernum,(Nontaxamount - profit) as cost from sale order by cost DESC;
SQL笔试题
Student：
Score：
select sname,ssex,class from student;
select * from score between 60 and 80;
select class,avg(degree) from Score a join student b on a.sno = b.sno GROUP BY CLASS；
create table Student_new (sno varchar(20) PRIMARY KEY, sname varchar(10),ssex char(2), sage int,sdept varchar(25))；
select * from student where sdept = "计算机" order by sno ;
select a.sno,a.sname,a.ssex from student a join (Course b ,SC c) on a.sno=c.sno and b.cno =c.cno where Ccredit = 5 and Grade > 60；
SELECT a.cus_id from `表a` as a INNER JOIN `表b` as b on a.cus_id=b.cus_id；
SELECT * from `表a` UNION SELECT * from `表b`；
SELECT * from `表a` where cus_id not in (SELECT * from `表b`) UNION SELECT * from `表b` where cus_id not in (SELECT * from `表a`)；
SELECT * from `表a` WHERE cus_id not in (SELECT cus_id from `表b`)；
数据分析师，年终述职报告可以这么写（文末送书）
天猫双十一成交额是如何预测的
强势回归！再说回归分析