SQL轻松玩转Excel的10大功能,最后再来几道面试题练练手。
来源 : 知乎 转自:SQL数据库开发
create table sale_guang
SELECT * from sale where city="广州";
SELECT * from sale a
inner JOIN
(SELECT ordernum,profit from sale_guang) b
on a.ordernum=b.ordernum
SELECT * from sale a
WHERE a.ordernum not in
(SELECT b.ordernum from sale_guang b);
SELECT * FROM sale
where salesnum not in
(SELECT salesnum from sale
GROUP BY salesman
HAVING COUNT(salesnum)>1)
--用0填充:
update sale set city = 0 where city = NULL
--删除有缺失值的行:
delete from sale where city = NULL;
SELECT * from sale
where salesman = "张爱"
and city = "北京"
and orderaccount >=6000;
SELECT * from sale
where inventoryname like "%三星%"
or 存货名称 like "%索尼%";
SELECT city,sum(`profit`)
from sale
WHERE city = "北京"
GROUP BY `city`;
--有多少个?
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`;
SELECT trim(inventoryname) from sale;
SELECT city,ordernum,
(Nontaxamount - profit) as cost
from sale
order by cost DESC;
总结:结构化查询语言(Structured Query Language)简称SQL,果然和它名字一样,查询起来得心应手,但做想做数据处理方面,能明细感受到比Python和excel吃力(也可能是我还没学好orz)。
贴一些在面试时遇到过的SQL笔试题吧:
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`);
点分享 点收藏 点点赞 点在看
评论